一、Explain 概述

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

1、MySQL查询过程

通过explain我们可以获得以下信息：

• 表的读取顺序；
• 数据读取操作的操作类型；
• 哪些索引可以被使用；
• 哪些索引真正被使用；
• 表的直接引用；
• 每张表的有多少行被优化器查询了；

Explain使用方式: explain+sql语句, 通过执行explain可以获得sql语句执行的相关信息。

explain select * from L1;

二、Explain 详解

1) 数据准备

-- 创建数据库 
CREATE DATABASE test_explain CHARACTER SET 'utf8'; 

-- 创建表 
CREATE TABLE L1(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L2(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) ); 
CREATE TABLE L3(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) ); 
CREATE TABLE L4(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) ); 

-- 每张表插入3条数据 
INSERT INTO L1(title) VALUES('zhang001'),('zhang002'),('zhang003'); 
INSERT INTO L2(title) VALUES('zhang004'),('zhang005'),('zhang006'); 
INSERT INTO L3(title) VALUES('zhang007'),('zhang008'),('zhang009'); 
INSERT INTO L4(title) VALUES('zhang010'),('zhang011'),('zhang012');

2) ID字段说明

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

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

EXPLAIN SELECT * FROM L1,L2,L3 WHERE L1.id=L2.id AND L2.id = L3.id;

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

EXPLAIN SELECT * FROM L2 WHERE id = ( SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title = 'zhang'));

3) select_type和table字段说明

表示查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询

simple : 简单的select查询，查询中不包含子查询或者UNION

EXPLAIN SELECT * FROM L1 where id = 1;

primary : 查询中若包含任何复杂的子部分，最外层查询被标记

subquery : 在select或where列表中包含了子查询

EXPLAIN SELECT * FROM L2 WHERE id = ( SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title = 'zhang'));

union : union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union

derived : 在from列表中包含的子查询被标记为derived（派生表），MySQL会递归执行这些子查询，把结果放到临时表中

union result : UNION 的结果

EXPLAIN SELECT * FROM (select * from L3 union select * from L4)a;

4) type字段说明

type字段在 MySQL 官网文档描述如下：

The join type. For descriptions of the difffferent types.

type字段显示的是连接类型 ( join type表示的是用什么样的方式来获取数据)，它描述了找到所需数据所使用的扫描方式, 是较为重要的一个指标。

下面给出各种连接类型,按照从最佳类型到最坏类型进行排序:

-- 完整的连接类型比较多 
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 

-- 简化之后,我们可以只关注一下几种 
system > const > eq_ref > ref > range > index > ALL

一般来说,需要保证查询至少达到 range级别,最好能到ref,否则就要就行SQL的优化调整

下面介绍type字段不同值表示的含义:

system: 表中就仅有一行数据的时候. 这是const连接类型的一个特例,很少出现。

const: const表示命中主键索引(primary key) 或者唯一索引(unique),表示通过索引一次就找到数据记录。因为只匹配一条记录,所以被连接的部分是一个常量。(如果将主键放在 where条件中, MySQL就能将该查询转换为一个常量) 这种类型非常快。

例如以下查询:

explain select * from L1 where id = 3;

-- 为L1表的title字段添加唯一索引
alter table L1 add unique(title);

explain select * from L1 where title = 'zhang';

eq_ref : 对于前一个表中的每个一行，后表只有一行被扫描。除了system和const类型之外，这是最好的连接类型。只有当联接使用索引的部分都是主键或惟一非空索引时，才会出现这种类型。

例如以下查询：

EXPLAIN SELECT L1.id,L1.title FROM L1 left join L2 on L1.id = L2.id;

ref : 非唯一性索引扫描(使用了普通索引), 对于前表的每一行(row)，后表可能有多于一行的数据被扫描,它返回所有匹配某个单独值的行。

例如以下查询:

-- 为L1表的title字段添加普通索引
alter table L1 add index idx_title (title) ;
EXPLAIN SELECT * FROM L1 inner join L2 on L1.title = L2.title;

range : 索引上的范围查询,检索给定范围的行,between，in函数，> 都是典型的范围(range)查询。

例如以下查询:

EXPLAIN SELECT * FROM L1 WHERE L1.id between 1 and 10;

注: 当in函数中的数据很大时,可能会导致效率下降,最终不走索引

index : 出现index 是 SQL 使用了索引, 但是没有通过索引进行过滤,需要扫描索引上的全部数据 (查找所有索引树,比ALL快一些,因为索引文件要比数据文件小 ), 一般是使用了索引进行排序分组。

EXPLAIN SELECT * FROM L2 group by id order by id;

-- 该count查询需要通过扫描索引上的全部数据来计数
EXPLAIN SELECT count(*) FROM L2;

ALL : 没有使用到任何索引, 连接查询时对于前表的每一行，后表都要被全表扫描。

EXPLAIN SELECT * FROM L3 inner join L4 on L3.title = L4.title;

总结各类type类型的特点:

5) possible_keys 与 key说明

possible_keys

显示可能应用到这张表上的索引, 一个或者多个. 查询涉及到的字段上若存在索引, 则该索引将被列出, 但不一定被查询实际使用.

