0基础学MySQL数据库—从小白到大牛(28)性能分析工具的使用

前言

本章使用的测试数据库为GreatSQL8.0.25版本

(Wed Aug  3 16:17:03 2022)[root@GreatSQL][(none)]>select version();+-----------+| version() |+-----------+| 8.0.25-16 |+-----------+1 row in set (0.00 sec)

一、数据库服务器的优化步骤

我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。

整个流程划分成了观察（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和 SHOWPROFILING。

小结：

二、查看系统性能参数

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

SHOW STATUS语句语法如下：

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

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

• Connections：连接MySQL服务器的次数。
• Uptime：MySQL服务器的上线时间。
• Slow_queries：慢查询的次数。 默认十秒以上，超过10秒就默认为慢的SQL，此时会显示1。
• 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：删除操作的次数。

三、统计SQL的查询成本：last_query_cost

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

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

student_info 表为例：

(Mon Aug 15 14:35:18 2022)[root@GreatSQL][index_text]>SELECT student_id, class_id, NAME, create_time FROM student_info WHERE id = 900001 ;+------------+----------+--------+---------------------+| student_id | class_id | NAME   | create_time         |+------------+----------+--------+---------------------+|     145187 |    10153 | rzwIev | 2022-08-12 15:17:34 |+------------+----------+--------+---------------------+1 row in set (0.01 sec)

然后再看下查询优化器的成本，实际上我们只需要检索一个页即可：

(Mon Aug 15 14:35:33 2022)[root@GreatSQL][index_text]>SHOW STATUS LIKE 'last_query_cost';+-----------------+----------+| Variable_name   | Value    |+-----------------+----------+| Last_query_cost | 1.000000 |+-----------------+----------+1 row in set (0.00 sec)

这个1代表查找需要用到一个数据页

再次测试

SELECT student_id, class_id, NAME, create_time FROM student_infoWHERE id BETWEEN 900001 AND 900100;

然后再看下查询优化器的成本

(Mon Aug 15 14:39:51 2022)[root@GreatSQL][index_text]>SHOW STATUS LIKE 'last_query_cost';+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| Last_query_cost | 20.453528 |+-----------------+-----------+1 row in set (0.01 sec)

这时我们大概需要进行 20 个页的查询。

你能看到页的数量是刚才的 20 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然页数量（last_query_cost）增加了不少，但是通过缓冲池的机制，并没有增加多少查询时间。

使用场景： 它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。

SQL查询是一个动态的过程，从页加载的角度来看，我们可以得到以下两点结论:

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

所以说，遇到IO并不用担心，方法找对了，效率还是很高的。我们首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。

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

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10S，意思是运行10秒以上(不含10秒)的语句，认为是超出了我们的最大忍耐时间值。

它的主要作用是，帮助我们发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。比如一条sq|执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合explain进行全面分析。

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

慢查询日志支持将日志记录写入文件。

开启慢查询日志参数

查看是否开启

(Mon Aug 15 14:40:07 2022)[root@GreatSQL][index_text]>show variables like '%slow_query_log%';+-----------------------------------+-------------------------+| Variable_name                     | Value                   |+-----------------------------------+-------------------------+| slow_query_log                    | ON                      || slow_query_log_always_write_time  | 10.000000               || slow_query_log_file               | /data/GreatSQL/slow.log || slow_query_log_use_global_control |                         |+-----------------------------------+-------------------------+4 rows in set (0.00 sec)set global slow_query_log='ON'; #开启慢查询

修改慢查询计入时间

# 测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并执行下述语句mysql> set global long_query_time = 1;mysql> show global variables like '%long-query_time%';只对新建会话起作用# 即更改global 也更改了session变量mysql> set long_query_time=1;mysql> show variables like '%long_query_time%';  

补充:配置文件中一并设置参数
如下的方式相较于前面的命令行方式，可以看作是永久设置的方式。

修改my.cnf 文件，[mysqld]下增加或修改参数long_query_time、slow_query_log和slow_query_log_file后，然后重启MySQL服务器。|

[mysqld]slow_query_log=ON #开启慢查询日志的开关slow_query_log_file=/var/lib/mysql/my-slow.log #慢查询日志的目录和文件名信息long_query_time=3 #设置慢查询的阈值为3秒，超出此设定值的SQL即被记录到慢查询日志log_output=FILE

如果不指定存储路径，慢查询日志将默认存储到MySQL数据库的数据文件夹下。如果不指定文件名，默认文件名为hostname-slow.log。

查看慢查询数目

查询当前系统中有多少条慢查询记录

(Tue Aug 16 16:57:51 2022)[root@GreatSQL][index_text]>SHOW GLOBAL STATUS LIKE '%Slow_queries%';+---------------+-------+| Variable_name | Value |+---------------+-------+| Slow_queries  | 17    |+---------------+-------+1 row in set (0.00 sec)

创建函数：

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 ;

创建存储过程：

DELIMITER //CREATE PROCEDURE insert_stu1( START INT , max_num INT )BEGINDECLARE 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);

测试及分析

1.测试

