Mysql 执行计划
不管是开发、运维还是实施等岗位的同学，对于自己所接触、所编写的各种SQL语句，都应该能够进行调优，从而使自己能写出更优解的SQL语句，个人觉得更是一种必备技能。
- 如何查看各个sql语句的执行计划，是本篇文章的主题。
- Mysql查看执行计划一般是通过 explain + sql。
- 本文假设读者已经掌握mysql相关的索引知识。
先提供例子中涉及到表的建表、建索引语句。
-- 用户表 create table t_user( id int primary key, loginname varchar(100), name varchar(100), age int, sex char(1), dep_id int, address varchar(100) ); --部门表 create table t_dep( id int primary key, name varchar(100) ); --创建普通索引 mysql> alter table t_user add index idx_dep_id(dep_id); --创建唯一索引 mysql> alter table t_user add unique index uk_loginname(loginname); --创建组合索引 mysql> alter table t_user add index idx_name_age_sex(name,age,sex);
explain出来的信息有12列，分别是：
id、select_type 、table 、partitions 、type、possible_keys、key 、key_len、ref 、rows、filtered、Extra
接下来会对各个字段做对应的说明，并列举对应的例子。
-
id
每个select语句都会自动分配一个唯一的标识符
-
select_type
表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等
- SIMPLE
简单表，即不使用表连接查询
mysql> explain select * from t_user; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
- PRIMARY
主查询，即外层的查询。一个需要union或者子查询的select，位于最外层的单位查询的select_type。
mysql> explain select (select name from t_user) from t_user; +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+ | 1 | PRIMARY | t_user | NULL | index | NULL | idx_dep_id | 5 | NULL | 6 | 100.00 | Using index | | 2 | SUBQUERY | t_user | NULL | index | NULL | idx_name_age_sex | 312 | NULL | 6 | 100.00 | Using index | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
- UNION
UNION中的第二个或者后面的查询语句,union连接的两个查询语句，第一个是PRIMARY，除了第一个表外，第二个以后的表select_type都是union
mysql> explain select name from t_user union select name from t_user2; +----+--------------+------------+------------+-------+---------------+------------------+---------+------+---------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+-------+---------------+------------------+---------+------+---------+----------+-----------------+ | 1 | PRIMARY | t_user | NULL | index | NULL | idx_name_age_sex | 312 | NULL | 6 | 100.00 | Using index | | 2 | UNION | t_user2 | NULL | ALL | NULL | NULL | NULL | NULL | 9756827 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+-------+---------------+------------------+---------+------+---------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec)
- SIMPLE
-
table
输出结果集的表
-
type
表示MySQL在表中找到所需行的方式，或者叫访问类型，常见类型如下：
ALL < index < range < ref < eq_ref < const,system
从左到右，性能由差到最好
- ALL
全表扫描，MySQL遍历全表来找到匹配的行
mysql> explain select * from t_user; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
- index
索引全扫描，MySQL遍历整个索引来查询匹配的行
mysql> explain select name from t_user; +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_user | NULL | index | NULL | idx_name_age_sex | 312 | NULL | 6 | 100.00 | Using index | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
- range
索引范围扫描，常见于<、<=、>、>=、between等操作符
mysql> explain select * from t_user where dep_id > 10; +----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t_user | NULL | range | idx_dep_id | idx_dep_id | 5 | NULL | 1 | 100.00 | Using index condition | +----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.01 sec)
- ref
使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值得记录行
mysql> explain select * from t_user where dep_id = 10; +----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t_user | NULL | ref | idx_dep_id | idx_dep_id | 5 | const | 1 | 100.00 | NULL | +----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
索引idx_dep_id是非唯一索引，查询条件为等值查询条件dep_id = 10，所以扫描索引的类型为ref。ref 还经常出现在join操作中。
mysql> explain select * from t_user a join t_dep b on a.name = b.name; +----+-------------+-------+------------+------+------------------+------------------+---------+------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+------------------+------------------+---------+------------+------+----------+-------------+ | 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | | 1 | SIMPLE | a | NULL | ref | idx_name_age_sex | idx_name_age_sex | 303 | ssm.b.name | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+------------------+------------------+---------+------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
- eq_ref
类似ref，区别就在使用的索引是唯一索引，此类型通常出现在多表的join查询；简单来说，就是多表连接中使用primary key或者unique index 作为关联条件。
mysql> explain select b.id from t_user a left join t_dep b on a.dep_id = b.id; +----+-------------+-------+------------+--------+---------------+------------+---------+--------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+------------+---------+--------------+------+----------+-------------+ | 1 | SIMPLE | a | NULL | index | NULL | idx_dep_id | 5 | NULL | 5 | 100.00 | Using index | | 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ssm.a.dep_id | 1 | 100.00 | Using index | +----+-------------+-------+------------+--------+---------------+------------+---------+--------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
- const,system
单表中最多有一个匹配行，查询起来非常迅速，使用主键primary key或者唯一索引unique index进行的查询。
mysql> explain select * from t_user where loginname = 'liubei'; +----+-------------+--------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t_user | NULL | const | uk_loginname | uk_loginname | 303 | const | 1 | 100.00 | NULL | +----+-------------+--------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
类型type还有其他值，如ref_or_null（与ref类似，区别在于条件中包含对NULL的查询）、index_merge（索引合并优化）、unique_subquery（in 的后面是一个查询主键字段的子查询）、index_subquery（与 unique_subquery 类似，区别在于 in 的后面是查询非唯一索引字段的子查询）等
-
possible_keys
表示查询时可能使用的索引
-
key
表示实际使用的索引
-
key_len
使用到索引字段的长度
-
rows
扫描行的数据
-
Extra
执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。
- Using filesort
- 排序时无法使用到索引，就会出现这个，常见于order by和group by中
mysql> explain select * from t_user order by address; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using filesort | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.00 sec)
所有不是通过索引直接返回排序结果的排序都叫Filesort排序。Filesort并不代表通过磁盘文件进行排序，而只是说明进行了一个排序操作，至于排序操作是否使用了磁盘文件或临时表等，则取决于MySQL服务器对排序参数的设置和需要排序数据的大小。
Filesort是通过相应的排序算法，将取得的数据在sort_buffer_size系统变量设置的内存排序区中进行排序，如果内存装载不下，它就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集。sort_buffer_size 设置的排序区是每个线程独占的，所以同一个时刻，MySQL中存在多个 sort buffer排序区。了解了MySQL排序的方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。WHERE条件和ORDER BY使用相同的索引，并且ORDER BY的顺序和索引顺序相同，并且ORDER BY的字段都是升序或者都是降序。否则肯定需要额外的排序操作，这样就会出现Filesort。
--引自<深入浅出Mysql>18.4.3优化ORDER BY语句 章节
- 排序时无法使用到索引，就会出现这个，常见于order by和group by中
- Using index
- 查询使用到了覆盖索引，不需要回表，效率不错
- 如果同时出现了Using where ,说明索引被用来读取数据，并在server层执行查找索引键值过滤
- 如果没有出现Using where,说明索引被用来读取数据
mysql> explain select name,age,sex from t_user ; +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_user | NULL | index | NULL | idx_name_age_sex | 312 | NULL | 5 | 100.00 | Using index | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select name,age,sex from t_user where age > 10; +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t_user | NULL | index | NULL | idx_name_age_sex | 312 | NULL | 5 | 33.33 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
- Using temporary
- 说明使用了临时表存储中间结果
- 多见于对查询结果进行order by 或者group by 操作
mysql> explain select distinct a.id from t_user a,t_dep b where a.dep_id = b.id; +----+-------------+-------+------------+-------+--------------------------------------------------+------------+---------+----------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+--------------------------------------------------+------------+---------+----------+------+----------+------------------------------+ | 1 | SIMPLE | b | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using index; Using temporary | | 1 | SIMPLE | a | NULL | ref | PRIMARY,uk_loginname,idx_dep_id,idx_name_age_sex | idx_dep_id | 5 | ssm.b.id | 5 | 100.00 | Using index | +----+-------------+-------+------------+-------+--------------------------------------------------+------------+---------+----------+------+----------+------------------------------+ 2 rows in set, 1 warning (0.00 sec)
问题：如果变更条件，出现的执行计划应该是怎样的？
explain select distinct a.id from t_user a,t_dep b where a.dep_id = b.id and b.name = 'na';
有兴趣回答的读者可以评论区回复。
- Using where
- 表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤
mysql> explain select * from t_user where age > 10; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 33.33 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ mysql> explain select * from t_user where address = 'beijing'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from t_user where id in (1,2,3); +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_user | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 3 | 100.00 | Using where | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
- 表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤
- Using index condition
- 5.6.x之后支持ICP(Index Condition Pushdow)特性，可以把where条件也下推到存储引擎层，只不过where条件列需要是普通索引，不符合where条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。
- type值为range、 ref或者eq_ref时候 , 配合普通索引列添加会使用到索引条件下推技术
- 使用复合索引时，当不满足最左前缀原则时，也会使用到索引下推技术
mysql> show index from t_user; +--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t_user | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | | | t_user | 0 | uk_loginname | 1 | loginname | A | 5 | NULL | NULL | YES | BTREE | | | | t_user | 1 | idx_dep_id | 1 | dep_id | A | 1 | NULL | NULL | YES | BTREE | | | | t_user | 1 | idx_name_age_sex | 1 | name | A | 5 | NULL | NULL | YES | BTREE | | | | t_user | 1 | idx_name_age_sex | 2 | age | A | 5 | NULL | NULL | YES | BTREE | | | | t_user | 1 | idx_name_age_sex | 3 | sex | A | 5 | NULL | NULL | YES | BTREE | | | +--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 6 rows in set (0.00 sec) mysql> explain select * from t_user where id in (1,2,3) and name = 'liubei'; +----+-------------+--------+------------+------+--------------------------+------------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+--------------------------+------------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | t_user | NULL | ref | PRIMARY,idx_name_age_sex | idx_name_age_sex | 303 | const | 1 | 60.00 | Using index condition | +----+-------------+--------+------------+------+--------------------------+------------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from t_user where name = 'aa' and sex = '1'; +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | t_user | NULL | ref | idx_name_age_sex | idx_name_age_sex | 303 | const | 1 | 20.00 | Using index condition | +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
- Using filesort
今天的Mysql执行计划分析就到这了。相信掌握了上面的内容，会对自己也是一个不错的提升。
上文有误的地方，欢迎读者提出来，一起探讨。
