高性能MySQL读书摘要（六）查询性能优化

优化内容包括：查询优化、索引优化、库表结构优化。

6.1 为什么查询速度会慢

​了解查询的生命周期、清楚查询的时间消耗情况对于优化查询有很大的意义。

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

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

1. 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。
2. 确认MySQL服务器层是否在分析大量超过需要的数据行。

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

​有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这里有一些典型的案例：
查询不需要的记录
多表关联时返回全部列

​SELECT * FROM actor INNER JOIN film_actor… 这将返回这三个表的全部数据列。

总是取出全部列
​每次看到SELECT * 的时候都需要用怀疑的眼光审视，是不是真的需要返回全部的列？因此，一些DBA是严格禁止SELECT * 的写法的，这样做有时候还能避免某些列被修改带来的问题。如果应用程序使用了某种缓存机制，或者有其他考虑，获取超过需要的数据也可能又其好处，但不要忘记这样做的代价是什么？获取并缓存所有的列的查询，相比多个独立的只获取部分列的查询可能就更有好处。

重复查询相同的数据
​很容易出现这样的错误—不断地重复执行相同的查询，并且每次都返回相同的数据。比较好的方案是，当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好。

6.2.2 MySQL是否在扫描额外的记录

​对于MySQL，最简单的衡量查询开销的三个指标如下：
​1. 响应时间
​2. 扫描的行数
​3. 返回的行数
​MySQL慢查询记录了这三个指标，所以检查慢日志记录的是找出扫描行数过多的查询的好办法。
响应时间
​响应时间是两个部分之和：服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间—可能是等I/O操作完成，也可能是等待行锁。判断响应时间是否在合理的值，需要了解这个查询需要哪些索引以及它的执行计划是什么，然后计算大概需要多少个顺序和随机I/O，再用其乘以在具体硬件条件下一次I/O的消耗时间。最后把这些都加起来则可。

查询缓存的功能要根据实际的情况进行使用，建议设置为按需缓存（DEMAND）模式，因为查询缓存的功能并不是那么好用。比如我们设置了 query_cache_type = ON，当我们好不容易缓存了很多查询语句之后，任何一条对此表的更新操作都会把和这个表关联的所有查询缓存全部清空，那么在更新频率相对较高的业务中，查询缓存功能完全是一个鸡肋。因此，在 MySQL 8.0 的版本中已经完全移除了此功能，也就是说在 MySQL 8.0 之后就完全没有查询缓存这个概念和功能了。

扫描的行数和返回的行数
​理想情况下扫描的行数和返回的行数应该是相同的。但实际情况中这种情况不多。
扫描的行数和访问类型
​在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。在EXPLAIN语句中的type列反应了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些，速度从慢到快，扫描行数也是从小到大。
​如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引。
一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏依此为：

1. 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
2. 使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务层完成的，但无须再回表查询记录。
   3.从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现using Where）。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。
   ​MySQL不会告诉我们生成结果实际上需要扫描多少行数据，而只会告诉我们生成结果时一共扫描了多少行数据。如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它：
3. 使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应的行就可以返回结果了。
4. 改变库表结构，例如使用单独的汇总表。
5. 重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询。

6.3 重构查询方式

有时候，可以将查询转换一种写法让其返回一样的结果，但是性能更好。

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

​设计查询的时候一个需要考虑的重要问题是，是否需要将一个复杂的查询分成多个简单的查询。在传统实现中，总是强调需要数据库层完成尽可能多的工作，这样做的逻辑在于以前总是认为网络通信、查询解析和优化是一件代价很高的事情。
​但是这样的想法对于MySQL并不适用，MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。现代的网络速度比以前要快很多，无论是带宽还是延迟。在某些版本的MySQL上，即使在一个通用服务器上，也能够运行每秒超过10万的查询，即使是一个千兆网卡也能轻松满足每秒超过2000次的查询。
​MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢很多。有时候，将一个大查询分解为多个小查询是很有必要的。但是如果一个查询能够胜任时还写成多个独立查询是不明智的。

6.3.2 切分查询

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

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

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

那么可以用类似下面的办法完成同样的工作：

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

一次删除一万行数据一般来说是一个比较高效而且对服务器影响也最小的做法（如果是事务型引擎，很多小事务能够更高效）。同时，需要注意的是，如果每次删除数据后，都暂停一会儿再做下一次删除，这样也可以将服务器上原本一次性的压力分散到一个很长时间段中，就可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。

