MySQL执行计划深度解析：慢查询诊断与索引优化实战指南

小编 • • 数据库

MySQL慢查询是数据库性能问题的头号杀手。一条低效SQL可能导致整库响应变慢，甚至触发连接池耗尽。EXPLAIN是MySQL提供的执行计划分析工具，通过解读执行计划可以精确判断索引使用情况、扫描行数和连接策略。本文以真实慢查询案例为驱动，覆盖执行计划解读、索引设计和优化实战。

EXPLAIN执行计划字段详解

EXPLAIN是MySQL查询优化器的输出，展示SQL将如何执行。理解每个字段的含义是优化的前提。

-- 创建测试表和数据
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT NOT NULL,
    status TINYINT NOT NULL DEFAULT 0,
    amount DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_status_created (status, created_at)
) ENGINE=InnoDB;

CREATE TABLE order_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    product_name VARCHAR(128) NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    INDEX idx_order_id (order_id)
) ENGINE=InnoDB;

-- 执行EXPLAIN
EXPLAIN SELECT o.order_no, o.amount, oi.product_name, oi.quantity
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 10086
  AND o.status = 1
ORDER BY o.created_at DESC
LIMIT 20;

EXPLAIN输出字段详解：

+----+--------+-------------+-------+------+-------------------+-------------------+---------+----------+------+----------+------------------------------------+
| id | select | table       | type  | key  | key_len           | ref               | rows    | filtered | Extra                              |
+----+--------+-------------+-------+------+-------------------+-------------------+---------+----------+------------------------------------+
| 1  | SIMPLE | o           | ref   | idx_user_id | 8       | const             | 156     | 3.33     | Using where; Using filesort        |
| 1  | SIMPLE | oi          | ref   | idx_order_id| 9       | test.o.id         | 3       | 100.00   | NULL                               |
+----+--------+-------------+-------+------+-------------------+-------------------+---------+----------+------------------------------------+

各字段含义：

  • id：查询标识符。相同id表示在同一层执行，子查询id递增。
  • select_type：SIMPLE（简单查询）、PRIMARY（最外层）、SUBQUERY（子查询）、DERIVED（派生表）。
  • type：访问类型，性能从好到差：system > const > eq_ref > ref > range > index > ALL。ALL表示全表扫描，必须优化。
  • key：实际使用的索引。NULL表示未使用索引。
  • key_len：索引使用的字节数。可用于判断复合索引使用了几个字段。
  • rows：预估扫描行数。越小说明索引越有效。
  • filtered：过滤后剩余的行百分比。3.33表示扫描156行后只有约5行满足条件。
  • Extra：额外信息。Using index（覆盖索引）、Using filesort（文件排序）、Using temporary（临时表）是需要关注的信号。

type字段深度解读与优化目标

type字段是判断查询效率的核心指标。各类型的具体含义和优化目标：

-- ALL：全表扫描，最差情况
EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2026-07-23';
-- type=ALL，rows=1000000（全表扫描）
-- 问题：在索引列上使用函数导致索引失效

-- 优化：改为范围查询
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2026-07-23 00:00:00'
  AND created_at < '2026-07-24 00:00:00';
-- type=range，rows=1200（索引范围扫描）

-- index：全索引扫描，比ALL好但仍需优化
EXPLAIN SELECT COUNT(*) FROM orders;
-- type=index，扫描整个索引树

-- ref：非唯一索引等值匹配
EXPLAIN SELECT * FROM orders WHERE user_id = 10086;
-- type=ref，使用idx_user_id索引

-- eq_ref：唯一索引等值匹配（JOIN场景最优）
EXPLAIN SELECT * FROM orders o
JOIN order_items oi ON o.id = oi.order_id;
-- type=eq_ref，使用主键关联

-- const：主键或唯一索引等值查询
EXPLAIN SELECT * FROM orders WHERE id = 1;
-- type=const，最多匹配一行

Extra字段关键信号与优化策略

Extra字段包含执行计划的额外信息，三个关键信号直接影响性能：

Using filesort（文件排序）

表示MySQL无法使用索引完成排序，需要在内存或磁盘中排序。数据量大时严重影响性能。

-- 触发filesort的查询
EXPLAIN SELECT * FROM orders WHERE user_id = 10086 ORDER BY created_at DESC;
-- Extra: Using where; Using filesort
-- 问题：idx_user_id索引不包含created_at，排序无法利用索引

