MySQL EXPLAIN 简单了解

MySQL EXPLAIN 简单了解

八月 13, 2021 阅读量

帮朋友解决了一个查询问题(查询报错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语句的执行计划信息,可用于SELECTDELETEINSERTREPLACE,和UPDATE语句,用来分析优化SQL以及表结构。执行(EXPLAIN + SQL语句)效果如下:

1
2
3
4
5
6
7
mysql> explain select 1 from dual;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

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 对结果使用一个外部索引排序,而不是按索引次序从表里读取行(需要优化,一般用索引来优化)