6.3.3 分解关联查询

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

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,555);

重构查询有什么好处呢？

1. 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。例如，上面查询中的tag已经被缓存了，那么应用就可以跳过第一个查询。
2. 将查询分解后，执行单个查询可以减少锁的竞争。
3. 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
4. 查询本身效率也可能会有所提升。在这个例子中，使用IN()代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效。
5. 可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。
6. 更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多。

6.4 查询执行的基础

​只有弄清楚MySQL是如何优化和执行查询的，才能让SQL执行更高效。

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

6.4.1 MySQL客户端/服务器通信协议

​MySQL客户端和服务器之间的通信协议是“半双工”的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。相反的，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不是简单地只取前面几条结果，然后让服务器停止发送数据。这种情况下，客户端若接收完整的结果，然后取前面几条需要的结果，或者接收完几条结果后就“粗暴”地断开连接，都不是好主意。这也是在必要的时候一定要在查询中加上LIMIT限制的原因。

查询状态

对于一个MySQL连接，或者说一个线程，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。
SLEEP:线程正在等待客户端发生新的请求。
Query:线程正在执行查询或者正在将结果发送给客户端。
Locked:在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。
Analyzing and statistics:线程正在收集存储引擎的统计信息，并生成查询的执行计划。
Copying to tmp table[on disk]
线程正在执行查询，并将其结果集都复制到一个临时表中，这种状态一般要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作。如果这个状态后面还有“on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上。此时，效率就比较低。
Sorting result
线程正在对结果集进行排序。
Sending data
这表示多种情况：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

6.4.2 查询缓存

​在解析一个查询语句之前，如果查询存的是打开的，一般生产是不打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果，这种情况下查询就会进入下一阶段的处理。

6.4.3 查询优化处理

​查询的生命周期的下一步是将一个SQL转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划。在实际执行中，这几部分可能一起执行也可能单独执行。

1. 语法解析器和预处理
   ​MySQL解析器将使用MySQL语法规则验证和解析查询。预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名。会验证权限。
2. 查询优化器
   ​现在语法树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可能有很多执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
   ​MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。最初，成本的最小单位是随机读取一个4K数据页的成本，后来变得更加复杂，并且引入了一些“因子”来估算某些操作的代价，如当执行一次WHERE条件比价的成本。可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本。

这个结果表示MySQL的优化器认为大概需要做1040个数据页的随机查找才能完成上面的查询。这是根据一系列的统计信息计算得出的：每个表或者索引的页面个数、索引的基数（索引中不同值得数量）、索引的数据行的长度、索引分布情况。优化器在评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘I/O。

有很多种原因会导致MySQL优化器选择错误的执行计划，如下所示：

1. 统计信息不准确。MySQL依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能非常大。例如，InnoDB因为其MVCC的架构，并不能维护一个数据表的行数的精确统计信息。
2. 执行计划中的成本估算不等同于实际执行的成本。MySQL层面并不知道哪些页面在内存中、哪些在磁盘上，所以查询实际执行过程中到底需要多少次物理I/O是无法得知的。
3. MySQL的最优可能和你想的最优不一样。
4. MySQL从不考虑其他并发执行的查询，这可能会影响到当前查询的速度。
5. MySQL也并不是任何时候都是基于成本的优化。有时会基于一些固定的规则，如使用MATCH()则在存在全文索引的时候就使用全文索引。
6. MySQL不会考虑不受其控制的操作的成本，例如执行存储过程或者用户自定义函数的成本。
   优化策略可以分为静态优化和动态优化。静态优化可以直接对解析树进行分析，并完成优化。静态优化在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会发生变化。可以认为是一种“编译时优化”。动态优化则和查询的上下文有关，这需要在每次查询的时候都重新评估，可以认为这是“运行时优化”。
   下面是一些MySQL能够处理的优化类型：
7. 重新定义关联表的顺序
   ​ 数据表的关联并不总是按照在查询中指定的顺序进行。
8. 将外连接转化成内连接
9. 使用等价变化规则
10. 优化COUNT()、MIN()和MAX()
    ​索引和列是否可为空通常可以帮助MySQL优化这类表达式。如果MySQL使用了这种类型的优化，那么在EXPLAIN中就可以看到“SELECT tables optimized away”。