(Tue Aug 16 17:04:51 2022)[root@GreatSQL][index_text]>SELECT * FROM student WHERE stuno = 3455655;+---------+---------+--------+------+---------+| id      | stuno   | name   | age  | classId |+---------+---------+--------+------+---------+| 3355654 | 3455655 | vlTLXD |   11 |     404 |+---------+---------+--------+------+---------+1 row in set (9.50 sec)(Tue Aug 16 17:05:40 2022)[root@GreatSQL][index_text]>SELECT * FROM student WHERE name = 'vlTLXD';+---------+---------+--------+------+---------+| id      | stuno   | name   | age  | classId |+---------+---------+--------+------+---------+| 1002293 | 1102294 | VltlXd |   54 |     273 ||  219607 |  319608 | VLTlxD |   57 |     903 ||  126928 |  226929 | vLtLxd |   12 |     903 ||  305059 |  405060 | vLtLxd |   13 |     962 ||  398328 |  498329 | vLtLxd |   11 |     881 || 1344191 | 1444192 | vLtLxd |  100 |     787 || 2377317 | 2477318 | vlTLXD |   11 |     387 || 2039748 | 2139749 | vlTLXD |   10 |     352 || 3495402 | 3595403 | vlTLXD |   12 |     407 || 2803354 | 2903355 | vLtLxd |  100 |     797 || 2588242 | 2688243 | vlTLXD |  100 |     266 || 3518222 | 3618223 | vLtLxd |   14 |      27 || 3355654 | 3455655 | vlTLXD |   11 |     404 |+---------+---------+--------+------+---------+13 rows in set (10.95 sec)

2.分析

(Tue Aug 16 17:08:42 2022)[root@GreatSQL][index_text]>show status like 'slow_queries';+---------------+-------+| Variable_name | Value |+---------------+-------+| Slow_queries  | 2     |+---------------+-------+1 row in set (0.00 sec)

补充说明:

除了上述变量，控制慢查询日志的还有一个系统变量: min_examined_row_limit。这个变量的意思是，查询扫描过的最少记录数。这个变量和查询执行时间，共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过long_query_time的值，那么，这个查询就被记录到慢查询日志中; 反之，则不被记录到慢查询日志中。

(Tue Aug 16 17:09:51 2022)[root@GreatSQL][index_text]>show variables like 'min%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| min_examined_row_limit | 100   |+------------------------+-------+1 row in set (0.00 sec)

可以根据需要，通过修改“my.ini"文件，来修改"min_examined_row_limit”的值

慢查询日志分析工具：mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow 。

查看mysqldumpslow的帮助信息

mysqldumpslow --help

mysqldumpslow 命令的具体参数如下：

• -a: 不将数字抽象成N，字符串抽象成S
• -s: 是表示按照何种方式排序： c: 访问次数l: 锁定时间r: 返回记录t: 查询时间al:平均锁定时间ar:平均返回记录数at:平均查询时间 （默认方式）ac:平均查询次数
• -t: 即为返回前面多少条的数据；
• -g: 后边搭配一个正则匹配模式，大小写不敏感的

关闭慢查询日志

一般在调优的时候才需要开启慢查询，否则是不需要开启慢查询的

方式1：永久性方式

[mysqld]slow_query_log=OFF

或者，把slow_query_log一项注释掉 或 删除

[mysqld]#slow_query_log =OFF

重启MySQL服务，执行如下语句查询慢日志功能。
重启MySQL服务

systemctl restart mysqld

SHOW VARIABLES LIKE '%slow%'; #查询慢查询日志所在目录SHOW VARIABLES LIKE '%long_query_time%'; #查询超时时长

方式2：临时性方式

使用SET语句来设置。 （1）停止MySQL慢查询日志功能，具体SQL语句如下。

SET GLOBAL slow_query_log=off;

（2）重启MySQL服务，使用SHOW语句查询慢查询日志功能信息，具体SQL语句如下

SHOW VARIABLES LIKE '%slow%';#以及SHOW VARIABLES LIKE '%long_query_time%';

删除慢查询日志

(Wed Aug 17 14:29:25 2022)[root@GreatSQL][(none)]>show variables like '%slow_query_log%';+-----------------------------------+-------------------------+| Variable_name                     | Value                   |+-----------------------------------+-------------------------+| slow_query_log                    | ON                      || slow_query_log_always_write_time  | 10.000000               || slow_query_log_file               | /data/GreatSQL/slow.log || slow_query_log_use_global_control |                         |+-----------------------------------+-------------------------+4 rows in set (0.00 sec)

从执行结果可以看出，慢查询日志的目录默认为MySQL的数据目录，在该目录下手动删除慢查询日志文件即可。
使用命令mysqladmin flush-logs来重新生成查询日志文件，具体命令如下，执行完毕会在数据目录下重新生成慢查询日志文件。

mysqladmin -uroot -p flush-logs slow 单纯只重置slow 日志，如果没加slow 就会重置其他日志

五、查看 SQL 执行成本：SHOW PROFILE

show profile在《逻辑架构》章节中讲过，这里作为复习。

Show Profile是MySQL提供的可以用来分析当前会话中SQL都做了什么、执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。

我们可以在会话级别开启这个功能

(Wed Aug 17 14:32:51 2022)[root@GreatSQL][(none)]>show variables like 'profiling';+---------------+-------+| Variable_name | Value |+---------------+-------+| profiling     | OFF   |+---------------+-------+1 row in set (0.00 sec)

通过设置profiling='ON’ 来开启 show profile：

(Wed Aug 17 14:36:51 2022)[root@GreatSQL][(none)]>set profiling = 'ON';Query OK, 0 rows affected, 1 warning (0.00 sec)(Wed Aug 17 14:38:14 2022)[root@GreatSQL][(none)]>show variables like 'profiling';+---------------+-------+| Variable_name | Value |+---------------+-------+| profiling     | ON    |+---------------+-------+1 row in set (0.01 sec)

然后执行相关的查询语句。接着看下当前会话都有哪些 profiles，使用下面这条命令：

show profiles;

如果我们想要查看最近一次查询的开销，可以使用：

show profile;

我们也可以查看指定的Query lD的开销，比如show profile for query 2查询结果是一样的。在SHOWPROFILE中我们可以查看不同部分的开销，比如cpu、block.io等:

show profile cpu,block io for query 2;

