第09章_性能分析工具的使用

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

当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。
整个流程划分成了观察（Showstatus）和行动（Action）两个部分。字母S的部分代表观察（会使用相应的分析工具），字母A代表的部分是行动（对应分析可以采取的行动）。

show profiling 我们可以去查找每一个步骤的时间成本

我们可以通过观察了解数据库整体的运行状态，通过性能分析工具可以让我们了解执行慢的SQL都有哪些，查看具体的SQL执行计划，甚至是SQL执行中的每一步的成本代价，这样才能定位问题所在，找到了问题，再采取相应的行动。

详细解释一下这张图：
首先在S1部分，我们需要观察服务器的状态是否存在周期性的波动。如果存在周期性波动，有可能是周期性节点的原因，比如双十一、促销活动等。这样的话，我们可以通过A1这一步骤解决，也就是加缓存，或者更改缓存失效策略。

如果缓存策略没有解决，或者不是周期性波动的原因，我们就需要进一步分析查询延迟和卡顿的原因。接下来进入S2这一步，我们需要开启慢查询。慢查询可以帮我们定位执行慢的SQL语句。我们可以通过设置long-query_time参数定义“慢"的阈值，如果sQL执行时间超过了long_query_time，则会认为是慢查询。当收集上来这些慢查询之后，我们就可以通过分析工具对慢查询日志进行分析。

在S3这一步骤中，我们就知道了执行慢的SQL，这样就可以针对性地用EXPLAIN查看对应SQL语句的执行计划，或者使用showprofile查看SQL中每一个步骤的时间成本。这样我们就可以了解SQL查询慢是因为执行时间长，还是等待时间长。

如果是SQL等待时间长，我们进入A2步骤。在这一步骤中，我们可以调优服务器的参数，比如适当增加数据库缓冲池等。如果是SQL执行时间长，就进入A3步骤，这一步中我们需要考虑是索引设计的问题？还是查询关联的数据表过多？然后在这上进行对应的调整

如果A2和A3都不能解决问题，我们需要考虑数据库自身的SQL查询性能是否已经达到了瓶颈，如果确认没有达到性能瓶颈，就需要重新检查，重复以上的步骤。如果已经达到了性能瓶颈，进入A4阶段，需要考虑增加服务器，采用读写分离的架构，或者考虑对数据库进行分库分表，比如垂直分库、垂直分表和水平分表等。

以上就是数据库调优的流程思路。如果我们发现执行SQL时存在不规则延迟或卡顿的时候，就可以采用分析工具帮我们定位有问题的SQL，这三种分析工具你可以理解是SQL调优的三个步骤：慢查询、EXPLAIN和SHOW PROFILING。

2.查看系统性能参数

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

SHOWSTATUS语句语法如下：
SHOW [GLOBALISESSION] STATUS LIKE '参数';

InnoDB引擎的统计指标

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

查询MySQL服务器状态

连接次数: 使用SHOW STATUS LIKE 'Connections';查询。
服务器工作时间: 使用SHOW STATUS LIKE 'Uptime';查询。
慢查询次数: 使用SHOW STATUS LIKE 'Slow_queries';查询。

慢查询次数参数可以结合慢查询日志找出慢查询语句，然后针对慢查询语句进行表结构优化或者查询语句优化。

查看相关指令情况

使用SHOW STATUS LIKE 'Innodb_rows_%';可以查看与InnoDB引擎相关的行操作统计。

3. 统计SQL的查询成本：last_query_cost

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

我们依然使用第8章的student_info表为例：

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

使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。
SQL查询是一个动态的过程，从页加载的角度来看，我们可以得到以下两点结论：
1.位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。
2.批量决定效率。如果我们从磁盘中对单一页进行随机读，那么效率是很低的（差不多10ms），而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。

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

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

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

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

set slow_query_log = on;


mysql> show variables like %slow_query_log%;
+-----------------------+----------------------------------------+
|     Variable_name     |         Value                          |
+-----------------------+----------------------------------------+
|     slow_query_log    |          ON                            |
|  slow_query_log_file  |    /var/lib/mysql/atguigu05-slow.log   |
+----------------------------------------------------------------+

接下来我们来看下慢查询的时间阔值设置，使用如下命令：
mysql > show variables like '%long-query_time%';
基本上都是10s左右，大概。

4.2 查看慢查询数目

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

