性能分析工具的使用
数据库服务器的优化步骤
1、整个流程划分：观察（Show status）、行动（Action）
（1）字母 S 代表观察，会使用相应的分析工具
（2）字母 A 代表行动，对应分析可以采取的行动
2、S1 步骤，需要观察服务器的状态是否存在周期性的波动
（1）如果存在周期性波动，有可能是周期性节点的原因，可以通过 A1 加缓存解决，或更改缓存失效策略
（2）如果缓存策略没有解决，或不是周期性波动的原因，需要进一步分析查询延迟和卡顿的原因
3、S2 步骤，开启慢查询
（1）通过慢查询可用快速定位执行慢的 SQL 语句
（2）通过设置 long_query_time 参数定义慢的阈值，如果 SQL 执行时间超过阈值，则认为是慢查询
4、收集慢查询的 SQL 语句之后，可以通过分析工具对慢查询日志进行分析
（1）使用 EXPLAIN 查看对应 SQL 语句的执行计划
（2）使用 SHOW PROFILE 查看 SQL 中每一个步骤的时间成本
5、S3 步骤：了解 SQL 查询慢的原因是因为执行时间长，还是等待时间长
（1）如果 SQL 等待时间长，进入 A2 步骤，进行调优服务器的参数，比如适当增加数据库缓冲池等
（2）如果 SQL 执行时间长，进入 A3 步骤
6、A3 步骤，三方面优化
（1）索引设计优化，如联合索引比多个单个索引的查询效率要快
（2）JOIN 表是否过多，JOIN 表的数据最好不要超过三张，表的数据越多，嵌套循环就越多，查询时间也就越长
（3）数据表设计优化，一般设计数据表都遵循三范式，但可以适当增加数据冗余度，以空间换取时间，提高数据的查询效率
7、A2、A3 都不能解决问题，考虑数据库自身 SQL 查询性能是否已经达到瓶颈
（1）如果确认没有达到性能瓶颈，需要重复上述步骤
（2）如果已经达到性能瓶颈，进入 A4 阶段
8、A4 步骤
（1）增加服务器
（2）采用读写分离的架构
（3）对数据库进行分库分表，比如：垂直分库，垂直分表、水平分表等
查看系统性能参数
1、在 MySQL 中，可以使用 SHOW STATUS 语句查询 MySQL 数据库服务器的性能参数、执行频率
SHOW [GLOBAL | SESSION] STATUS LIKE '参数';2、常用性能参数
（1）Connections：连接 MySQL 服务器的次数
（2）Uptime：MySQL 服务器的上线时间
（3）Slow_queries：慢查询的次数
（4）Innodb_rows_read：执行 Select 查询返回的行数
（5）Innodb_rows_inserted：执行 INSERT 操作插入的行数
（6）Innodb_rows_updated：执行 UPDATE 操作更新的行数
（7）Innodb_rows_deleted：执行 DELETE 操作删除的行数
（8）Com_select：查询操作的次数
（9）Com_insert：插入操作的次数，对于批量插入的 INSERT 操作，只累加一次
（10）Com_update：更新操作的次数
（11）Com_delete：删除操作的次数
统计 SQL 查询成本：last_query_cost
1、一条 SQL 查询语句在执行前，需要确定查询执行计划，如果存在多种执行计划，MySQL 计算每个执行计划所需要的成本，从中选择成本最小的一个作为最终执行计划
2、查看某条 SQL 语句的查询成本：在执行完 SQL 语句后，查看当前会话中 last_query_cost 变量值来得到当前查询的成本
（1）通常是评价一个查询的执行效率的一个常用指标
（2）查询成本对应 SQL 语句所需要读取的页的数量
3、使用场景：比较开销，在有好几种查询方式可选时
4、SQL 查询是一个动态的过程，从页加载的角度来看，可以得到以下两点结论
（1）位置决定效率：如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多
（2）批量决定效率：如果从磁盘中对单一页进行随机读取，那么效率很低（大约 10ms），而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取
定位执行慢的 SQL：慢查询日志
1、MySQL 慢查询日志：记录在 MySQL 中响应时间超过阈值的语句，运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中
（1）默认情况下，MySQL 数据库不开启慢查询日志
（2）不需要调优情况下，一般不建议启动该参数，开启影响一定性能
2、慢查询日志支持将日志记录写入文件
3、slow_query_log
（1）查看是否开启慢查询日志，以及慢查询日志文件位置
SHOW VARIABLES LIKE '%slow_query_log%';（2）临时开启慢查询日志
SET [GLOBAL | SESSION] slow_query_log='ON';（3）修改 my .cnf 文件，永久生效
[mysqld]
#开启慢查询日志的开关
slow_query_log=ON
#慢查询日志的目录和文件名信息
#如果不指定存储路径，慢查询日志将默认存储到MySQL数据库的数据文件夹下
#如果不指定文件名，默认文件名为hostname-slow.log.
slow.query_log_file=/var/lib/mysq1/文件名.log
#表示将日志存入文件
log_output=FILE（4）永久性关闭慢查询日志：slow_query_log 修改为 OFF，或直接删除，或注释
[mysqld]
slow_query_log=OFF（5）临时性关闭慢查询日志：SET
SET [GLOBAL | SESSION] slow_query_log='OFF';4、long_query_time
（1）查看慢查询的时间阈值
（2）默认值为 10
SHOW VARIABLES LIKE '%long_query_time%';（3）临时修改慢查询时间阈值
#设置 GLOBAL 方式对当前 SESSION 的 long_query_time 失效，对新连接的客户端有效
SET GLOBAL long_query_time=秒数值;#修改当前会话
SET long_query_time=秒数值;（4）修改 my .cnf 文件，永久生效
[mysqld]
#设置慢查询的阈值为3秒，超出此设定值的SQL即被记录到慢查询日志
long.query_time=35、查询当前系统中有多少条慢查询记录
SHOW GLOBAL STATUS LIKE '%Slow_queries%';6、min_examined_row_limit
（1）查询扫描过的最少记录数
（2）该变量和查询执行时间，共同组成判别一个查询是否为慢查询的条件
（3）如果查询扫描过的记录数大于等于该变量的值，并且查询执行时间超过 long_query_time 的值，则查询就被记录到慢查询日志中；反之，则不被记录
（4）默认值为 0
7、删除慢查询日志
（1）在指定目录下，手动删除慢查询日志文件即可
（2）重新生成查询日志文件，执行命令后，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，必须事先备份
mysqladmin -root -p flush-logs slow
慢查询日志分析工具：mysqldumpslow
1、查看 mysqldumpslow 帮助信息
mysqldumpslow --help2、命令的具体参数
（1）-a：不将数字抽象成 N，字符串抽象成 S
（2）-s：表示按照何种方式排序：c：访问次数；l：锁定时间；r：返回记录；t：查询时间；al：平均锁定时间；ar：平均返回记录数；at：平均查询时间 （默认方式）；ac：平均查询次数
（3）-t：返回指定条数的数据
（4）-g：后边搭配一个正则匹配模式，大小写不敏感
SHOW PROFILE
1、查看 SQL 执行成本
2、查看变量
SHOW VARIABLES LIKE 'profiling';3、开启 SHOW PROFILE
SET profiling = 'ON';4、常用查询参数
（1）ALL：显示所有的开销信息
（2）BLOCK IO：显示块 IO 开销
（3）CONTEXT SWITCHES：上下文切换开销
（4）CPU：显示 CPU 开销信息
（5）IPC：显示发送和接收开销信息
（6）MEMORY：显示内存开销信息
（7）PAGE FAULTS：显示页面错误开销信息
（8）SOURCE：显示和 Source_function、Source_file、Source_line 相关的开销信息
（9）SWAPS：显示交换次数开销信息
5、出现以下 4 条结果中的任何一条，则 SQL 语句需要优化
（1）Converting HEAP to MyISAM：查询结果太大，内存不够，多出数据在磁盘上
（2）Creating tmp table：创建临时表，先拷贝数据到临时表，用完后再删除临时表
（3）Copying to tmp table on disk：把内存中临时表复制到磁盘上
（4）locked。
6、SHOW PROFILE 命令将被弃用，可以从 information_schema 中的 profiling 数据表进行查看
EXPLAIN
1、分析查询语句
2、定位查询慢的 SQL 后，可以使用 EXPLAIN 或 DESCRIBE 工具，做针对性的分析查询语句
3、DESCRIBE 使用方法与 EXPLAIN 相同，并且分析结果相同
4、MySQL 提供 EXPLAIN，查看某个查询语句的具体执行计划
5、版本情况
（1）MySQL 5.6.3 以前只能 EXPLAIN SELECT
（2）MySQL 5.6.3 以后可以 EXPLAIN SELECT，UPDATE，DELETE
（3）MySQL 5.7 以前，显示 partitions 需要使用 EXPLAIN partitions，显示 filtered 需要使用 EXPLAIN extended 命令
（4）MySQL 5.7 以后，默认 EXPLAIN 直接显示 partitions、filtered 中的信息
6、基本语法
（1）在具体的查询语句前，加一个 EXPLAIN，查看某个查询的执行计划
EXPLAIN SELECT select_options;
#或
DESCRIBE SELECT select_options;（2）除 SELECT 外，DELETE、INSERT、REPLACE、UPDATE 等都可以加上 EXPLAIN
（3）注意：执行 EXPLAIN 时，并没有真正的执行该后面的语句，因此可以安全的查看执行计划
7、输出各个列的作用
| 列名 | 描述 |
|---|---|
| id | 查询语句中每个 SELECT 关键字都对应一个唯一 id |
| select_type | SELECT 关键字对应的查询类型 |
| table | 表名 |
| partitions | 匹配的分区信息 |
| type | 针对单表的访问方法 |
| possible_keys | 可能用到的索引 |
| key | 实际使用的索引 |
| key_len | 实际使用的索引长度 |
| ref | 当使用索引列等值查询时，与索引列进行等值匹配的对象信息 |
| rows | 预估的需要读取的记录条数 |
| filtered | 某个表经过搜索条件过滤后，剩余记录条数的百分比 |
| Extra | 额外信息 |
8、事项
（1）不考虑各种 Cache
（2）不能显示 MySQL 在执行查询时所作的优化工作
（3）不显式关于触发器、存储过程的信息、用户自定义函数对查询的影响情况
（4）部分统计信息是估算的，并非精确值
table
1、不论查询语句有多复杂，包含多少个表，最后也是需要对每个表进行单表访问的，所以 MySQL 规定 EXPLAIN 输出的每条记录都对应着某个单表的访问方法
2、该条记录的 table 列代表着该表的表名，有时不是真实表名，可能是简称
id
1、查询语句中每个 SELECT 关键字都对应一个唯一 id
2、id 如果相同，可以认为是一组，从上往下顺序执行
3、在所有组中，id 值越大，优先级越高，越先执行
4、每个 id 号码，表示一次独立的查询，一个 SQL 的查询次数越少越好
select_type
1、查询语句包含若干个 SELECT
（1）每个 SELECT 代表着小查询
（2）每个 SELECT 的 FROM 子句中，包含若干张表，用来做连接查询
（3）每一张表都对应着执行计划输出中的一条记录，对于在同一个 SELECT 中的表来说，id 值相同
2、MySQL 为每一个 SELECT 代表的小查询，都定义一个 select_type 属性，代表 SELECT 的小查询对应的查询类型，即在整个查询中所扮演的角色
| 名称 | 描述 |
| SIMPLE | 简单 SELECT（不使用 UNION 或子查询） |
| PRIMARY | 最外层 SELECT |
| UNION | UNION 中的第二条或以后的 SELECT 语句 |
| UNION RESULT | UNION 的结果 |
| SUBQUERY | 子查询中的第一个 SELECT |
| DEPENDENT SUBQUERY | 子查询中的第一个 SELECT，取决于外部查询 |
| DEPENDENT UNION | UNION 中的第二条或以后的 SELECT 语句，取决于外部查询 |
| DERIVED | 衍生表 |
| MATERIALIZED | 物化子查询 |
| UNCACHEABLE SUBQUERY | 一个子查询，其结果不能被缓存，必须对外层查询的每一行进行重新评估 |
| UNCACHEABLE UNION | UNION 中的第二个或以后的选择，属于不可缓存的子查询（参见不可缓存的子查询） |
partitions
1、匹配的分区信息
2、对于非分区表，该值为 NULL
type
1、表示 MySQL 在表中找到所需行的方式，又称访问类型
2、访问类型
（1）system：system 是 const 特例， 当查询的表只有一行的情况下使用 system，系统表，少量数据，不需要进行磁盘 I/O
（2）const：单表中最多只有一条匹配行，优化器在当前查询中，将匹配行中的其他列中的值，当做常量来处理，如：根据主键或唯一索引的查询
（3）eq_ref：类似 ref，区别：使用唯一索引，在联表查询中使用 primary key 或者非空 unique key 作为关联条件
（4）ref：使用非唯一性索引，或唯一索引的前缀扫描， 返回匹配某个单独值的记录行
（5）fulltext：全文索引
（6）ref_or_null：类似 ref，但 MySQL 做一个额外查询，查看哪些行包含 NULL，常见于解析子查询的优化
（7）index_merge：表示出现索引合并优化，包括交集、并集、交集之间的并集，但不包括跨表和全文索引
（8）unique_subquery：比 eq_ref 复杂，使用 IN 子查询，而且子查询是主键或唯一索引；只是一个索引查找函数，它可以完全替代子查询以提高效率
（9）index_subquery：类似 unique_subquery，但在子查询中使用非唯一索引
（10）range：常数值的范围，只有给定范围内的行才能被检索，使用索引来查询出多行，输出行中的类决定使用哪个索引
（11）index：和 ALL 相同，区别：index 是扫描索引树；以下两种触发情况：1、当索引是查询的覆盖索引，即所有数据均可从索引树获取，则只扫描索引树，不需要回表查询，EXPLAIN 的 Extra 列的结果是 Using index，仅索引扫描通常比 ALL 快，因为索引的大小通常小于表数据；2、全表扫描会按索引的顺序来查找数据行，使用索引不会出现在 Extra 列中（无 Using Index）；如果 Extra 中 Using Index、Using Where 同时出现，表示利用索引查找键值；如果单独出现，则是用读索引来代替读行，但不用于查找
（12）ALL：全表扫描
3、性能从优到劣：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
4、SQL 性能优化的目标：至少达到 range 级别，要求 ref 级别，最好 consts 级别
possible_keys、key
1、possible_keys 列：表示在某个查询语句中，对某个表执行单表查询时，可能用到的索引，一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
2、key 列：表示实际使用的索引，如果为 NULL，则没有使用索引
key_len
1、实际使用的索引长度
2、计算
（1）允许为 NULL，占用 1 字节
（2）变长字段，占用 2 字节
（3）与数据类型相关
（4）与编码相关：utf8mb4 = 4，utf8 = 3，gbk = 2，latin1 = 1
ref
1、显示哪些列或常量被用于查找索引列上的值
2、当使用索引列等值匹配的条件去执行查询时，即访问方法是 const、eqmref、ref、ref_or_null.unique_subquery、index_subquery 其中之一时，ref 列展示与索引列作等值匹配的结构，如：只是一个常数或者是某个列
rows
1、MySQL 认为必须要逐行去检查和判断的记录的条数，即在 SQL 执行过程中会被扫描的行数
2、该数值越大，意味着需要扫描的行数，相应的耗时更长
3、EXPLAIN 输出 rows 是一个估算值
filtered
1、某个表经过搜索条件过滤后，剩余记录条数的百分比
2、如果使用索引，执行单表扫描，则计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条
3、不关注单表查询的 filtered，关注在连接查询中的 filtered 值，决定被驱动表要执行的次数 = rows * filtered
Extra
1、说明额外信息，包含不适合在其他列中显示，但十分重要的额外信息
2、No tables used：查询语句的没有 FROM 子句
3、Impossible WHERE：查询语句的 WHERE 子句永远为 FALSE
4、Using where
（1）对表的全部请求列都是同一个索引的部分时，不用读取表中所有信息，仅通过索引就可以获取所需数据，表示 MySQL 服务器将在存储引擎检索行后，再进行过滤
（2）当使用全表扫描，执行对某个表的查询，并且该语句 WHERE 子句中，有针对该表的搜索条件时，在 Extra 列中会提示上述额外信息
（3）当使用索引访问，执行对某个表的查询，并且该语句 WHERE 子句中，有除了该索引包含的列之外的其他搜索条件时，在 Extra 列中也会提示上述额外信息
5、No matching min / max row：查询列表有 MIN 或 MAX 聚合函数，但没有符合 WHERE 子句中的搜索条件的记录
6、Using index：查询列表以及搜索条件中，只包含属于某个索引的列，即使用索引覆盖，不需要回表情况下，
7、Using index condition
（1）有些搜索条件中，虽然出现索引列，但却不能使用索引
（2）索引条件下推：Index Condition Pushdown，减少回表操作，使用该特性，在 Extra 列中将会显示 Using index condition
8、Using join buffer（Block Nested Loop）：在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL 一般会为其分配一块名叫 join buffer 内存块来加快查询速度，即基于块的嵌套循环算法
9、Not exists：使用连接查询时，WHERE 子句中包含要求被驱动表的指定列等于 NULL，且指定列不允许存储 NULL 值
10、Using intersect(...)、Using union(...)、Using sort_union(...)
（1）Using intersect(...)：说明准备使用 Intersect 索引合并的方式执行查询，括号中的 ... 表示需要进行索引合并的索引名称
（2）Using union(...)：说明准备使用 Union 索引合并的方式执行查询
（3）Using sort_union(...)：说明准备使用 Sort-Union 索引合并的方式执行查询
11、Zero limit：当 LIMIT 子句的参数为 0 时，表示不打算从表中读出任何记录
12、Using filesort
（1）某个查询需要使用文件排序的方式执行查询
（2）某些情况下，对结果集中的记录进行排序，是可以使用索引的
（3）但是很多情况下，排序操作无法使用到索引，只能在内存中（记录较少时）或者磁盘中（记录较多的时候）进行排序
（4）MySQL 把这种在内存中或磁盘上进行排序的方式，统称为文件排序（filesort）
13、Using temporary
（1）查询中使用到内部的临时表
（2）在许多查询的执行过程中，MySQL 可能会借助临时表来完成一些功能，比如：DISTINCT、GROUP BY、UNION 等，如果不能有效利用索引来完成查询，MySQL 可能通过建立内部的临时表来执行查询
EXPLAIN 四种输出格式
1、传统格式
2、JSON 格式
3、TREE 格式
4、可视化输出
传统格式
1、默认，不需要加限定修饰
2、输出一个表格形式，概要说明查询计划
3、缺少执行计划的成本
JSON 格式
1、最详尽的输出信息的格式，包含执行的成本信息
2、在 EXPLAIN 和真正的查询语句中，加上 FORMAT=JSON
3、EXPLAIN 的 column 与 JSON 对应关系（MySQL 5.7）
| Column | JSON |
| id | select_id |
| select_type | None |
| table | table_name |
| partitions | partitions |
| type | access_type |
| possible_keys | possible_keys |
| key | key |
| key_len | key_length |
| ref | ref |
| rows | rows |
| filtered | filtered |
| Extra | None |
4、cost_info：成本信息
（1）query_cost： 一个查询块的总成本，不管是顶级查询还是子查询，包含在 cost_info 节点，是 query_block 节点的子节点，对于顶层的 SELECT 应该等于 last_query_cost 状态变量
（2）sort_cost：第一个排序操作（GROUP BY 或 ORDER BY）成本（包含使用 filesort 成本），第二个排序操作的成本不被打印，因为优化器并不计算它，与算法无关，包含在 cost_info 节点，是 order_operation 节点的子节点
（3）read_cost：在查询块中使用的每个表的读取成本，即访问方法成本，包含 I/O 成本、检测 rows * (1 - filter) 条记录的 CPU 成本
（4）eval_cost：查询块中每个表的条件评估成本，检测 rows * filter 条记录的成本
（5）prefix_cost：在查询块中执行前缀连接的成本，即从第一个表到给定值的那个表（包括它）连接查询块的成本
（6）data_read_per_join：在单个查询块执行过程中，估计从表中处理的数据量
（7）rows_produced_per_join / rows_examined_per_scan：在单个查询块执行过程中，估计从表中（查询块中的每个表）产生 / 检查的记录数量
（8）used_columns：查询中用于读或写的表中的列（查询块中的每个表）
TREE 格式
1、MySQL 8.0.16 版本之后引入的新格式
2、主要根据查询的各个部分之间的关系、执行顺序来描述如何查询
可视化输出
1、通过 MySQL Workbench 可视化查看 MySQL 执行计划
SHOW WARNINGS
1、使用 EXPLAIN 查看某个查询的执行计划后，还可以使用 SHOW WARNINGS 查看与这个查询的执行计划有关的一些扩展信息
2、三个信息字段：Level、Code 、Message
3、最常见：Code 为 1003 时，Message 类似于查询优化器，展示重写查询后的语句
trace
1、OPTIMIZER_TRACE
（1）MySQL 5.6 引入一项跟踪功能，跟踪优化器做出的决策，如：访问表的方法、各种开销计算、各种转换等
（2）将跟踪结果记录到 INFORMATION_SCHENA、OPTIMIZER_TRACE 表中
（3）此功能默认关闭
2、可分析语句：SELECT、INSERT、REPLACE、UPDATE、DELETE、EXPLAIN、SET、DECLARE、CASE、IF、RETURN、CALL
3、开启并设置格式为 JSON
SET optimizer_trace="enabled=on",end_markers_in_json=on;4、设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示，默认 1048576
SET optimizer_trace_max_mem_size=1000000;5、optimizer_trace
（1）总开关，默认值：enabled=off,one_line=off
（2）enabled：是否开启 optimizer_trace，on 表示开启，off 表示关闭
（3）one_line：是否开启单行存储，on 表示开启，off 表示关闭，将会用标准 JSON 格式化存储，设置成 on 将会有良好的格式，设置成 off 可节省一些空间
6、optimizer_trace_features
（1）控制 optimizer_trace 跟踪内容
（2）默认值：greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on，表示开启所有跟踪项
7、greedy_search：是否跟踪贪心搜索
8、range_optimizer：是否跟踪范围优化器
9、dynamic_range：是否跟踪动态范围优化
10、repeated_subselect
（1）是否跟踪子查询
（2）如果设置成 off，只跟踪第一条 Item_subselect 执行
11、optimizer_trace_limit
（1）控制 optimizer_trace 展示多少条结果
（2）默认 1
12、optimizer_trace_max_mem_size
（1）optimizer_trace 堆栈信息允许的最大内存
（2）默认 1048576
13、optimizer_trace_offset
（1）第一个要展示 optimizer trace 的偏移量
（2）默认 -1
14、end_markers_in_json
（1）若 JSON 结构过大，则很难将右括号和左括号配对
（2）为了帮助读者阅读，可将其设置成 on，会在右括号附近加上注释
（3）默认 off
15、以上参数可用 SET 语句操作
16、optimizer_trace_limit、optimizer_trace_offset 常配合使用
（1）默认情况下，optimizer_trace_offset=-1，optimizer_trace_limit=1：记录最近 1 条 SQL 语句，展示时，每次展示 1 条数据
17、查询 information_schema.optimizer_trace，获悉 MySQL 如何执行 SQL
SELECT * FROM information_schema.optimizer_trace\G（1）QUERY：查询语句
（2）TRACE：QUERY 字段对应语句的跟踪信息、
（3）MISSING_BYTES_BEYOND_MAX_MEM_SIZE：跟踪信息过长时，被截断的跟踪信息的字节数
（4）INSUFFICIENT_PRIVILEGES：执行跟踪语句的用户是否有查看对象的权限，当不具有权限时，该列信息为 1，且 TRACE 字段为空，一般在调用带有 SQL SECURITY DEFINER 视图，或是存储过程的情况下，会出现此问题
18、TRACE 字段
1、join_preparation：展示准备阶段的执行过程
2、join_optimization：展示优化阶段的执行过程，是分析 OPTIMIZER TRACE 重点
3、join_execution：展示执行阶段的执行过程
join_optimization 段落
1、condition_processing
（1）该段用来做条件处理，主要对 WHERE 条件进行优化处理
（2）condition：优化对象类型，WHERE 条件句，或 HAVING条件句
（2）original_condition：优化前的原始语句
（3）steps：主要包括三步，分别是 quality_propagation（等值条件句转换），constant_propagation（常量条件句转换），trivial_condition_removal（无效条件移除的转换）
（4）transformation：转换类型句
（5）resulting_condition：转换之后的结果输出
2、substitute_generated_columns：用于替换虚拟生成列
3、table_dependencies
（1）分析表之间的依赖关系
（2）table：涉及的表名，若存在别名，也会展示
（3）row_may_be_null：行是否可能为 NULL，指 JOIN 操作之后，该表中的数据是否可能为 NULL，如果语句中使用 LEFT JOIN，则后一张表的 row_may_be_null 显示为 true
（4）map_bit：表的映射编号，从 0 开始递增
（5）depends_on_map_bits：依赖的映射表，主要是当使用 STRAIGHT_JOIN 强行控制连接顺序，或 LEFT JOIN / RIGHT JOIN 有顺序差别时，会在 depends_on_map_bits 中展示前置表的 map_bit 值
4、ref_optimizer_key_uses
（1）列出所有可用的 ref 类型的索引
（2）如果使用组合索引的多个部分，则会在 ref_optimizer_key_uses 下列出多个元素，每个元素中会列出 ref 使用的索引及对应值
5、rows_estimation
（1）用于估算需要扫描的记录数
（2）table：表名
（3）range_analysis：table_scan：如果全表扫描，需要扫描多少行，以及需要的代价，potential_range_indexes：列出表中所有的索引，并分析其是否可用，如果不可用的话，列出不可用原因，如果可用，列出索引中可用的字段；setup_range_conditions：如果有可下推的条件，则带条件考虑范围查询；group_index_range：当使用 GROUP BY 或 DISTINCT 时，是否有合适的索引可用，当未使用 GROUP BY 或 DISTINCT时，显示 chosen=false, cause=not_group_by_or_distinct；若使用 GROUP BY 或 DISTINCT，但多表查询时，会显示chosen=false,cause =not_single_table；其他情况下，尝试分析可用索引（potential_group_range_indexes），并计算对应的扫描行数及其所需代价；skip_scan_range：是否使用 skip scan，为 MySQL 8.0 新特性；analyzing_range_alternatives：分析各个索引的使用成本：range_scan_alternatives：range 扫描分析：index：索引名，ranges：range 扫描的条件范围，index_dives_for_eq_ranges：是否使用 index dive，该值会被参数 eq_range_index_dive_limit变量值影响，rowid_ordered：该 range 扫描的结果集是否根据 PK 值进行排序，using_mrr：是否使用 mrr，index_only：表示是否使用覆盖索引，rows：扫描的行数，cost：索引的使用成本，chosen：表示是否使用了该索；analyzing_roworder_intersect：分析是否使用索引合并（index merge），如果未使用，在 cause 中展示原因；如果使用索引合并，在该部分展示索引合并代价
（4）chosen_range_access_summary：在前一个步骤中分析各类索引使用的方法及代价，得出一定中间结果后，在 summary 阶段汇总前一阶段的中间结果确认最后的方案：range_access_plan：range 扫描最终选择的执行计划：type：展示执行计划的type，如果使用了索引合并，则显示 index_roworder_intersect，index：索引名，rows：扫描的行数，ranges：range 扫描的条件范围；rows_for_plan：该执行计划的扫描行数；cost_for_plan：该执行计划的执行代价；chosen：是否选择该执行计划
6、considered_execution_plans
（1）负责对比各可行计划的开销，并选择相对最优的执行计划
（2）plan_prefix：当前计划的前置执行计划
（3）table：涉及的表名，如果有别名，也会展示
（4）best_access_path：通过对比 considered_access_paths，选择一个最优的访问路径：considered_access_paths：当前考虑的访问路径，access_type：使用索引的方式，可参考 EXPLAIN 中的 type 字段，index：索引，rows：行数，cost：开销，chosen：是否选用这种执行路径
（5）condition_filtering_pct：类似 EXPLAIN 的 filtered 列，为一个估算值
（6）rows_for_plan：执行计划最终的扫描行数，由 considered_access_paths.rows * condition_filtering_pct 计算获得
（7）cost_for_plan：执行计划的代价，由 considered_access_paths.cost 相加获得
（8）chosen：是否选择该执行计划
7、attaching_conditions_to_tables
（1）基于 considered_execution_plans 中选择的执行计划，改造原有 WHERE 条件，并针对表增加适当的附加条件，以便于单表数据的筛选
（2）便于 ICP（索引条件下推），但 ICP 是否开启，并不影响这部分内容的构造
（3）original_condition：原始的条件语句
（4）attached_conditions_computation：使用启发式算法计算已使用的索引，如果已使用的索引的访问类型为 ref，则计算用 range 能否使用组合索引中更多的列，如果可以，则用 range 方式替换 ref
（5）attached_conditions_summary：附加之后的情况汇总：table：表名，attached：附加条件，或原语句中能直接下推给单表筛选的条件
8、finalizing_table_conditions：最终经过优化后的表条件
9、refine_plan：改善执行计划：table：表名及别名
sys schema
1、MySQL 监控分析视图
2、在 MySQL 5.7.7 版本中新增 sys schema，将 performance_schema、information_schema 中的数据，以更容易理解的方式总结归纳为视图
3、目的：降低查询 performance_schema 复杂度，让 DBA 能够快速的定位问题
4、视图摘要
（1）主机相关：以 host_summary 开头，汇总 I/O 延迟的信息
（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 开头，展示等待事件的延迟情况
5、索引情况
（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='表名' ;6、表相关
（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）查询占用 buffer pool 较多的表
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='表名';7、语句相关
（1）监控 SQL 执行的频率
SELECT db,exec_count,query
FROM sys.statement_analysis
ORDER BY exec_count DESC;（2）监控使用了排序的 SQL
SELECT db,exec_count,first_seen,last_seen,query
FROM sys.statements_with_sorting
LIMIT 1;（3）监控使用了临时表，或磁盘临时表的 SQL
SELECT db,exec_count,tmp_tables,tmp_disk_tables,query
FROM sys.statement_analysis
WHERE tmp_tables > 0 OR tmp_disk_tables > 0
ORDER BY (tmp_tables + tmp_disk_tables) DESC;8、I/O 相关
（1）查看消耗磁盘 I/O 文件
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;9、Innodb 相关
（1）行锁阻塞情况
SELECT *
FROM sys.innodb_lock_waits;
浙公网安备 33010602011771号