同索引场景下，加FORCE INDEX的SQL查询为何性能差异巨大？

这是个非常典型的MySQL优化器决策偏差问题，咱们来拆解背后的核心原因：

1. 优化器的成本估算失误，选了“看起来划算”的差计划 #

MySQL的查询优化器会基于表统计信息（比如行数、索引基数等）估算不同执行路径的成本，再选它认为最优的方案。但在你的场景里：

• 原计划中，优化器可能觉得先执行派生表（获取用户有权限的team_set_id列表），再用这个列表关联contacts表过滤数据，最后做排序的成本更低。但实际这个路径会导致关联后的结果集量级接近200万，不得不触发Using temporary和Using filesort来完成排序——这两个操作在大数据量下是极其耗时的。
• 而FORCE INDEX(idx_del_date_modified)直接强制优化器走主表的指定索引，跳过了它的错误成本估算，走了一条更高效的路径。

2. 索引有序性的利用是性能飞跃的关键 #

idx_del_date_modified这个索引的结构应该是(deleted, date_modified DESC, id DESC)，本身就完全匹配你的ORDER BY顺序。

• 原计划没有利用这个有序性：它先过滤出符合权限的contacts数据，再对这个结果集做排序，相当于要对几十万甚至上百万条数据做全量排序，必然触发临时表和文件排序。
• 加了FORCE INDEX后，优化器会沿着索引的有序顺序读取主表数据，每读取一条就检查它的team_set_id是否在派生表的权限列表里，符合条件的就直接加入结果。因为索引本身就是按目标顺序排好的，所以只需要取前21条符合条件的数据就可以直接返回，完全不需要额外的排序操作——这就是为什么EXPLAIN里不再出现Using temporary和Using filesort，性能直接从2分钟降到0.01秒。

3. 派生表的关联顺序发生了本质变化 #

原计划中，优化器可能是先把派生表的结果集和主表做JOIN，得到所有符合权限的记录后再排序；而FORCE INDEX后，执行顺序变成了：

1. 先按索引顺序遍历主表的contacts记录（已经是目标排序顺序）
2. 对每条记录，检查其team_set_id是否在派生表的权限集合中（派生表可能被优化成了哈希表，匹配速度极快）
3. 收集够21条符合条件的记录后立即停止遍历

这种“边遍历边过滤边收集”的方式，避免了处理大量中间数据，自然效率极高。

4. 统计信息过时可能是潜在诱因 #

如果contacts表或者team_sets_teams等关联表的统计信息没有及时更新，优化器会错误估算派生表的结果集大小、主表符合条件的行数等，导致它做出错误的执行计划选择。FORCE INDEX相当于直接“告诉”优化器该走哪条路，绕过了错误的统计信息影响。

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