MySQL性能优化

前言

数据库调优的优化步骤

定位查询慢的SQL：慢查询日志

MySQL的慢查询日志，用于记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time（默认值为10s）的SQL，会被记录到慢查询日志中。主要是用于帮助我们发现那些执行时间特别长的SQL查询。

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动设置参数。如果不是调优需要的话，一般不建议启动该参数，因为慢日志会或多或少带来性能影响。

1. 开启慢查询日志

1.查看慢查询日志是否开启

show variables like '%slow_query_log%';

开启慢查询日志

set global slow_query_log='ON';

2.修改long_query_time阈值

接下来我们来看下慢查询的时间阈值，使用如下命令：

show variables like '%long_query_time%';

+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

修改慢查询日志时间

set global long_query_time=5;

再次查询

+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

注意到修改的时间并没有生效，这是因为修改后只对新会话有效。我们打开新会话后查询

+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+

补充：永久生效的方式

修改配置文件，在[mysqld]下添加

[mysqld]
slow_query_log=ON
slow_query_log_file=/var/lib/mysql/atguigu-slow.log # 执行慢查询的目录和文件信息
long_query_time=3 #慢查询阈值
log_output=FILE

不指定文件名，默认文件名为hostname-slow.log

2. 查询慢查询数目

查询当前系统中有多少条慢查询记录

show global status like '%Slow_queries';

3. 案例演示

建表

CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

创建函数

