MySQL EXPLAIN执行计划：SQL性能翻倍的秘密武器

天才不需求证 發表於 2025-11-17 10:19:00

MySQL EXPLAIN执行计划：SQL性能翻倍的秘密武器

<blockquote>
MySQL系列文章
在数据库性能优化领域，Explain执行计划是MySQL开发者与DBA必须掌握的利器。它揭示了SQL语句的执行路径、索引使用情况及资源消耗模型，是诊断慢查询和优化索引策略的核心工具。本文将全方位解析Explain的机制与实践技巧，助你彻底掌握SQL性能调优。
</blockquote>
<h3 id="一explain工具概述">一、Explain工具概述</h3>
Explain是MySQL提供的SQL分析指令，通过在SELECT前添加<code>EXPLAIN</code>关键字（或<code>EXPLAIN FORMAT=JSON</code>获取详细报告），可模拟优化器生成执行计划而不实际执行查询。其核心价值在于：
<ol>
<li>执行路径可视化：展示表的读取顺序、访问方法及连接方式</li>
<li>索引有效性分析：揭示可能使用与实际使用的索引</li>
<li>资源消耗预估：通过扫描行数和过滤比例预判性能瓶颈</li>
<li>执行策略诊断：识别全表扫描、临时表、文件排序等危险操作</li>
</ol>
<hr>
<h3 id="二explain-12大核心字段详解">二、Explain 12大核心字段详解</h3>
执行计划包含12个关键字段，每个字段都承载着优化器决策的关键信息：
<table>
<thead>
<tr>
<th>字段</th>
<th>说明</th>
<th>优化意义</th>
</tr>
</thead>
<tbody>
<tr>
<td>id</td>
<td>查询序列号，相同id按顺序执行，不同id值越大优先级越高</td>
<td>定位复杂查询执行顺序，识别子查询层级</td>
</tr>
<tr>
<td>select_type</td>
<td>查询类型</td>
<td>识别简单查询或复杂子查询结构</td>
</tr>
<tr>
<td>table</td>
<td>访问的表名</td>
<td>确定查询涉及的表对象，含别名和<unionM,N>等特殊标记</td>
</tr>
<tr>
<td>partitions</td>
<td>匹配的分区</td>
<td>分区表查询时显示命中的分区名</td>
</tr>
<tr>
<td>type（关键）</td>
<td>访问类型，性能排序：<code>system > const > eq_ref > ref > range > index > ALL</code></td>
<td>SQL优化的核心指标，决定数据检索效率</td>
</tr>
<tr>
<td>possible_keys</td>
<td>可能使用的索引</td>
<td>检查索引设计是否合理</td>
</tr>
<tr>
<td>key（关键）</td>
<td>实际使用的索引</td>
<td>验证优化器最终选择的索引</td>
</tr>
<tr>
<td>key_len(关键)</td>
<td>索引使用的字节数</td>
<td>计算复合索引中使用到的字段长度，验证索引利用率</td>
</tr>
<tr>
<td>ref</td>
<td>索引关联的列或常量</td>
<td>显示与索引比较的列或常量，检查关联条件</td>
</tr>
<tr>
<td>rows（关键）</td>
<td>预估扫描行数</td>
<td>数值越小性能越好，大数值需优化</td>
</tr>
<tr>
<td>filtered</td>
<td>存储引擎层过滤后的剩余比例</td>
<td>查询效率核心指标，100%表示完美过滤</td>
</tr>
<tr>
<td>Extra（关键）</td>
<td>额外执行信息</td>
<td>揭示潜在性能问题（如临时表/文件排序）</td>
</tr>
</tbody>
</table>
<hr>
<h3 id="三重点字段深度解析">三、重点字段深度解析</h3>
<h4 id="1-type访问类型性能核心指标">1. type访问类型（性能核心指标）</h4>
<ul>
<li>
system：这是 <code>const</code> 类型的一个特例。当查询的表只有一行记录（系统表）时会出现。
</li>
<li>
const：通过主键 (Primary Key)  或唯一索引 (Unique Index)  进行等值查询时，最多只返回一条记录。
<pre><code class="language-sql">EXPLAIN SELECT * FROM users WHERE id = 1;-- type：const类型
EXPLAIN SELECT * FROM users WHERE id IN(1,2);-- type：range类型
</code></pre>
</li>
</ul>
<blockquote>
注意⚠️：
<ul>
<li>条件为id IN(1)这种单值查询type也是const类型。</li>
<li>假如id = 1这行数据在表中不存在时，Extra中会有提示信息：“no matching row in const table”。</li>
</ul>
</blockquote>
<ul>
<li>
eq_ref：在连接查询 (<code>JOIN</code>) 时，对于前表的每一行，从本表中只读取一行。通常发生在使用主键或唯一索引作为连接条件的场景。
<pre><code class="language-sql">EXPLAIN SELECT * FROM orders
JOIN users ON orders.user_id = users.id;
-- 假设 users.id 是主键，orders.user_id 是外键
-- 对于 orders 表中的每一行，通过 users.id 主键只能找到唯一的一条 users 记录
</code></pre>
</li>
<li>
ref：使用普通索引 (Non-Unique Index)  进行等值查询，可能会返回多条匹配的记录。
<pre><code class="language-sql">EXPLAIN SELECT * FROM orders WHERE user_id=100; -- 常见索引查询
</code></pre>
</li>
<li>
range：使用索引检索给定范围的行。关键是在 <code>WHERE</code> 子句中出现了范围查询。
</li>
</ul>
<blockquote>
常见操作符：<code>=</code>, <code><></code>, <code>></code>, <code>>=</code>, <code><</code>, <code><=</code>, <code>IS NULL</code> 
, <code>BETWEEN</code>, <code>IN()</code>, <code>LIKE ‘prefix%’</code>（注意是前缀匹配）。
</blockquote>
<ul>
<li>
index：全索引扫描 (Full Index Scan) ，MySQL会遍历整个索引树来查找数据。
</li>
<li>
ALL：全表扫描 (Full Table Scan) ，MySQL会读取表中的每一行来找到匹配的行。（必须优化的红色警报）
</li>
</ul>
<h4 id="2-extra关键信息解读">2. Extra关键信息解读</h4>
<table>
<thead>
<tr>
<th>值</th>
<th>含义</th>
<th>优化建议</th>
</tr>
</thead>
<tbody>
<tr>
<td>Using filesort</td>
<td>额外文件排序（无法使用索引有序性）</td>
<td>为ORDER BY字段添加复合索引</td>
</tr>
<tr>
<td>Using temporary</td>
<td>使用临时表存储中间结果</td>
<td>优化GROUP BY/子查询</td>
</tr>
<tr>
<td>Using index</td>
<td>覆盖索引（无需回表）</td>
<td>理想状态，保持</td>
</tr>
<tr>
<td>Using where</td>
<td>存储引擎返回行后再次过滤</td>
<td>检查索引覆盖性或查询条件</td>
</tr>
<tr>
<td>Using index condition</td>
<td>索引条件下推（ICP特性）</td>
<td>MySQL5.6+优化特性</td>
</tr>
<tr>
<td>Using join buffer</td>
<td>使用连接缓冲区</td>
<td>增大join_buffer_size参数</td>
</tr>
<tr>
<td>Impossible WHERE</td>
<td>WHERE条件永假</td>
<td>检查业务逻辑错误</td>
</tr>
</tbody>
</table>
<h4 id="3-复合字段分析">3. 复合字段分析</h4>
key_len计算规则：
<ul>
<li>INT：4字节（允许为NULL+1字节）</li>
<li>CHAR(10) UTF8：10×3=30字节（字符长度 * 字符编码字节）</li>
<li>可变长度字段（VARCHAR/TEXT）：长度+2字节</li>
</ul>
示例分析：
<pre><code class="language-sql">-- 表结构
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,-- key_len:50×4+2=202
age TINYINT NOT NULL, -- key_len:1
email VARCHAR(100) NOT NULL, -- key_len:100×4+2=402
INDEX idx_profile (name, age, email)
) CHARSET=utf8mb4;

