一文讲清，MySQL的执行计划

2021-10-29 00:00:00 索引查询数据执行计划

以下文章来源于南山的架构笔记，作者南山

搞清楚了MySQL索引的原理之后，就需要学习查询语句的执行计划和SQL调优，这块可能是MySQL实践中对开发人员为常见的一个技能了。

每次我们提交一个SQL查询语句给MySQL，他内核里的查询优化器，都会针对这个SQL语句的语义去生成一个执行计划，这个执行计划就代表了，他会怎么查各个表，用哪些索引，如何做排序和分组，看懂这个执行计划，你可能就会写出高性能的SQL语句了。

MySQL提供explain/desc命令输出执行计划，如explain select * from user;

一般，如果是一个简单的单表查询，可能执行计划就输出一条数据，如果你的SQL语句特别复杂，执行计划就会输出多条数据，因为一个复杂的SQL语句的执行会拆分为多个步骤，比如先访问表A，接着搞一个排序，然后来一个分组聚合，再访问表B，接着搞一个连接。

接下来，我们就先来研究一下这个执行计划里比较重要的字段都是什么意思。

（1）id

这个id呢，就是说每个SELECT都会对应一个id，其实说白了，就是一个复杂的SQL里可能会有很多个SELECT，也可能会包含多条执行计划，每一条执行计划都会有一个的id，这个没啥好说的。

（2）select_type

select_type说的就是这一条执行计划对应的查询是个什么查询类型

（3）table

table就是表名，意思是要查询哪个表。

（4）type

type就比较重要了，提供了判断查询是否高效的重要依据依据，一般有这几种情况：

const

假如你写一个SQL语句select * from table where id=x或者select * from table where name=x，直接就可以通过聚簇索引或者二级索引+聚簇索引查询到你要的数据，这种根据索引直接可以快速查到数据的过程，称之为const类型，意思就是常量级的性能。

所以你以后在执行计划里看到const的时候，就知道他就是直接通过索引定位到数据，速度极快。

const类型要求你的二级索引必须是索引，保证二级索引的每一个值都是的才可以。

ref

如果你对name加了一个普通的索引，不是索引，你的查询SQL像这样select * from table where name=x，它在执行计划里叫做ref，查询速度也是很快的。

如果你是包含多个列的普通索引的话，那么必须是从索引左侧开始连续多个列都是等值比较才可以是属于ref方式，就是类似于select * from table where name=xx and age=xx and sex=xx，然后索引可能是这样的INDEX(name,age,sex)。

有一种特例，如果你用name IS NULL这种语法，即使name是主键或索引，还是只能走ref方式。

总的来说，ref就是用来普通索引，或者主键/索引搞了一个IS NULL/IS NOT NULL。

range

range，顾名思义就是对一个范围查询时会走这种方式。

比如：selct * from table where age >=x and age <=x，假如age是一个普通索引，此时必然利用索引来进行范围查询，一旦利用索引做了范围查询，这种方式就是range。

index

假如有一个联合索引INDEX(x1,x2,x3)，查询语句时select x1,x2,x3 from table where x2=xxx。

估计好多同学看到这个查询语句，就会觉得x2不是联合索引里左侧的那个字段，没法走索引。

是的，这个SQL是没办法直接从联合索引的索引树的根节点开始二分查找，快速一层一层跳转的，那么他会怎么执行呢？

仔细观察会发现，要查询的3个字段，正好是联合索引的几个字段。

对于这种SQL，会遍历INDEX(x1, x2, x3)联合索引的叶子节点，也就是遍历联合索引叶子节点的数据页里的一行一行的数据，每行数据都是x1,x2,x3和主键的值。

所以此时针对这个SQL，会直接遍历INDEX(x1,x2,x3)索引树的叶子节点的那些页，一个接一个的遍历，然后找到 x2=xxx 的那个数据，就把里面的x1，x2，x3三个字段的值直接提取出来就可以了！这个遍历二级索引的过程，比不走索引直接走聚簇索引快多了，毕竟二级索引叶子节点就包含几个字段的值，比聚簇索引叶子节点少很多，所以速度也快！

也就是说，此时只要遍历一个INDEX(x1,x2,x3)索引就可以了，不需要再到聚簇索引去查找！针对这种只要遍历二级索引就可以拿到你想要的数据，而不需要回源到聚簇索引的访问方式，就叫做index访问方式！

ref_or_null

跟ref查询类似，在ref的查询基础上，会加多一个IS NULL值的条件查询。

类似于select * from table where name=xx or name IS NULL，那么此时执行计划的type就是ref_of_null。

all

all意思就是全表扫描，扫描你聚簇索引里所有的叶子节点，当然是慢的一种了。

总结：

const、ref和range，都是基于索引树的二分查找和多层跳转来查询，所以性能一般都是很高的；

index，速度就比上面三种要差一些，因为它是遍历二级索引树的叶子节点的方式来查询，那肯定比基于索引树的二分查找要慢多了，但是还是比全表扫描好一些的。

