MySQL-09.性能分析工具的使用

1.数据库服务器的优化步调

当碰到数据库调优标题时，思索的流程如下图。
整个流程划分成了观察(Show status)和行动(Action)两个部分。字母S的部分代表观察(会使用相应的分析工具)，字母A代表的部分是行动(对应分析可以采取的行动)。

上图，就是数据库调优的思绪。如果发现执行SQL时存在不规则或卡顿的时间，就可以接纳分析工具帮我们定位有标题的SQL，这三种分析工具你可以理解是SQL调优的三个步调：慢查询、EXPLAIN和SHOW PROFILING。
小结：

2.查看体系性能参数

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

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

复制代码

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

• Connections：连接MySQL服务器的次数。
  
• Uptime：MySQL服务器的上线时间。单位是秒s
  
• Slow_queries：慢查询的次数。
  
• Innodb_rows_read：Select查询返回的行数
  
• Innodb_rows_inserted：执行INSERT操纵插入的行数
  
• Innodb_rows_updated：执行UPDATE操纵更新的行数
  
• Innodb_rows_deleted：执行DELETE操纵删除的行数
  
• Com_select：查询操纵的次数。
  
• Com_insert：插入操纵的次数。对于批量插入的 INSERT 操纵，只累加一次。
  
• Com_update：更新操纵的次数。
  
• Com_delete：删除操纵的次数。
  

1. show status like 'Connections';#查询服务器连接次数
3. show status like 'Uptime';#查询服务器工作时间
5. show status like 'Slow_queries';#查询MySQL服务器的慢查询次数
7. show status like 'Innodb_rows_%';#查看相关的指令情况

复制代码

慢查询次数参数可以结合慢查询日志找出慢查询语句，然后针对慢查询语句进行表布局优化或者查询语句优化
3.统计SQL的查询成本：last_query_cost

以第8章的student_info表为例