EXPLAIN
SELECT * FROM users
WHERE name = 'John' AND age = 30 AND email = 'john@example.com';
-- key_len = 605（202+1+402）
</code></pre>
filtered深度解析：
<ul>
<li>表示存储引擎返回数据后，WHERE子句过滤的剩余百分比</li>
<li>理想值100%：索引完全覆盖WHERE条件</li>
<li>低于10%：严重过滤失效，需优化索引</li>
</ul>
<h4 id="4-执行顺序分析id与select_type">4. 执行顺序分析（id与select_type）</h4>
<ul>
<li>id相同：从上到下顺序执行</li>
<li>id不同：从大到小优先级执行</li>
<li>select_type详解：
<ul>
<li>SIMPLE：简单SELECT（无子查询/UNION）</li>
<li>PRIMARY：最外层查询</li>
<li>DERIVED：FROM子句中的子查询</li>
<li>SUBQUERY：SELECT列表中的子查询</li>
<li>UNION：UNION中第二个及以后的SELECT</li>
</ul>
</li>
</ul>
<hr>
<h3 id="四索引优化最佳实践">四、索引优化最佳实践</h3>
<h4 id="1-最左前缀法则实战">1. 最左前缀法则实战</h4>
复合索引<code>(department, salary, hire_date)</code>生效场景： 
✅ <code>WHERE department='IT' AND salary>10000</code> 
✅ <code>WHERE department='Sales' ORDER BY salary</code> 
❌ <code>WHERE salary>10000 ORDER BY hire_date</code> -- 索引部分失效
<h4 id="2-覆盖索引与索引下推">2. 覆盖索引与索引下推</h4>
<pre><code class="language-sql">-- 覆盖索引避免回表（Extra: Using index）
CREATE INDEX idx_cover ON orders(user_id, product_id, amount);
EXPLAIN SELECT user_id, amount FROM orders WHERE product_id=200;

