Explain 性能分析（一）

一 概念

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

用法： Explain+SQL 语句。 

Explain 执行后返回的信息如下

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+

功能

• 表的读取顺序

• 数据读取操作的操作类型

• 哪些索引可以使用

• 哪些索引被实际使用

• 表之间的引用

• 每张表有多少行被优化器查询

二 Explain 脚本准备

CREATE TABLE t1(
    id INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL ,
    PRIMARY KEY (id)
);
CREATE TABLE t2(
    id INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL ,
    PRIMARY KEY (id)
);
CREATE TABLE t3(
    id INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL ,
    PRIMARY KEY (id)
);
CREATE TABLE t4(
    id INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL ,
    PRIMARY KEY (id)
);

INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));
INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));
INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)))

三 id

select 查询的序列号,包含一组数字，表示查询中执行 select 子句或操作表的顺序。

它是解决表的读取顺序问题。

1 id 相同，执行顺序由上至下

mysql> explain select * from t1,t2,t3 where t1.id=t2.id and t2.id=t3.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL       |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db01.t1.id |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | t3    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db01.t1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
3 rows in set, 1 warning (0.00 sec)

执行顺序是 t1，t2，t3

2 id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

mysql> explain select t1.id from t1 where t1.id in
    ->  (select t2.id from t2 where t2.id in
    ->    (select t3.id from t3 where t3.content='')
    ->  );

执行顺序是 t3，t2，t1

3 有相同也有不同

mysql> explain select t2.* from t2,( select * from t3 where t3.content='') s3 where s3.id=t2.id;

id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行。

衍生 = DERIVED

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

执行顺序是：t3，<derived2>=s3, t2

四  select_type

select_type 代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

它是解决数据读取操作的操作类型问题。

官方定义类型

翻译后

select_type 属性	含义
SIMPLE	简单的 select 查询,查询中不包含子查询或者 UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为 Primary
DERIVED	在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)， MySQL 会递归执行这些子查询, 把结果放在临时表里。
SUBQUERY	在 SELECT 或 WHERE 列表中包含了子查询
DEPEDENT SUBQUERY	在 SELECT 或 WHERE 列表中包含了子查询,子查询基于外层
UNCACHEABLE SUBQUERY	无法使用缓存的子查询
UNION	若第二个 SELECT 出现在 UNION之 后，则被标记为 UNION；  若 UNION 包含在 FROM 子句的子查询中,外层 SELECT 将被标记为：DERIVED
UNION RESULT	从 UNION 表获取结果的SELECT

常见类型

1  SIMPLE

SIMPLE 代表简单的 select 查询，查询中不包含子查询或者 UNION。

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)

2  PRIMARY

查询中若包含任何复杂的子部分，最外层查询则被标记为 Primary。

mysql> explain select * from (select t1.content from t1) a;

3  DERIVED

在 FROM 列表中包含的子查询被标记为 DERIVED(衍生),MySQL 会递归执行这些子查询, 把结果放在临时表里。

mysql> explain select * from (select t1.content from t1) a;

4 SUBQUERY

在 SELECT 或 WHERE 列表中包含了子查询。

mysql> explain select t2.id from t2 where t2.id = (select t3.id from t3 where t3.id =2);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | t2    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
|  2 | SUBQUERY    | t3    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

5  DEPENDENT SUBQUER

在 SELECT 或 WHERE 列表中包含了子查询,子查询基于外层。

mysql> explain select t2.id from t2 where t2.id in (select t3.id from t3 where t3.content ='hello');

都是 where 后面的条件，subquery 是单个值，dependent subquery 是一组值。

6  UNCACHEABLE SUBQUREY

无法使用缓存的子查询。

mysql> explain select * from t3 where id =(select id from t2 where t2.id=@@sort_buffer_size);
+----+----------------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type          | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+----------------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | PRIMARY              | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
|  2 | UNCACHEABLE SUBQUERY | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+----------------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
2 rows in set, 1 warning (0.00 sec)

当使用了@@来引用系统变量的时候，不会使用缓存。

7 UNION 

若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION；若 UNION 包含在 FROM 子句的子查询中,外层 SELECT 将被标记为：DERIVED。

mysql> explain select t2.id,t2.content from t2 union all select t3.id,t3.content from t3;

8  UNION RESULT

从 UNION 表获取结果的 SELECT。

mysql> explain select t2.id,t2.content from t2 union all select t3.id,t3.content from t3;