11. 预估并转化为常数表达式
    ​当MySQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理。
    
    如上，MySQL会分两步执行这个查询，第一步先从film表找到需要的行。因为在film_id字段上有主键索引，所以MySQL优化器知道这只会返回一行数据，优化器在生成执行计划的时候，就已经通过索引信息知道将返回多少行数据。因为优化器已经明确知道有多少个值（WHERE条件中的值）需要做索引查询，所以这里的表访问类型是const。
    第二步，将第一步中返回的film_id列当做一个已知取值的列来处理。因为优化器清楚在第一步执行完后，该值就会是明确的了。注意到正如第一步中一样，使用film_actor字段对表的访问类型也是const。
    另一种会看到常数条件的情况是通过等式将常数值从一个表传到另一个表，这可以通过WHERE、USING或者ON语句来限制某列取值为常数。因为使用了USING子句，优化器知道这也限制了film_id在整个查询过程中都始终是一个常量–因为它必须等于WHERE子句中的那个取值。
12. 覆盖索引扫描
    ​当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据。
13. 子查询优化
14. 提前终止查询
    ​在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询。一个典型的例子就是当使用了LIMIT子句的时候。类似这种“不同值/不存在”的优化一般用于DISTINCT、NOT EXIST()或者LEFT JOIN类型的查询。
15. 等值传播
    ​如果两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE条件传递到另一列上。
16. 列表IN()的比较
    在很多数据库系统中，IN()完全等同于多个OR条件的子句，因为这两者是完全等价的。在MySQL中这点是不成立的，MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这时一个O(logn)复杂度的操作，等价地转换成OR查询的复杂度为O(n)，对于IN()列表中有大量取值的时候，MySQL的处理速度将会更快。

​如果能够确认优化器给出的不是最佳选择，并清楚背后的原理，那么也可以帮助优化器做进一步优化。例如，可以在查询中添加hint提示，也可以重写查询，或者重新设置更优的库表结构，或者添加更加合适的索引。
hint示例：

1. 强制索引 FORCE INDEX

SELECT * FROM TABLE1 FORCE INDEX (FIELD1)

以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。

数据和索引的统计信息
​在服务器层有查询优化器，却没有保存数据和索引的统计信息。统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息。由于服务器层没有统计信息，所以MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息。包括：每个表或者索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引长度、索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划。
MySQL如何执行关联查询
​MySQL认为任何一个查询都是一次“关联”查询—并不仅仅是一个查询需要到两个表匹配才叫关联，每个查询，每个片段都可能是关联（a bit curious）。
​as for UNION，MySQL先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成UNION查询。当前MySQL关联执行的strategy is so simple。MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行以后，MySQL返回到上一层次关联表，看是否能找到更多的匹配行，依此类推迭代执行。

按照这样的方式查找第一个表记录，再嵌套查询下一个关联表，然后回溯到上一个表，在MySQL中是通过嵌套循环的方式实现–正如其名“嵌套关联查询”，如下：

从本质上说，MySQL对所有的类型的查询都以同样的方式运行。例如，MySQL在FROM子句中遇到子查询时，先执行子查询并将其结果放到一个临时表中（MySQL的临时表是没有任何索引的，在编写复杂的子查询和关联查询的时候需要注意这点。）
不过，不是所有的查询都可以转换成上面的形式。例如，全外连接就无法通过嵌套循环和回溯的方式完成。

执行计划

如果对某个查询执行EXPLAIN EXTENDED后，再执行SHOW WARNINGS，就可以看到重构出的查询。
任何多表查询都可以使用一颗树表示，例如，可以按照图6-3执行一个四表的关联查询操作。在计算机科学中，这被称为一颗平衡树。但是，这并不是MySQL执行查找的方式。MySQL总是从一个表开始一直嵌套循环、回溯完成所有表关联。所以，MySQL的执行计划总是如图6-4所示，是一颗左侧深度优先的树。

关联查询优化器
​MySQL优化器最重要的一部分就是关联查询优化，它决定了多个表关联时的顺序。通常多表关联的时候，可以有多种不同的关联顺序来获得相同的执行结果。关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序。
USING(id)=(JOIN a.id = b.id),使用STRAIGHT_JOIN关键字，按照我们之前的顺序执行。即不优化。
下面的查询可以通过不同的顺序关联最后都获取相同的结果：

SELECT film.film_id, film.title, film.release_year, actor.actor_id, actor.first_name, actor.last_name FROM sakila.film INNER JOIN sakila.film_actor USING(film_id) INNER JOIN sakila.actor USING(actor_id);