如果是executing比较长就可能是代码哪里没写好，使用explain 继续查询问题

show profile的常用查询参数：

① ALL：显示所有的开销信息。

② BLOCK IO：显示块IO开销。

③ CONTEXT SWITCHES：上下文切换开销。

④ CPU：显示CPU开销信息。

⑤ IPC：显示发送和接收开销信息。

⑥ MEMORY：显示内存开销信息。

⑦ PAGE FAULTS：显示页面错误开销信息。

⑧ SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。

⑨ SWAPS：显示交换次数开销信息。

日常开发需注意的结论:

converting HEAP to MyISAM: 查询结果太大，内存不够，数据往磁盘上搬了。
creating tmp table: 创建临时表。先拷贝数据到临时表，用完后再删除临时表。
Copying to tmp table on disk:把内存中临时表复制到磁盘上，警惕!
locked。
如果在show profile诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化。

注意:
不过SHOW PROFILE命令将被弃用，我们可以从information_schema中的profiling数据表进行查看。

六、分析查询语句：EXPLAIN

概述

定位了查询慢的SQL之后，我们就可以使用EXPLAIN或DESCRIBE工具做针对性的分析查询语句。 DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。

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

这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。MySQL为我们提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，大家看懂EXPLAIN语句的各个输出项，可以有针对性的提升我们
查询语句的性能。

1.能做什么?

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

版本情况

MySQL 5.6.3以前只能 EXPLAIN SELECT ；MYSQL 5.6.3以后就可以EXPLAIN SELECT，UPDATE，DELETE
在5.7以前的版本中，想要显示partitions需要使用 explain partitions 命令；想要显示filtered 需要使用 explain extended 命令。在5.7版本后，默认explain直接显示partitions和filtered中的信息。

基本语法

如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN ，就像这样：

EXPLAIN SELECT * FROM student_info LIMIT 10;

