MySQL --- 读书笔记 --- EXPLAIN

• last_query_cost，查询使用的页数量，衡量执行计划的成本

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

• 慢查询日志，用于记录执行过程中，响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，默认值是10s

• 默认系统是没有开启慢查询日志的，因为开启之后会带来一定的性能影响

• 查看慢查询是否开启

show variables like '%slow_query_log'

• 开启慢查询

set global slow_query_log='ON'

• 查看阈值

show variables like '%long_query_time%'

• 设置阈值

set global long_query_time=1

• 查看系统中有多少条慢查询记录

show global status like '%slow_queries%'

慢查询日志分析工具：mysqldumpslow

查看SQL执行成本：show profile

分析查询语句：EXPLAIN

定位到查询慢的语句之后，我们就可以使用EXPLAIN工具做针对性的分析，通过这个工具我们可以知道

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

工具分析后的显示是一个执行计划，它们有很多参数

• table：工具查询后的每一条记录都对应着一个表

• id：在一个大的查询语句中，一个SELECT关键字对应一个唯一的id；id相同，执行顺序从上到下，id不同，值越大优先级越高，一个不同的id代表一趟独立的查询

• select_type：每一个select查询都有一个这样的属性，表明它在整个大查询里面扮演的角色

  1. 查询语句不包含UNION或者子查询的都是SIMPLE类型
  2. 连接查询也算是SIMPLE类型
  3. 包含UNION或者UNION ALL或者子查询的大查询，最左边或者最外层的子查询是PRIMARY类型
  4. 包含UNION或者UNION ALL的大查询，除了最左边的，其他的小查询都是UNION类型
  5. 在包含UNION的大查询，临时表的类型是UNION RESULT
  6. 在包含子查询中，并且是不相关子查询，类型是SUBQUERY
  7. 在包含子查询中，并且是相关子查询，类型是DEPENDENT SUBQUERY
  8. 包含UNION或者UNION ALL的大查询，小查询依赖于外层查询的话，除了最左的小查询，其余的类型是DEPENDENT UNION
  9. 包含派生表的大查询，派生表对应的子查询的类型是DERIVED

• type：执行计划的一条记录就代表着对某个表的执行查询时的访问方法，type就表明这个访问方法是什么，完整的访问方法有如下几种，性能从高到低排列

  • system：当表中只有一条记录，并且存储引擎的统计数据是精确的，比如，MyISAM、Memory

  • const：当根据主键或者唯一索引列与常数做等值匹配

  • eq_ref：在连接查询中，如果被驱动表是通过主键或者唯一索引列等值匹配的方式，那么对被驱动表的访问是eq_ref

  • ref：当通过普通的二级索引列与常量做等值匹配

  • fulltext

  • ref_or_null：当对普通二级索引做等值匹配时，该索引列的值可以是NULL

    SELECT * FROM s1 WHERE k1='a' OR k1 IS NULL
    

  • index_merge：单表访问在某些场景下可以使用索引合并的方式查询

    SELECT * FROM s1 WHERE k1='a' OR k2='b'
    // k1和k2都有索引
    

  • unique_subquery：针对一些包含IN子查询的语句中，当查询优化器决定将IN子查询优化为EXISTS子查询，而且子查询可以使用到主键等值匹配的时候

  • index_subquery

  • range：使用索引获取某些范围的记录

  • index：当可以使用索引覆盖，但需要扫描全部索引记录时

  • ALL：全表扫描

• possible_keys和key：可能用到的索引和实际会用到的索引

• key_len：实际使用到的索引长度（bytes），值越大越好，主要针对联合索引，有一定参考意义

• ref：当使用索引列等值匹配时，与索引列进行等值匹配的对象信息；比如只是一个常数或者某个列

• rows：预估需要读取的记录条数，值越小越好

• filtered：某个表经过搜索条件过滤之后剩余记录条数的百分比；对于单表查询来说，这个值意义不大，主要是在连接查询中，驱动表对应的执行计划的该值，它决定了被驱动表需要执行的次数（rows * filtered）

• Extra：用来说明一些额外信息，可以根据这个值更准确的理解MySQL到底如何执行给定的查询语句，有几个最重要的信息如下

  • No tables used：当查询语句中没有FROM关键字时
  • Impossible WHERE：WHERE关键字的条件永远是FALSE
  • Using where：全表扫描时，使用WHERE；或者有索引但不是全部条件都包含索引列的时候
  • No matching row：当查询列表处使用聚合函数，但是WHERE条件没有符合的记录
  • Using index：当可以使用覆盖索引的时候，不需要回表操作时
  • Using index condition：搜索条件中虽然出现了索引列，但却不能使用到索引；出现索引条件下推
  • Using join buffer：连接查询中，当被驱动表不能使用索引时，MySQL会分配一块内存来加快查询速度，也就是基于块的嵌套循环算法
  • No exists：在左外连接时，WHERE条件要求被驱动表某个列是NULL,但是这个列不可能是NULL时出现
  • Using intersect(..)/union(..)/sort_union(..)：索引合并
  • Zero limit：LIMIT限制是0
  • Using filesort：排序操作无法使用到索引时
  • Using temporary：当不能有效使用到索引时，使用临时表

小结

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

EXPLAIN的进一步使用

四种输出格式

1. 传统格式
2. JSON格式：FORMAT=JSON，JSON是输出最详尽的格式 ，里面包含了执行的成本信息
3. TREE格式：主要根据查询的各个部分之间的关系和各部分的执行顺序

分析优化器执行计划：trace

OPTIMIZER_TRACE可以跟踪优化器做出的各种决策，并将跟踪结果记录到INFORMATION_SCHEMA.OPTIMZER_TRACE

• 默认关闭，需要开启

SET optimizer_trace="enabled=on", end_markers_in_json=on;

set optimizer_trace_max_mem_size=100000;

MySQL监控分析分析视图：sys-schema

它将performance_schema和information_schema中的数据以更容易理解的方式总结归纳为视图，目的是为了降低查询performance_schema的复杂度

摘要

通过sys库查询时，会消耗大量资源收集相关信息，严重的可能会导致业务请求被阻塞，建议不要生产频繁地查询sys