MySQL 优化工具EXPLAIN

大约 13 分钟

1 查看系统性能参数

在MySQL中，可以使用SHOW STATUS语句查询一些MySQL服务器的性能参数、执行效率。

SHOW [GLOBAL|SESSION] STATUS LIKE '参数'

一些常用的性能参数：

Connections：连接MySQL服务器的次数
Uptime：MySQL服务器运行时间
Slow_queries：慢查询次数
Innodb_rows_read：select查询返回的行数
Innodb_rows_inserted：执行INSERT操作插入的行数
Innodb_rows_updated：执行UPDATE操作更新的行数
Innodb_rows:deleted：执行DELETE语句删除的行数
Com_insert：查询操作的次数
Com_insert：插入操作的次数。批量插入算一次
Com_update：更新操作的次数
Com_delete：删除操作的次数

例：

查询MySQL连接次数
```
SHOW STATUS LIKE 'Connections';
```
查询慢查询次数
```
SHOW STATUS LIKE 'Slow_queries';
```

2 统计SQL的查询成本：last_query_cost

一条SQL查询语句在执行前需要确定执行计划，如果存在多种执行计划的话，MySQL会计算每个执行计划所需要的成本、从中选择成本最小的一个作为最终执行的执行计划。

如果我们想要查看某条SQL语句的拆线呢成本，可以在执行玩这条SQL语句之后，通过查看当前会话中的last_query_cost变量值得到当前查询的成本。它通常也是我们评价一个查询的执行效率的常用指标。这个查询成本对应的是SQL语句所需要读取的页的数量

SELECT * FROM TABLE;
SHOW STATUS LIKE 'last_query_cost';

3 定位执行慢的SQL：慢查询日志

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间炒股共long_query_time值的SQL，则会被记录在慢查询日志中。long_query_time的默认值是10。

# 查看慢查询日志是否开启
SHOW VARIABLES LIKE '%slow_query_log';

默认情况下，MySQL数据没有开启慢查询日志，需要手动设置这个参数。如果不需要调优的话，一般不建议启动改参数，因为慢查询日志会带来一定的性能影响

3.1 开启慢查询日志参数

3.1.1 准备数据

创建表

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 ) 
)

创建字符随机数函数

DELIMITER //
CREATE FUNCTION rand_string ( n INT ) RETURNS VARCHAR ( 255 ) #该函数会返回一个字符串
BEGIN
	DECLARE
		chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvWXyZABCDEFJHIJKLMNOPORSTUVWXYZ';
	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 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;

创建插入数据存储过程

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;

设置执行参数，否则可能报错

SET GLOBAL log_bin_trust_function_creators=1;

执行存储过程，插入数据

CALL insert_stu1(10001,400000); // 要稍微等一会

3.1.2 开启slow_query_log

SET GLOBAL slow_query_log = on;

slow_query_log_file 表示慢SQL日志的地址

3.1.3 配置文件方式永久设置

[mysqld]
slow_query_log=ON # 开启慢查询日志
slow_query_log_file=/var/lib/mysql/slow_query.log # 设置慢查询日志地址
long_query_time=3 # 慢查询阈值
log_output=FILE # 慢查询日志设置为文件输出

3.1.4 查看慢查询数目

查看当前慢查询阈值
```
SHOW VARIABLES LIKE '%long_query_time';
```
修改当前session的慢查询阈值（为了演示，只修改当前Session）
```
SET long_query_time = 1;
```
对student表进行count语句查询
```
SELECT count(*) FROM student;
```
查看慢查询次数，
```
SHOW STATUS LIKE 'slow_queries';
```
发现有一次慢查询

3.1.5 慢查询日志分析工具mysqldumpslow

登陆到MySQL服务器

找到慢查询日志文件

SHOW VARIABLES LIKE '%slow_query_log_file';

执行mysqldumpslow命令
找到慢查询sql语句后，就可以采用EXPLAIN分析语句的执行效率。

3.1.6 关闭慢查询sql日志

SET GLOBAL slow_query_log = OFF;

永久关闭，配置文件ON改为OFF；

3.1.7 重置慢查询日志

# 先删除之前的慢查询日志文件
mysqladmin -uroot -p flush-logs slow

4 查看你SQL执行成本：SHOW PFOILE

4.1 检查是否开启

#开启
# SET GLOBAL profiling = ON;
SHOW VARIABLES LIKE 'profiling';

