你好，我是leo。平时使用MySQL的过程中，多多少少都会用到执行计划，看看SQL是按什么计划执行的，有没有用到索引。但是执行计划的输出内容很多，以前对它的认识只有一知半解。今天，leo结合本地数据的实验结果，对执行计划做一个全面总结。
执行计划的内容
查看执行计划的方式大家一定不会陌生，使用explain关键字执行这样的一条SQL语句即可查看执行计划：
explain select * from user
id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra|
--+-----------+-----+----------+----+-------------+---+-------+---+-----+--------+-----+
1|SIMPLE |user | |ALL | | | | |10150| 100.0| |
执行计划展示的结果是一个二维表。
如果用 explain format=json，可以展示json格式的更多详细信息。
explain format=json select * from user
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1039.25"
},
"table": {
"table_name": "user",
"access_type": "ALL",
"rows_examined_per_scan": 10150,
"rows_produced_per_join": 10150,
"filtered": "100.00",
"cost_info": {
"read_cost": "24.25",
"eval_cost": "1015.00",
"prefix_cost": "1039.25",
"data_read_per_join": "14M"
},
"used_columns": [
"id",
"user_name",
"password",
"user_type",
"enabled",
"real_name",
"tel",
"create_date"
]
}
}
}
准备工作
因为不同版本的MySQL对SQL的执行计划有差异，本文使用的MySQL版本为8.0.30。
为便于解释执行计划对不同的SQL的分析结果，首先建两张表，下文的所有SQL都是基于这两张表。
用户表：user
CREATE TABLE `user` (
`id` bigint NOT NULL,
`user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '' COMMENT '用户名',
`password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '密码',
`user_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户类型',
`enabled` int DEFAULT NULL COMMENT '是否可用',
`real_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '真实姓名',
`tel` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '联系电话',
`create_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_user_name_IDX` (`user_name`) USING BTREE,
KEY `user_tel_IDX` (`tel`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户表';
用户表有两个二级索引，分别用user_name和tel字段建立。
用户表初始化了1w条数据。
用户属性表：user_prop
CREATE TABLE `user_prop` (
`id` bigint NOT NULL,
`prop_id` int DEFAULT NULL COMMENT '属性id',
`prop_desc` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '属性描述',
`user_id` bigint NOT NULL COMMENT '用户id',
`user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名称',
`create_date` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `user_prop_user_id_IDX` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户属性表';
用户属性表有一个二级索引，用user_id字段建立。
用户属性表初始化了2w+条数据。
用户表和用户属性表是一对多的关系，用户属性表冗余存储用户表的user_id和user_name字段。
执行计划各字段含义
执行计划包括以下字段：
- id
id是执行计划中每个select/insert/update/delete语句的唯一标识符。
有多个select关键字，就会有多个id
explain select c from( select count(*) c from user where user_name='leo') a;
id|select_type|table |partitions|type |possible_keys |key |key_len|ref |rows|filtered|Extra |
--+-----------+----------+----------+------+------------------+------------------+-------+-----+----+--------+-----------+
1|PRIMARY |<derived2>| |system| | | | | 1| 100.0| |
2|DERIVED |user | |ref |user_user_name_IDX|user_user_name_IDX|403 |const| 1| 100.0|Using index|
id值大的先执行，id值小的后执行。
但是对于join连接，会有两条同样id值的记录，这两条记录为一组，执行顺序是从上往下，上面的是join的驱动表的执行计划，下面的是被驱动表的执行计划。
explain select * from user join user_prop on user.id=user_prop.user_id ;
id|select_type|table |partitions|type |possible_keys |key |key_len|ref |rows |filtered|Extra|
--+-----------+---------+----------+------+---------------------+-------+-------+-------------------------+-----+--------+-----+
1|SIMPLE |user_prop| |ALL |user_prop_user_id_IDX| | | |19656| 100.0| |
1|SIMPLE |user | |eq_ref|PRIMARY |PRIMARY|8 |leotest.user_prop.user_id| 1| 100.0| |
- select_type
select_type表示每个select/insert/update/delete语句的类型。
insert/update/delete语句对应的值分别为insert/update/delete，而select语句对应的值有12种，描述了具体是什么类型的查询。
| 字段值 | 含义 |
|---|---|
| SIMPLE | 简单查询（没有使用UNION和子查询） |
| PRIMARY | 包含union或者子查询时，最外层的SELECT为PRIMARY |
| UNION | 在union中的第二个和之后的select |
| DEPENDENT UNION | 在union中的第二个和之后的select，依赖外部的查询 |
| UNION RESULT | 从union创建的临时表获取结果 |
| SUBQUERY | 子查询的第一个select |
| DEPENDENT SUBQUERY | 子查询的第一个select，依赖外部的查询 |
| DERIVED | 使用派生表的子查询 |
| DEPENDENT DERIVED | 依赖其他表的派生表 |
| MATERIALIZED | 使用物化表的子查询 |
| UNCACHEABLE SUBQUERY | 非缓存的子查询 |
| UNCACHEABLE UNION | 非缓存的union |
select_type的值在本文的下一小节展开描述。
-
table
当前查询所涉及的表。可以是真实的表名，也可以是在查询过程中产生的匿名临时表。 -
partitions
当前查询所涉及的分区。 -
type
访问表或者连接表的类型。性能从高到低共14种方式。
| 字段值 | 含义 |
|---|---|
| system | 表中只有一条记录。 |
| const | 用主键或者唯一索引，与常数做等值查询，最多只有一行结果 |
| eq_ref | 在多表连接查询时，使用主键或唯一索引字段连接表 |
| ref | 用二级索引字段与常数做等值查询 |
| fulltext | 全文索引 |
| ref_or_null | 用二级索引字段与常数做等值查询，并且允许该索引字段的值为NULL |
| index_merge | 使用索引合并的方式查询表。包括Intersection、Union、Sort-Union这三种索引合并方式。 |
| unique_subquery | 在in的相关子查询中可以用主键进行等值查询。如：value IN (SELECT primary_key FROM single_table WHERE some_expr) |
| index_subquery | 在in的相关子查询中用二级索引进行等值查询。如：value IN (SELECT key_column FROM single_table WHERE some_expr) |
| range | 使用索引查询给定范围的数据 |
| index | 扫描某个索引的全部记录 |
| all | 全表扫描 |
type的值在本文的下一小节展开描述。
- possible_keys
可能使用到的索引。 - key
实际使用的索引。 执行计划预估使用不同索引花费的成本，并选出执行当前SQL成本最小的索引。 - key_len
索引的长度。索引的长度越短，查询时的效率越高。 - ref
用来表示哪些列或常量被用来与key列中的索引进行比较。 - rows
需要扫描的行数，这个行数不是精准的数据，而是表的统计信息中的行数的粗略值。 - filtered
过滤掉的行数占扫描行数的百分比。该值越大，表示查询结果越准确。 - Extra
表示其他额外的信息。官方文档共有37个Extra的值，包含空表、空值、空记录等边界条件，以及子查询优化策略、连接查询优化策略、是否回表、是否使用临时表等执行计划的细节信息。leo选择了一些常见的值，在本文的下一节详细说明。
select_type的具体含义
select_type的值共有12种，比较常见的有：
- SIMPLE
SIMPLE表示简单查询。例如，下面的连接查询是两个为一组的简单查询
explain select * from user join user_prop on user.id=user_prop.user_id ;
id|select_type|table |partitions|type |possible_keys |key |key_len|ref |rows |filtered|Extra|
--+-----------+---------+----------+------+---------------------+-------+-------+-------------------------+-----+--------+-----+
1|SIMPLE |user_prop| |ALL |user_prop_user_id_IDX| | | |19656| 100.0| |
1|SIMPLE |user | |eq_ref|PRIMARY |PRIMARY|8 |leotest.user_prop.user_id| 1| 100.0| |
- PRIMARY
包含union或者子查询时，最外层的SELECT为PRIMARY。
explain select * from user where user_type=20 union select * from user where user_type=30;
id|select_type |table |partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra |
--+------------+----------+----------+----+-------------+---+-------+---+-----+--------+---------------+
1|PRIMARY |user | |ALL | | | | |10150| 10.0|Using where |
2|UNION |user | |ALL | | | | |10150| 10.0|Using where |
|UNION RESULT|<union1,2>| |ALL | | | | | | |Using temporary|
这个SQL的union前半部分的查询select_type即为PRIMARY，后半部分的查询select_type为UNION。union去重的方式是用内部临时表，把两部分查询的结果放在内部临时表中去重得到最终的结果，因此第3行执行计划的select_type为UNION RESULT。
-
UNION
在union查询中的第二个和之后的select。例子如上。 -
UNION RESULT
从union创建的临时表获取结果。例子如上。 -
DERIVED
DERIVED表示使用派生表的子查询。派生表是在from关键字之后的独立子查询的结果，是一个内部临时表。
explain select * from (select user_id,count(*) from user_prop where prop_id=20 group by user_id) t;
id|select_type|table |partitions|type |possible_keys |key |key_len|ref|rows |filtered|Extra |
--+-----------+----------+----------+-----+---------------------+---------------------+-------+---+-----+--------+-----------+
1|PRIMARY |<derived2>| |ALL | | | | | 1965| 100.0| |
2|DERIVED |user_prop | |index|user_prop_user_id_IDX|user_prop_user_id_IDX|8 | |19656| 10.0|Using where|
在这个查询中，select user_id,count(*) from user_prop where prop_id=20 group by user_id是一个独立子查询，它是外层查询的数据源，它的查询结果放在内部临时表中，称为派生表。
- MATERIALIZED
MATERIALIZED表示使用物化表的子查询。物化表是在where关键字之后的子查询的结果，是一个内部临时表。
来看一个in查询：
explain select * from user where user_name in(select user_name from user_prop)
id|select_type |table |partitions|type |possible_keys |key |key_len|ref |rows |filtered|Extra |
--+------------+-----------+----------+------+-------------------+-------------------+-------+----------------------+-----+--------+-----------+
1|SIMPLE |user | |ALL |user_user_name_IDX | | | |10150| 100.0|Using where|
1|SIMPLE |<subquery2>| |eq_ref|<auto_distinct_key>|<auto_distinct_key>|403 |leotest.user.user_name| 1| 100.0| |
2|MATERIALIZED|user_prop | |ALL | | | | |19656| 100.0| |
id为2的步骤，是将select user_name from user_prop这个子查询的结果用物化表的方式缓存起来，然后和user表做join连接，id为1的两条数据表示join查询，user表为驱动表，id为2的子查询结果为被驱动表。
之所以会出现MATERIALIZED，是因为查询优化器对这个in的子查询做了优化，把子查询转换为join连接，可以进一步使用join连接的优化手段提高查询效率，如：比较所连接的两个表哪个更合适作为驱动表，是否可以使用join buffer，是否可以使用hash join等等。
- SUBQUERY
SUBQUERY表示子查询的第一个select。
explain select * from user where user_name not in(select user_name from user_prop)
id|select_type|table |partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra |
--+-----------+---------+----------+----+-------------+---+-------+---+-----+--------+-----------+
1|PRIMARY |user | |ALL | | | | |10150| 100.0|Using where|
2|SUBQUERY |user_prop| |ALL | | | | |19656| 100.0| |
这个SQL中，leo把上述第6点MATERIALIZED的in查询改为not in查询。第二行select_type变成了SUBQUERY，表示select user_name from user_prop是一个独立子查询。用not in的条件，查询优化器无法将子查询物化之后再和user表连接，但其实子查询还是使用了物化表，只不过没有在select_type中体现出来。我们只需在explain的后面加上FORMAT=JSON，就可以看到执行计划的更多细节：
explain FORMAT=JSON select * from user where user_name not in(select user_name from user_prop)
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1039.25"
},
"table": {
"table_name": "user",
"access_type": "ALL",
"rows_examined_per_scan": 10150,
"rows_produced_per_join": 10150,
"filtered": "100.00",
"cost_info": {
"read_cost": "24.25",
"eval_cost": "1015.00",
"prefix_cost": "1039.25",
"data_read_per_join": "14M"
},
"used_columns": [
"id",
"user_name",
"password",
"user_type",
"enabled",
"real_name",
"tel",
"create_date"
],
"attached_condition": "<in_optimizer>(`leotest`.`user`.`user_name`,`leotest`.`user`.`user_name` in ( <materialize> (/* select#2 */ select `leotest`.`user_prop`.`user_name` from `leotest`.`user_prop` where true having true ), <primary_index_lookup>(`leotest`.`user`.`user_name` in <temporary table> on <auto_distinct_key> where ((`leotest`.`user`.`user_name` = `<materialized_subquery>`.`user_name`)))) is false)",
"attached_subqueries": [
{
"table": {
"table_name": "<materialized_subquery>",
"access_type": "eq_ref",
"key": "<auto_key>",
"key_length": "403",
"rows_examined_per_scan": 1,
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "2085.85"
},
"table": {
"table_name": "user_prop",
"access_type": "ALL",
"rows_examined_per_scan": 19656,
"rows_produced_per_join": 19656,
"filtered": "100.00",
"cost_info": {
"read_cost": "120.25",
"eval_cost": "1965.60",
"prefix_cost": "2085.85",
"data_read_per_join": "83M"
},
"used_columns": [
"user_name"
]
}
}
}
}
}
]
}
}
}
注意到输出的json中有materialized_from_subquery，表示把子查询的结果物化了。
type的具体含义
type是访问表或者连接表的类型。共有14种类型，常见的有下面这些：
- system
system表示表中只有一行数据。
对于InnoDB存储引擎来说，表的数据量是一个估计值，并非准确值，所以即使InnoDB的表只有一条记录，也会走索引查询或者全表扫描。对于MyISAM和Memory存储引擎来说，表的数据量是准确值，查询只有一行数据的表，type可以是system级别。
这里举一个InnoDB的例子：
explain select c from( select count(*) c from user where user_name='leo') a;
id|select_type|table |partitions|type |possible_keys |key |key_len|ref |rows|filtered|Extra |
--+-----------+----------+----------+------+------------------+------------------+-------+-----+----+--------+-----------+
1|PRIMARY |<derived2>| |system| | | | | 1| 100.0| |
2|DERIVED |user | |ref |user_user_name_IDX|user_user_name_IDX|403 |const| 1| 100.0|Using index|
子查询select count(*) c from user where user_name=‘leo’ 是一个派生表，只有一个字段，一行数据，因为派生表本质上是一个内部临时表，使用的是Memory存储引擎，所以外层的查询从这个派生表查询数据就可以达到system级别。
- const
const表示用主键或者唯一索引，与常数做等值查询，最多只有一行结果。
这是一个用主键做等值查询的例子：
explain select * from user where id=1;
id|select_type|table|partitions|type |possible_keys|key |key_len|ref |rows|filtered|Extra|
--+-----------+-----+----------+-----+-------------+-------+-------+-----+----+--------+-----+
1|SIMPLE |user | |const|PRIMARY |PRIMARY|8 |const| 1| 100.0| |
- eq_ref
在多表连接查询时，使用主键或唯一索引字段连接表。特别注意的是，仅当被驱动表使用主键或唯一索引连接，才是eq_ref。
explain select * from user_prop left join user on user_prop.user_id = user.id ;
id|select_type|table |partitions|type |possible_keys|key |key_len|ref |rows |filtered|Extra|
--+-----------+---------+----------+------+-------------+-------+-------+-------------------------+-----+--------+-----+
1|SIMPLE |user_prop| |ALL | | | | |19656| 100.0| |
1|SIMPLE |user | |eq_ref|PRIMARY |PRIMARY|8 |leotest.user_prop.user_id| 1| 100.0| |
用user_prop表左连接user表，这样指定了user_prop表是驱动表，user表是被驱动表。user表是用id字段也就是主键关联，所以执行计划第二行user表的type是eq_ref，而第一行user_prop未指定查询条件，所以是全表扫描all类型。
如果把连接顺序反过来呢？
explain select * from user left join user_prop on user.id = user_prop.user_id ;
id|select_type|table |partitions|type|possible_keys |key |key_len|ref |rows |filtered|Extra|
--+-----------+---------+----------+----+---------------------+---------------------+-------+---------------+-----+--------+-----+
1|SIMPLE |user | |ALL | | | | |10150| 100.0| |
1|SIMPLE |user_prop| |ref |user_prop_user_id_IDX|user_prop_user_id_IDX|8 |leotest.user.id| 2| 100.0| |
这个SQL的被驱动表是user_prop，用二级索引字段user_id字段关联，所以type变成了ref。
- ref
ref表示用二级索引字段与常数做等值查询。
上面的例子，被驱动表用二级索引连接，type值为ref。除了这种情况之外，直接用二级索引查询单表也可以得到ref访问类型：
explain select * from user where user_name='leo'
id|select_type|table|partitions|type|possible_keys |key |key_len|ref |rows|filtered|Extra|
--+-----------+-----+----------+----+------------------+------------------+-------+-----+----+--------+-----+
1|SIMPLE |user | |ref |user_user_name_IDX|user_user_name_IDX|403 |const| 1| 100.0| |
- ref_or_null
ref_or_null表示用二级索引字段与常数做等值查询，并且允许该索引字段的值为NULL。
把上面的单表查询的SQL多加一个 or user_name is null的条件，就可以得到ref_or_null访问类型。
explain select * from user where user_name='leo' or user_name is null;
id|select_type|table|partitions|type |possible_keys |key |key_len|ref |rows|filtered|Extra |
--+-----------+-----+----------+-----------+------------------+------------------+-------+-----+----+--------+---------------------+
1|SIMPLE |user | |ref_or_null|user_user_name_IDX|user_user_name_IDX|403 |const| 2| 100.0|Using index condition|
- index_merge
index_merge表示使用索引合并的方式查询表。
索引合并是指在查询单表时，同时使用多个索引，目的是减少回表次数，提高查询效率。索引合并包括Intersection、Union、Sort-Union三种方式。
-
Intersection 交集索引合并
有两种情况可以用到交集索引合并，第一种是用and连接多个二级索引的等值查询，如key1 = const1 AND key2 = const2 … AND keyN = constN。但是对于这种查询条件，查询优化器可能会选择一个区分度比较高的索引，然后回表查到每条记录的所有字段值之后再在server层按剩下的条件过滤数据，换句话讲，查询优化器可能认为使用交集索引合并的查询代价反而比使用单索引回表的代价高。
第二种是用and连接二级索引等值查询和聚簇索引范围查询，格式为primary_key < const1 AND key1 = const2。例如：
explain select * from user where user_name='leo' and id<100 ;
id|select_type|table|partitions|type |possible_keys |key |key_len|ref|rows|filtered|Extra |
--+-----------+-----+----------+-----------+--------------------------+--------------------------+-------+---+----+--------+--------------------------------------------------------+
1|SIMPLE |user | |index_merge|PRIMARY,user_user_name_IDX|user_user_name_IDX,PRIMARY|411,8 | | 1| 100.0|Using intersect(user_user_name_IDX,PRIMARY); Using where|
注意执行计划的type=index_merge，并且Extra字段的Using intersect表示使用了交集合并的方式。
-
Union 并集索引合并
与交集索引合并类似，并集索引合并同样也有两种情况可以使用。第一种是用or连接多个二级索引的等值查询，如key1 = const1 OR key2 = const2 … OR keyN = constN。第二种是用or连接二级索引等值查询和聚簇索引范围查询，格式为primary_key < const1 OR key1 = const2
explain select * from user where user_name='leo' or tel='123';
id|select_type|table|partitions|type |possible_keys |key |key_len|ref|rows|filtered|Extra |
--+-----------+-----+----------+-----------+-------------------------------+-------------------------------+-------+---+----+--------+---------------------------------------------------------+
1|SIMPLE |user | |index_merge|user_user_name_IDX,user_tel_IDX|user_user_name_IDX,user_tel_IDX|403,203| | 2| 100.0|Using union(user_user_name_IDX,user_tel_IDX); Using where|
explain select * from user where user_name='leo' or id<100;
id|select_type|table|partitions|type |possible_keys |key |key_len|ref|rows|filtered|Extra |
--+-----------+-----+----------+-----------+--------------------------+--------------------------+-------+---+----+--------+----------------------------------------------------+
1|SIMPLE |user | |index_merge|PRIMARY,user_user_name_IDX|user_user_name_IDX,PRIMARY|403,8 | | 100| 100.0|Using union(user_user_name_IDX,PRIMARY); Using where|
-
Sort-Union 排序并集索引合并
与并集索引合并相比，排序并集索引合并多了一个排序的步骤，用于多个索引（可以是二级索引也可以是聚簇索引）的范围查询。
explain select * from user force index(user_user_name_IDX,user_tel_IDX) where user_name<'leo' or tel<'123' ;
id|select_type|table|partitions|type |possible_keys |key |key_len|ref|rows|filtered|Extra |
--+-----------+-----+----------+-----------+-------------------------------+-------------------------------+-------+---+----+--------+--------------------------------------------------------------+
1|SIMPLE |user | |index_merge|user_user_name_IDX,user_tel_IDX|user_user_name_IDX,user_tel_IDX|403,203| |1243| 100.0|Using sort_union(user_user_name_IDX,user_tel_IDX); Using where|
在这个例子中，对于查询条件user_name<‘leo’ or tel<‘123’ ，leo本地的查询优化器会走全表扫描，而不是索引合并，因此使用了force index强制使用索引。
- range
range表示使用索引查询给定范围的数据。
explain select * from user where user_name like 'leo%'
id|select_type|table|partitions|type |possible_keys |key |key_len|ref|rows|filtered|Extra |
--+-----------+-----+----------+-----+------------------+------------------+-------+---+----+--------+---------------------+
1|SIMPLE |user | |range|user_user_name_IDX|user_user_name_IDX|403 | | 1| 100.0|Using index condition|
- index
index表示扫描某个索引的全部记录。
explain select user_name,count(*) from user group by user_name
id|select_type|table|partitions|type |possible_keys |key |key_len|ref|rows |filtered|Extra |
--+-----------+-----+----------+-----+------------------+------------------+-------+---+-----+--------+-----------+
1|SIMPLE |user | |index|user_user_name_IDX|user_user_name_IDX|403 | |10150| 100.0|Using index|
- all
全表扫描。
Extra的具体含义
官方文档给出了37个Extra值，较为常见的值有：
- Using index
Using index表示使用索引覆盖，select的字段以及where条件的字段都是二级索引字段，查询结果不需要回表，是性能比较好的表现。
explain select user_name,count(*) from user group by user_name
id|select_type|table|partitions|type |possible_keys |key |key_len|ref|rows |filtered|Extra |
--+-----------+-----+----------+-----+------------------+------------------+-------+---+-----+--------+-----------+
1|SIMPLE |user | |index|user_user_name_IDX|user_user_name_IDX|403 | |10150| 100.0|Using index|
- Using index condition
Using index condition表示使用索引下推，索引下推中的索引是二级索引，下推是指把where条件下推到Engine层，在Engine层过滤数据，而不是在Server层过滤数据。 使用索引下推可以减少回表的次数。
explain select * from user where tel>'130' and tel<'139'
id|select_type|table|partitions|type |possible_keys|key |key_len|ref|rows|filtered|Extra |
--+-----------+-----+----------+-----+-------------+------------+-------+---+----+--------+---------------------+
1|SIMPLE |user | |range|user_tel_IDX |user_tel_IDX|203 | | 85| 100.0|Using index condition|
- Using where
Using where表示在Server层过滤数据。Engine层回表取出聚簇索引记录后返回给Servr层，Server再根据where条件过滤数据。
explain select * from user where tel>'130' and tel<'139' and real_name ='leo'
id|select_type|table|partitions|type |possible_keys|key |key_len|ref|rows|filtered|Extra |
--+-----------+-----+----------+-----+-------------+------------+-------+---+----+--------+----------------------------------+
1|SIMPLE |user | |range|user_tel_IDX |user_tel_IDX|203 | | 85| 10.0|Using index condition; Using where|
这个例子中，因为real_name不是一个索引字段，按这个字段过滤数据就只能回表之后取出real_name的值再在Server过滤数据。
- Using filesort
Using filesort表示使用文件排序。如果搜索结果无法直接根据索引排序，那么就要使用文件排序。数据量小时文件排序可以直接在内存中完成，数据量大就要在磁盘上排序。
explain select * from user where tel>'130' and tel<'139' and real_name ='leo' order by user_name ;
id|select_type|table|partitions|type |possible_keys|key |key_len|ref|rows|filtered|Extra |
--+-----------+-----+----------+-----+-------------+------------+-------+---+----+--------+--------------------------------------------------+
1|SIMPLE |user | |range|user_tel_IDX |user_tel_IDX|203 | | 85| 10.0|Using index condition; Using where; Using filesort|
这个SQL的查询条件使用了tel字段的索引，而排序字段是另一个，所以只能用文件排序。文件排序对于查询的性能有一定影响，应尽量改写SQL或增加索引，使用索引排序。
- Using temporary
Using temporary表示使用临时表。这种临时表并非我们使用CREATE TEMPORARY TABLE语句创建的外部临时表，而是MySQL自动创建的内部临时表。union、group by、distinct都有可能使用临时表。
explain select distinct real_name from user
id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra |
--+-----------+-----+----------+----+-------------+---+-------+---+-----+--------+---------------+
1|SIMPLE |user | |ALL | | | | |10150| 100.0|Using temporary|
这个SQL会使用临时表做数据去重。
另外，上文所说的派生表、物化表本质上也是内部临时表，只是在Extra字段没有显示Using temporary。
总结
本文列举了执行计划输出内容中的各个字段，以及每个字段的含义和对应的取值范围，用实际的SQL和执行计划结果解读了执行计划的具体含义。但是执行计划的内容很多，文章不能面面俱到，遇到不常见的执行计划内容，还需要我们随时查阅资料。另外，文本的重点在于讲执行计划是什么，有什么内容，但并未涉及MySQL生成执行计划的过程，这部分内容待后续的文章进一步说明。
参考资料
MySQL文档-执行计划
MySQL文档-索引合并
MySQL 是怎样运行的：从根儿上理解 MySQL
MySQL 执行计划详解 | 京东物流技术团队
扫一扫
1237
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
