MySQL执行计划详解 #11
Description
| Column | Meaning |
|---|---|
| id | The SELECT identifier |
| select_type | The SELECT type |
| table | The table for the output row |
| partitions | The matching partitions |
| type | The join type |
| possible_keys | The possible indexes to choose |
| key | The index actually chosen |
| key_len | The length of the chosen key |
| ref | The columns compared to the index |
| rows | Estimate of rows to be examined |
| filtered | Percentage of rows filtered by table condition |
| extra | Additional information |
SQL语句优化的目标
SQL优化的指标很多，优化手段也是很多， 往往我们迷思在这些指标和优化手段里的时候，就是遗忘了SQL优化最本质的目标。SQL优化最重要的目标之一：减少数据库读取磁盘的IO次数。
场景设定
因为后面反复要用到查询来举例，所以这里设定一个场景，方便后面举例：
CREATE TABLE test_table (
id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
a int NOT NULL DEFAULT '0' COMMENT 'a',
b int NOT NULL DEFAULT '0' COMMENT 'b',
c varchar(50) NOT NULL DEFAULT '' COMMENT 'c',
d datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT 'd',
PRIMARY KEY (id),
KEY idx_a_b (a, b),
KEY idx_c_a(c, a)
) COMMENT='测试表';id字段
意为select查询的序列号，包含一组数字，表示查询中执行select字句或操作表的顺序
- id相同，顺序自然自上而下
- id全不同，如果是子查询，id的序号会递增，id数字越大越先执行
- id部分相同，先按数字大的先执行，数字相同的按自上而下顺序执行
select_type字段
查询类型，用于区别普通查询、联合查询、子查询等复杂查询
-
simple：简单的select查询，查询中不包含子查询或union；
-
primary：查询中包含子查询，最外层查询则标记为primary；
-
subquery：在select或where中包含子查询；
-
derived：在from列表中包含的子查询，也叫派生类，MySQL会递归执行这些子查询，把结果放在临时表里；
-
union：若第二个select出现在union之后，则被标记为UNION，若UNION包含在from子句的子查询中，外层select将被标记为DERIVED；
table字段
显示这一行的数据是关于哪张表，内容为表名或者别名，可能是临时表或者union合并结果集
type字段
显示访问类型，表示以何种方式访问了数据，从最好到最差的排列为：
system > const > eq_ref > ref > range > index > all
一般情况下，得保证查询至少达到range，最好能达到ref
system
表里只有一行记录，是const的特例，一般不会出现。
const
表里至多有一个匹配行。
eq_ref
主键索引（primary key）或者非空唯一索引（unique not null）等值扫描
ref
非主键非唯一索引等值扫描。
range
利用索引查询的时候限定了范围，一般出现于between <（<=） > (>=) in 等查询
index
全索引扫描。
index说明查询在做全索引扫描，这种情况只比全表扫描要稍微好一点，相当于是逐条遍历数据，只不过是遍历的索引。
这种情况，应该已经是慢查的范围， 我们在优化中需要避免，不应该出现需要全索引扫描的情况。
all
全表扫描。
说明查询没有命中任何索引，必须扫描全表逐条数据过滤。
这种情况基本上是不可接受的，必须要优化。
index_merge
索引合并。
这是一种很有意思的类型，出现在多条件查询的场景下。
举个例子：
select * from test_table where a=1 and c='test'在我们不知道数据分布的情况下，这行查询语句有可能选中索引idx_a_b，也有可能选中idx_c_d。还有一种可能，优化器觉得这两个索引都不太理想，也有可能分别使用idx_a_b和idx_c_d扫描获得两个结果集，然后取交集，这就是index_merge。
除了出现在and条件，or条件也同样会出现。
为什么优化器会选择索引合并，个人觉得还是和优化器判断需要回表扫描的行数有很大关系，回表的行数在优化器的决策中占了很大的比重，为了减少回表的行数，甚至会不惜多扫描几个索引。
但是索引合并并不一定能获得更好的性能，偶尔也会出现比使用单一索引性能更差，这是因为多次扫描索引在拖慢查询速度。
possible_keys字段
本次查询可能用到的key，查询中涉及到的字段，如果存在于索引中，那么这个索引就是本次查询可能用到的索引，所有可能用到的索引组成当前这个字段。
但是这些索引最终只会有一个被用到，或者一个都不会被用到。
key字段
本次查询实际用到的索引，如果是空，说明没有用到索引。没有用到索引就是个很坏的情况，需要具体分析优化器为什么不选择使用索引。
Tip
偶尔我们会遇到一种情况，就是我们在测试环境执行sql语句能正确的命中索引，但是到了生产环境却无法命中我们目标的索引，可能优化器选择了其他索引，或者直接选择了全表扫描。这种情况很有可能是测试环境和生产环境数据差异过大，或者数据量差异太大导致优化器的选择产生了不同，我们可以按照下面的步骤来解决：
1、仔细分析生产环境数据，特别是索引字段，重点关注字段值的分布情况，尽量在测试环境模拟出这种分布情况；
2、在测试环境模拟出和生产环境接进的数据量，如果生产环境的表巨大，可以在执行完第一步后先看看SQL执行的行为和生产环境是否一致，如果不一致，再逐步增加数据量，反复验证；
key_len字段
当前查询命中的索引中使用的字节数。
如果是联合索引，通过key_len字段可以很清晰的看出来查询用到了索引的多少个字段。
在不考虑其他影响因素的情况下，索引长度越短，索引的效率越高。这是因为索引索引长度越短，需要进行的读操作也就越少，查询主要的耗时就是来源于读操作的I/O耗时。
但是key_len不用作为优化的主要方面，首先是设计合理的索引，当多个索引其他方面差异不大，或者key_len差异非常大的，再考虑key_len指标也不迟。
查询1
select * from test_table where c='test_a' and a=0
此时很明显会命中idx_c_a这个索引，那么索引长度就是a,c两个字段的长度：50*4+2+4，varchar一个字符占4个字节，而且由于varchar是变成字段，所以需要额外2个字节记录长度信息，bigint占8个字节，所以key_len=206。
查询2
select * from test_table where c='test_a'
此时也会命中idx_c_a这个索引，但是根据最左匹配原则，只会用到字段c，所以索引长度也是c字段的长度：50*4+2=202，所以key_len就是202。
Tip
索引覆盖的情况下，explain给出的key_len只包含了过滤数据使用到的字段长度，不包含回收字段的部分，这里要注意，所以覆盖还是以extra字段中的Using index为准。
ref字段
显示索引的哪一列被使用了，如果可能的话，是一个常数
rows字段
查询扫描的行数。
根据表的统计信息及索引选用情况，大致估算出的当前查询需要扫描的行数。这个数字虽然有零有整，但是实际上是个估算出来的数值，并不是直接执行查询的结果。
Important
rows是非常重要的指标之一，rows如果非常大，索引当前查询mysql要扫描的数据非常多，扫描的数据越多，查询的效率一定越慢。rows字段值非常大时需要结合filtered字段来看：如果filtered也很大，说明查询的结果集就很大，需要从减小查询的结果集入手来优化；如果filtered很小，说明当前使用的索引没能很好的分离出结果集，需要做大量的回表来过滤数据，需要从优化索引的角度来入手。
filtered字段
filtered是结果集行数和扫描行数的比值的百分数，所以filetered的最大值是100，这个值是越大越好，值越大代表索引越精确。
举几个例子：
1、如果我们使用主键等值查询，那么结果集是1行，由于可以通过聚簇索引直接找到这行记录，所以总共只需要扫描1行，filtered就会是100；
2、如果我们一个查询的结果集是10行，为了查到这10行数据，mysql扫描了100行数据，那么filtered值就是10。
Important
filtered是非常重要指标之一，如果filtered非常小，表示当前查询使用的索引，能过滤掉的数据非常少，必须要通过回表的方式来过滤掉大量的数据，要考虑当前索引是不是不能很好的匹配查询条件，要考虑优化索引或者查询条件。
extra字段
额外信息，无法在一个字段中概述但是依旧重要的信息。
Using index
代表使用了覆盖索引。表示当前的查询语句只需要使用命中的索引就可以完成查询，不需要额外的回表操作。
使用到覆盖索引一定是个好兆头，但是也有一些问题要注意：
覆盖索引的条件
只有当查询回收的字段和搜索条件字段全都包含在索引内时，才能走覆盖索引。
除了索引字段外，我们额外回收主键字段，也能走覆盖索引，因为主键字段就保存在非聚簇索引的叶子节点上，不需要额外回表。
如果搜索条件使用了非等值查询，那么即使所有字段全都包含在索引内，也不一定能走覆盖索引。
覆盖索引并不一定等于效率高
例如：select id, a, b from test_table, 执行计划会提示Using index使用了覆盖索引，但是这条sql语句效率高吗，不见得，回收的结果集过大也一定会影响sql效率。
Tip
在实操中没必要特意去追求覆盖索引，例如为了覆盖索引专门去增加一些字段进索引，这个就需要认真权衡是否划算，一般场景下，如果回收的结果集不大，回表不会是瓶颈所在。
Using where
说明where条件没有全都包含在索引内，在使用索引筛选完数据后，还需要再次回表筛选其他的where条件。
这种情况是经常会发生的，需不需要优化要看具体情况，如果索引筛选完的结果集很小，那么回表的成本也会比较小；如果结果集很大，那么回表的成本也会高，就要考虑优化了。
Using index condition
这个提示是花了我挺多时间研究的一个提示，网络上的大部分资料讲的都不是很清楚，现在尝试来讲清楚这个提示。
这个提示通常在使用非聚簇索引的非等值查询的情况下可能会出现，那它表示的是啥意思呢？
它的意思是，凭借索引无法准确的命中记录，还需要根据where条件额外的扫描一些记录才能最终确定结果集，为啥会这样，举个例子来说明：
还是上面的表：test_table，索引是idx_a_b(a, b)，假设a,b都是int型。
等值查询
如果是等值查询：select a, b from test_table where a=10 and b=20
根据最左匹配原则，引擎会先从索引树找a=10的节点，因为是等值，所以我们可以利用索引树的结构准确的找到a=10的节点；那么以此为起点，再匹配b=20的节点，因为在a字段值确定时，b字段是有序的，所以我们查找b=20的节点时仍然可以利用索引树的结构来查找，就可以准确的找到等值的数据，同时保证每一条读到的数据都是我们的目标数据。
非等值查询
非等值查询有多重情况，例如范围查询或者模糊查询，以范围查询举例。
如果是范围查询：select * from test_table where a=10 and b>20 and b<30
因为a字段还是等值查询，根据最左匹配原则，引擎还是会先利用索引树的结构准确的找到a=10的节点，但是在处理b字段时有所不同了。
由于b字段并不是等值查询，所以没有办法再利用树结构准确的找到符合b字段条件的节点，所以只能轮询去找当前节点下的子节点去寻找符合b字段条件的节点，此时就有可能读取一些并不符合条件的记录，就会出现Using index condition提示。意思就是虽然查询使用了索引，但是仍然需要过滤一些额外的数据来圈定查询的结果集。
ICP
ICP全称是Index Condition Pushdown，是MySQL5.6开始支持的一种查询优化技术。
在ICP出现之前，如果出现上述场景的非等值查询，在引擎层只能用到a字段过滤数据，引擎将拿到的数据返回给server层，服务层再通过回表的方式，去过滤b字段，此时就会出现using where的提示。
当使用了ICP优化之后，server层会将b字段的过滤也交给innodb，innodb在索引中直接做过滤，这个优势是显而易见的，减小了需要回表的数据集大小。
ICP并不是只能作用在范围查询，模糊查询也同样可以生效，只是字段是否匹配的判断方式变了。
当使用到了ICP特性，并且确实通过ICP特性能过滤掉一些数据，我们就会在执行计划中收到Using index condition的提示。
一些有趣的现象
b字段查询范围的影响
按照非等值查询设定的场景，如果当a=10过滤出来的数据，b字段的值全都落在20到30之间，我们会发现Using index condition提示没有了。
这里我没有找到非常准确的解释，但是按照我的理解，这种情况下，虽然引擎扫描了所有a=10的索引数据，但是并没有扫描任何一条额外的数据，也就是没有扫描不会被加入结果集的数据，所以mysql认为并没有额外的开销产生。
覆盖索引的影响
按照非等值查询设定的场景，我们把查询的字段改成只查询id, a, b三个字段，然后我们发现Using index condition变成了
Using index。
这里我也没有非常准确的找到答案，我认为是mysql是发现这里已经是覆盖索引了，不会再涉及后面回表的操作了，所以没有必要使用ICP了，所以直接取回数据在server层处理，当然这只是猜测。
Using filesort
文件排序意为MySQL无法利用索引进行排序，而使用了外部的排序算法，常见于order by 或 group by
Using join buffer
使用了连接缓存
impossible where
where语句结果是false
select tables optimized away
在没有group by字句情况下，基于索引优化操作或对于MyISAM存储引擎优化COUNT（*）操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
distinct
优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作
Backward index scan
使用索引进行倒序的扫描。
Using temporary
使用了临时表保存中间结果，查询完成后临时表删除