开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MySQL——分析查询语句：EXPLAIN -> 正文阅读

[大数据]MySQL——分析查询语句：EXPLAIN

文章目录

MySQL——分析查询语句：EXPLAIN

MySQL——分析查询语句：EXPLAIN

1、概述

Explain 命令是查看查询优化器如何决定执行查询的主要方法。学会解释 Explain 命令输出的信息将帮助你了解 MySQL 优化器是如何工作的。

使用Explain 关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

Explain 显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

官网介绍：http://dev.mysql.com/doc/refman/5.7/en/explain-output.html

MySQL 5.6.3以前只能EXPLAIN SELECT;MYSQL 5.6.3以后就可以EXPLAIN SELECT，UPDATE，DELETE。在5.7以前的版本中，想要显示partitions需要使用explain partitions命令；想要显示filtered需要使用explain extengled命令。在5.7版本后，默认explain直接显示partitions和filtered中的信息。

Explain 作用

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

Explain 语法

-- 实例：
mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3990079 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.02 sec)

输出的上述信息就是所谓的执行计划。在这个执行计划的辅助下，我们需要知道应该怎样改进自己的查询语句以使查询执行起来更高效。其实除了以SELECT开头的查询语句，其余的DELETE、INSERT、REPLACE以及UPDATE语句等都可以加上EXPLAIN，用来查看这些语句的执行计划，只是平时我们对 SELECT语句更感兴趣。

注：执行EXPLAIN时并没有真正的执行该后面的语句，因此可以安全的查看执行计划。

EXPLAIN 语句输出的各个列的作用：

列名	描述
id	在一个复杂查询语句中每个SELECT关键字都对应一个id
select_type	SELECT 关键字对应查询的类型
table	表名
partitions	匹配的分区信息
type	针对表单的访问方法
possible_keys	可能用到的索引
key	实际上用到的索引
key_len	实际用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

2、数据准备

建表

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),
    commom_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),
    commom_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 
        'abcdefghijklmnopqrstuvwsyzABCDEFGJHIKLMNOPQRSTUVWSYZ';
    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 ;

创建存储过程

创建往s1添加数据的存储过程

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 ;

创建往s2添加数据的存储过程

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_s2(10001,10000);
call insert_s1(10001,10000);

mysql> select count(*) from s1;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from s2;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

3、EXPLAIN 各列作用分析

在这里插入图片描述

3.1、table

table：输出的行所引用的表名

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)

-- s1 驱动表，s2 被驱动表
mysql> explain select * from s1 join s2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| 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 |   100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.00 sec)

1)、当在from子句中有子查询时，table列是<derivedN>的形式，其中N是子查询的id。这总是“向前引用”，即N指向explain输出中后面的一行。

2)、当有union时，union result 的 table 列包含一个参与union的id列表。这总是“向后引用”，因为union result出现在union中所有参与行之后

3.2、id

id：SELECT识别符。这是SELECT的查询序列号

两个select查询，所以两个不同id

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.01 sec)

子查询和外查询的id相同，因为查询优化器可能对涉及子查询的查询语句进行重写,转变为多表查询的操作，所以只会有一个select

mysql> explain select * from s1 where key1 in (select key3 from s2 where commom_field = 'a');
+----+--------------+-------------+------------+------+---------------+----------+---------+------------------+------+----------+-------------+
| id | select_type  | table       | partitions | type | possible_keys | key      | key_len | ref              | rows | filtered | Extra       |
+----+--------------+-------------+------------+------+---------------+----------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL  | NULL          | NULL     | NULL    | NULL             | NULL |   100.00 | Using where |
|  1 | SIMPLE       | s1          | NULL       | ref  | idx_key1      | idx_key1 | 303     | <subquery2>.key3 |    1 |   100.00 | NULL        |
|  2 | MATERIALIZED | s2          | NULL       | ALL  | idx_key3      | NULL     | NULL    | NULL             | 9895 |    10.00 | Using where |
+----+--------------+-------------+------------+------+---------------+----------+---------+------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

union 去重，<union1,2> 临时表

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)

id 如果相同，可以认为是一组，从上往下顺序执行

在所有组中，id值越大，优先级越高，越先执行

id号每个号码，表示一趟独立的查询，一个sql的查询趟数越少越好

3.3、select_type

MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为select.type的属性，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色。

查询的类型，可以是下表的任何一种类型：

