《高性能MySQL》- 06 查询性能优化

这章主要是从查询设计的一些基本原则开始--也是在发现查询效率不高的时候首先需要考虑的因素。

为什么查询速度会慢

如果把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行的更快。（有时候也需要修改一些查询，减少对其他查询产生的影响）

慢查询基础：优化数据访问

查询性能低下最基本的原因是访问的数据太多。对于低效的查询，我们通过两个步骤来分析总是很有效：

• 确认应用程序是否在检索大量超过需要的数据。
• 确认MySQL服务器层是否在分析大量超过需要的数据行。

是否向数据库请求了不需要的数据

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

• 查询不需要的记录。
• 多表关联时返回全部的列。
• 总是取出全部的列。
• 重复查询相同的数据。（最好的方法是查一次就缓存一下）

一句话总结一下，只查需要的，相同内容尽量少查。

MySQL是否在扫描额外的记录

对于MySQL，最简单的衡量查询开销的三个指标如下：响应时间，扫描的行数，返回的行数。这三个指标都会记录到MySQL的慢日志中，检查慢日志是找出扫描行过多查询的好办法。

响应时间

响应时间由两部分组成，服务时间和排队时间。服务时间就是处理这个查询要花的时间，排队时间是等待资源而没有真正执行查询的时间，可能是等I/O，也可能是等锁释放，等等。当然，我们现在很难把这两部分具体分开，通常会使用一个“快速上限估计”的方式来估计查询响应时间。（了解需要什么索引，以及它怎么执行，然后计算需要多少个顺序和随机I/O，用这个乘以当前硬件执行的I/O的时间，大体就能获得一个参考值）

扫描行数和返回行数

最理想的情况是扫描行数与返回行数一致。但现实的查询中这种情况不多见。比方说我们做一个关联查询，服务器必须要扫描多行才能生成结果集中的一行。

扫描的行数和访问类型

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

在EXPLAIN语句中的type列反应了访问类型。访问类型有很多种，从全表扫描到索引扫描，范围扫描，唯一索引查询，常数引用等等（这里是从慢到快的顺序）。如果查询没有办法找到合适的访问类型，那么解决的最好办法通常是加一个合适的索引，索引可以让MySQL以最高效的，行扫描最少的方式找到需要的记录。

通常MySQL能够使用三种方式应用WHERE条件（由好到坏）：

• 在索引中使用WHERE条件过滤不匹配的记录，这是在存储引擎层的。
• 使用索引覆盖扫描（Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。
• 从数据表中返回数据，然后过滤不满足条件的记录（Using where）。在服务器层完成，需要先读表然后过滤。

这就说明了好的索引是多么的重要，它能使得尽可能只扫描需要的行。

但是MySQL并不能告诉我们生成结果实际上需要扫描多少行，只能告诉我们扫描了多少行。（具体一个查询需要扫描多少行和实际需要使用的行数需要先去理解这个查询背后的逻辑）

如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的方式去优化：

• 使用索引覆盖扫描，把所有需要用的列都放到索引里。
• 改变库表结构。例如使用单独的汇总表。
• 重写这个复杂的查询，让MySQL能以更优的方式去执行。

重构查询的方式

在优化有问题的查询时，目标应该是找到一个更优的方法获得实际所需要的结果--而不一定总是需要从MySQL获取一模一样的结果集。有时候修改应用代码，用另一种方式完成查询，最终达到目的一致。

一个复杂的查询还是简单的查询

设计时常考虑的一个问题，在其他条件相同的时候，尽可能少的查询当然是更好的。但也有时候将一个大查询分解是很有必要的。

切分查询

有时候对于一个大查询我们需要“分而治之”。

删旧数据就是一个很好的例子，一个大查询会需要锁很多数据，占满整个事务日志等等（简单来说需要很多资源，而且可能会卡住其他操作）。如果我们将其切分成多个小的查询，可以尽可能小的影响MySQL性能，同时还可以减少MySQL复制延迟。

分解关联查询

简单来说就是将关联查询改为逐个表查询。例如：

SELECT * FROM  tag  JOIN tag_post ON tag_post.tag_id=tag.id 

    WHERE tag.tag='mysql'

把上面那个拆了：

SELECT * FROM  tag  WHERE tag='mysql'

SELECT * FROM  tag_post WHERE tag_id=1234

这样做好处如下：

• 让缓存效率更高。
• 将查询分解后，执行单个查询可以减少锁的竞争。
• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
• 查询本身效率也可能会有所提升。
• 可以减少冗余记录的查询。

查询执行的基础

当希望MySQL能以高性能执行查询时，最好的办法是弄清楚MySQL是如何优化和执行查询的。

MySQL执行查询的过程：

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

MySQL客户端/服务通信协议