​我们来分析一下为什么MySQL会将关联顺序倒转过来：可以看到，关联顺序倒转后的第一个关联表只需要扫描很少的行数（strict speaking,MySQL并不根据读取的记录来选择最优的执行计划。实际上，MySQL通过预估需要读取的数据页来选择，读取的数据页越少越好。不过读取的记录数通常能够很好地反映一个查询的成本。）在两种关联顺序下，第二个和第三个关联表都是根据索引查询，速度都很快，不同的是需要扫描的索引项的数据是不同的：

1. 将film表作为第一个关联表时，会找到951条记录，然后对film_actor和actor表进行嵌套循环查询。
2. 如果MySQL选择首先扫描actor表，只会返回200条记录进行后面的嵌套循环查询。换句话说，倒转的关联顺序会让查询进行更少的嵌套循环和回溯操作。可以通过对应的Last_query_cost状态值。我们看到倒转的关联顺序的预估成本为241，而原来的查询的预估成本是1154。
3. 老实说，人的判断很难那么精准。绝大多数的时候，优化器做出的选择都比普通人的判断要更准确。
   ​如果有超过n个表的关联，那么需要检查n的阶乘种关联顺序。当需要关联的表超过optimizer_search_depth（可调节）的限制的时候，就会选择“贪婪”搜索模式了。

排序优化
​从性能的角度来说，应尽可能避免排序或者尽可能避免对大量数据进行排序。当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，不过MySQL将这个过程统一称为文件排序，即使完全是内存排序不需要任何磁盘文件时也是如此。
​如果需要排序的数据量小于“排序缓冲区”。MySQL使用内存进行“快速排序”操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并，最后返回排序结果。
MySQL有如下两种排序算法：

两次传输排序（旧版本使用）
读取行指针和需要的排序字段，对其进行排序，然后再根据排序结果读取所需要的数据行。
这需要进行两次数据传输，即需要从数据表中读取两次数据，第二次读取数据的时候，因为是读取排序列进行排序后的所有记录，这会产生大量的随机IO,所以两次数据传输的成本非常高。

单次传输排序（新版本使用）
先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。这个算法只在MySQL4.1和后续更新的版本才引入。因为不再需要从数据库中读取两次数据，对于IO密集型的应用，这样做的效率高了很多。另外，相比两次传输排序，这个算法只需要一次顺序IO读取所有的数据，而无须任何的随机IO。缺点是，如果需要返回的列非常多、非常大，会额外占用大量的空间，而这些列队排序操作本身来说是没有任何作用的。因为单条排序记录很大，所有可能会有更多的排序块需要合并。

很难说哪个算法效率更高，两种算法都有各自最好和最槽的场景。当查询需要所有列的总长度不超过参数max_length_for_sort_data时，MySQL使用“单次传输排序”，可以通过调整这个参数来影响MySQL排序算法的选择。关于这个细节，可以参考第8章“文件排序优化”。

​MySQL在进行文件排序的时候需要使用的临时存储空间可能会比想象的要大得多。原因在于MySQL在排序时，对每一个排序记录都会分配一个足够长的定长空间来存放。这个定长空间必须足够长以容纳其中最长的字符串，例如，如果是VARCHAR列则需要分配其完整长度；如果使用UTF-8字符集，那么MySQL将会为每个字符预留三个字节。

在关联查询的时候如果需要排序，MySQL会分两种情况来处理这样的文件排序。如果ORDER BY子句中的所有列都来自关联的第一个表，那么MySQL再关联处理第一个表的时候就进行文件排序。如果是这样，那么在MySQL的EXPLAIN结果中可以看到Extra字段会有“Using filesort"。除此之外的所有情况，MySQL都会先将关联的结果存放到一个临时表中，然后在所有的关联都结束后，再进行文件排序。这种情况下，在MySQL的EXPLAIN结果的Extra字段可以看到"Using temporary; Using filesort"。如果有LIMIT，LIMIT也会在排序之后应用，所有即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大。

MySQL5.6之后对此做了改进，当只需要返回部分排序结果的时候，例如使用了LIMIT子句，MySQL不再对所有的结果进行排序，而是根据实际情况，选择抛弃不满足条件的结果，然后再进行排序。

6.4.4 查询执行引擎

