MySQL 优化和性能分析工具的使用

烧脑262

402人浏览 · 2026-04-27 13:01:22

烧脑262 · 2026-04-27 13:01:22 发布

本文章基于优化方面来讲解 MySQL 的性能分析工具，包括 mysqldumpslow、show profile、explain、sys schema 的使用。其中 explain 讲的过于全面、详细，也是整篇文章的重头戏，占用了文章的大部分字数，但也进行了详细的注释，如果要看完整篇文章，请一定要看注释

在 Linux 系统环境对 MySQL 8.0 进行配置、执行，使用 SQLyog 连接 MySQL（因为 SQL yog 默认对关键字自动转为大写，阅读的时候请理解一下）。有一些 sql 语句在 SQLyog 里执行没有结果（可能是版本原因），在 Linux 里执行才有结果，已特地进行注释，注意看注释就行

慢查询日志演示

数据库调优的目标就是响应时间更快、吞吐量更大。利用宏观的监控工具和微观的日志分析可以快速找到调优的思路和方式。一条 SQL 查询语句在执行前需要确定查询执行计划，如果存在多种执行计划，MySQL 会计算每个执行计划所需要的成本，从中选择成本最小的一个作为最终执行的执行计划。
如果想要查看某条 SQL 语句的查询成本，可以在执行完这条 SQL 语句之后，通过查看当前会话中的 last_query_cost 变量值来获取当前查询的成本，也是评价一个查询执行效率的一个常用指标，查询成本对应的是 SQL 语句所需要读取的页的数量

# sql 查询成本
USE atguigudb1;
SELECT * FROM student_info WHERE student_id > 199000;
SHOW STATUS LIKE 'last_query_cost';
SELECT * FROM student_info WHERE student_id > 199900;
SHOW STATUS LIKE 'last_query_cost';

SQL 查询是一个动态过程，从页加载的角度来看：

位置决定效率：如果页就在数据库缓冲池中，效率就是最高的，否则还需要从内存或者磁盘中进行读取，针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。
批量决定效率：如果从磁盘中对单一页进行随机读，效率就会很低，而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取

慢查询日志用来记录在 MySQL 中响应时间超过阀值的语句（long_query_time 的默认值是10秒），执行时间超过 long_query_time 值的语句就会记录在慢查询日志中
主要作用是记录执行时间过长的 SQL 查询，进行有针对性地进行优化，从而提高系统的整体效率。在数据库服务器发生阻塞、运行变慢的时候，检查慢查询日志对解决问题很有帮助
慢查询日志默认是关闭状态，需要手动启动，或者编辑 my.cnf 配置文件来启动。因为开启慢查询日志会带来一定的性能影响，如果不是调优的需要，一般不建议启动该参数

控制慢查询日志的系统变量：
long_query_time：定义慢查询日志时间阈值（默认为 10）
min_examined_row_limit：查询扫描过的最少记录数（默认为 0，表示不限制扫描行数）

假设 min_examined_row_limit = 100，long_query_time = 10

若查询 A 执行了 15 秒，扫描了 50 行，不满足扫描行数 ≥ 100，则会不记录到慢查询日志
若查询 B 执行了 15 秒，扫描了 200 行，同时满足两个条件，会被记录到慢查询日志

A 扫 50 行，行少、开销小，只是单纯跑的久，但压力不大
B 扫 200 行同样卡 15 秒，还多扫了4 倍数据，IO、CPU、内存开销更大

两个系统变量共同组成定义慢查询的判定标准。如果查询扫描过的记录数大于等于 min_examined_row_limit 的值，并且查询执行时间超过 long_query_time 的值，这个查询就被记录到慢查询日志中