MySQL客户端和服务器之间的通信协议是“半双工”的。（半双工，同一时间只能由一方发生，另一方接收，但是双方都可以成为发送方和接收方）。这种协议使得MySQL通信简单快速，但是也没有办法流控。一旦一方开始发送消息，另一方要完整收完才能响应。

这也就是为什么查询很长的时候，参数max_allowed_packet就特别重要了。因为客户端发送后它能做的只是等待结果。相反，一般服务器响应给用户的数据通常很多，是由许多包组成的。所以接收结果并缓存可以减少服务器的压力。

查询状态

对于一个MySQL连接，或者说一个线程，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么（可以通过 show full processlist 查看）：

• Sleep：线程正在等待客户端发送新的请求。
• Query：正在执行查询或者正在将结果发给客户端。
• Locked：在服务器层面表示等待锁。
• Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划。
• Copying to tmp table [on disk]：线程正在执行查询，并且将结果都复制到一个临时表里（Group By 或者 文件排序 或者 Union操作）。
• Sorting result：线程正在对结果集进行排序。
• Sending data：这表示多种情况：线程可能在多个状态之间传送数据，或者生成结果集，或者在向客户端返回数据。

查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。

查询优化处理

语法解析器和预处理

首先，MySQL通过关键字将SQL语句进行解析，并生成一棵对应的解析树。例如，它将验证是否使用错误的关键字，或者使用关键字的顺序是否正确等，再或者它还会验证引号是否匹配。预处理则是根据一些MySQL规则进一步检查解析树是否合法。

查询优化器

语法树被认为合法了，就由优化器将其转化为执行计划。一条查询有很多种执行方式，优化器就是找到那个最好的方式。

有很多原因也会导致优化器选择错误的执行计划：

• 统计信息不准确。
• 执行计划中的成本估算不等于实际执行的成本。（比方说有些表在内存中，尽管需要读更多的页面，但是成本更小，MySQL并不知道哪些在内存，哪些在磁盘，实际中的物理I/O是无法得知的）
• MySQL的最优可能和你想的不一样。
• MySQL从不考虑其他并发执行的查询，这可能会影响到当前的查询速度。
• MySQL也并不是任何时候都基于成本的优化。
• MySQL不会考虑不受其控制的操作的成本。

MySQL的查询优化器是很复杂的部件，简单来说有两种优化策略：静态优化和动态优化。

静态优化，直接对解析树进行分析，并完成优化。并且不依赖于特别的数值，如WHERE条件中代入的一些常数等等。

动态优化，和查询的上下文有关，也可能和很多其他因素有关，例如WHERE条件中的取值，索引中的条目对应的数据行数等。

下面列一些MySQL能够处理的优化类型：

• 重新定义关联表的顺序。
• 将外连接转化为内连接。（并不是所有的OUTER JOIN语句都必须以外连接的方式执行）
• 使用等价变换规则。
• 优化COUNT()，MIN()，MAX()，索引和列是否为空通常帮助MySQL优化这类表达式。
• 预估并转化为常数表达式。
• 覆盖索引扫描，当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无须查询对应的数据行。
• 子查询优化，MySQL在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问。
• 提前终止查询（典型例子就是LIMIT）。
• 等值传播，如果两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE条件传递到另一个上。
• 列表IN()的比较，在MySQL中，IN() 不等于 多个or条件子句（MySQL将IN()列表中的数据排序，然后二分的方式确定列表中的值是否满足条件，这是一个O(logn)的方式（前提是不计算排序代价，只二分查），对于OR来说则是O(n)，每个都要判断）。

当然这些也远不是MySQL优化器的全部，切记：“不要自以为比优化器更聪明”。

当然，虽然优化器已经很智能了，也会有无法给出最优的结果的时候。如果能够确认优化器给出的不是最佳选择，并且清楚背后的原理，那么也可以帮助优化器优化（比方说，加hint提示，重写查询或者重新设计更优的库表）。

数据和索引的统计信息

MySQL的服务层没有任何统计信息，所以MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息。

MySQL如何执行关联查询

MySQL中的“关联”一词包含比一般意义上理解的要更广泛。MySQL的任何一次查询就是一次“关联”。MySQL执行关联的策略：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到所有表都匹配。

并不是所有的查询都可以按照循环嵌套和回溯的方式完成。例如，全外连接就没办法，这也大概就是MySQL不支持全外连接的原因。

执行计划

与其他关系型数据库不同，MySQL不会生成查询字节码来执行查询。MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。（MySQL的计划是一棵左深度优先树）

关联查询优化器

MySQL优化器最重要的一部分就是关联查询优化，它决定了多个表关联时的顺序。它会评估不同顺序时的成本来选择一个代价小的。不过有时候优化器可能给出的并不是最优的，这时可以使用STRAIGHT_JOIN关键字重写查询，让优化器按照你认为的顺序去执行。

