编程学习网 > 数据库 > mysql查询语句优化
2014
12-26

mysql查询语句优化

 这篇说下mysql查询语句优化

  • 是否请求了不需要的数据

典型案例:查询不需要的记录,多表关联时返回全部列,总是取出全部列,重复查询相同的数据。

  • 是否在扫描额外的记录

最简单的衡量查询开销的指标。

  1. 响应数据
  2. 扫描的行数
  3. 返回的行数
  •  访问类型

在评估查询开销时,需要考虑下从表中找到某一行数据的成本,mysql有好多种方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果,也有些方式可能无须扫描就能返回结果。

在EXPLAIN语句中type列反应了访问类型。访问类型有很多种,从全表扫描到索引扫描,范围扫描,唯一索引查询,常数引用等。这里列的这些,速度是从慢到快,扫描的行数也是从小到大。

因此,要尽力避免让每一条sql做全表扫描。

如果查询没办法找到合适的访问类型,那么解决的最好方式通常就是增加一个合适的索引,这个上一篇里说到过。索引让mysql以最高效,扫描行数最少的方式找到需要的记录。

 一般mysql有三种方式应用where条件。从好到坏依次为

  • 在索引中使用where条件过滤不匹配的记录,这是在存储引擎层中完成。
  • 使用索引覆盖扫描(在extra列中出现using index)来返回记录,直接从索引中过滤不需要的记录并返回命中的结果。这是在mysql服务层完成的,但不用再回表查询记录。
  • 从表中返回数据,然后过滤不满足条件的记录(在extra列中出现where),这是在mysql服务层完成的,mysql需要先从数据表中读取记录然后过滤。

 如果发现查询中扫描大量的数据却只返回少量的行。可以尝试下面方法优化。

  • 使用索引覆盖扫描,把所有需要用到的列都放到索引中,这样存储引擎不用回表获取对应行就可以返回结果了。
  • 改变表的结构,例如使用单独的汇总表
  • 重写这个复杂的查询,让mysql优化器以更优化的方式执行这个查询

 

  • 重构查询方式

一个复杂查询还是多个简单查询?

在传统实现中,总是强调数据库层完成尽可能多的工作,这样做的逻辑在于以前总是认为网络通信,查询解析,优化是一件代价很高的事。

但是这样的想法对于mysql并不适用,mysql从设计上让连接和断开连接都很轻量,在返回一个小的查询结果方面很高效。另外,现在的网络速度比以前快的多,无论是宽带还是延迟。在某些版本的mysql上,即便在一个通用的服务器上,也能运行每秒超过10万的查询。即使是一个千兆网卡也能轻松满足每秒超过2000次的查询。

 

切分查询

即所谓的分而治之,将大查询切分成小查询,每个查询功能完全一样,每次只返回一小部分结果。

删除旧的数据就是个很好的例子,定期的清理大量数据时,如果用一个大语句一次性完成的话,则可能一次锁住很多数据,占满整个事务日志,耗尽系统资源,阻塞很多小的但很重要的查询。

 因此可以

 

 

 

分解关联查询

简单说,就是对每个表进行一次单表查询,然后将结果在应用程序中进行关联。例如

可以将其分解成下面查询来替代

乍一看,这样做没有好处。事实上,有下面这些优势

  1. 让缓存效率更高。许多应用程序可以方便的缓存单表查询对应的结果对象。
  2. 将查询分解后,执行单个查询可以减少锁的竞争。
  3. 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能,可扩展。
  4. 查询本身效率也会有所提升。在这个例子中,使用in代替关联查询,可以让mysql按照id顺序进行查询,这可能比随机的关联更高效。
  5. 可以减少冗余记录的查询。做关联查询时,可能需要重复访问一部分数据。从这点看,这样的重构还可能减少网络和内存的消耗。
  6. 实现了哈希关联,而不是使用mysql的嵌套循环关联。某些场景,哈希关联的效率要高很多。
  •  mysql如何执行关联查询

mysql中“关联”一词所包含的意义比一般理解上要更广泛。总的来说,mysql认为任何一个查询都是一次“关联”,并不仅仅是一个查询需要到两个表匹配才叫关联。所以,在mysql中,每个查询,每个片段(包括子查询,甚至基于单表的select)都可能是关联。

下面看下mysql如何执行关联查询。

先看union查询。mysql先将一系列的单个查询结果放到一个临时表中,然后再重新读取临时表数据完成union查询。在mysql概念中,每个查询都是一次关联,所以读取结果临时表也是一次关联。

mysql对任何关联都执行嵌套循环关联策略,即mysql先在一个表中循环取出单条数据,然后再嵌套循环到下一个表中寻找匹配的行,依次下去,直到所有表中匹配的行为止。然后根据各个表匹配的行,返回查询中所需要的各个列。

 

可以看到查询是从actor表开始的,这是mysql关联查询优化器自动做的选择。现在用STRAIGHT_JOIN关键字,不让mysql自动优化关联。

 这次的关联顺序倒转过来,可以看到,倒转后第一个关联表只需要扫描很少的行数。而且第二个,第三个关联表都是根据索引查询,速度都很快。

