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 输出说明
- id:
select
的序列号,查询语句中有几个select
就会有多少个 id 列,一般来说id
的顺序按 select 出现的顺序增加。序号越大表示该select
的执行优先级越高,id
相同则按照列表顺序从上往下执行,若id
为NULL
,则最后执行。 - select_type:
select
的查询类型,表示对应行是简单查询还是复杂查询,该类型的值如下表。
最常见的几种类型:
- simple:简单查询,即查询语句中不包含子查询和
union
。 - primary:表示为复杂查询中最外层的
select
- union:在
union
中的第二个和随后的select
- derived:包含在
from
子句中的子查询。MySQL 会将结果存放在一个临时表中,mysql 中也称为派生表(derived table),由子查询派生出来的表。 - Subquery:子查询中的第一个
select
,其不在 from 子句中。
- Table:表示 explain 中一行正在访问的表。如果 sql 语句中定义表的别名,则显示的是表别名。
- 当
from
子句中存在子查询时,该列是derivenN
格式的,表示当前查询依赖id=N
的查询,会先执行id=N
的查询。 - 当语句中有
union
时,UNION RESULT 的 table 列的值为union1,2
,1
和2
表示参与union
的select
行id
。
- partitions:当前查询匹配表的分区,若是没有分区的表则返回
NULL
;反之则返回查询将访问的分区。 - 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:全表扫描,性能最差。
- possible_keys:当前查询可能使用到哪些索引,由于是在优化前期创建的,后续优化可能不会使用到。如果该列为
NULL
,这种情况是可能是查询表中只有少量数据,MySQL 认为全表查询比索引查询更快。 - key:当前查询中 mysql 实际使用到的索引。如果该列为
NULL
,则表明所有哦使用索引。我们可以使用force index
来强制mysql 使用possible_keys
列中的索引,或者通过ignore index
忽略possible_keys
列中的索引。 - key_len:当前查询 mysql 在索引中使用的字节数,我们可以通过判断该值的大小算出使用索引的具体列。索引是有最大长度限制的(768 字节),如果索引字符串过长时,mysql 会采用类似左前缀索引的方式进行处理,将索引字符串的前半部分字符摘出来作为索引。
- ref:表示key 列记录的索引中,表查找值所用到的字段或常量。常见的有:const(常量),字段名(例:film.id)
- rows:MySQL 估算读取需要检测的行数,理论上数值越小越好,但实际情况不一定,调优时以实际情况为准。注意该值并不是结果集的行数。
- filtered:表示符合查询条件数据的百分比,可以使用
rows * filtered/100
估算出将要和explain
中前一个表连接的行数(前一个表指explain
中的id
值比当前表id
值小的表,id
值越小越先执行)。 - 在MySQL 5.7之前,可以通过
explain extended
显示filtered
字段。 - MySQL.5.7及更高版本,
explain
默认展示filtered
字段。 - 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 计算规则如下:
- 字符串 :分别是
char(n)
和varchar(n)
,在 mysql5.0.3 以后版本中,n
均代表字符数,而不是字节数。在 utf-8 编码格式下,一个数字或字母占 1 个字节,一个汉字占 3个字节。这两种占用字节分别计算如下: char(n)
:若是汉字,则长度就是3n
字节;数字或字母则是n
。varchar(n)
:若是汉字,则长度是3n + 2
字节, 2 字节表示存储字符串的长度,因为varchar
是变长字符串。数字或字母则是n+2
。- 数值类型
Tinyint
:1 字节Smallint
:2 字节Int
:4 字节Bigint
:8 字节- 时间类型 1. Date:3 字节 2. Timestamp:4 字节 3. Datetime:8 字节
- 如果字段允许为
NULL
,需要 1 字节记录是否为NULL
覆盖索引定义:mysql 执行计划 explain 结果里的 key 有使用索引,如果 select 后面查询的字段都可以从这个索引的树中获取,这种情况一般可以说是用到了覆盖索引,extra 里一般都有 using index;覆盖索引一般针对的是辅助索引,整个查询结果只通过辅助索引就能拿到结果,不需要通过辅助索引树找到主键,再通过主键去主键索引树里获取其它字段值