show status like 'Connections'; # 连接 MySQL 服务器的次数
show status like 'Uptime' # MySQL 服务器的上线时间
show status like 'Slow_queries' # 慢查询的次数
show status like 'Innodb_rows_read' # Select 查询返回的行数
show status like 'Innodb_rows_inserted' # 执行 INSERT 操作插入的行数
show status like 'Innodb_rows_updated' # 执行 UPDATE 操作更新的行数
show status like 'Innodb_rows_deleted' # 执行 DELETE 操作删除的行数
show status like 'Com_select' # 查询操作的次数
show status like 'Com_insert' # 插入操作的次数。对于批量插入的 INSERT 操作，只累加一次
show status like 'Com_update' # 更新操作的次数
show status like 'Com_delete' # 删除操作的次数

开启慢查询日志参数

show variables like 'slow_query_log'; # 查看是否开启慢查询日志
show variables like 'long_query_time'; # 查看慢查询日志的时间阈值
show variables like 'slow_query_log_file'; # 查看慢查询日志文件地址

set global slow_query_log = ON; # 开启慢查询日志
# set global slow_query_log = OFF; # 关闭慢查询日志
show variables like 'slow_query_log'; # 查看

set global long_query_time = 1; # 全局修改慢查询日志时间阈值为一秒
set long_query_time = 1; # 局部修改慢查询日志时间阈值为一秒
show global variables like 'long_query_time'; # 全局查看
show variables like 'long_query_time'; # 局部查看

show global status like 'slow_queries'; # 查看慢查询数目

/* 修改配置文件方式，在 /etc/my.cnf 添加以下数据
slow_query_log=ON # 开启慢查询日志的开关
slow_query_log_file=/var/lib/mysql/atguigu-slow.log # 慢查询日志的目录和文件名信息
1ong_query_time=3 # 慢查询的阀值为3秒
log_output=FILE
# 如果指定存储路径，慢查询日志默认存储到 MySQL 数据库的数据文件下
# 如果不指定文件名，默认文件名为 hostname-slow.log
*/

环境

注意，使用存储函数以循环方式批量插入 400 万条测试数据，执行后要等待好几分钟，设备越好执行越快

USE test01;

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)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

# 允许创建函数设置
SET GLOBAL log_bin_trust_function_creators = 1;

