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

问题背景

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执行时间是秒级别。

执行计划

🧠 关键分析点

① 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 > 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;

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

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

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

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

✅ 总结

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

经过分析，是因为limit 600的原因导致一直在扫描数据，直到全部扫描完成发现只有33条满足条件的数据，才返回。