select_type	类型说明
SIMPLE	简单SELECT(包括连接查询，不包括UNION或子查询)
PRIMARY	对于包含UNION或UNION ALL的大查询中，最外层（第一个）的SELECT语句的select_type就是PRIMARY
UNION	对于包含UNION或UNION ALL的大查询中，第二个或之后的SELECT语句的select_type就是UNION
DEPENDENT UNION	对于包含UNION或UNION ALL的大查询中，如果小查询都依赖于外查询的话，那第二个或之后的SELECT语句的select_type就是 DEPENDENT UNION
UNION RESULT	临时表就是UNION去重的结果，UNION RESULT就是查询临时表的 select_type
SUBQUERY	包含在select 列表中的子查询，不能够转为连表查询，并且是不相关子查询，标记为SUBQUERY。
DEPENDENT SUBQUERY	包含在select 列表中的子查询，不能够转为连表查询，并且是相关子查询，则该子查询的第一个select标记为DEPENDENT SUBQUERY。
DERIVED	派生表对应的子查询(FROM子句中的子查询)
MATERIALIZED	当查询优化器在执行包含子查询的语句时，选择将子查询物化之后于外层查询进行连接查询时，该子查询对应的select_type就是 MATERIALIZED
UNCACHEABLE SUBQUERY	结果集无法缓存的子查询，必须重新评估外部查询的每一行
UNCACHEABLE UNION	UNION中第二个或之后的SELECT，属于无法缓存的子查询

3.4、partitions

代表分区表中的命中情况，非分区表，该项为NULL。一般情况下我们的查询语句的执行计划的partitions列的值都是NULL。

3.5、type

执行计划的一条记录就代表着MysQL对某个表的执行查询时的访问方法，又称“访问类型”，其中的type列就表明了这个访问方法是何种类型，是较为重要的一个指标。比如，看到type列的值是ref，表明MySQL即将使用ref访问方法来执行对s1表的查询。

访问类型从最好到最差依次为：

system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL

system

当表中只有一条记录或者是空表并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM 或 Memory，那么对该表的访问类型就是 system。如果是InnoDB引擎，type列在这个情况通常都是all 或者 index。

-- 建表，使用MyISAM引擎
mysql> create table t(i int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)
-- 插入一条数据
mysql> insert into t value(1);
Query OK, 1 row affected (0.00 sec)
-- 分析执行计划  type的值为system,当再添加一条数据后，type的值变为all
mysql> explain select * from t;
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

-- 建表，使用InnoDB引擎
mysql> create table t1(i int) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
-- 插入一条数据
mysql> insert into t1 value(1);
Query OK, 1 row affected (0.04 sec)
-- 分析执行计划  type的值为 ALL
mysql> explain select * from t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

const

当我们根据主键或者唯一索引列与常数进行等值匹配时，对单表的访问类型就是 const 。

-- 根据主键等值匹配
mysql> explain select * from s1 where id = 10009;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 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

在连接查询时，如果被驱动表是通过主键或者唯一索引列等值匹配的方式进行访问的(如果该主键或者唯一索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问类型就是 eq_ref

eq_ref可用于使用’='操作符作比较的索引列。比较的值可以是常量，也可以是使用在此表之前读取的表的列的表达式。

相对于下面的ref区别就是它使用的唯一索引，即主键或唯一索引，而ref使用的是非唯一索引或者普通索引。eq_ref只能找到一行，而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       | testdb1.s1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

ref

当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问类型就是 ref

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)

ref_or_null

当对普通的二级索引进行等值匹配查询，该索引的值也可以是 null 值时，那么对该表的访问类型为ref_or_null

mysql> explain select * from s1 where key1 = 'a' or key1 is null;
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type        | possible_keys | key      | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | s1    | NULL       | ref_or_null | idx_key1      | idx_key1 | 303     | const |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

index_merge

表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range

mysql> explain select * from s1 where key1 = 'a' or key3 = 'a';
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| 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_key1,idx_key3 | 303,303 | NULL |    2 |   100.00 | Using union(idx_key1,idx_key3); Using where |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)

unique_subquery

unique_subquery 是针对在一些包含 IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为 EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的 type 列的值就是 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

该类型类似于unique_subquery。适用于非唯一索引，可以返回重复值。

range

索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。

mysql> explain select * from s1 where key1 in ('a','b','c');
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 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 |    3 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

index

当使用覆盖索引，但需要扫描全部索引记录时，该表的访问类型就是 index

mysql> explain select key_part2 from s1 where key_part3 = '3';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| 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 s2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

小结

访问类型从最好到最差：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range> index > ALL

SQL性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 const 级别。

3.6、possible_keys 和 key

在EXPLAIN语句输出的执行计划中， possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引。

mysql> explain select * from s1 where key1 > 'z' and key3 = 'a';
+----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys     | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1,idx_key3 | idx_key3 | 303     | const |    1 |     5.00 | Using where |
+----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

注意：