DELIMITER //
CREATE FUNCTION rand_string(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 ;

#测试
SELECT rand_string(10);

如果出现This function has none of DETERMINISTIC......错误，开启允许创建的命令：

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效

继续

DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;

#测试：
SELECT rand_num(10,100);

创建存储过程

DELIMITER //
CREATE PROCEDURE insert_stu1( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student (stuno, NAME ,age ,classId ) VALUES
((START+i),rand_string(6),rand_num(10,100),rand_num(10,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;

调用存储过程

#调用刚刚写好的函数, 4000000条记录,从100001号开始
CALL insert_stu1(100001,4000000);

4. 测试与分析

1. 测试

select * from student where stuno=1231231;

+---------+---------+--------+------+---------+
| id      | stuno   | name   | age  | classId |
+---------+---------+--------+------+---------+
| 1131230 | 1231231 | yMmarJ |   22 |     805 |
+---------+---------+--------+------+---------+
1 row in set (1.52 sec)

可以看到查询时间达到秒级了，非常慢，下个小节分析。

2. 分析

show status like 'slow_queries';

5. 慢查询日志分析：mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow 。

查看mysqldumpslow的帮助信息

mysqldumpslow --help

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

mysqldumpslow命令的具体参数如下：

• -a: 不将数字抽象成N，字符串抽象成S

• -s: 是表示按照何种方式排序：

  • c: 访问次数

  • l: 锁定时间

  • r: 返回记录

  • t: 查询时间

  • al:平均锁定时间

  • ar:平均返回记录数

  • at:平均查询时间 （默认方式）

  • ac:平均查询次数

• -t: 即为返回前面多少条的数据；

• -g: 后边搭配一个正则匹配模式，大小写不敏感；

举例：按照时间排序，查看前5条SQL语句

mysqldumpslow -s t -t 5 /var/lib/mysql/bb18a6c9eccc-slow.log

6. 关闭慢查询日志

方式一：永久性方式

MySQL服务器停止慢查询日志功能有两种方法：

slow_query_log=OFF

方式二：临时性方式

set global slow_query_log=off;

7. 删除慢查询日志

手动删除

先执行下面的语句，然后手动删除。

SHOW VARIABLES LIKE 'slow_query_log%';

重建日志文件

使用命令mysqladmin flush-logs来重新生成查询日志文件

mysqladmin -uroot -p flush-logs slow

提示

慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意，一旦执行了这个命令，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，就必须事先备份。

查询SQL执行成本：SHOW PROFILE

show profile是MySQL提供的可以用来分析当前会话中SQL都做了什么、什么的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。

查看是否开启

show variables like 'profiling';

启动

set profiling=on;

查看当前会话有哪些开销

show profiles;

+----------+------------+----------------------------------------------+
| Query_ID | Duration   | Query                                        |
+----------+------------+----------------------------------------------+
|        9 | 0.00007625 | show profilel                                |
|       10 | 0.00022475 | select version()                             |
|       11 | 1.58368275 | select * from student where stuno=123123     |
+----------+------------+----------------------------------------------+

查看最近一次开销

show profile;

通过指定Query_ID，可以指定查询某一个

show profile for query 11;

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000176 |
| checking permissions | 0.000048 |
| Opening tables       | 0.000058 |
| init                 | 0.000045 |
| System lock          | 0.000028 |
| optimizing           | 0.000044 |
| statistics           | 0.000017 |
| preparing            | 0.000032 |
| executing            | 0.000006 |
| Sending data         | 1.582986 |
| end                  | 0.000023 |
| query end            | 0.000036 |
| closing tables       | 0.000011 |
| freeing items        | 0.000050 |
| logging slow query   | 0.000075 |
| cleaning up          | 0.000049 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)

分析查询语句：EXPLAIN

1. 概述

定位了查询慢的SQL之后，我们就可以是使用EXPLAIN或DESCRIBE工具查看某个查询语句的具体执行计划，帮助我们有针对性的提升查询语句的性能。

2. 基本语法

EXPLAIN 或 DESCRIBE语句的语法形式如下：

EXPLAIN SELECT select_options
或者
DESCRIBE SELECT select_options

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

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

在这里把它们都列出来只是为了描述一个轮廓，让大家有一个大致的印象。

3. 数据准备

1. 建表

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;

2. 设置参数 log_bin_trust_function_creators

创建函数，假如报错，需开启如下命令：允许创建函数设置：

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

3. 创建函数

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 ;

4. 创建存储过程

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 ;

5. 调用存储过程

CALL insert_s1(10001,10000);
CALL insert_s2(10001,10000);

4. EXPLAIN各列作用

1. table

不论我们的查询语句有多复杂，里边儿 包含了多少个表 ，到最后也是需要对每个表进行单表访问的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法**，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。

2. id

例1：

 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)

例2：

EXPLAIN SELECT * FROM s1 INNER JOIN 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 | 9858 |   100.00 | NULL                                  |
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9874 |   100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.00 sec)

虽然查了两张表，但是是一个SELECT

列3：

 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 | 9874 |   100.00 | Using where |
|  2 | SUBQUERY    | s2    | NULL       | index | idx_key1      | idx_key1 | 303     | NULL | 9858 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

例4：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE 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               | 9874 |   100.00 | Using where                        |
|  1 | SIMPLE      | s2    | NULL       | ref  | idx_key2      | idx_key2 | 5       | atguigudb1.s1.key1 |    1 |    10.00 | Using index condition; Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+--------------------+------+----------+------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

可以看到，这里用了子查询，有两个SELECT，但为什么显示相同的id呢，原因是优化器进行了优化。

例5：

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 | 9874 |   100.00 | NULL            |
|  2 | UNION        | s2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9858 |   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)

例6：

 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 | 9874 |   100.00 | NULL  |
|  2 | UNION       | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9858 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

小结：

• id如果相同，可以认为是一组，从上往下顺序执行
• 在所有组中，id值越大，优先级越高，越先执行
• 关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好，即id越少越好

3. select_type

一条大的查询语句里边可以包含若干个SELECT关键字，每个SELECT关键字代表着一个小的查询语句，而每个SELECT关键字的FROM子句中都可以包含若干张表（这些表用来做连接查询），每一张表都对应着执行计划输出中的一条记录，对于在同一个SELECT关键字中的表来说，它们的id值是相同的。

MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色。我们看一下select_type都能取哪些值，请参考官方文档。

