这个问题很典型——带LIMIT反而比全量查询慢，核心原因是MySQL优化器对小结果集的执行路径判断失误，选了低效的索引。咱们一步步拆解解决：

问题根源分析 #

从你的执行计划能看出：

• 优化器选择了type索引，要扫描506082行，但最终只有0.05%的行符合所有过滤条件（filtered: 0.05）。这意味着大部分扫描的行都要被后续的status、service、created_at条件过滤掉，还要回表取所有字段（因为select *），最后再按id排序取200条，这个过程极其耗时。
• 不带LIMIT时，优化器知道要返回大量数据，会切换到更高效的索引（比如created_at），扫描符合时间范围的行，整体开销反而更小。

具体优化方案 #

1. 构建针对性的组合索引 #

组合索引要覆盖过滤条件+排序字段，并且遵循「等值过滤在前，范围过滤在后，排序字段收尾」的原则：

• 先把等值条件type=0、service IN(...)放在索引最前面（这两个字段的选择性越高，过滤效果越好）
• 接着放范围条件created_at >= ...
• 最后加排序字段id，让索引直接支持排序，避免额外的filesort

如果status的可选值有限（比如只有0、1、2），建议把status != '2' and status != '0'改成status = '1'（等值条件），这样可以把status也加入索引，进一步提升过滤效率。

推荐创建的索引：

-- 方案1：如果status只能是0/1/2，用等值条件
CREATE INDEX idx_trans_type_service_status_created_id ON transactions(type, service, status, created_at, id);

-- 方案2：如果status有更多值，保留不等条件
CREATE INDEX idx_trans_type_service_created_id ON transactions(type, service, created_at, id);

2. 避免select *，使用覆盖索引 #

如果你的业务不需要返回所有字段，只查询必要列，就可以把这些列加入索引，实现覆盖索引——不需要回表查询，直接从索引中获取所有数据，速度会大幅提升。

比如你只需要id、amount、user_id这几个字段，就可以调整索引：

CREATE INDEX idx_trans_cover ON transactions(type, service, created_at, id, amount, user_id);

3. 强制使用新索引（测试阶段） #

如果创建新索引后，优化器依然没选择它，可以用FORCE INDEX强制指定索引，验证效果：

SELECT * FROM transactions 
FORCE INDEX(idx_trans_type_service_created_id)
WHERE type = 0 
  AND status != '2' AND status != '0' 
  AND service IN ('credit_card', 'paypal') 
  AND created_at >= '2021-09-15 14:04:40' 
ORDER BY id DESC LIMIT 200;

4. 用EXPLAIN ANALYZE深入分析（MySQL 8.0+） #

如果是MySQL 8.0及以上版本，用EXPLAIN ANALYZE替代EXPLAIN，它会返回实际执行的行数、时间、循环次数等细节，能帮你更精准地定位瓶颈。

为什么不带LIMIT更快？ #

当没有LIMIT时，优化器评估到要返回大量数据，会优先选择扫描总行数更少的索引（比如created_at索引）——虽然需要过滤其他条件，但整体扫描的行数远小于type索引的50万行，所以总耗时更短。而带LIMIT时，优化器误以为只要扫描少量行就能找到200条符合条件的数据，所以选了type索引，结果实际扫描了大量不符合条件的行，导致变慢。

内容的提问来源于stack exchange，提问作者radu paraleste