ySQL EXPLAIN 简单了解
帮朋友解决了一个查询问题(查询报错The table 'C:\Windows\TEMP#sqlxxxxxx' is full)。试过增加tmp_table_size和max_heap_table_size的大小，然并卵；用SQL_BIG_RESULT强制跑，结果把C盘100多G的空间干满了，No space left on device；最后用了EXPLAIN分析SQL语句的执行计划信息，来去除临时表，然后问题解决。了解到了以前不了解的知识，比如最左(前缀)匹配原则。为了以后不再踩坑，这里简单写一下EXPLAIN的信息以备后用。
EXPLAIN简介
EXPLAIN
可以获取SQL语句的执行计划信息，可用于SELECT
，DELETE
，INSERT
，REPLACE
，和UPDATE
语句，用来分析优化SQL以及表结构。执行（EXPLAIN
+ SQL语句）效果如下：
EXPLAIN列信息
id SELECT的标识符，按照SELECT出现顺序增长，UNION查询id为NULL。
select_type SELECT的类型
| 值 | 意义 |
|---|---|
| SIMPLE | 简单查询（不使用UNION和子查询） |
| PRIMARY | 复杂查询最外层的SELECT |
| UNION | UNION中第二个或以后的SELECT |
| DEPENDENT UNION | UNION中第二个或以后的SELECT（依赖于外部查询） |
| UNION RESULT | UNION的结果 |
| SUBQUERY | 子查询第一个SELECT |
| DEPENDENT SUBQUERY | 子查询第一个SELECT（依赖于外部查询） |
| DERIVED | 派生表 |
| MATERIALIZED | 物化子查询 |
| UNCACHEABLE SUBQUERY | 子查询无法被物化，每次都需要重新评估 |
| UNCACHEABLE UNION | UNION中第二个或以后的无法被物化的SELECT（类似UNCACHEABLE SUBQUERY） |
table 输出行所引用表名，或者以下值：
| 值 | 意义 |
|---|---|
| <unionM,N> | id为M和N行的并集 |
| <derivedN> | id为N行的派生表结果 |
| <subqueryN> | id为N行的物化子查询结果 |
partitions 匹配记录所在的分区（非分区表该值为NULL）
type 连接类型，效率从高到低排列为：
| 值 | 意义 |
|---|---|
| system | 表只有一行（类似系统表，const连接类型的一个特例） |
| const | 该表最多有一个匹配行，该列的值可以被优化器的其余部分视为常量 |
| eq_ref | 从该表中读取一行与前一张表的所有行组合（使用主键索引或唯一非空索引） |
| ref | 从该表中读取具有匹配索引值的所有行与前一张表的所有行的组合（不使用唯一索引，使用普通索引或唯一性索引的部分前缀） |
| fulltext | 使用全文索引 |
| ref_or_null | 类似ref，但是需要额外搜索NULL |
| index_merge | 使用索引合并的优化方法 |
| unique_subquery | 替代了 value IN (SELECT primary_key FROM single_table WHERE some_expr) 形式的eq_ref一些IN子查询 |
| index_subquery | 类似unique_subquery，替代了IN子查询，适用于 value IN (SELECT key_column FROM single_table WHERE some_expr) 形式的子查询中的非唯一索引 |
| range | 使用索引来检索给定范围的行 |
| index | 与ALL一样，但是扫描索引树（全索引扫描） |
| ALL | 全表扫描 |
possible_keys 该表中可以使用哪个索引去查找行
key 该表中实际使用索引去查找行
key_len 该表中实际使用索引长度（可以推断使用组合索引中的多少）
ref key列记录的索引中，表查找值所用到的列，常量（const）或函数（func）
rows 读取并检测的行数
filtered 按表条件过滤的行的估计百分比，最大值为100（没有发生行过滤）
Extra 附加信息，比较常见重要的如下
| 值 | 意义 |
|---|---|
| Using index | 使用索引树中的信息从表中检索列信息 |
| Using where | 使用WHERE限制返回的行 |
| Using temporary | 需要创建一个临时表来保存结果（比如GROUP BY和ORDER BY会出现，需要优化，一般用添加索引来优化） |
| Using filesort | 对结果使用一个外部索引排序，而不是按索引次序从表里读取行（需要优化，一般用索引来优化） |
文章转载自糸色望TestBeta，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
