SQL优化--Explain
Explain 输出详解
Explain Output Columns
| Column | JSON Name | Meaning | eg |
|---|---|---|---|
id | select_id | 每个select子句的标识id | |
select_type | None | select语句的类型 | |
table | table_name | 当前表名 | |
partitions | partitions | 匹配的分区 | |
type | access_type | 当前表内访问方式 join type | |
possible_keys | possible_keys | 可能使用到的索引 | |
key | key | 经过优化器评估最终使用的索引 | |
key_len | key_length | The length of the chosen key | |
ref | ref | 根据索引应用到的列（关联外表） | |
rows | rows | Estimate of rows to be examined，估计真实扫描到的行数 | |
filtered | filtered | 根据条件过滤掉的百分比 | |
Extra | None | 额外说明 |
Each Explain Column Detail
1.id
SELECT的序号，查询序号是SQL执行语句的顺序。
2.select_type
SELECT类型有
select_type Value | JSON Name | Meaning |
|---|---|---|
SIMPLE | None | 简单查询（无UNION和子查询） |
PRIMARY | None | 最外层的SELECT |
UNION | None | UNION操作中，查询中处于内层的SELECT，且与外层SELECT独立 |
DEPENDENT UNION | dependent (true) | UNION操作中，查询中处于内层的SELECT，且与外层SELECT依赖 |
UNION RESULT | union_result | UNION的结果，对应ID为NULL |
SUBQUERY | None | 子查询的第一个SELECT |
DEPENDENT SUBQUERY | dependent (true) | 子查询中首个SELECT，但依赖于外层的表（如果有多个子查询存在） |
DERIVED | None | Derived table |
DEPENDENT DERIVED | dependent (true) | Derived table，依赖另外的表 |
MATERIALIZED | materialized_from_subquery | Materialized 子查询 |
UNCACHEABLE SUBQUERY | cacheable (false) | 无法缓存的子查询 |
UNCACHEABLE UNION | cacheable (false) | Union操作中 ，无法缓存的子查询 |
视图（View） ：虚拟的数据库关系，视图不是数据库系统存储的真实逻辑关系，每次视图访问的时候都会实时生成。
物化视图（Materialized Views）：当视图的结果存储在数据库系统中时，就叫做物化视图，物化视图无法用SQL进行定义，而是由数据库软件管理使用。
3.table
当前表名
4.partitions
匹配的分区
5.type
当前表内访问方式，性能由好到坏排序：
| id | type value | Meaning |
|---|---|---|
| 1 | system | 表中只有一行，const 的一种特殊情况 |
| 2 | const | 单表中最多有一个匹配行，primary key 或者 unique index的检索 |
| 3 | eq_ref | 多表连接中被驱动表的连接列上有primary key或者unique index的检索 |
| 4 | ref | 与eq_ref类似，但不是使用primary key或者unique index，而是普通索引，或者是索引的左前缀序列。简言之，就是无法根据索引值确定单一行。 |
| 5 | fulltext | 使用FULLTEXT索引执行连接 |
| 6 | ref_or_null | 与ref类似，区别在于条件中包含对NULL的查询 |
| 7 | index_merge | 索引合并优化，利用一个表里的N个索引查询,key_len表示这些索引键的和最长长度。 |
| 8 | unique_subquery | in的后面是一个查询primary key\unique字段的子查询 |
| 9 | index_subquery | in的后面是一个查询普通index字段的子查询 |
| 10 | range | 单表索引中的范围查询,使用索引查询出单个表中的一些行数据。ref列会变为null |
| 11 | index | 等于ALL，只是在遍历时使用了索引树。它有两种情况：(1)覆盖索引 (2)用索引的顺序做一个全表扫描。 |
| 12 | all | 全表扫描 |
6.possible_keys
提示可以使用的索引
7.key
已经使用的索引
8.key_len
索引长度
9.ref
关联到的外部表的列
10.rows
预估扫描的行数
11.filtered
过滤 掉行的百分比
12.Extra
Extra是对执行计划的额外说明，包含重要信息。由于过多，下面举出重要的优化项
Using filesort
使用filesort来进行order by操作。
Order by 排序操作有两种方式
- 使用索引：借助B-Tree进行快速排序
- 使用filesort：外部排序，基于磁盘与内存进行排序，效率低
优化思路：建立索引
Using temporary
使用了临时表去储存中间结果，通常在Group by 和 order by 语句中常见。属于重点优化项。
Using where
使用了Where进行过滤，没有使用索引
Using Index
查询使用了覆盖索引，不用回表，查询效率非常高。
相关的有
- Using index condition：
- Using index for group-by：在Group By 中使用了索引，不用去访问表中查数据
- Using index for skip scan：使用了Skip Scan 策略
Using join buffer
表明使用 了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
其他
| type value | Meaning | Suggestion |
|---|---|---|
| const row not found | 所要查询的表为空 | SQL可能有错误 |
| Distinct | mysql正在查询distinct值，因此当它每查到一个distinct值之后就会停止当前组的搜索，去查询下一个值 | 优化策略 |
| Impossible HAVING | Having条件总为false， | SQL可能有错误 |
| Impossible WHERE | where条件总为false | SQL可能有错误 |
| Impossible WHERE noticed after reading const tables | 在优化器评估了const表之后，发现where条件均不满足 | SQL可能有错误 |
| Not exists | 优化器发现内表记录不可能满足where条件 | |
| Select tables optimized away | 对索引进行MIN，MAX操作时进行优化 | 优化策略 |