目录
以下表格可用于在阅读理解完本文在未来用于快速回顾知识点,初次了解explain请先忽略此表格
id | select_type | table | type | Possible _keys | key | key_len | ref | rows | Extra |
1 | SIMPLE | 使 | system | 可能使用的索引 | 实际 | 表示查询优化 | 显示 | 所需读取 | 其他额外 |
2 | PRIMARY | 用 | const | 使 | 器使用 | 索引 | 的可 | 信息 | |
3 | SUBQUERY | 的 | eq_ref | 用 | 了的索引的字 | 的哪一列 | 能的行数 | Using filesort | |
4 | DERIVED | 表 | ref | 的 | 节数 | 被使 | Using temporary | ||
越大 | UNION | fulltext | 索 | 用了 | Using index | ||||
越先 | UNION RESULT | …… | 引 | 如果可能 | Using where | ||||
执行 | range | 是一 | Using join buffer | ||||||
index | 个常 | Impossible where | |||||||
ALL | 量 | …. | |||||||
const |
id
select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
1、id相同:执行顺序由上至下
2、id不同:如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
3、id相同又不同(两种情况同时存在):id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行
select_type
查询的类型,主要是用于区分普通查询、联合查询、子查询等复杂的查询
1、SIMPLE:简单的select查询,查询中不包含子查询或者union
2、PRIMARY:查询中包含任何复杂的子部分,最外层查询则被标记为primary
3、SUBQUERY:在select 或 where列表中包含的子查询
4、DERIVED:在from列表中包含的子查询被标记为derived(衍生),mysql或递归执行这些子查询,把结果放在临时表里
5、UNION:若第二个select出现在union之后,则被标记为union;若union包含在from子句的子查询中,外层select将被标记为derived
6、UNION RESULT:从union表获取结果的select
Type
访问类型,sql查询优化中一个很重要的指标,结果值从好到坏依次是:
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>
index_subquery>range>index>ALL,一般来说,好的sql查询至少达到range级别,最好能达到ref
1、system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,可以忽略不计
2、const:表示通过索引一次就找到了,const用于比较primary key 或者 unique索引。因为只需匹配一行数据,所以很快。如果将主键置于where列表中,mysql就能将该查询转换为一个const
3、eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
4、ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质也是一种索引访问,它返回所有匹配某个单独值的行,因为他可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体
5、range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点,结束于另一个点,不用扫描全部索引
6、index:Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常比ALL快,因为索引文件通常比数据文件小。(Index与ALL虽然都是读全表,但index是从索引中读取,而ALL是从硬盘读取)
7、ALL:Full Table Scan,遍历全表以找到匹配的行
possible_keys
查询涉及到的字段上存在索引,则该索引将被列出,但不一定被查询实际使用
key
实际使用的索引,如果为NULL,则没有使用索引。
查询中如果使用了覆盖索引,则该索引仅出现在key列表中
覆盖索引:如果一个索引包含(或覆盖)所有需要查询的字段的值,称为‘覆盖索引’。即只需扫描索引而无须回表。
key_len
表示索引中使用的字节数,查询中使用的索引的长度(最大可能长度),并非实际使用长度,理论上长度越短越好。key_len是根据表定义计算而得的,不是通过表内检索出的
对于单列索引,比如字符集编码格式为utf8(占用三个字节),这时如果索引定义是char(20),那么key_len=60
对于组合索引,其情况如上,为两个索引定义 × 字符集占用字节数相加,其中,如果有索引定义为默认为null,那么其key_len要+1来标识null,即key_len=20 * 3 + (20 * 3 +1)=121
计算公式解释1:key_len=字符数*字符集每个字符占用字节+其他字节
1、所有的索引字段,如果没有设置not null,则需要加一个字节。
2、定长字段与变长字段
类型占用 | 字节/字符 | 类型占用 | 字节/字符 |
char(n) | n 字符 | BIGINT | 8字节 |
varchar(n) | n 字符 + 2 | DATE | 3字节 |
TINYINT | 1字节 | TIMESTAMP | 4字节 |
SMALLINT | 2字节 | DATETIME | 8字节 |
MEDIUMINT | 3字节 | null | 1字节 |
INT | 4字节 | Not null | 0字节 |
3、不同的字符集,一个字符占用的字节数不同。
使用的字符集 | 一个字符占用的字符数 |
latin1 | 1 |
gbk | 2 |
utf8 | 3 |
utf8mb4 | 4 |
计算公式解释2:
1、varchar(10)变长字段且允许NULL,key_len=10*(character set:utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
2、varchr(10)变长字段且不允许NULL=10 *(character set:utf8=3,gbk=2,latin1=1)+2(变长字段)
3、char(10)固定字段且允许NULL=10*(character set:utf8=3,gbk=2,latin1=1)+1(NULL)
4、char(10)固定字段且不允许NULL=10*( character set:utf8=3,gbk=2,latin1=1)
ref
显示索引的哪一列被使用了,如果可能,是一个常量const。
rows
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
Extra
不适合在其他字段中显示,但是十分重要的额外信息
1、Using filesort:很慢,必须优化
mysql对数据使用一个外部的索引排序,而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作称为“文件排序”
由于索引是先按email排序、再按address排序,所以查询时如果直接按address排序,索引就不能满足要求了,mysql内部必须再实现一次“文件排序”,常见于Order By
2、Using temporary:查询效率不高,建议优化
使用临时表保存中间结果,也就是说mysql在对查询结果排序时使用了临时表,常见于group by
3、Using index:效率不错
表示相应的select操作中使用了覆盖索引(Covering Index),避免了访问表的数据行,效率高。如果同时出现Using where,表明索引被用来执行索引键值的查找(参考上图)。
如果没用同时出现Using where,表明索引用来读取数据而非执行查找动作
4、Using where :使用了where过滤
5、Using join buffer :使用了链接缓存
6、Impossible WHERE:where子句的值总是false,不能用来获取任何元祖
7、select tables optimized away:
在没有group by子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段在进行计算,查询执行计划生成的阶段即可完成优化
8、distinct:优化distinct操作,在找到第一个匹配的元素后即停忽略下次再次找到的一样的值
参考链接:
https://blog.csdn.net/wuseyukui/article/details/71512793