key

实际使用的索引，若为null，则没有使用到索引。（两种可能，1.没建立索引, 2.建立索引，但索引失效）。查询中若使用了覆盖索引，则该索引仅出现在key列表中。

1. 理论上没有使用索引,但实际上使用了

EXPLAIN SELECT L3.id FROM L3;

2. 理论和实际上都没有使用索引

EXPLAIN SELECT * FROM L3 WHERE title = 'zhang007';

3. 理论和实际上都使用了索引

EXPLAIN SELECT * FROM L2 WHERE title = 'zhang004';

6) key_len字段说明

表示索引中使用的字节数, 可以通过该列计算查询中使用索引的长度。

key_len 字段能够帮你检查是否充分利用了索引， ken_len 越长, 说明索引使用的越充分。

创建表

CREATE TABLE L5(
	a INT PRIMARY KEY,
	b INT NOT NULL,
	c INT DEFAULT NULL,
	d CHAR(10) NOT NULL
);

使用explain 进行测试

EXPLAIN SELECT * FROM L5 WHERE a > 1 AND b = 1;

观察key_len的值, 索引中只包含了1列 是int类型 ,所以,key_len是4字节。

为b字段添加索引,进行测试

ALTER TABLE L5 ADD INDEX idx_b(b);

-- 执行SQL,这次将b字段也作为条件
EXPLAIN SELECT * FROM L5 WHERE a > 1 AND b = 1;

为c、d字段添加联合索引,然后进行测试

ALTER TABLE L5 ADD INDEX idx_c_b(c,d); 

explain select * from L5 where c = 1 and d = '';

c字段是int类型 4个字节, d字段是 char(10)代表的是10个字符相当30个字节

数据库的字符集是utf8 一个字符3个字节,d字段是 char(10)代表的是10个字符相当30个字节,多出的一个字节用来表示是联合索引

下面这个例子中,虽然使用了联合索引,但是可以根据ken_len的长度推测出该联合索引只使用了一部分,没有充分利用索引,还有优化空间。

explain select * from L5 where c = 1 ;

7) ref 字段说明

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

L1.id='1'; 1是常量 , ref = const

EXPLAIN SELECT * FROM L1 WHERE L1.id=1;

8) rows 字段说明

表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数。

L3中的title没有添加索引, 所以L3中有3条记录,就需要读取3条记录进行查找。

EXPLAIN SELECT * FROM L3,L4 WHERE L3.id = L4.id AND L3.title LIKE 'zhang007';

需要注意的是 rows只是一个估算值,并不准确 .所以rows行数过大的问题并不值得过多考虑，主要分析的还是索引是否使用正确了

9) fifiltered 字段说明

它指返回结果的行占需要读到的行(rows列的值)的百分比。

10) extra 字段说明

Extra 是 EXPLAIN 输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息。

准备数据

CREATE TABLE users (
uid INT PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(20),
age INT(11)
);

INSERT INTO users VALUES(NULL, 'lisa',10);
INSERT INTO users VALUES(NULL, 'lisa',10);
INSERT INTO users VALUES(NULL, 'rose',11);
INSERT INTO users VALUES(NULL, 'jack', 12);
INSERT INTO users VALUES(NULL, 'sam', 13);

Using fifilesort

执行结果Extra为 Using filesort ，这说明，得到所需结果集，需要对所有记录进行文件排序。这类SQL语句性能极差，需要进行优化。

典型的，在一个没有建立索引的列上进行了order by，就会触发fifilesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。

EXPLAIN SELECT * FROM users ORDER BY age;

Using temporary

表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

EXPLAIN SELECT COUNT(*),uname FROM users GROUP BY uname;

需要注意的是：

1. 返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需要进行优化；
2. 使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接类型）来综合判断。例如本例查询的 age 未设置索引，所以返回的type为ALL，仍有优化空间，可以建立索引优化查询。

EXPLAIN SELECT * FROM users WHERE age=10;

Using index

表示直接访问索引就能够获取到所需要的数据(覆盖索引) , 不需要通过索引回表。

-- 为uname创建索引
alter table users add index idx_uname(uname);
EXPLAIN SELECT uid,uname FROM users WHERE uname='lisa';

Using join buffffer

使用了连接缓存, 会显示join连接查询时,MySQL选择的查询算法。

EXPLAIN SELECT * FROM users u1 LEFT JOIN (SELECT * FROM users WHERE age = 1)u2 ON u1.age = u2.age;

Using join buffer (Block Nested Loop) 说明，需要进行嵌套循环计算, 这里每个表都有五

条记录，内外表查询的type都为ALL。

问题在于 两个关联表的关联使用了字段 age，并且age字段未建立索引，就会出现这种情况。

Using index condition

查找使用了索引 (但是只使用了一部分,一般是指联合索引)，但是需要回表查询数.

explain select * from L5 where c > 10 and d = '';

Extra主要指标的含义(有时会同时出现)

• using index ：使用覆盖索引的时候就会出现；
• using where ：在查找使用索引的情况下，需要回表去查询所需的数据；
• using index condition ：查找使用了索引，但是需要回表查询数据；
• using index & using where ：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据；