文章目录
MySQL explain介绍
MySQL提供了一个explain命令,可以用于对select语句的执行计划进行分析,并详细的输出分析结果。
通过explain命令,可以深入了解到MySQL的优化器,还可以获得很多被优化器考虑到的访问策略的细节以及运行sql语句时哪种策略预计会被优化器采用。
MySQL explain参数
1. id
select的序列号列,表示查询中操作表的顺序。
- id越大,优先级越高(先执行)
- id相同,执行顺序从上到下
- id列为null表示为结果集,不需要使用这个语句来查询
MySQL将select 查询分为简单查询和复杂查询。复杂查询分为三类:简单子查询、派生表(from语句中的子查询)、union 查询。
-
简单子查询
EXPLAIN SELECT (SELECT id FROM timed_task LIMIT 1) FROM article;
-
派生表(from语句中的子查询)
EXPLAIN SELECT * FROM (SELECT id FROM timed_task LIMIT 1) a;
-
union查询
EXPLAIN SELECT * FROM timed_task UNION ALL SELECT * FROM timed_task;
2. select_type
查询类型。主要用于区别普通查询、联合查询(union、union all)、子查询等复杂查询。
select_type取值范围:
- simple:简单查询。查询不包含子查询和union
- primary:复杂查询中最外层的select(最后执行的select)
- derived:派生查询。包含在from 子句中的子查询(MySQL会将结果存放在一个临时表中,也称为派生表)
- subquery:包含在select中的子查询(非from子句中子查询)
- dependent subquery:相比于subquery,子查询的结果受到外层的影响的为dependent subquery
- union:在union中的第二个和随后的select
- dependent union:和union一样,出现在union或者union all中,但是这个查询要受到外部查询的影响
- union result:union连接的多表查询,第一个查询是primary,后面的是union, 结果集是union result
3. table
查询的表名。
- 如果查询使用了别名,那么这里显示的就是别名
- 如果不涉及对数据表的操作,那么这里就是null
- 如果格式为<derived N>,表示这是一个临时表。N就是执行计划的id,表示结果来自这个查询
- 如果格式为<union n,m>,表示这是一个临时表。表示来自union查询id为n、m的结果集
4. partitions
查询将匹配记录的分区。 对于非分区表,该值为 NULL。
5. type
查询所使用的访问类型。表示MySQL如何查找表中的行。
type值的从优到差顺序为:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
type的值分析
-
null:mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。
例如:在索引列中选取最小值,可以单独查找索引来完成,不需要在执行时访问表
EXPLAIN SELECT MAX(id) FROM timed_task;
-
system:表中只有一行数据或者是空表(貌似MySQL5.7版本后不会有该值了)。
-
const:使用唯一索引或者主键,返回记录一定是一条的等值where条件时,通常type是const。
-
eq_ref:连接字段为主键或者唯一索引,此类型通常出现于多表的join查询,表示对于前表的每一个结果,都对应后表的唯一一条结果。并且查询的比较是=操作,查询效率比较高。
-
ref:ref的三种情况:
- 非主键或者唯一键的等值查询
- join连接字段是非主键或者唯一键
- 最左前缀索引匹配
-
ref_or_null:和ref类似,增加了null值判断
-
range:范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
-
index:辅助索引,减少回表次数,因为要查询的索引都在一颗索引树上。
-
index_merge:索引合并,表示查询使用了两个或者以上的索引数量,常见于and或者or查询匹配上了多个不同索引的字段
-
unique_subquery:子查询,返回唯一值
-
index_subquery:子查询,返回值可能有重复。
-
ALL:全表扫描,意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了
-
fulltext:全文检索索引。
6. possible_keys
可能使用的索引。
7. key
实际使用的索引。
- 可能出现 possible_keys 有列,而 key 显示 NULL 的情况,这种情况是因为表中数据不多,mysql认为索引对此查询帮助不大,选择了全表查询。
- 如果没有使用索引,则该列是 NULL。
- 当select_type为index_merge时,key列可能有多个索引,其它时候这里只会有一个。
8. key_len
索引里使用的字节数。
当索引为组合索引时,通过这个值可以算出具体使用了索引中的哪些列。
例如:组合索引index_column1_column2,其中column1和column2都为bigint(8个字节)
- 如果key_len=8,说明只是使用了索引中的column1。
- 如果key_len=16,说明只是使用了索引中的column1和column2。
MySQL数据类型的字节长度
类型 | 字节长度 |
---|---|
char(n) | 对于utf-8,如果是数字或者字母,字节长度为n;如果是汉字,字节长度为3n |
varchar(n) | 对于utf-8,如果是m个数字或者字母,字节长度为m+2;如果是m个汉字,字节长度为3m+2 (2个字节存字符串的长度) |
tinyint | 1 |
smallint | 2 |
int | 4 |
bigint | 8 |
date | 3 |
timestamp | 4 |
datetime | 8 |
特殊:如果字段允许为 NULL,需要1个字节记录是否为 NULL。
9. ref
关联的字段。如果是连接查询,则会显示关联的字段。
- 可以为Null
- 如果是使用的常数等值查询,这里会显示const
- 如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段
- 如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能会显示func
10. rows
执行计划估算的扫描行数,不一定是精确值。(innodb不是精确值,myisam是精确值,主要是因为innodb使用了mvcc)。
11. filtered
存储引擎返回的数据在server层过滤后,剩下的满足的记录数量的占比(百分比)。
12. Extra
不适合在其他列中显示但十分重要的额外信息。常见的值有:Using index、Using temporary、Using filesort、Using where、Using where、Using join buffer、impossible where、select tables optimized away、distinct
-
Using index
相应的select操作中使用了覆盖索引(covering index),避免访问了表的数据行,效率不错。- 如果同时出现using where,表明索引被用来执行索引键值的查找
- 如果没有同时出现using where,表明索引用来读取数据而非执行查找动作
覆盖索引:
查询列(包括查询条件)只用从索引中就能够取得,不必读取数据行。换句话说就是:查询列要被所建的索引覆盖,MySQL可以利用索引返回查询列,而不必根据索引再次读取数据文件。
举例说明:- 表user有一组合索引index_name_age(name、age两字段)
- 查询SQL:select name from user where age=20
该查询就使用了覆盖索引。
-
Using filesort
MySQL对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。排序的时候最好遵循所建索引的顺序与个数否则就可能会出现using filesort
-
Using temporary
MySQL使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。group by一定要遵循所建索引的顺序。
-
Using where
表明使用了where过滤。 -
Using join buffer
表明使用了连接缓存。 -
impossible where
where子句的值总是false,不能用来获取任何元组。 -
select tables optimized away
在没有GROUPBY子句的情况下,基于索引优化min/max操作,或者对于MyIsam存储引擎优化count(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。 -
Distinct
优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作。
MySQL 查询的执行流程
可参考文章:MySQL查询的执行流程