带ORDER BY的MySQL动态查询执行过慢，索引未正常选用求助

首先，咱们拆解下问题的核心：MySQL优化器之所以选了主键索引全表扫描，是因为它误判了成本——觉得按主键顺序扫描并过滤survey_id的开销，比用survey相关索引后做filesort排序更低，但实际情况正好相反：你的目标survey_id对应4.5万条记录，远小于全表620万，filesort的成本远低于全表扫描。

下面是无需显式指定USE INDEX的优化方案，按优先级排序：

1. 创建精准匹配查询模式的复合索引 #

针对你的四种WHERE组合，最有效的方式是打造覆盖过滤条件+排序字段的复合索引，让优化器能自动识别最优路径：

核心索引：(survey_id, id) #

这个索引完美匹配你最基础的查询WHERE survey_id=? ORDER BY id LIMIT 100：

• 前缀survey_id能快速过滤出目标数据集
• 后缀id让过滤后的结果天然按id有序，完全避免filesort

执行创建语句：

CREATE INDEX survey_id_id_idx ON response_set(survey_id, id);

如果你之前创建过这个索引但无效，大概率是MySQL的统计信息过时了，执行以下命令更新统计数据，让优化器重新评估索引成本：

ANALYZE TABLE response_set;

扩展索引覆盖其他WHERE组合 #

为了同时支持另外三种查询组合，你可以根据业务查询频率创建针对性索引（避免过多索引影响写入性能）：

• 针对WHERE survey_id=? AND t=?：(survey_id, t, id)（先按survey_id过滤，再按t缩小范围，最后id有序）
• 针对WHERE survey_id=? AND terminated_survey=?：(survey_id, terminated_survey, id)
• 针对WHERE survey_id=? AND t=? AND terminated_survey=?：如果t的过滤性更强，用(survey_id, t, terminated_survey, id)；反之则用(survey_id, terminated_survey, t, id)

这些索引的设计逻辑都是：先放过滤条件（按过滤性从强到弱），最后放排序字段id，既能快速过滤数据，又能让结果天然有序，无需额外排序。

2. 调整现有索引结构（替代方案） #

如果你不想新增太多索引，可以修改现有索引，把id加到合适的位置：
比如把survey_timestamp_idx从(survey_id, t)改成(survey_id, id, t)——这样它既支持survey_id过滤+id排序，也能覆盖包含t的查询条件。

修改索引的语句（先删旧索引，再建新索引）：

DROP INDEX survey_timestamp_idx ON response_set;
CREATE INDEX survey_timestamp_idx ON response_set(survey_id, id, t);

3. 辅助：优化MySQL统计信息 #

如果优化器依然固执地选错索引，除了ANALYZE TABLE，还可以手动触发索引统计更新：

ALTER TABLE response_set FORCE INDEX FOR SELECT (survey_id_id_idx);

（这只是临时让优化器关注该索引，后续会自动恢复，无需长期指定）

验证效果 #

创建索引并更新统计信息后，重新执行EXPLAIN查看执行计划，应该会看到优化器自动选择你创建的复合索引，且Extra字段没有Using filesort，执行时间会稳定在毫秒级。

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