MySQL 重写查询语句的三种策略

在优化存在问题的查询时，我们需要改变方式去获取查询结果——但这并不意味着从 MySQL获取同样的结果集。有些时候我们可以将查询转换为获取相同结果，但更好性能的查询形式。然而，我们也需要考虑重写查询去获取不同的结果，因为这样可以提高开发效率。也可以通过修改应用程序代码来取得相同的效果。本篇文章将介绍如何重写查询的技巧。

复杂查询与分步查询

一个重要的查询设计课题是将复杂查询分解为多个简单查询是否会更好。在传统的数据库设计中强调尽可能地用更少的查询解决大量工作。在过往，这种方式会更好。这是因为以前的网络通讯成本更高以及考虑查询解析器和优化器的负荷。

然而，这种建议并不怎么适用于 MySQL，这是由于 MySQL 处理建立连接和断开连接的方式十分高效，并且对简单查询的响应很快。当今的网络速度相比以前也有了大幅度的提升。根据不同的服务端版本，MySQL 可以在普通机器上一秒内运行超过10万次的简单查询，并且在千兆网络上完成每秒2000次的查询通讯。因此，进行分布查询并不是过往说的那么糟糕。

相比于每秒遍历的数据行数，连接响应依旧是比较慢的。在内存数据中，这个时间达到了毫秒级。当然，使用尽可能的查询次数依旧是一个不错的选择。但是，有时我们可以通过拆分复杂查询为几个简单的查询来提高性能。接下来我们将展示一些示例。

在程序设计中，使用过多的查询是一个常犯的错误。例如，有些应用执行了10个单独的查询来获取10行数据（使用循环一条条获取），而这本可以通过一条查询10行数据的查询来完成。因此，这并不是倡导每次都做查询的拆分，而是根据实际情况来。

切分查询语句

另一个方式是拆分查询后重新再组合。通过在大数据量的查询拆分为更小范围的查询以减少每次影响的行数。

清洗旧数据就是一个典型的例子。周期性的清洗数据工作需要移除大量数据，进行这样的操作会长时间锁定大量数据行。这种操作还会产生事务日志、消耗大量资源并且会阻塞那些本不应该被打断的小数据量的查询。将DELETE语句切分后，使用中等规模的查询可以显著改善性能，并且在查询是重复的时候可以减少重复查询产生的额外延迟。例如下面的删除语句：

DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH);

应用的伪代码的形式如下：

rows_affected = 0
do {
  rows_affected = do_query (
  "DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH)
  LIMIT 10000")
  } while rows_affected > 0

一次删除10000行对于提高每次查询的效率来说已经是一个足够大的任务了。一个足够短的任务会减少对服务端的影响（事务存储引擎会从中受益）。在 DELETE 语句中插入一些休眠时间也是一个不错的主意，这样可以在时间上分散负荷并且缩短持有锁的持续时间。

拆解联合查询

很多高性能的应用会拆解联合查询。可以通过将联合查询拆分为多个单表查询，然后在应用中再将结果组合起来。例如：

SELECT * FROM tag
	JOIN tag_post ON tag_post.tag_id=tag.id
  JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

可以将这个联合查询拆分如下是哪个部分。

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123, 456, 567, 9098, 8904);

