mysql深入-关于explain解释工具分析SQL语句

用法：explain + sql语句,不出意外，会展示以下几列：

ID	select_type	table	type	partitions	possible_keys	key	key_len	ref	rows	filtered	extra

其中标识的4个是我们分析sql是否合格的重要标志

ID (执行标识符)

越大越先执行。相同可以认为是同一组别，执行从上到下依次执行

select_type (sql的执行类型)

1. simple - 简单的select，没有子查询和union查询的情况
2. primary- 为最外层的select
3. union - union中第二个以及之后的select(union中带有子查询)
4. subquery - 第一个子查询的select（子查询中带有子查询）
5. dependend union union中第二个以及之后的select,取决于外部查询的结果
6. dependend subquery 第一个子查询的select，取决于外部查询的结果
7. union result: union查询结果

dependend-通常表示使用了相关的子查询

table (查询相关表)

type (sql执行的方式)

null > system > const > eq_ref > ref > range > index > all （官方文档中还有 fulltext> ref_or_null>index_merge>unique_subquery>index_subquery等等，位于ref与range中间）

1. null 完全不需要去查询索引和表就可以得到结果
2. system 表中只有一个结果(const的特殊)
3. const 主键或者唯一索引的等值查询，只有一条记录会命中
4. eq_ref 连接查询时，以主键或唯一索引作为连接条件的查询，一次查询的结果总是唯一对应(高效)
5. ref 连接查询时，索引访问，以非主键索引或者非唯一索引为连接条件的查询（索引对应着记录可能是多条记录，）
6. range 在一定范围内遍历索引的查询，一般会出现在条件中带有“>,<,>=,<=,in,between,like”等范围操作（一般都要在这个以上，优化结果就是可以接受的）
7. index 扫描全索引进行查询
8. all 扫描全表（要想办法避免出现）

partitions(定位扫描的表分区)

possible keys(列出可能用到的索引)

key (正式采用的索引)

ref (查询中与key比较的列或者常量)

展示哪些列或者常量与key中索引进行了比较

key_len(采用索引的数据数据长度)

key_len 采用索引的长度，可以用于判定联合索引的使用情况，使用最左前缀原则计算即可
常见的计算情况：
char(n) 长度n
varchar(n) 按照编码，utf8--3n+2； utf8mb4--4n+2
tinyint 1
smallint 2
middleint 3
int  4
bigint  8
date 3
timestamp 4
datetime 8

如果字段的值没有定义not null 长度+1

rows (预计会查找的行数)

一般情况越少越好

filtered(过滤数据的百分比)

extra (额外重要信息)

这里会解释sql实现数据查找的具体执行方式，常见的有：

1. using index: 使用了覆盖索引，可以从索引中得到想要的数据，不用去回表查询
2. using where：作用于where子句，用于限制传递的数据行数（返回给用户或者与其他表匹配）
3. using temporary：（建议优化）使用了中间表数据，一般会出现在order by
4. using filesort : （额外消耗cpu资源，建议优化）常见于order by，不能使用索引排序, 需要额外排序操作（不一定是文件排序），可以考虑优化可以使用索引，或者添加对应索引来消除
5. using index condition: 一般读取行字段较多，无法从索引中获取全部数据列的情况下，会测试索引元祖来确定是否需要读取整行数据来读取表(延迟下推读取整行优化-index condition pushdown optimization)，可以去除不必要的回表读取IO消耗 参考
6. impossible where: where子句总是返回false

参考文档链接

1. MYSQL官方文档资料库(最权威，非常推荐，值得一读)
2. explain – 查询分析学习(简书, 分析切合实际，值得一读)

总结

1. 我使用“康奈尔笔记法”阅读最初的文档，尝试着抓住重点，然后整理线索，最后总结，开始写此篇文档，写文档的过程中，你会发现有不少不明确的地方，之后，再去查资料，再重复以上的片段，慢慢的知识就会牢记在脑中，推荐使用
2. explain是个很好的工具，可以帮助我们分析sql语句是否合理，是否需要增加或者修改对应的索引，从而提高查询效率