MySQL性能分析

MySQL性能分析

一、show status

在MySQL中，可以使用 show status 语句查询一些MySQL数据库服务器的 性能参数、执行频率 。

# show status 语法
show [global|session] status like '参数';
1
2

一些常用的性能参数如下：

Connections				连接MySQL服务器的次数。 
Uptime					MySQL服务器的上线时间。 
Slow_queries			慢查询的次数。
Innodb_rows_read		Select查询返回的行数 
Innodb_rows_inserted	执行INSERT操作插入的行数
Innodb_rows_updated		执行UPDATE操作更新的行数
Innodb_rows_deleted		执行DELETE操作删除的行数
Com_select				查询操作的次数。
Com_insert				插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
Com_update				更新操作的次数。
Com_delete				删除操作的次数。
1
2
3
4
5
6
7
8
9
10
11

二、last_query_cost

通过 last_query_cost 可以查看上一个Query的页的数量（需要先执行 select 语句 再执行下列语句）

# 上一次 查询 页的数量
show status like 'last_query_cost';
1
2

三、慢查询日志

MySQL的慢查询日志会记录响应时间超过阈值的语句，方便我们对查询进行优化。

1、慢查询日志的开启

mysql的慢查询日志记录默认是关闭的。

# 查看是否开启慢查询日志（默认关闭）
show variables like '%slow_query_log%';

# 开启慢查询日志（mysql重启后失效）
set global slow_query_log=1;
1
2
3
4
5

如果想要mysql重启后依然生效，需要修改配置文件 my.cnf

# 开启慢查询日志（0关闭，1开启，默认关闭）
slow_query_log=1
# 保存慢查询日志的地址
slow_query_log_file=/usr/local/mysql/logs/slow.log
1
2
3
4

2、慢查询日志的阈值

# 查看慢查询的阈值，单位：s（默认10s）
show variables like 'long_query_time';

# 设置慢查询的阈值，单位：s（重新连接后生效，重启mysql后失效）
set global long_query_time=4;
1
2
3
4
5

如果想要mysql重启后依然生效，需要修改配置文件 my.cnf

# 定义执行时间超过多少秒为慢查询（默认为10）
long_query_time = 4
1
2

3、慢查询日志的相关配置

慢查询日志的开启与阈值

# 开启慢查询日志（0关闭，1开启，默认关闭）
slow_query_log=1

# 保存慢查询日志的地址
slow_query_log_file=/usr/local/mysql/logs/slow.log

# 定义执行时间超过多少秒为慢查询（默认为10）
long_query_time = 4
1
2
3
4
5
6
7
8

默认情况下，慢查询日志不记录 不使用索引进行查询的语句 和 管理语句（如alter table、create index等）

# 是否记录管理语句（0关闭，1开启，默认0）
log_slow_admin_statements=1

# 是否记录不使用索引进行查询的语句（0不记录，1记录。默认不记录）
log_queries_not_using_indexes=1

# 表示每分钟允许记录的不使用索引进行查询的语句的次数（默认为0，不限制）
log_throttle_queries_not_using_indexes=10
1
2
3
4
5
6
7
8

其他慢查询相关的设置

# 扫描少于200行不记录（默认为0）
min_examined_row_limit=200

# 从机是否开启慢查询（0关闭，1开启，默认0）
log_slow_slave_statements=1

# 日志记录形式（'file'表示存入文件，'table'表示存入系统表。默认为file，开销较低）
log_output=file
1
2
3
4
5
6
7
8

4、慢查询日志的模拟

-- 模拟慢查询（这条语句会执行4秒左右）
select sleep(4);

-- 查看有几条慢查询
show global status like '%slow_queries%';
1
2
3
4
5

下图是慢查询日志的记录

主要功能是, 统计不同慢sql的:
1. Time 	 		慢sql执行时间
2. User@Host 		用户信息
3. Query_time		执行查询的时间
4. Lock_time		等待锁的时间
5. Rows_sent		发送给客户端的行总数
6. Rows_examined	扫描的行总数
7. sql		     	执行的sql（会格式化, 比如 limit 1,5 会用 limit N,N 表示）
1
2
3
4
5
6
7
8

5、慢查询日志的分析工具

慢查询日志里往往有许多SQL，不可能一条一条SQL看过去，我们需要将所有的慢SQL分组统计后在进行分析。