优化器会尝试每种关联顺序，当然这就有一个问题，如果有n个关联，就有n！种顺序，单说10个关联，也有360w种情况。当搜索空间非常大的时候，优化器不可能逐一评估，会采用“贪婪”搜索的方式查找最优的关联顺序。（可以通过optimizer_serch_depth来设置）

排序优化

无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。如果不使用索引的话，MySQL需要自己排序，如果数量小于排序缓冲区，使用快排。如果内存不够，则将数据分块，然后对每个独立的块快排，并将他们存到磁盘上，之后再进行合并（merge）操作，最后返回结果（这个和外部排序的道理是一样的）

查询执行引擎

相对于查询优化阶段，查询执行阶段不是那么复杂：MySQL只是简单地根据执行计划给出的指令逐步执行。其中有大量的操作需要通过调用接口来完成，成为 handle API 的接口。查询中的每个表由一个handle的实例表示。实际上在优化阶段就为每个表创建了一个handle实例，优化器根据这些实例接口来获取表的相关信息。

返回结果的客户端

查询执行的最后一步。

优化特定类型的查询

优化COUNT()查询

count()的作用：它可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值是非空的。

关于MyISAM的神话：MyISAM的count()函数非常快，这个前提是没有WHERE条件的count(*)。

使用近似值：一种用在对精度要求不高的情况下的方式，采用explain统计，并不需要真正的执行。

更复杂的优化：通常来说count()需要扫描大量的行，才能获得精确的数据，因此是十分难优化的。常用的就是索引覆盖扫描或者修改应用架构，增加词汇表，或者外部缓存等等。“快速，精确，易于实现”通常只能实现两个而舍弃一个。

优化关联查询

事实上讨论过这个问题了这里要注意的是：

• 确保ON或者USING子句中的列上有索引。
• 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化。
• 升级MySQL的时候需要注意：关联语法，运算符优先级等其他可能会发生变化的地方。

优化GROUP BY 和 DISTINCT

它们都可以使用索引来优化，这也是最有效的优化办法。

如果索引无法使用，GROUP BY 会采用两种策略：使用临时表或者文件排序来做分组。对此，可以通过使用提示 SQL_BIG_RESULT  和 SQL_SAMLL_RESULT 来让优化器按你希望的方式运行。

优化LIMIT分页

在系统中需要进行分页的时候会使用到LIMIT。但有个问题，如果偏移量非常大的时候，例如，LIMIT 1000，20 这样的查询这时候需要查10020条，然后返回最后20条。对于这类优化，要么是在页面中限制分页数量，要么是优化大偏移量的性能。

优化此分页查询最简单的方法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回需要的列

比方说： select film_id, description from sakila.film order by title limit 50,5

如果表非常大，则可以修改为：

select film_id, description

from sakila.film

    inner join (

       select film_id from sakila.film

       order by title limit 50, 5

    ) as lim using(film_id)

这里的延迟关联将大大提升查询效率，它让MySQL扫描尽可能少的页面，获取需要访问的记录再根据关联列返回原表查询需要的所有列。

优化SQL_CALC_FOUND_ROWS

分页时，另一个常用的技巧是在LIMIT语句中加上SQL_CALC_FOUND_ROWS提示，这样就可以获得去掉LIMIT以后满足条件的行数，因此可以作为分页的总数。

还有一个设计是将具体页数替换为下一页，如果每页10条记录，那么每次查11条，如果第11条存在那么还有下一页否则没有。

优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询。因此很多优化策略在UNION中没法很好的使用。经常需要手工地将WHERE，LIMIT，ORDER BY等子句“下推”到UNION中，以便于优化器使用这些条件优化。

除非确实需要服务器消除重复行，否则就一定要使用UNION ALL。如果没有ALL 关键字，MySQL就会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。

杂谈（非正式向）  

这章总结的时候我省去了几部分，一个是MySQL查询优化器的局限性，因为那个是基于当时的情况，现在我也不确定是否还存在（我还没有深入到那个地步）。第二个是用户自定义变量，我没太看懂那个，完了看明白了补上。第三个是案例分析，因为我是写总结，外加一点点自己的思考和想法，案例分析不如看书或者看其他专栏来的好的多。

写blog要比看书慢多了，写这篇我把这章又看了一遍，还要思考哪些应该是重点，哪些是废话。最近在看分布式的东西，而且Python那边的总结还有一堆拖着（书看完了），JAVA虚拟机那个也是开了坑没填。之后抽时间慢慢补上吧，不过接下来准备学习linux和git，主要是看两本书《linux shell脚本攻略》和《github入门与实战》我不是不会linux，git。而是只会一丢丢基础，所以趁这个时间打算整体学习一下。这次我想试试一边写blog一边看书，看看效果吧，主要是看自己吸收的效果，毕竟我写总结的目的就是把书读“薄”。