-- 索引下推减少IO（Extra: Using index condition）
EXPLAIN SELECT * FROM products
WHERE category='electronics' AND price>1000; -- 复合索引(category,price)
</code></pre>
<h4 id="3-索引失效的隐蔽陷阱">3. 索引失效的隐蔽陷阱</h4>
<ul>
<li>隐式编码转换：<code>utf8</code>表与<code>utf8mb4</code>字段关联 → 索引失效</li>
<li>函数计算索引列：<pre><code class="language-sql">WHERE DATE(create_time)='2023-01-01' -- 失效
WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59' -- 有效
</code></pre>
</li>
<li>OR条件未覆盖：<pre><code class="language-sql">WHERE a=1 OR b=2 -- 若b无索引则全表扫描
WHERE a=1 UNION ALL (SELECT * FROM t WHERE b=2) -- 优化方案
</code></pre>
</li>
</ul>
<hr>
<h3 id="五高级应用技巧">五、高级应用技巧</h3>
<h4 id="1-扩展explain方法">1. 扩展Explain方法</h4>
<ul>
<li>EXPLAIN ANALYZE（MySQL 8.0+）： 
实际执行并返回执行时间统计<pre><code class="language-sql">EXPLAIN ANALYZE SELECT * FROM large_table WHERE category_id=5;
</code></pre>
</li>
<li>EXPLAIN FORMAT=TREE（MySQL 8.0+）： 
树形结构展示执行流程</li>
</ul>
<h4 id="2-json格式深度分析">2. JSON格式深度分析</h4>
<pre><code class="language-json">EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE user_id IN (
SELECT id FROM users WHERE reg_date>'2020-01-01'
);

-- 输出包含：
{
"query_block": {
"cost_info": {
"query_cost": "2.95"-- 查询总成本
},
"nested_loop": [ ... ]-- 嵌套循环细节
}
}
</code></pre>
<h4 id="3-优化案例大数据量分页">3. 优化案例：大数据量分页</h4>
原始低效查询：
<pre><code class="language-sql">SELECT * FROM user_logs
ORDER BY create_time DESC LIMIT 1000000, 10; -- 扫描100万行
</code></pre>
Explain驱动优化：
<pre><code class="language-sql">-- 通过覆盖索引跳过扫描
SELECT * FROM user_logs l
JOIN (
SELECT id FROM user_logs
ORDER BY create_time DESC LIMIT 1000000, 10
) tmp ON l.id = tmp.id; -- type: ref, rows:10
</code></pre>
<hr>
<h3 id="六避坑指南与最佳实践">六、避坑指南与最佳实践</h3>
<ol>
<li>统计信息时效性： 
定期执行<code>ANALYZE TABLE</code>更新统计信息（注意会占用IO），避免优化器误判</li>
<li>索引维护代价： 
写密集型表每个索引增加20%-30%写开销，需平衡读写需求</li>
<li>优化器版本差异：
<ul>
<li>MySQL 5.6：引入ICP索引下推</li>
<li>MySQL 5.7：优化子查询物化</li>
<li>MySQL 8.0：新增直方图统计</li>
</ul>
</li>
<li>执行计划局限性：
<ul>
<li>不显示存储过程/触发器内的查询</li>
<li>无法预测锁竞争情况</li>
<li>缓存命中率不影响rows值</li>
</ul>
</li>
</ol>
<blockquote>
终极优化策略：结合<code>EXPLAIN ANALYZE</code>实际执行数据与<code>SHOW PROFILE</code>资源消耗分析，形成闭环优化流程。
</blockquote>
<blockquote>
文章的最后，想和你多聊两句。
技术之路，常常是热闹与孤独并存。那些深夜的调试、灵光一闪的方案、还有踩坑爬起后的顿悟，如果能有人一起聊聊，该多好。
为此，我建了一个小花园——我的微信公众号「[努力的小郑]」。
这里没有高深莫测的理论堆砌，只有我对后端开发、系统设计和工程实践的持续思考与沉淀。它更像我的数字笔记本，记录着那些值得被记住的解决方案和思维火花。
如果你觉得今天的文章还有一点启发，或者单纯想找一个同行者偶尔聊聊技术、谈谈思考，那么，欢迎你来坐坐。 
<img src="https://img2024.cnblogs.com/blog/3703499/202601/3703499-20260105210259813-964799315.jpg">
愿你前行路上，总有代码可写，有梦可追，也有灯火可亲。
</blockquote> 
来源：https://www.cnblogs.com/xzqcsj/p/19231363

頁: [1]

琼殿技术论坛's Archiver

MySQL EXPLAIN执行计划：SQL性能翻倍的秘密武器