最后,确保任何的group by,order by中的表达式只涉及到一个表中的列,这样mysql才有可能使用索引优化这个过程。

  • 排序优化

无论如何排序都是一个成本很高的操作。所以从性能角度考虑,应尽可能避免排序或避免对大量数据进行排序。

 上一篇说到了如何通过索引排序。当不能使用索引生成排序结果时,mysql需要自己进行排序,如果数据量小,就在内存中进行,数据量大,则需要使用磁盘。mysql统一将这一过程称为文件排序(filesort)。

 在关联查询时如果需要排序,mysql会分两种情况处理文件排序。

1.如果order by子句中的所有列都来自关联的第一个表,mysql在关联处理第一个表时就进行文件排序。如果是这样,在EXPLAIN结果中的Extra字段会有Using filesort.

2.除此之外的所有情况,mysql都会先将关联的结果存放到一个临时表中,然后在所有的关联结束后再进行文件排序。如果是这样,在EXPLAIN结果中的Extra字段会有Using temporary;Using filesort.如果查询中有LIMIT的话,LIMIT也会在排序之后应用。所以即使需要返回较少的行数,临时表和需要排序的数据量仍然会非常大。

 mysql5.6在这里做了很多重要的改进。当只需要返回部分排序结果的时候,例如,使用LIMIT子句,mysql不再所有结果排序,而是根据实际情况,选择抛弃不满足条件的结果,然后再排序。

  •  关联子查询

mysql的子查询实现非常糟糕,最糟糕的一类查询是where条件中包含in的子查询语句。

mysql对in()列表中的选项有专门的优化策略,一般会认为,mysql会先执行子查询。但是,很不幸,mysql会先将相关的外层表押到子查询中。例如

mysql会将查询改成这样

 

可以看到,mysql会先对film进行全表扫描,然后根据返回的film_id逐个执行子查询。如果外层表是个非常大的表,那这个查询的性能会非常糟糕。当然很容易重写这个查询,直接用关联就可以了。

 另一个优化方法是使用函数GROUP_CONCAT()在IN()中构造一个由逗号分隔的列表。

另外,通常建议用EXISTS()等效的改写IN()子查询。

  •  如何用好关联子查询

并不是所有的关联子查询性能都会很差。写好之后,先测试,然后做出自己的判断。有时候,子查询也会快些,例如当返回结果中只有一个表的某些列时,假设要返回所有包含同一个演员参演的电影,因为一个电影会有很多演员参演,所以可能会返回些重复记录。

使用DISTINCT和GROUP BY移除重复的记录

如果用EXISTS的话,就不需要使用DISTINCT和GROUP BY,也不会产生重复的结果集。我们知道一旦使用DISTINCT和GROUP BY,那么在执行过程中,通常会参数临时中间表。

测试,看哪种写法快点

 可以看到在这个案例中,子查询速度要快些。

  •  最值优化

对于MIN(),MAX(),mysql的优化做的并不好,例如

mysql不能够进行主键扫描,只有全表扫描了。这时可以用LIMIT重写查询。

 这样可以让mysql扫描尽可能少的表

  •  优化group by和distinct

它们都可以使用索引优化,这也是最有效的办法。当无法使用索引时,group by使用两种策略完成:使用临时表或文件排序来做分组。

 对关联查询分组,通常用查找表的标识符分组的效率比其他列更高。例如

下面的效率更高

 这个查询利用了演员姓名和id直接相关的特点,所以改写后的结果不受影响。

 如果不相关的话,可以用MIN(),MAX().绕过这种限制。但一定要清楚,select后面出现的非分组列一定是直接依赖分组列的,并且在每个组内的值是唯一的。

 实在较真的话,写成这样

不过这样成本有点高。因为子查询需要创建和填充临时表,而创建的临时表是没有任何索引的。

  •  优化LIMIT分页

最简单的办法是尽可能使用索引覆盖扫描,而不是查询所有的列。然后根据需要做一次关联操作,再返回所需的列。例如

如果这个表非常大,最好改写成这样

 这里的”延迟关联“将大大提升效率,让mysql扫描尽可能少的页面,获取需要访问的记录后再根据关联列回原表查询需要的所有列。这个也可以用来优化关联查询里面的limit.

 有时候也可以将limit查询转换为已知位置的查询,让mysql通过范围扫描获得结果。例如

在一个位置列上有索引,并且预先计算出了边界值。

另外,limit和offset的问题,会导致mysql扫描了大量不需要的行然后在抛弃掉,比如select .... limit 1000,20.

这时可以有变通方法,例如图书馆按照租借记录翻页,获取第一页。

因为rental_id是递增的,而查看记录的时候都是从离当前时间最近的地方开始的。后面的页就可以用类似于下面的查询实现

扫码二维码 获取免费视频学习资料

Python编程学习

查 看2022高级编程视频教程免费获取