以 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

举例：我们想要按照查询时间排序，查看前五条 SQL 语句，这样写即可：

# 查看mysqldumpslow的帮助信息
$ mysqldumpslow -s t -t 5 /usr/local/mysql/logs/slow.log 
Reading mysql slow query log from /usr/local/mysql/logs/slow.log 

Count: 1 Time=2.39s (2s) Lock=0.00s (0s) Rows=13.0 (13), root[root]@localhost 
SELECT * FROM student WHERE name = 'S'

Count: 1 Time=2.09s (2s) Lock=0.00s (0s) Rows=2.0 (2), root[root]@localhost
SELECT * FROM student WHERE stuno = N

Died at /usr/bin/mysqldumpslow line 162, <> chunk 2.
1
2
3
4
5
6
7
8
9
10
11

一些常用的命令参考

# 得到返回记录集最多的10个SQL 
mysqldumpslow -s r -t 10 /usr/local/mysql/logs/slow.log 

# 得到访问次数最多的10个SQL 
mysqldumpslow -s c -t 10 /usr/local/mysql/logs/slow.log 

# 得到按照时间排序的前10条里面含有左连接的查询语句 
mysqldumpslow -s t -t 10 -g "left join" /usr/local/mysql/logs/slow.log 

# 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况 
mysqldumpslow -s r -t 10 /usr/local/mysql/logs/slow.log | more
1
2
3
4
5
6
7
8
9
10
11

四、explain

使用 explain 关键字可以模拟优化器执行SQL语句，分析查询语句 或 表结构的性能瓶颈。

• https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
• https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

具体作用：
	查看表的读取顺序
	数据读取操作的操作类型
	查看哪些索引可以使用
	查看哪些索引被实际使用
	查看表之间的引用
	查看每张表有多少行被优化器执行

使用方式
	在要执行的sql语句之前加上explain关键字即可

版本情况：
	MySQL 5.6.3 以前只能 EXPLAIN SELECT
	MYSQL 5.6.3 以后可以 EXPLAIN SELECT，UPDATE，DELETE

	MySQL 5.7 以前
		想要显示 partitions 需要使用 explain partitions 命令
	    想要显示 filtered 需要使用 explain extended 命令。
	MySQL 5.7 以后
		默认直接显示 partitions 和 filtered 的信息。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

【数据准备】

# 建表
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

# 允许创建函数设置
set global log_bin_trust_function_creators=1;		
1
2

# 创建函数
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 创建往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 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;

# 创建往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_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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

# 调用存储过程，插入1万条记录
CALL insert_s1(10001, 10000);
CALL insert_s2(10001, 10000);
1
2
3

00、Explain 输出列概述

01、id

每个select都对应一个唯一的id，表示查询中select或操作表的顺序（id相同从上往下，id不同值越大优先级越高）

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

# 简单查询
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
1
2

# 内连接
EXPLAIN SELECT * FROM s1 INNER JOIN s2;
1
2

# 子查询
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
1
2

# 被重写的子查询（查询优化器将此处的`子查询`重写为`连接查询`）
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field= 'a');
1
2

# 全连接 union
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
1
2

# 全连接 union all
EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
1
2

02、select_type

1）SIMPLE

简单查询 和 连接查询 (这里包括内连接和外连接) 属于 SIMPLE（查询中未使用 union 或 子查询）

# 简单查询
EXPLAIN SELECT * FROM s1;
1
2

# 连接查询
EXPLAIN SELECT * FROM s1 INNER JOIN s2;
1
2

# 被重写的子查询（查询优化器将此处的`子查询`重写为`连接查询`，因此本质上还是连接查询）
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field= 'a');
1
2

2）PRIMARY

查询中使用了 Union 或 子查询 时，最外层的查询属于 PRIMARY

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
1

3）UNION

查询中使用了 union 时，union 后面的 select 属于 UNION

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
1

4）UNION RESULT

查询中使用了 union 时，因去重对 union的结果集的查询 属于 UNION RESULT（union all不需要去重，就没有）

# 全连接 union（去重）
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
1
2

# 全连接 union all
EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
1
2

5）SUBQUERY

查询中使用了 不相关子查询 时，子查询的select属于 SUBQUERY

• 注意：这里的 不相关子查询 的查询语句不能够转为对应的 semi-join 的形式

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

