查询性能优化

在索引中使用WHERE条件来过滤不匹配的记录.这是在存储引擎层完成的.
使用索引覆盖扫描(在Extra列中出现了Using index)来返回记录,直接从索引中过滤不需要的记录并返回命中的结果.这是在MySQL服务层完成的,但无须回表查询记录.
从数据表中返回数据,然后过滤不满足条件的记录(在Extra列中出现Using Where).这在MySQL服务层完成,MySQL需要从数据表读出记录然后过滤.

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

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

重构查询的方式

重构查询的方式:可以将一个复杂查询分成多个简单的查询.

切分查询:将大查询切分成小查询,每个查询功能完全一样,只完成一小部分,每次只返回一部分查询结果.一般用于定期清除大量数据的场景下.
分解关联查询:可以对每一个表进行一次单表查询,然后将结果都在应用程序中进行关联.分解后可以让缓存的效率更高,可以减少锁的竞争,可以减少冗余记录的查询.可以避免使用MySQL的嵌套循环关联.

查询执行基础

MySQL执行一个查询的大致过程:

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

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

MySQL客户端和服务端之间的通信协议是"半双工的",意味着没法进行流量控制.
客户端用一个单独的数据包将查询传给服务器.一旦客户端发送了请求,它能做的事情就只是等待结果了.

查询状态

对于一个MySQL连接,或者说一个线程,任何时刻都有一个状态,该状态表示了MySQL当前正在做什么.可以使用SHOW FULL PROCESSLIST命令查看(该命令返回结果中的Command就表示当前的状态).

查询缓存

在解析一个查询语句之前,如果查询缓存是打开的,那么MySQL会优先检查这个查询是否命中查询缓存中的数据.如果当前的查询命中了查询缓存,那么在返回查询结果之前MySQL会检查一次用户权限.

查询优化处理

这一阶段会将一个SQL转换成一个执行计划,MySQL再依照这个执行计划和存储引擎进行交互.这包括多个子阶段:解析SQL,预处理,优化SQL执行计划.

MySQL通过关键字将SQL语句进行解析,并生成一棵对应的"解析树".MySQL解析器将使用MySQL语法规则验证和解析查询.
预处理器则根据一些MySQL规则进一步检查解析树是否合法.接着预处理器会验证权限.
现在语法树被认为是合法的了,并且由优化器将其转化成执行计划.一条查询可以有很多种执行方式,最后都返回相同的结果.优化器的作用就是找到这其中最好的执行计划.MySQL使用基于成本的优化器,它将尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个.

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

统计信息不准确(InnoDB因为其MVCC的架构,并不能维护一个数据表的精确统计信息).
执行计划中的成本估算不等同于实际执行的成本.
MySQL的最优可能和你想的不一样.
MySQL从不考虑其他并发执行的查询.
MySQL也并不是任何时候都是基于成本的优化.
MySQL不会考虑不受其控制的操作的成本.如存储过程或用户自定义函数.
优化器有时候无法去估算所有可能的执行计划,所以它可能错过实际上最优的执行计划.

查询优化器的优化策略可以简单的分为两种,一种是静态优化,一种是动态优化.

静态优化可以直接对解析树进行分析,并完成优化.例如可以通过一些简单的代数变化将WHERE条件转换成另一种等价形式(如WHERE条件中带入的一些常量等.).静态优化在第一次完成后就一直有效,即使使用不同的参数重复执行查询也不会发生变化,可以认为是一种编译时优化.
动态优化则和上下文有关,也可能和很多其他因素有关,例如WHERE条件中的取值,索引中条目对应的数据行数等.这需要在每次查询的时候都重新评估.有时候甚至在查询的执行过程中也会重新优化.

静态优化只需要做一次,而动态优化则在每次执行时都需要重新评估.

MySQL能够处理的优化类型:

重新定义关联表的顺序.
将外连接转化成内连接.
使用等价变化规则.
优化COUNT(),MIN()和MAX().EXPLAIN中可以看到Select tables optimized away
预估并转化为常数表达式.
覆盖索引扫描.
子查询优化.
提前终止查询.如使用了LIMIT子句.
等值传播.
列表IN()的比较.先排序,然后二分查找.

数据和索引的统计信息