存储引擎接口有着非常丰富的功能，但是底层接口却只有十几个。这几十个接口像“搭积木”一样能够完成查询的大部分操作。这种简单的接口模式，让MySQL的存储引擎插件式架构成为可能，但是也给优化器带来了一定的限制。

6.4.5 返回结果给客户端

​如果查询可以被缓存，那么MySQL在这个阶段也会将结果存放到查询缓存中。
​MySQL将结果集返回客户端是一个增量、逐步返回的过程。例如，我们回头看看前面的关联操作，一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。
​这样处理有两个好处：服务器端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也让MySQL客户端第一时间获取返回的结果（我们可以使用SQL_BUFFER_RESULT影响这些行为）。
​结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的包发送，再通过TCP协议进行传输，在TCP传输的过程中，可能对MySQL的封包进行缓存然后批量传输。

6.5 MySQL查询优化器的局限性

6.5.1 关联子查询

​MySQL的子查询实现得非常糟糕。最糟糕的一类查询是WHERE条件中包含IN()的子查询语句。例如，我们希望找到Sakila数据库中，演员Penelope Guiness(他的actor_id为1)参演过的所有影片信息。

SELECT * FROM sakila.film WHERE film_id IN(SELECT film_id FROM sakila.film_actor WHERE actor_id = 1);

因为MySQL对IN()列表中的选项有专门的优化策略，一般会认为MySQL会先执行子查询返回所有包含actor_id为1的film_id。一般来说，IN()列表查询速度很快，所以我们会认为上面的查询会是这样执行：

-- SELECT GROUP_CONCAT(film_id) FROM sakila.fim_actor WHERE actor_id = 1;
-- Result:1,23,25,...
SELECT * FROM sakila.film WHERE film_id IN(1,23,25...)

很不幸，MySQL不是这样做的。MySQL会将相关的外层表压到子查询中，它认为这样可以更高效率地查询到数据行。也就是说，MySQL会将查询改成下面的样子：

SELECT * FROM sakila.film WHERE EXISTS(SELECT * FROM sakila.film_actor WHERE actor_id = 1 AND film_actor.film_id = film.film_id);

这时，子查询需要根据film_id来关联外部表film，因为需要film_id字段，所以MySQL认为无法先执行这个子查询。通过EXPLAIN我们可以看到子查询是一个相关子查询（DEPENDENT SUBQUERY）。

根据EXPLAIN的输出我们可以看到，MySQL先选择对file表进行全表扫描，然后根据返回的film_id逐个执行子查询。如果是一个很小的表，这个查询糟糕的性能可能还不会引起注意，如果是大表，则性能非常糟糕。当然可以通过INNER JOIN下面语句优化：

SELECT film.* FROM sakila.film INNER JOIN sakila.film_actor USING(film_id) WHERE actor_id = 1;

另一个优化的办法是使用函数GROUP_CONCAT()在IN()中构造一个由逗号分隔的列表。有时候这比上面的使用关联改写更快。因为使用IN()加子查询，性能经常会非常糟，所以通常建议使用EXISTS()等效的改写查询来获得更好的效率。下面是另外 一种改写IN()加子查询的办法（即优化器优化后的语句，但是存在相关子查询，但是并不是所有的关联子查询性能都很差）：

SELECT * FROM sakila.film WHERE EXISTS(SELECT * FROM sakila.film_actor WHERE actor_id = 1 AND film_actor.film_id = film.film_id);

如何用好关联子查询
并不是所有的关联子查询的性能都会很差。如：

一般会建议使用左外连接（LEFT OUTER JOIN）重写该查询，以代替子查询。理论上，改写后MySQL的执行计划完全不会改变。

可以看到，这里的执行计划基本上一样，下面是一些微小的区别：

1. 表film_actor的访问类型一个是DEPENDENT SUBQUERY，而另一个是SIMPLE。这个不同是由于语句的写法不同导致的，一个是普通查询，一个是子查询。这对底层存储引擎接口来说，没有任何不同。
2. 对film表，第二个查询的Extra中没有“Using where”，但这不重要，第二个查询的USING子句和第一个查询的WHERE子句实际上是完全一样的。
3. 在第二个表film_actor的执行计划的Extra列有“Not exists”。这是我们前面章节中提到的提前终止算法那，MySQL通常使用“Not exists”优化来避免在表film_actor的索引中读取任何额外的行。这完全等效于直接编写NOT EXISTS子查询，这个执行计划中也是一样，一旦匹配到一行数据，就立刻停止扫描。
4. 语句顺序优化器并不会自动调整为区分度高的字段，所以，往往增加索引的时候会伴随着SQL的修改。