# 存储函数1：创建随机产生字符串函数
DELIMITER //
CREATE FUNCTION rand_string(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 ;

# 存储函数2：创建随机数函数
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 ;

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 ;

CALL insert_stu1(100001, 4000000);

SELECT COUNT(*) FROM student;

mysqldumpslow

MySQL 自带的慢查询日志分析工具，不用安装，直接用
作用：把慢查询日志里的 SQL 统计、排序、展示

# 相关参数
-a：显示 SQL 语句中完整的参数

-s：排序方式（默认为at）
    t：按总耗时排序
    a：按平均耗时排序
    l：按锁定时间排序
    r：按扫描行数排序
    c：按执行次数排序

-t N：只显示前 N 条结果

-r：反向排序（从小到大排序）

-d：显示调试信息

SELECT * FROM student WHERE stuno = 3500000; # 耗时：3.152sec
SELECT * FROM student WHERE NAME = 'OVjkzP'; # 耗时：3.542sec
SELECT * FROM student WHERE stuno = 3500100; # 耗时：1.290sec
SHOW STATUS LIKE 'slow_queries'; # 查看慢查询数目


# 第二个窗口执行
mysqldumpslow -a -s t -t 5 /var/lib/mysql/mysql_8-slow.log
/* 执行结果为：
Reading mysql slow query log from /var/lib/mysql/mysql_8-slow.log
Count: 1  Time=1603.10s (1603s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[192.168.188.1]
  call insert_stu1(100001, 4000000)
Count: 1  Time=4.72s (4s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@[192.168.188.1]
  select * from student where stuno = 3500100 LIMIT 0, 1000
Count: 1  Time=3.54s (3s)  Lock=0.00s (0s)  Rows=8.0 (8), root[root]@[192.168.188.1]
  select * from student where name = 'OVjkzP' LIMIT 0, 1000
Count: 1  Time=3.15s (3s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@[192.168.188.1]
  select * from student where stuno = 3500000 LIMIT 0, 1000
Count: 1  Time=1.29s (1s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@[192.168.188.1]
  select count(*) from student LIMIT 0, 1000
*/

mysqladmin -uroot -p flush-logs slow # 生成新的慢日志文件


# 关闭慢查询日志
SET GLOBAL slow_query_log = off;
rm -rf /var/lib/mysql/mysql_8-slow.log # 删除慢查询日志文件

show profile

用于分析单条 SQL 语句执行过程中资源消耗的工具，详细展示查询执行的各个阶段所消耗的时间、CPU、IO 等资源

# 相关参数
ALL：显示所有的开销信息
BLOCK IO：显示块 IO 开销
CONTEXT SWITCHES：上下文切换开销
CPU：显示 CPU 开销信息
IPC：显示发送和接收开销信息
MEMORY：显示内存开销信息
PAGEF AULTS：显示页面错误开销信息
SOURCE：显示 Source_function、Source_file、Source_line 相关的开销信息
SWAPS：显示交换次数开销信息


# 出现以下任一诊断结果，都要对 SQL 语句进行优化
converting HEAP to MyISAM：查询结果太大，内存不够
Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表
Copying to tmp table on disk：把内存中临时表复制到磁盘上
locked：查询在执行过程中处于锁定等待状态

set profiling = 'on'; # 启动，默认为关闭状态
show variables like 'profiling'; # 查看

use test01;
SELECT * FROM student WHERE stuno = 3500000; # 耗时：3.152sec
SELECT * FROM student WHERE NAME = 'OVjkzP'; # 耗时：3.542sec
SELECT * FROM student WHERE stuno = 3500100; # 耗时：1.290sec
show profiles; # 当前会话中已记录的 SQL 语句及其执行时间

show profile; # 上一条语句的执行环节
show profile cpu,block io for query 4; # 查询指定语句编号的详细记录
# 此时发现 executing 的耗时最大，而 executing 是查询的执行过程

set profiling = 'off'; # 关闭

explain

explain 分析 SQL 执行计划，可以查看：有没有走索引、索引类型、扫描行数、表连接方式、是否全表扫描
explain 的四种输出格式：传统格式，JSON 格式，TREE 格式、可视化输出

# 格式
EXPLAIN SELECT [字段] FROM [表]; # 传统格式
EXPLAIN FORMAT=JSON SELECT [字段] FROM [表]\G # json 格式，建议在 cmd 执行
EXPLAIN FORMAT=TREE SELECT [字段] FROM [表]\G # tree 格式，建议在 cmd 执行
# 可视化输出需要下载工具，暂不演示

使用 explain 或 describe 查看指定语句的执行计划，不会进行任何实际操作。describe 语句的使用方法与 explain 语句是一样的，并且分析结果也是一样的

USE test01;
EXPLAIN SELECT * FROM student;
EXPLAIN DELETE FROM student WHERE id = 2; # 不会进行实际的删除

id：查询序列号，表示查询语句的执行顺序
- 如果 ID 值相同，由上至下顺序执行
- 如果 ID 值不同，在子查询情况下，ID 序号会自增，ID 值越大优先级越高，越先被执行
select_type：查询类型（普通查询、联合查询等）
table：匹配到的表
partitions：使用的分区情况，默认是 null
type：连接类型，不同的连接类型代表 SQL 的查询性能的高低
possible：可能用到的索引，实际不一定会用到
key：实际用到的索引，为 null 则表示没有用到索引
key_len：索引中使用的字节数，会用该字段判断联合索引是否全部被应用上了（联合索引只有部分被用到了）
ref：使用不同的查询会显示不同的效果
- const：使用了常量等值查询
- func：使用了函数（如：WHERE id = UUID() ）
rows：估计值，基于统计信息，如果行数过大就不值得参考）
filtered：条件过滤后，剩余行占 rows 的百分比，估计值
extra：额外信息，显示 MySQL 在查询过程中一些详细的信息

# 环境，执行语句后需要等待一会
USE test01;

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;


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_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;

SHOW TABLES;

# 允许创建函数设置
SET GLOBAL log_bin_trust_function_creators = 1;

# 存储函数：创建随机产生字符串函数
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, IN max_num INT)
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, IN max_num INT)
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);