注：这里的 tag_id=1234和post.id IN (123, 456, 567, 9098, 8904)都是基于前面查询的结果得到的值。为什么要这么做？第一眼看过去好像是毫无必要的——增加了查询的次数而已。然而，这种重建查询可以带来如下优势：

  • 缓存机制会更有效。很多应用直接使用 ORM 映射数据表。在这个例子中，如果 tag 为 mysql 的对象已经被缓存了，第一条查询就会跳过。如果 posts 中 id 为123，567或9908在缓存中，则可以从 IN 列表中移除这几个。通过这种策略，查询缓存会得到相应的受益。如果只有其中的一个表经常变化，拆解联合查询可以减少缓存失效的次数。
  • 单独执行这些查询有时候可以减少锁表的机会。
  • 通过这种方式很容易扩展数据库，并把数据表放到不同的机器上。
  • 查询自身可以进行优化。这个例子中，使用 IN 查询替代联合查询后，MySQL 对行 ID 进行排序和获取数据行有可能会更优。
  • 可以减少冗余的行访问。使用这种方式意味着只做一次数据行获取，而在联合查询中有可能重复获取相同的数据。基于这种原因，这种拆解方式也可能会减少整个网络负荷和内存占用。
  • 扩展一下，也可以通过人为进行哈希联合查询来替代MySQL联合查询的嵌套循环，哈希联合查询也可能会更有效。

最终可以看到，通过拆解联合查询可以使得缓存复用性更高，多服务器分布式数据方案更简单，并可以在大的数据表中使用 IN 查询替代联合查询或同一张表的多次重复查询。