所以，从理论上讲，MySQL将使用完全相同的执行计划来完成这个查询。但我们建议通过一些测试来判断使用哪种写法速度会更快。以下是我们实际的测试结果。

而且不需要使用DISTINCT和GROUP BY，也不会产生重复的结果集，我们知道一旦使用了DISTINCT 和 GROUP BY，那么在查询的执行过程中，通常需要产生临时中间表。下面我们用子查询的写法替换上面的关联：

SELECT film_id FROM sakila.film WHERE EXISTS(SELECT * FROM sakila.film_actor WHERE film.film_id = film_actor.film_id);

再一次，我们需要通过测试来对比这两种写法，哪个更快些。测试结果如下。

通过上面的例子，想说明两点：一是不需要听取哪些关于子查询的“绝对真理”，二是应该用测试来验证子查询的执行计划和响应时间的假设。

6.5.2 UNION的限制

如果希望UNION的各个子句能够根据LIMIT只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在UNION的各个子句中分别使用这些子句。例如，想将两个子查询结果联合起来，然后再取前20条记录，那么MySQL会将两个表都存放到同一个临时表中，然后再取出前20行记录：

(SELECT first_name, last_name FROM sakila.actor ORDER BY last_name)
UNION ALL
(SELECT first_name, last_name FROM sakila.customer ORDER BY last_name)
LIMIT 20;

这条查询将会把actor中的200条记录和customer表中的599条记录存放在一个临时表中，然后再从临时表中取出前20条。可以通过在UNION的两个子查询中分别加上一个LIMIT20来减少临时表中的数据：

(SELECT first_name, last_name FROM sakila.actor ORDER BY last_name LIMIT 20) UNION ALL
(SELECT first_name, last_name FROM sakila.customer ORDER BY last_name LIMIT 20) 
LIMIT 20;

这里如果想获得正确的顺序，还需要加上一个全局的ORDER BY 和 LIMIT操作。

6.5.3 索引合并优化

在前面的章节已经讨论过，在5.0和更新的版本中，当WHERE子句中包含多个复杂条件的时候，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

6.5.5 并行执行

MySQL无法利用多核特性来并行执行查询。很多其他的关系型数据库能够提供这个特性，但是MySQL做不到。不知道高版本的MySQL是否有改进。

6.5.6 哈希关联

只有在使用Memory存储引擎时，索引都是哈希索引，所以关联的时候也类似于哈希关联。

6.5.7 松散索引扫描

由于历史原因，MySQL并不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。通常，MySQL的索引扫描需要先定义一个起点和终点，即使需要的数据只是这段索引中很少数的几个，MySQL仍需要扫描这段索引中的每一个条目。
如我们有索引（a,b），有下面的查询：

SELECT * FROM tb1 WHERE b BETWEEN 2 AND 3;

因为索引的前导字段是列a，但是在查询中只指定了字段b，MySQL无法使用这个索引，从而只能通过全表扫描找到匹配的行，如下图：

了解索引的物理结构的话，不难发现还可以有一个更快的方法执行上面的查询。索引的物理结构（不是存储引擎的API)使得可以先扫描a列第一个值对应的b列的范围，然后再跳转到a列的第二个不同值扫描对应的b列的范围。图6-6展示了如果由MySQL来实现这个过程会怎样。

注意到，这时就无须再使用WHERE子句过滤，因为松散索引扫描已经跳过了所有不需要的记录。

6.5.8 最大值和最小值优化

对于MIN()和MAX()查询，MySQL的优化做得并不好。

6.5.9 在同一个表上查询和更新

MySQL不允许对同一张表同时进行查询和更新。这其实并不是优化器的限制。如果清楚MySQL是如何执行查询的，就可以避免这种情况。下面是一个无法运行的SQL，虽然这是一个符合标准的SQL语句。这个SQL语句尝试将两个表中相似的数量记录到字段cnt中：

可以通知使用生成表的形式来绕过上面的限制，因为MySQL只会把这个表当做一个临时表来处理。实际上，这执行了两个查询：一个是子查询中的SELECT语句，另一个是多表关联UPDATE，只是关联的表示一个临时表。子查询会在UPDATE语句打开之前就完成。所以下面的查询将会正常执行：

