MySQL中Explain作用详解
前言：Explain（执行计划），使用explain关键字可以模拟优化器执行sql查询语句，从而知道MySQL是如何处理sql语句。
explain主要用于分析查询语句或表结构的性能瓶颈。
一、Explain的作用（通过Explain+sql语句可以知道如下内容）
- 表的读取顺序。（对应id）
- 数据读取操作的操作类型。（对应select_type）
- 哪些索引可以使用。（对应possible_keys）
- 哪些索引被实际使用。（对应key）
- 表直接的引用。（对应ref）
- 每张表有多少行被优化器查询。（对应rows）
二、Explain包含的信息
Explain使用：explain+sql语句，通过执行explain可以获得sql语句执行的相关信息。
mysql> EXPLAIN -> SELECT * FROM t_custom t1 -> LEFT JOIN t_custom_acounts t2 ON t1.custom_id = t2.custom_id -> LEFT JOIN t_ext_sign t3 ON t2.custom_id = t3.custom_id -> WHERE t1.custom_id = '02241EE988CD4955EF31DAC397844D77'; +----+-------------+-------+------------+-------+---------------+---------------+---------+-------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------------+---------+-------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | t1 | NULL | const | PRIMARY | PRIMARY | 386 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | ref | idx_custom_id | idx_custom_id | 386 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+-------+---------------+---------------+---------+-------+------+----------+----------------------------------------------------+ 3 rows in set, 1 warning (0.00 sec)
下面对Explain的表头字段含义进行解释:
1）id # select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，该字段通常与table字段搭配来分析。
#id相同，执行顺序从上到下，搭配table列进行观察可知，执行顺序为t1->t3->t2
mysql> EXPLAIN -> SELECT * FROM t_custom t1,t_custom_acounts t2,t_ext_sign t3 -> WHERE t1.custom_id = t2.custom_id -> AND t1.custom_id = t3.custom_id -> AND t1.custom_id = '02241EE988CD4955EF31DAC397844D77'; +----+-------------+-------+------------+-------+---------------+---------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | t1 | NULL | const | PRIMARY | PRIMARY | 386 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 12.50 | Using where | | 1 | SIMPLE | t2 | NULL | ref | idx_custom_id | idx_custom_id | 386 | const | 1 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+---------------+---------+-------+------+----------+-----------------------+ 3 rows in set, 1 warning (0.00 sec)
#id不同，如果是子查询，id的序号会递增，id值越大执行优先级越高，搭配table列可知，执行顺序为t3->t1->t2。
mysql> EXPLAIN -> SELECT t1.* FROM t_custom t1 -> WHERE t1.custom_id = (SELECT t2.custom_id FROM t_custom_acounts t2 -> WHERE t2.custom_id = (SELECT t3.custom_id FROM t_ext_sign t3 WHERE t3.custom_id = '02241EE988CD4955EF31DAC397844D77')); +----+-------------+-------+------------+-------+---------------+---------------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------------+---------+-------+------+----------+--------------------------+ | 1 | PRIMARY | t1 | NULL | const | PRIMARY | PRIMARY | 386 | const | 1 | 100.00 | NULL | | 2 | SUBQUERY | t2 | NULL | ref | idx_custom_id | idx_custom_id | 386 | const | 1 | 100.00 | Using where; Using index | | 3 | SUBQUERY | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 12.50 | Using where | +----+-------------+-------+------------+-------+---------------+---------------+---------+-------+------+----------+--------------------------+ 3 rows in set, 1 warning (0.00 sec)
#id相同不同，同时存在，在所有组中，id值越大执行优先级越高。
mysql> EXPLAIN -> SELECT t.* FROM ( -> SELECT t1.custom_id -> FROM t_custom_acounts t1 -> WHERE t1.custom_id = '02241EE988CD4955EF31DAC397844D77') s1,t_custom t -> WHERE s1.custom_id = t.custom_id; +----+-------------+-------+------------+-------+---------------+---------------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | t | NULL | const | PRIMARY | PRIMARY | 386 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | t1 | NULL | ref | idx_custom_id | idx_custom_id | 386 | const | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+---------------+---------+-------+------+----------+--------------------------+ 2 rows in set, 1 warning (0.00 sec)
总结：id的值表示select子句或表的执行顺序，id相同，执行顺序从上到下，id不同，值越大的执行优先级越高。
2）select_type #查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。其值主要有六个
- SIMPLE #简单的select查询，查询中不包含子查询或union查询。
- PRIMARY #查询中若包含任何复杂的子部分，最外层查询为 PRIMARY，也就是最后加载的就是PRIMARY。
- SUBQUERY #在select或where列表中包含了子查询，就为被标记为SUBQUERY。
- DERIVED #在from列表中包含的子查询会被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，将结果放在临时表中。
- UNION #若第二个select出现在union后，则被标记为UNION，若union包含在from子句的子查询中，外层select将被标记为DERIVED。
- UNION RESULT #从union表获取结果的select。
3）table #显示sql操作属于哪张表的
4）partitions #官方定义为The matching partitions（匹配的分区），值为NULL表示表未被分区。
5）type #表示查询所使用的访问类型。
type的值主要有八种，该值表示查询的sql语句好坏，从最好到最差依次为：system>const>eq_ref>ref>range>index>ALL
6）possible_keys #显示可能应用在表中的索引，可能一个或多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。
7）key #实际中使用的索引，如为NULL，则表示未使用索引。若查询中使用了覆盖索引，则该索引和查询的select字段重叠。
mysql> EXPLAIN SELECT custom_id FROM t_custom; +----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_custom | NULL | index | NULL | cid | 386 | NULL | 8 | 100.00 | Using index | +----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
简单理解：possible_keys表示理论上可能用到的索引，key表示实际中使用的索引。
possible_keys为NULL表示可能未用到索引，但 key=cid 表示在实际查询的过程中进行了索引的全扫描。
8）key_len #表示索引中所使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。
注：在使用索引查询时，当条件越精确，key_len的长度可能会越长，所以在不影响结果的情况下，key_len的值越短越好。
9）ref #显示关联的字段。如果使用常数等值查询，则显示const，如果是连接查询，则会显示关联的字段。
mysql> EXPLAIN -> SELECT t.*,t1.* FROM t_custom t ,t_custom_acounts t1 -> WHERE t.custom_id = t1.custom_id -> AND t.custom_id = '02241EE988CD4955EF31DAC397844D77'; +----+-------------+-------+------------+-------+---------------+---------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | t | NULL | const | PRIMARY,cid | PRIMARY | 386 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | t1 | NULL | ref | idx_custom_id | idx_custom_id | 386 | const | 1 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+---------------+---------+-------+------+----------+-----------------------+ 2 rows in set, 1 warning (0.00 sec)
#由于id相同，因此从上到下执行
t_custom 表为非唯一性索引扫描，实际使用的索引列为PRIMARY，
由于t.custom_id = '02241EE988CD4955EF31DAC397844D77'为一个常量，所以ref=const。
10）rows #根据表统计信息及索引选用情况大致估算出找到所需记录所要读取的行数。当然该值越小越好。
11）filtered #百分比值，表示存储引擎返回的数据经过滤后，剩下多少满足查询条件记录数量的比例。
12）Extra #显示十分重要的额外信息。其取值有以下几个：
#Using filesort
Using filesort表明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
mysql中无法利用索引完成的排序操作称为“文件排序”。
出现Using filesort就非常危险了，在数据量非常大的时候几乎“九死一生”。出现Using filesort尽快优化sql语句。
deptname字段未建索引的情况。
mysql> EXPLAIN -> SELECT * FROM t_custom -> ORDER BY nick_name; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | t_custom | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using filesort | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.00 sec)
#Using temporary
使用了临时表保存中间结果，常见于排序order by和分组查询group by。非常危险，“十死无生”，急需优化。
将tb_emp中name的索引先删除，出现如下图结果，非常烂，Using filesort和Using temporary，“十死无生”。
mysql> EXPLAIN -> SELECT acount_type FROM t_custom -> GROUP BY acount_type; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | 1 | SIMPLE | t_custom | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using temporary; Using filesort | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ 1 row in set, 1 warning (0.00 sec)
#Using index
表明相应的select操作中使用了覆盖索引，避免访问表的额外数据行，效率不错。
如果同时出现了Using where，表明索引被用来执行索引键值的查找。（where custom_id=1）
如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。
删除t_custom表中nick_name和custom_id字段的单独索引，创建复合索引。
从这里给出覆盖索引的定义：select的数据列只从索引中就能取得数据，不必读取数据行。
通过上面的例子理解：创建了（nick_name，custom_id）的复合索引，
查询的时候也使用复合索引或部分，这就形成了覆盖索引。
简记：查询使用复合索引，并且查询的列就是索引列，不能多，个数需对应。
使用优先级Using index>Using filesort（九死一生）>Using temporary（十死无生）。
也就说出现后面两项表明sql语句是非常烂的，急需优化！
总结：
explain（执行计划）包含的信息十分的丰富，着重关注以下几个字段信息。
- id，select子句或表执行顺序，id相同，从上到下执行，id不同，id值越大，执行优先级越高。
- type，type取值表示sql的好坏程度（由好到差排序）：system>const>eq_ref>ref>range>index>ALL。
- key，实际被使用的索引列。
- ref，关联的字段，常量等值查询，显示为const，如果为连接查询，显示关联的字段。
- Extra，额外信息，使用优先级Using index>Using filesort（九死一生）>Using temporary（十死无生）。
着重关注上述五个字段信息，对日常生产过程中调优十分有用。
原文引用： mysql 用法 Explain
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 使用C#创建一个MCP客户端
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· ollama系列1：轻松3步本地部署deepseek，普通电脑可用
· 按钮权限的设计及实现