6）DEPENDENT SUBQUERY

查询中使用了 相关子查询 时，相关子查询的select属于 SUBQUERY（相关子查询：依赖于外部查询的子查询）

• 注意：这里的 相关子查询 的查询语句不能够转为对应的 semi-join 的形式

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

7）DEPENDENT UNION

在包含 union 或 union all 的大查询中，如果各个小查询都依赖于外层查询，则 union 后面的 小查询 属于 DEPENDENT UNION

# 这里看上去没有依赖于外层查询，但是优化器会将这里的in优化为exists，这样就依赖于外层查询了
EXPLAIN SELECT * FROM s1 WHERE key1 IN (
    SELECT key1 FROM s2 WHERE key1 = 'a' 
    UNION 
    SELECT key1 FROM s1 WHERE key1 = 'b'
);
1
2
3
4
5
6

8）DERIVED

对于包含 派生表 的查询，该 派生表 对应的子查询 属于 DERIVED（衍生）

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

9）MATERIALIZED

查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询（物化表）

# 子查询 SELECT key1 FROM s2 被转为了物化表
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);
1
2

03、type ☆

代表MySQL对某个表的执行查询时的访问方法，如下（从好到差）

• 完整的访问方法（从好到差）

• 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

• SQL 性能优化的目标：

• 至少要达到 range 级别，要求是 ref 级别，最好是 consts 级别。

01）system

表中只有一条记录，并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory

• MyISAM有一个变量记录统计数据，是system；而InnoDB是读取数据累加的，因此会是all

create table t1(int i) engine=InnoDB;
insert into t1 value(1);
EXPLAIN select * from t1;	# all
1
2
3

create table t2(int i) engine=MyISAM;
insert into t2 value(1);
EXPLAIN select * from t2;	# system
1
2
3

02）const

根据 主键或唯一索引列 进行 等值匹配（只匹配一行数据）

EXPLAIN SELECT * FROM s1 WHERE id = 10005;
1

03）eq_ref

连接查询时，被驱动表 通过 主键或唯一索引列 等值匹配（对于被驱动表只匹配一行数据）

• 如果改主键或唯一索引时联合索引，则 所有的索引列 都必须进行等值匹配

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

从执行计划可以看出，MySQL将s2作为驱动表，s1作为被驱动表，通过s1的主键等值匹配

04）ref（要求）

通过 普通的二级索引列 等值匹配（可能会找到多个符合条件的行）

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

05）fulltext

通过 全文索引 进行匹配

06）ref_or_null

通过 普通的二级索引列 等值匹配，该索引列的值可以为Null时（可能会找到多个符合条件的行）

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

07）index_merge

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

# 此处如果将or换成and，则只会用到一个索引，则type=ref
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
1
2

08）unique_subquery

在包含 in子查询 的查询语句中，查询优化器 将 in子查询 转化为 exists子查询，并且子查询可以使用到主键索引列进行等值匹配

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

查询优化器 将 in子查询 转化为 exists子查询，并且用到了 s2的主键索引列 和 s1的key2 进行 等值匹配

09）index_subquery

在包含 in子查询 的查询语句中，查询优化器 将 in子查询 转化为 exists子查询，并且子查询可以使用到 索引列 进行等值匹配

EXPLAIN SELECT * FROM s1 
WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';
1
2

查询优化器 将 in子查询 转化为 exists子查询，并且用到了 s2的key3索引列 和 s1的common_field 进行 等值匹配

10）range（底线）

使用 索引 获取某些 范围区间 的记录（between、in、> 和 < 等条件）

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

EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';
1

11）index

查询字段与匹配字段在同一颗索引树上，通过遍历索引树，然后根据索引字段去查询需要的信息（覆盖索引，避免全表扫描）

# 联合索引是 ( key_part1, key_part2, key_part3 ) 根据最左原则，理论上索引会失效
# 但是由于查询的字段和条件的字段在同一颗索引树上（索引覆盖），因此还是用上了索引
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
1
2
3

12）ALL（禁止）

EXPLAIN SELECT * FROM s1;
1

04、possible_keys 和 key

possible_keys是可能使用的索引；key表示实际使用的索引

possible_keys如果有多个，MySQL会比较几个索引，选择最为合适的（因此不是越多越好，多了反而会降低效率）

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

