文章目录
概述
explain语句(或describe语句，等效)用来展示某个SQL的具体执行方式，包括表的读取顺序、数据读取时的类型、哪些索引可以被使用、哪个索引被实际使用、表之间的引用以及每张表有多少行被优化器查询。
官网介绍：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
基本语法：explain/describe SQL语句，本文以查询语句为例，记录explain语句的学习过程。
输出格式：
| 列名 | 含义 |
|---|---|
| id | 一个SQL中，每个select关键字都有一个唯一ID |
| select_type | select关键字对应的查询类型 |
| table | 表名 |
| partitions | 匹配的分区信息 |
| type | 针对单表的查询方法 |
| possible_keys | 可能用到的索引候选 |
| key | 实际使用的索引 |
| key_len | 实际使用的索引长度 |
| ref | 当使用索引列进行等值查询时，与索引列进行等值匹配的对象信息 |
| rows | 预估要读取的记录条数 |
| filtered | 某个表经过搜索条件过滤后，剩余记录所占全部记录的百分比 |
| extra | 额外信息 |
数据准备
建表
CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;
CREATE TABLE s2 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;
开启允许创建函数
set global log_bin_trust_function_creators=1;
创建函数和存储过程
创建用来产生随机数据的函数和存储过程：
DELIMITER //
CREATE FUNCTION rand_string1(n INT)
RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s1 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s2 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
调用存储过程
调用存储过程来产生数据：
CALL insert_s1(10001, 10000);
CALL insert_s2(10001, 10000);
explain各列说明
table
不管select语句多复杂，里面包含了多少张表，到最后也是要对每张表进行查询的。因此explain语句输出的每条记录都对应着某张单表的访问方法，该记录的table字段代表着该表的表名或简称。
id
简单select
mysql> explain select * from s1 where key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
多表连接
mysql> explain select * from s1 inner join s2 on s1.key1 = s2.key1 where s1.common_field = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9895 | 10.00 | Using where |
| 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | review_mysql.s1.key1 | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
可见多表连接里包含了两条语句，若多条语句的id相等，则认为这些语句为同一组，从上往下顺序执行。
子查询
mysql> explain select * from s1 where key1 in (select key2 from s2 where s2.common_field = 'a');
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+------------------------------------+
| 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9895 | 100.00 | Using where |
| 1 | SIMPLE | s2 | NULL | ref | idx_key2 | idx_key2 | 5 | review_mysql.s1.key1 | 1 | 10.00 | Using index condition; Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+------------------------------------+
2 rows in set, 2 warnings (0.00 sec)
union和union all
mysql> explain select * from s1 union select * from s2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL |
| 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 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)
mysql> explain select * from s1 union all select * from s2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL |
| 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
如果多个查询语句的ID不同，则ID越大者越先执行。多个查询ID意味着有多趟查询，我们要让查询趟数越少越好。
select_type
| 值 | 描述 |
|---|---|
| simple | 简单查询，没使用union或子查询 |
| primary | 最外层的select |
| union | union语句中的非第一个select |
| union result | union的结果 |
| subquery | 子查询中的第一个select |
| dependent subquery | 子查询中的第一个select，且该select依赖于外部查询 |
| dependent union | union语句中的非第一个select，且该select依赖于外部查询 |
| derived | 某个查询存在派生表时，派生表对应的查询即为derived查询 |
| materialized | 物化子查询 |
| uncacheable subquery | 查询结果不能被缓存，且必须为外部查询的每一行进行重新计算的子查询 |
| uncacheable union | union语句中属于uncacheable subquery(不可缓存的子查询)的非第一个select |
接下来对其中每一项进行举例，不过后两项不常用，在此略过。
simple
单表查询是典型的simple：
mysql> explain select * from s1 where key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
多表连接也算是simple：
mysql> explain select * from s1 inner join s2 on s1.key1 = s2.key1 where s1.common_field = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9895 | 10.00 | Using where |
| 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | review_mysql.s1.key1 | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
如果子查询可以被优化器重写为多表连接，则该子查询也算是simple：
mysql> explain select * from s1 where key1 in (select key2 from s2 where s2.common_field = 'a');
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+------------------------------------+
| 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9895 | 100.00 | Using where |
| 1 | SIMPLE | s2 | NULL | ref | idx_key2 | idx_key2 | 5 | review_mysql.s1.key1 | 1 | 10.00 | Using index condition; Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+------------------------------------+
2 rows in set, 2 warnings (0.00 sec)
上例中子查询是不相关子查询，因此是simple查询。
primary
典型的场景就是union：
mysql> explain select * from s1 union select * from s2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL |
| 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 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)
其中也包含了union类型和union result类型的查询
subquery
如果子查询不能被优化器重写为多表连接，且为不相关子查询，则对应主查询为primary，子查询为subquery：
mysql> explain select * from s1 where key1 in (select key1 from s2) or key3 = 'a';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9895 | 100.00 | Using where |
| 2 | SUBQUERY | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9895 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
上例说明select * from s1 where key1 in (select key1 from s2) or key3 = 'a';没有被写成多表连接，因为主查询的where中多了一个or。
dependent subquery
由于在子查询的where中将主查询的过滤字段作为子查询的过滤字段（即相关子查询），且不能被重写为多表连接，因此该子查询的类型为dependent subquery：
mysql> explain select * from s1 where key1 in (select key1 from s2 where s1.key2 = s2.key2) or key3 = 'a';
+----+--------------------+-------+------------+------+-------------------+----------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+------+-------------------+----------+---------+----------------------+------+----------+-------------+
| 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9895 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | s2 | NULL | ref | idx_key2,idx_key1 | idx_key2 | 5 | review_mysql.s1.key2 | 1 | 10.00 | Using where |
+----+--------------------+-------+------------+------+-------------------+----------+---------+----------------------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)
dependent union
如果子查询里的union中非第一个select语句的where，将主查询的where过滤字段作为自己的过滤字段，则该子查询union为dependent union：
mysql> explain select * from s1 where key1 in (select key1 from s2 where s2.key1 = 'a' union select key1 from s1 where s1.key1 = 'b');
+----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+
| 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | Using where; Using index |
| 3 | DEPENDENT UNION | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | Using where; Using index |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+
4 rows in set, 1 warning (0.00 sec)
这里子查询里面的两部分都是相关的(相关子查询、相关联合)，是因为优化器将我们的SQL重构成了相关子查询。
derived
如果需要将子查询的结果固定成一张表，则该子查询就是派生表：
mysql> explain select * from (select key1, count(*) as c from s1 group by key1) as derived_s1;
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL |
| 2 | DERIVED | s1 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9895 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
materialized
如果某子查询经过优化后成为一张只包含子查询select字段的表，则该子查询就是物化子查询：
mysql> explain select * from s1 where key1 in (select key1 from s2);
+----+--------------+-------------+------------+--------+---------------+------------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+---------------+------------+---------+----------------------+------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9895 | 100.00 | Using where |
| 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_key> | <auto_key> | 303 | review_mysql.s1.key1 | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9895 | 100.00 | Using index |
+----+--------------+-------------+------------+--------+---------------+------------+---------+----------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
partition
表示分区表的分区命中情况，非分区表的partition字段均为null。
type
| 值 | 场景 |
|---|---|
| system | 表中只有一条记录，且该表使用的存储引擎是精确统计数据 |
| const | 根据主键或唯一(unique)索引列和常数进行等值匹配 |
| eq_ref | 表连接时，被连接表是通过主键或唯一索引进行连接的 |
| ref | 普通索引和常量进行等值匹配，注意不能有类型转换 |
| fulltext | 全文索引 |
| ref_or_null | 普通索引和常量进行等值匹配，并且where子句中有null判断，注意不能有类型转换 |
| index_merge | 需要将两个普通单列索引合并成一个索引时 |
| unique_subquery | 针对一些in子查询，优化器决定将in子查询转换成exist子查询，并且子查询中用到了主键进行等值匹配 |
| index_subquery | 一般在update或delete中显示，且子查询中使用到了普通索引 |
| range | 使用索引进行范围查询 |
| index | 索引覆盖+全表扫描，特别是使用联合索引时，没有按照定义联合索引时规定的顺序定义where子句时 |
| all | 经过优化后，相当于没有索引时的全表遍历的查询 |
上表中，越往下的类型查询效率越低。
system
这是性能最高的查询：
mysql> create table test_system_type(id int) engine = myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test_system_type (id) values(1);
Query OK, 1 row affected (0.00 sec)
mysql> explain select * from test_system_type;
+----+-------------+------------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test_system_type | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+------------------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
const
mysql> explain select * from s1 where id = 10005;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from s1 where key2 = 10066;
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | const | idx_key2 | idx_key2 | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
eq_ref
mysql> explain select * from s1 inner join s2 on s1.id = s2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 9895 | 100.00 | NULL |
| 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | review_mysql.s1.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
ref
mysql> explain select * from s1 where key3 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ref | idx_key3 | idx_key3 | 303 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
ref_or_null
mysql> explain select * from s1 where key3 = 'a' or key3 = null;
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | s1 | NULL | ref_or_null | idx_key3 | idx_key3 | 303 | const | 2 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
index_merge
mysql> explain select * from s1 where key3 = 'a' or key1 = 'b';
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| 1 | SIMPLE | s1 | NULL | index_merge | idx_key1,idx_key3 | idx_key3,idx_key1 | 303,303 | NULL | 2 | 100.00 | Using union(idx_key3,idx_key1); Using where |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)
ref_or_null要求多个单列索引在where中的关系为并集关系。
unique_subquery
mysql> explain select * from s1 where key2 in (select id from s2 where s1.key1 = s2.key1) or key3 = 'a';
+----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+
| 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9895 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | s2 | NULL | unique_subquery | PRIMARY,idx_key1 | PRIMARY | 4 | func | 1 | 10.00 | Using where |
+----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)
index_subquery
mysql> explain update s1 set key1 = 'a' where key1 in (select key2 from s2);
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+------+----------+--------------------------+
| 1 | UPDATE | s1 | NULL | index | NULL | PRIMARY | 4 | NULL | 9895 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | s2 | NULL | index_subquery | idx_key2 | idx_key2 | 5 | func | 1 | 100.00 | Using where; Using index |
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+------+----------+--------------------------+
2 rows in set (0.00 sec)
mysql> explain delete from s1 where key1 in (select key2 from s2);
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+------+----------+--------------------------+
| 1 | DELETE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | s2 | NULL | index_subquery | idx_key2 | idx_key2 | 5 | func | 1 | 100.00 | Using where; Using index |
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+------+----------+--------------------------+
2 rows in set (0.00 sec)
range
mysql> explain select * from s1 where key1 in ('a', 'b');
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from s1 where key1 > 'a' and key1 < 'e';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 1565 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
index
mysql> explain select key_part1 from s1 where key_part3 = 'a';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | s1 | NULL | index | NULL | idx_key_part | 909 | NULL | 9895 | 10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
all
mysql> explain select * from s1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
possible_keys和key
前者表示执行该查询可能用到的索引候选集合，后者表示执行该查询时实际用的索引(没用到即为NULL，且不一定为候选集合的子集)，前者越少越好，因为涉及候选项的成本计算。
key_len
key_len表示实际用到的索引长度(byte)。对于同一个索引，值越大越好。针对字符串类型char和varchar，单列索引的计算公式如下：
| 类型 | 是否非空 | 计算公式 |
|---|---|---|
| varchar(n) | 是 | n * len_charset + 1 + 2 |
| varchar(n) | 否 | n * len_charset + 2 |
| char(n) | 是 | n * len_charset + 1 |
| char(n) | 否 | n * len_charset |
len_charset取决于使用的字符集：utf8、gbk和latin1分别对应的值为3、2、1。
如果使用的是联合索引，则key_len为联合索引中每个单列字段的key_len之和。
varchar可空单列索引举例
mysql> explain select * from s1 where key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
其中，key_len = 100 * 3 + 1 + 2 = 303。
varchar可空联合索引举例
mysql> explain select * from s1 where key_part1 = 'a' and key_part2 = 'b';
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ref | idx_key_part | idx_key_part | 606 | const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
key_len = (100 * 3 + 1 + 2) + (100 * 3 + 1 + 2) = 606
int非空举例
mysql> explain select * from s1 where id = 10005;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
key_len为4，是因为key1是主键，所以是非空的，因此索引长度就是int的长度。
int可空举例
mysql> explain select * from s1 where key2 = 11326;
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | const | idx_key2 | idx_key2 | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
多出来的1就是因为key2是可空的。
ref
当使用索引列进行等值查询时，与索引列进行等值匹配的对象信息。如果等值查询匹配的是常数，则索引列的类型为ref；如果等值匹配用到了多表连接，则被连接表的索引列类型为eq_ref。
普通索引的等值匹配
mysql> explain select * from s1 where key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
多表连接时进行的等值匹配
mysql> explain select * from s1 join s2 on s1.id = s2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 9895 | 100.00 | NULL |
| 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | review_mysql.s1.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
可见被连接表的索引列对应的类型就是eq_ref
多表连接时对被连接表的索引列使用函数
mysql> explain select * from s1 join s2 on upper(s1.id) = s2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL |
| 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
对被连接表的索引列使用函数后，除了被连接表的索引列类型时eq_ref外，引用源的ref也变成了func。
rows
要读取的行数预计值，越小越好。
举例：
mysql> explain select * from s1 where key1 > 'v';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9895 | 19.01 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
filtered
预计过滤后，剩余数据占所有数据百分比。
单表查询
mysql> explain select * from s1 where key1 > 'v' and common_field = 'a';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9895 | 1.90 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
结合rows字段，filtered的含义是9895条数据中，符合where条件的数据占比为1.9%
多表连接
filtered更多的关注点在多表连接，连接表的filtered * rows决定了被连接表要连接多少次：
mysql> explain select * from s1 join s2 on s1.id = s2.id where s1.common_field = 'a';
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 9895 | 10.00 | Using where |
| 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | review_mysql.s1.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
上例中，连接表的rows * filtered = 989.5，即被连接表预计要被连接989次。
extra
extra字段提供了一些不太适合放到其他字段中显示，但却对执行SQL非常重要的信息，常见的字段值如下表所示：
| 值 | 说明 |
|---|---|
| no table used | 没有使用表 |
| impossible where | where条件不可能成立 |
| using where | 使用了where语句，且没有使用索引 |
| no matching min/max row | 使用了最小/最大聚合函数，但没有匹配where条件的数据 |
| using index | 使用了覆盖索引，即select的字段可以被where中的索引字段包含，即不需要回表了 |
| using index condition | where中出现了索引列，且需要进行索引条件下推优化 |
| using join buffer (block nested loop) | 多表连接中，被连接表不能通过索引加速检索时，MySQL会为其分配一块叫做join buffer的缓存加快查询速率 |
| not exists | 左外连接时，where语句要求被连接表中某个非空列的列值为空 |
| using interset(…)/union(…)/sort_union(…) | 准备使用interset/union/sort union的方式合并索引进行查询，括号中的…即要合并的索引名 |
| zero limit | 使用了limit子句，且限制条数为0 |
| using filesort | 对varchar类型字段进行排序，且该这种排序用不到索引 |
| using temporary | 使用了临时表 |
| 其他 | 忽略 |
no table used
mysql> explain select 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
impossible where
mysql> explain select * from s1 where 1 > 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)
using where
mysql> explain select * from s1 where id > 1000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 4947 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
no matching min/max row
mysql> explain select min(id) from s1 where id < 0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No matching min/max row |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+
1 row in set, 1 warning (0.00 sec)
using index
mysql> explain select key1 from s1 where key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select key_part1, key_part2, id from s1 where key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c';
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ref | idx_key_part | idx_key_part | 909 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
加上主键id也属于覆盖索引，因为主键本身就存储于非聚簇索引B+树的叶子结点中。
using index condition
mysql> explain select * from s1 where key1 > 'z' and key1 like '%a';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 367 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
using join buffer (block nested loop)
mysql> explain select * from s1 join s2 on s1.common_field = s1.common_field;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 10.00 | Using where |
| 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.00 sec)
not exists
mysql> explain select * from s1 left join s2 on s1.id = s1.id where s2.id is null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL |
| 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 10.00 | Using where; Not exists; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
using interset(…)/union(…)/sort_union(…)
mysql> explain select * from s1 where key1 = 'a' or key2 = 'b';
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| 1 | SIMPLE | s1 | NULL | index_merge | idx_key2,idx_key1 | idx_key1,idx_key2 | 303,5 | NULL | 2 | 100.00 | Using union(idx_key1,idx_key2); Using where |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)
zero limit
mysql> explain select * from s1 limit 0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Zero limit |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
1 row in set, 1 warning (0.00 sec)
using filesort
mysql> explain select * from s1 order by key1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
对非字符串型字段排序，或可以使用索引字段的排序，就不会有using filesort：
mysql> explain select * from s1 order by id;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | index | NULL | PRIMARY | 4 | NULL | 9895 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
using temporary
特别是distinct、group by等用到的字段是非索引列时，extra中会有using temporary：
mysql> explain select distinct common_field from s1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select count(*) from s1 group by common_field;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)
如果分组、去重等操作可以用到索引，就无需临时表：
mysql> explain select common_field from s1 group by key1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9895 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
因为创建、维护临时表需要开销，所以还是尽量使用索引进行相关操作。
小结
explain不考虑任何cache，不能显示优化过程，不会显示触发器、存储过程的信息，或用户自定义函数对查询的影响，而且其部分统计信息是估算的，不是精确值。
两种输出格式
传统格式
即默认格式，输出形式为一张表。
JSON格式
在explain后面指定format = json即可：
mysql> explain format = json select * from s1 where key1 = 'a';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.20" # 查询成本
},
"table": {
"table_name": "s1",
"access_type": "ref", # 传统格式下的type
"possible_keys": [
"idx_key1"
],
"key": "idx_key1",
"used_key_parts": [
"key1"
],
"key_length": "303", # 传统格式下d的key_len
"ref": [
"const"
],
"rows_examined_per_scan": 1, # 即传统模式下的rows
"rows_produced_per_join": 1, # 传统模式下rows * filtered的向下取整
"filtered": "100.00",
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.20",
"prefix_cost": "1.20",
"data_read_per_join": "1K"
},
"used_columns": [
"id",
"key1",
"key2",
"key3",
"key_part1",
"key_part2",
"key_part3",
"common_field"
]
}
}
} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
其中cost_info信息如下：
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.20",
"prefix_cost": "1.20",
"data_read_per_join": "1K"
}
read_cost包括两部分：1）、IO成本；2）、检测到rows * (1 - filtered)条记录的CPU成本
eval_cost：检测rows * filtered条记录的成本；
prefix_cost：单独查询s1标的成本，即read_cost + eval_cost；
data_read_per_join：此次查询中需要读取的数据量。
show warnings
该语句用于显示上一条语句执行后，产生的警告信息：
mysql> explain select * from s1 where key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `review_mysql`.`s1`.`id` AS `id`,`review_mysql`.`s1`.`key1` AS `key1`,`review_mysql`.`s1`.`key2` AS `key2`,`review_mysql`.`s1`.`key3` AS `key3`,`review_mysql`.`s1`.`key_part1` AS `key_part1`,`review_mysql`.`s1`.`key_part2` AS `key_part2`,`review_mysql`.`s1`.`key_part3` AS `key_part3`,`review_mysql`.`s1`.`common_field` AS `common_field` from `review_mysql`.`s1` where (`review_mysql`.`s1`.`key1` = 'a')
1 row in set (0.00 sec)
上例中，show warnings显示的警告信息中的Message字段，输出了我们的SQL经过优化后的大体格式。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