统计信息由存储引擎实现,MySQL查询优化器在生成查询的执行计划时,需要向存储引擎获取相应的统计信息.存储引擎则提供给优化器对应的统计信息,包括:每个表或者索引有多少个页面,每个表的每个索引的基数是多少,数据行和索引长度,索引的分布信息等.优化器根据这些信息来选择一个最优的执行计划.

MySQL如何执行关联查询

MySQL认为任何一个查询都是一次"关联",并不仅仅是一个查询需要要两个表匹配才叫关联.

MySQL对任何关联都执行嵌套循环关联操作,即MySQL先在一个表中循环取出单条数据,然后再嵌套循环到下一个表中寻找匹配的行,依次下去,直到找到所有表中匹配的行为止.然后根据各个表匹配的行,返回查询中需要的各个列.MySQL会尝试在最后一个关联表中找到所有匹配的行,如果最后一个关联表无法找到更多的行,MySQL返回到上一层次关联表,看是否能够找到更多的匹配记录,依次类推迭代执行.

MySQL在FROM子句中遇到子查询是,先执行子查询并将结果放到一个临时表中,然后将这个临时表当做一个普通表对待.

MySQL在执行UNION查询时也是用类似的临时表,在遇到右外连接的时候,MySQL将其改写成等价的左外连接.

执行计划

MySQL生成查询的一颗指令树,然后通过存储引擎执行完成这颗指令树并返回结果.

如果对某个查询执行EXPLAN EXTENDED后,再执行SHOW WARNINGS就可以看到重构出的查询.结果可能和原查询不完全相同,但和原查询有完全相同的语义.

MySQL执行查询的方式类似于一颗左侧深度优先的树.

关联查询优化器

关联查询优化决定了多个表关联时的顺序.通常多表关联的时候,可以有多种不同的关联顺序来获得相同的执行结果.关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序.

排序优化

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

当不能使用索引生成排序结果的时候,MySQL需要自己进行排序,如果数据量小则在内存中进行,如果数据量大则需要使用磁盘,不过MySQL将这个过程统一称为文件排序(filesort)即使完全是内存排序不需要任何磁盘文件时也是会称为文件排序.

如果需要排序的数据量小于"排序缓冲区",MySQL使用内存进行"快速排序"操作.如果内存不够排序,那么MySQL会先将数据分块,对每个独立的块使用"快速排序"进行排序,并将各个块的排序结果存放在磁盘上,然后将各个排好序的块进行合并,最后返回排序结果.

MySQL有两种排序算法:

两次传输排序(旧版本使用):读取行指针和需要排序的字段,对其进行排序,然后再根据排序结果读取所需要的数据行.产生大量随机I/O.
单词传输排序(4.1版本后使用):先读取查询所需要的所有列,然后再根据给定列进行排序,最后直接返回排序结果.如果返回的列非常多,非常大,会额外占用大量的空间.

当查询需要所有列的总长度不超过参数max_length_for_sort_data时.MySQL使用单次传输排序.

MySQL在排序时对每一个排序记录都会分配一个足够长的定长空间来存放.这个定长空间必须足够长以容纳其中最长的字符串.

在关联查询的时候如果需要排序,MySQL会分两种情况来处理这样的文件排序.

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

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

返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端.即使查询不需要返回结果集给客户端,MySQL仍然会返回这个查询的一些信息,如该查询影响到的行数.如果查询可以被缓存,那么MySQL在这个阶段也会将结果存放到查询缓存中.
MySQL将结果集返回客户端是一个增量,逐步返回的过程.例如关联操作,一旦服务器处理完最后一个关联表,开始生产第一条结果时,MySQL就可以开始向客户端逐步返回结果集了.
结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送,再通过TCP协议进行传输,在TCP传输的过程中,可能对MySQL的封包进行缓存然后批量传输.

MySQL查询优化器的局限性

MySQL的万能的"嵌套循环"并不是对每种查询都是最优的.少部分的这些查询可以通过改写查询让MySQL高效地完成工作.

关联子查询

对于WHERE条件中包含IN()的子查询语句,因为MySQL对IN()列表中的选项有专门的优化策略,MySQL会将相关的外层表压倒子查询中,它认为这样可以更高效地查找到数据行.通过EXPLAIN可以看到子查询是一个相关子查询(DEPENDENT SUBQUERY).
优化的办法是使用EXISTS()等效的改写查询来获取更好的效率.

MariaDB不存在这种问题.

UNION的限制