如果 possible_keys 备选的数量大于3那说明已经太多了，因为太多会导致选择索引而损耗性能，所以建表时字段最好精简，同时也要建立联合索引，避免无效的单列索引；

3.7、key_len

key_len 表示本次查询中，所选择的索引长度有多少字节，通常可借此判断联合索引有多少列被选择了。在不损失精确性的情况下，长度越短越好

key_len的计算规则

一般地，key_len 等于索引列类型字节长度，例如int类型为4-bytes，bigint为8-bytes；
如果是字符串类型，还需要同时考虑字符集因素，例如：CHAR(30) UTF8则key_len至少是90-bytes；
若该列类型定义时允许NULL，其key_len还需要再加 1-bytes；
若该列类型为变长类型，例如 VARCHAR（TEXT\BLOB不允许整列创建索引，如果创建部分索引，也被视为动态列类型），其key_len还需要再加 2-bytes;

key_len 只指示了WHERE中用于条件过滤时被选中的索引列，是不包含 ORDER BY/GROUP BY 这部分被选中的索引列。

mysql> explain select * from s1 where key2 = 11776;
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| 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)

3.8、ref

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)
-- 可以看到ref列的值是const，表明在使用idx_key1索引执行查询时，与key1列作等值匹配的对象是一个常数

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       | testdb1.s1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

3.8、rows

预估的需要读取的记录条数;

虽然只是一个估值，却也足以反映出SQL执行所需要扫描的行数，因此这个值越小越好；

3.9、filtered

某个表经过搜索条件过滤后剩余记录条数的百分比。

如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

对于单表查询来说，这个filtered列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的filtered值，它决定了被驱动表要执行的次数(即: rows * filtered)：

mysql> explain select * from s1 join s2 on s1.key1 = s2.key1 where s1.commom_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     | testdb1.s1.key1 |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

s1当作驱动表，s2当作被驱动表。可以看到驱动表s1表的 rows列为9688，fltered列为10.00，这意味着驱动表s1的扇出值就是9688 × 10.00% =968.8，这说明还要对被驱动表s2执行大约968次查询。

3.10、Extra

Extra 包含了不适合在其他列中显式但十分重要的额外信息。

我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。

No tables used

当查询语句的没有FROM子句时将会提示该额外信息

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

where子句的值总是false，不能用来获取任何元组

mysql> explain select * from s1 where id = 12345 and id = 54321;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 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

表明使用了全表扫描进行查询，并且使用了where过滤，并且where字句中有该表的搜索条件

mysql> explain select * from s1 where commom_field ='a';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 row in set, 1 warning (0.00 sec)

No matching min/max row

当查询列表处有MIN或者MAX聚合函数，但是并没有符合WHERE子句中的搜索条件的记录

mysql> explain select min(key1) from s1 where key1 = 'abcdefg';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+
| 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

表示相应查询列表以及搜索条件中只包含属于某个索引的列，使用了覆盖索引（Covering 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)

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 |  406 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

Using join buffer

表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，当被驱动表不能有效的利用索引加快访问速度，可以将配置文件中的缓冲区的join buffer调大一些。

mysql> explain select * from s1 join s2 on s1.commom_field = s2.commom_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 | NULL                                               |
|  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

Not exists

当我们使用左（外）连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列又是不允许存储NULL值的，那么在该表的执行计划的Extra列就会提示 Not exists额外信息

mysql> explain select * from s1 left join s2 on s1.key1 = s2.key1 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       | ref  | idx_key1      | idx_key1 | 303     | testdb1.s1.key1 |    1 |    10.00 | Using where; Not exists |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+-------------------------+
2 rows in set, 1 warning (0.00 sec)

Using union

如果执行计划的Extra 列出现了Using intersect(…)提示，说明准备使用Intersect 索引合并的方式执行查询，括号中的…表示需要进行索引合并的索引名称;

如果出现了Using union (…)提示，说明准备使用Union 索引合并的方式执行查询;

出现了Uusing sort union(…)提示，说明准备使用Sort-Union 索引合并的方式执行查询。

mysql> explain select * from s1 where key1 = 'a' or key3 = 'a';
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| 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_key1,idx_key3 | 303,303 | NULL |    2 |   100.00 | Using union(idx_key1,idx_key3); Using where |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Zero limit

当我们的 LIMIT 子句的展示记录条数参数为0时，表示不打算从表中读出任何记录

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会对数据使用一个外部的索引排序，很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候)进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名: 'filesort ') 。

mysql> explain select * from s1 order by commom_field limit 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 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 temporary

在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含DISTINCT 、GROUP BY、UNION等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示Using temporary提示。

建立与维护临时表要付出很大的成本。

mysql> explain select distinct commom_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)