MySQL 慢查询调优，把我踩过的坑写给你看

一、前言¶

我接手过一个业务模块，线上一个列表页加载要 8 秒。查了日志发现是一条 SQL 跑了 6.3 秒——数据量不到 200 万行，表结构看起来也没什么问题。加了个索引，降到 0.02 秒。

这不是段子，是每天都在发生的事。MySQL 的慢查询，80% 是索引问题，15% 是写法问题，5% 是架构问题。

本文不讲 B+ 树原理，不画执行流程图。我从实际踩过的坑里挑了几个典型案例，每个都附 SQL、现象和修复方案。你看完能直接对着自己的慢查询日志定位问题。

二、先看慢查询日志¶

2.1 打开慢查询¶

-- 查看是否开启
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 临时开启（生产慎用，测试环境随便开）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;       -- 超过 1 秒就算慢
SET GLOBAL log_queries_not_using_indexes = ON;  -- 没走索引也记

慢查询日志位置：

# 查看日志路径
SHOW VARIABLES LIKE 'slow_query_log_file';

# 用 mysqldumpslow 分析
mysqldumpslow -s t -t 10 /var/lib/mysql/*-slow.log
# -s t  按查询耗时排序
# -t 10 取前 10 条

2.2 用 EXPLAIN 分析执行计划¶

1	EXPLAIN SELECT * FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 20;

关注这几列：

列名	重点关注什么
`type`	ALL 是灾难，range/ref 算正常，const 是理想
`Extra`	Using filesort、Using temporary 是危险信号
`rows`	扫描行数，越大越慢
`key`	实际使用的索引，NULL 意味着没走索引
`possible_keys`	能用的索引有哪些

一个经验：type = ALL + rows > 10万 + Extra 有 Using filesort，这条 SQL 基本需要优化。

三、案例一：没走索引¶

3.1 现场¶

1 2	-- 耗时：4.2 秒 SELECT * FROM payment_log WHERE amount > 100 ORDER BY user_id;

1	EXPLAIN 结果：type = ALL, rows = 185万, Extra = Using where; Using filesort

表数据量 185 万，全表扫描 + 文件排序，4 秒很正常。

3.2 诊断¶

1	SHOW INDEX FROM payment_log;

发现只有主键索引，amount 和 user_id 都没有索引。WHERE amount > 100 无法走索引（因为 > 范围查询的索引选择性很差，但至少比全表扫描好），ORDER BY user_id 需要排序。

3.3 修复¶

1 2	-- 加复合索引，把 WHERE 条件和 ORDER BY 字段都覆盖 ALTER TABLE payment_log ADD INDEX idx_amount_user (`amount`, `user_id`);

改完：type = range, rows = 3.2万, Extra = Using where，耗时 0.03 秒。

注意顺序：等值条件放前面，范围条件（> < BETWEEN）放中间，ORDER BY 放最后。如果范围条件在索引最左列，后面的字段不会用于排序。

四、案例二：ORDER BY + LIMIT 分页深度问题¶

4.1 现场¶

1 2	-- 耗时：6.3 秒 SELECT id, title, content FROM articles ORDER BY created_at DESC LIMIT 100000, 20;

这是经典的分页深翻问题。LIMIT 100000, 20 不是只读 20 条——MySQL 先读 100020 条，丢弃前 100000 条，只返回最后 20 条。越多翻越慢。

4.2 修复方案一：游标分页（推荐）¶

-- 前端传上一页最后一条的 created_at
SELECT id, title, content FROM articles
WHERE created_at < '2024-05-01 12:00:00'
ORDER BY created_at DESC
LIMIT 20;

走 idx_created_at 索引，扫描 20 条就停。不管翻到多少页，性能都一样。

4.3 修复方案二：延迟关联¶

如果无法改成分页方式，用子查询先走索引拿 ID，再关联回表取数据：

SELECT a.id, a.title, a.content FROM articles a
INNER JOIN (
    SELECT id FROM articles
    ORDER BY created_at DESC
    LIMIT 100000, 20
) tmp ON a.id = tmp.id;

子查询只走索引（覆盖索引，不回表），拿到 20 个 ID 后再回主表查一次。深翻场景下，比裸 LIMIT 快 10-50 倍。

五、案例三：隐式类型转换¶

5.1 现场¶

1 2	-- 耗时：3.8 秒 SELECT * FROM users WHERE id_card = '110101199001011234';

id_card 列是 VARCHAR(32)，表里 50 万行，有索引但没走。

5.2 诊断¶

1 2	EXPLAIN SELECT * FROM users WHERE id_card = 110101199001011234; -- type = ALL, key = NULL

问题在于传入的参数是数值类型，但列是 VARCHAR。MySQL 做了隐式类型转换，把列值转成数字再比较，导致索引失效。

更隐蔽的情况：

1
2
3

# Python 代码里
cursor.execute("SELECT * FROM users WHERE id_card = %s", (110101199001011234,))
# 传入的是 int，不是 str

5.3 修复¶

客户端传参确保类型一致：

1 2	# 修复后：传入字符串 cursor.execute("SELECT * FROM users WHERE id_card = %s", ("110101199001011234",))

一个检查方法：对比这两个 SQL 的 EXPLAIN 结果，一个有索引一个没有，说明就在类型问题上：

1 2	EXPLAIN SELECT * FROM users WHERE id_card = '110101199001011234'; -- 走索引 EXPLAIN SELECT * FROM users WHERE id_card = 110101199001011234; -- 不走

六、案例四：函数包裹导致索引失效¶

6.1 现场¶

1 2	-- 耗时：2.1 秒（查当月订单） SELECT * FROM orders WHERE DATE_FORMAT(created_at, '%Y-%m') = '2024-05';

created_at 有索引，但 DATE_FORMAT() 对列做了函数计算，导致索引无法使用。

6.2 修复¶

-- 改成范围查询，走索引
SELECT * FROM orders
WHERE created_at >= '2024-05-01'
  AND created_at < '2024-06-01';

等价效果，但前者全表扫描，后者走索引范围扫描。

常见函数陷阱：

错误写法	正确写法
`WHERE DATE(created_at) = '2024-05-20'`	`WHERE created_at >= '2024-05-20' AND created_at < '2024-05-21'`
`WHERE YEAR(created_at) = 2024`	`WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'`
`WHERE LEFT(name, 1) = '张'`	`WHERE name LIKE '张%'`
`WHERE id + 1 = 100`	`WHERE id = 99`

原则：列在左边是等式；列在函数里就是全表扫描。

七、案例五：大表 JOIN 未命中索引¶

7.1 现场¶

-- 耗时：12 秒
SELECT u.name, o.order_no, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 100;

7.2 诊断¶

1
2
3

EXPLAIN ...
-- users: type = range, rows = 30万 (用了 created_at 索引)
-- orders: type = ALL, rows = 500万 (被驱动表全表扫描)

orders.user_id 没有索引。MySQL 选择 users 作为驱动表（30 万行），对每一行去 orders 里找 user_id 匹配——由于没索引，每次都是全表扫描。30 万 × 500 万 = 灾难。

7.3 修复¶

1	ALTER TABLE orders ADD INDEX idx_user_id (user_id);

添加后：orders 的 type = ref, rows = 1-5，总耗时降到 0.08 秒。

JOIN 的核心原则：

被驱动表的连接列必须建索引
小表驱动大表
EXPLAIN 结果里排在第一行的是驱动表

八、案例六：COUNT 慢¶

8.1 现场¶

1 2	-- 耗时：5 秒 SELECT COUNT(*) FROM logs WHERE status = 1;

logs 表 2000 万行，status 没有索引。

8.2 诊断¶

COUNT(*) 在 InnoDB 下不走主键聚簇索引——它需要一个二级索引才能加速。没有索引就是全表扫。

8.3 修复¶

1	ALTER TABLE logs ADD INDEX idx_status (status);

加了索引后，COUNT(*) 在二级索引上统计行数（二级索引比聚簇索引小很多），只需扫描 status=1 对应的那部分索引页。

如果只是想知道"大概有多少行"，用 SHOW TABLE STATUS LIKE 'logs' 里的 Rows 字段，那是估算值，但快得多。

如果精确计数频繁调用，用计数表或 Redis 维护计数器：

CREATE TABLE counter (
    `key` VARCHAR(64) PRIMARY KEY,
    `value` BIGINT UNSIGNED NOT NULL
);
-- 每次插入/删除 logs 时，原子更新 counter
UPDATE counter SET value = value + 1 WHERE `key` = 'logs_status_1';

九、调优检查清单¶

下次遇到慢查询，按这个顺序排查：

步骤	检查项	解决
1	慢查询日志打开了没？	`SET GLOBAL slow_query_log = ON`
2	索引建了没？	EXPLAIN 看 type，没走索引就加
3	索引用了没？	检查列有没有被函数/类型转换包裹
4	复合索引顺序对不对？	等值 → 范围 → ORDER BY
5	是不是深翻页？	改成游标分页或延迟关联
6	JOIN 的被驱动表有索引吗？	连接列必建索引
7	COUNT 走索引了吗？	二级索引或计数表
8	数据量是否该归档了？	分区表或历史表分离

十、总结¶

问题类型	核心原因	一句话修复
全表扫描	没索引或索引失效	EXPLAIN 看 type，建合适的索引
深分页	LIMIT 抛弃了大量行	游标分页 / 延迟关联
类型转换	参数类型与列类型不匹配	客户端传参保持类型一致
函数包裹	对列做函数计算	改写为范围查询
JOIN 慢	被驱动表连接列无索引	加索引
COUNT 慢	大表扫全行	二级索引 / 计数表
排序慢	ORDER BY 未覆盖索引	复合索引包含排序字段

MySQL 的调优不像网上说的那么玄乎。打开慢查询日志，用 EXPLAIN 看执行计划，对照上面的清单一条条查，80% 的问题 10 分钟内能找到原因并修复。

最后一个建议：把慢查询阈值设短一点，0.5 秒或者 1 秒。线上没有那么多"特殊查询"，慢就是慢，该优化就优化。