MySQL 慢查询定位与索引优化实战：从 EXPLAIN 到索引设计

一、慢查询为什么要当回事

数据库往往是整个链路里最贵、最难扩展的一环。一条没走索引的 SQL，可能让一次接口从 10ms 变成 2s；几个高频慢查询叠在一起，就能把连接池占满、拖垮整个服务。所以“定位慢查询 + 用对索引”是后端工程师的基本功。

本文讲三件事：怎么找到慢查询、怎么读懂执行计划、以及常见索引失效场景与优化手段。

二、先把慢查询日志打开

MySQL 自带慢查询日志，是定位问题的第一手资料。

-- 查看当前设置
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

-- 打开慢查询日志并设置阈值（超过 1 秒记录）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

线上建议常开（阈值如 1s），并用 pt-query-digest 或 mysqldumpslow 做聚合，按“总耗时/平均耗时/扫描行数”排序，优先治理 Top SQL，而不是逐条瞎看。

三、用 EXPLAIN 读懂执行计划

EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 1;

重点看这几列：

• type：访问类型，性能从好到差大致是 const > eq_ref > ref > range > index > ALL。出现 ALL（全表扫描）基本就是问题。
• key：实际使用的索引。为 NULL 说明没走索引。
• rows：预估扫描行数。越小越好，动辄几十万行要警惕。
• Extra：Using filesort（额外排序）、Using temporary（临时表）常见于需要优化的查询；Using index 表示走了覆盖索引，是好事。

四、索引为什么有效（一句话原理）

InnoDB 的主键索引和二级索引都是 B+ 树。二级索引的叶子节点存的是“索引列 + 主键”，查非索引列时需要“回表”再按主键查一次。联合索引遵循最左前缀原则：(a, b, c) 能用于 a、a,b、a,b,c 的过滤，但不能只给 b 或 c 用。

五、常见的索引失效场景

1. 对索引列做函数或运算：

-- 失效：created_at 上索引用不上
SELECT * FROM orders WHERE DATE(created_at) = '2026-01-01';
-- 改成范围条件
SELECT * FROM orders WHERE created_at >= '2026-01-01'
                      AND created_at <  '2026-01-02';

2. 隐式类型转换：phone 是 varchar，却用数字查 WHERE phone = 13800000000，会触发转换导致索引失效，改成字符串 '13800000000'。

3. 前导模糊查询：LIKE '%abc' 无法用索引（LIKE 'abc%' 可以）。

4. 不符合最左前缀：联合索引 (user_id, status)，只按 status 查询用不上索引。

5. OR 连接非索引列：WHERE a = 1 OR b = 2，若 b 无索引，可能导致整体走全表。

六、两个高频优化套路

1. 深翻页（LIMIT 偏移很大）

-- 慢：要扫描并丢弃前 100000 行
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 快：游标/延迟关联，用上次最大 id 续查
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

2. 覆盖索引减少回表

如果查询只需要少数几列，建立包含这些列的联合索引，让查询直接在索引里完成，避免回表：

ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);
-- 查询列都在索引里，Extra 显示 Using index
SELECT user_id, status FROM orders WHERE user_id = 1001;

七、索引设计几条原则

• 区分度高、过滤性强的列优先建索引（如 user_id 优于 status）。
• 联合索引把等值过滤列放前、范围/排序列放后。
• 控制索引数量：索引会拖慢写入并占空间，不要“见列就加”。
• 定期用 EXPLAIN 回归核心 SQL，避免上线后退化。

八、总结

治理慢查询的闭环是：开慢日志找到 Top SQL → EXPLAIN 判断是否走索引 → 消除索引失效/加合适的联合索引/改写 SQL（深翻页、覆盖索引）→ 回归验证。索引不是越多越好，而是要“命中正确的那一个”。把这条排查路径固化成日常巡检，数据库的性能问题就能被扼杀在发生之前。