mysql-读懂explain

explain

• id：执行顺序，数字越大优先级越高，如果id相同从上到下执行
• select_type：每个子句的类型
  • simple：不包含子查询和union
  • PRIMARY：内部包含子查询
  • SUBQUERY：子查询（select和where中的）
  • DEPENDENT SUBQUERY：子查询，依赖于外部查询的结果集
  • DERIVED：子查询（from中的）
  • UNION：出现在UNION后
  • UNION RESULT：从union的表中获取结果
• table:来自哪张表
  • < unionM,N>M和N union后的结果集
  • < derivedN> id为N的查询结果
• partitions:查询的分区id
• type：在表中查询用哪种方式
  • all：全表扫描
  • index：遍历整个索引树，索引扫描，因为是内部有链表结构，所以速度很快 select id from tb
  • range：对索引范围进行扫描 select * from tb where id between 1 and 5
  • ref：非唯一索引扫描，返回匹配某个单独值的所有行
  • eq_ref：唯一索引进行扫描，对于每个索引键，表中只有一条记录与之匹配
  • const、system：mysql进行优化，用主键唯一锁定一条数据，将其转换为常量，在where中使用主键,如果是联合主键，就是所有的主键值一起使用
  • null：不访问表或者索引
• possible_keys：字段上所有涉及的索引，但是不一定使用
• key：使用的索引
• key_len：索引字段的长度，由定义长度计算，并不是实际长度
• ref：哪些列或者常量被用来查询索引
• rows：估算出扫描的行数
• filtered：返回的结果行数占读取行数的百分比，这个值越大越好
• Extra：
  • Using index：索引覆盖
  • Using where：使用where子句过滤
  • Using filesort：无法利用索引完成排序为文件排序
  • Using index condition：查找使用了索引，但是需要回表查询数据,mysql开启了ICP的话，可以减少存储引擎访问基表的次数

  https://www.cnblogs.com/zhp-king/p/7250810.html

实际分析

• extra是using index，意思就是索引覆盖

• extra是using where，type是ref，表明虽然用到了索引，但是没有索引覆盖，产生了回表二次查询。

• extra是using where using index，type是ref，表明用到了索引，索引也覆盖了，using where代表发生了索引过滤

• extra是using where using index，type变成了index，这表明发生了索引扫描

• extra是using index，type是const，代表引擎根据主键直接取出了唯一的数据，因为是唯一的数据，所以没有using where。同时还索引全覆盖

  explain select xxx from table where xxx=’something’ and yyy= ‘some’ and zzz=’2018-01-19 00:00:00’ 
  -- xxx yyy zzz是联合主键的情况
  

• extra变成了null，type还是const，因为没有索引覆盖，直接使用主键在聚集索引上查找

explain select aaa from table where xxx=’something’ and yyy= ‘some’ and zzz=’2018-01-19 00:00:00’ 
-- xxx yyy zzz是联合主键的情况

• extra都为using where，type是all，代表是全表扫描后过滤

参考：https://blog.csdn.net/jeffrey11223/article/details/79100761

限制：

• explain无法展示存储过程，触发器的影响
• explain无法分析存储过程
• 早期版本只能分析select，5.7后可以分析delete update

获取sql语句的执行时间

• 开启profile: set profiling=1;
• 获取sql语句执行时间: show profiles;
• 获取某一条的详细执行时间: show profile for query query_id

慢查询日志

• set global slow_query_log_file=... 慢查询日志的位置
• set global log_queries_not_using_indexes=on 记录没有使用到索引的查询
• set global long_query_time=0.001 慢查询的阈值，单位秒
• set global low_query_log=on 开启慢查询日志
• 可以使用mysqldumpslow log 分析慢查询日志

trace

• 开启optimizer_trace功能，可以查看到执行计划的选择逻辑

set optimizer_trace='enabled=on';    --- 开启trace
set optimizer_trace_max_mem_size=1000000;    --- 设置trace大小
set end_markers_in_json=on;    --- 增加trace中注释
-- 执行相应的语句
select * from information_schema.optimizer_trace;-- 查看分析结果

trace具体使用方式：https://blog.csdn.net/xj626852095/article/details/52767963