名称	描述
SIMPLE	简单的SELECT（不使用UNION或子查询）
PRIMARY	最外层的SELECT
UNION	UNION中的第二个或后续的SELECT语句
UNION RESULT	UNION的结果
SUBQUERY	子查询中的第一个SELECT
DEPENDENT SUBQUERY	依赖于外部查询的子查询中的第一个SELECT
DEPENDENT UNION	依赖于外部查询的UNION中的第二个或后续的SELECT语句
DERIVED	衍生表
MATERIALIZED	材料化子查询
UNCACHEABLE SUBQUERY	无法缓存结果的子查询，在外部查询的每一行中都必须重新评估
UNCACHEABLE UNION	属于无法缓存结果的子查询的UNION中的第二个或后续的SELECT语句

SIMPLE

是最简单的了

PRIMARY

UNION

UNION RESULT

多见于union 查询，会把最后结果放入中间表。所以用这个类型表示

   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 | 9874 |   100.00 | NULL            |
  |  2 | UNION        | s2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9858 |   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)

SUBQUERY

子查询

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 | 9874 |   100.00 | Using where |
  |  2 | SUBQUERY    | s2    | NULL       | index | idx_key1      | idx_key1 | 303     | NULL | 9858 |   100.00 | Using index |
  +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
  2 rows in set, 1 warning (0.00 sec)

DEPENDENT SUBQUERY

   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               | 9874 |   100.00 | Using where |
  |  2 | DEPENDENT SUBQUERY | s2    | NULL       | ref  | idx_key2,idx_key1 | idx_key2 | 5       | atguigudb1.s1.key2 |    1 |    10.00 | Using where |
  +----+--------------------+-------+------------+------+-------------------+----------+---------+--------------------+------+----------+-------------+
  2 rows in set, 2 warnings (0.00 sec)

如果包含子查询的查询语句不能转化为对应的semi-join形式（连表查询。优化器可以把子查询优化为连表查询），并且该子查询就是相关子查询。

值得注意的是，select_type为DEPENDENT SUBQUERY的查询可能会被执行多次

DEPENDENT UNION

  EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 ='a' UNION SELECT key1 FROM s1 WHERE 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  | 9874 |   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)

在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION。

DERIVED

  EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1\G

  *************************** 1. row ***************************
             id: 1
    select_type: PRIMARY
          table: <derived2>
     partitions: NULL
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 9874
       filtered: 33.33
          Extra: Using where
  *************************** 2. row ***************************
             id: 2
    select_type: DERIVED
          table: s1
     partitions: NULL
           type: index
  possible_keys: idx_key1
            key: idx_key1
        key_len: 303
            ref: NULL
           rows: 9874
       filtered: 100.00
          Extra: Using index
  2 rows in set, 1 warning (0.00 sec)

对于包含派生表的查询，该派生表对应的子查询的select_type就是DERIVED。

MATERIALIZE

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2)\G

  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: s1
     partitions: NULL
           type: ALL
  possible_keys: idx_key1
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 9874
       filtered: 100.00
          Extra: Using where
  *************************** 2. row ***************************
             id: 1
    select_type: SIMPLE
          table: <subquery2>
     partitions: NULL
           type: eq_ref
  possible_keys: <auto_key>
            key: <auto_key>
        key_len: 303
            ref: atguigudb1.s1.key1
           rows: 1
       filtered: 100.00
          Extra: NULL
  *************************** 3. row ***************************
             id: 2
    select_type: MATERIALIZED
          table: s2
     partitions: NULL
           type: index
  possible_keys: idx_key1
            key: idx_key1
        key_len: 303
            ref: NULL
           rows: 9858
       filtered: 100.00
          Extra: Using index
  3 rows in set, 1 warning (0.00 sec)

当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的select_type属性就是MATERIALIZED

UNCACHEABLE SUBQUERY

不常用，就不多说了。

UNCACHEABLE UNION

不常用，就不多说了。

4. partitions

创建分区表，按照id分区，id<100 p0分区，其他p1分区

CREATE TABLE user_partitions (id INT auto_increment,
NAME VARCHAR(12),PRIMARY KEY(id))
PARTITION BY RANGE(id)(
PARTITION p0 VALUES less than(100),
PARTITION p1 VALUES less than MAXVALUE
);

DESC SELECT * FROM user_partitions WHERE id<90\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_partitions
   partitions: p0
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

可以看到分区与我们想要的一致，为p0

