MySQl索引(二)如何看懂explain工具信息,使用explain工具来分析索引

MySQL
40
0
0
2024-10-31
标签   MySQL索引

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.png

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

image.png

image.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,212表示参与 unionselectid
  1. partitions:当前查询匹配表的分区,若是没有分区的表则返回 NULL;反之则返回查询将访问的分区。
  2. 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:全表扫描,性能最差。
  1. possible_keys:当前查询可能使用到哪些索引,由于是在优化前期创建的,后续优化可能不会使用到。如果该列为 NULL,这种情况是可能是查询表中只有少量数据,MySQL 认为全表查询比索引查询更快。
  2. key:当前查询中 mysql 实际使用到的索引。如果该列为 NULL ,则表明所有哦使用索引。我们可以使用 force index 来强制mysql 使用 possible_keys 列中的索引,或者通过 ignore index 忽略 possible_keys 列中的索引。
  3. key_len:当前查询 mysql 在索引中使用的字节数,我们可以通过判断该值的大小算出使用索引的具体列。索引是有最大长度限制的(768 字节),如果索引字符串过长时,mysql 会采用类似左前缀索引的方式进行处理,将索引字符串的前半部分字符摘出来作为索引。
  4. ref:表示key 列记录的索引中,表查找值所用到的字段或常量。常见的有:const(常量),字段名(例:film.id)
  5. rows:MySQL 估算读取需要检测的行数,理论上数值越小越好,但实际情况不一定,调优时以实际情况为准。注意该值并不是结果集的行数。
  6. filtered:表示符合查询条件数据的百分比,可以使用 rows * filtered/100 估算出将要和 explain 中前一个表连接的行数(前一个表指 explain 中的 id 值比当前表 id 值小的表,id 值越小越先执行)。
  7. 在MySQL 5.7之前,可以通过 explain extended 显示filtered字段。
  8. MySQL.5.7及更高版本,explain 默认展示 filtered 字段。
  9. Extra:展示当前查询的附加信息。常见的重要值如下:
  • Using index:表示使用覆盖索引,即 select 数据在索引信息中,不需要再查找读取其他值。
  • Using filesort:查询语句用外部排序(order by),无法使用索引排序时。若数据较小,则从内存排序;否则需要在磁盘完成排序。这种情况下一般要尝试使用索引优化。
  • Using index condition:先按照条件过滤索引,找到符合索引条件的数据行,再用 where 子句条件中其他条件过滤数据行。即索引查询的列不能完全被索引覆盖。
  • Using temporary:当前查询语句需要创建一张临时表来保存数据,如果查询中有 group byorder 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;覆盖索引一般针对的是辅助索引,整个查询结果只通过辅助索引就能拿到结果,不需要通过辅助索引树找到主键,再通过主键去主键索引树里获取其它字段值

参考