在MySQL中,查询优化器会对用户提交的SQL语句进行调整优化并最终生成执行计划。具体地,可通过Explain命令来进行查看
基本使用
关于Explain命令的使用方法,在前面已经多次出现了。直接在我们的SQL语句前添加explain,即可查看该SQL语句的执行计划。下面即是一个查看select语句执行计划的示例。事实上,Explain命令亦可用于delete、insert、update等其它类型的SQL语句上,只不过日常我们更关心查询语句的执行计划
字段释义
上面,我们知道了如何查看执行计划。现在我们来了解下查询语句的执行计划中各字段的含义
id字段
对于查询语句而言,在执行计划中其会对每一个select关键字分配一个唯一的id值
- 单表查询
例如下面是一个单表查询的select语句
1 | explain select * from access_method4; |
由于是对单表的查询,所以执行计划中只有一条记录,符合预期
- 连接查询
对于下面的连接查询而言,其涉及到对两张表的查询
1 | explain |
从执行计划的结果中可以看出,虽然有两条记录。但是由于只含一个select关键字,故两条记录均使用同一个id值
Note
对于连接查询的执行计划而言,前面的记录用于表示驱动表,后面的记录用于表示被驱动表
- 子查询
对于一个SQL查询语句而言,其可能会有不止一个select关键字。典型地,有所谓的子查询
1 | explain |
从下图的执行计划中可以看出,由于存在两个select关键字,故两条记录的id值不一样
值得一提的是对于子查询而言,查询优化器有时候会将其优化为连接查询(准确地说,是semi-join半连接)。这样我们在查看它的执行计划时即会发现各记录的id值是一样的
- Union联合查询
在Union联合查询中,其也会存在多个select关键字。例如下面的SQL语句
1 | explain |
结果符合预期,两条记录的id值不一样
现在我们再来看另外一个联合查询。与上面SQL不同的是,由于其没有使用all关键字,故该联合查询需要对重复的记录进行去重
1 | explain |
现在,我们来看看该联合查询的执行计划。可以看出,其多了一条记录且id值为NULL。原因在于MySQL为了对两条select命令的结果进行去重合并,需要建立一个名为 <union1, 2>的临时表。所以这第三条记录的id值为NULL
select_type字段
从上面的测试结果可以看到,执行计划中会生成各数据表相应的记录。而select_type字段即表示对数据表进行select操作的类型。下面介绍几种常见的类型
- SIMPLE
简单select,即未使用UNION或子查询。比如对于单表查询、连接查询,其就是SIMPLE类型
- PRIMARY
对于含联合查询或子查询的SQL语句而言,其会有多个select关键字。则SQL语句中第一个(即最左边)的select类型就是PRIMARY
- UNION
对于联合查询而言,除了SQL语句中第一个select类型为PRIMARY,SQL语句中的其余select类型即为UNION
- UNION RESULT
对于联合查询而言,如果需要对联合查询的结果集进行去重,则其对临时表的select类型即为UNION RESULT
- SUBQUERY
对于子查询而言,如果查询优化器无法将其优化为semi-join半连接查询,且该子查询为不相关子查询。则该子查询中第一个select的类型即为SUBQUERY
- DEPENDENT SUBQUERY
对于子查询而言,如果查询优化器无法将其优化为semi-join半连接查询,且该子查询为相关子查询。则该子查询中第一个select类型即为DEPENDENT SUBQUERY
- DEPENDENT UNION
对于一个通过union连接、内部含有多个select的子查询而言,且子查询内部各select语句均与外层查询相关时,则该子查询除了第一个select类型为DEPENDENT SUBQUERY,其余select类型均为DEPENDENT UNION。SQL语句示例如下所示
1 | explain |
执行计划如下所示,符合预期
- DERIVED
对于采用物化的方式执行的包含派生表的查询,该派生表对应的子查询的select类型即为DERIVED。示例SQL如下所示
1 | explain |
执行计划如下所示,符合预期
table字段
该字段即是执行查询时所作用的数据表的表名
type字段
关于type字段,其表示的就是进行查询时所使用的访问方法。之前我们已经介绍MySQL进行单表查询时一些常用的Access Method访问方法了。这里我们再对其他常见的访问方法进行补充说明
- system
前面我们提到MySQL会对表中的记录数进行统计。在表中只有一条记录的情况下,此时查询的访问方法即为system。需要注意其有一个前提条件,即要求存储引擎对表中记录数的统计必须是准确值,而不能是估计值。换言之,该访问方法对InnoDB存储引擎不适用,适用于MyISAM、Memory等存储引擎。下面的SQL语句,即建立了一个基于MyISAM的数据表并向其中插入一条记录
1 | -- 创建基于MyISAM的数据表 |
结果如下,符合预期
- fulltext
使用全文索引
- unique_subquery
有时候查询优化器会对in子查询进行优化进而转换为exists子查询,例如下面的SQL使用了in子查询,其中access_method3的主键为id字段
1 | explain |
现在我们将其转换为exists子查询,可以看到现在对于exists子查询而言,可以利用access_method3表的主键(聚簇索引)进行等值查询。这也是为啥查询优化器要进行如此优化的原因所在
1 | explain |
故当将查询优化器将in子查询优化为exists子查询时,如果子查询可以利用主键索引进行等值查询,则该子查询的访问方法即为unique_subquery。现在我们查看上文in子查询的执行计划
- index_subquery
其与unique_subquery类似,只不过其是在查询优化器将in子查询优化为exists子查询的条件下,子查询可以利用二级索引进行等值查询时的访问方法。例如下面的SQL语句,其中对access_method3表的name字段建立了一个二级索引
1 | explain |
执行结果如下,符合预期
possible_keys字段
故名思义,该字段只是列出该查询可以利用的索引。需要注意的是该字段所列出的索引越多,则查询优化器在计算、比较各查询方案成本时所需的时间也就越多
key字段
前面我们提了possible_keys字段只是给出在查询过程可以利用的索引,而实际真正地使用哪些索引进行查询则体现在key字段中
key_len字段
该字段意为当使用索引进行查询时,该索引的最大长度字节数。具体地:
- 对于固定长度类型的索引字段而言,则其占用的最大长度就是该固定值
- 对于指定字符集的变长类型的索引字段而言,例如对utf8字符集下的varchar(10)而言,则其所占用的最大长度为3*10=30个字节
- 对于变长类型的索引字段而言,其还需占用2个字节用于存储变长类型的实际长度
- 若该索引字段允许NULL值,则其key_len值比不允许NULL值时多1个字节
说了这么多,那这个字段到底有什么用呢?为此,我们先来建立一张表并插入一些数据。其中该数据表使用utf8字符集
1 | create table user_info( |
好了,现在我们查看下面的SQL查询语句的执行计划
1 | explain |
执行计划中的key_len值为33,为什么呢?其实也很好理解。首先对于上面的SQL语句而言,如果使用index1索引,最多也只会用到name索引字段,则此时该索引记录的最大长度即为10*3 + 2(变长类型) + 1(允许为NULL) = 33
如果我们同时使用index1索引中的name、sex索引字段是什么效果呢
1 | explain |
可以看到此时该key_len值变为66了。由于name字段与sex字段的类型在数据表的定义中完全一致,故此处就不再具体解释这个66是怎么计算出来的,可以参照上文对name字段的计算过程
至此,相信大家应该知道key_len字段的用处了。通过该字段即可让用户知道,在通过索引查询时到底使用了联合索引的几个索引字段
ref字段
该字段表示索引字段进行等值查询过程中”值”的类型。例如对于下面的SQL查询语句而言
1 | explain |
这里对索引index1的name索引字段进行了等值查询,其中值为’aaron’。即是一个常数。从执行计划中我们可以看到其ref字段为const,即常数
此外该字段还可以是某个列的列名。例如对于下面的SQL语句而言,在对被驱动表user_info进行单表查询时,是以tagList表中记录的tag字段作为等值查询时的值
1 | explain |
则从执行计划中我们可以看到,结果符合预期
此外该字段的值还可以是func,即等值匹配的类型是一个函数。SQL实例如下所示
1 | explain |
执行计划结果如下,符合预期
rows字段
该字段表示预计查询需要扫描的记录数(行数)。具体地,当采用全表扫描的方式进行查询时,其指的就是数据记录的行数;当通过索引进行查询时,其指的是索引记录的行数
filtered字段
该字段是一个以百分比%为单位的估计值,其表示的是 通过查询条件过滤后符合要求的记录数 占 预计扫描的记录数(即row字段值) 的百分比。换言之,预计结果记录数量为 filtered值 / 100 * rows值
这里向user_info数据表插入一些数据,具体结果如下。其中name字段上建立了一个二级索引index1
示例SQL语句如下
1 | explain |
从执行计划中我们可以看出,符合上述查询要求的记录数的估计值为 6 * 28.57% = 1.7142,通过观察我们可以看到真正满足查询要求的记录数是2条。这也进一步执行计划中的结果只是一个估计值,而非精确值
Extra字段
该字段是对执行计划信息的补充和完善,这里就一些常见的补充信息进行说明
No tables used
没有数据表,即未使用from子句。示例SQL如下所示
1 | explain select 1; |
结果如下,符合预期
Impossible WHERE
where子句中条件恒为false。示例SQL如下所示
1 | explain |
结果如下,符合预期
Zero limit
limit子句的参数为0,即查询语句返回的记录数为0条。示例SQL如下所示
1 | explain select * from user_info limit 0; |
结果如下,符合预期
Using index
出现索引覆盖,即不需要进行回表操作。为了方便演示,这里先建立一张表,并在name、age字段上建立了一个联合索引index1
1 | create table staff( |
现在我们就来写一个发生索引覆盖情况的SQL,示例如下
1 | explain |
执行计划如下所示,符合预期
相应的,如果我们的索引index1不是一个覆盖索引时,既不会存在该补充信息,示例SQL如下
1 | explain |
从下面的执行计划可以看到Extra为NULL,符合预期
Using index condition
对于利用二级索引进行查询的过程我们已经很熟悉了,基本是先利用 索引字段的查询条件 获取 二级索引记录,然后再通过回表操作获取完整记录。那问题来了,对于存在一些无法使用索引的索引字段的查询条件。该何时使用呢?具体地,即是在获得二级索引的记录后使用该查询条件进行过滤,还是在回表操作后获取到完整的记录时使用该查询条件进行过滤呢?在MySQL 5.6之前,对于该查询条件的使用时机是在回表后获取到完整记录的时候。而在MySQL 5.6中引入了 Index Condition Pushdown(ICP, 索引条件下推) 特性,该优化将 索引字段的查询条件 的使用时机放在获取到二级索引的记录时,此举大大减少了不必要的回表操作。故如果某个查询语句使用了ICP特性,这即会出现补充信息Using index condition
这么说可能有点抽象,下面通过具体的实例作进一步的解释。其中,name字段上存在一个二级索引index1。可以看到对于查询条件 name>’aa’ 而言,是可以利用到索引index1。但对于查询条件 name like ‘%ron’,由于是前缀匹配,故该索引字段的查询条件无法使用索引。但MySQL可以先利用查询条件name > ‘aa’在索引index1的B+树中获取符合条件的记录,然后再对这些二级索引的记录进一步使用 name like ‘%ron’ 条件进行过滤,最后再利用二级索引记录中主键进行回表获取完整的用户记录
1 | explain |
执行计划结果如下所示,符合预期
Using where
在全表扫描时或二级索引回表后,使用where子句中的查询条件进行过滤。SQL示例如下所示,其中name字段上建有二级索引index1
1 | explain |
执行计划的结果如下,符合预期
Not exists
对于外连接查询而言,如果在where子句要求被驱动表中某个不允许为NULL字段的值为NULL。则会在被驱动表执行计划的Extra字段中出现Not exists信息。因为在此种场景下,驱动表中的记录只有在被驱动表中无法匹配到记录时,才会被加入到最终的结果集。换言之,对于驱动表中的某条记录A1来说,如果其通过on子句在被驱动表中找到一条匹配的记录后,即可提前停止对A1记录的匹配。因为A1记录肯定不会被加入到最终的结果集中。显然通过提前停止匹配的操作,大大减少不必要的性能浪费
对于下面的左外连接查询SQL语句而言,除staff表的id字段不允许为NULL外,其余字段均可为NULL
1 | explain |
执行计划的结果如下,符合预期
Using intersect、Using union、Using sort_union
我们在介绍单表查询的Access Method访问方法时,介绍过一种同时使用多个索引的访问方法——index merge。在该访问方法下,具体有Intersection、Union、Sort-Union等算法。故当执行计划的type字段值为index merge时,Extra字段即会给出具体算法的补充信息。对应于上面提到三种不同算法,分别为Using intersect、Using union、Using sort_union。并在后面的括号中会给出涉及到的索引名称
这里以Using intersect为例进行介绍,首先建立如下的数据表并插入一些数据
1 | create table access_method4 ( |
则SQL查询的示例如下所示
1 | explain |
则执行计划的结果如下所示
Using filesort
众所周知,可以利用索引直接实现某些排序需求。例如对于下面的SQL而言,其在name、age字段上建立了一个联合索引
1 | explain |
则其执行计划如下所示,Extra字段值空空如也
但大多数时候我们无法利用索引直接实现有序,而只能在内存或磁盘中进行排序。前者内存中适用于记录较少时,后者磁盘中适用于记录较多时。不论是在内存中还是在硬盘中实现排序,在MySQL下均被称作file sort文件排序。例如对于下面的SQL语句而言,即无法通过索引直接实现排序,而只能通过file sort排序实现
1 | explain |
执行计划结果如下,符合预期
Using temporary
当MySQL进行去重、排序、分组等操作时,可能需要在内部建立临时表来完成。值得一提的是,由于建立、维护临时表的成本并不低,必要时可以通过索引来避免MySQL使用临时表。示例SQL如下所示
1 | explain select distinct salary from staff; |
执行计划结果如下所示
参考文献
- MySQL是怎样运行的