DESC SELECT * FROM user_partitions WHERE id>200\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_partitions
   partitions: p1
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

分区为p1

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。

 CREATE TABLE t(i INT) ENGINE=MYISAM;
 INSERT INTO t VALUES(1);

EXPLAIN SELECT * FROM t\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

可以看到对于我们创建的表t，表中就只有一条记录，查看执行计划，type就是system。如果我们再插入一条数据，type就是ALL了。

执行引擎换成 INNODB 可以看到，type是ALL。（没有索引，也没有统计数据精准的存储引擎。）

const

当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const。举例：

主键

 EXPLAIN SELECT * FROM s1 WHERE id = 10005\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: const
			...

唯一二级索引

EXPLAIN SELECT * FROM s1 WHERE key2 = 10066\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: const
			...

普通索引

EXPLAIN SELECT * FROM s1 WHERE key1 = '10066'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ref
			...

可以看到 key1 是普通索引，这里的 type 就不是 const 。

eq_ref

执行连接查询时，如果被驱动表是通过主键或者不允许存储 NULL 值的唯一二级索引等值匹配的方式进行访问的（如果该主键或者不允许存储 NULL 值的唯一二级索引是联合索引，则所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref 。比如：

主键

 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s2
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
     		...
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      		...

从执行计划的结果可以看出，MySQL 打算将 s2 作为驱动表，将 s1 作为被驱动表。可以看到 s1 的访问方法是 eq_ref，表明在访问 s1 表时，可以通过主键的等值匹配来访问。

不允许存储 NULL 值的唯一二级索引

我们创建表的时候表 s1 和表 s2 的 key2 都允许为 NULL，先看一下为 NULL 的情况：

 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key2 = s2.key2\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s2
   partitions: NULL
         type: ref
     		...

这里的驱动表是 s1，被驱动表是 s2，由于 key2 可以为空，所以这里的 type 并不是 eq_ref。 我们手动修改一下，再执行一次。

alter table s1 modify column key2 int  not null;
alter table s2 modify column key2 int  not null;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
			...
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s2
   partitions: NULL
         type: eq_ref
			...

发现和描述中的一样。

恢复状态

alter table s1 modify column key2 int null;
alter table s2 modify column key2 int null;

ref

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

 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ref
         	...

ref_or_null

当对普通二级索引进行等值匹配查询，该索引列的值也可以是 NULL 值时，那么对该表的访问方法就可能是 ref_or_null。

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ref_or_null
			...

index_merge

单表访问方法时在某些场景下可以使用 Intersection、Union、Sort-Union 这三种索引合并的方式来执行查询。

 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: index_merge
possible_keys: idx_key1,idx_key3
          key: idx_key1,idx_key3
      key_len: 303,303
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using union(idx_key1,idx_key3); Using where
1 row in set, 1 warning (0.00 sec)

可以看到使用了索引合并。

unique_subquery

unique_subquery 是针对在一些包含 IN 子查询的查询语句中，如果查询优化器决定将 IN 子查询转换为 EXISTS 子 查询，而且子查询可以使用到主键或者不允许存储 NULL 值的唯一二级索引进行等值匹配的话，那么该子查询执行计划的 type，列的值就是unique_subquery。比如：

EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 WHERE s1.key1 = s2.key1) OR key3 = 'a'\G

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: s1
   partitions: NULL
         type: ALL
			...
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: s2
   partitions: NULL
         type: unique_subquery
			...

若修改两个表中的 key2，变为非空，然后执行

EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT key2 FROM key2 WHERE s1.key1 = s2.key1) OR key3 = 'a'\G

也可以发现使用了 unique_subquery。

range

如果使用索引获取某些范围区间的记录，那么就可能使用到 range 访问方法。

EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c')\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: range
			...

EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';# 效果同上

index

当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index。

EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: index
			...

all

最熟悉的全表扫描

EXPLAIN SELECT * FROM s1;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
			...

6. possible_keys 和 key

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

7. key_len

在了解 key_len 之前，我们先要明白两个概念：扫描区间和边界条件。

