MySQL explain 洞悉SQL执行全过程

柏油
226 阅读5分钟

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

如何让SQL跑的更快、效率更高

系统负载高、响应慢，在DB层面，低效的SQL很有可能是罪魁祸首！

通过explain关键字诊断SQL效率

explain关键字是mysql提供用于分析sql执行的具体信息，比如 扫描类型、索引、扫描行数、排序、临时文件等等

  1. explain可用于SELECT, DELETE, INSERT, REPLACE以及 UPDATE语句之前；

explain输出列

ColumnJSON NameMeaning
idselect_idThe SELECT identifier
select_typeNoneThe SELECT type
tabletable_nameThe table for the output row
partitionspartitionsThe matching partitions
typeaccess_typeThe join type
possible_keyspossible_keysThe possible indexes to choose
keykeyThe index actually chosen
key_lenkey_lengthThe length of the chosen key
refrefThe columns compared to the index
rowsrowsEstimate of rows to be examined
filteredfilteredPercentage of rows filtered by table condition
ExtraNoneAdditional information

其中关键的几列：

  1. type: 描述表的join关系，由好到坏：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL；其中除了ALL全表扫描外，其他类型都会通过索引优化 进而减少扫描行数

  2. possible_keys: 当前SQL中出现的索引, 也就是当前SQL可能会引用的索引

  3. key：最终选择的索引列

  4. key_len: 表示使用的索引长度,是以字节为单位

  5. ref: 引用字段，如果是使用的常数等值查询，这里会显示const；如果是关联查询，这里是 驱动表的某个字段(关联查询时通常驱动表的某个字段是被驱动表中建立索引一个字段)；如果条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

  6. rows: 表示的是预计扫描行数

  7. filtered：表示返回结果的行数占 rows 的百分比(最大值是100)；在rows一定的情况下，filtered列的值越小越好，因为对于被驱动表来说，其扫描次数由驱动表的rows x filtered决定。例如，如果rows为1000，过滤后(where子句过滤)为50.00(50%)，则需要与下表连接的行数为1000 × 50% = 500 查看

  8. Extra: 输出查询计划的一些额外重要信息，比如文件排序、索引等等

type字段(常见部分)

  1. system: 表里只有一行数据，根据条件将这行数据查询出来
  2. const：最多匹配表中的一行数据，通常是PRIMARY KEY or UNIQUE index查询
  3. eq_ref: join操作时，驱动表A与被驱动表B，A表的驱动字段f通常是B表的主键id或者B表的唯一键
  4. ref: 一般是通过普通索引匹配少量数据时的类型。例如，join操作时，驱动表A与被驱动表B，A表的驱动字段f通常是B表的非主键id或者B表的非唯一键，也就是只是b表的普通索引键，这一点正好和eq_ref类型互补
  5. ref_or_null: 与ref类型相似，不同是加了is null条件处理为空的查询
  6. index_merge：多索引查询，然后合并各索引搜索的结果集
  7. range: 通过索引查询的结果集通常是一个数据行数不小的范围
  8. index：这种类型的效率仅优于ALL，会先扫描索引树，如果要求返回的列可以满足要求就直接返回不用进行回表操作了，否则会将满足条件的所有数据都进行回表查询数据
  9. ALL: 全表扫描

Extra字段详解(常见部分)

  1. Using filesort: 表示需要排序。当需要通过order by子句有序输出时，如果待排序内容超过了缓冲区大小，则会借助于文件排序的方式来完成排序操作

  2. Using index: 使用覆盖索引，不需要回表。查询字段从索引树(普通索引)就可以拿到的情况就不会通过回表的方式从聚簇索引(也就是主键索引)索取数据，节约的回表操作可以减少I/O操作

  3. Using index condition: 联合索引上使用索引下推尽可能多的过滤掉不满足where条件的数据，从而减少I/O操作

  4. Using join buffer：join连表查询会使用到join buffer。Block Nested Loop 和 Batched Key Access算法会使用。

  5. Using MRR: Multi-Range Read优化策略，通常情况下我们会通过普通索引快速定位到主键ID，然后回表从聚簇索引上获取数据；虽然在普通索引上是顺序访问，但在聚簇索引树上很有可能是通过随机访问的方式，因此，该优化策略通过将从普通索引获取的主键ID排序后，然后顺序访问的方式访问聚簇索引树

  6. Using temporary：表示使用了临时表。分为内存临时表和磁盘临时表，内存临时表有一定的限制，达到限制之后需要使用磁盘临时表存储中间数据。通常可能在group by, order by 以及union操作(会去重) 可能会使用内部临时表

  7. Using where：使用where条件过滤

join算法：

  1. Index Nested-Loop Join(NLJ)：驱动表走全表扫描，被驱动表通过索引扫描

  2. Block Nested-Loop Join(BNL)：被驱动字段不存在索引，因此被驱动表也只能走全表扫描，此时通过join buffer，将驱动表的数据一块块读到join buffer, 然后从被驱动表一条条读取数据做join操作(与join buffer中的数据对比)，重复这两步操作，直到join操作完成；由此可见合理设置join buffer大小至关重要，join buffer越大分块放入次数越少, I/O次数也就越少。

  3. Batched Key Access(BKA)：是NLJ算法的优化版本。借助于MRR优化操作，将多条主键ID暂存join buffer，排序后从聚簇索引读取数据，因为磁盘顺序读取的效率也很高





参考文档 mysql5.7

avatar
文章
阅读
粉丝