SHOW GLOBAL STATUS LIKE '%Slow_queries%;

4.3 案例演示

步骤1：建表

CREATE TABLE `student` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `stuno` INT NOT NULL,
  `name` VARCHAR(20) DEFAULT NULL,
  `age` INT(3) DEFAULT NULL,
  `classid` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

步骤2：设置参数 `log_bin_trust_function_creators`

在创建函数时，如果遇到错误提示：

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

可以通过设置参数 log_bin_trust_function_creators 来解决。

SET GLOBAL log_bin_trust_function_creators = 1;

注意：如果不加 GLOBAL，设置只在当前窗口有效。

步骤3：创建函数

创建一个函数来随机生成字符串。（具体实现可以参考上一章）

DELIMITER //
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255)  -- 该函数会返回一个字符串
BEGIN
  DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  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 ;

#测试
SELECT rand_string(10);

步骤4：创建存储过程

DELIMITER //
CREATE PROCEDURE insert_stu1(START INT, max_num INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  SET autocommit = 0;  -- 设置手动提交事务
  REPEAT  -- 循环
    SET i = i + 1;  -- 赋值
    INSERT INTO student (stuno, name, age, classId) VALUES
    ((START + i), rand_string(6), rand_num(10, 100), rand_num(18, 1000));
  UNTIL i = max_num
  END REPEAT;
  COMMIT;  -- 提交事务
END //
DELIMITER ;

步骤5：调用存储过程

调用刚刚创建的存储过程 insert_stu1，从 100001 号开始插入 4000000 条记录。（需要3-7分钟）

CALL insert_stu1(100001, 4000000);

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

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

注意这个不是在mysql的命令段下执行，而是在root下执行这个脚本文件。

systemctl restart mysqld;
mysqldumpslow -help

4.7删除慢查询日志

使用SHOW语句显示慢查询日志信息，具体SQL语句如下。
SHOW VARIABLES LIKE 'slow_query_log%';

从执行结果可以看出，慢查询日志的目录默认为MySQL的数据目录，在该目录下手动删除慢查询日志文件即可。

使用命令mysqladminflush-logs来重新生成查询日志文件，具体命令如下，执行完毕会在数据目录下重新生成慢查询日志文件。
mysqladmin -uroot -p flush-logs slow

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

showprofile在《逻辑架构》章节中讲过，这里作为复习。
ShowProfile是MySQL提供的可以用来分析当前会话中SQL都做了什么、执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。

我们可以在会话级别开启这个功能
mysql > show variables like 'profiling';

通过设置profiling='ON'来开启show profile：
mysql > set profiling = ‘ON';

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

我们就可以判断出采SQL到底慢在哪里。

showprofile的常用查询参数：

ALL：显示所有的开销信息。
BLOCKIO：显示块IO开销。
CONTEXTSWITCHES:上下文切换开销。
CPU：显示CPU开销信息。
IPC：显示发送和接收开销信息。
MEMORY：显示内存开销信息。
PAGEFAULTS：显示页面错误开销信息。
SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
SWAPS：显示交换次数开销信息。

日常开发需注意的结论：
①converting HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了。
②Creatingtmptable：创建临时表。先拷贝数据到临时表，用完后再删除临时表。
③Copyingtotmptableondisk：把内存中l临时表复制到磁盘上，警惕！
④locked。
如果在showprofile诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化

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

6.分析查询语句：EXPLAIN

定位了查询慢的SQL之后，我们就可以使用EXPLAIN或DESCRIBE工具做针对性的分析查询语句。DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。
MySQL中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间）。

1.能做什么？

·表的读取顺序
·数据读取操作的操作类型
·哪些索引可以使用
·哪些索引被实际使用
·表之间的引用
·<fontcolor='red'>每张表有多少行被优化器查询</font>

2.官网介绍

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

6.2基本语法

EXPLAIN或DESCRIBE语句的语法形式如下：

EXPLAIN SELECT select_options
或者
DESCRIBE SELECT select_options

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

6.3 数据准备

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

CREATE TABLE s1 (
  id INT AUTO_INCREMENT,
  key1 VARCHAR(100),
  key2 INT,
  key3 VARCHAR(100),
  key_part1 VARCHAR(100),
  key_part2 VARCHAR(100),
  key_part3 VARCHAR(100),
  common_field VARCHAR(100),
  PRIMARY KEY (id),
  INDEX idx_key1 (key1),
  UNIQUE INDEX idx_key2 (key2),
  INDEX idx_key3 (key3),
  INDEX idx_key_part (key_part1, key_part2, key_part3)  #key123构建联合索引
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE s2 (
    id INT AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_key1 (key1),
    UNIQUE INDEX idx_key2 (key2),
    INDEX idx_key3 (key3),
    INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

2.设置参数log_bin_trust_function_creators

创建函数，假如报错，需开启如下命令：允许创建函数设置：
set global log_bin_trust_function_creators=1;    #不加global只是当前窗口有效。

#创建存储函数：
DELIMITER //
CREATE FUNCTION rand_string1(n INT) 
	RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN 
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE i < n DO
		SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
		SET i = i + 1;
	END WHILE;
	RETURN return_str;
END //
DELIMITER ;

SET GLOBAL log_bin_trust_function_creators=1;

#创建存储过程：
DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT
	SET i = i + 1;
	INSERT INTO s1 VALUES(
    (min_num + i),
    rand_string1(6),
    (min_num + 30 * i + 5),
    rand_string1(6),
    rand_string1(10),
    rand_string1(5),
    rand_string1(10),
    rand_string1(10));
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END //
DELIMITER ;


DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT
	SET i = i + 1;
	INSERT INTO s2 VALUES(
        (min_num + i),
		rand_string1(6),
		(min_num + 30 * i + 5),
		rand_string1(6),
		rand_string1(10),
		rand_string1(5),
		rand_string1(10),
		rand_string1(10));
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END //
DELIMITER ;

#调用存储过程
CALL insert_s1(10001,10000);

CALL insert_s2(10001,10000);

SELECT COUNT(*) FROM s1;

SELECT COUNT(*) FROM s2;

“ 资料 ”文件夹中有一个EXPLAIN使用.sql文件 P136的22.00 大概tts相关的内容

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

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

#2. id：在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
 SELECT * FROM s1 WHERE key1 = 'a';


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


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


 SELECT * FROM s1 UNION SELECT * FROM s2;


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

######查询优化器可能对涉及子查询的查询语句进行重写,转变为多表查询的操作########
 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');
 
 #Union去重 实际上是取并集的操作，因此会多一个
 EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

id如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
关注点：id号每个号码，表示一趟独立的查询，一个sql的查询趟数越少越好，多了嵌套就成了指数增长。

3. select_type

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

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

 #3. select_type：SELECT关键字对应的那个查询的类型,确定小查询在整个大查询中扮演了一个什么角色
 
 # 查询语句中不包含`UNION`或者子查询的查询都算作是`SIMPLE`类型
 EXPLAIN SELECT * FROM s1;
 
 
 #连接查询也算是`SIMPLE`类型
 EXPLAIN SELECT * FROM s1 INNER JOIN s2;
 
 
 #对于包含`UNION`或者`UNION ALL`或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个
 #查询的`select_type`值就是`PRIMARY`
 
 
 #对于包含`UNION`或者`UNION ALL`的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询
 #以外，其余的小查询的`select_type`值就是`UNION`
 
 #`MySQL`选择使用临时表来完成`UNION`查询的去重工作，针对该临时表的查询的`select_type`就是
 #`UNION RESULT`
 EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
 
 EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;

左边的列表就是primary，右边union。

然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。
每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。
如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，
子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然

#子查询：
 #如果包含子查询的查询语句不能够转为对应的`semi-join`的形式，并且该子查询是不相关子查询。
 #该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`SUBQUERY`
 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
 
 
 #如果包含子查询的查询语句不能够转为对应的`semi-join`的形式，并且该子查询是相关子查询，
 #则该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`DEPENDENT SUBQUERY`
 EXPLAIN SELECT * FROM s1 
 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';
 #注意的是，select_type为`DEPENDENT SUBQUERY`的查询可能会被执行多次。
 
 
 #在包含`UNION`或者`UNION ALL`的大查询中，如果各个小查询都依赖于外层查询的话，那除了
 #最左边的那个小查询之外，其余的小查询的`select_type`的值就是`DEPENDENT UNION`。
 EXPLAIN SELECT * FROM s1 
 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');

 #对于包含`派生表`的查询，该派生表对应的子查询的`select_type`就是`DERIVED`
 EXPLAIN SELECT * 
 FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1;
 
 
 #当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，
 #该子查询对应的`select_type`属性就是`MATERIALIZED`
 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2); #子查询被转为了物化表

讲解讲的太长了，

7 EXPLAIN

7.1 EXPLAIN四种输出格式

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

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

mysql> 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 | s2 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9954 | 90.00 | Using where |
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | xiaohaizi.s2.key1 | 1 | 100.00 | Using index |
+----+-------------+-------+--------+------------+-------+-------+------------+---------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

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

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

EXPLAIN FORMAT=JSON SELECT ...

- EXPLAIN的Column与JSON的对应关系：（来源于MySQL 5.7文档）

#json格式的explain
EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 
WHERE s1.common_field = 'a';

我们使用#后边跟随注释的形式为大家解释了EXPLAIN FORMAT=JSON语句的输出内容，但是大家可能有疑问“cost_info”里边的成本看着怪怪的，它们是怎么计算出来的？先看s1表的“cost_info”部分：

"cost_info": {
    "read_cost": "1840.84",
    "eval_cost": "193.76",
    "prefix_cost": "2034.60",
    "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": "968.80",
    "eval_cost": "193.76",
    "prefix_cost": "3197.16",
    "data_read_per_join": "1M"
}

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

968.80 + 193.76 + 2034.60 = 3197.16

3.TREE格式

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

4.可视化输出

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

7.2 SHOW WARNINGS的使用

在我们使用EXPLAIN语句查看了某个查询的执行计划后，紧接着还可以使用SHOWWARNINGS语句查看与这个查询的执行计划有关的一些扩展信息，比如这样：

message相当于原组换式重写以后的结构

139-EXPLAIN的4种格式与查看优化器重写SQL_哔哩哔哩_bilibili开始tts

8.分析优化器执行计划：trace

OPTIMIZER_TRACE是MySQL5.6引I入的一项跟踪功能，它可以跟踪优化器做出的各种决策（比如访问表的方法、各种开销计算、各种转换等），并将跟踪结果记录INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。此功能默认关闭。开启trace，并设置格式为JSON，同时设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

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

测试：执行如下SQL语句
select * from student whqre id < 10;
最后，查询information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的：
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": 3973767,
                    "cost": 408558
                  } /* table_scan */,     //扫描表
                  "potential_range_indexes": [     //潜在的范围索引
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [     //设置范围条件
                  ] /* 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,
                        "rows": 9,
                        "cost": 1.91986,
                        "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.91986,
                    "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.81986,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,   //行过滤百分比
                "rows_for_plan": 9,
                "cost_for_plan": 2.81986,
                "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   //缺失权限
1 row in set (0.00 sec)

03.28-----------tts

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

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

#1.查询究余索引
SELECT * FROM sys.schema_redundant_indexes;
#2.查询未使用过的索引
SELECT * FROM sys.schema_unused_indexes;
#3.查询索引的使用情况
SELECT index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
FROM sys.schema_index_statistics WHERE table_schema='dbname'

这几条都是对于整个数据库系统来说的，即sys中，因此放到任意数据库中均可，140-trace分析优化器执行计划与Sys schema视图的使用_哔哩哔哩_bilibili

#1.查询表的访问量
SELEcT table_schema,table_name,SUM(io_read_requests+io_write_requests) AS io
FROM sys.schema_table_statistics GRoUP BY table_schema,table_name ORDER BY io DESC;

#2.查询占用bufferpool较多的表
SELECT object_schema,object_name,allocated,DATA
FROM sys.innodb_buffer_stats_by_table ORDER BY allocated LIMIT 10;
#3.查看表的全表扫描情况
SELECT * FROM sys.statements with full table scans WHERE db='dbname';


#1.监控SQL执行的频率
SELECT db,exec_count,QUERY FROM sys.statement_analysis
ORDER BY eXeC_count DESC;
#2.监控使用了排序的SQL
SELECT db,exec_count,first_seen,last_seen,QUERY
FROM sys.statements_with_sorting LIMIT 1;
#3.监控使用了临时表或者磁盘临时表的SQL
SELECT db,exec_count,tmp_tables,tmp_disk_tables,QUERY
FROM sys.statement_analysis WHERE tmp_tables>O OR tmp_disk_tables >0
ORDER BY (tmp_tables+tmp_disk_tables)DESC;

IO相关

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

Innodb 相关

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

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