(Wed Aug 17 14:47:09 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT 1;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+| 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 |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+1 row in set, 1 warning (0.00 sec)

输出的上述信息就是所谓的执行计划。在这个执行计划的辅助下，我们需要知道应该怎样改进自己的查询语句以使查询执行起来更高效。其实除了以SELECT开头的查询语句，其余的DELETE、INSERT、REPLACE以及UPDATE语句等都可以加上EXPLAIN,用来查看这些语句的执行计划，只是平时我们对SELECT语句更感兴趣。

注意：执行EXPLAIN时并没有真正的执行该后面的语句，因此可以安全的查看执行计划。

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

EXPLAIN各列作用

table

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

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

(Wed Aug 17 14:58:42 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT * FROM s1;+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+------------------------------+| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+------------------------------+|  1 | SIMPLE      | <gather2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | Parallel execute (4 workers) ||  2 | SIMPLE      | s1        | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | NULL                         |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+------------------------------+2 rows in set, 1 warning (0.00 sec)

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

(Wed Aug 17 15:01:56 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT * FROM s1 INNER JOIN s2;+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                         |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+|  1 | SIMPLE      | <gather2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | Parallel execute (4 workers)  ||  2 | SIMPLE      | s1        | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | NULL                          ||  2 | SIMPLE      | s2        | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | Using join buffer (hash join) |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+3 rows in set, 1 warning (0.01 sec)S1在前S1是驱动表，S2在后，S2是被驱动表驱动表和被驱动表是 优化器决定的，他认为哪个比较好久用哪个

id

在一个大的查询语句中每个SELECT关键字都对应一个唯一的id,select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序

正常来说一个select 一个id ，也有例外的可能，查询优化器做了优化

(Wed Aug 17 15:50:13 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    1 |   100.00 | NULL  |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

一个相同的id，一个SELECT对应一个id

(Wed Aug 17 15:50:18 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT * FROM s1 INNER JOIN s2;+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                         |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+|  1 | SIMPLE      | <gather2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | Parallel execute (4 workers)  ||  2 | SIMPLE      | s1        | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | NULL                          ||  2 | SIMPLE      | s2        | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | Using join buffer (hash join) |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+3 rows in set, 1 warning (0.00 sec)

两个id

(Wed Aug 17 15:57:54 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+|  1 | PRIMARY     | s1    | NULL       | ALL   | idx_key3      | NULL     | NULL    | NULL | 9895 |   100.00 | Using where ||  2 | SUBQUERY    | s2    | NULL       | index | idx_key1      | idx_key1 | 303     | NULL | 9895 |   100.00 | Using index |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)

查询优化器优化

查询优化器可能对涉及子查询的查询语句进行重写,转变为多表查询的操作

(Wed Aug 17 15:57:57 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');+----+-------------+-------+------------+--------+---------------+----------+---------+--------------------+------+----------+------------------------------------+| id | select_type | table | partitions | type   | possible_keys | key      | key_len | ref                | rows | filtered | Extra                              |+----+-------------+-------+------------+--------+---------------+----------+---------+--------------------+------+----------+------------------------------------+|  1 | SIMPLE      | s1    | NULL       | ALL    | idx_key1      | NULL     | NULL    | NULL               | 9895 |   100.00 | Using where                        ||  1 | SIMPLE      | s2    | NULL       | eq_ref | idx_key2      | idx_key2 | 5       | index_text.s1.key1 |    1 |    10.00 | Using index condition; Using where |+----+-------------+-------+------------+--------+---------------+----------+---------+--------------------+------+----------+------------------------------------+2 rows in set, 2 warnings (0.00 sec)运行结果： id 只有一个，原因是查询优化器做了优化

Union去重
原本想的1个select 一个 id , 预计两个。

union 去重，union all 不去重

(Wed Aug 17 16:02:04 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|  1 | PRIMARY      | s1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | NULL            ||  2 | UNION        | s2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | NULL            || NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+3 rows in set, 1 warning (0.00 sec)union 去重，union all 不去重

union all 不去重 所以不需要放在临时表里面

(Wed Aug 17 16:03:51 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+|  1 | PRIMARY     | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | NULL  ||  2 | UNION       | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | NULL  |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+2 rows in set, 1 warning (0.00 sec)

小结:

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

select_type

一条大的查询语句里边可以包含若干个SELECT关键字，每个SELECT关键字代表着一个小的查询语句，而每个SELECT关键字的FROM子句中都可以包含若干张表(这些表用来做连接查询)，每一张表都对应着执行计划输出中的一条记录，对于在同一个SELECT关键字中的表来说，它们的id值是相同的。

MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色，我们看一下
select_type都能取哪些值，请看官方文档:

SIMPLE

• 查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型
• 连接查询也算是SIMPLE类型

PRIMARY 与 UNION与 UNION RESULT

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

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

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

MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT

SUBQUERY
如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY，比如下边这个查询:

• 在MySQL中，semi-join是专门针对SPJ(没有group by/order by等操作) 的IN/Exists子查询进行优化的一种join语义，起到了对外层表的过滤作用，通过将相关/非相关subquery转换为semi join来充分利用join reordering的灵活性，以期获取最高的执行效率。

(Wed Aug 17 16:08:52 2022)[root@GreatSQL][index_text]> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+|  1 | PRIMARY     | s1    | NULL       | ALL   | idx_key3      | NULL     | NULL    | NULL | 9895 |   100.00 | Using where ||  2 | SUBQUERY    | s2    | NULL       | index | idx_key1      | idx_key1 | 303     | NULL | 9895 |   100.00 | Using index |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)

DEPENDENT SUBQUERY
如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是相关子查询，则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY

(Wed Aug 17 16:13:46 2022)[root@GreatSQL][index_text]> EXPLAIN SELECT * FROM s1     ->  WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';、+----+--------------------+-------+------------+--------+-------------------+----------+---------+--------------------+------+----------+-------------+| id | select_type        | table | partitions | type   | possible_keys     | key      | key_len | ref                | rows | filtered | Extra       |+----+--------------------+-------+------------+--------+-------------------+----------+---------+--------------------+------+----------+-------------+|  1 | PRIMARY            | s1    | NULL       | ALL    | idx_key3          | NULL     | NULL    | NULL               | 9895 |   100.00 | Using where ||  2 | DEPENDENT SUBQUERY | s2    | NULL       | eq_ref | idx_key2,idx_key1 | idx_key2 | 5       | index_text.s1.key2 |    1 |    10.00 | Using where |+----+--------------------+-------+------------+--------+-------------------+----------+---------+--------------------+------+----------+-------------+2 rows in set, 2 warnings (0.00 sec)

注意的是，select_type为DEPENDENT SUBQUERY的查询可能会被执行多次。

DEPENDENT UNION

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

(Wed Aug 17 16:18:33 2022)[root@GreatSQL][index_text]> EXPLAIN SELECT * FROM s1     ->  WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');+----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+| id | select_type        | table      | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra                    |+----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+|  1 | PRIMARY            | s1         | NULL       | ALL  | NULL          | NULL     | NULL    | NULL  | 9895 |   100.00 | Using where              ||  2 | DEPENDENT SUBQUERY | s2         | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    1 |   100.00 | Using where; Using index ||  3 | DEPENDENT UNION    | s1         | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    1 |   100.00 | Using where; Using index || NULL | UNION RESULT       | <union2,3> | NULL       | ALL  | NULL          | NULL     | NULL    | NULL  | NULL |     NULL | Using temporary          |+----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+4 rows in set, 1 warning (0.00 sec)

这里优化器会重构成exist

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

(Wed Aug 17 16:19:29 2022)[root@GreatSQL][index_text]> EXPLAIN SELECT *     ->  FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1;+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+| id | select_type | table      | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL     | NULL    | NULL | 9895 |   100.00 | NULL        ||  2 | DERIVED     | s1         | NULL       | index | idx_key1      | idx_key1 | 303     | NULL | 9895 |   100.00 | Using index |+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)

MATERIALIZED

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

(Wed Aug 17 16:22:32 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);子查询被转为了物化表 +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------------+------+----------+-------------+| id | select_type  | table       | partitions | type   | possible_keys       | key                 | key_len | ref                | rows | filtered | Extra       |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------------+------+----------+-------------+|  1 | SIMPLE       | s1          | NULL       | ALL    | idx_key1            | NULL                | NULL    | NULL               | 9895 |   100.00 | Using where ||  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 303     | index_text.s1.key1 |    1 |   100.00 | NULL        ||  2 | MATERIALIZED | s2          | NULL       | index  | idx_key1            | idx_key1            | 303     | NULL               | 9895 |   100.00 | Using index |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------------+------+----------+-------------+3 rows in set, 1 warning (0.00 sec)

UNCACHEABLE SUBQUERY

• 不常用

UNCACHEABLE UNION

• 不常用

type

针对单表的访问方法

执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，又称"访问类型”，其中的type列就表明了这个访问方法是啥，是较为重要的一个指标。比如，看到type列的值是ref，表明MySQL即将使用ref访问方法来执行对s1表的查询。

完整的访问方法如下：system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ，index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL越靠前越希望看到，越往前效果越好

我们详细解释一下：

system
当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system。
innodb系统不会存条数字段，MyISAM会存储这么一个字段

const
当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const
表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!

EXPLAIN SELECT * FROM s1 WHERE id = 10005;

eq_ref
对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。

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

(Wed Aug 17 16:35:32 2022)[root@GreatSQL][index_text]> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;+----+-------------+-----------+------------+--------+---------------+---------+---------+------------------+------+----------+------------------------------+| id | select_type | table     | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra                        |+----+-------------+-----------+------------+--------+---------------+---------+---------+------------------+------+----------+------------------------------+|  1 | SIMPLE      | <gather2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL             | 9895 |   100.00 | Parallel execute (4 workers) ||  2 | SIMPLE      | s1        | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL             | 9895 |   100.00 | NULL                         ||  2 | SIMPLE      | s2        | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | index_text.s1.id |    1 |   100.00 | NULL                         |+----+-------------+-----------+------------+--------+---------------+---------+---------+------------------+------+----------+------------------------------+3 rows in set, 1 warning (0.00 sec)

从执行计划的结果中可以看出，MySQL打算将s2作为驱动表，s1作为被驱动表，重点关注s1的访问方法是 eq_ref ，表明在访问s1表的时候可以 通过主键的等值匹配 来进行访问。

ref
非唯一性索引扫描，返回匹配某个单独值的所有行本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

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

(Wed Aug 17 16:35:38 2022)[root@GreatSQL][index_text]> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    1 |   100.00 | NULL  |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

fulltext
全文索引

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

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

index_merge
该联接类型表示使用了索引合并优化方法。

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

(Wed Aug 17 16:37:44 2022)[root@GreatSQL][index_text]> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+| id | select_type | table | partitions | type        | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                                       |+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+|  1 | SIMPLE      | s1    | NULL       | index_merge | idx_key1,idx_key3 | idx_key1,idx_key3 | 303,303 | NULL |    2 |   100.00 | Using union(idx_key1,idx_key3); Using where |+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+1 row in set, 1 warning (0.00 sec)

从执行计划的 type 列的值是 index_merge 就可以看出，MySQL 打算使用索引合并的方式来执行对 s1 表的查询。

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

(Wed Aug 17 16:40:02 2022)[root@GreatSQL][index_text]> EXPLAIN SELECT * FROM s1     ->  WHERE key2 IN (SELECT id FROM s2 WHERE s1.key1 = s2.key1) OR key3 = 'a';+----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+| id | select_type        | table | partitions | type            | possible_keys    | key     | key_len | ref  | rows | filtered | Extra       |+----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+|  1 | PRIMARY            | s1    | NULL       | ALL             | idx_key3         | NULL    | NULL    | NULL | 9895 |   100.00 | Using where ||  2 | DEPENDENT SUBQUERY | s2    | NULL       | unique_subquery | PRIMARY,idx_key1 | PRIMARY | 4       | func |    1 |    10.00 | Using where |+----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+2 rows in set, 2 warnings (0.00 sec)

index_subquery
该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引

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

range
只检索给定范围的行,使用一个索引来选择行。
如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法

EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');#同上EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';

index
Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。(也就是说虽然al和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的)

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

索引覆盖，INDEX idx_key_part(key_part1, key_part2, key_part3） 这3个构成一个复合索引 key_part3 在复合索引里面，查询的字段也在索引里面，干脆就直接遍历索引查出数据
思考： 好处，索引存的数据少，数据少页就少，这样可以减少io。

ALL
将遍历全表以找到匹配的行

EXPLAIN SELECT * FROM s1;

一般来说，这些访问方法中除了All这个访问方法外，其余的访问方法都能用到索引，除了index_merge访问方法外，其余的访问方法都最多只能用到一个索引。

小结

结果值从最好到最坏依次是：

system > const > eq_ref > ref >fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > index > ALL

SQL 性能优化的目标：至少要达到range级别，要求是ref 级别，最好是consts级别。（阿里巴巴开发手册要求）

possible_keys和key

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

(Wed Aug 17 16:41:20 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';+----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys     | key      | key_len | ref   | rows | filtered | Extra       |+----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1,idx_key3 | idx_key3 | 303     | const |    1 |     5.00 | Using where |+----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

上述执行计划的possible_keys列的值是idx_key1,idx_key3，表示该查询可能使用到idx_key1 , idx_key3两个索引，然后key列的值是idx_key3，表示经过查询优化器计算使用不同索引的成本后，最后决定使用idx_key3索引只能用一个。所以他要选一个出来用。查看上面 index_merge ,or 的话 会走索引合并。

key_len

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

key_len越小 索引效果越好 这是前面学到的只是，短一点效率更高

但是在联合索引里面，命中一次key_len加一次长度。越长代表精度越高，key_len值越大，效果越好

主要针对于联合索引有一定的参考意义

(Wed Aug 17 16:49:48 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT * FROM s1 WHERE id = 10005;+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|  1 | SIMPLE      | s1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

(Wed Aug 17 16:51:48 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref   | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+|  1 | SIMPLE      | s1    | NULL       | const | idx_key2      | idx_key2 | 5       | const |    1 |   100.00 | NULL  |+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

key2 是int 类型 unique 索引，因为还可能有一个null值，所以 null占一个字段。4+1 = 5

(Wed Aug 17 16:52:16 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    1 |   100.00 | NULL  |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

原因： key1 是varchar字符类型，在utf8中一个字符占3个字节。所以是100*3 = 300。字段可以为空是占用字节1，字符类型变长的字节需要记录具体的实际长度是多少占2个字节，300+1+2=303。

(Wed Aug 17 16:53:42 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';+----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref         | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key_part  | idx_key_part | 606     | const,const |    1 |   100.00 | NULL  |+----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

这里命中了两次联合索引，精度更高，效果更好

key_len的长度计算公式：

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

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

(Wed Aug 17 16:54:18 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    1 |   100.00 | NULL  |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

rows

值越小越好

通常与filtered 一起使用

(Wed Aug 17 16:57:49 2022)[root@GreatSQL][index_text]> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+|  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL |  389 |   100.00 | Using index condition |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)

rows 值越小，代表，数据越有可能在一个页里面，这样io就会更小。

filtered

filtered 的值指返回结果的行占需要读到的行(rows 列的值)的百分比。越大越好

如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

(Wed Aug 17 17:00:07 2022)[root@GreatSQL][index_text]> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                              |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+|  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL |  389 |    10.00 | Using index condition; Using where |+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+1 row in set, 1 warning (0.00 sec)

对于单表查询来说，这个filtered列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的filtered值，它决定了被驱动表要执行的次数(即：rows * filtered)

Extra

顾名思义，Extra列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，一下捡重点介绍

No tables used

当查询语句的没有FROM子句时将会提示该额外信息，比如:

(Wed Aug 17 17:01:16 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT 1;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+| 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 |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+1 row in set, 1 warning (0.00 sec)

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 = 'fUhcQU' and  common_field = 'uDHCOnalcF';

No matching min/max row

当查询列表处有MIN或者MAX聚合函数，但是并没有符合WHERE子句中的搜索条件的记录时，将会提示该额外信息

 # 数据库不存在 QLjKYOx EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'QLjKYOx';

Using index

表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行，效率不错！
如果同时出现using where,表明索引被用来执行索引键值的查找；
如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。

比方说下边这个查询中只需要用到idx_key1而不需要回表操作：

(Wed Aug 17 17:05:59 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    1 |   100.00 | Using index |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

覆盖索引（Covering Index）,一说为索引覆盖。

• 理解方式一: 就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
• 理解方式二: 索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。
• 注意： 如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *， 因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

Using index condition
有些搜索条件中虽然出现了索引列，但却不能使用到索引，看课件理解索引条件下推

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

• 步骤1. 这里key1 > ‘z’ 走了索引，查出了378条数据。。。
• 步骤2. key1 LIKE ‘%b’; 这个条件依然是 key1 索引，，，所以接下来只要在遍历这378个索引。哪些符合 ‘%a’
• 步骤3. 通过步骤2 过滤出了有效 索引。。 这就是Using index condition 。
• 步骤4. 把符合条件的索引，进行回表查询。

完整的说明：

其中的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

Using join buffer (Block Nested Loop)
没有索引的字段进行表关联。

在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，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 intersect(...) 、 Using union(...) 和 Using sort_union(...)

• 如果执行计划的Extra列出现了Using intersect(...)提示，说明准备使用Intersect索引
• 合并的方式执行查询，括号中的...表示需要进行索引合并的索引名称；
• 如果出现了Using union(...)提示，说明准备使用Union索引合并的方式执行查询；
• 出现了Using sort_union(...)提示，说明准备使用`Sort-Union索引合并的方式执行查询。

Zero limit
当我们的LIMIT子句的参数为0时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息

Using filesort
说明ysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为文件排序”

EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;

这个查询语句可以利用idx_key1索引直接取出key1列的10条记录，然后再进行回表操作就好了。但是很多情况下排序操作无法使用到索引，只能在内存中(记录较少的时候）或者磁盘中(记录较多的时候）进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排
序（英文名: filesort)。如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort提示

EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

Using temporary
在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含DISTINCT、GROUP BY、UNION等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示Using temporary提示

 EXPLAIN SELECT DISTINCT common_field FROM s1;

#执行计划中出现Using temporary并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以我们最好能使用索引来替代掉使用临时表。比如：扫描指定的索引idx_key1即可

 (Thu Aug 18 10:35:02 2022)[root@GreatSQL][index_text]> EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------+| id | select_type | table     | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                        |+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------+|  1 | SIMPLE      | <gather2> | NULL       | ALL   | NULL          | NULL     | NULL    | NULL | 9895 |   100.00 | Parallel execute (4 workers) ||  2 | SIMPLE      | s1        | NULL       | index | idx_key1      | idx_key1 | 303     | NULL | 9895 |   100.00 | Using index                  |+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------+2 rows in set, 1 warning (0.00 sec)

小结

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

七、EXPLAIN的进一步使用

EXPLAIN四种输出格式

谈谈EXPLAIN的输出格式。EXPLAIN可以输出四种格式： 传统格式 ， JSON格式 ， TREE格式 以及 可视化输出 。用户可以根据需要选择适用于自己的格式。

传统格式

传统格式简单明了，输出是一个表格形式，概要说明查询计划。

(Thu Aug 18 11:12:23 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;+----+-------------+-----------+------------+------+---------------+----------+---------+--------------------+------+----------+------------------------------+| id | select_type | table     | partitions | type | possible_keys | key      | key_len | ref                | rows | filtered | Extra                        |+----+-------------+-----------+------------+------+---------------+----------+---------+--------------------+------+----------+------------------------------+|  1 | SIMPLE      | <gather2> | NULL       | ALL  | NULL          | NULL     | NULL    | NULL               | 9895 |    90.00 | Parallel execute (4 workers) ||  2 | SIMPLE      | s2        | NULL       | ALL  | idx_key1      | NULL     | NULL    | NULL               | 9895 |    90.00 | Using where                  ||  2 | SIMPLE      | s1        | NULL       | ref  | idx_key1      | idx_key1 | 303     | index_text.s2.key1 |    1 |   100.00 | Using index                  |+----+-------------+-----------+------------+------+---------------+----------+---------+--------------------+------+----------+------------------------------+3 rows in set, 1 warning (0.01 sec)

JSON格式

第1种格式中介绍的EXPLAIN语句输出中缺少了一个衡量执行计划好坏的重要属性——成本。而JSON格式是四种格式里面输出信息最详尽的格式，里面包含了执行的成本信息。

JSON格式：在EXPLAIN单词和真正的查询语句中间加上 FORMAT=JSON 。

EXPLAIN FORMAT=JSON SELECT ....  

“cost_info”里边的成本看着怪怪的，它们是怎么计算出来的？先看 s1 表的 “cost_info” 部分：

"cost_info": {    "read_cost": "914.80",    "eval_cost": "98.95",    "prefix_cost": "1013.75",    "data_read_per_join": "1M"}  

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": "247.38",    "eval_cost": "98.95",    "prefix_cost": "1360.08",    "data_read_per_join": "1M"}

由于s2表是被驱动表，所以可能被读取多次，这里的read_cost和eval_cost是访问多次 s2 表后累
加起来的值，大家主要关注里边儿的prefix_cost的值代表的是整个连接查询预计的成本，也就是单
次查询s1表和多次查询s2表后的成本的和，也就是 :

247.38 + 98.95 + 1013.75 = 1360.08

TREE格式

TREE格式是8.0.16版本之后引入的新格式，主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询

可视化输出

可视化输出，可以通过MySQL Workbench可视化查看MySQL的执行计划。通过点击Workbench的放大镜图标，即可生成可视化的查询计划。

SHOW WARNINGS的使用

(Thu Aug 18 14:17:04 2022)[root@GreatSQL][index_text]>EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;+----+-------------+-----------+------------+------+---------------+----------+---------+--------------------+------+----------+------------------------------+| id | select_type | table     | partitions | type | possible_keys | key      | key_len | ref                | rows | filtered | Extra                        |+----+-------------+-----------+------------+------+---------------+----------+---------+--------------------+------+----------+------------------------------+|  1 | SIMPLE      | <gather2> | NULL       | ALL  | NULL          | NULL     | NULL    | NULL               | 9895 |    90.00 | Parallel execute (4 workers) ||  2 | SIMPLE      | s2        | NULL       | ALL  | idx_key1      | NULL     | NULL    | NULL               | 9895 |    90.00 | Using where                  ||  2 | SIMPLE      | s1        | NULL       | ref  | idx_key1      | idx_key1 | 303     | index_text.s2.key1 |    1 |   100.00 | Using index                  |+----+-------------+-----------+------------+------+---------------+----------+---------+--------------------+------+----------+------------------------------+3 rows in set, 1 warning (0.00 sec)使用完explain 后紧接着使用 SHOW WARNINGS \G使用完explain 后紧接着使用 SHOW WARNINGS \G使用完explain 后紧接着使用 SHOW WARNINGS \G(Thu Aug 18 14:17:11 2022)[root@GreatSQL][index_text]>SHOW WARNINGS\G*************************** 1. row ***************************  Level: Note   Code: 1003Message: /* select#1 */ select `index_text`.`s1`.`key1` AS `key1`,`index_text`.`s2`.`key1` AS `key1` from `index_text`.`s1` join `index_text`.`s2` where ((`index_text`.`s1`.`key1` = `index_text`.`s2`.`key1`) and (`index_text`.`s2`.`common_field` is not null))1 row in set (0.00 sec)(Thu Aug 18 14:17:23 2022)[root@GreatSQL][index_text]>

大家可以看到SHOW WARNINGS展示出来的信息有三个字段，分别是Level、Code、Message。我们最常见的就是Code为1003的信息，当Code值为1003时,Message字段展示的信息类似于查询优化器将我们的查询语句重写后的语句。比如我们上边的查询本来是一个左(外)连接查询，但是有一个s2.common_field IS NOT NULL的条件，这就会导致查询优化器把左(外）连接查询优化为内连接查询，从 SHOW WARNINGS的Message字段也可以看出来，原本的LEFT JOIN已经变成了JOIN(内连接)。

exists：外表先进行循环查询，将查询结果放入exists的子查询中进行条件验证，确定外层查询数据是否保留

in： 先查询内表，将内表的查询结果当做条件提供给外表查询语句进行比较

分析优化器执行计划：trace

OPTIMIZER_TRACE 是MySQL 5.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 * from student where id < 10;

最后， 查询 information_schema.optimizer_trace 就可以知道MySQL是如何执行SQL的 :

select * from information_schema.optimizer_trace\G

(Thu Aug 18 14:33:33 2022)[root@GreatSQL][index_text]>select * from information_schema.optimizer_trace\G*************************** 1. row ***************************第1部分：查询语句QUERY: select * from student where id < 10第2部分：QUERY字段对应语句的跟踪信息TRACE: {  "steps": [    {      "join_preparation": { /*预备工作*/        "select#": 1,        "steps": [          {            "expanded_query": "/* select#1 */ select `student`.`id` AS `id`,`student`.`stuno` AS `stuno`,`student`.`name` AS `name`,`student`.`age` AS `age`,`student`.`classId` AS `classId` from `student` where (`student`.`id` < 10)"          }        ] /* steps */      } /* join_preparation */    },    {      "join_optimization": {/*进行优化*/        "select#": 1,        "steps": [          {            "condition_processing": {/*条件处理*/              "condition": "WHERE",              "original_condition": "(`student`.`id` < 10)",              "steps": [                {                  "transformation": "equality_propagation",                  "resulting_condition": "(`student`.`id` < 10)"                },                {                  "transformation": "constant_propagation",                  "resulting_condition": "(`student`.`id` < 10)"                },                {                  "transformation": "trivial_condition_removal",                  "resulting_condition": "(`student`.`id` < 10)"                }              ] /* steps */            } /* condition_processing */          },          {            "substitute_generated_columns": {/*替换生成的列*/            } /* substitute_generated_columns */          },          {            "table_dependencies": [   /* 表的依赖关系*/              {                "table": "`student`",                "row_may_be_null": false,                "map_bit": 0,                "depends_on_map_bits": [                ] /* depends_on_map_bits */              }            ] /* table_dependencies */          },          {            "ref_optimizer_key_uses": [ /* 使用键*/            ] /* ref_optimizer_key_uses */          },          {            "rows_estimation": [ /*行判断*/              {                "table": "`student`",                "range_analysis": {                  "table_scan": {                    "rows": 3945207,                    "cost": 404306                  } /* table_scan */,/*表扫描*/                  "potential_range_indexes": [                    {                      "index": "PRIMARY",                      "usable": true,                      "key_parts": [                        "id"                      ] /* key_parts */                    }                  ] /* potential_range_indexes */,                  "setup_range_conditions": [                   ] /* 设置条件范围 */,                  "group_index_range": {                    "chosen": false,                    "cause": "not_group_by_or_distinct"                  } /* group_index_range */,                  "skip_scan_range": {                    "potential_skip_scan_indexes": [                      {                        "index": "PRIMARY",                        "usable": false,                        "cause": "query_references_nonkey_column"                      }                    ] /* potential_skip_scan_indexes */                  } /* skip_scan_range */,                  "analyzing_range_alternatives": {/*分析范围选项*/                    "range_scan_alternatives": [                      {                        "index": "PRIMARY",                        "ranges": [                          "id < 10"                        ] /* ranges */,                        "index_dives_for_eq_ranges": true,                        "rowid_ordered": true,                        "using_mrr": false,                        "index_only": false,                        "in_memory": 0.159895,                        "rows": 9,                        "cost": 1.79883,                        "chosen": true                      }                    ] /* range_scan_alternatives */,                    "analyzing_roworder_intersect": {                      "usable": false,                      "cause": "too_few_roworder_scans"                    } /* analyzing_roworder_intersect */                  } /* analyzing_range_alternatives */,                  "chosen_range_access_summary": {/*选择范围访问摘要*/                    "range_access_plan": {                      "type": "range_scan",                      "index": "PRIMARY",                      "rows": 9,                      "ranges": [                        "id < 10"                      ] /* ranges */                    } /* range_access_plan */,                    "rows_for_plan": 9,                    "cost_for_plan": 1.79883,                    "chosen": true                  } /* chosen_range_access_summary */                } /* range_analysis */              }            ] /* rows_estimation */          },          {            "considered_execution_plans": [/*考虑执行计划*/              {                "plan_prefix": [                ] /* plan_prefix */,                "table": "`student`",                "best_access_path": {/*最佳访问路径*/                  "considered_access_paths": [                    {                      "rows_to_scan": 9,                      "access_type": "range",                      "range_details": {                        "used_index": "PRIMARY"                      } /* range_details */,                      "resulting_rows": 9,                      "cost": 2.69883,                      "chosen": true                    }                  ] /* considered_access_paths */                } /* best_access_path */,                "condition_filtering_pct": 100, /*行过滤百分比*/                "rows_for_plan": 9,                "cost_for_plan": 2.69883,                "chosen": true              }            ] /* considered_execution_plans */          },          {            "attaching_conditions_to_tables": { /*将条件附加到表上*/              "original_condition": "(`student`.`id` < 10)",              "attached_conditions_computation": [              ] /* attached_conditions_computation */,              "attached_conditions_summary": [ /*附加条件概要*/                {                  "table": "`student`",                  "attached": "(`student`.`id` < 10)"                }              ] /* attached_conditions_summary */            } /* attaching_conditions_to_tables */          },          {            "finalizing_table_conditions": [              {                "table": "`student`",                "original_table_condition": "(`student`.`id` < 10)",                "final_table_condition   ": "(`student`.`id` < 10)"              }            ] /* finalizing_table_conditions */          },          {            "refine_plan": [ /*精简计划*/              {                "table": "`student`"              }            ] /* refine_plan */          }        ] /* steps */      } /* join_optimization */    },    {      "join_execution": {  /*执行*/        "select#": 1,        "steps": [        ] /* steps */      } /* join_execution */    }  ] /* steps */}//*第3部分：跟踪信息过长时，被截断的跟踪信息的字节数。*/MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 /*丢失的超出最大容量的字节*//*第4部分：执行跟踪语句的用户是否有查看对象的权限。当不具有权限时，该列信息为1且TRACE字段为空，一般在调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下，会出现此问题。*/INSUFFICIENT_PRIVILEGES: 0 /*缺失权限*/

MySQL监控分析视图-sys schema

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

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开头，展示等待事件的延迟情况。

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_deletedfrom sys.schema_index_statistics where table_schema='dbname' ;

表相关

# 1. 查询表的访问量select table_schema,table_name,sum(io_read_requests+io_write_requests) as io fromsys.schema_table_statistics group by table_schema,table_name order by io desc;# 2. 查询占用bufferpool较多的表select object_schema,object_name,allocated,datafrom 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_analysisorder by exec_count desc;#2. 监控使用了排序的SQLselect db,exec_count,first_seen,last_seen,queryfrom sys.statements_with_sorting limit 1;#3. 监控使用了临时表或者磁盘临时表的SQLselect db,exec_count,tmp_tables,tmp_disk_tables,queryfrom sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0order by (tmp_tables+tmp_disk_tables) desc;

IO 相关

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

Innodb 相关

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

风险提示:

通过sys库去查询时，MySQL会消耗大量资源去收集相关信息，严重的可能会导致业务请求被阻塞，从而引起故障。建议生产上不要频繁的去查询sys或者performance_schema、information_schema来完成监控、巡检等工作。