MySQL慢查询优化：执行计划解读与索引策略实战

慢查询日志的配置与分析

MySQL慢查询日志是定位性能瓶颈的第一步。合理配置慢查询阈值，确保捕获真正需要优化的查询：

-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志，阈值设为0.1秒
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.1;
SET GLOBAL log_queries_not_using_indexes = ON;

-- 使用pt-query-digest分析慢日志
-- pt-query-digest /var/lib/mysql/slow.log --limit=20

生产环境建议long_query_time设为0.1到0.5秒，而不是默认的10秒。捕获范围过大会产生大量日志影响IO，过小则遗漏有优化价值的查询。

EXPLAIN执行计划关键字段解读

EXPLAIN是MySQL慢查询优化的核心工具，理解每个字段的含义是正确判断优化方向的前提：

EXPLAIN SELECT o.id, o.order_no, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid' AND o.created_at > '2026-07-01'
ORDER BY o.created_at DESC
LIMIT 20;

执行计划各字段含义：

type（访问类型，从优到差）：system > const > eq_ref > ref > range > index > ALL。出现ALL意味着全表扫描，必须加索引优化。index是全索引扫描，比ALL稍好但仍然低效。

key：实际使用的索引名。如果为NULL说明没有可用索引。

rows：MySQL预估需要扫描的行数，是多个表时为各表rows的乘积。这个值越接近实际返回行数越好。

Extra：附加信息。Using index表示覆盖索引，性能最佳。Using filesort表示额外排序，需要优化。Using temporary表示使用了临时表，常见于GROUP BY无索引场景。Using where表示存储引擎返回数据后还需要在Server层过滤。

索引失效的常见场景与修复

索引存在但查询没有使用，是慢查询中最常见的问题类型：

场景1：索引列上使用函数或运算

-- 索引失效：在created_at列上使用DATE函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-08-07';
-- 修复：改写为范围查询
SELECT * FROM orders
WHERE created_at >= '2026-08-07' AND created_at < '2026-08-08';

场景2：隐式类型转换

-- user_id是varchar类型，传入整数值导致隐式转换
SELECT * FROM orders WHERE user_id = 12345;
-- 修复：传入字符串值
SELECT * FROM orders WHERE user_id = '12345';

场景3：联合索引最左前缀违反

-- 联合索引 idx_status_created_at (status, created_at)
-- 索引失效：跳过status直接用created_at
SELECT * FROM orders WHERE created_at > '2026-07-01';
-- 修复：包含最左列status
SELECT * FROM orders
WHERE status = 'paid' AND created_at > '2026-07-01';

场景4：LIKE前缀通配符

-- 索引失效：%开头的LIKE无法利用B+Tree索引
SELECT * FROM users WHERE name LIKE '%张%';
-- 修复：前缀匹配可以利用索引
SELECT * FROM users WHERE name LIKE '张%';
-- 全文搜索场景使用全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_name(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('张');

覆盖索引与延迟关联优化

当查询只需要索引列的数据时，MySQL可以直接从索引中返回结果而无需回表，称为覆盖索引（Extra列显示Using index）：

-- 覆盖索引：(status, created_at) 联合索引包含status和created_at
SELECT status, created_at FROM orders
WHERE status = 'paid' ORDER BY created_at DESC LIMIT 100;

当查询需要非索引列时，可以先用覆盖索引查出主键，再回表获取完整数据，减少回表次数：

-- 原始查询：status有索引但需要回表获取所有列
SELECT * FROM orders
WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20;

-- 延迟关联优化：先通过覆盖索引查出id，再回表
SELECT o.* FROM orders o
JOIN (
    SELECT id FROM orders
    WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20
) tmp ON o.id = tmp.id;

当status=’paid’匹配行数很多时（比如10万行），原始查询需要回表10万次并排序，而延迟关联只需要回表20次。效果在数据量大时尤为显著。

分页查询的深度翻页优化

LIMIT offset, size在offset很大时性能极差，因为MySQL需要扫描前offset+size行再丢弃前offset行：

-- 深度翻页：扫描100020行，丢弃前100000行
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 优化1：游标分页（推荐）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

-- 优化2：延迟关联
SELECT o.* FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) tmp ON o.id = tmp.id;

游标分页的局限是不支持跳转到指定页码，只支持上一页/下一页。业务上需要跳页的场景，延迟关联是更好的折中方案。

索引设计原则与常见反模式

索引设计没有固定公式，但以下原则可以减少踩坑：

1. 选择性高的列优先：status列只有几个离散值，单独建索引意义不大。但(status, created_at)联合索引因为区分度足够高，效果很好。

2. 联合索引顺序遵循最左前缀：将等值查询的列放前面，范围查询的列放后面。

3. 避免冗余索引：已有(a, b)索引时，单独的(a)索引是冗余的。

4. 控制单表索引数量：每个索引都增加写操作的开销。单表索引建议不超过5到6个。