以上就是MySQL 重写查询语句的三种策略的详细内容，更多关于MySQL 重写查询语句的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • MySQL查询重写插件的使用

    查询重写插件 从MySQL 5.7.6开始,MySQL Server支持查询重写插件,可以在服务器执行之前检查并可能修改服务器接收的语句. 以下是官方文档介绍: 预解析重写插件具有以下特点: 1.该插件允许在服务器处理之前重写到达服务器的SQL语句. 2.该插件接收一个语句字符串,并可能返回一个不同的字符串. 后解析查询重写插件具有以下特征: 1.该插件支持基于解析树的语句重写. 2.服务器解析每个语句并将其解析树传递给插件,插件可以遍历树.插件可以将原始树返回到服务器以进行进一步处理,或者构造

  • MySQL常用SQL语句总结包含复杂SQL查询

    1.复杂SQL查询 1.1.单表查询 (1)选择指定的列 [例]查询全体学生的学号和姓名 select Sno as 学号,Sname as 姓名 from student; select Sno,Sname from student; (2)查询全部列 [例]查询全体学生的详细信息 select * from student; (3)对查询后的指定列进行命名 [例]查询全部学生的"姓名"及其"出生年"两列 select Sname as 姓名,(2014-Sage

  • MySQL 数据库 like 语句通配符模糊查询小结

    MySQL 报错:Parameter index out of range (1 > number of parameters, which is 0)--MySQL 数据库 like 语句通配符模糊查询小结 前言 今天在使用MySQL语句执行增删改查操作时,控制台报出了以下错误:Parameter index out of range (1 > number of parameters, which is 0).翻译过来意思就是:查到结果数据为1,真实值应为0,参数越界,产生错误.如此也就明

  • MySQL模糊查询语句整理集合

    SQL模糊查询语句 一般模糊语句语法如下: SELECT 字段 FROM 表 WHERE 某字段 Like 条件 其中关于条件,SQL提供了四种匹配模式: 1.%:表示任意0个或多个字符.可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示. SELECT * FROM [user] WHERE u_name LIKE '%三%' 将会把u_name为"张三","张猫三"."三脚猫","唐三藏"等等有&

  • Python使用sql语句对mysql数据库多条件模糊查询的思路详解

    Python使用sql语句对mysql数据库多条件模糊查询的思路详解

    def find_worldByName(c_name,continent): print(c_name) print(continent) sql = " SELECT * FROM world WHERE 1=1 " if(c_name!=None): sql=sql+"AND ( c_name LIKE '%"+c_name+"%' )" if(continent!=None): sql=sql+" AND ( continent

  • 一篇文章弄懂MySQL查询语句的执行过程

    一篇文章弄懂MySQL查询语句的执行过程

    前言 需要从数据库检索某些符合要求的数据,我们很容易写出 Select A B C FROM T WHERE ID = XX  这样的SQL,那么当我们向数据库发送这样一个请求时,数据库到底做了什么? 我们今天以MYSQL为例,揭示一下MySQL数据库的查询过程,并让大家对数据库里的一些零件有所了解. MYSQL架构 mysql架构 MySQL 主要可以分为 Server 层和存储引擎层. Server层 包括连接器.查询缓存.分析器.优化器.执行器等,所有跨存储引擎的功能都在这一层实现,比如存

  • mysql语句查询用户权限过程详解

    mysql语句查询用户权限过程详解

    在MySQL中,如何查看一个用户被授予了那些权限呢? 授予用户的权限可能分全局层级权限.数据库层级权限.表层级别权限.列层级别权限.子程序层级权限.具体分类如下: 全局层级 全局权限适用于一个给定服务器中的所有数据库.这些权限存储在mysql.user表中.GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限. 数据库层级 数据库权限适用于一个给定数据库中的所有目标.这些权限存储在mysql.db和mysql.host表中.GRANT ALL ON db_na

  • 浅谈pymysql查询语句中带有in时传递参数的问题

    直接给出例子说明: cs = conn.cursor() img_ids = [1,2,3] sql = "select img_url from img_url_table where id in %s" cs.execute(sql, (img_ids, )) # 直接传递元组包裹列表即可 补充知识:Python将多行数据处理成SQL语句中where条件in(' ',' ',' ')的数据 在工作中有时需要查询上万行指定的数据,就会用到SQL语句中 select * from ta

  • SQL语句执行深入讲解（MySQL架构总览->查询执行流程->SQL解析顺序）

    SQL语句执行深入讲解（MySQL架构总览->查询执行流程->SQL解析顺序）

    前言: 一直是想知道一条SQL语句是怎么被执行的,它执行的顺序是怎样的,然后查看总结各方资料,就有了下面这一篇博文了. 本文将从MySQL总体架构--->查询执行流程--->语句执行顺序来探讨一下其中的知识. 一.MySQL架构总览: 架构最好看图,再配上必要的说明文字. 下图根据参考书籍中一图为原本,再在其上添加上了自己的理解. 从上图中我们可以看到,整个架构分为两层,上层是MySQLD的被称为的'SQL Layer',下层是各种各样对上提供接口的存储引擎,被称为'Storage Engin

  • MySQL 重写查询语句的三种策略

    在优化存在问题的查询时,我们需要改变方式去获取查询结果--但这并不意味着从 MySQL获取同样的结果集.有些时候我们可以将查询转换为获取相同结果,但更好性能的查询形式.然而,我们也需要考虑重写查询去获取不同的结果,因为这样可以提高开发效率.也可以通过修改应用程序代码来取得相同的效果.本篇文章将介绍如何重写查询的技巧. 复杂查询与分步查询 一个重要的查询设计课题是将复杂查询分解为多个简单查询是否会更好.在传统的数据库设计中强调尽可能地用更少的查询解决大量工作.在过往,这种方式会更好.这是因为以前的

  • 整理MySql常用查询语句（23种)

    废话不多了,直接贴代码了 一查询数值型数据: SELECT * FROM tb_name WHERE sum > 100; 查询谓词:>,=,<,<>,!=,!>,!<,=>,=< 二查询字符串 SELECT * FROM tb_stu WHERE sname = '小刘' SELECT * FROM tb_stu WHERE sname like '刘%' SELECT * FROM tb_stu WHERE sname like '%程序员' SE

  • MyBatis中多条件查询商品的三种方法及区别

    MyBatis中多条件查询商品的三种方法及区别

    目录 一.Sql语句设置多个参数有几种方式 二.代码附上 一.Sql语句设置多个参数有几种方式 1.散装参数:需要使用@Param标记Sql语句中占位符处的名称例如 #{name} 2.实体类封装参数 只需要保证Sql中的参数名和实体类属性名对应上,即可设置成功BrandMapper.xml中的SQL语句不用动,把TestBrandMapper中的代码修改即可 3.Map集合 只需要保证Sql中的参数名和Map集合的键的名称对应上,即可设置成功.BrandMapper.xml中Sql语句不用改

  • 防止MySQL重复插入数据的三种方法

    新建表格 CREATE TABLE `person` ( `id` int NOT NULL COMMENT '主键', `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '姓名', `age` int NULL DEFAULT NULL COMMENT '年龄', `address` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin N

  • WPF路由事件中的三种策略介绍

    WPF路由事件中的三种策略介绍

    什么是路由事件 路由事件是具有更强传播能力的事件,它可以在元素树中向上冒泡和向下隧道传播,并且能够沿着传播路径被事件处理程序来处理. 路由事件允许事件在某个元素上被处理,即使这个事件源自于另外一个元素.事件路由允许某个元素的事件由另外一个元素引发. 路由事件是一种可以针对元素树中的多个侦听器而不是仅仅针对引发该事件的对象调用处理程序的事件.路由事件是一个CLR事件. 路由事件与一般事件的区别在于:路由事件是一种用于元素树的事件,当路由事件触发后,它可以向上或向下遍历可视树和逻辑树,他用一种简单而

  • MySQL条件查询语句常用操作全面汇总

    MySQL条件查询语句常用操作全面汇总

    目录 模糊查询 union 排序 数量限制 分组 综合 顾名思义, 条件查询就是使用where字句 , 将满足条件的数据筛选出来 语法 : select < 结果 > from < 表名 > where < 条件 > 这里我们以t_user表为例 -- 查询性别为男的信息 SELECT * FROM t_user WHERE sex='男' -- 查询性别不为男的信息 SELECT * FROM t_user WHERE NOT sex='男' -- 查询性别为男并且年

  • Python 循环终止语句的三种方法小结

    在Python循环终止语句有三种: 1.break break用于退出本层循环 示例如下: while True: print "123" break print "456" 2.continue continue为退出本次循环,继续下次循环 示例如下: while True: print "123" continue print "456" 3.自定义标记 Tag 自已定义一个标记为True或False 示例代码: Tag

  • Go语言defer语句的三种机制整理

    Golang 的 1.13 版本 与 1.14 版本对 defer 进行了两次优化,使得 defer 的性能开销在大部分场景下都得到大幅降低,其中到底经历了什么原理? 这是因为这两个版本对 defer 各加入了一项新的机制,使得 defer 语句在编译时,编译器会根据不同版本与情况,对每个 defer 选择不同的机制,以更轻量的方式运行调用. 堆上分配 在 Golang 1.13 之前的版本中,所有 defer 都是在堆上分配,该机制在编译时会进行两个步骤: 在 defer 语句的位置插入 ru

  • MySQL导入sql文件的三种方法小结

    目录 一.使用工具Navicat for MySQL导入 1.打开localhost_3306,选中右击“新建数据库” 2.指定数据库名和字符集(可根据sql文件的字符集类型自行选择) 3.选中数据库下的表运行SQL文件 4.选中路径导入 二.使用官方工具MySQL Workbench导入 1.第一种方法 2.第二种方法 三.使用命令行导入 总结 一.使用工具Navicat for MySQL导入 工具的具体下载及使用方法推荐的一篇文章:https://www.jb51.net/article/

  • 提高MySQL深分页查询效率的三种方案

    提高MySQL深分页查询效率的三种方案

    开发经常遇到分页查询的需求,但是当翻页过多的时候,就会产生深分页,导致查询效率急剧下降.有没有什么办法,能解决深分页的问题呢?本文总结了三种优化方案,查询效率直接提升10倍,一起学习一下. 开发经常遇到分页查询的需求,但是当翻页过多的时候,就会产生深分页,导致查询效率急剧下降. 有没有什么办法,能解决深分页的问题呢? 本文总结了三种优化方案,查询效率直接提升10倍,一起学习一下. 1. 准备数据 先创建一张用户表,只在create_time字段上加索引: CREATE TABLE `user`

随机推荐