4.2 执行sql

执行查询sql

SELECT * FROM student WHERE id = 12345;
SELECT * FROM student WHERE name = 'Kcerva';

show profiles;
查看具体信息
```
show profile cpu,block io for query 3;
```

show profile 的常用查询参数

ALL：显示所有的开销信息
BLOCK IO：显示块IO的开销
CONTEXT SWITCHES：上下文切换开销
CPU：CPU开销
IPC：发送和接收开销
MEMORY：内存开销
PAGE FAULTS：页面错误开销
SWAPS：交换次数开销

5 分析查询语句：EXPLIAN

5.1 概述

定位了慢查询SQL后，我们就可以使用EXPLIAN工具坐针对性的分析。

MySQL中又专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划（它认为最有的数据检索方式，但不见得是DBA认为最优的，这部分最耗费时间）。

这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。MySQL为我们提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划。

5.2 EXPLAIN能做什么

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

5.3 基本语法

EXPLAIN + sql语句。例：

EXPLAIN SELECT * FROM student WHERE id = 12345
EXPLAIN DELETE FROM student WHERE id = 12345
EXPLAIN UPDATE SET name = 'xxx' WHERE id = 12345

执行EXPLAIN语句并不会真正的执行sql语句

5.4 字段说明

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

为了演示、另外在创建2张表，并插入数据

// 创建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_keyl ( 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;


