Mysql查询性能优化

Posted on | In

为什么查询会慢

如果把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定时间。如果要优化查询，实际上是优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务执行更快。

查询的生命周期大致可以分为：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。在完成这些任务的时候，查询需要在不同 的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划，锁等待，互斥等待等操作，尤其是像底层存储引擎检索数据的调用操作，这些调用需要在内存操作，CPU操作和内存不足时导致的I/O操作上消耗时间。根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。

在每一个消耗大量时间的查询案列中，我们都能看到一些不必要的额外操作，某些操作被额外的重复了很多次，某一个操作执行的太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。

优化数据访问

避免请求不需要的数据

有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃，这会给Mysql服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的CPU和内存资源。

  • 避免查询不需要的记录：
    处理分页时，应该使用LIMIT限制MySql只返回一页的数据，而不是向应用程序返回全部数据后，再由应用程序过滤不需要的行。

  • 避免查询全部列：
    使用SELECT *这种方式会返回全部列，但有时我们不需要这么多列，我们应该使用SELECT后面加具体的列名还查询需要的列。
    当然有时候返回更多列也不是坏事，因为这可以提高代码片段的复用性，可以简化开发，所以我们在实际开发中根据自己的需要可以适当返回更多的列。

  • 避免多表关联时返回全部列：
    这个和第二点其实差不多，我们在多表关联查询时应指定需要返回的列，避免返回所有表的全部列。

  • 避免重复查询相同的数据：
    我们有时会重复执行相同的查询，然后每次都返回相同的数据，这时我们可以在初次查询时将数据缓存起来，这样性能会更好。

避免扫描额外的记录

分析查询时，查看改查询扫描的行数是非常有帮助的。理想情况下扫描的行数和返回的行数应该是相同的，但实际情况很难达到这种完美情况，通常扫描的行数和返回的行数比值在1:1和10:1之间。

在Explain语句中type列反应了访问类型，rows列反应了扫描的行数。访问的类型有很多种，从全表扫描到索引扫描，返回扫描，唯一索引扫描，常数引用等，这些的速度是从慢到快，扫描的行数也是从多到少。如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引。

如果发现查询需要扫描大量的数据但只返回少数的行，可以通过尝试下面的技巧去优化它：

  • 使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表就可以返回结果。
  • 改变库表结构。例如使用单独的汇总表。
  • 重写这个复杂的查询，让Mysql优化器能够以更优化的方式执行这个查询。

重构查询的方式

在优化有问题的查询时，目标应该是找到一个更有的方法获得实际需要的结果。此时我们可以将查询转换一种写法让其返回一样的结果，但是性能更好，也可以通过修改应用代码，用另一种方式完成查询，最终达到一样的目的。

切分查询

有时候对于一个大查询我们需要分而治之，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

删除旧的数据就是一个很好的例子。定期的清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据，占满整个事务日志，耗尽系统资源，阻塞很多小的但重要的查询。将一个大的delete语句切分成多个较小的查询可以尽可能小的影响Mysql性能，同时还可以减少Mysql复制的延迟。

分解关联查询

很多高性能的应用都会对关联查询进行分解。简单地，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。例如，下面这个查询：

1
2
3
4
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';

可以分解成下面这些查询来代替：

1
2
3
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);

用分解关联查询的方式重构查询有如下优势：

  • 让缓存效率更高。如果缓存的是关联表结果，如果关联中的某个表发生了变化，那么缓存就失效了，而拆分后，如果某个表变化，并不会影响所有的缓存。
  • 将查询分解后，执行单个查询可以减少锁的竞争。
  • 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
  • 查询本身的效率也可能会有所提升。例如上面用IN()代替关联查询比随机的关联更加高效。
  • 可以减少冗余记录的查询。在应用层做关联查询，意味着对某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复的访问一部分数据。

查询执行过程

当希望Mysql能够以更高的性能运行查询时，最好的办法就是弄清楚Mysql是如何优化和执行查询的。
images
Mysql的查询过程根据上图可知：

  1. 客户端发送一条查询给服务器。
  2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
  3. 服务器进行SQL解析，预处理，再由优化器生成对应的执行计划。
  4. Mysql根据生成的执行计划，调用存储引擎的API来执行查询。
  5. 将结果返回给客户端。

Mysql客户端服务器通信协议

Mysql酷护短和服务器之间的通信协议是半双工的，这意味着，在任何一个时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。这种协议让Mysql通信简单快速，但是也从很多地方限制了Mysql。一个明显的限制就是没发进行流量控制，一旦一端开始发送消息，另一端要接受完整个消息才能响应它。

查询缓存

在解析一个查询语句前，如果查询缓存是打开的，Mysql会先检查这个查询是否命中缓存中的数据，这个检查是通过一个对大小写敏感的哈希查找实现的。命中了则直接返回结果，否则进行下一阶段处理。

