数据库调优：性能分析工具EXPLAIN的使用

数据准备

# 表s1
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;
# 表s2
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;

# 函数
DELIMITER //
CREATE FUNCTION rand_string1(n INT)
	RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
        SET i = i + 1;
    END WHILE;
    RETURN return_str;
END //
DELIMITER ;

# 存储过程
DELIMITER //
	CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO s1 VALUES(
        (min_num + i),
        rand_string1(6),
        (min_num + 30 * i + 5),
        rand_string1(6),
        rand_string1(10),
        rand_string1(5),
        rand_string1(10),
        rand_string1(10));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END //
DELIMITER ;

# 存储过程
DELIMITER //
	CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO s2 VALUES(
        (min_num + i),
        rand_string1(6),
        (min_num + 30 * i + 5),
        rand_string1(6),
        rand_string1(10),
        rand_string1(5),
        rand_string1(10),
        rand_string1(10));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END //
DELIMITER ;
# 调用
CALL insert_s1(10001,10000);
CALL insert_s2(10001,10000);

EXPLAIN 基础语法

EXPLAIN SELECT * FROM s1 WHERE id = '10002'

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

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

table

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

# 有些sql语句会查询出三条语句，因为有可能有临时表或中间表的创建
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1

这两条记录的table列分别是s1和s2，用来分别说明对s1表和s2表的访问方法是什么。

---

id

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

每一个SELECT对应一个唯一的id（如果有中间表，id为null），总结：

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

---

select_type

SIMPLE

# 连接查询也是SIMPLE
EXPLAIN SELECT * FROM s1 INNER JOIN s2;

PRIMARY

对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type的值就是PRIMARY,比方说：

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

UNION

对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询意外，其余的小查询的select_type值就是UNION，可以对比上一个例子的效果

UNION RESULT

MySQL 选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT, 例子上边有。

SUBQUERY

包含子查询的查询语句不能够转为对应的semi-join的形式(就是不能优化成多表连接了)，并且该子查询是不相关子查询（相关子查询是子查询会关联外查询的列），查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY，比如下边这个查询：

# SELECT key1 FROM s2该子查询和外查询没有关联，可以独立执行，这样称为不相关子查询
# 整个大查询语句不能转化为多表连接
EXPLAIN SELECT * FROM s1 
WHERE key1 IN (SELECT key1 FROM s2)
OR key3 = 'a';

DEPENDENT SUBQUERY

满足上述SUBQUERY条件，不同于是该子查询是相关子查询，该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY，比如下边这个查询：

# 子查询SELECT key1 FROM s2 WHERE s1.key2 = s2.key2依赖于外查询的表，不能独立执行，是相关子查询
EXPLAIN SELECT * FROM s1 
WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) 
OR key3 = 'a';

DEPENDENT UNION

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

EXPLAIN SELECT * FROM s1 
WHERE key1 IN (
	SELECT key1 FROM s2 WHERE key1 = 'a' 
	UNION 
	SELECT key1 FROM s1 WHERE key1 = 'b');

DERIVED

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

# derived_s1是派生表，查询结果是一个简称
EXPLAIN SELECT * 
FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 
where c > 1;

MATERIALIZED

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

# SELECT key1 FROM s2相当于该子查询结果物化了一张只包含了key1字段的表，去供外查询用
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);

---

partitions (可略)

---

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。比方说我们新建一个MyISAM表，并为其插入一条记录：

CREATE TABLE t(i int) Engine=MyISAM;
INSERT INTO t VALUES(1);
# system，MyISAM会有变量单独记录这唯一的记录(统计数据是精确的)，不会走全表遍历，此时Type就是system
EXPLAIN SELECT * FROM t;

const

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

EXPLAIN SELECT * FROM s1 WHERE key2 = 10036

eq_ref

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

# s1是主表（驱动表），s2是从表（被驱动表）
EXPLAIN SELECT * 
FROM s1 LEFT JOIN s2 
ON s1.id = s2.id;

ref

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

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

fulltext

全文索引

ref_or_null

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

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

index_merge

一般情况下对于某个表的查询只能使用到一个索引，但单表访问方法时在某些场景下可以使用Interseation、union、Sort-Union这三种索引合并的方式来执行查询。我们看一下执行计划中是怎么体现MySQL使用索引合并的方式来对某个表执行查询的：

用到了两个索引去执行

unique_subquery

unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery，比如下边的这个查询语句：

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

index_subquery

index_subquery 与 unique_subquery 类似，只不过访问子查询中的表时使用的是普通的索引

range

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

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

index

当我们可以使用索引覆盖(不用回表)，但需要扫描全部的索引记录时，该表的访问方法就是index，比如这样：