对于查询来说，最简单粗暴的方式就是全表扫描，但这样太慢了，因此我们可以利用 B+ 树查找索引列值等于某个值的记录，这样可以减少需要扫描记录的数量。由于 B+ 树叶子节点中的记录是按照索引列值从小到大的顺序排序的，所以只扫描某个区间或者某些区间中的记录也可以明显减少需要扫描的记录数量。比如：SELECT * FROM s1 WHERE key1 < 'z' AND key1 > 'a';通过这个语句我们可以利用索引快速找到 key1 值在（a，z）区间的所有记录。我们把（a，z）称为扫描区间，把形成这个区间的搜索条件（key1 < ‘z’ AND key1 > ‘a’）称为边界条件（边界条件并不是都用大于小于号，用了where，where中用到了索引）。

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ref
possible_keys: idx_key1
          key: idx_key1
      key_len: 303
         	...

边界条件中的列都维护了一个 key_len值。该值由 3 部分组成：

• 该列的实际数据最多占用的存储空间长度。对于固定长度类型的列来说，比如说 INT 类型，最多 4 字节。对于变长类型则要看所使用的字符集，如果是 utf8，类型为 VARCHAR(100)的列，实际数据占用的存储空间长度就是一个字符最多占用的字节数乘以该类型最多可以存储的字符数的积，也就是 3 × 100 = 300 字节。
• 如果该列可以存储 NULL 值，再加 1 字节
• 如果是变长类型的列，会有 2 字节的空间来表示实际长度。

因此 key_len 是 303。

再来看 2 个执行计划：

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' and key_part3 = 'b';

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ref
possible_keys: idx_key_part
          key: idx_key_part
      key_len: 303
          	...

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' and key_part3 = 'b';

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ref
possible_keys: idx_key_part
          key: idx_key_part
      key_len: 606
			...

可以看到第一个执行计划中 key_len 是303，说明只用上了 key_part1 来充当边界条件。

第二个执行计划中 key_len 是606 ，说明 key_part1 和 key_part2 都用上了。

这两个执行计划都使用到了联合索引 idx_key_part，但第二个执行计划利用联合索引利用的最充分。因此当使用联合索引时，key_len 越大越好。

8. ref

当访问方法（type）是 const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery中的其中一个时，ref列展示的就是与索引列进行等值匹配的是内容，可以是常量、列、函数等。

 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ref
possible_keys: idx_key1
          key: idx_key1
      key_len: 303
          ref: const
          	...

ref 是 const，一个常量。

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9964
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s2
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: atguigudb1.s1.id
         	...

ref 是一个列。

 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9964
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s2
   partitions: NULL
         type: ref
possible_keys: idx_key1
          key: idx_key1
      key_len: 303
          ref: func
        	...

ref 是一个函数。

9. rows

预估需要读取的记录条数，越小越好。例子就举了。

10. filtered

MySQL 在计算驱动表扇出式会采用 condition filtering 的策略：

• 如果使用全表扫描的方式来执行单表查询，那么计算驱动表扇出时需要估计出满足全部搜索条件的记录到底有多少条。
• 如果使用索引来执行单表扫描，那么计算驱动表扇出时需要估计出在满足形成索引扫描区间的搜索条件外，还满足其他搜索条件的记录有多少条。

比如下面这个查询：

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: range
possible_keys: idx_key1
          key: idx_key1
      key_len: 303
          ref: NULL
         rows: 375
     filtered: 10.00
   			...

该查询使用 idx_key1 索引来执行查询。条件 key1>‘z’ 用来形成扫描区间，从 rows 列可以看出满足条件的记录有 375 条。filtered 为 10 表明查询优化器预测375条记录中有10.00% 的记录满足 common_field=‘a’ 条件。

对于单表查询来说，filtered 列没有什么帮助，我们更关注驱动表对应的执行计划中 filtered 的值，它决定了被驱动表要执行的次数，所以 filtered 值越小越好。

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: idx_key1
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9964
     filtered: 10.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s2
   partitions: NULL
         type: ref
possible_keys: idx_key1
          key: idx_key1
      key_len: 303
          ref: atguigudb1.s1.key1
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

该执行计划表明 s2 要被驱动大约 996 次。

11. Extra