1. CREATE TABLE `student_info` (
2.     `id` INT(11) NOT NULL AUTO_INCREMENT,
3.     `student_id` INT NOT NULL ,
4.     `name` VARCHAR(20) DEFAULT NULL,
5.     `course_id` INT NOT NULL ,
6.     `class_id` INT(11) DEFAULT NULL,
7.     `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
8.     PRIMARY KEY (`id`)
9. ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

复制代码

第8章为student_info表，构建了1000000条数据
如果我们想要查询id = 900001的记载，然后看下查询成本，我们可以直接在聚簇索引上进行查找：

1. mysql> select * from student_info where id = 900001;
2. +--------+------------+--------+-----------+----------+---------------------+
3. | id     | student_id | name   | course_id | class_id | create_time         |
4. +--------+------------+--------+-----------+----------+---------------------+
5. | 900001 |     118791 | NcVxAk |     10058 |    10013 | 2024-05-03 17:41:49 |
6. +--------+------------+--------+-----------+----------+---------------------+
7. 1 row in set (0.00 sec)

复制代码

查看优化器的成本，现实上我们只需要检索一个页即可：

1. mysql> show status like 'last_query_cost';
2. +-----------------+----------+
3. | Variable_name   | Value    |
4. +-----------------+----------+
5. | Last_query_cost | 1.000000 |
6. +-----------------+----------+
7. 1 row in set (0.00 sec)

复制代码

如果我们想要查询id在900001到900100之间的门生记载呢？

1. mysql> select * from student_info where id >= 900001 and id <= 900100;
2. +--------+------------+--------+-----------+----------+---------------------+
3. | id     | student_id | name   | course_id | class_id | create_time         |
4. +--------+------------+--------+-----------+----------+---------------------+
5. | 900001 |     118791 | NcVxAk |     10058 |    10013 | 2024-05-03 17:41:49 |
6. | 900002 |     162004 | yWlrZW |     10050 |    10172 | 2024-05-03 17:41:49 |
7. | 900003 |     160036 | JLNHVI |     10072 |    10159 | 2024-05-03 17:41:49 |
8. ...
9. | 900098 |      56625 | akXhUc |     10002 |    10094 | 2024-05-03 17:41:49 |
10. | 900099 |      22024 | VnDeIe |     10054 |    10109 | 2024-05-03 17:41:49 |
11. | 900100 |     158129 | SSOofI |     10046 |    10007 | 2024-05-03 17:41:49 |
12. +--------+------------+--------+-----------+----------+---------------------+
13. 100 rows in set (0.01 sec)

复制代码

从结果来看，页的数量是刚才的20倍，但是查询的服从并没有明显的变化，现实上这两个SQL查询的时间基本上一样，就是因为接纳了序次读取(第8章提到的序次I/O)的方式将页面一次性加载到缓存池中，然后再进行查找，固然页数量(last_query_cost)增加了不少，但是通过缓冲池的机制，并没有增加多少查询时间。
使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时间。

SQL查询是一个动态的过程，从页的加载的角度来看，我们可以得到以下两点结论：
1.位置决定服从。如果页就在数据库缓冲池中，那么服从是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取服从高很多。
2.批量决定服从。如果我们从磁盘中对单一页进行随机读，那么服从是很低的(差不多10ms)，而接纳序次读取的方式，批量对页进行读取，平均一页的读取服从就会提升很多，甚至要快于单个页面在内存中的随机读取。
所以说，碰到I/O并不消担心，方法找对了，服从还是很高的。我们首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中，其次应该充实使用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取服从也就得到了提升。

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

MySQL的慢查询日志，用来记载在MySQL中的响应时间凌驾阈值的语句，具体指运行时间凌驾long_query_time值的SQL，则会被记载到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上(不含10秒)的语句，认为是超出了我们的最大忍耐时间值。
它的主要作用是，资助我们发现那些执行时间特别长的SQL查询，并且有针对性的进行优化，从而提高体系的团体服从。当我们的数据库服务器发生阻塞，运行变慢的时间，查抄一下慢查询日志，找到那些慢查询，对解决标题很有资助。好比一条sql执行凌驾5秒钟，就算慢sql的话，希望能网络凌驾5s的sql，结合explain进行全面分析。
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一样平常不发起启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记载写入文件。
4.1 开启慢查询日志参数

1.开启slow_query_long

在使用前，我们需要先看下慢查询是否已经开启。

1. mysql> show status like 'last_query_cost';
2. +-----------------+-----------+
3. | Variable_name   | Value     |
4. +-----------------+-----------+
5. | Last_query_cost | 20.290751 |
6. +-----------------+-----------+
7. 1 row in set (0.00 sec)

复制代码

能看到slow_query_log=OFF，我们可以把慢查询日志打开，注意设置变量值的时间需要使用global，否则会报错：

1. mysql> show variables like '%slow_query_log';
2. +----------------+-------+
3. | Variable_name  | Value |
4. +----------------+-------+
5. | slow_query_log | OFF   |
6. +----------------+-------+
7. 1 row in set (0.00 sec)

复制代码

然后，查看慢查询日志是否开启，以及慢查询日志文件的位置：

1. mysql> set global slow_query_log = on;

复制代码

可以看到，此时慢查询已经开启，同时文件生存在/var/lib/mysql/x-slow.log文件中。注意x代表当前linux操纵体系的主机名。这是默认情况。
2.修改long_query_time阈值

接下来我们来看下慢查询的时间阈值设置，使用如下命令

1. mysql> show variables like '%slow_query_log%';
2. +---------------------+------------------------------------------+
3. | Variable_name       | Value                                    |
4. +---------------------+------------------------------------------+
5. | slow_query_log      | ON                                       |
6. | slow_query_log_file | /var/lib/mysql/x-slow.log |
7. +---------------------+------------------------------------------+
8. 2 rows in set (0.00 sec)

复制代码

这里我们想把时间缩短，好比设置1秒，可以这样设置：

1. mysql> show variables like '%long_query_time';#单位是秒s
2. +-----------------+-----------+
3. | Variable_name   | Value     |
4. +-----------------+-----------+
5. | long_query_time | 10.000000 |
6. +-----------------+-----------+
7. 1 row in set (0.00 sec)

复制代码

注意，从执行结果来看，修改全局慢sql的时间阈值，long_query_time的值时，默认对新的会话生效，当前会话还是修改前的值。
增补：配置文件中--并设置参数
可以以修改配置文件的方式，设置为永世的方式，是对比命令行方式，称为永世。

1. mysql> set global long_query_time = 1;
2. Query OK, 0 rows affected (0.00 sec)
4. mysql> show global variables like '%long_query_time';
5. +-----------------+----------+
6. | Variable_name   | Value    |
7. +-----------------+----------+
8. | long_query_time | 1.000000 |
9. +-----------------+----------+
10. 1 row in set (0.00 sec)
12. mysql> show session variables like 'long_query_time';
13. +-----------------+-----------+
14. | Variable_name   | Value     |
15. +-----------------+-----------+
16. | long_query_time | 10.000000 |
17. +-----------------+-----------+
18. 1 row in set (0.01 sec)

复制代码

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

查询当前体系中有多少条慢查询记载

1. [mysqld]
2. slow_query_log=ON #开启慢查询日志的开关
3. slow_query_log_file=/var/lib/mysql/x-slow.log #慢查询日志的目录和文件信息，可以配置成非默认位置
4. long_query_time = 3 #设置慢查询的阈值为3秒，超过此设定的值的sql即被记录到慢查询日志
5. log_output=FILE #以文件的形式输出日志

复制代码

4.3 案例演示

步调1.建表

1. show global status like '%Slow_queries';

复制代码

步调2.设置参数log_bin_trust_function_creators

创建函数，如果报错

1. CREATE TABLE `student` (
2.     `id` INT(11) NOT NULL AUTO_INCREMENT,
3.     `stuno` INT NOT NULL ,
4.     `name` VARCHAR(20) DEFAULT NULL,
5.     `age` INT(3) DEFAULT NULL,
6.     `classId` INT(11) DEFAULT NULL,
7.     PRIMARY KEY (`id`)
8. ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

复制代码

• 命令开启，允许创建函数设置
  

1. This function has none of DETERMINISTIC......

复制代码

步调3.创建函数

随机产生字符串：(同上一章)

1. set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

复制代码

产生随机数值：(同上一章)

1. DELIMITER //
2. CREATE FUNCTION rand_string(n INT)
3.         RETURNS VARCHAR(255) #该函数会返回一个字符串
4. BEGIN
5.     DECLARE chars_str VARCHAR(100) DEFAULT
6.     'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
7.     DECLARE return_str VARCHAR(255) DEFAULT '';
8.     DECLARE i INT DEFAULT 0;
9.     WHILE i < n DO
10.         SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
11.         SET i = i + 1;
12.     END WHILE;
13.     RETURN return_str;
14. END //
15. DELIMITER ;
16. #测试
17. SELECT rand_string(10);

复制代码

步调4.创建存储过程

1. DELIMITER //
2. CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
3. BEGIN
4.     DECLARE i INT DEFAULT 0;
5.             SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
6.     RETURN i;
7. END //
8. DELIMITER ;
9. #测试：
10. SELECT rand_num(10,100);

复制代码

步调5.调用存储过程

1. DELIMITER //
2. CREATE PROCEDURE insert_stu1( START INT , max_num INT )
3. BEGIN
4. DECLARE i INT DEFAULT 0;
5.     SET autocommit = 0; #设置手动提交事务
6.     REPEAT #循环
7.     SET i = i + 1; #赋值
8.     INSERT INTO student (stuno, NAME ,age ,classId ) VALUES
9.     ((START+i),rand_string(6),rand_num(10,100),rand_num(10,1000));
10.     UNTIL i = max_num
11.     END REPEAT;
12.     COMMIT; #提交事务
13. END //
14. DELIMITER ;

复制代码

4.4 测试及分析

1.测试

1. #调用刚刚写好的函数, 4000000条记录,从100001号开始 执行时间较长 大概5分钟左右
2. CALL insert_stu1(100001,4000000)

复制代码

注意，此结果中name的值，是不区分大小写的。是因为，在创建表示未设置Collate也即校对规则。
使用的是当前数据库的校对规则，而在前一章，创建数据库时，也为指定校对规则，所以在mysql8.0.25默认的校对规则是utf8mb4_0900_ai_ci 是不区分大小写的

不消记，知道有这个东西即可。
可以使用show create 语法查看表或数据库使用的校对规则。
还有个小细节，在使用show create 语法查看数据库时，如果数据库名中有-的需要加``才能执行乐成

从上面的结果可以看出来，查询门生编号为“3455655”的门生信息花费时间为0.85秒。查询门生姓名为“oQmLUr”的门生信息花费时间为0.92秒。已经到达了秒的数量级，说明现在查询服从是比较低的，下面的小节我们分析一下原因。
2.分析

1. mysql> SELECT * FROM student WHERE stuno = 3455655;
2. +---------+---------+--------+------+---------+
3. | id      | stuno   | name   | age  | classId |
4. +---------+---------+--------+------+---------+
5. | 3355654 | 3455655 | dOJcnf |   81 |     599 |
6. +---------+---------+--------+------+---------+
7. 1 row in set (0.85 sec)
9. mysql> SELECT * FROM student WHERE name = 'oQmLUr';
10. +---------+---------+--------+------+---------+
11. | id      | stuno   | name   | age  | classId |
12. +---------+---------+--------+------+---------+
13. |  664741 |  764742 | Oqmlur |   55 |     502 |
14. |  895221 |  995222 | OQMlUR |   57 |      85 |
15. | 1068466 | 1168467 | Oqmlur |   56 |     542 |
16. | 2060322 | 2160323 | OQMlUR |   51 |     806 |
17. | 2173596 | 2273597 | OQMlUR |   53 |     870 |
18. | 2354014 | 2454015 | Oqmlur |   51 |     294 |
19. | 2451838 | 2551839 | oQmLUr |   96 |     791 |
20. | 2522014 | 2622015 | oQmLUr |  100 |     935 |
21. | 3249176 | 3349177 | OQMlUR |   54 |     912 |
22. | 3333250 | 3433251 | Oqmlur |   57 |     554 |
23. +---------+---------+--------+------+---------+
24. 10 rows in set (0.92 sec)

复制代码

增补说明
除了上述变量，控制慢查询日志的还有一个体系变量：min_examined_row_limit。这个变量的意思是，查询扫描过的最少记载数。这个变量和查询的执行时间，共同构成了判别一个查询是否是慢查询的条件。如果查询扫描过的记载数大于等于这个变量的值，并且查询执行时间凌驾long_query_time的值，那么，这个查询就被记载到慢查询日志中；反之，则不被记载到慢查询日志中。

1. show status like 'slow_queries';

复制代码

这个值默认是0。与long_query_time=10合在一起，表示只要查询的执行时间凌驾10秒钟，哪怕一条记载也没有扫描过，都要被记载到慢查询日志中。你也可以根据需要，通过修改"my.ini"文件，来修改查询时长，或者通过set指令，用SQL语句修改"min_examined_row_limit"的值。

4.5 慢查询日志分析工具：mysqldumpslow

在生产环境中，如果要手工分析日志，查找，分析sql，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
查看mysqldumpslow的资助信息

1. mysql> show variables like 'min_%';
2. +------------------------+-------+
3. | Variable_name          | Value |
4. +------------------------+-------+
5. | min_examined_row_limit | 0     |
6. +------------------------+-------+
7. 1 row in set (0.00 sec)

复制代码

mysqldumpslow命令的具体参数如下：

1. mysqldumpslow --help #在命令行模式下执行，本质是.sh脚本文件

复制代码

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

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

1. [root@LinuxCentOS7-132 ~]# mysqldumpslow --help
2. Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
4. Parse and summarize the MySQL slow query log. Options are
6.   --verbose    verbose
7.   --debug      debug
8.   --help       write this text to standard output
10.   -v           verbose
11.   -d           debug
12.   -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
13.                 al: average lock time
14.                 ar: average rows sent
15.                 at: average query time
16.                  c: count
17.                  l: lock time
18.                  r: rows sent
19.                  t: query time  
20.   -r           reverse the sort order (largest last instead of first)
21.   -t NUM       just show the top n queries
22.   -a           don't abstract all numbers to N and strings to 'S'
23.   -n NUM       abstract numbers with at least n digits within names
24.   -g PATTERN   grep: only consider stmts that include this string
25.   -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
26.                default is '*', i.e. match all
27.   -i NAME      name of server instance (if using mysql.server startup script)
28.   -l           don't subtract lock time from total time

复制代码

工作常用参考

1. #注意替换为自己的慢sql日志文件
2. [root@LinuxCentOS7-132 mysql]# mysqldumpslow -s t -t 5 /var/lib/mysql/LinuxCentOS7-132-slow.log
4. Reading mysql slow query log from /var/lib/mysql/LinuxCentOS7-132-slow.log
5. Count: 1  Time=268.52s (268s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@xxxx
6.   CALL insert_stu1(N,N)
8. Died at /usr/bin/mysqldumpslow line 162, <> chunk 1.
9. #这里只有一条的原因是，设置的慢sql的long_qiery_time阈值是1秒，而之前测试的sql语句执行时间都小于1s所以，只有调用存储过程的sql语句保存在了慢sql日志文件中
11. [root@LinuxCentOS7-132 mysql]# mysqldumpslow -a -s t -t 5 /var/lib/mysql/LinuxCentOS7-132-slow.log
13. Reading mysql slow query log from /var/lib/mysql/LinuxCentOS7-132-slow.log
14. Count: 1  Time=268.52s (268s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@xxxx
15.   CALL insert_stu1(100001,4000000)
17. Died at /usr/bin/mysqldumpslow line 162, <> chunk 1.
18. #演示-a的作用，就是不将数字抽象成N，字符串抽象成S 其他略

复制代码

4.6 关闭慢查询日志

MySQL服务器停止慢查询日志功能有两种方法：
方式1：永世性方式

1. #得到返回记录集最多的10个SQL
2. mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
4. #得到访问次数最多的10个SQL
5. mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
7. #得到按照时间排序的前10条里面含有左连接的查询语句
8. mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
10. #另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
11. mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

复制代码

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

1. [mysqld]
2. slow_query_log=OFF

复制代码

方式2：暂时性方式

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

1. [mysqld]
2. #slow_query_log=OFF

复制代码

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

1. SET GLOBAL slow_query_log=off;

复制代码

4.7 删除慢查询日志

使用show语句显示慢查询日志信息，具体SQL语句如下

1. SHOW VARIABLES LIKE '%slow%';
2. #以及
3. SHOW VARIABLES LIKE '%long_query_time%';

复制代码

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

1. mysql> SHOW VARIABLES LIKE 'slow_query_log%';
2. +---------------------+------------------------------------------+
3. | Variable_name       | Value                                    |
4. +---------------------+------------------------------------------+
5. | slow_query_log      | ON                                       |
6. | slow_query_log_file | /var/lib/mysql/LinuxCentOS7-132-slow.log |
7. +---------------------+------------------------------------------+
8. 2 rows in set (0.00 sec)

复制代码

提示
慢查询日志都是使用mysqladmin flush-logs命令来删除重修的。使用时一定要注意，一旦执行了这个命令，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，就要先备份。

5.查看SQL执行成本：SHOW PROFILE

在逻辑架构章节有笔记，这里回首

1. mysqladmin -uroot -p flush-logs slow #需要输入命令

复制代码

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数据表进行查看。
6.分析查询语句：EXPLAIN

6.1 概述

定位了查询慢的SQL之后，我们就可以使用EXPLAIN或DESCRIBE工具做针对性的分析查询语句。DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。
MySQL中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析体系中网络到的统计信息，为客户端哀求的Query提供它认为最优的执行计划(他认为最优的数据检索方式，但不见得是DBA认为的最优的，这部分最耗费时间)。
这个执行计划展示了接下来具体执行查询的方式，好比多表连接的序次是什么，对于每个表接纳什么访问方法来具体执行查询等等。MySQL为我们提供了EXPLAIN语句来资助我们查看某个查询语句的具体执行计划，看懂EXPLAIN语句的各个输出项，可以有针对性的提高查询语句的性能。
1.能做什么？

• 表的读取序次
  
• 数据读取操纵的操纵类型
  
• 那些索引可以使用
  
• 那些索引被现实使用
  
• 表之间的引用
  
• 每张表有多少行被优化器查询
  
• 额外信息
  

2.官网介绍

5.7
8.0
3.版本情况

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

6.2 基本语法

EXPLAIN或DESCRIBE语句的语法情势如下：

1. mysql> show variables like 'profiling';
2. +---------------+-------+
3. | Variable_name | Value |
4. +---------------+-------+
5. | profiling     | OFF   |
6. +---------------+-------+
7. 1 row in set (0.01 sec)
9. mysql> set profiling = on;#开启执行计划
10. Query OK, 0 rows affected, 1 warning (0.00 sec)
12. mysql> show variables like 'profiling';
13. +---------------+-------+
14. | Variable_name | Value |
15. +---------------+-------+
16. | profiling     | ON    |
17. +---------------+-------+
18. 1 row in set (0.00 sec)
20. mysql> use atguigudb1;
21. Database changed
22. #执行两条sql
23. mysql> select * from student where stuno = 3453451;
24. +---------+---------+--------+------+---------+
25. | id      | stuno   | name   | age  | classId |
26. +---------+---------+--------+------+---------+
27. | 3353450 | 3453451 | sWJqJx |   31 |     824 |
28. +---------+---------+--------+------+---------+
29. 1 row in set (3.20 sec)
31. mysql> select * from student where name = 'sWJqJx';
32. +---------+---------+--------+------+---------+
33. | id      | stuno   | name   | age  | classId |
34. +---------+---------+--------+------+---------+
35. | 2160136 | 2260137 | SwJQjx |   74 |     228 |
36. | 3353450 | 3453451 | sWJqJx |   31 |     824 |
37. | 3543434 | 3643435 | sWJqJx |   33 |     938 |
38. | 3845668 | 3945669 | SwJQjx |   73 |     164 |
39. | 3918212 | 4018213 | SwJQjx |   76 |     325 |
40. +---------+---------+--------+------+---------+
41. 5 rows in set (0.94 sec)
43. mysql> show profiles;#查看当前会话的执行计划
44. +----------+------------+---------------------------------------------+
45. | Query_ID | Duration   | Query                                       |
46. +----------+------------+---------------------------------------------+
47. |        1 | 0.00142000 | show variables like 'profiling'             |
48. |        2 | 0.00033150 | SELECT DATABASE()                           | #这里为何是select database()函数
49. |        3 | 3.20470075 | select * from student where stuno = 3453451 |
50. |        4 | 0.93289375 | select * from student where name = 'sWJqJx' |
51. +----------+------------+---------------------------------------------+
52. 4 rows in set, 1 warning (0.00 sec)
54. mysql> show profile;#查看最近的一次查询的开销
55. +--------------------------------+----------+
56. | Status                         | Duration |
57. +--------------------------------+----------+
58. | starting                       | 0.000085 |
59. | Executing hook on transaction  | 0.000007 |
60. | starting                       | 0.000007 |
61. | checking permissions           | 0.000006 |
62. | Opening tables                 | 0.000032 |
63. | init                           | 0.000005 |
64. | System lock                    | 0.000006 |
65. | optimizing                     | 0.000035 |
66. | statistics                     | 0.000018 |
67. | preparing                      | 0.000015 |
68. | executing                      | 0.932597 |
69. | end                            | 0.000019 |
70. | query end                      | 0.000005 |
71. | waiting for handler commit     | 0.000008 |
72. | closing tables                 | 0.000009 |
73. | freeing items                  | 0.000033 |
74. | cleaning up                    | 0.000011 |
75. +--------------------------------+----------+
76. 17 rows in set, 1 warning (0.00 sec)
78. mysql> show profile for query 4;#根据Query_ID查看指定查询
79. +--------------------------------+----------+
80. | Status                         | Duration |
81. +--------------------------------+----------+
82. | starting                       | 0.000085 |
83. | Executing hook on transaction  | 0.000007 |
84. | starting                       | 0.000007 |
85. | checking permissions           | 0.000006 |
86. | Opening tables                 | 0.000032 |
87. | init                           | 0.000005 |
88. | System lock                    | 0.000006 |
89. | optimizing                     | 0.000035 |
90. | statistics                     | 0.000018 |
91. | preparing                      | 0.000015 |
92. | executing                      | 0.932597 |
93. | end                            | 0.000019 |
94. | query end                      | 0.000005 |
95. | waiting for handler commit     | 0.000008 |
96. | closing tables                 | 0.000009 |
97. | freeing items                  | 0.000033 |
98. | cleaning up                    | 0.000011 |
99. +--------------------------------+----------+
100. 17 rows in set, 1 warning (0.00 sec)
102. mysql> show profile cpu,block io for query 4;
103. +--------------------------------+----------+----------+------------+--------------+---------------+
104. | Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
105. +--------------------------------+----------+----------+------------+--------------+---------------+
106. | starting                       | 0.000085 | 0.000066 |   0.000013 |            0 |             0 |
107. | Executing hook on transaction  | 0.000007 | 0.000004 |   0.000001 |            0 |             0 |
108. | starting                       | 0.000007 | 0.000006 |   0.000001 |            0 |             0 |
109. | checking permissions           | 0.000006 | 0.000004 |   0.000001 |            0 |             0 |
110. | Opening tables                 | 0.000032 | 0.000027 |   0.000006 |            0 |             0 |
111. | init                           | 0.000005 | 0.000003 |   0.000000 |            0 |             0 |
112. | System lock                    | 0.000006 | 0.000005 |   0.000001 |            0 |             0 |
113. | optimizing                     | 0.000035 | 0.000030 |   0.000007 |            0 |             0 |
114. | statistics                     | 0.000018 | 0.000014 |   0.000002 |            0 |             0 |
115. | preparing                      | 0.000015 | 0.000012 |   0.000003 |            0 |             0 |
116. | executing                      | 0.932597 | 0.947367 |   0.177593 |            0 |             0 |
117. | end                            | 0.000019 | 0.000012 |   0.000000 |            0 |             0 |
118. | query end                      | 0.000005 | 0.000004 |   0.000000 |            0 |             0 |
119. | waiting for handler commit     | 0.000008 | 0.000008 |   0.000000 |            0 |             0 |
120. | closing tables                 | 0.000009 | 0.000009 |   0.000000 |            0 |             0 |
121. | freeing items                  | 0.000033 | 0.000034 |   0.000000 |            0 |             0 |
122. | cleaning up                    | 0.000011 | 0.000009 |   0.000000 |            0 |             0 |
123. +--------------------------------+----------+----------+------------+--------------+---------------+
124. 17 rows in set, 1 warning (0.00 sec)

复制代码

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

1. EXPLAIN SELECT select_options;
2. #或
3. DESCRIBE SELECT select_options;
4. #可以简单的理解为，在select语句前，explain和describe关键字

复制代码

输出的上述信息就是所谓的执行计划。在这个执行计划的辅助下，我们需要知道应该怎样改进自己的查询语句以使查询执行起来更高效。实在除了以SELECT开头的查询语句，其余的DELETE，INSERT，REPLACE以及UPDATE语句等都可以加上EXPLAIN，用来查看这些语句的执行计划，只是平时我们对SELECT语句更感兴趣。
注意，执行EXPLAIN时并没有真正的执行该关键字后面的语句，因此可以安全的查看执行计划。
EXPLAIN语句输出的各个列的作用如下
列名描述id在一个大的查询语句中每个SELECT关键字都对应一个唯一的idselect_typeSELECT关键字对应的那个查询的类型table表名partitions匹配的分区信息type针对单表的访问方法possible_keys可能用到的索引key现实上使用的索引key_len现实使用到的索引长度ref当使用索引等职查询时，与索引列进行等职匹配的对象信息rows预估的需要读取的记载条数filtered某个表经过搜索条件过滤后剩余记载条数的百分比Extra一些额外的信息6.3 数据预备

1.建表

1. mysql> explain select * from student where id = 100001;

复制代码

2.设置参数log_bin_trust_function_creators

创建函数，如果报错，需开启如下命令，允许创建函数设置

1. CREATE DATABASE atguigudb2;
3. USE atguigudb2;
5. CREATE TABLE s1 (
6.     id INT AUTO_INCREMENT,
7.     key1 VARCHAR(100),
8.     key2 INT,
9.     key3 VARCHAR(100),
10.     key_part1 VARCHAR(100),
11.     key_part2 VARCHAR(100),
12.     key_part3 VARCHAR(100),
13.     common_field VARCHAR(100),
14.     PRIMARY KEY (id),
15.     INDEX idx_key1 (key1),
16.     UNIQUE INDEX idx_key2 (key2),
17.     INDEX idx_key3 (key3),
18.     INDEX idx_key_part(key_part1, key_part2, key_part3)
19. ) ENGINE=INNODB CHARSET=utf8;
21. CREATE TABLE s2 (
22.     id INT AUTO_INCREMENT,
23.     key1 VARCHAR(100),
24.     key2 INT,
25.     key3 VARCHAR(100),
26.     key_part1 VARCHAR(100),
27.     key_part2 VARCHAR(100),
28.     key_part3 VARCHAR(100),
29.     common_field VARCHAR(100),
30.     PRIMARY KEY (id),
31.     INDEX idx_key1 (key1),
32.     UNIQUE INDEX idx_key2 (key2),
33.     INDEX idx_key3 (key3),
34.     INDEX idx_key_part(key_part1, key_part2, key_part3)
35. ) ENGINE=INNODB CHARSET=utf8;

复制代码

3.创建函数

1. set global log_bin_trust_function_creators = 1;#不加global只是当前会话有效
2. #出现错误原因是bin_log日志，不信任用户创建的函数

复制代码

4.创建存储过程

1. DELIMITER //
2. CREATE FUNCTION rand_string1(n INT)
3.         RETURNS VARCHAR(255) #该函数会返回一个字符串
4. BEGIN
5.     DECLARE chars_str VARCHAR(100) DEFAULT
6.     'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
7.     DECLARE return_str VARCHAR(255) DEFAULT '';
8.     DECLARE i INT DEFAULT 0;
9.     WHILE i < n DO
10.     SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
11.     SET i = i + 1;
12.     END WHILE;
13.     RETURN return_str;
14. END //
15. DELIMITER ;

复制代码

5.调用存储过程

1. #向s1表插入数据
2. DELIMITER //
3. CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
4. BEGIN
5.     DECLARE i INT DEFAULT 0;
6.     SET autocommit = 0;
7.     REPEAT
8.     SET i = i + 1;
9.     INSERT INTO s1 VALUES(
10.     (min_num + i),
11.     rand_string1(6),
12.     (min_num + 30 * i + 5),
13.     rand_string1(6),
14.     rand_string1(10),
15.     rand_string1(5),
16.     rand_string1(10),
17.     rand_string1(10));
18.     UNTIL i = max_num
19.     END REPEAT;
20.     COMMIT;
21. END //
22. DELIMITER ;
24. #向s2表插入数据
25. DELIMITER //
26. CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
27. BEGIN
28.         DECLARE i INT DEFAULT 0;
29.     SET autocommit = 0;
30.     REPEAT
31.     SET i = i + 1;
32.     INSERT INTO s2 VALUES(
33.     (min_num + i),
34.     rand_string1(6),
35.     (min_num + 30 * i + 5),
36.     rand_string1(6),
37.     rand_string1(10),
38.     rand_string1(5),
39.     rand_string1(10),
40.     rand_string1(10));
41.     UNTIL i = max_num
42.     END REPEAT;
43.     COMMIT;
44. END //
45. DELIMITER ;

复制代码

6.4 EXPLAIN各列作用

宋红康老师在解说的过程中，调解了EXPLAIN输出列的序次。
1.table

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

1. CALL insert_s1(10001,10000);#向s1表插入1万条数据
3. CALL insert_s2(10001,10000);#向s2表插入1万条数据

复制代码

2.id

我们写的查询语句一样平常都以SELECT关键字开头，比较简单的查询语句里只有一个SELECT关键字，好比下边这个查询语句：

1. #1. table：表名
2. #查询的每一行记录都对应着一个单表
3. EXPLAIN SELECT * FROM s1;
4. #该条语句执行结果只输出一条记录，其中的table列的值是s1，表明这条记录是用来说明对s1表的单表访问方法的
6. #s1:驱动表  s2:被驱动表
7. EXPLAIN SELECT * FROM s1 INNER JOIN s2;
8. #结果，是指执行计划的结果
9. #在多表连接中，假如有两行结果，从上到下，依次是驱动表和被驱动表
10. #三行结果，从上到下，s1,s2,s3多表连接，其中s2是s1的被驱动表，也是s3的驱动表，其他类似
11. #执行结果来看，这两条记录的table列分别是`s1`和`s2`，这两条记录用来分别对`s1`表和`s2`表的访问方法是什么。

复制代码

轻微复杂一点的连接查询中也只有一个 SELECT 关键字，好比：

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

复制代码

一条查询sql中包罗多个select关键字的情况

• 查询中包罗子查询的情况
  

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

复制代码

• 查询中包罗UNION语句的情况
  

1. SELECT * FROM s1
2. WHERE key1 IN (SELECT key3 FROM s2);

复制代码

查询语句中每出现一个SELECT关键字，MySQL就会为其分配一个唯一的id值。这个id值就是EXPLAIN语句的第一个列，好比下边这个查询中只有一个SELECT关键字，所以EXPLAIN的结果中也就只有一条id列为1的记载：

1. mysql> EXPLAIN #1. table：表名
2. #查询的每一行记录都对应着一个单表
3. EXPLAIN SELECT * FROM s1;
4. #该条语句执行结果只输出一条记录，其中的table列的值是s1，表明这条记录是用来说明对s1表的单表访问方法的
6. #s1:驱动表  s2:被驱动表
7. EXPLAIN SELECT * FROM s1 INNER JOIN s2;
8. #结果，是指执行计划的结果
9. #在多表连接中，假如有两行结果，从上到下，依次是驱动表和被驱动表
10. #三行结果，从上到下，s1,s2,s3多表连接，其中s2是s1的被驱动表，也是s3的驱动表，其他类似
11. #执行结果来看，这两条记录的table列分别是`s1`和`s2`，这两条记录用来分别对`s1`表和`s2`表的访问方法是什么。

复制代码

对于连接查询来说，一个SELECT关键字后边的FROM子句中可以跟随多个表，所以在连接查询的执行计划中，每个表都会对应一条记载，但是这些记载的id值都是雷同的，好比：

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

复制代码

1. mysql>  EXPLAIN SELECT * FROM s1 INNER JOIN s2;

复制代码

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

复制代码

1. #Union去重mysql> EXPLAIN SELECT * FROM s1
2. WHERE key1 IN (SELECT key3 FROM s2);#注意，在union语句的执行计划中，会多出一行数据，因为会使用到暂时表，因为union关键字会去重，也就是会将合并后的数据去除重复的，#此时就使用到了暂时表，用以存储去重后的表数据。在Extra列，会有Using temporary

复制代码

1. #Union去重
2. mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
3. #注意，在union语句的执行计划中，会多出一行数据，因为会使用到临时表，因为union关键字会去重，也就是会将合并后的数据去除重复的，
4. #此时就使用到了临时表，用以存储去重后的表数据。在Extra列，会有Using temporary

复制代码

小结

• id如果雷同，可以认为是一组，从上往下序次执行
  
• 在全部组中，id值越大，优先级越高，越先执行
  
• 关注点：id号每个号码，表示一趟独立的查询,一个sql的查询趟数越少越好
  

3.select_type

一条大的查询语句里边可以包罗多少个SELECT关键字，每个SELECT关键字代表着一个小的查询语句，而每个SELECT关键字的FROM子句中都可以包罗多少张表(这些表用来做连接查询)，每一张表都对应着执行计划输出中的一条记载，对于在同一个SELECT关键字中的表来说，它们的id值是雷同的。
MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么脚色，我们看一下select_type都能取哪些值

1. mysql> EXPLAIN SELECT * FROM s1  UNION ALL SELECT * FROM s2;
2. #UNION ALL不会多一行数据，是因为不用去重

复制代码

1. #连接查询也算是`SIMPLE`类型mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

复制代码

1. #对于包罗`UNION`或者`UNION ALL`或者子查询的大查询来说，它是由几个小查询构成的，其中最左边的那个 #查询的`select_type`值就是`PRIMARY`   #对于包罗`UNION`或者`UNION ALL`的大查询来说，它是由几个小查询构成的，其中除了最左边的那个小查询 #以外，其余的小查询的`select_type`值就是`UNION`  #`MySQL`选择使用暂时表来完成`UNION`查询的去重工作，针对该暂时表的查询的`select_type`就是 #`UNION RESULT` EXPLAIN SELECT * FROM s1
2. WHERE key1 IN (SELECT key3 FROM s2);  EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;

复制代码

1. #对于包含`UNION`或者`UNION ALL`或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个
2. #查询的`select_type`值就是`PRIMARY`
5. #对于包含`UNION`或者`UNION ALL`的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询
6. #以外，其余的小查询的`select_type`值就是`UNION`
8. #`MySQL`选择使用临时表来完成`UNION`查询的去重工作，针对该临时表的查询的`select_type`就是
9. #`UNION RESULT`
10. EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
12. EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;

复制代码

1. #子查询：
2. #如果包含子查询的查询语句不能够转为对应的`semi-join`(多表连接)的形式，并且该子查询是不相关子查询。
3. #该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`SUBQUERY`
4. EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
7. #如果包含子查询的查询语句不能够转为对应的`semi-join`(多表连接)的形式，并且该子查询是相关子查询，
8. #则该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`DEPENDENT SUBQUERY`
9. EXPLAIN SELECT * FROM s1
10. WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';
11. #注意的是，select_type为`DEPENDENT SUBQUERY`的查询可能会被执行多次。
14. #在包含`UNION`或者`UNION ALL`的大查询中，如果各个小查询都依赖于外层查询的话，那除了
15. #最左边的那个小查询之外，其余的小查询的`select_type`的值就是`DEPENDENT UNION`。
16. EXPLAIN SELECT * FROM s1
17. WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');
18. #注意，从sql语句上看，该sql语句，中的子查询，只会执行一次，也即是不相关子查询，怎么也会是DEPENDENT，也就是相关子查询才会出现得关键字呢？
19. #根据宋红康老师的讲解，查询优化器，会把IN关键字转换成exists，也就是会拿外查询中s1的数据中的key列的值，去和子查询中得出的结果，进行比较判断当前列是否在子查询的执行结果中，如此循环，直至判断全部的s1表的key1列数据，这样也是相关子查询。这估计也是IN关键字会使得索引失效的原因，我个人根据宋红康老师讲解做的解释，不一定正确。

复制代码

1. #对于包含`派生表`的查询，该派生表对应的子查询的`select_type`就是`DERIVED`
2. EXPLAIN SELECT *
3. FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1;

复制代码

4.partitions(可略)

1. #当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，
2. #该子查询对应的`select_type`属性就是`MATERIALIZED`
3. mysql>  EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2)\G #子查询被转为了物化表，数据过长，采用\G进行行列转换
4. *************************** 1. row ***************************
5.            id: 1
6.   select_type: SIMPLE
7.         table: s1
8.    partitions: NULL
9.          type: ALL
10. possible_keys: idx_key1
11.           key: NULL
12.       key_len: NULL
13.           ref: NULL
14.          rows: 9895
15.      filtered: 100.00
16.         Extra: Using where
17. *************************** 2. row ***************************
18.            id: 1
19.   select_type: SIMPLE
20.         table: <subquery2>
21.    partitions: NULL
22.          type: eq_ref
23. possible_keys: <auto_distinct_key>
24.           key: <auto_distinct_key>
25.       key_len: 303
26.           ref: atguigudb2.s1.key1
27.          rows: 1
28.      filtered: 100.00
29.         Extra: NULL
30. *************************** 3. row ***************************
31.            id: 2
32.   select_type: MATERIALIZED
33.         table: s2
34.    partitions: NULL
35.          type: index
36. possible_keys: idx_key1
37.           key: idx_key1
38.       key_len: 303
39.           ref: NULL
40.          rows: 9895
41.      filtered: 100.00
42.         Extra: Using index
43. 3 rows in set, 1 warning (0.00 sec)

复制代码

5.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。
不在展示结果。

1. #当表中`只有一条记载`并且该表使用的存储引擎的统计数据是精确的，好比MyISAM、Memory， #那么对该表的访问方法就是`system`。 CREATE TABLE t(i INT) ENGINE=MYISAM; INSERT INTO t VALUES(1);  EXPLAIN SELECT * FROM t;  #换成InnoDB CREATE TABLE tt(i INT) ENGINE=INNODB; INSERT INTO tt VALUES(1); EXPLAIN SELECT * FROM tt;   #当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是`const` EXPLAIN SELECT * FROM s1 WHERE id = 10005;  EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;   #在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的 #（如果该主键或者唯一二级索引是连合索引的话，全部的索引列都必须进行等值比较），则 #对该被驱动表的访问方法就是`eq_ref` EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;     #当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是`ref` EXPLAIN #1. table：表名
2. #查询的每一行记录都对应着一个单表
3. EXPLAIN SELECT * FROM s1;
4. #该条语句执行结果只输出一条记录，其中的table列的值是s1，表明这条记录是用来说明对s1表的单表访问方法的
6. #s1:驱动表  s2:被驱动表
7. EXPLAIN SELECT * FROM s1 INNER JOIN s2;
8. #结果，是指执行计划的结果
9. #在多表连接中，假如有两行结果，从上到下，依次是驱动表和被驱动表
10. #三行结果，从上到下，s1,s2,s3多表连接，其中s2是s1的被驱动表，也是s3的驱动表，其他类似
11. #执行结果来看，这两条记录的table列分别是`s1`和`s2`，这两条记录用来分别对`s1`表和`s2`表的访问方法是什么。   #当对普通二级索引进行等值匹配查询，该索引列的值也可以是`NULL`值时，那么对该表的访问方法 #就可能是`ref_or_null` EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;   #单表访问方法时在某些场景下可以使用`Intersection`、`Union`、 #`Sort-Union`这三种索引合并的方式来执行查询 这个是index_merge EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';#注意过滤条件的连接符必须是OR   #`unique_subquery`是针对在一些包罗`IN`子查询的查询语句中，如果查询优化器决定将`IN`子查询 #转换为`EXISTS`子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的`type` #列的值就是`unique_subquery` EXPLAIN SELECT * FROM s1  WHERE key2 IN (SELECT id FROM s2 WHERE s1.key1 = s2.key1) OR key3 = 'a';   #如果使用索引获取某些`范围区间`的记载，那么就可能使用到`range`访问方法 EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');  #同上 EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';   #当我们可以使用索引覆盖，但需要扫描全部的索引记载时，该表的访问方法就是`index` EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'; #索引覆盖，就是过滤条件和查询的列都属于连合索引的一部分的话，就会使用到该连合索引的一种现象。也可以讲，不消回表操纵就是索引覆盖，后面会讲。  #最熟悉的全表扫描 EXPLAIN SELECT * FROM s1;

复制代码

小结：
结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index > index_merge > unique_subquery > index_subquery > range > index > ALL
其中比较重要的是，蓝色的部分。SQL性能优化的目标：至少要到达range级别，要求是ref级别，最好是const级别。(阿里巴巴开发手册要求)
6.possible_key和key

possible_key表示可能会使用的索引，key列表示现实使用到的索引。

1. #查询id大于200（200>100，p1分区）的记录，查看执行计划，partitions是p1，符合我们的分区规则
2. DESC SELECT * FROM user_partitions WHERE id>200;

复制代码

7.key_len(重要)

1. #7.  key_len：现实使用到的索引长度(即：字节数)# 帮你查抄`是否充实的使用上了索引`，`值越大越好`,主要针对于连合索引，有一定的参考意义。 EXPLAIN SELECT * FROM s1 WHERE id = 10005; EXPLAIN SELECT * FROM s1 WHERE key2 = 10126; EXPLAIN #1. table：表名
2. #查询的每一行记录都对应着一个单表
3. EXPLAIN SELECT * FROM s1;
4. #该条语句执行结果只输出一条记录，其中的table列的值是s1，表明这条记录是用来说明对s1表的单表访问方法的
6. #s1:驱动表  s2:被驱动表
7. EXPLAIN SELECT * FROM s1 INNER JOIN s2;
8. #结果，是指执行计划的结果
9. #在多表连接中，假如有两行结果，从上到下，依次是驱动表和被驱动表
10. #三行结果，从上到下，s1,s2,s3多表连接，其中s2是s1的被驱动表，也是s3的驱动表，其他类似
11. #执行结果来看，这两条记录的table列分别是`s1`和`s2`，这两条记录用来分别对`s1`表和`s2`表的访问方法是什么。 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';  EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b'; EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';  EXPLAIN SELECT * FROM s1 WHERE key_part3 = 'a';#最左前缀原则 #训练：#单位 字节B 1B = 8bit#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)

复制代码

8.ref

1. # 8. ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息。 #好比只是一个常数或者是某个列。  EXPLAIN #1. table：表名
2. #查询的每一行记录都对应着一个单表
3. EXPLAIN SELECT * FROM s1;
4. #该条语句执行结果只输出一条记录，其中的table列的值是s1，表明这条记录是用来说明对s1表的单表访问方法的
6. #s1:驱动表  s2:被驱动表
7. EXPLAIN SELECT * FROM s1 INNER JOIN s2;
8. #结果，是指执行计划的结果
9. #在多表连接中，假如有两行结果，从上到下，依次是驱动表和被驱动表
10. #三行结果，从上到下，s1,s2,s3多表连接，其中s2是s1的被驱动表，也是s3的驱动表，其他类似
11. #执行结果来看，这两条记录的table列分别是`s1`和`s2`，这两条记录用来分别对`s1`表和`s2`表的访问方法是什么。   EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;   EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

复制代码

9.rows(重要)

1. #7.  key_len：实际使用到的索引长度(即：字节数)
2. # 帮你检查`是否充分的利用上了索引`，`值越大越好`,主要针对于联合索引，有一定的参考意义。
3. EXPLAIN SELECT * FROM s1 WHERE id = 10005;
6. EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;
9. EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
12. EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';
15. EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';
17. EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
19. EXPLAIN SELECT * FROM s1 WHERE key_part3 = 'a';#最左前缀原则
21. #练习：
22. #单位 字节B 1B = 8bit
23. #varchar(10)变长字段且允许NULL  = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
25. #varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
27. #char(10)固定字段且允许NULL    = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
29. #char(10)固定字段且不允许NULL  = 10 * ( character set：utf8=3,gbk=2,latin1=1)

复制代码

10.filtered

1. # 8. ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息。
2. #比如只是一个常数或者是某个列。
4. EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
7. EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
10. EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

复制代码

11.Extra

1. # 9. rows：预估的需要读取的记录条数
2. # `值越小越好`
3. EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';

复制代码

12.小结

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

7.EXPLAIN的进一步使用

7.1 EXPLAIN的四种输特别式

这里谈谈EXPLAIN的输特别式。EXPLAIN可以输出四种格式：传统格式，JSON格式，TREE格式以及可视化输出。
1.传统格式

见6.4小节。此处略
2.JSON格式

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

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

1. # 10. filtered: 某个表经过搜索条件过滤后剩余记录条数的百分比，值越大越好
3. #如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用
4. #到对应索引的搜索条件外的其他搜索条件的记录有多少条。
5. EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';
8. #对于单表查询来说，这个filtered列的值没什么意义，我们`更关注在连接查询
9. #中驱动表对应的执行计划记录的filtered值`，它决定了被驱动表要执行的次数(即：rows * filtered)
10. EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';

复制代码

• EXPLAIN的COLUMN与JSON的对应关系
  
  
  
  这样我们就可以得到一个json格式的执行计划，内里包罗该计划花费的成本，
  

1. #11. Extra:一些额外的信息
2.   #更准确的理解MySQL到底将如何执行给定的查询语句
3. #``中的字符串，是在Extra列显示的
5. #当查询语句的没有`FROM`子句时将会提示该额外信息 `No tables used`
6. EXPLAIN SELECT 1;
9. #查询语句的`WHERE`子句永远为`FALSE`时将会提示该额外信息 `Impossible WHERE`
10. EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
13. #当我们使用全表扫描来执行对某个表的查询，并且该语句的`WHERE`
14. #子句中有针对该表的搜索条件时，在`Extra`列中会提示上述额外信息。 `Using where`
15. EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
18. #当使用索引访问来执行对某个表的查询，并且该语句的`WHERE`子句中 `Using where`
19. #有除了该索引包含的列之外的其他搜索条件时，在`Extra`列中也会提示上述额外信息。
20. EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';
23. #当查询列表处有`MIN`或者`MAX`聚合函数，但是并没有符合`WHERE`子句中
24. #的搜索条件的记录时，将会提示该额外信息
25. #`No matching min/max row`
26. EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';
28. EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'STNmSD'; #STNmSD 是 s1表中key1字段真实存在的数据
29. # `Select tables optimized away`
31. #select * from s1 limit 10;
33. #当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以
34. #使用覆盖索引的情况下，在`Extra`列将会提示该额外信息。比方说下边这个查询中只
35. #需要用到`idx_key1`而不需要回表操作： `Using index`
36. EXPLAIN SELECT key1,id FROM s1 WHERE key1 = 'a';
39. #有些搜索条件中虽然出现了索引列，但却不能使用到索引 `Using index condition`
40. #看课件理解索引条件下推
41. EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
42. #`索引条件下推` 是指如果key1 > 'z' 此时使用到了key1列对应的索引，但是后续又有过滤条件使用该索引列，
43. #此时不急着把查出的id回表，也就是根据后续继续使用该索引过滤的条件，
44. # 进行先过滤一部分id，之后，在把过滤后的主键id，用以回表查询数据  这样的一个过程，称为`索引条件下推`
45. #因为回表操作其实是一个`随机IO`，比较耗时，所以上述修改虽然只改进了一点点，但是可以省去好多回表操作的成本。
47. #在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为
48. #其分配一块名叫`join buffer`的内存块来加快查询速度，也就是我们所讲的`基于块的嵌套循环算法`
49. #见课件说明
50. EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
53. #当我们使用左（外）连接时，如果`WHERE`子句中包含要求被驱动表的某个列等于`NULL`值的搜索条件，
54. #而且那个列又是不允许存储`NULL`值的，那么在该表的执行计划的Extra列就会提示`Not exists`额外信息
55. EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;
58. #如果执行计划的`Extra`列出现了`Using intersect(...)`提示，说明准备使用`Intersect`索引
59. #合并的方式执行查询，括号中的`...`表示需要进行索引合并的索引名称；
60. #如果出现了`Using union(...)`提示，说明准备使用`Union`索引合并的方式执行查询；
61. #出现了`Using sort_union(...)`提示，说明准备使用`Sort-Union`索引合并的方式执行查询。
62. EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
65. #当我们的`LIMIT`子句的参数为`0`时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息
66. EXPLAIN SELECT * FROM s1 LIMIT 0;#`Zero Limit`
69. #有一些情况下对结果集中的记录进行排序是可以使用到索引的。
70. #比如：无提示信息
71. EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
74. #很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）
75. #进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：`filesort`）。
77. #如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的`Extra`列中显示`Using filesort`提示
78. #出现此提示词，要考虑进行优化
79. EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
82. #在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们
83. #在执行许多包含`DISTINCT`、`GROUP BY`、`UNION`等子句的查询过程中，如果不能有效利用索引来完成
84. #查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行
85. #计划的`Extra`列将会显示`Using temporary`提示
86. EXPLAIN SELECT DISTINCT common_field FROM s1;
88. #EXPLAIN SELECT DISTINCT key1 FROM s1;
90. #同上。
91. EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;
93. #执行计划中出现`Using temporary`并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以
94. #我们`最好能使用索引来替代掉使用临时表`。比如：扫描指定的索引idx_key1即可
95. EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;

复制代码

关于cost_info的解释

1. EXPLAIN FORMAT=JSON SELECT...

复制代码

• 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部分是这样的

1. mysql> EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2
2.     -> WHERE s1.common_field = 'a'\G
3. *************************** 1. row ***************************
4. EXPLAIN: {
5.   "query_block": {
6.     "select_id": 1,#整个查询语句只有1个SELECT关键字，该关键字对应的id号为1
7.     "cost_info": {
8.       "query_cost": "1360.07" #整个查询的执行成本预计为3197.16
9.     },
10.     #以下是参与嵌套循环连接算法的各个表的信息
11.     "nested_loop": [
12.       {
13.         "table": {
14.           "table_name": "s1", #s1表是驱动表
15.           "access_type": "ALL", #访问方法为ALL，意味着使用全表扫描访问
16.           "possible_keys": [
17.             "idx_key1"
18.           ],
19.           "rows_examined_per_scan": 9895,#查询一次s1表大致需要扫描9688条记录
20.           "rows_produced_per_join": 989,#驱动表s1的扇出是968
21.           "filtered": "10.00",#condition filtering代表的百分比
22.           "cost_info": {
23.             "read_cost": "914.80",
24.             "eval_cost": "98.95",
25.             "prefix_cost": "1013.75",#单次查询s1表总共的成本
26.             "data_read_per_join": "1M"#读取的数据量
27.           },
28.           "used_columns": [#执行查询中涉及到的列
29.             "id",
30.             "key1",
31.             "key2",
32.             "key3",
33.             "key_part1",
34.             "key_part2",
35.             "key_part3",
36.             "common_field"
37.           ],
38.           #对s1表访问时针对单表查询的条件
39.           "attached_condition": "((`atguigudb2`.`s1`.`common_field` = 'a') and (`atguigudb2`.`s1`.`key1` is not null))"
40.         }
41.       },
42.       {
43.         "table": {
44.           "table_name": "s2", #s2表是被驱动表
45.           "access_type": "eq_ref", #访问方法为ref，意味着使用索引等值匹配的方式访问
46.           "possible_keys": [
47.             "idx_key2"
48.           ],
49.           "key": "idx_key2",
50.           "used_key_parts": [#使用到的索引列
51.             "key2"
52.           ],
53.           "key_length": "5",#key_len
54.           "ref": [#与key2列进行等值匹配的对象
55.             "atguigudb2.s1.key1"
56.           ],
57.           "rows_examined_per_scan": 1,#查询一次s2表大致需要扫描1条记录
58.           "rows_produced_per_join": 989,#被驱动表s2的扇出是968(由于后边没有多余的表进行连接，所以这个值没啥用)
59.           "filtered": "100.00",#filtered列
60.           #s2表使用索引进行查询的搜索条件
61.           "index_condition": "(cast(`atguigudb2`.`s1`.`key1` as double) = cast(`atguigudb2`.`s2`.`key2` as double))",
62.           "cost_info": {
63.             "read_cost": "247.38",
64.             "eval_cost": "98.95",
65.             "prefix_cost": "1360.08", #单次查询s1,多次查询s2表总共的成本
66.             "data_read_per_join": "1M"
67.           },
68.           "used_columns": [#执行查询中涉及到的列
69.             "id",
70.             "key1",
71.             "key2",
72.             "key3",
73.             "key_part1",
74.             "key_part2",
75.             "key_part3",
76.             "common_field"
77.           ]
78.         }
79.       }
80.     ]
81.   }
82. }
83. 1 row in set, 2 warnings (0.00 sec)

复制代码

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

1. "cost_info": {
2.             "read_cost": "914.80",
3.             "eval_cost": "98.95",
4.             "prefix_cost": "1013.75",#单次查询s1表总共的成本
5.             "data_read_per_join": "1M"#读取的数据量
6.           },

复制代码

3.TREE格式

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

1. "cost_info": {
2.     "read_cost": "247.38",
3.     "eval_cost": "98.95",
4.     "prefix_cost": "1360.08", #单次查询s1,多次查询s2表总共的成本
5.     "data_read_per_join": "1M"
6.   },

复制代码

4. 可视化输出

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

1. 1013.75 + 247.38 + 98.95 = 1360.08

复制代码

注意，从执行计划来看，s2作为了驱动表，s1作为了被驱动表，但是SQL语句中LEFT JOIN应该是s2作为主表的。这就表明select查询优化器，选择了它认为更好的执行计划，对原有的sql语句进行了改写，可以通过show warnings语句查看，注意，该语句要作为查看执行计划下一条sql。使用该语句，可以看到改写后执行的sql语句。

1. mysql> EXPLAIN FORMAT=TREE SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2  WHERE s1.common_field = 'a'\G
2. *************************** 1. row ***************************
3. EXPLAIN: -> Nested loop inner join  (cost=1360.08 rows=990)
4.     -> Filter: ((s1.common_field = 'a') and (s1.key1 is not null))  (cost=1013.75 rows=990)
5.         -> Table scan on s1  (cost=1013.75 rows=9895)
6.     -> Single-row index lookup on s2 using idx_key2 (key2=s1.key1), with index condition: (cast(s1.key1 as double) = cast(s2.key2 as double))  (cost=0.25 rows=1)
8. 1 row in set, 1 warning (0.00 sec)

复制代码

8.分析优化器执行计划：trace(可略)

1. mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE
2.     -> s2.common_field IS NOT NULL;

复制代码

开启后，可分析如下语句：
SELECT、INSERT、REPLACE、UPDATE、DELETE、EXPLAIN、SET、DECLARE、CASE、IF、RETURN、CALL
测试：执行如下SQL语句

1. mysql> show warnings\G
2. *************************** 1. row ***************************
3.   Level: Note
4.    Code: 1003
5. Message: /* select#1 */ select `atguigudb2`.`s1`.`key1` AS `key1`,`atguigudb2`.`s2`.`key1` AS `key1` from `atguigudb2`.`s1` join `atguigudb2`.`s2` where ((`atguigudb2`.`s1`.`key1` = `atguigudb2`.`s2`.`key1`) and (`atguigudb2`.`s2`.`common_field` is not null))
6. 1 row in set (0.00 sec)

复制代码

最后， 查询 information_schema.optimizer_trace 就可以知道MySQL是怎样执行SQL的 ：

1. SET optimizer_trace="enabled=on",end_markers_in_json=on;
3. set optimizer_trace_max_mem_size=1000000;

复制代码

9.MySQL监控分析视图-sys schema

9.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开头，展示等候事故的延迟情况。
9.2 Sys schema视图使用场景

索引情况

1. use atguigudb1;
3. select * from student where id < 10;

复制代码

表相干

1. *************************** 1. row ***************************
2.     //第1部分：查询语句
3.     QUERY: select * from student where id < 10
4.     //第2部分：QUERY字段对应语句的跟踪信息
5. TRACE: {
6.   "steps": [
7.     {
8.       "join_preparation": {       
9.         "select#": 1,
10.         "steps": [
11.           {
12.             "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)"
13.           }
14.         ] /* steps */
15.       } /* join_preparation */
16.     },
17.     {
18.       "join_optimization": {
19.         "select#": 1,
20.         "steps": [
21.           {
22.             "condition_processing": {
23.               "condition": "WHERE",
24.               "original_condition": "(`student`.`id` < 10)",
25.               "steps": [
26.                 {
27.                   "transformation": "equality_propagation",
28.                   "resulting_condition": "(`student`.`id` < 10)"
29.                 },
30.                 {
31.                   "transformation": "constant_propagation",
32.                   "resulting_condition": "(`student`.`id` < 10)"
33.                 },
34.                 {
35.                   "transformation": "trivial_condition_removal",
36.                   "resulting_condition": "(`student`.`id` < 10)"
37.                 }
38.               ] /* steps */
39.             } /* condition_processing */
40.           },
41.           {
42.             "substitute_generated_columns": {
43.             } /* substitute_generated_columns */
44.           },
45.           {
46.             "table_dependencies": [
47.               {
48.                 "table": "`student`",
49.                 "row_may_be_null": false,
50.                 "map_bit": 0,
51.                 "depends_on_map_bits": [
52.                 ] /* depends_on_map_bits */
53.               }
54.             ] /* table_dependencies */
55.           },
56.           {
57.             "ref_optimizer_key_uses": [
58.             ] /* ref_optimizer_key_uses */
59.           },
60.           {
61.             "rows_estimation": [
62.               {
63.                 "table": "`student`",
64.                 "range_analysis": {
65.                   "table_scan": {
66.                     "rows": 3806334,
67.                     "cost": 390596
68.                   } /* table_scan */,
69.                   "potential_range_indexes": [
70.                     {
71.                       "index": "PRIMARY",
72.                       "usable": true,
73.                       "key_parts": [
74.                         "id"
75.                       ] /* key_parts */
76.                     }
77.                   ] /* potential_range_indexes */,
78.                   "setup_range_conditions": [
79.                   ] /* setup_range_conditions */,
80.                   "group_index_range": {
81.                     "chosen": false,
82.                     "cause": "not_group_by_or_distinct"
83.                   } /* group_index_range */,
84.                   "skip_scan_range": {
85.                     "potential_skip_scan_indexes": [
86.                       {
87.                         "index": "PRIMARY",
88.                         "usable": false,
89.                         "cause": "query_references_nonkey_column"
90.                       }
91.                     ] /* potential_skip_scan_indexes */
92.                   } /* skip_scan_range */,
93.                   "analyzing_range_alternatives": {
94.                     "range_scan_alternatives": [
95.                       {
96.                         "index": "PRIMARY",
97.                         "ranges": [
98.                           "id < 10"
99.                         ] /* ranges */,
100.                         "index_dives_for_eq_ranges": true,
101.                         "rowid_ordered": true,
102.                         "using_mrr": false,
103.                         "index_only": false,
104.                         "rows": 9,
105.                         "cost": 1.84735,
106.                         "chosen": true
107.                       }
108.                     ] /* range_scan_alternatives */,
109.                     "analyzing_roworder_intersect": {
110.                       "usable": false,
111.                       "cause": "too_few_roworder_scans"
112.                     } /* analyzing_roworder_intersect */
113.                   } /* analyzing_range_alternatives */,
114.                   "chosen_range_access_summary": {
115.                     "range_access_plan": {
116.                       "type": "range_scan",
117.                       "index": "PRIMARY",
118.                       "rows": 9,
119.                       "ranges": [
120.                         "id < 10"
121.                       ] /* ranges */
122.                     } /* range_access_plan */,
123.                     "rows_for_plan": 9,
124.                     "cost_for_plan": 1.84735,
125.                     "chosen": true
126.                   } /* chosen_range_access_summary */
127.                 } /* range_analysis */
128.               }
129.             ] /* rows_estimation */
130.           },
131.           {
132.             "considered_execution_plans": [
133.               {
134.                 "plan_prefix": [
135.                 ] /* plan_prefix */,
136.                 "table": "`student`",
137.                 "best_access_path": {
138.                   "considered_access_paths": [
139.                     {
140.                       "rows_to_scan": 9,
141.                       "access_type": "range",
142.                       "range_details": {
143.                         "used_index": "PRIMARY"
144.                       } /* range_details */,
145.                       "resulting_rows": 9,
146.                       "cost": 2.74735,
147.                       "chosen": true
148.                     }
149.                   ] /* considered_access_paths */
150.                 } /* best_access_path */,
151.                 "condition_filtering_pct": 100,
152.                 "rows_for_plan": 9,
153.                 "cost_for_plan": 2.74735,
154.                 "chosen": true
155.               }
156.             ] /* considered_execution_plans */
157.           },
158.           {
159.             "attaching_conditions_to_tables": {
160.               "original_condition": "(`student`.`id` < 10)",
161.               "attached_conditions_computation": [
162.               ] /* attached_conditions_computation */,
163.               "attached_conditions_summary": [
164.                 {
165.                   "table": "`student`",
166.                   "attached": "(`student`.`id` < 10)"
167.                 }
168.               ] /* attached_conditions_summary */
169.             } /* attaching_conditions_to_tables */
170.           },
171.           {
172.             "finalizing_table_conditions": [
173.               {
174.                 "table": "`student`",
175.                 "original_table_condition": "(`student`.`id` < 10)",
176.                 "final_table_condition   ": "(`student`.`id` < 10)"
177.               }
178.             ] /* finalizing_table_conditions */
179.           },
180.           {
181.             "refine_plan": [
182.               {
183.                 "table": "`student`"
184.               }
185.             ] /* refine_plan */
186.           }
187.         ] /* steps */
188.       } /* join_optimization */
189.     },
190.     {
191.       "join_execution": {
192.         "select#": 1,
193.         "steps": [
194.         ] /* steps */
195.       } /* join_execution */
196.     }
197.   ] /* steps */
198. }
199. //第3部分：跟踪信息过长时，被截断的跟踪信息的字节数。
200. MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 //丢失的超出最大容量的字节
201. //第4部分：执行跟踪语句的用户是否有查看对象的权限。当不具有权限时，该列信息为1且TRACE字段为空，一般在
202. 调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下，会出现此问题。
203. INSUFFICIENT_PRIVILEGES: 0 //缺失权限
204. 1 row in set (0.00 sec)

复制代码

语句相干

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

复制代码

IO相干

1. # 1. 查询表的访问量
2. select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from
3. sys.schema_table_statistics group by table_schema,table_name order by io desc;
5. # 2. 查询占用bufferpool较多的表
6. select object_schema,object_name,allocated,data
7. from sys.innodb_buffer_stats_by_table order by allocated limit 10;
9. # 3. 查看表的全表扫描情况
10. select * from sys.statements_with_full_table_scans where db='dbname';

复制代码

Innodb 相干

1. #1. 监控SQL执行的频率
2. select db,exec_count,query from sys.statement_analysis
3. order by exec_count desc;
5. #2. 监控使用了排序的SQL
6. select db,exec_count,first_seen,last_seen,query
7. from sys.statements_with_sorting limit 1;
9. #3. 监控使用了临时表或者磁盘临时表的SQL
10. select db,exec_count,tmp_tables,tmp_disk_tables,query
11. from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
12. order by (tmp_tables+tmp_disk_tables) desc;

复制代码

风险提示
通过sys库去查询时，MySQL会斲丧大量资源去网络相干信息，严重的可能会导致业务哀求被阻塞，从而引起故障。发起生产上不要频繁的去查询sys或者performance_schema、information_schema来完成监控、巡检等工作。

只是为了记载自己的学习历程，且本人水平有限，不对之处，请指正。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。