SELECT COUNT(*) FROM s1;
SELECT COUNT(*) FROM s2;

table、id

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

# 1. table：表名
# 查询的每一行记录都对应着一个单表
EXPLAIN SELECT * FROM s1;


# s1：驱动表
# s2：被驱动表
EXPLAIN SELECT * FROM s1 INNER JOIN s2;


# 2. id：每个SELECT关键字都对应一个唯一的id
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';


EXPLAIN SELECT * FROM s1 INNER JOIN s2;
# 虽然是两个表，但只有一个select，所以只有一个唯一的id


EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
# 子查询里也有select，共两个select，所以有两个不同的id


EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');
# 注意：查询优化器可能会对涉及子查询的查询语句进行重写，转变为多表查询的操作
# 因为转为多表查询后效率会更高。所以，此时的两条记录的id都为 1


# Union 去重
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
# 执行后有三条记录，第三条记录的id为空
# 因为union是取并集意思，需要去除相同得部分，此时需要在临时的表里做去重操作

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

select_type

一条大查询语句里可以包含若干个 SELECT 关键字，每个 SELECT 关键字都代表一个小的查询语句，而每个 SELECT 关键字的 FROM 子句都可以包含若干张表（连接查询），每一张表都对应着执行计划输出中的一条记录，对于在同一个 SELECT 关键字中的表来说，它们的 id 值是相同的
MySQL 为每一个 SELECT 关键字代表的小查询都定义了一个 select_type 属性，只要知道小查询的 select_type 属性，就知道小查询在整个大查询中扮演的角色
对于包含 UNION 或者 UNION ALL 或者子查询的大查询来说，它是由几个小查询组成，其中最左边的那个查询的 select_type 值就是 PRIMARY
- 理解1：s1 对 s2 进行 UNION 或者 UNION ALL，s1 就是 PRIMARY
- 理解2：s1 为外层，s2 为 s1 的子查询，s1 就是 PRIMARY
对于包含 UNION 或者 UNION ALL 的大查询来说，它是由几个小查询组成，其中除了最左边的那个小查询以外，其余的小查询的 select_type 值就是 UNION
使用临时表来完成 UNION 查询的去重工作，针对该临时表的查询的 select_type 就是 UNION RESULT

# 3. select_type：SELECT 关键字对应的查询类型，可以确定小查询在整个大查询中的角色
# 查询语句中不包含 UNION 或者子查询的查询都算作是 SIMPLE 类型
EXPLAIN SELECT * FROM s1;
 
 
# 连接查询也算是SIMPLE类型
EXPLAIN SELECT * FROM s1 INNER JOIN s2;

 
# 对于包含 UNION 或者UNION ALL或者子查询的大查询来说，它是由几个小查询组成的
# 其中最左边的那个查询的 select_type 值就是 PRIMARY
# 理解1：s1 对 s2  进行UNION或者UNION ALL，s1就是PRIMARY
# 理解2：s1 为外层，s2 为 s1 的子查询，s1 就是 PRIMARY

# 对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的
# 其中除了最左边的那个小查询以外，其余的小查询的select_type值就是UNION

# MySQL使用临时表来完成UNION查询的去重工作，针对临时表的查询的select_type是UNION RESULT
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;


# 子查询：
# 如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询。
# 该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';


# 如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是相关子查询，
# 则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY
EXPLAIN SELECT * FROM s1 
WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';
# 注意的是，select_type为DEPENDENT SUBQUERY的查询可能会被执行多次


