MySQl索引（二）如何看懂explain工具信息，使用explain工具来分析索引

2024-05-21 20:43:56 浏览数 (1)

Explain工具

EXPLAIN is used to obtain a query execution plan (that is, an explanation of how MySQL would execute a query).

EXPLAIN 工具能用于获取查询执行计划，即分析 MySQL 如何执行一个 SQL 语句。我们可以通过使用EXPLAIN 去模拟优化器执行 SQL 语句，从而分析 SQL 语句有没有使用索引、是否采用全表扫描方式、判断能否更进一步优化等。我们可以根据EXPLAIN 输出的数据来分析如何优化查询语句，提升查询语句的性能瓶颈。

如何使用 EXPLAIN ？

在 select 语句之前增加 explain 关键字即可使用EXPLAIN 工具。MySQL 会在查询上设置一个标记，此时，执行查询不会返回查询结果，而是返回执行计划的相关信息。如果查询语句的 from 中有子查询语句，依旧会执行这个子查询语句，结果会被放入临时表。

explain 输出说明

image.pngimage.png
  1. id：select 的序列号，查询语句中有几个 select 就会有多少个 id 列，一般来说 id 的顺序按 select 出现的顺序增加。序号越大表示该 select 的执行优先级越高，id 相同则按照列表顺序从上往下执行，若 id 为 NULL，则最后执行。
  2. select_type：select 的查询类型，表示对应行是简单查询还是复杂查询，该类型的值如下表。
image.pngimage.png
image.pngimage.png

最常见的几种类型：

  • simple：简单查询，即查询语句中不包含子查询和 union。
  • primary：表示为复杂查询中最外层的select
  • union：在 union 中的第二个和随后的select
  • derived：包含在 from 子句中的子查询。MySQL 会将结果存放在一个临时表中，mysql 中也称为派生表（derived table），由子查询派生出来的表。
  • Subquery：子查询中的第一个 select，其不在 from 子句中。
  1. Table：表示 explain 中一行正在访问的表。如果 sql 语句中定义表的别名，则显示的是表别名。
    • 当 from 子句中存在子查询时，该列是 derivenN 格式的，表示当前查询依赖 id=N 的查询，会先执行 id=N 的查询。
    • 当语句中有 union 时，UNION RESULT 的 table 列的值为 union1,2，1和2表示参与 union 的 select 行 id。
  2. partitions：当前查询匹配表的分区，若是没有分区的表则返回 NULL；反之则返回查询将访问的分区。
  3. type：表示连接类型或者访问类型，即MySQL如何查找表中的数据行，查找数据行记录的大概范围。性能从优到劣排序如下：
    • NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表
    • system：该表只有一行（相当于系统表），system是const类型的特例
    • const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可
    • eq_ref：当使用了索引的全部组成部分，并且索引是PRIMARY KEY或UNIQUE NOT NULL 才会使用该类型，性能仅次于system及const。
    • ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。
    • fulltext：全文索引
    • ref_or_null：该类型类似于ref，但是MySQL会额外搜索哪些行包含了NULL。这种类型常见于解析子查询
    • index_merge：此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引
    • unique_subquery：该类型和eq_ref类似，但是使用了IN查询，且子查询是主键或者唯一索引。
    • index_subquery：和unique_subquery类似，只是子查询使用的是非唯一索引
    • range：范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。
    • index：全索引扫描，和ALL类似，只不过index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型。有两种场景会触发：
      • 如果索引是查询的覆盖索引，并且索引查询的数据就可以满足查询中所需的所有数据，则只扫描索引树。此时，explain的Extra 列的结果是Using index。index通常比ALL快，因为索引的大小通常小于表数据。
      • 按索引的顺序来查找数据行，执行了全表扫描。此时，explain的Extra列的结果不会出现Uses index。
    • ALL：全表扫描，性能最差。
  4. possible_keys：当前查询可能使用到哪些索引，由于是在优化前期创建的，后续优化可能不会使用到。如果该列为 NULL，这种情况是可能是查询表中只有少量数据，MySQL 认为全表查询比索引查询更快。
  5. key：当前查询中 mysql 实际使用到的索引。如果该列为 NULL ，则表明所有哦使用索引。我们可以使用 force index 来强制mysql 使用 possible_keys 列中的索引，或者通过 ignore index 忽略 possible_keys 列中的索引。
  6. key_len：当前查询 mysql 在索引中使用的字节数，我们可以通过判断该值的大小算出使用索引的具体列。索引是有最大长度限制的（768 字节），如果索引字符串过长时，mysql 会采用类似左前缀索引的方式进行处理，将索引字符串的前半部分字符摘出来作为索引。
  7. ref：表示key 列记录的索引中，表查找值所用到的字段或常量。常见的有：const（常量），字段名（例：film.id）
  8. rows：MySQL 估算读取需要检测的行数，理论上数值越小越好，但实际情况不一定，调优时以实际情况为准。注意该值并不是结果集的行数。
  9. filtered：表示符合查询条件数据的百分比，可以使用 rows * filtered/100 估算出将要和 explain 中前一个表连接的行数（前一个表指 explain 中的 id 值比当前表 id 值小的表，id 值越小越先执行）。
    1. 在MySQL 5.7之前，可以通过 explain extended 显示filtered字段。
    2. MySQL.5.7及更高版本，explain 默认展示 filtered 字段。
  10. Extra：展示当前查询的附加信息。常见的重要值如下：
    • Using index：表示使用覆盖索引，即 select 数据在索引信息中，不需要再查找读取其他值。
    • Using filesort：查询语句用外部排序(order by)，无法使用索引排序时。若数据较小，则从内存排序；否则需要在磁盘完成排序。这种情况下一般要尝试使用索引优化。
    • Using index condition：先按照条件过滤索引，找到符合索引条件的数据行，再用 where 子句条件中其他条件过滤数据行。即索引查询的列不能完全被索引覆盖。
    • Using temporary：当前查询语句需要创建一张临时表来保存数据，如果查询中有 group by 和 order by 子句（不同列）时可能会出现这种情况。而出现这种情况，我们也是需要对sql 语句进行优化，首先可以尝试建立索引来优化。
    • Select tables optimized away：结果集只有一行数据；得到该行数据需要计算一组确定的数据行。一般在使用某些聚合函数（max()、min()）访问索引中的某个字段时出现。

补充： Key_len 计算规则如下：

  1. 字符串 ：分别是 char(n) 和 varchar(n)，在 mysql5.0.3 以后版本中，n 均代表字符数，而不是字节数。在 utf-8 编码格式下，一个数字或字母占 1 个字节，一个汉字占 3个字节。这两种占用字节分别计算如下：
  2. char(n) ：若是汉字，则长度就是 3n 字节；数字或字母则是 n。
  3. varchar(n)：若是汉字，则长度是 3n 2 字节， 2 字节表示存储字符串的长度，因为 varchar 是变长字符串。数字或字母则是 n 2。
  4. 数值类型
  5. Tinyint：1 字节
  6. Smallint：2 字节
  7. Int：4 字节
  8. Bigint：8 字节　　
  9. 时间类型　 1. Date：3 字节 2. Timestamp：4 字节 3. Datetime：8 字节
  10. 如果字段允许为 NULL，需要 1 字节记录是否为 NULL

覆盖索引定义：mysql 执行计划 explain 结果里的 key 有使用索引，如果 select 后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra 里一般都有 using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值

参考

  • MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format
  • explain之key_len计算 - yayun - 博客园 (cnblogs.com)
优化 mysql 工具 数据 索引

0 人点赞