当前位置：首页 > news >正文

MySQL Limit数量不满足时导致查询变慢

news 2025/6/28 8:45:14

问题背景

mysql 查询语句中，common_id ，effective_date ，status 分别是单独的索引，id为主键

select id, serial_no from account where 1=1
and id > 3706705 and effective_date >= '2016-01-01' and effective_date <= '2019-05-16'
and common_id in ( '1006' ) and status in ( 1 , 2 )
order by id asc limit 600 ;

这条sql执行时间是2分钟多，实际的查询结果只有33条

这条sql是分页查询的最后一条查询语句，前面查询都很快。

select id, serial_no from account where 1=1
and id > 3706705
and common_id in ( '1006' ) and status in ( 1 , 2 )
order by id asc limit 600 ;

这条sql执行时间是秒级别。

执行计划

字段	值
type	`range`
possible_keys	`PRIMARY`, `index common_id`, `idx effectdate`, `idx status`
key	`PRIMARY`
rows	`12,754,809`
filtered	`0.58`
Extra	`Using where`

🧠 关键分析点

① `key = PRIMARY` ➜ 只用了 `id` 索引

虽然你写了多个条件（common_id, status, effective_date），但 MySQL 最终选择只使用了 PRIMARY KEY (id) 索引。

这说明：

MySQL 试图通过 id > 3706705 开始顺序扫描表的主键，但其余过滤条件（common_id, status, effective_date）都变成了 “额外过滤”，不能用索引加速。

② `rows = 12754809` ➜ 扫描了约 1275 万行！

这是致命性能瓶颈。

这表示 MySQL 扫描了整个主键范围中1275 万行来寻找满足 effective_date 和其他条件的数据。
实际只返回了 33 条。
效率极低，扫描和返回比为 386,511:1。

因为最后一次查询没有满足limit 600，所以一直查询直到最后没有满足600条的数据才返回，大坑！！！

③ `filtered = 0.58` ➜ 只有 0.58% 的行满足条件

这个值也说明：

MySQL 预估大部分行都不会满足 where 条件；
但又因为索引选择不佳，无法提前排除这些行，只能暴力遍历。

📌 为什么不用其他索引（如 `common_id`、`idx_effectdate`）？

因为用了以下复杂组合：

id > 3706705
common_id IN (...)
status IN (...)
effective_date BETWEEN ...
再加 ORDER BY id

MySQL 必须选择一个 单一索引路径。如果这些字段不是同一个联合索引的一部分，它无法优化所有条件。

而：

idx_effectdate 无法帮助排序（ORDER BY id）；
idx_common_id 和 idx_status 也是单列索引，不能联合使用；
最终 MySQL 只能选择 PRIMARY 来满足 id > ... ORDER BY id，牺牲过滤效率。

✅ 解决方案建议

✅ 方案一：加复合索引（强烈推荐）

ALTER TABLE account 
ADD INDEX idx_csid_effective_id (common_id, status, effective_date, id);

这个索引可以覆盖所有过滤条件 + 排序，MySQL 就能：

快速定位满足条件的数据；
按 id 顺序返回；
避免扫描千万行。

⚠️ 顺序不能乱：common_id, status, effective_date, id 是根据你查询的筛选 + 排序优先级排列的。

✅ 方案二：尝试使用 `FORCE INDEX`

如果你无法修改表结构，可以尝试如下变通方法：

SELECT id, serial_no 
FROM account FORCE INDEX(idx_effectdate) 
WHERE id > 3706705AND effective_date >= '2016-01-01'AND effective_date <= '2019-05-16'AND common_id IN ('1006')AND status IN (1, 2)
ORDER BY id ASC 
LIMIT 600;

但效果可能有限，因为 ORDER BY id 与 idx_effectdate 无关联，MySQL 仍可能需要做 filesort，所以只是尝试，不如建复合索引效果好。

✅ 方案三：按时间切片分批查（业务层分页）

如果无法加索引，你可以写一段逻辑：

拿 common_id, status 等过滤后的记录；
进一步按 effective_date 做范围过滤；
或者在业务侧分页时跳过没有数据的时间段（你已知哪些段是“稀疏”的）；

✅ 总结

问题项	原因
查询很慢	查询用了 `PRIMARY KEY` 单一索引，只能遍历大量无效数据行
索引没用上	没有合适的联合索引满足所有筛选条件和排序要求
行数多结果少	MySQL 扫描了 1275 万行，只返回 33 条，效率极低
优化建议	建联合索引：`(common_id, status, effective_date, id)`

你说得完全对，LIMIT 600 导致 MySQL 必须扫描海量数据才能“找够”符合条件的记录，但由于数据分布稀疏、索引不理想，最终只能找到 33 条，且必须扫描上千万行才能确认没有更多了。