UPDATE tb1 INNER JOIN （ SELECT type, count(*) AS cnt FROM tb1 GROUP BY type ) AS der USING(type) SET tb1.cnt = der.cnt;

6.6 查询优化器的提示（hint）

​如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示（hint）来控制最终的执行计划。

HIGH_PROORITY和LOW_PRIORITY
这个提示告诉MySQL，当多个语句同时访问某个表的时候，哪些语句的优先级相对高些、哪些语句的优先级相对低些。
HIGH_PRORITY用于SELECT语句的时候，MySQL会将此SELECT语句重新调度到所有正在等待表锁以便修改数据的语句之前。实际上MySQL是将其放在表的队列的最前面，而不是按照常规顺序等待。HIGH_PRIORITY还可以用于INSERT语句，其效果只是简单地抵消了全局LOW_PRIORITY设置对该语句的影响。LOW_PRIORITY则正好相反，它会让该语句一直处于等待状态。
这两个提示只对使用表锁的存储引擎有效，千万不要在InnoDB或者其他有细粒度锁机制和并发控制的引擎中使用。即使在MyISAM中使用这个也要注意，因为会导致并发插入被禁用，可能会严重降低性能。

DELAYED
这个提示对INSERT和REPLACE有效。MySQL会将使用该提示的语句立即返回给客户端，并将插入的行数据放入到缓存区，然后在表空闲时批量将数据写入。日志系统使用这样的提示非常有效，或者是其他需要写入大量数据但是客户端却不需要等待单条语句完成I/O的应用。这个用法有一些限制：并不是所有的存储引擎都支持这样的做法；并且该提示会导致函数LAST_INSERT_ID()无法正常工作。

6.7 优化特定类型的查询

6.7.1 优化COUNT()查询

COUNT()的作用
COUNT()是一个特殊的函数，有两种非常不同的作用：它可以统计某个列值得数量，也可以统计行数。在统计列值时要求列值是非空的（不统计NULL）。如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。当MySQL确认括号内的表达式值不可能为空时，实际上就是在统计行数。最简单的就是当我们使用COUNT(*)的时候，它会忽略所有的列而直接统计所有的行数。
我们发现了一个最常见的错误就是，在括号内指定了一个列却希望统计结果集的行数。如果希望知道的是结果集的行数，最好使用COUNT(*)，这样写意义清晰，性能也会很好。

使用近似值
更复杂的优化
通常来说，COUNT()都需要扫描大量的行（意味着要访问大量数据）才能获得精确的结果，因此是很难优化的。除了前面的方法，在MySQL层面还能做的就只是索引覆盖扫描了。如果还不够，就需要考虑修改应用的架构，可以增加汇总表，或者增加Memcached这样的外部缓存系统。可能很快你就会陷入到一个熟悉的困境，“快速，精确和实现简单”，三者永远只能满足其二，必须舍掉其中一个。

6.7.2 优化关联查询

1. 确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器的关联顺序是B、A,那么就不需要在B表的对应列建上索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。
2. 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。
3. 当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能会编程笛卡尔积，不同类型的关联可能会生成不同的结果。

6.7.3 优化子查询

关于子查询优化我们给出的最重要的优化建议就是尽可能使用关联查询替代，至少当前的MySQL版本需要这样。如果使用的是MySQL5.6或更新的版本或者MariaDB，那么就可以直接忽略关于子查询的这些建议了。

6.7.4 优化GROUP BY和DISTINCT

在很多场景下，MySQL都使用同样的办法优化这两种查询，事实上，MySQL优化器会在内部处理的时候相互转化这两类查询。它们都可以使用索引来优化，这也是最有效的优化办法。但是当无法使用索引的时候，GROUP BY使用两种策略来完成：使用临时表或者文件排序来做分组。
如果需要对关联查询做分组（GROUP BY)，并且是按照查询表的某个列进行分组，那么通常采用查询表的标识列分组的效率会比其他列更高。例如下面的查询效率不会很好：

SELECT actor.first_name,actor.last_name,COUNT(*) FROM sakila.film_actor INNER JOIN sakila.actor USING(actor_id) GROUP BY actor.first_name,actor_last_name;

如果查询按照下面的写法效率则会更高：

SELECT actor.first_name,actor.last_name, COUNT(*) FROM sakila.film_actor INNER JOIN sakila.actor USING(actor_id) GROUP BY film_actor.actor_id;

使用actor.actor_id列分组的效率甚至会比使用film_actor.actor_id更好。这一点通过简单的测试即可验证。