有时,MySQL无法将限制条件从外层"下推"到内层,这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上.
如果希望UNION的各个子句能够根据LIMIT只取部分结果集,或者希望能够先排好序再合并结果集的话,就需要在UNION的各个子句中分别使用这些子句.

索引合并优化

当WHERE子句中包含多个复杂条件的时候,MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行.

并行执行

MySQL无法利用多核特性来并行执行查询.

哈希关联

MySQL的所有关联都是嵌套循环关联.不过,可以通过建立一个哈希索引来曲线地实现哈希关联.另外,MariaDB已经实现了真正的哈希关联.

松散索引扫描

MySQL不支持松散索引扫描,也就无法按照不连续的方式扫描一个索引.通常,MySQL索引扫描需要先定义一个起点和终点,即使需要的数据只是这段索引中很少数的几个,MySQL仍需要扫描这段索引中的每一个条目.
MySQL5.0之后的版本,在某些特殊的场景下是可以使用松散索引扫描的,例如,在一个分组查询中需要找到分组的最大值和最小值.这种情况下,在EXPLAIN的Extra字段会显示"Using index for group_by",表示这里将使用松散索引扫描.也可以给前面的列加上可能的常数值来解决.
MySQL5.6之后的版本,关于松散索引扫描的一些限制会通过"索引条件下推"的方式解决.

最大值和最小值

SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = 'PENELOPE';

对于上面这条语句,因为在first_name字段上并没有索引,因此MySQL将会进行一次全表扫描.一个曲线的优化办法是移除MIN(),然后使用LIMIT来将查询重写如下:

SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY)
WHERE first_name = 'PENELOPE' LIMIT 1;

在同一个表上查询和更新

MySQL不允许对同一张表同时进行查询和更新.可以通过使用生成表的形式来绕过限制.

查询优化器的提示(hint)

如果对优化器的执行计划不满意,可以使用优化器提供的几个提示(hint)来控制最终的执行计划.

官方网址:优化器提示

优化特定类型的查询

优化关联查询

确保ON或者USING子句中的列上有索引.在创建索引的时候就要考虑到关联的顺序.两表关联时只需要在关联顺序的第二个表的相应列上创建索引.
确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列.

优化子查询

关于子查询的优化建议是:尽可能使用关联查询代替.如果使用的是MySQL5.6以后的版本或者MariaDB,那么就可以忽略这条建议.

优化GROUP BY和DISTINCT

在很多场景下,MySQL都使用同样的办法优化这两种查询.它们都可以使用索引来优化.
在MySQL中,当无法使用索引的时候,GROUP BY使用两种策略来完成:使用临时表或者文件排序来分组.对于任何查询语句,这两种策略的性能都有可以提升的地方.
如果需要对关联查询做分组,那么通常采用查找表的标识列分组的效率会比其他列更高,但是不建议使用,因为索引改变时可能会出现故障.
如果没有通过ORDER BY子句显式地指定排序列,当查询使用GROUP BY子句的时候,结果集会自动按照分组的字段进行排序.如果不关心结果集的顺序,而这种默认排序又导致了需要文件排序,则可以使用ORDER BY NULL,让MySQL不再进行文件排序.也可以在GROUP BY子句中直接使用DESC或者ASC关键字,使分组的结果集按需要的方向排序.

优化GROUP BY WITH ROLLUP

分组查询的一个变种就是要求MySQL对返回的分组结果再做一次超级聚合.优化的办法是尽可能的将WITH ROLLUP功能转移到应用程序中处理.

优化LIMIT分页

分页操作在偏移量非常大的时候(如LIMIT 1000,10),查找的代价会非常高,因为前面的9990条记录都将被抛弃.

优化的办法是尽可能地使用索引覆盖扫描配合延迟关联技巧.
或者可以通过使用书签记录上次数据的位置的方式来避免使用偏移量.如下:

SELECT * FROM sakila.rental
WHERE rental_id <16030
ORDER BY rental_id DESC LIMIT 10;

也可以使用预先计算的汇总表,或者关联到一个冗余表的方式来优化,冗余表只包含主键列和需要做排序的数据列.

优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询,因此很多优化策略在UNION查询中都没法很好的使用.经常需要手工地将WHERE.LIMIT.ORDER BY等子句"下推"到UNION的各个子查询中,以便优化器可以充分利用这些条件进行优化.
除非确实需要服务器消除重复的行,否则就一定要使用UNION ALL.