# 在包含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');


# 对于包含派生表的查询，该派生表对应的子查询的select_type就是DERIVED
EXPLAIN SELECT * 
FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1;
# DERIVED对应的table是derived2，因为是由id为2的表构成的


# 当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，
# 该子查询对应的select_type属性就是MATERIALIZED
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);
# 子查询 SELECT key1 FROM s2 查询的结果转为了物化表

type

描述了找到所需数据所使用的扫描方式，反映了查询的效率
type 字段所表示的连接类型：

system：表示表中只有一行数据的时候（很少出现）
const：表示通过主键索引或者唯一索引一次就找到了数据
eq_ref：在连接查询中，对于前一个表中的每一行，后表只有一行被扫描了。只有当连接使用索引的部分都是主键或唯一非空索引时，才会有这种类型
ref：连接查询时使用普通索引，对于前表的每一行，后表可能有多于一行的数据被扫描
range：表示进行了索引上的范围查询
index：如果出现了 index 是指的 SQL 使用了索引，但是没有通过索引进行过滤，扫描了索引上的全部数据（查找了所有的索引树，比 ALL 快一点）
ALL：没有用到任何索引，进行全表扫描（连接查询时对于前表的每一行，后表都要被全表扫描）
一般情况下应该让 sql 语句的 type 类型维持在 range，如果是 index 或 ALL 就一定要进行优化

# 5. type：针对单表的访问方法
# 当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的（如MyISAM、Memory）
# 那么对该表的访问方法就是system
CREATE TABLE t(i INT) ENGINE=MYISAM;
INSERT INTO t VALUES(1);

EXPLAIN SELECT * FROM t; # type结果为system
# 如果字段没有索引，再插入一条数据后，结果为ALL


# 换成InnoDB
CREATE TABLE tt(i INT) ENGINE=INNODB;
INSERT INTO tt VALUES(1);

EXPLAIN SELECT * FROM tt; # type结果为ALL


# 根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const
EXPLAIN SELECT * FROM s1 WHERE id = 10005; # id为主键
EXPLAIN SELECT * FROM s1 WHERE key2 = 10066; # key2为唯一索引


# 在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的
#（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），
# 则对该被驱动表的访问方法就是eq_ref
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
# s1的id字段和s2的id字段都是主键
# 驱动表（外表）：s1
# 被驱动表（内表）：s2
 
 
# 通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
# key1是普通索引


# 当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，
# 那么对该表的访问方法就可能是ref_or_null
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;


# 单表访问方法时在某些场景下可以使用Intersection、Union、Sort-Union这三种索引合并的方式来执行查询
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
# OR是固定搭配


# 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';


# 如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法
EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';


# 可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';


# 最熟悉的全表扫描
EXPLAIN SELECT * FROM s1;

possible_key、key

possible_keys：可能用到的索引
key：实际用到的索引

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

key_len

实际使用到的索引长度(即：字节数)

# 检查是否充分的利用上索引，值越大越好，主要针对于联合索引，有一定的参考意义
EXPLAIN SELECT * FROM s1 WHERE id = 10005; # 结果为4
# id是主键，自增，是int类型占4个字节

EXPLAIN SELECT * FROM s1 WHERE key2 = 10126; # 结果为5
# key2是唯一性约束，int类型，占4个字节，可为null，也要占一个字节

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; # 结果为303
# key1是普通索引，数据类型是varchar(100)，变长类型，要2个字节记录实际长度
# 字符集是utf8，一个字符占三个字节，为300
# 可为null，要占一个字节

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a'; # 结果为为303
# key_part1是联合索引，数据类型是varchar(100)，变长类型，要2个字节记录实际长度
# 字符集是utf8，一个字符占三个字节，为300
# 可为null，要占一个字节

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b'; # 结果为606
# key_part1和key_part2位于同一个联合索引，解析同上

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b'AND key_part3 = 'c'; # 结果为909
# key_part1、key_part2和key_part3位于同一个联合索引，解析同上

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