all，全表扫描是慢的一种，如果数据量大的话，应该避免这种情况出现。

（5）possible_keys

这个也挺重要的，它是跟type结合起来的，意思就是说你type确定访问方式了，那么到底有哪些索引是可供选择的，可以使用的，都会放到这里。

（6）key

就是在possible_keys里实际选择的那个索引，而key_len就是所有的长度。

（7）ref

ref就是使用某个字段的索引进行等值匹配搜索的时候，跟索引列进行等值匹配的那个目标值的一些信息。

（8）rows

预估通过索引或者其他方式访问这个表的时候，大概会读取多少条数据，是个估算值。

（9）filtered

经过搜索条件过滤之后剩余的数据的百分比。实际显示的行数 = rows * filtered。

比如执行计划的时候，扫描了1万条数据，经过索引过滤后有100条数据，那么filtered就是1%。

（10）extra

额外信息，这个字段还是挺重要的。它的值比较多，下面列举几个常见的：

using index，就是说这次查询，仅仅涉及到一个二级索引，不需要回表；

using index condiion，在二级索引里查出来的数据还会额外的跟其他查询条件做比对，如果满足条件就会被筛选出来；

using where，这个一般常见于你直接对一个表扫描，没用到索引，然后where里好几个条件，就会告诉你using where；

using join buffer，对于查出来的数据，会在内存里做一些特殊的优化，减少全表扫描次数；

using filesort，基于内存或者磁盘文件来排序，大部分时候都基于磁盘文件来排序：

using temporary，SQL会在临时表里做大量的磁盘文件操作，性能比较低；

其实，只是干巴巴的罗列出执行计划的各个字段是什么意思，实际应用的时候，还是经常不知道怎么优化SQL，下面就举几个例子帮大家更好的理解执行计划的实际应用。

执行计划实际应用分析

先来个简单的：explain select * from t1

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+| 1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 6603|   100.00 | NULL  |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

首先id是1，先不用管它，select_type是SIMPLE，就是查询类型是简单的、普通的。

table是t1，查询的表是t1。

type是all，走的是全表扫描，因为你where里没有加任何条件，只能走全表扫描了。

rows是6603，说明全表扫描到了6603条数据，此时filtered是，筛选出来的数据就是你表里数据的占比。

再看一个SQL语句的执行计划：

explain select * from t1 join t2

多表关联SQL语句的执行顺序是，先选择一个表查询出来数据，接着遍历每一条数据去另一个表里查询可以关联在一起的数据，然后关联起来，此时它的执行计划是这样的：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+|1| SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1850 |   100.00 | NULL                                  || 1| SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 6603 |   100.00 | Using join buffer (Block Nested Loop) |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+

表t1里有6549条数据，t2里有1823条数据，MySQL执行上面语句的时候做了优化，把t2作为驱动表，t1作为被驱动表。

这个执行计划的id都是1，一般来说，在执行计划里，一个select对应一个id，因为这两条直线计划对应的是一个select语句，所以他们的id都是1。

针对t2先用ALL全表扫描，扫描出了1850条数据。

然后是t1表，由于它这种表关联方式，是笛卡尔积的结果，t2表的每条数据都会去t1表里扫描所有的数据，跟t1表里的每一条数据都做一个关联，而且extra里说是Nested Loop，也就是嵌套循环的方式。

后我们再来看一个语句：

EXPLAIN SELECT * FROM t1 WHERE x1 IN (SELECT x1 FROM t2) OR x3 = 'xxxx';

它的执行计划是这样的

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+|1 | PRIMARY     | t1    | NULL       | ALL   | index_x3      | NULL     | NULL    | NULL | 3457 |   100.00 | Using where || 2 | SUBQUERY   | t2    | NULL       | index | index_x1      | index_x1 | 507     | NULL | 4687 |   100.00 | Using index |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+

因为这条SQL里有两个select，所以执行计划的条id是1，第二条id是2。

其次条执行计划的select_type是primary，不是SIMPLE了，说明个执行计划的查询类型是主查询的意思，对主查询而已它有一个where条件是x3='xxx'，搜易它的possible_keys里包含了index_x3，也就是x3字段的索引，但是它的key实际上是NULL，type是ALL，表示它后没有用到index_x3这个索引，而是走的全表扫描。

第二个执行计划的select_type是SUBQUERY，就是子查询的意思，子查询针对的是t2这个表，当然子查询本身就是一个全表查询，但是对主查询而言，会使用x1 in 这个筛选条件，他这里type是index，说明使用了扫描index_x1这个x1字段的二级索引的方式，直接扫描x1字段的二级索引，来跟子查询的结果集做比对。

总结：

执行计划能为我们调优SQL提供很多信息，不同的SQL，不同的数据量，执行计划不一样，需要具体问题具体分析。

不过，我们调优SQL的本质是不变的，就是分析执行计划哪些地方出现了全表扫描，或者扫描的数据量太大，尽可能的通过合理优化索引保证执行计划每个步骤都可以基于索引执行，避免扫描过多的数据。

相关文章