一：数据库服务器常规优化步骤
在数据库调优中，我们的目标就是响应时间更快，吞吐量更大。利用宏观的监控工具和微观的日志分析可以帮我们快速找到调优的思路和方式；那当我们遇到数据库调优问题的时候，该如何思考呢？
其实这里我把思考的流程整理成下面这张图：
整个流程划分成了观察(Show status)和行动(Action)两个部分;字母S的部分代表观察(会使用相应的分析工具)，字母A代表的部分是行
动(对应分析可以采取的行动)。
我们可以通过观察了解数据库整体的运行状态，通过性能分析工具可以让我们了解执行慢的SQL都有哪些，查看具体的SQL执行计划，
甚至是SQL执行中的每一步的成本代价，这样才能定位问题所在，找到了问题，再采取相应的行动。
详解上面图：
[浅灰色区域]
①：首先在S1部分，我们需要观察服务器的状态是否存在周期性的波动。如果存在周期性波动，有可能是周期性节点的原因，比如双十一、
促销活动等。这样的话，我们可以通过A1这一步骤解决，也就是加缓存，或者更改缓存失效策略。
[浅粉色区域]
①：如果缓存策略都没有解决，或者不是周期性波动的原因，我们就需要进一步分析查询延迟和卡顿的原因。接下来进入S2这一步，我们需要
开启慢查询。慢查询可以帮我们定位执行慢的SQL语句。我们可以通过设置long_query_time参数定义“慢”的阀值，如果SQL执行时间
超过了long_query_time， 则会认为是慢查询。当收集上来这些慢查询之后，我们就可以通过分析工具对慢查询日志进行分析。
②：在S3这一步骤中，我们就知道了执行慢的SQL，这样就可以针对性地用EXPLAIN查看对应SQL语句的执行计划，或者使用SHOW PROFILE
查看SQL中每一个步骤的时间成本。这样我们就可以了解SQL查询慢是因为执行时间长，还是等待时间长。
③：如果是SQL等待时间长，我们进入A2步骤。在这一步骤中，我们可以调优服务器的参数，比如适当增加数据库缓冲池等。如果是SQL执行
时间长，就进入A3步骤，这一步中我们需要考虑是索引设计的问题？还是查询关联的数据表过多？还是因为数据表的字段设计问题导致了
这一现象。然后在这些维度上进行对应的调整。
[浅红色区域]
①：如果A2和A3都不能解决问题，我们需要考虑数据库自身的SQL查询性能是否已经达到了瓶颈，如果确认没有达到性能瓶颈，就需要重新检
查，重复以上的步骤。如果已经达到了性能瓶颈，进入A4阶段，需要考虑增加服务器，采用读写分离的架构，或者考虑对数据库进行分
库分表，比如垂直分库、垂直分表和水平分表等。
以上就是数据库调优的流程思路。如果我们发现执行SQL时存在不规则延迟或卡顿的时候，就可以采用分析工具帮我们定位有问题的SQL，这三种
分析工具你可以理解是SQL调优的三个步骤：慢查询、EXPLAIN和SHOW PROFILING。
二：查看系统性能参数
我们在MySQL中可以使用SHOW VARIABLES来查看服务器的配置信息，而使用SHOW STATUS语句则是查询MySQL数据库服务器的性能参数、执行频率等信息
基本语法如下：
SHOW [GLOBAL | SESSION] STATUS LIKE '参数';
-- 注：默认为SESSION会话级变量，若执行SHOW STATUS则打印会话级别的全部系统性能配置
一些常用的性能参数查询：
-- 查询MySQL服务器被连接次数
SHOW GLOBAL STATUS LIKE 'Connections';
-- 查询MySQL服务上线（启动）时间（秒）
SHOW GLOBAL STATUS LIKE 'Uptime';
-- 慢查询次数，可以结合慢查询日志找到慢查询语句，然后针对语句进行分析和优化（后面有说明）
SHOW GLOBAL STATUS LIKE 'Slow_queries';
-- 查询MySQL执行的SELECT返回行数及INSERT、UPDATE、DELETE操作的返回行数
SHOW GLOBAL STATUS LIKE 'Innodb_rows_%';
SHOW GLOBAL STATUS LIKE 'Innodb_rows_read';
SHOW GLOBAL STATUS LIKE 'Innodb_rows_inserted';
SHOW GLOBAL STATUS LIKE 'Innodb_rows_updated';
SHOW GLOBAL STATUS LIKE 'Innodb_rows_deleted';
-- 查询MySQL的增删改查的执行操作次数（如一次查询就代表一次查询操作）
SHOW GLOBAL STATUS LIKE 'Com_select';
SHOW GLOBAL STATUS LIKE 'Com_insert';
SHOW GLOBAL STATUS LIKE 'Com_update';
SHOW GLOBAL STATUS LIKE 'Com_delete';
三：开启慢查询日志
MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10秒(不含10秒)，超过阈值则会被认为是个慢查询。
它的主要作用是，帮助我们发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。比如一条SQL执行超过5秒钟我们就算慢SQL，希望能收集超过5秒的SQL,结合explain进行全面分析。
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件。
1：开启慢查询日志参数
Ⅰ：开启slow_query_log
-- 先查询当前是否开启了慢查询日志
SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
Ⅱ：修改慢查询日志的位置(可选)
-- 查询当前慢查询位置在哪
SHOW GLOBAL VARIABLES LIKE 'slow_query%';
-- 修改位置（可选）
SET GLOBAL slow_query_log_file = '/var/lib/mysql/mysql_slow.log';
Ⅲ：修改long_query_time阈值
-- 查询慢查询阈值
SHOW GLOBAL VARIABLES LIKE 'long_query_time';
-- 设置慢查询阈值1秒
SET GLOBAL long_query_time = 1;
说明：上面方式只是临时设置变量，若要永久性设置则需要通过配置方式（不推荐，因为排查优化完后尽量关闭此功能）
[mysqld]
slow_query_log=ON # 开启慢查询日志开关 OFF代表关闭
slow_query_log_file=/var/lib/mysql/mysql_slow.log # 慢查询日志目录和文件信息
long_query_time=1 # 设置慢查询阈值为2秒
说明（min_examined_row_limit变量）：（正常默认0即可，了解即可）
除了上述long_query_time变量外，控制慢查询日志的还有一个系统变量：min_examined_row_limit。
这个变量的意思是查询扫描过的最少记录数。min_examined_row_limit（扫描最少记录数）变量和long_query_time（查询执行时间）
共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过long_query_time
的值，那么，这个查询就被记录到慢查询日志中;反之则不被记录到慢查询日志中。
min_examined_row_limit值默认是0；与long_query_time默认10结合在一起，表示只要查询的执行时间超过10秒钟，就是说我执行的
这条语句查询到0条记录，但是执行SQL耗费10秒以上则构成了慢查询我们也可以根据需要，通过修改“my.ini"文件，来修改查询时长，或
者通过SET指令，用SQL语句修改“min_examined_row_limit的值
2：查看慢查询数目及分析
我们上面开启了慢查询日志，这时我们的SQL执行若超过阈值则会被记录到慢查询日志中，我们可以使用如下SQL语句：
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
我们此时查询的慢查询是0，因为我们还没执行任何超过阈值的SQL，这里我们先准备数据【SQL建表建库语句】
-- 执行慢查询的SQL（这里我们强行睡眠3秒）
SELECT s.*, SLEEP(2) FROM student_info s LIMIT 5;
-- 一个简单的非表的查询语句
SELECT '我延迟了', SLEEP(3) FROM DUAL;
Ⅰ：使用慢查询记录语句查询：
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 2 |
+---------------+-------+
Ⅱ：慢查询日志分析工具：mysqldumpslow
在生产环境中，如果要手工分析日志、查找、分析SQL不借助工具很难定位，所以MySQL提供了日志分析工具mysqldumpslow
-- 查询帮助
mysqldumpslow --help
-- 按照查询时间排序，查看前1条SQL语句，这样写即可：
mysqldumpslow -s t -t 1 /var/lib/mysql/mysql_slow.log
-- 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/mysql_slow.log
-- 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/mysql_slow.log
-- 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/mysql_slow.log
-- 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/mysql_slow.log | more
说明：mysqldumpslow参数说明：
--verbose、-v: 详细模式
--debug、-d: 写入调试信息
-r: 反转排序顺序（最大的最后一个而不是第一个）
-a: 不要将所有数字抽象为N，字符串抽象为S
-n num: 名称中至少有num位数字的抽象数字
-t num: 只显示前num个查询
-g pattern: 仅考虑与（grep 样式）模式匹配的查询；就是后边搭配一个正则匹配模式，大小写不敏感的
-h host_name: 文件名的 MySQL 服务器的主机名。该值可以包含通配符。默认值为（全部匹配）。*-slow.log*
-i name: 服务器实例的名称（如果使用 mysql.server 启动脚本）
-l: 不要从总时间中减去锁定时间
-s sort_type: 如何对输出进行排序。应从以下列表中选择sort_type的值：
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间(默认方式)
ac:平均查询次数
如下执行：
[root@VM-4-10-centos mysql]# mysqldumpslow -s t -t 1 /var/lib/mysql/mysql_slow.log
Reading mysql slow query log from /var/lib/mysql/mysql_slow.log
Count: 6 Time=11.67s (70s) Lock=0.00s (0s) Rows=5.0 (30), root[root]@[125.118.95.231]
SELECT s.*, SLEEP(N) FROM student_info s LIMIT N
说明：
Count：出现次数
Time：执行最长时间s（累计总耗费时间s）
Lock：等待锁的时间
Rows：发送给客户端的行总数（扫描的行总数）
用户以及sql语句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示)
3：关闭慢查询及删除日志
MySQL服务器停止慢查询日志功能有两种方法（具体按照如何开启的，也使用对应方式关闭）：
方式1：永久性配置方式
[mysqld]
slow_query_log=OFF # 关闭慢查询日志开关 ON代表关闭；
方式2：临时性方式（使用SET修改变量）
SET GLOBAL slow_query_log=OFF;
删除之前残留的慢查询日志：
我们找到慢查询日志然后执行删除操作：rm -rf mysql_slow.log
使用命令mysqladmin flush-logs来重新生成查询日志文件，执行完毕会在数据目录下重新生
mysqladmin -uroot -p flush-logs slow
-- 说明：这个命令后缀slow代表说是重置慢查询日志，若在关闭慢查询日志时执行，则不会创建，只有开启则会创建
注：慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意，一且执行了这个命令，慢查询日志都只存在新
的日志文件中，如果需要旧的查询日志，就必须事先备份。
四：查看SQL执行成本[SHOW PROFILE]
SHOW PROFILE 和 SHOW PROFILES 命令用于展示SQL语句的资源使用情况，包括CPU的使用，CPU上下文切换，IO等待，内存使用等，
这个命令对于分析某个SQL的性能瓶颈非常有帮助，借助于SHOW PROFILE的输出信息，能让我们知道一个SQL在哪个阶段耗时最长，消耗资源最多，从而为SQL优化，提高SQL性能提供重要的依据。但是这个命令很快将成为历史了，因为MySQL5.7版本将会逐渐废弃这个功能，取而代之的是使用Performance Schema
Ⅰ：开启功能：
SET SESSION profiling = 1; -- 1开启 0关闭
Ⅱ：查看是否开启：
SHOW SESSION VARIABLES LIKE 'profiling';
Ⅲ：执行SQL：
-- 这样我们就正式开启了这项功能，先执行几条SQL，看有没有记录
SELECT * FROM student_info WHERE name='QcMDHz';
SELECT student_id, name, class_id FROM student_info WHERE class_id = 10041 LIMIT 10;
Ⅳ：使用SHOW PROFILES命令查看被监控记录的语句：
SHOW PROFILES
+--------+----------+-----------------------------------------------------------------------------------+
|Query_ID|Duration |Query |
+--------+----------+-----------------------------------------------------------------------------------+
| 1|0.00203900|SHOW SESSION VARIABLES LIKE 'profiling' |
| 2|0.39030900|SELECT * FROM student_info WHERE name='QcMDHz' |
| 3|0.00057225|SELECT student_id, name, class_id FROM student_info WHERE class_id = 10041 LIMIT 10|
+--------+----------+-----------------------------------------------------------------------------------+
-- 说明：Query_ID（查询ID）、Duration（执行时间）、Query（查询语句）
Ⅴ：针对Query_ID详细分析使用SHOW PROFILE命令：
SHOW PROFILE展示一个SQL语句的执行耗时细节，如果不加FOR QUERY {n}子句，默认展示最新的一次SQL的执行情况，
加了FOR QUERY {n}，表示展示第n个SQL的执行情况。
比如看第2个SQL，执行：
SHOW PROFILE FOR QUERY 2;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000092 |
| Executing hook on transaction | 0.000013 |
| starting | 0.000009 |
| checking permissions | 0.000007 |
| Opening tables | 0.000040 |
| init | 0.000005 |
| System lock | 0.000009 |
| optimizing | 0.000010 |
| statistics | 0.000019 |
| preparing | 0.000024 |
| executing | 0.389972 |
| end | 0.000019 |
| query end | 0.000005 |
| waiting for handler commit | 0.000013 |
| closing tables | 0.000012 |
| freeing items | 0.000054 |
| cleaning up | 0.000010 |
+--------------------------------+----------+
我们还可以在SHOW PROFILE时，可以加上类型子句，比如CPU,IPC等，查看具体某类资源的消耗情况，例如：
SHOW PROFILE CPU,IPC FOR QUERY 2;
+------------------------------+--------+--------+----------+-------------+-----------------+
|Status |Duration|CPU_user|CPU_system|Messages_sent|Messages_received|
+------------------------------+--------+--------+----------+-------------+-----------------+
|starting |0.000092|0.000086| 0.000000| 0| 0|
|Executing hook on transaction |0.000013|0.000010| 0.000003| 0| 0|
|starting |0.000009|0.000006| 0.000002| 0| 0|
|checking permissions |0.000007|0.000006| 0.000002| 0| 0|
|Opening tables |0.000040|0.000030| 0.000009| 0| 0|
|init |0.000005|0.000004| 0.000001| 0| 0|
|System lock |0.000009|0.000007| 0.000002| 0| 0|
|optimizing |0.000010|0.000007| 0.000003| 0| 0|
|statistics |0.000019|0.000014| 0.000004| 0| 0|
|preparing |0.000024|0.000019| 0.000006| 0| 0|
|executing |0.389972|0.391095| 0.000000| 0| 0|
|end |0.000019|0.000012| 0.000000| 0| 0|
|query end |0.000005|0.000005| 0.000000| 0| 0|
|waiting for handler commit |0.000013|0.000013| 0.000000| 0| 0|
|closing tables |0.000012|0.000011| 0.000000| 0| 0|
|freeing items |0.000054|0.000054| 0.000000| 0| 0|
|cleaning up |0.000010|0.000010| 0.000000| 0| 0|
+------------------------------+--------+--------+----------+-------------+-----------------+
Ⅵ：其它说明及语法：
SHOW PROFILE 和 SHOW PROFILES 除了这两个语句之外，还可以展示所有的SQL语句的执行情况，包括语法错误的SQL也会被监测到。
比如执行SHOW TABLE;这个SQL有语法错误，但仍然被记录到了SHOW PROFILES;列表中。
语法：
SHOW PROFILE [type [, type] …… ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]]
type:
① ALL：显示所有的开销信息。
② BLOCK I0：显示块I0开销。
③ CONTEXT SWITCHES：上下文切换开销。
④ CPU：显示CPU开销信息。
⑤ IPC：显示发送和接收开销信息。
⑥ MEMORY：显示内存开销信息。
⑦ PAGE FAULTS：显示页面错误开销信息。
⑧ SOURCE：显示和Source_function, Source_file, Source_line相关的开销信息。
⑨ SWAP：显示交换次数开销信息。
五：分析查询语句[EXPLAIN重点]
定位到慢查询的SQL之后，我们就可以使用EXPLAIN或DESCRIBE工具做针对性的分析查询语句；通过EXPLAIN工具可以知道执行SQL的执行计划，比如是全表扫描，还是索引扫描等；EXPLAIN命令是查看优化器如何决定执行查询的主要方法。可以帮助我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。需要注意的是，生成的QEP（查询执行器'Query Execution Plan'）并不确定，它可能会根据很多因素发生改变。MySQL不会将一个QEP和某个给定查询绑定，QEP将由SQL语句每次执行时的实际情况确定，即便使用存储过程也是如此。尽管在存储过程中SQL语句都是预先解析过的，但QEP仍然会在每次调用存储过程的时候才被确定。
-- 创建数据库并使用数据库
CREATE DATABASE IF NOT EXISTS demo_index_explain;
USE demo_index_explain;
-- 创建一个s1的表
CREATE TABLE IF NOT EXISTS s1 (
id INT AUTO_INCREMENT COMMENT '主键id',
key1 VARCHAR(100) COMMENT '普通索引，索引名 idx_key1',
key2 INT COMMENT '唯一索引，索引名 idx_key2',
key3 VARCHAR(100) COMMENT '普通索引 索引名 idx_key3',
key_part1 VARCHAR(100) COMMENT '组合索引 索引名 idx_key_part',
key_part2 VARCHAR(100) COMMENT '组合索引 索引名 idx_key_part',
key_part3 VARCHAR(100) COMMENT '组合索引 索引名 idx_key_part',
common_field VARCHAR(100) COMMENT '普通字段，不包含索引',
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 DEFAULT CHARSET = utf8mb4;
-- 创建一个s2的表
CREATE TABLE IF NOT EXISTS s2 (
id INT AUTO_INCREMENT COMMENT '主键id',
key1 VARCHAR(100) COMMENT '普通索引，索引名 idx_key1',
key2 INT COMMENT '唯一索引，索引名 idx_key2',
key3 VARCHAR(100) COMMENT '普通索引 索引名 idx_key3',
key_part1 VARCHAR(100) COMMENT '组合索引 索引名 idx_key_part',
key_part2 VARCHAR(100) COMMENT '组合索引 索引名 idx_key_part',
key_part3 VARCHAR(100) COMMENT '组合索引 索引名 idx_key_part',
common_field VARCHAR(100) COMMENT '普通字段，但是这个会当一个逻辑外键连接s1表的common_field字段',
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 DEFAULT CHARSET = utf8mb4;
-- 创建函数（最好Navicat下执行，若报错DETERMINISTIC看下面）
-- 该函数会返回一个指定长度的字符串
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
-- 创建存储过程
-- 存储函数插入s1表值
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
-- 存储函数插入s2表值
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
-- 调用存储过程
-- s1表数据的添加：加入1万条记录：
CALL insert_s1(10001,10000);
-- s2表数据的添加：加入1万条记录：
CALL insert_s2(10001,10000);
注：若创建函数时报错 This function has none of DETERMINISTIC......
由于开启过慢查询日志bin-log, 我们就必须为我们的function指定一个参数。
查看mysql是否允许创建函数：
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
命令开启：允许创建函数设置（不加global只是当前窗口有效）：
SET GLOBAL log_bin_trust_function_creators=1;
mysqld重启，上述参数又会消失。永久方法：
在Windows下：my.ini[mysqld]加上：log_bin_trust_function_creators=1
在Linux下：/etc/my.cnf下my.cnf[mysqld]加上：log_bin_trust_function_creators=1
EXPLAIN案例演示资料准备（建表建库）
1：EXPLAIN功能及语法
Ⅰ：EXPLAIN能做什么，能从中获取什么信息
①：表的读取顺序。
②：数据读取操作的操作类型。
③：哪些索引可以使用。
④：哪些索引可以被实际使用。
⑤：表之间的引用。
⑥：每张表多上行被优化器查询。
Ⅱ：官网说明
MySQL5.7版本EXPLAIN
MySQL8.0版本EXPLAIN
Ⅲ：版本说明
MySQL5.6.3以前只能EXPLAIN SELECT；而MYSQL5.6.3以后就可以 EXPLAIN SELECT|UPDATE|DELETE
在MySQL5.7以前的版本中：
想要显示partitions需要使用explain partitions命令想要显示filtered需要使用explain extended命令。
在MySQL5.7版本后，默认explain直接显示partitions和filtered中的信息。
Ⅳ：EXPLAIN或DESCRIBE语句的语法形式如下：
-- 下面2种方式
EXPLAIN SELECT select_options
DESCRIBE SELECT select_options
-- 查看执行计划输出的信息
EXPLAIN SELECT 1 FROM DUAL;
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------+
| 1|SIMPLE |NULL |NULL |NULL|NULL |NULL|NULL |NULL|NULL| NULL|No tables used|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------+
从上面的输出信息可以看到12个字段，下面说明各个字段简单说明：
id： 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type： SELECT关键字对应的那个查询的类型
table： 表名
partitions： 匹配的分区信息
type： 针对单表的访问方法（重要）
possible_keys：可能用到的索引
key： 实际上使用的索引
key_len： 实际使用到的索引长度（重要）
ref： 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows： 预估的需要读取的记录条数（重要）
filtered： 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra： 一些额外的信息（重要）
说明：EXPLAIN只是分析SQL语句，不管是SELECT、UPDATE、DELETE哪一种，它都不会真正执行SQL造成查询或者删除，
EXPLAIN只是把要执行的SQL执行计划告诉我们；
比如EXPLAIN DELETE FROM student_info WHERE id = 5; 执行后返回执行计划信息，并不会删除数据。
注：若表的结构发生变化后通过EXPLAIN分析还是和之前的结构一样则需要分析表：ANALYZE TABLE 表名;
2：table字段
不论我们的查询语句有多复杂，SQL包含了多少个表 ，到最后也是需要对每个表进行单表访问的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（若存在别名，则以别名为主）。
①：分析的每一条记录都对应着一张单表
EXPLAIN SELECT * FROM s1 s;
+----+-------------+-------+------------+------+...
| id | select_type | table | partitions | type |...
+----+-------------+-------+------------+------+...
| 1 | SIMPLE | s | NULL | ALL |...
+----+-------------+-------+------------+------+...
②：使用2张表连接查询应该对应2条分析记录
EXPLAIN SELECT * FROM s1 INNER JOIN s2 USING(common_field);
+----+-------------+-------+------------+------+...
| id | select_type | table | partitions | type |...
+----+-------------+-------+------------+------+...
| 1 | SIMPLE | s1 | NULL | ALL |...
| 1 | SIMPLE | s2 | NULL | ALL |...
+----+-------------+-------+------------+------+...
-- 注，记录越靠上的就是驱动表，记录靠下的是被驱动表，此时s1驱动s2
③：使用 UNION 和 UNION ALL 查
-- s1和s2进行联合，不去重，所有存在2张表
EXPLAIN (SELECT * FROM s1) UNION ALL (SELECT * FROM s2);
+----+-------------+-------+------------+------+...
| id | select_type | table | partitions | type |...
+----+-------------+-------+------------+------+...
| 1 | PRIMARY | s1 | NULL | ALL |...
| 2 | UNION | s2 | NULL | ALL |...
+----+-------------+-------+------------+------+...
-- s1和s2联合查询并去重，因为s1和s2联合后生成一个”<union1,2>“临时表，其中1代表id1，2代表id2然后进行去重
EXPLAIN (SELECT * FROM s1) UNION (SELECT * FROM s2);
+----+--------------+------------+------------+------+...
| id | select_type | table | partitions | type |...
+----+--------------+------------+------------+------+...
| 1 | PRIMARY | s1 | NULL | ALL |...
| 2 | UNION | s2 | NULL | ALL |...
| 3 | UNION RESULT | <union1,2> | NULL | ALL |...
+----+--------------+------------+------------+------+...
④：派生表，该行引用值为N的行的派生表结果。例如，派生表可能由子句中的子查询生成
EXPLAIN SELECT * FROM (SELECT COUNT(*) FROM s1) as derived;
+----+-------------+------------+------------+--------+...
| id | select_type | table | partitions | type |...
+----+-------------+------------+------------+--------+...
| 1 | PRIMARY | <derived2> | NULL | system |...
| 2 | DERIVED | s1 | NULL | index |...
+----+-------------+------------+------------+--------+...
-- 派生的表是id为2的记录
2：id字段
实际上每次SELECT查询都会对应一个id，它代表着SQL执行的顺序，如果id值越大，说明对应的SQL语句执行的优先级越高。在一些复杂的查询SQL语句中常常包含一些子查询，那么id序号就会递增，如果出现嵌套查询，我们可以发现最里层的查询对应的id最大，因此也优先被执行。
①：包含一张表的情况下，只会存在id=1的一条记录
EXPLAIN SELECT * FROM s1 s;
+----+-------------+-------+------------+------+...
| id | select_type | table | partitions | type |...
+----+-------------+-------+------------+------+...
| 1 | SIMPLE | s | NULL | ALL |...
+----+-------------+-------+------------+------+...
②：稍微复杂一点的连接查询，虽然只有一个SELECT关键字，但是出现了2行分析记录
EXPLAIN SELECT * FROM s1 INNER JOIN s2 USING(common_field);
+----+-------------+-------+------------+------+...
| id | select_type | table | partitions | type |...
+----+-------------+-------+------------+------+...
| 1 | SIMPLE | s1 | NULL | ALL |...
| 1 | SIMPLE | s2 | NULL | ALL |...
+----+-------------+-------+------------+------+...
-- 说明：虽然出现了2条记录，但是id都是等于1，这就意味着没有先后顺序之分，其实结论是我从s1取一条记录，然后
-- 在s2表中找到对应连接的记录，这样就构成了并行查询，
③：若存在子查询则会区分现在执行顺序
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
+----+-------------+-------+------------+-------+...
| id | select_type | table | partitions | type |...
+----+-------------+-------+------------+-------+...
| 1 | PRIMARY | s1 | NULL | ALL |...
| 2 | SUBQUERY | s2 | NULL | index |...
+----+-------------+-------+------------+-------+...
④：若优化器对子查询优化，可能和写的不一样，转换为其它方式
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');
+----+-------------+-------+------------+--------+...
| id | select_type | table | partitions | type |...
+----+-------------+-------+------------+--------+...
| 1 | SIMPLE | s1 | NULL | ALL |...
| 1 | SIMPLE | s2 | NULL | eq_ref |...
+----+-------------+-------+------------+--------+...
-- 这就是被优化过的，优化器觉得以这种方式执行好
-- 被优化成这个SQL：SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s2.common_field='a';
总结：
①：id如果相同，可以认为是一组，从上往下顺序执行
②：在所有组中，id值越大，优先级越高，越先执行
③：关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好
3：select_type字段
表示的执行计划对应的查询是什么类型，其实一条大的查询语句里边可能包含若干个SELECT关键字，每个SELECT关键字代表着一个小的查询语句，而每个SELECT关键字的FROM子句中都可以包含若干张表(这些表用来做连接查询)，每一张表都对应着一条执行计划并输出一条记录；其实MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色，下面看一下select_type都能取哪些值。
select_type官方文档属性:
SIMPLE：简单查询，该SQL中没有子查询或者UNION联合查询
PRIMARY：表示查询为外部查询
UNION：代表是个联合查询，此时是被连接的那张表
UNION RESULT：代表联合查询时为了去重，生成一个临时表用来去重
DEPENDENT UNION：UNION操作中，查询中处于内存的SELECT（内层的SELECT语句与外层的SELECT语句有依赖关系）
SUBQUERY：代表当前记录是一个子查询的角色存在的
DEPENDENT SUBQUERY：代表当前记录是一个相关子查询的角色存在
DERIVED：派生表
DEPENDENT DERIVED：派生表依赖于另一个表（下文未详细说明）
MATERIALIZED：物化表，把子查询查询的数据当作常量
UNCACHEABLE SUBQUERY：无法缓存结果且必须为外部查询的每一行重新计算结果的子查询（下文未详细说明）
UNCACHEABLE UNION：属于不可缓存子查询的 UNION 中的第二个或以后的选择（下文未详细说明）
SIMPLE说明：
-- 查询语句中不包含子查询和UNION联合查询则算作SIMPLE简单类型
EXPLAIN SELECT * FROM s1 LIMIT 2;
+----+-------------+-------+------------+------+...
| id | select_type | table | partitions | type |...
+----+-------------+-------+------------+------+...
| 1 | SIMPLE | s1 | NULL | ALL |...
+----+-------------+-------+------------+------+...
-- 连接查询也算作SIMPLE简单类型
EXPLAIN SELECT * FROM s1 INNER JOIN s2 USING(common_field);
+----+-------------+-------+------------+------+...
| id | select_type | table | partitions | type |...
+----+-------------+-------+------------+------+...
| 1 | SIMPLE | s1 | NULL | ALL |...
| 1 | SIMPLE | s2 | NULL | ALL |...
+----+-------------+-------+------------+------+...
PRIMARY说明：
对于包含'UNION'或者'UNION ALL'或者子查询的SQL来说，它是由几个小查询组成的，其中最左边的查询的select_type值
就是PRIMARY，对于A表UNION上B表，那么A表就是PRIMARY；对于A表内部嵌套一个子查询，那么A表就是PRIMARY
-- 对于连接查询UNION或者UNION ALL
EXPLAIN (SELECT * FROM s1) UNION ALL (SELECT * FROM s2);
+----+-------------+-------+------------+------+...
| id | select_type | table | partitions | type |...
+----+-------------+-------+------------+------+...
| 1 | PRIMARY | s1 | NULL | ALL |...
| 2 | UNION | s2 | NULL | ALL |...
+----+-------------+-------+------------+------+...
-- 对于表内部嵌套一个子查询
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
+----+-------------+-------+------------+-------+...
| id | select_type | table | partitions | type |...
+----+-------------+-------+------------+-------+...
| 1 | PRIMARY | s1 | NULL | ALL |...
| 2 | SUBQUERY | s2 | NULL | index |...
+----+-------------+-------+------------+-------+...
UNION、UNION RESULT说明：
-- UNION ALL无需去重
EXPLAIN (SELECT * FROM s1) UNION ALL (SELECT * FROM s2);
+----+-------------+-------+------------+------+...
| id | select_type | table | partitions | type |...
+----+-------------+-------+------------+------+...
| 1 | PRIMARY | s1 | NULL | ALL |...
| 2 | UNION | s2 | NULL | ALL |...
+----+-------------+-------+------------+------+...
-- UNION需要去重
EXPLAIN (SELECT * FROM s1) UNION (SELECT * FROM s2);
+----+--------------+------------+------------+------+...
| id | select_type | table | partitions | type |...
+----+--------------+------------+------------+------+...
| 1 | PRIMARY | s1 | NULL | ALL |...
| 2 | UNION | s2 | NULL | ALL |...
| 3 | UNION RESULT | <union1,2> | NULL | ALL |...
+----+--------------+------------+------------+------+...
SUBQUERY说明：
如果包含子查询的查询语句不能被优化器转为对应的JOIN多表连接查询的形式，并且该子查询是不相关子查询。
该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY
-- 一个简单的无法被转换多表连接的不相关子查询
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
+----+-------------+-------+------------+-------+...
| id | select_type | table | partitions | type |...
+----+-------------+-------+------------+-------+...
| 1 | PRIMARY | s1 | NULL | ALL |...
| 2 | SUBQUERY | s2 | NULL | index |...
+----+-------------+-------+------------+-------+...
DEPENDENT SUBQUERY说明：
如果包含子查询的查询语句不能被优化器转为对应的JOIN多表连接查询的形式，并且该子查询是相关子查询。
该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY
-- 一个简单的无法被转换多表连接的相关子查询
EXPLAIN SELECT * FROM s1 WHERE key1 = (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';
+----+--------------------+-------+------------+--------+...
| id | select_type | table | partitions | type |...
+----+--------------------+-------+------------+--------+...
| 1 | PRIMARY | s1 | NULL | ALL |...
| 2 | DEPENDENT SUBQUERY | s2 | NULL | eq_ref |...
+----+--------------------+-------+------------+--------+...
DEPENDENT UNION说明（这个有点难理解）：
在包含UNION或者UNIONALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，
其余的小查询的iselect_type的值就是DEPENDENT UNION；说到这大家可能有点疑惑，说大白话就是一个大查询内存在一个
相关子查询DEPENDENT SUBQUERY，但是这个子查询有点特殊，其实这个子查询是由2个表联合查询的产物，看案例：
-- 一个简单的子查询分析
EXPLAIN SELECT * FROM s1 yz WHERE key1
IN( (SELECT key1 FROM s1 ss1 WHERE key1 = 'a') UNION (SELECT key1 FROM s2 ss2 WHERE key1 = 'b'));
+----+--------------------+------------+------------+------+...
| id | select_type | table | partitions | type |...
+----+--------------------+------------+------------+------+...
| 1 | PRIMARY | yz | NULL | ALL |...
| 2 | DEPENDENT SUBQUERY | ss1 | NULL | ref |...
| 3 | DEPENDENT UNION | ss2 | NULL | ref |...
| 4 | UNION RESULT | <union2,3> | NULL | ALL |...
+----+--------------------+------------+------------+------+...
说明：执行完上面的一条SQL后会发现分析出4条记录，第一条PRIMARY不用说明大家都知道，但后面为什么会这样呢？
第2条DEPENDENT SUBQUERY其实是相关子查询，我们把上面IN()内的ss1表想成一个相关子查询，但是大家肯定疑问，这
也没有用到外围的表yz表呀，这个疑问后面说明，
第3条DEPENDENT UNION其实是被ss1联合的表，ss2是被联合的，所有他就成为UNION，但是他是DEPENDENT UNION，所以
它和第二条记录一样，内部也引用了外部的yz表，这里我也没看出来
第4条UNION RESULT不用说，就是ss1和ss2表生成的一个临时表用来去重
针对上面的问题其实在执行SQL的时候，优化器帮我们转换为EXISTS的方式，转换如下：
执行完使用：SHOW WARNINGS; -- 注：必须是执行完一条EXPLAIN分析，才可以执行SHOW WARNINGS; 否则没信息；
分析被优化器优化后的SQL：（这里我把冗余的部分剔除，留取关键）
select * from s1 yz where exists
((select ss1.key1 from s1 ss1 where ss1.key1 = 'a' and yz.key1 = 'a')
union
(select ss2.key1 from s2 ss2 where ss2.key1 = 'b' and yz.key1 = 'b'));
DERIVED说明：
派生出来的表为派生表。就是FROM不是跟具体的表，而是通过子查询查询出来的派生表
EXPLAIN SELECT * FROM (SELECT key1,COUNT(*) cot FROM s2 GROUP BY key1) as derived;
+----+-------------+------------+------------+-------+...
| id | select_type | table | partitions | type |...
+----+-------------+------------+------------+-------+...
| 1 | PRIMARY | <derived2> | NULL | ALL |...
| 2 | DERIVED | s2 | NULL | index |...
+----+-------------+------------+------------+-------+...
MATERIALIZED说明：
当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，
该子查询对应的iselect_type属性就是MATERIALIZED
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);
+----+--------------+-------------+------------+--------+...
| id | select_type | table | partitions | type |...
+----+--------------+-------------+------------+--------+...
| 1 | SIMPLE | s1 | NULL | ALL |...
| 1 | SIMPLE | <subquery2> | NULL | eq_ref |...
| 2 | MATERIALIZED | s2 | NULL | index |...
+----+--------------+-------------+------------+--------+...
4：partitions字段
代表分区表中的命中情况，非分区表，该项为NULL，一般都为NULL，就包括我上面写的这个字段全为NULL；只有当数据表中的数据量很大时，分区带来的效率提升才会显现出来。只有检索字段为分区字段时，分区带来的效率提升才会比较明显。
5：type字段
执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，又称“访问类型”，其中type列就表明了这个访问方法是啥，是较为重要的一个指标。比如，看到type列的值是ref，表明MySQL即将使用ref访问方法来执行对s1表的查询。
完整的type类型如下（按照从效率高到效率低的顺序）： system -> const -> eq_ref -> ref -> fulltext -> ref_or_null -> index_merge ->
unique_subquery -> index_subquery -> range -> index -> ALL 说明：日常写SQL最好在前四个（蓝色）；若存在慢SQL最差的优化目标也要达到range级别，我们一般写SQL要达到ref级别， 最好的是consts级别；若SQL查询为index或者ALL为不合格SQ，一定要优化。 每种type类型的说明（下面有案例）： system： 当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory那么对该表的访问方法就是system const： 发生在数据表最多只有一个匹配行，就是说我们根据主键或者唯一索引与常数进行等值匹配时，对单表的访问方法就是'const',因为 它一旦在表检索到即返回，因为只读取一次,const用于使用常数值比较PRIMARY KEY或UNIQUE索引的所有部分的场合。 eq_ref： 在连接查询时，如果被驱动表是通过主键或者唯一索引列等值匹配的方式进行连接的，若该主键或者唯一索引是联合索引的话,所有的 索引列都必须进行等值比较，则对该被驱动表的访问方法就是eq_ref。 ref： 当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是'ref' 其实ref可以用于使用“＝”或“＜＝＞”操作符的带索引的列。 ref_or_null： 当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null(和上面相似) index_merge： 单表访问方法时在某些场景下系统会为什么使用索引合并优化；其中key输出行中的列包含使用的索引列表，并key_len包含使用的 索引的最长键部分的列表 unique_subquery： 是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行 等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery index_subquery： 同上，只不过子查询使用到的是普通索引 range： 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。 当使用＝、＜＞、＞、＞＝、＜、＜＝、IS NULL、＜＝＞、BETWEEN或者IN操作符用常量比较关键字列时，类型为range index： 第一种如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，Extra列显示为 Using index，第二种是使用对索引的读取执行全表扫描，以按索引顺序查找数据行。Uses index没有出现在Extra列中。 ALL： 对于前面的表的任意行组合进行完整的表扫描 system说明： -- 比如创建一张MyISAM引擎的表并添加一条记录 CREATE TABLE tb1(i int) Engine=MyISAM; INSERT INTO tb1 VALUES(1); -- 查询执行计划 EXPLAIN SELECT * FROM tb1; +----+-------------+-------+------------+--------+---------------+------+... | id | select_type | table | partitions | type | possible_keys | key |... +----+-------------+-------+------------+--------+---------------+------+... | 1 | SIMPLE | tb1 | NULL | system | NULL | NULL |... +----+-------------+-------+------------+--------+---------------+------+... -- 若再添加一条数据则查询type就变成ALL了 INSERT INTO tb1 VALUES(2); EXPLAIN SELECT * FROM tb1; +----+-------------+-------+------------+------+---------------+------+... | id | select_type | table | partitions | type | possible_keys | key |... +----+-------------+-------+------------+------+---------------+------+... | 1 | SIMPLE | tb1 | NULL | ALL | NULL | NULL |... +----+-------------+-------+------------+------+---------------+------+... -- 说明：因为MyISAM它底层是有一个记录着表总行数的变量，当为1条记录，它就直接返回，为什么InnoDB不行，因为它 -- 没有这个变量记录总行数，而是一行一行变量才知道，遍历到一行后没有了，那就返回数据 const说明： -- 在有唯一索引或者主键的列进行查询 EXPLAIN SELECT * FROM s1 WHERE id = 10086; +----+-------------+-------+------------+-------+---------------+---------+... | id | select_type | table | partitions | type | possible_keys | key |... +----+-------------+-------+------------+-------+---------------+---------+... | 1 | SIMPLE | s1 | NULL | const | PRIMARY | PRIMARY |... +----+-------------+-------+------------+-------+---------------+---------+... EXPLAIN SELECT * FROM s2 WHERE key2 = 10336; +----+-------------+-------+------------+-------+---------------+----------... | id | select_type | table | partitions | type | possible_keys | key ... +----+-------------+-------+------------+-------+---------------+----------... | 1 | SIMPLE | s2 | NULL | const | idx_key2 | idx_key2 ... +----+-------------+-------+------------+-------+---------------+----------... -- 但是注意的是，查询的常量必须和字段类型对应，若不对应使用到隐式转换函数则会失效 eq_ref说明： -- 在连接查询时驱动表和被驱动表在主键或者唯一索引的字段下进行连接 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id; +----+-------------+-------+------------+--------+---------------+---------+... | id | select_type | table | partitions | type | possible_keys | key |... +----+-------------+-------+------------+--------+---------------+---------+... | 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL |... | 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY |... +----+-------------+-------+------------+--------+---------------+---------+... EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key2 = s2.key2; +----+-------------+-------+------------+--------+---------------+----------+... | id | select_type | table | partitions | type | possible_keys | key |... +----+-------------+-------+------------+--------+---------------+----------+... | 1 | SIMPLE | s1 | NULL | ALL | idx_key2 | NULL |... | 1 | SIMPLE | s2 | NULL | eq_ref | idx_key2 | idx_key2 |... +----+-------------+-------+------------+--------+---------------+----------+... ref说明： -- 在连接查询时驱动表和被驱动表在普通索引的字段下进行连接 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1; +----+-------------+-------+------------+------+---------------+----------+... | id | select_type | table | partitions | type | possible_keys | key |... +----+-------------+-------+------------+------+---------------+----------+... | 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL |... | 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 |... +----+-------------+-------+------------+------+---------------+----------+... -- 普通索引等值查询 EXPLAIN SELECT * FROM s1 WHERE key1 = '10156'; +----+-------------+-------+------------+------+---------------+----------+... | id | select_type | table | partitions | type | possible_keys | key |... +----+-------------+-------+------------+------+---------------+----------+... | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 |... +----+-------------+-------+------------+------+---------------+----------+... ref_or_null说明： -- 普通索引等值查询，但是对哪个普通索引列还要查询NULL记录 EXPLAIN SELECT * FROM s1 WHERE key1 = '10156' OR key1 IS NULL; +----+-------------+-------+------------+-------------+---------------+----------+... | id | select_type | table | partitions | type | possible_keys | key |... +----+-------------+-------+------------+-------------+---------------+----------+... | 1 | SIMPLE | s1 | NULL | ref_or_null | idx_key1 | idx_key1 |... +----+-------------+-------+------------+-------------+---------------+----------+... index_merge说明： -- 在一张表中使用2个单独的索引进行检索，优化器发现后会帮我们自动建立一个这两个索引的组合索引，OR的情况下可以 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key2 = 'b'; +--+-----------+-----+----------+-----------+-----------------+-----------------+-------+... |id|select_type|table|partitions|type |possible_keys |key |key_len|... +--+-----------+-----+----------+-----------+-----------------+-----------------+-------+... | 1|SIMPLE |s1 |NULL |index_merge|idx_key2,idx_key1|idx_key1,idx_key2|403,5 |... +--+-----------+-----+----------+-----------+-----------------+-----------------+-------+... unique_subquery说明： -- 查询表条件使用IN返回具体的主键索引或者唯一索引列 EXPLAIN SELECT * FROM s1 WHERE id IN ( SELECT key2 FROM s2 WHERE s1.key1 = s2.key1) OR key3 = 'a'; +----+--------------------+-------+------------+---------------+-----------------+--------+... | id | select_type | table | partitions |type |possible_keys |key |... +----+--------------------+-------+------------+---------------+-----------------+--------+... | 1 | PRIMARY | s1 | NULL |ALL |idx_key3 |NULL |... | 2 | DEPENDENT SUBQUERY | s2 | NULL |unique_subquery|idx_key2,idx_key1|idx_key2|... +----+--------------------+-------+------------+---------------+-----------------+--------+... rang说明： -- 查询范围条件，使用唯一索引key2字段 EXPLAIN SELECT * FROM s1 WHERE id BETWEEN 10011 AND 10020; +----+-------------+-------+------------+-------+---------------+---------+... | id | select_type | table | partitions | type | possible_keys | key |... +----+-------------+-------+------------+-------+---------------+---------+... | 1 | SIMPLE | s1 | NULL | range | PRIMARY | PRIMARY |... +----+-------------+-------+------------+-------+---------------+---------+... index说明： -- 使用覆盖索引查询（查询的数据正好在二级索引中都存在） EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'; +--+-----------+-----+...+-------+-------------+------------+...+--------------------------+ |id|select_type|table|...| type |possible_keys|key |...| Extra | +--+-----------+-----+...+-------+-------------+------------+...+--------------------------+ | 1|SIMPLE |s1 |...| index |idx_key_part |idx_key_part|...| Using where; Using index | +--+-----------+-----+...+-------+-------------+------------+...+--------------------------+ ALL说明： -- 全表扫描不利用上索引查询数据 EXPLAIN SELECT * FROM s1 WHERE common_field = 'a'; +----+-------------+-------+------------+------+---------------+------+... | id | select_type | table | partitions | type | possible_keys | key |... +----+-------------+-------+------------+------+---------------+------+... | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL |... +----+-------------+-------+------------+------+---------------+------+...
6：possible_key和key字段
在EXPLAIN语句输出的执行计划中，possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引。
使用说明：
下面执行计划的possible_keys列的值是idx_key1,idx_key3，表示该查询可能使用到idx_key1，idx_key3两个索引，然后key
列的值是idx_key3，表示经过查询优化器计算使用不同索引的成本后,最后决定使用最优的索引来执行这条SQL
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';
+----+-------------+-------+------------+------+-------------------+----------+---------+...
| id | select_type | table | partitions | type | possible_keys | key | key_len |...
+----+-------------+-------+------------+------+-------------------+----------+---------+...
| 1 | SIMPLE | s1 | NULL | ref | idx_key1,idx_key3 | idx_key3 | 403 |...
+----+-------------+-------+------------+------+-------------------+----------+---------+...
7：key_len字段
该列表示MySQL决定使用到的索引长度（即字节数）。可以帮我们检查是否充分利用上索引，其值越大越好，这个字段主要针对联合索引才有一定的参考意义。
例句说明：
-- 简单的利用id索引查询
EXPLAIN SELECT * FROM s1 WHERE id = 10002;
+----+-------------+-------+------------+-------+---------------+---------+---------+...
| id | select_type | table | partitions | type | possible_keys | key | key_len |...
+----+-------------+-------+------------+-------+---------------+---------+---------+...
| 1 | SIMPLE | s1 | NULL | const | PRIMARY | PRIMARY | 4 |...
+----+-------------+-------+------------+-------+---------------+---------+---------+...
-- 说明：为什么key_len为4呢，因为当前主键使用的是INT类型，占用4个字节
-- 简单利用key1普通索引查询
EXPLAIN SELECT * FROM s1 WHERE key1 = 'abc';
+----+-------------+-------+------------+------+---------------+----------+---------+...
| id | select_type | table | partitions | type | possible_keys | key | key_len |...
+----+-------------+-------+------------+------+---------------+----------+---------+...
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 403 |...
+----+-------------+-------+------------+------+---------------+----------+---------+...
-- 说明：为什么key_len为403呢，因为在utf8mb4的环境下，一个字符为4字节，就是400，第二是因为可为NULL
-- 值，所有再加一个NULL标记位，一个字节，还要2个字节是，MySQL会记录字符串的可变长度，若超过255
-- 字节则需要2个字节来记录当前的字符串长度，一共位403
-- 简单利用key_part1和key_part2两个字段的组合索引查询（索引名称为idx_key_part）
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'abc' AND key_part2 = 'xyz';
+----+-------------+-------+------------+------+---------------+--------------+---------+...
| id | select_type | table | partitions | type | possible_keys | key | key_len |...
+----+-------------+-------+------------+------+---------------+--------------+---------+...
| 1 | SIMPLE | s1 | NULL | ref | idx_key_part | idx_key_part | 806 |...
+----+-------------+-------+------------+------+---------------+--------------+---------+...
-- 说明：就是两个字段的占用字节加起来，若使用3个字段的组合索引，那就是3个字段占用字节加起来
8：ref字段
当使用的索引等值查询时，与索引列进行等值匹配的对象信息，比如只是一个常数或者是某个列都是等值比较；若不是索引或者未命中索引则为NULL
例句说明：
-- 基本的等值常数比较
EXPLAIN SELECT * FROM s1 WHERE common_field = '10002';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+...
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref |...
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+...
| 1 | SIMPLE | s1 | NULL | const | PRIMARY | PRIMARY | 4 | const |...
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+...
-- 说明：此时ref列为const，因为当时的查询条件为常数与索引进行匹配
-- 基本的等值连接查询
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
+--+-----------+-----+----------+------+-------------+-------+-------+------------------------+...
|id|select_type|table|partitions|type |possible_keys|key |key_len|ref |...
+--+-----------+-----+----------+------+-------------+-------+-------+------------------------+...
| 1|SIMPLE |s1 |NULL |ALL |PRIMARY |NULL |NULL |NULL |...
| 1|SIMPLE |s2 |NULL |eq_ref|PRIMARY |PRIMARY|4 |demo_index_explain.s1.id|...
+--+-----------+-----+----------+------+-------------+-------+-------+------------------------+...
-- 说明：s1表是驱动表没有ref值，而s2为被驱动表，它的ref值来自与s1表的主键，因为s1每一条记录要和s2表一一匹配查询
-- 索引查询时匹配的是函数
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);
+----+-------------+-------+------------+------+---------------+----------+---------+------+...
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref |...
+----+-------------+-------+------------+------+---------------+----------+---------+------+...
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL |...
| 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 403 | func |...
+----+-------------+-------+------------+------+---------------+----------+---------+------+...
-- 说明：为函数时则打印func
9：rows字段
rows列是显示的是MySQL认为当前预估的记录条数，对于InnoDB表，此数字是估计值，可能并不总是准确的。预估的越小越好。
例句说明：
-- 简单查询
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';
+----+-------------+-------+...+----------+---------+------+------+----------+...
| id | select_type | table |...| key | key_len | ref | rows | filtered |...
+----+-------------+-------+...+----------+---------+------+------+----------+...
| 1 | SIMPLE | s1 |...| idx_key1 | 403 | NULL | 358 | 100.00 |...
+----+-------------+-------+...+----------+---------+------+------+----------+...
-- 说明：此时rows为358条，代表具体执行这个查询语句可能会出现358条记录
10：filtered字段
表示某个表结果搜索条件过滤后剩余记录条数的百分比；
例句说明：
-- 如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';
+----+-------------+-------+...+-----+-------------+--------+-------+----+----+--------+...
| id | select_type | table |...|type |possible_keys|key |key_len|ref |rows|filtered|...
+----+-------------+-------+...+-----+-------------+--------+-------+----+----+--------+...
| 1 | SIMPLE | s1 |...|range|idx_key1 |idx_key1|403 |NULL| 358| 10.00|...
+----+-------------+-------+...+-----+-------------+--------+-------+----+----+--------+...
-- 说明：上面存在两个条件查询，其中key1为索引，就是说当前查询的一条记录按照索引可以查询到358条记录，但是再
-- 加上common_field普通字段的搜索，可能实际查询到的记录数会减少，预估是358条的10%；计算358*10%约为35条
-- 对于单表查询来说，这个filtered列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的filtered值，它决
-- 定了被驱动表要执行的次数(即: rows×filtered)
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';
+--+-----------+-----+..+----+-------------+--------+-------+--------------------------+----+--------+
|id|select_type|table|..|type|possible_keys|key |key_len|ref |rows|filtered|
+--+-----------+-----+..+----+-------------+--------+-------+--------------------------+----+--------+
| 1|SIMPLE |s1 |..|ALL |idx_key1 |NULL |NULL |NULL |9595| 10.00|
| 1|SIMPLE |s2 |..|ref |idx_key1 |idx_key1|403 |demo_index_explain.s1.key1| 1| 100.00|
+--+-----------+-----+..+----+-------------+--------+-------+--------------------------+----+--------+
-- 说明：此条SQL是连接查询，第一条记录是驱动表，它符合连接条件的存在9595条记录，这9595条会进行连接表操作，但是
-- common_field过滤发现它大概会有10%的数据符合，第二条是被驱动表，代表驱动表的每条数据记录和被驱动表都有一条对应的，
-- 且filtered=100%
11：Extra字段
Extra列是用来说明一些额外信息的，包含不适合在其它列中显示但十分重要的额外信息。我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，所以我们只挑比较重要的额外信息说明
No tables used（未使用表）说明：
当查询语句没有FROM子句或FROM子句为DUAL时将会提示该额外信息
EXPLAIN SELECT 1 FROM DUAL;
Impossible WHERE（不可能的条件）说明：
查询语句的WHERE子句永远为false时将会提示该额外信息
EXPLAIN SELECT * FROM s1 WHERE 1!=1;
Using where（使用WHERE查询）说明：
当使用全表扫描来执行对某个表的查询时，并且该语句的WHERE子句中有针对该表的搜索条件时，在Extra列中会提示上述额外信息。
EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
-- 说明：没有走索引时，使用普通字段查询会全表查询
当使用索引访问来执行对某个表查询，并且该语句的WHERE子句中除了该索引还包含其它没有索引列的其它搜索条件时在Extra列中也
会提示上述额外信息。
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';
No matching min/max row（没有匹配的最大或最小行）说明：
当查询列表处有MIN或者MAX聚合函数，但是并没有符合WHERE子句中的搜索条件的记录时，将会提示该额外信息
EXPLAIN SELECT MIN(key2) FROM s1 WHERE key2 = 1;
Using index（使用索引）说明：
当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用覆盖索引的情况下，在Extra列将会提示该额外信息。
EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';
-- 此时使用了覆盖索引，key1为普通索引，若直接打印key1则不需要回表查询
Using index condition（使用索引条件）说明：
有些搜索条件中虽然出现了索引列，但却不能使用到索引，比如下边这个查询:
SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a'
-- key1>'z'可以使用到索引,但是key1 LIKE '%a'却无法使用到索引，在以前版本的MySQL中,是按照下边步骤来执行这个查询:
-- ①：先根据key1 > 'z'这个条件，从二级索idx_key1中获取到对应的二级索引记录。
-- ②：根据上一步骤得到的二级索引记录中的主键值进行回表，找到完整的用户记录再检测该记录是否符合 key1 LIKE '%a' 这
-- 个条件，将符合条件的记录加入到最后的结果集。
-- 但是虽然key1 LIKE '%a'不能组成范围区间参与range访问方法的执行，但这个条件毕竟只涉及到了key1列,所以MySQL把上边
-- 的步骤改进了一下:
-- ①：先根据key1 > 'z'这个条件，定位到二级索引idx_key1中对应的二级索引记录。
-- ②：对于指定的二级索引记录，先不着急回表，而是先检测一下该记录是否满足key1 LIKE '%a' 这个条件,如果这个条件不满
-- 足，则该二级索引记录压根儿就没必要回表。
-- ③：对于满足key1 LIKE '%a' 这个条件的二级索引记录执行回表操作。
我们说回表操作其实是一个随机IO，比较耗时,所以上述修改虽然只改进了一点点,但是可以省去好多回表操作的成本。MySQL把他们的这个
改进称之为索引条件下推(英文名: Index Condition Pushdown)。
如果在查询语句的执行过程中将要使用索引条件下推这个特性,在Extra列中将会显示Using index condition,比如这样:
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%b';
Using where; Using join buffer (hash join)（使用连接缓冲区）说明：
在连接查询执行过程中，当驱动表不能有效的利用索引加快连接访问速度时，MySQL会为其分配一块名叫join buffer的内存块来加快查
询速度，也就是我们所说的基于块的嵌套循环算法
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
Not exists（不存在）说明：
当我们使用左(外)连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列又是不允许存储NULL值的，
那么在该表的执行计划的Extra列就会提示Not exists额外信息
EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;
Using union(...)（使用联合体）说明：
如果执行计划的Extra列出现了Using intersect(...)提示，说明准备使用Intersect索引合并的方式执行查询，括号中的....表示
需要进行索引合并的索引名称；如果出现了Using union(...)提示，说明准备使用Union索引合并的方式执行查询；
出现了Using sort union(...)提示，说明准备使用Sort-Union索引合并的方式执行查询。
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
+----+..+-------------+-------------------+...+-------------------------------------------+
| id |..| type | possible_keys |...|Extra |
+----+..+-------------+-------------------+...+-------------------------------------------+
| 1 |..| index_merge | idx_key1,idx_key3 |...|Using union(idx_key1,idx_key3); Using where|
+----+..+-------------+-------------------+...+-------------------------------------------+
Zero limit（零极限值）说明：
当我们的LIMIT子句的参数为0时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息
EXPLAIN SELECT * FROM s1 LIMIT 0;
六：EXPLAIN四种输出格式
EXPLAIN可以输出四种格式：传统格式、JSON格式、TREE格式以及可视化输出
1：传统格式
这个就是我们之前使用EXPLAIN不加任何格式信息，就以表格形式输出，返回一个查询计划(上面也详细介绍了此模式的每个字段)
如：EXPLAIN SELECT * FROM s1 INNER JOIN s2 USING(id);
+--+-----------+-----+----------+------+-------------+-------+-------+-----+----+--------+-----+
|id|select_type|table|partitions|type |possible_keys|key |key_len|ref |rows|filtered|Extra|
+--+-----------+-----+----------+------+-------------+-------+-------+-----+----+--------+-----+
| 1|SIMPLE |s1 |NULL |ALL |PRIMARY |NULL |NULL |NULL |9595| 100.00|NULL |
| 1|SIMPLE |s2 |NULL |eq_ref|PRIMARY |PRIMARY|4 |s1.id| 1| 100.00|NULL |
+--+-----------+-----+----------+------+-------------+-------+-------+-----+----+--------+-----+
2：JSON格式
直接使用EXPLAIN语句输出中缺少了一个衡量执行计划好坏的重要属性"成本"。 而JSON格式是四种格式里面输出信息最详尽的格式。
语法：EXPLAIN FORMAT=JSON SQL语句....
比如：EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 USING(id)\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1, -- 整个查询语句只有1个SELECT关键字，该关键字对应的id号为1
"cost_info": {
"query_cost": "4342.00" -- 整个查询的执行成本语句4342.00
},
"nested_loop": [
-- 以下是参与嵌套循环连接算法的各个表的信息
{
"table": {
"table_name": "s1", -- s1表是驱动表
"access_type": "ALL", -- 使用方法为ALL，意味着使用全表扫描访问
"possible_keys": ["PRIMARY"], -- 可能使用的索引
"rows_examined_per_scan": 9595, -- 查询一次s1表大致需要扫描9595条记录
"rows_produced_per_join": 9595, -- 驱动表s1的扇出是9595
"filtered": "100.00", -- condition filtering代表百分比
"cost_info": {
"read_cost": "24.25", -- 后面说明
"eval_cost": "959.50", -- 后面说明
"prefix_cost": "983.75", -- 单次查询s1表总共的成本
"data_read_per_join": "22M" -- 读取的数据量
},
"used_columns": [ -- 执行查询中涉及到的列
"id","key1","key2","key3","key_part1","key_part2","key_part3","common_field"]
}
},
{
"table": {
"table_name": "s2", -- s2表是驱动表
"access_type": "eq_ref", -- 访问方法为eq_ref，使用被驱动表的主键或者唯一索引连接
"possible_keys": ["PRIMARY"], -- 可能使用到的索引
"key": "PRIMARY", -- 具体使用的索引
"used_key_parts": ["id"], -- 使用到的索引列
"key_length": "4", -- 索引长度
"ref": ["demo_index_explain.s1.id"], -- 与s1驱动表的id字段进行等值匹配
"rows_examined_per_scan": 1, -- 查询一次s2表大致需要扫描1条记录
"rows_produced_per_join": 9595, -- 被驱动表s2的扇出(由于后面没有多余表进行连接，所有没啥用)
"filtered": "100.00", -- condition filtering代表百分比
"cost_info": {
"read_cost": "2398.75", -- 后面说明
"eval_cost": "959.50", -- 后面说明
"prefix_cost": "4342.00", -- 单次查询s1，多次查询s2表总共的成本
"data_read_per_join": "22M" -- 读取的数据量
},
"used_columns": [ -- 执行查询中涉及到的列
"id","key1","key2","key3","key_part1","key_part2","key_part3","common_field"]
}
}
]
}
}
read_cost
是由下边这两部分组成的：IO成本、检测rows×(1-filter)条记录的CPU成本
说明：rows和filter都是我们前边介绍执行计划的输出列，在JSON格式的执行计划中，
而rows相当于rows_examined_per_scan，filtered名称不变。
eval_cost
计算方式：检测rows × filter条记录的成本
prefix_cost
就是单独查询s1表的成本，计算方式：read_cost + eval_cost
data_read_per_join
表示在此次查询中需要读取的数据量
对于s2表的"cost_info"部分是这样的：
"cost_info": {
"read_cost": "2398.75", -- 后面说明
"eval_cost": "959.50", -- 后面说明
"prefix_cost": "4342.00", -- 单次查询s1，多次查询s2表总共的成本
"data_read_per_join": "22M" -- 读取的数据量
},
由于s2表是被驱动表，所以可能被读取多次，这里的read_cost和eval_cost是访问多次s2表后累加起来的值，大家主要关注里边儿
的prefix_cost的值代表的是整个连接查询预计的成本，也就是单次查询s1表和多次查询s2表后的成本的和，
也就是：2398.75+959.50+983.75 = 4342
3：TREE格式
TREE格式是8.0.16版本之后引入的新格式，主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询。
例如：EXPLAIN FORMAT=TREE SELECT * FROM s1 INNER JOIN s2 USING(id);
+----------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=4342.00 rows=9595) |
| -> Table scan on s1 (cost=983.75 rows=9595) |
| -> Single-row index lookup on s2 using PRIMARY (id=s1.id) (cost=0.25 rows=1) |
+----------------------------------------------------------------------------------+
4：可视化输出
可视化输出，可以通过MySQL Workbench【下载地址】可视化查看MySQL的执行计划。通过点击Workbench的放大镜图标，即可生成可视化的查询计划。
设置中文导航栏：参考github的一个设置
复制`main_menu.xml`替换`mysql workbench`安装目录的`data/main_menu.xml`文件。
MacOS: /Applications/MySQLWorkbench.app/Contents/Resources/data/main_menu.xml
Windows(默认): C:\Program Files\MySQL\MySQL Workbench 8.0 CE\data\main_menu.xml
说明：连MySQL报错：SSL connection error
# 1. 先退出 mysql workbench （8.0.27+）
# 2. 执行下面的代码，windows 在 git bash 窗口中执行命令
sed -i "s/useSSL\">2/useSSL\">0/" ${APPDATA}/MySQL/Workbench/connections.xml # windows
sed -i "s/useSSL\">2/useSSL\">0/" ${HOME}/.mysql/workbench/connections.xml # 应该是这个目录
把文件 connections.xml 中 useSSL 的值改成 0 即可
七：SHOW WARNINGS使用
其实SHOW WARNINGS是显示上一个语句的错误、警告或注意事项，还可以查看上一句MySQL执行器具体执行优化器优化后的执行语句
比如执行如下语句：
EXPLAIN SELECT * FROM s1 WHERE key1 IN ( SELECT key2 FROM s2 );
想查看刚才执行的语句被优化器怎么优化的(若上一句报错或者警告都可以查看)：
SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1739
Message: Cannot use ref access on index 'idx_key1' due to type or collation conversion on field 'key1'
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `demo_index_explain`.`s1`.`id` AS `id`,`demo_index_explain`.`s1`.`key1`
AS `key1`,`demo_index_explain`.`s1`.`key2` AS `key2`,`demo_index_explain`.`s1`.`key3` AS `key3`,
`demo_index_explain`.`s1`.`key_part1` AS `key_part1`,`demo_index_explain`.`s1`.`key_part2` AS
`key_part2`,`demo_index_explain`.`s1`.`key_part3` AS `key_part3`,`demo_index_explain`.`s1`.
`common_field` AS `common_field` from `demo_index_explain`.`s2` join `demo_index_explain`.`s1`
where (cast(`demo_index_explain`.`s1`.`key1` as double) =
cast(`demo_index_explain`.`s2`.`key2` as double))
2 rows in set (0.01 sec)
可以看到SHOW WARNINGS展示出来的信息有三个字段，分别是Level、Code、 Message。
我们最常见的就是Code为1003的信息，当Code值为1003时，Message字段展示的信息类似于查询优化器将我们的查询语句重写后的语句。
比如我们上边的语句是子查询，但是优化器优化成连接查询语句
八：分析优化器执行计划
OPTIMIZER_TRACE是MySQL5.6引入的一项跟踪功能，它可以跟踪优化器做出的各种决策(比如访问表的方法、各种开销计算、各种转换等)，并将跟踪结果记录到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。
使用方式：默认关闭，需开启trace, 设置格式为JSON,同时设置trace最大能够使用的内存大小，避免解析后因默认内存过小而不能够完整展示
SET optimizer_trace="enabled=on",end_markers_in_json=on;
SET optimizer_trace_max_mem_size=1000000;
开启后可以分析如下语句：
SELECT、INSERT、REPLACE、UPDATE、DELETE、EXPLAIN、SET、DECLARE、CASE、IF、RETURN、CALL
测试：执行如下SQL语句
SELECT id,key1,key2 FROM s1 WHERE id = 10003;
执行完SQL后，查询information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的：
SELECT * FROM information_schema.optimizer_trace\G
-- 下面说明输出的信息
*************************** 1. row ***************************
-- 第一部分：查询的语句
QUERY: SELECT id,key1,key2 FROM s1 WHERE id = 10003
-- 第二部分：QUERY字段对应语句的跟踪信息
TRACE: {
"steps": [
{
## join_preparation段落展示了准备阶段的执行过程
"join_preparation": {
"select#": 1,
-- steps对比下原始语句，可以知道，这一步做了个格式化。
"steps": [{"expanded_query": "/* select#1 */ select `s1`.`id` AS `id`,`s1`.`key1` AS `key1`,
`s1`.`key2` AS `key2` from `s1` where (`s1`.`id` = 10003)"}]
}
},
{
## join_optimization展示了优化阶段的执行过程，是分析OPTIMIZER TRACE的重点。下面逐段分析：
"join_optimization": {
"select#": 1,
"steps": [
{
## condition_processing该段用来做条件处理，主要对WHERE条件进行优化处理。
"condition_processing": {
"condition": "WHERE", -- 优化对象类型。WHERE条件句或者是HAVING条件句
"original_condition": "(`s1`.`id` = 10003)", -- 优化前的原始语句
## steps包括三步，分别是quality_propagation（等值条件句转换），constant_propagation（常量条件句转换）,
## trivial_condition_removal（无效条件移除的转换）
"steps": [
{
"transformation": "equality_propagation", -- 转换类型句
"resulting_condition": "multiple equal(10003, `s1`.`id`)" -- 转换之后的结果输出
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal(10003, `s1`.`id`)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(10003, `s1`.`id`)"
}
]
}
},
{
## substitute_generated_columns用于替换虚拟生成列
"substitute_generated_columns": {}
},
{
##table_dependencies分析表之间的依赖关系
"table_dependencies": [
{
"table": "`s1`", -- 涉及的表名，如果有别名，也会展示出来
"row_may_be_null": false, -- 行是否可能为NULL，这里是指JOIN操作之后，这张表里的数据是不是可
-- 能为NULL。如果语句中使用了LEFT JOIN，则后一张表的
-- row_may_be_null会显示为true
"map_bit": 0, -- 表的映射编号，从0开始递增
"depends_on_map_bits": [] -- 依赖的映射表。主要是当使用STRAIGHT_JOIN强行控制连接顺序或
-- 者LEFT JOIN/RIGHT JOIN有顺序差别时，
-- 会在depends_on_map_bits中展示前置表的map_bit值。
}
]
},
{
## 列出所有可用的ref类型的索引。如果使用了组合索引的多个部分
## （例如本例，用到了index(from_date, to_date) 的多列索引），则会在ref_optimizer_key_uses下列出多
## 个元素，每个元素中会列出ref使用的索引及对应值。
"ref_optimizer_key_uses": [
{
"table": "`s1`",
"field": "id",
"equals": "10003",
"null_rejecting": true
}
]
},
{
## rows_estimation用于估算需要扫描的记录数。
"rows_estimation": [
{
"table": "`s1`",
"rows": 1,
"cost": 1,
"table_type": "const",
"empty": false
}
] /* rows_estimation */
},
{
"condition_on_constant_tables": "true",
"condition_value": true
},
{
"attaching_conditions_to_tables": {
"original_condition": "true",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
-- 第三阶段：跟踪信息过长时，被截断的跟踪信息的字节数。（这里是0，说明我们optimizer_trace_max_mem_size设置的大）
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
-- 第四阶段：执行跟踪语句的用户是否有查看对象的权限。当不具有权限时，该列信息为1且TRACE字段为空，
-- 一般在调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下，会出现此问题。
NSUFFICIENT_PRIVILEGES: 0
具体参考资料：手把手教你认识OPTIMIZER_TRACE
具体参考资料：Tracing the Optimizer
九：MySQL监控分析视图--sys schema（sys表）
关于MySQL的性能监控和问题诊断，我们一般都从performance_schema中去获取想要的数据，在MySQ5.7.7版本中新增sys schema（也就是sys表）,它将performance_schema和information_schema中的数据以更容易理解的方式总结归纳为"视图”，其目的就是为了降低查询performance_schema的复杂度，让DBA能够快速的定位问题。
1：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开头，展示等待事件的延迟情况。
2：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;
.
标签：...,--,性能,查询,MySQL,工具,NULL,type,s1 From： https://www.cnblogs.com/antLaddie/p/17101777.html