EXPLAIN SELECT key_part2 FROM s2 WHERE key_part3 = 'a';

上述查询中的所有列表中只有key_part2 一个列，而且搜索条件中也只有 key_part3 一个列，这两个列又恰好包含在idx_key_part这个联合索引中，可是搜索条件key_part3不能直接使用该索引进行ref和range方式的访问，只能扫描整个idx_key_part索引的记录，所以查询计划的type列的值就是index。

ALL

最熟悉的全表扫描：

EXPLAIN SELECT * FROM s1;

总结

结果值从最好到最坏依次是:
system>const > eq_ref >ref> fulltext > ref_or_null >index_merge > unique_subquery > index_subquery > range>index>ALL
其中比较重要的几个提取出来〈见上图中的蓝色)。SQL性能优化的目标:至少要达到range级别，要求是ref级别，最好是consts级别。(阿里巴巴开发手册要求)

---

possible_keys和key

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

---

key_len ☆

实际使用到的索引长度 (即：字节数)
帮你检查是否充分的利用了索引，值越大越好，主要针对于联合索引，有一定的参考意义。

---

ref

ref:当使用索引列等值查询时，与索引列进行等值匹配的对象信息。

# ref列的值是const，表明在使用idx_key1索引执行查询时，与key1列作等值匹配的对象是一个常数
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

---

rows ☆

预估的需要读取的记录条数，值越小越好。

---

filtered

某个表经过搜索条件过滤后剩余记录条数的百分比(就是row预读取记录数里面真正需要被查询出来的数目占比)

11. Extra ☆

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

No tables used

# 当查询语句没有FROM子句时将会提示该额外信息，比如：
EXPLAIN SELECT 1;

Impossible WHERE

# 当查询语句的WHERE子句永远为FALSE时将会提示该额外信息
EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

Using where

# 没有针对common_field建立索引，走了全表扫描
# 或者走了索引，但where子句中还有AND其他没有建立索引的字段搜索条件
EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';

No matching min/max row

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

Using index

当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用覆盖索引的情况下，在Extra列将会提示该额外信息。比方说下边这个查询中只需要用到idx_key1而不需要回表操作:

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

Using index condition

有些搜索条件中虽然出现了索引列，但却不能使用到索引

Using filesort

有一些情况下对结果集中的记录进行排序是可以使用到索引的。

EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;

总结

• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• 部分统计信息是估算的，并非精确值

---

MySQL监控分析视图 sys schema

关于MysQL的性能监控和问题诊断，我们一般都从performance_schema中去获取想要的数据，在MySQL5.7.7版本中新增sys schema，它将performance_schema和informatiol_schema中的数据以更容易理解的方式总结归纳为"视图”，其目的就是为了降低查询performance_schema的复杂度，让DBA能够快速的定位问题。下面看看这些库中都有哪些监控表和视图，掌握了这些，在我们开发和运维的过程中就起到了事半功倍的效果。

Sys schema视图摘要

• 主机相关：以host_summary开头，主要汇总了IO延迟的信息。
• Innodb相关：以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息。
• I/o相关：以io开头，汇总了等待I/O、I/O使用量情况。
• 内存使用情况：以memory开头，从主机、线程、事件等角度展示内存的使用情况
• 连接与会话信息：processlist和session相关视图，总结了会话相关信息。
• 表相关：以schema_table开头的视图，展示了表的统计信息。
• 索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况。
• 语句相关：以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息。
• 用户相关：以user开头的视图，统计了用户使用的文件I/O、执行语句统计信息。
• 等待事件相关信息：以wait开头，展示等待事件的延迟情况。

Sys schema视图使用场景

索引情况

#1. 查询冗余索引
select * from sys.schema_redundant_indexes;
#2. 查询未使用过的索引
select * from sys.schema_unused_indexes;
#3. 查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics where table_schema='dbname';

表相关

# 1. 查询表的访问量
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from
sys.schema_table_statistics group by table_schema,table_name order by io desc;
# 2. 查询占用bufferpool较多的表
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10;
# 3. 查看表的全表扫描情况
select * from sys.statements_with_full_table_scans where db='dbname';

语句相关

#1. 监控SQL执行的频率
select db,exec_count,query from sys.statement_analysis
order by exec_count desc;
#2. 监控使用了排序的SQL
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1;
#3. 监控使用了临时表或者磁盘临时表的SQL
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
order by (tmp_tables+tmp_disk_tables) desc;

IO相关

#1. 查看消耗磁盘IO的文件
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;

Innodb 相关

#1. 行锁阻塞情况
select * from sys.innodb_lock_waits;