// 插入数据到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 s2
		VALUES
			(
				( min_num + i ),
				rand_string ( 6 ),
				( min_num + 30 * i + 5 ),
				rand_string ( 6 ),
				rand_string ( 10 ),
				rand_string ( 5 ),
				rand_string ( 10 ),
			rand_string ( 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_string ( 6 ),
				( min_num + 30 * i + 5 ),
				rand_string ( 6 ),
				rand_string ( 10 ),
				rand_string ( 5 ),
				rand_string ( 10 ),
			rand_string ( 10 ));
		UNTIL i = max_num 
	END REPEAT;
	COMMIT;
	
END // 
DELIMITER;

// 调用存储过程、每张表插入10000条数据
CALL insert_s1(10001,10000);
CALL insert_s2(10001,10000);

table

查询的每一行记录都对应着一个单表

EXPLAIN SELECT * FROM s1;
EXPLAIN SELECT * FROM s1 INNER JOIN s2;

id
- id如果相同，可以认为是一组，从上往下执行
- 在所有组中，id值越大、优先级越高、越先执行
- id号的每个号码，表示一趟独立的查询，一个sql的查询趟数越少越好
1 在一个大的查询中每个SELECT关键字都对应一个唯一的id。
```
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2);
```
2 查询优化器可能对涉及子查询的查询语句进行重写，转变为多表关联查询的操作
```
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE comon_field = 'a');
// 执行后发现只有一个id：1
```
3 union 去重
```
EXPLAIN SELECt * FROM s1 UNION SELECT * FROM s2;
EXPLAIN SELECt * FROM s1 UNION ALL SELECT * FROM s2;
// 由于union会进行去重操作，所以多了一个临时表union.1.2
```
select_type
- SIMPLE
  查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型
  连接查询也算作SIMPLE
- PRIMARY
  对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中左边的那个查询就是PRIMARY；
```
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
```
partitions（可略）
代表分区表中的命中情况，非分区表，该项为NULL。
type
执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，又称”访问类型“，其中的type列就表明了这个访问方法是什么，是较为重要的一个指标。
以下排列的性能由高到低。最好的就是system、最差的就是ALL。
- system
  当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比他MyISAM、Memory，那么对该表的访问方法就是system。
- const
  根据主键或者唯一的二级索引列于常数进行等值匹配时、对单表的访问方法就是const。
- eq_ref
  在连接查询时，如果被驱动表时通过主键或者唯一二级索引列等值匹配的方式进行访问的
```
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
```
- ref
  普通的二级索引列与常量值进行等值匹配时查询某个表
```
EXPLAIN SELECT * FROM s1 WHERE key3 = 'a';
```
- fulltext
- ref_or_null
  普通二级索引进行等值匹配查询时，该索引列的值也可以是NULL，那么此时访问方法就可能使ref_or_null
```
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;
```
- index_merge
  单表访问方法时，在某些场景下可以使用Intersection、Union、Sort-Union这三种索引合并的方式来执行查询
```
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
```
- unique_subquery
  针对一些包含IN子查询的语句中，如果查询优化器觉得将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type值就是uniqe_subquery
```
EXPLAIN SELECT * FROM s1 WHERE key2 iN(
    SELECt id FROM s2 WHERE s1.key1 = s2.key1
) OR key3 = 'a';
```
- index_subquery
- range
  如果使用索引获取某些范围的记录
```
EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a','b','c');
EXPLAIN SELECT * FROM s1 WHERE key1 BETWEEN 'a' AND 'b';
```
- index
  当可以使用索引覆盖、但需要扫描全部的索引记录时，该表的访问方法就是index
  索引覆盖：当查询列和WHERE条件中的字段都处于联合索引中，不用进行回表操作。
```
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
```
- ALL
  全表扫描
possible_keys、key
prossible_keys表示在重新语句中、对某个表执行单表查询时可能用到的所有由哪些，一般情况下涉及到字段上若存在所有则都会列出，但不一定使用
key表示实际上用到的索引
```
EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key3 = 'a';
```

key_len

实际使用到的索引长度（字节数）。检查是否充分的利用上了索引。值越大越好。（主要针对与联合索引）

EXPLAIN SELECT * FROM s1 WHERE id= 12345;
EXPLAIN SELECT * FROM s1 WHERE key2 = 12345;
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';
EXPLAIN SELECT * FROM s1 WHERE key_part3 = 'a';

ref

当使用索引列等值匹配时，与索引列进行当中匹配的对象信息。比如只是一个常数或者是某个列

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

rows
预估需要读取的记录条数。值越小越好。
filtered
某个表经过搜索条件过滤后剩余记录条数的百分比。
```
EXPLAIN SELECT * FROM s1 WHERE key1 > 'Z';
EXPLAIN SELECT * FROM s1 WHERE key1 > 'Z' AND common_field = 'a';
```
对于单表查询来说、这个feiltered列的值意义不大。更加关注在多表连接查询中驱动表对应的filtered值，它决定了被驱动表要执行的次数
```
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s1.key1 WHERE common_field = 'a';
```
Extra
额外的一些信息，包括不适合在其他列中显示但十分重要的额外信息。可以通过这些额外信息来更准确的理解MySQL到底将如何执行查询语句。
- No tables used
  当查询语句没有FROM子句时
```
EXPLAIN SELECT 1;
```
- Impossible WHERE
  当查询语句WHERE字节永远为FALSE时
```
EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
```
- Using WHERE
  当使用全表扫描且WHERE子句中有针对该表的搜索条件是
```
EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
```
- No matching min/max row，Select tables optimized away
  当查询语句含有MIN、MAX聚合函数时
```
// 没有满足WHERE子句记录时，
EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = '12345';
```
```
// 有满足WHERE子句记录时,
EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'aaabgD';
```
- Using Index
  当查询的列以及搜索条件中只包含某个索引列。（覆盖索引）不需要回表操作
```
EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';
```
- Using Index Condition
  WHERE条件出现了索引列，但却不能使用索引
```
EXPLAIN SELECT * FROM s1 WHERE key1 > 'Z' AND key1 LIKE '%a';
```
- Using join buffer
  连接查询中、当被驱动表不能有效利用索引时，MySQL一般会为起分配一块``join buffer`的内存块来加快查询速度。
```
EXPLAIN SELECT * FROM s1 INNER JOIN s1 ON s1.common_field = s1.common_field;
```
- Using union(idx1,idx2)
  对多个索引列采用了OR条件，采取索引合并的方式执行查询
```
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'b';
```
- Using fielsort
  对结果集中的记录进行排序，没有索引的列进行排序，此时MySQL在内存（记录少）或者磁盘上（记录多）进行排序操作，速度慢、效率低。
```
EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
```
- Using temporary
  使用GOUR BY、UNION、DISTINCT操作时，没有使用到有效的索引、此时会建立临时表表来执行查询。执行计划中出现Using temporary不是一个好的征兆、建立和维护临时表会付出很大的成本
```
EXPLAIN SELECT DISTINCT common_field FROM s1;
```
注意：
- EXPLAIN 不考虑各种Cache
- EXPLAIN 不能显示MySQL执行查询所作的优化工作
- EXPLAIN 不会告诉关于触发器、存储过程的信息或用户自定义函数对查询的情况
- 部分统计信息时估算的、并非精确的