-- 优化：创建复合索引
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);
EXPLAIN SELECT * FROM orders WHERE user_id = 10086 ORDER BY created_at DESC;
-- Extra: Using where; Using index  (filesort消失)

Using temporary（临时表）

MySQL需要创建临时表来处理查询。常见于GROUP BY、DISTINCT和UNION操作。

-- 触发temporary的查询
EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;
-- Extra: Using temporary; Using filesort

-- 优化：创建覆盖索引
ALTER TABLE orders ADD INDEX idx_status (status);
EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;
-- Extra: Using index  (temporary和filesort都消失)

Using index（覆盖索引）

查询所需的所有字段都能从索引中获取，无需回表读取数据行。这是最优的查询方式。

-- 覆盖索引示例
ALTER TABLE orders ADD INDEX idx_user_status_amount (user_id, status, amount);

EXPLAIN SELECT user_id, status, amount FROM orders WHERE user_id = 10086;
-- Extra: Using index  (覆盖索引，无需回表)

-- 注意：SELECT * 会破坏覆盖索引
EXPLAIN SELECT * FROM orders WHERE user_id = 10086;
-- Extra: NULL  (需要回表，因为查询了索引外的字段)

复合索引设计与最左前缀原则

复合索引的列顺序决定了索引的可用性。MySQL遵循最左前缀原则：查询条件必须从索引的最左列开始连续匹配。

-- 复合索引 (user_id, status, created_at)
-- 索引有效的情况：
SELECT * FROM orders WHERE user_id = 10086;                    -- 使用1列
SELECT * FROM orders WHERE user_id = 10086 AND status = 1;     -- 使用2列
SELECT * FROM orders WHERE user_id = 10086 AND status = 1
    AND created_at > '2026-07-01';                              -- 使用3列（范围）

-- 索引部分有效：
SELECT * FROM orders WHERE user_id = 10086
    AND created_at > '2026-07-01';
-- 只使用user_id列，跳过了status列（中间列缺失）

-- 索引完全无效：
SELECT * FROM orders WHERE status = 1;                          -- 缺少最左列
SELECT * FROM orders WHERE created_at > '2026-07-01';           -- 缺少最左列

通过key_len可以验证复合索引用了几个列：

-- key_len计算规则：
-- BIGINT: 8字节, TINYINT: 1字节, DATETIME: 5字节
-- 非空字段+1字节, 可变长度字段+2字节

EXPLAIN SELECT * FROM orders WHERE user_id = 10086 AND status = 1;
-- key_len = 8(user_id) + 1(status) = 9
-- 如果status字段允许NULL，key_len = 8 + 1 + 1(NULL标志) = 10

-- 通过key_len判断索引利用率
-- 索引(user_id, status, created_at)最大key_len = 8+1+5 = 14
-- 实际key_len=9说明只用了前两列

慢查询日志配置与分析实战

生产环境通过慢查询日志自动捕获低效SQL：

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 使用mysqldumpslow分析慢查询日志
-- 按总耗时排序
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
-- 按次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
-- 按返回行数排序
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

-- 或使用pt-query-digest（更强大）
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

pt-query-digest的分析结果会按SQL指纹聚合，展示每类SQL的执行次数、平均耗时、总耗时占比和95分位延迟，帮助快速定位TOP N慢查询。

索引优化决策checklist

面对慢查询，按以下流程决策是否需要新增或调整索引：

  1. EXPLAIN查看type字段：ALL或index需优化
  2. 检查WHERE条件字段是否有索引
  3. 检查ORDER BY/GROUP BY字段是否在索引中
  4. 检查是否有函数操作导致索引失效（DATE()、UPPER()、类型转换等）
  5. 检查LIKE查询是否以%开头（’%keyword’无法用索引）
  6. 评估复合索引列顺序：等值条件在前，范围条件在后
  7. 确认是否可使用覆盖索引避免回表
  8. 评估索引维护成本：写多读少的表需控制索引数量

单表索引数量建议不超过5个，每个索引都会增加INSERT/UPDATE/DELETE的维护成本。优先优化查询SQL本身（减少SELECT *、避免SELECT COUNT(*)频繁执行、大表分页用游标方案替代LIMIT OFFSET），再考虑添加索引。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql-zhi-xing-ji-hua-shen-du-jie-xi-man-cha-xun-zhen-duan/

赞 (0)
小编小编
0
上一篇 11小时前
下一篇 11小时前

相关推荐