查询优化处理

查询的生命周期下一步是讲一个SQL转换成一个执行计划，Mysql再依照这个执行计划和存储引擎进行交互。这个阶段包含多个子阶段：解析SQL、预处理、优化SQL执行计划。

  • 语法解析器和预处理
    首先Mysql通过关键字将SQL语句进行解析，并生成一颗对应的解析树。Mysql解析器将使用语法规则验证和解析查询，包括验证是否使用错误关键字、使用关键字顺序是否正确。预处理器则根据一些Mysql规则进一步检查解析树是否合法，包括检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。

  • 查询优化器
    解析树需要由查询优化器将其转化成执行计划，一个查询可以有很多种执行方式。优化器的作用就是找到这其中最好的执行计划。Mysql的查询优化器是一个非常复杂的部件，它使用了很多优化策略来生成一个最优的执行计划。优化策略可以简单的分为两种，一种是静态优化，一种是动态优化。

  • 关联查询优化器
    多表关联时，可以有多种不同的关联顺序来获得相同的执行结果，关联查询优化器通过评估不同顺序时的成本来选择一个代价最小的关联顺序。

  • 排序优化
    Mysql可以通过索引进行排序，当不能使用索引生成排序结果的时候，Mysql需要自己进行排序。如果数据量小，则使用快速排序在内存中操作，如果内存不够，那么Mysql会先将数据分块，对每个独立的块使用快速排序进行排序，并将结果存放在磁盘上，最后将结果合并。
    Mysql主要有两种排序算法：一是两次传输排序（旧版本使用），读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取需要的数据行；二是单词传输排序（新版本使用），先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。第一种方式缺点是需要两次数据传输，第二种缺点时需要返回的列非常多时会额外占用大量的空间。

查询执行引擎

Mysql的查询执行引擎根据执行计划来完成整个查询。相比于查询优化阶段，查询执行阶段不是那么复杂，Mysql只是简单的根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成，这些接口也就是我们称为“handler api”的接口。

返回结果给客户端

查询执行过程的最后一个阶段是将结果返回给客户端。解释查询不需要返回结果集给客户端，Mysq仍然会返回这个查询的一些信息，如该查询影响到的行数。Mysql将结果集返回客户端是一个增量、逐步返回的过程，开始生成第一条结果时，Mysql就可以开始向客户端逐步返回结果集了。这样有两个好处：一是服务器无须存储太多的结果；二是客户端可以第一时间获得返回的结果。

优化特定类型的查询

优化COUNT()查询

count()是一个特殊的函数，有两种非常不同的作用：一是可以统计某个列值的数量，此时要求列值是非空的；一是统计结果集行数。通常来说，count()需要扫描大量的行才能获得精确的结果，在mysql层面能做的优化只有通过索引覆盖扫描。

优化关联查询

  • 确保on子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器的关联顺序是B，A，那么就不需要在B表对应的列上建上索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。
  • 确保任何的group by 和order by 中的表达式只涉及一个表中的列，这样Mysql才有可能使用索引来优化这个过程。

优化子查询

关于子查询优化最重要的优化建议就是尽可能使用关联查询代替。

优化LIMIT分页

在系统中需要进行分页操作的时候，我们通常会使用limit加上偏移量的办法实现，同时加上合适的order by子句。如果有对应的索引，通常效率会不错，否则，Mysql需要做大量的文件排序操作。

在偏移量非常大的时候，例如可能是limit 10000，20这样的查询，这时Mysql需要查询10020条记录然后只返回最后20条记录，前面10000条记录被抛弃，这样的代价非常高。优化此类分页查询的一个最简单的办法就是尽可能的使用索引覆盖扫描，而不是查询所有的列，然后再根据需要做一次关联操作再返回所需要的列。例如下面的列子：

1
select film_id,description from sakila.film order by title limit 5000, 5;

如果这个表非常的大，可以改成下面的样子：

1
2
3
4
SELECT film.film_id, film.description FROM sakila.film
INNER JOIN 
(SELECT film_id FROM sakila.film ORDER BY title LIMIT 5000,5) AS lim
USING(film_id);

注意优化中关联的子查询，因为只查询film_id一个列，数据量小，使得一个内存页可以容纳更多的数据，这让MySQL扫描尽可能少的页面。在获取到所需要的所有行之后再与原表进行关联以获得需要的全部列。

优化UNION查询

Mysql总是通过创建并填充临时表的方式来执行union查询，因此很多优化策略在union查询中都没法很好的使用。除非确实需要服务器消除重复的行，否则一定要使用union all，这一点很重要，如果没有all关键字，Mysql会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性检查，这样做的代价非常高。