Extra 列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。通过这些信息我们可以更准确的理解 MySQL 到底将如何执行给定的查询语句。这里我们只介绍一些重要的。

No tables used

当表中没有 FROM 子句时将会提示该额外信息。

EXPLAIN SELECT 1\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
  			...
        Extra: No tables used

Impossible WHERE

查询语句的 WHERE 子句永远为 FALSE 时将会提示该额外信息

EXPLAIN SELECT * from s1 where 1!=1\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   			...
        Extra: Impossible WHERE

Using where

当我们使用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中有针对该表的搜索条件时，在 Extra 列中会提示上述额外信息。

 EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9964
     filtered: 10.00
        Extra: Using where

如果使用索引访问来执行对某个表的查询，并且该语句的 WHERE 子句中有除了该索引包含的列之外的其他搜索条件时，在 Extra 列中也会提示上述额外信息。

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ref
possible_keys: idx_key1
          key: idx_key1
      key_len: 303
          ref: const
         rows: 1
     filtered: 10.00
        Extra: Using where

No matching min/max row

当查询列表处有 MIN 或者 MAX 聚集函数，但是并没有记录符合 WHERE 子句中的搜索条件时，将会提示该额外信息。

EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   			...
        Extra: No matching min/max row

Using index

查询列表以及搜索条件中只包含属于某个索引的列，也就是可以使用覆盖索引的情况下，就会显示该额外信息。

 EXPLAIN SELECT key1,id FROM s1 WHERE key1 = 'a'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ref
possible_keys: idx_key1
          key: idx_key1
      key_len: 303
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index

Using index condition

当使用索引进行查询但索引有不能充分发挥性能时，会使用索引下推进行一定程度上的优化，减少成本。

 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: range
possible_keys: idx_key1
          key: idx_key1
      key_len: 303
          ref: NULL
         rows: 375
     filtered: 100.00
        Extra: Using index condition

Using join buffer

在连接查询的执行过程中，当被驱动表不能有效地利用索引加快访问速度时，MySQL一般会为其分配一块名为连接缓冲区（Join Buffer）的内存块加快查询速度；也就是使用基于块的嵌套循环算法来执行连接查询。

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9964
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9964
     filtered: 10.00
        Extra: Using where; Using join buffer (Block Nested Loop)

针对 s2 表的执行计划中，Extra 列显示了两个提示：

• Using join buffer (Block Nested Loop)：这是因为对表 s2 的访问不能有效利用索引，只好使用 Join Buffer 来减少对 s2 表的访问次数，从而提高性能。
• Using where：可以看出查询语句中有一个 s1.common_field=s2.common_field 条件，因为 s1 是驱动表，s2 是被驱动表，所以在访问 s2 表时，s1.common_field 的值已经确定下来了。因此，实际上查询 s2 表的条件就是”s2.common_field=一个常数“，所以显示 Using where 信息。

Not exists

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

EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9964
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s2
   partitions: NULL
         type: ref
possible_keys: idx_key1
          key: idx_key1
      key_len: 303
          ref: atguigudb1.s1.key1
         rows: 1
     filtered: 10.00
        Extra: Using where; Not exists

Using union(…)

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

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: index_merge
possible_keys: idx_key1,idx_key3
          key: idx_key1,idx_key3
      key_len: 303,303
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using union(idx_key1,idx_key3); Using where

Zero limit

当我们的 LIMIT 子句的参数为 0 时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息。

EXPLAIN SELECT * FROM s1 LIMIT 0;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   			...
        Extra: Zero limit

Using filesort

很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名： filesort ）。如果某个查询需要使用文件排序的方式执行查询，就会在执行计划 Extra 列中显示 Using filesort 提示。

EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9964
     filtered: 100.00
        Extra: Using filesort

Using temporary

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

EXPLAIN SELECT DISTINCT common_field FROM s1;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9964
     filtered: 100.00
        Extra: Using temporary

出现 Using temporary 不是一件好事，最好能用索引替代。

参考资料

1. MySQL是怎样运行的
2. MySQL数据库入门到大牛，mysql安装到优化，百科全书级，全网天花板