EXPLAIN SELECT * FROM s1 WHERE key_part3 = 'a'; # 结果为 null
# 联合索引遵循最左前缀原则，key_part3 没有使用到索引

# 练习：
# varchar(10)变长字段且允许NULL = 10 * (character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
# varchar(10)变长字段且不允许NULL = 10 * (character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
# char(10)固定字段且允许NULL = 10 * (character set：utf8=3,gbk=2,latin1=1)+1(NULL)
# char(10)固定字段且不允许NULL = 10 * (character set：utf8=3,gbk=2,latin1=1)

ref

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

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; # 结果为 const
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

rows：预估的需要读取的记录条数，值越小越好
filtered: 某个表经过搜索条件过滤后剩余记录条数的百分比

# 9. rows：预估的需要读取的记录条数，值越小越好
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';
# 结果为399，预估会查询到399条记录


# 10. filtered: 某个表经过搜索条件过滤后剩余记录条数的百分比
# 如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用
# 到对应索引的搜索条件外的其他搜索条件的记录有多少条。
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';
# rows结果为399，filtered结果为10
# 解析：
# 满足key1 > 'z'条件的有399条，在这个基础上还要满足'z' AND common_field = 'a'条件的比例只有10%

# 对于单表查询，filtered值意义不大，
# 我们更关注在连接查询中驱动表对应的执行计划记录的filtered值，
# 它决定了被驱动表要执行的次数(即：rows * filtered)
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';

extra

可以通过 extra 的额外信息来更准确的理解 MySQL 到底将如何执行给定的查询语句

字段说明：

Using index：表示直接访问索引就能够获取到数据（查询聚餐索引或者覆盖索引）
Using where：表示查询的列未被索引覆盖，where 后面的条件字段没有添加索引出现了全表扫描
Using filesort：表示进行了文件排序，这类 SQL 性能非常差，需要优化
Using temporary：MySQL 使用了临时表来存储结果集，常见于排序与分组查询中
Using join Buffer：表示使用了连接缓存，会显示 join 连接查询时的查询算法
Using index condition：表示使用了索引下推

# 11. Extra：额外的信息
# 当查询语句的没有FROM子句时将会提示该额外信息
EXPLAIN SELECT 1;
# 结果为：no tables used


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


# 使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件时
# 在Extra列中会提示上述额外信息
EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
# 结果为：using where



# 当使用索引访问来执行对某个表的查询，
# 并且该语句的WHERE子句中有除了该索引包含的列之外的其他搜索条件时，
# 在Extra列中也会提示上述额外信息
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';
# 结果为：using where


# 当查询列表处有MIN或者MAX聚合函数，
# 但是并没有符合WHERE子句中的搜索条件的记录时，将会提示该额外信息
EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';
# 表中没有 abcdefg 这条数据
# 结果为：no matching min/max row


SELECT * FROM s1 LIMIT 10; # 获取随机记录插入匹配条件中
EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'CYkfUj';
# 表中已插入CYkfUj记录
# 结果为：select tables optimezed away


# 当查询列表以及搜索条件中只包含属于某个索引的列，
# 也就是在可以使用覆盖索引的情况下，在Extra列将会提示该额外信息
EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';
# 结果为：using index
# 理解：key1是普通索引，如果要找到所有字段，查到叶子节点后，
# 就一定要回表，去找聚簇索引，再把与key1相关联的所有字段都找到，
# 因为key1所在的叶子节点中只有key1对应的数据。但此时只想找key1字段的数据，
# 所以就不用做回表操作，称为覆盖索引
EXPLAIN SELECT key1,id FROM s1 WHERE key1 = 'a'; # 也是覆盖索引


# 有些搜索条件中虽然出现了索引列，但却不能使用到索引
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
# 结果为：using index condition


