1500万+行表带WHERE、ORDER BY查询加LIMIT后性能骤降问题咨询

你遇到的性能差异是MySQL优化器的索引选择错误导致的：

• 不带LIMIT的查询：优化器选择ITEM_FK_1（config_id字段的索引），先快速过滤出所有config_id=678的约9.8万行数据，再对这9.8万行做内存排序（filesort），9.8万行的排序开销极低，所以总耗时只有800ms。
• 加了LIMIT 200的查询：优化器错误判断了执行成本，选择了ITEM_RULE_ITEM_UNQ索引（该索引是item_name字段开头的索引，天然按item_name有序）。优化器认为只要沿着这个索引按item_name的顺序扫描，找到200条符合config_id=678的行就可以提前终止，不需要排序，成本更低。但实际场景中，config_id=678的行在索引中分布非常稀疏，优化器预估只需要扫3万行就能凑够200条，实际可能需要扫描几十万甚至上百万行才能找到符合条件的200条，最终导致耗时飙升。

• 对比两个查询的执行计划差异：重点看key字段用到的索引、type字段的访问类型、rows字段的预估扫描行数，可直接定位到是索引选择错误的问题。
• 使用EXPLAIN ANALYZE（MySQL 8.0.18及以上版本支持）执行带LIMIT的查询，可以看到实际扫描的行数，如果实际扫描行数远大于预估的3.1万行，即可完全确认问题。
• 查看ITEM_RULE_ITEM_UNQ索引的字段组成，确认该索引的前缀是item_name，且不包含config_id作为前缀字段，无法同时满足过滤和排序需求。

推荐按优先级选择以下方案：

1. 创建最优联合索引（长期最优方案）
   创建(config_id, item_name)的联合索引，该索引可以同时满足WHERE config_id=?的过滤需求，以及ORDER BY item_name的排序需求：同config_id下的item_name在索引中天然有序，数据库可以直接按索引顺序取出前200条符合条件的行，不需要排序，也不需要扫描多余数据，查询耗时可以降到毫秒级。
   建索引语句参考：

CREATE INDEX idx_item_configid_itemname ON item(config_id, item_name);

2. 强制指定索引（快速临时修复）
   在查询语句中添加索引提示，强制使用ITEM_FK_1索引，走原来的执行逻辑：先过滤9.8万行再排序取前200，总耗时仍然保持在800ms左右，远快于5分钟。
   修改后的查询参考：

SELECT
    `t0`.`id`,
    `t0`.`item_name`
FROM
    `item` t0 FORCE INDEX(ITEM_FK_1)
WHERE (`t0`.`config_id` = 678)
ORDER BY
    `t0`.`item_name` ASC LIMIT 200;

3. 调整优化器参数
   临时调整优化器的成本计算规则，避免优化器优先选择为了避免排序而扫描更多行的索引，不过该方案可能影响其他查询，不推荐作为长期方案。

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