key不一定是possible_keys的子集，例如type=index的那个例子，possible_keys=NULL，但实际使用上了索引

EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
1

05、key_len ☆

实际使用到的索引长度（字节数），可以检查是否充分利用上了索引，相对于字段大小，值越大越好（通常用于联合索引）

• 字符对应字节数：utf8=3、gbk=2、latin1=1
• 允许为NULL需要多加一个字节
• varchar边长字段需要加两个字节（记录变长字段）；char固定字段就不需要

# 主键索引：id INT（int类型占4个字节）
EXPLAIN SELECT * FROM s1 WHERE id = 10005;
1
2

# 唯一索引：key2 INT
# int类型占4个字节，可以为null -> 加上1个字节记录null
EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;
1
2
3

# 普通索引：key1 VARCHAR ( 100 )
# utf8一个字符占3个字节，加上1个字节记录null，加上2个字节记录变长的实际长度（100*3+1+2=303）
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
1
2
3

# 联合索引：key_part1 VARCHAR (100), key_part2 VARCHAR (100), key_part3 VARCHAR (100)
# utf8一个字符占3个字节，加上1个字节记录null，加上2个字节记录变长的实际长度（100*3+1+2=303）
# 虽然和上面一样都是303，但是上面充分利用了索引，这个只利用了三分之一（key_len的比较是相对于索引字段长度的）
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';
1
2
3
4

# 联合索引：key_part1 VARCHAR (100), key_part2 VARCHAR (100), key_part3 VARCHAR (100)
# 同时使用上了key_part1和key_part2，比上面的就更精准一些
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';
1
2
3

06、ref

当使用索引等值查询时，显示与索引列进行等值匹配的对象信息（无索引null / 常数const / 函数func / 库.列.具体字段）

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

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

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

07、rows ☆

根据表统计信息及索引选用情况，预估扫描的行数，每张表有多少行被优化器查询过。（值越小越好）

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';
1

08、filtered

满足查询条件的记录数量的比例（检索结果/扫描行数），值越大越好。（依赖统计信息，并不十分准确）

• 对于单表查询来说，filtered的值没什么意义
• 对于连接查询来说，驱动表对应的filtered值，决定了被驱动表要执行的次数（即：rows * filtered/100 ）

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

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

09、Extra ☆

记录一些额外的信息（可以更准确的理解MySQL到底将如何执行给定的查询语句）

01）No tables used

查询语句没有 from 子句

EXPLAIN SELECT 1;
1

02）Impossible WHERE

where 条件永远不成立

EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
1

03）Using where

使用全表扫描来执行某个表的查询，并且where条件中有针对该表的搜索条件。（where存在无索引的条件列）

# where 无索引列 
EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
1
2

# where 索引列 and 无索引列
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';
1
2

04）No matching min/max row

SQL返回的数据有min或max聚合函数，但是没有符合where搜索条件的记录

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

05）Using index

SQL所需要返回的所有列数据均在一棵索引树上，而无需回表查询。（覆盖索引）

• 如果同时出现 Using where，表明索引被用来执行索引键值的查找
• 如果没有出现 Using where，表明索引被用来读取数据而非执行查找动作

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

06）Using index condition

确实命中了索引，但不是所有的列数据都在索引树上，还需要回表查询。

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

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' 的 Extra 也是 Using index condition

07）Using join buffer (Block Nested Loop)

在连接查询中，当被驱动表不能有效利用索引时，MySQL一般会为其分配一块 join buffer 缓冲区来加快查询速度

• Block Nested Loop Join 优化的思路是不再逐条获取驱动表的数据，而是一块一块的获取，减少IO的次数

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

08）Not exists

使用外连接时，where条件中包含 被驱动表的某个列 IS NULL 的条件，而那个列又不允许存储NULL

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

09）Using union

or连接的两个条件都使用了索引，type=index_merge

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

10）Zero limit

EXPLAIN SELECT * FROM s1 LIMIT 0;
1

11）Using filesort

排序没有使用到索引，只能在内存中（记录较少的时候）或磁盘中（记录较多的时候）进行文件排序

# 排序使用到了索引
EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
1
2

# 排序未使用到索引
EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
1
2

12）Using temporary

存在distinct、group by、union等操作并且不能有效利用到索引时，mysql会创建一个内部的临时表来执行操作

EXPLAIN SELECT DISTINCT common_field FROM s1;
1

EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;
1

EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;
1

从 Extra 的 Using index 的提示里我们可以看出，上述查询只需要扫描 idx_key1 索引就可以搞定了，不再需要临时表了。

13）其它

其它特殊情况这里省略。

10、小结

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

五、show profile

show profile 是mysql提供的 可以用来分析当前会话中sql语句执行的资源消耗情况的工具。

1、是否开启 show profile

show profile 默认关闭，并保存最近15次的运行结果。

# 查看是否开启（默认关闭）
show variables like 'profiling'; 

# 开启show profile（重新连接后生效，重启mysql后失效）
set profiling = 'ON'; 
1
2
3
4
5

2、show profiles

# 查看当前会话的Query及耗时
show profiles;
1
2

3、show profile

# 查看最近一次查询的开销
show profile;
1
2

4、show profile for query

ALL					显示所有的开销信息
BLOCK IO			显示块IO开销
CONTEXT SWITCHES	上下文切换开销
CPU					显示CPU开销信息
IPC 				显示发送和接收开销信息
MEMORY 				显示内存开销信息
PAGE FAULTS 		显示页面错误开销信息
SOURCE				显示和Source_function，Source_file， Source_line相关的开销信息。 
SWAPS 				显示交换次数开销信息
1
2
3
4
5
6
7
8
9

# 查看指定查询语句的开销
show profile cpu, block io for query 4;
1
2

5、show prodile 使用小结

• 开启 show profile

  • show profile默认是关闭的，并且开启后只存活于当前会话，也就说每次使用前都需要开启。

• show profiles

  • 通过 show profiles 查看sql语句的耗时时间

• show profile

  • 通过 show profile 对耗时时间长的sql语句进行诊断。

• 分析show profile的诊断结果

  • Converting heap to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了。
  • Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。
  • Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险!!!
  • Locked

  如果在show profile诊断结果中出现了以上几种情况，则sql语句需要优化。

六、mysql sys schema

	`mysql sys schema` 是MySQL官方提供的一个系统数据库，它包含一些视图和函数，用于提供MySQL服务器和实例的性能监控和状态查询。通过 `mysql sys schema` 可以更方便地了解MySQL服务器的运行情况，进行性能优化和故障排查。
1

0、Sys schema 视图- 摘要

1. 主机相关：以host_summary开头，主要汇总了IO延迟的信息。

2. Innodb相关：以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息。

3. I/O相关：以io开头，汇总了等待I/O、I/O使用量情况。

4. 内存使用情况：以memory开头，从主机、线程、事件等角度展示内存的使用情况

5. 连接与会话信息：processlist和session相关视图，总结了会话相关信息。

6. 表相关：以schema_table开头的视图，展示了表的统计信息。

7. 索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况。

8. 语句相关：以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息。

9. 用户相关：以user开头的视图，统计了用户使用的文件I/O、执行语句统计信息。

10. 等待事件相关信息：以wait开头，展示等待事件的延迟情况。

1、Sys schema 视图- 索引相关

# 查询冗余索引 
select * from sys.schema_redundant_indexes; 

# 查询未使用过的索引 
select * from sys.schema_unused_indexes; 

# 查询索引的使用情况 
select index_name, rows_selected, rows_inserted, rows_updated, rows_deleted 
from sys.schema_index_statistics where table_schema='dbname';
1
2
3
4
5
6
7
8
9

2、Sys schema 视图- 表相关

# 查询表的访问量 
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; 

# 查询占用bufferpool较多的表 
select object_schema, object_name, allocated, `data` 
from sys.innodb_buffer_stats_by_table order by allocated limit 10; 

# 查看表的全表扫描情况 
select * from sys.statements_with_full_table_scans where db='dbname';
1
2
3
4
5
6
7
8
9
10

3、Sys schema 视图- 语句相关

# 监控SQL执行的频率 
select db, exec_count, query 
from sys.statement_analysis order by exec_count desc;

# 监控使用了排序的SQL 
select db, exec_count, first_seen, last_seen, query 
from sys.statements_with_sorting limit 1;

# 监控使用了临时表或者磁盘临时表的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;
1
2
3
4
5
6
7
8
9
10
11
12

4、Sys schema 视图- IO相关

# 查看消耗磁盘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;
1
2
3
4

5、Sys schema 视图- Innodb相关

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