# 在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，
# MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度，
# 也是基于块的嵌套循环算法
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
# 结果为：using where; using join buffer (hash join)
# s1为驱动表，s2为被驱动表，两个表的 common_field 都没有索引
# s1的filtered为100，s2的filtered为10，匹配上的数据只有10%

# 使用左（外）连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，
# 而且那个列又是不允许存储NULL值的，那么在该表的执行计划的Extra列就会提示Not exists额外信息
EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;
# s2的id是主键，但过滤条件为 is null，即：1=2
# 结果为：using where; not exists


# 如果执行计划的Extra列出现了Using intersect(...)提示，
# 说明准备使用Intersect索引合并的方式执行查询，
# 括号中的...表示需要进行索引合并的索引名称
# 如果出现了Using union(...)提示，说明准备使用Union索引合并的方式执行查询
# 出现了Using sort_union(...)提示，说明准备使用Sort-Union索引合并的方式执行查询
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
# 结果为：Using union(idx_keyl,idx_key3); Using where

# 当LIMIT子句的参数为0时，表示不打算从表中读出任何记录，将会提示该额外信息
EXPLAIN SELECT * FROM s1 LIMIT 0;
# 结果为：Zero limit


# 有一些情况下对结果集中的记录进行排序是可以使用到索引的
EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
# 结果为：null


# 很多情况下排序操作无法使用到索引，
# 只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，
# MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort）
# 如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort提示
EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
# 结果为：Using filesort
# common_field 没有任何索引，如果要按照common_field字段进行排序，
# 就要把数据加载到内存中再进行排序，效率低下


# 在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，
# 比如在执行许多包含DISTINCT、GROUP BY、UNION等子句的查询过程中，
# 如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。
# 如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示Using temporary提示
EXPLAIN SELECT DISTINCT common_field FROM s1; # 普通字段去重
# 结果为：Using temporary
EXPLAIN SELECT DISTINCT key1 FROM s1; # key1存在索引，是有序状态
# 结果为：Using index
EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;
# 结果为：Using temporary


# 执行计划中出现Using temporary并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，
# 最好能使用索引来替代掉使用临时表。比如：扫描指定的索引idx_key1即可
EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;
# 结果为：Using index

sys schema

在 MySQL 5.7.7 版本中新增 sys schema，将 performance_schema 和 information_schema 中的数据以更容易理解的方式归纳为视图，目的是降低查询 performance_schema 的复杂度，快速定位问题
通过 sys 库去查询会消耗大量资源去收集相关信息，严重时会导致业务请求被阻塞，所以在生产上不建议频繁的查询 sys 或者 performance_schema、information_schema 来完成监控、巡检等工作

# 查询冗余索引
SELECT * FROM sys.schema_redundant_indexes;
show index from [表名]
/*
schema_nameoj：数据库名称
table_name：表名
redundant_index_name：冗余的索引名称
redundant_index_columns：冗余索引包含的字段列表
dominant_index_name：主导索引名称
dominant_index_columns：主导索引包含的字段列表
sql_drop_index：用于删除冗余索引的 SQL 语句（可直接执行）
*/


# 查询未使用过的索引
SELECT * FROM sys.schema_unused_indexes;
/*
object_name：数据库名称
object_name：表名
index_name：未被使用的索引名称
*/


# 查询表的访问量
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;
/*
table_schema：数据库名称
table_name：表名
io：io 次数
*/


# 查询占用 buffer pool 较多的表
SELECT object_schema, object_name, allocated, DATA
FROM sys.innodb_buffer_stats_by_table 
ORDER BY allocated 
LIMIT 10;


# 监控 SQL 执行的频率
SELECT db, exec_count, QUERY
FROM sys.statement_analysis
ORDER BY exec_count DESC;


# 查看消耗磁盘I0的文件
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;


# 行锁阻塞情况
SELECT * FROM sys.innodb_lock_waits;