优化GROUP BY WITH ROLLUP
分组查询的一个变种就是要求MySQL对返回的分组结果再做一次超级聚合。可以使用WITH ROLLUP子句来实现这种逻辑，但可能会不够优化。可以通过EXPLAIN来观察其执行计划，特别要注意分组是否是通过文件排序或者临时表实现的。然后再去掉WITH ROLLUP子句看执行计划是否相同。也可以通过本节前面介绍的优化器提示来固定执行计划。
很多时候，如果可以，在应用程序中做超级聚合是更好的。虽然这需要返回给客户端更多的结果。也可以在FROM子句中嵌套使用子查询，或者是通过一个临时表存放中间数据，然后和临时表执行UNION来得到最终结果。
最好的办法是尽可能的将WITH ROLLUP功能转移到应用程序中处理。

6.7.5 优化LIMIT分页

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

一个非常常见又令人头疼的问题就是，在便宜量非常大的时候，例如可能是LIMIT 1000,20这样的查询，这时MySQL需要查询10020条记录然后只返回最后20条，前面10000条记录都将抛弃，这样的代价很高。
优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于便宜量很大的时候，这样做的效率会提升非常大。考虑下面的查询：

SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50,5;

如果这个表非常大，那么这个查询最好改成下面的样子：

SELECT film.film_id, film.description FROM sakila.film INNER JOIN( SELECT film_id FROM sakila.film ORDER BY title LIMIT 50, 5) AS lim USING(film_id);

这里的“延迟关联”将大大提升查询效率，它让MySQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的LIMIT子句。
有时候也可以将LIMIT查询转换为已知位置的查询，让MySQL通过范围扫描获得到对应的结果。例如，如果在一个位置列上有索引，并且预先计算出了边界值，上面的查询就可以改写为：

SELECT film_id, description FROM sakila.film WHERE position BETWEEN 50 AND 54 ORDER BY position;
-- 或者通过以下语句
SELECT * FROM sakila.rental WHERE rental_id < 16030 ORDER BY rental_id DESC LIMIT 20;

还可以使用Sphinx优化一些搜索操作。

6.7.7 优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没有很好地使用。经常需要手工地将WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化。
除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检验。这样做的代价非常高。即使有ALL关键字，MySQL仍然会使用临时表存储结果。

6.7.8 静态查询分析

Percona Toolkit中的pt-query-advisor能够解析查找日志、分析查询模式、然后给出所有可能潜在问题的查询，并给出足够详细的建议。

6.7.9 使用用户自定义变量

用户自定义变量是一个用来存储内容的临时容器，在连接MySQL的整个过程中都存在，可以使用下面的SET和SELECT语句来定义它们：

SET @one := 1;
SET @min_actor := (SELECT MIN(action_id) FROM sakila.action);
SET @last_week := CURRENT_DATE-INTERVAL 1 WEEK;

然后可以在任何可以使用表达式的地方使用这些自定义变量：

SELECT *** WHERE col <= @last_week;

在了解自定义的变量的强大之前，我们再看看它自身的一些属性和限制，看看在哪些场景下我们不能使用用户自定义变量：

1. 使用自定义变量的查询，无法使用查询缓存。
2. 不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名和LIMIT子句中。
3. 用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信。
4. 如果使用连接池或者持久化连接，自定义变量可能让看起来毫无关系的代码发生交互（如果是这样，通常是代码BUG或者连接池BUG，这类情况确实可能发生）。
5. 在5.0之前的版本，是大小写敏感的，所以要注意代码在不同MySQL版本间的兼容性问题。
6. 不能显示地声明自定义变量的类型。确定未定义变量的具体类型的时机在不同MySQL版本中也是可能不一样。如果你希望变量是整数类型，那么最好是在初始化的时候就赋值为0，如果希望是浮点型则赋值为0.0，如果希望是字符串则赋值为‘’，用户自定义变量的类型在赋值的时候会改变。MySQL的用户自定义变量是一个动态类型。
7. MySQL优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想的方式运行。
8. 赋值的顺序和赋值的时间点并不总是固定的，这依赖于优化器的决定。实际情况可能很让人困惑。
9. 赋值符号：=的优先级非常低，所以需要注意，赋值表达式应该使用明确的括号。
10. 使用未定义变量不会产生任何语法错误，如果没有意识到这一点，非常容易犯错。