如何提升MySQL中ORDER BY排序语句的查询性能？

性能差异根本原因 #

不带ORDER BY时，数据库匹配到30条符合筛选条件的记录就会直接返回，不需要处理全量匹配结果；带ORDER BY时，默认需要先把所有符合条件的记录全部检索、关联完成后做全量排序，再取前30条，10亿级表的全量排序开销极高，导致耗时暴涨。

优化方案 #

• 优先创建复合覆盖索引（投入最小收益最高）
  针对主表auctions_opportunity创建左前缀匹配的复合索引，优先放等值筛选字段，再放排序字段，最后包含所有查询用到的该表字段，避免回表查询，实现索引直接排序：

-- MySQL 8.0+ 支持INCLUDE语法
CREATE INDEX idx_opp_emp_job_active_reviewed ON auctions_opportunity (employer_id, job_id, is_active, reviewed_at DESC) INCLUDE (interview_status, creation_source, candidate_id, is_location_match, salary, is_interested, previous_interview_status, created_at, last_modified, last_instant_alert_email_at, last_daily_alert_email_at, last_periodic_alert_email_at, last_modified_by_id, interview_request_notes, application_email_at, batch_application_email_at, is_strong_match, score, es_score, message, es_maybe);

-- MySQL 8.0以下版本直接把字段加到索引末尾即可
CREATE INDEX idx_opp_emp_job_active_reviewed ON auctions_opportunity (employer_id, job_id, is_active, reviewed_at DESC, interview_status, creation_source, candidate_id, is_location_match, salary, is_interested, previous_interview_status, created_at, last_modified, last_instant_alert_email_at, last_daily_alert_email_at, last_periodic_alert_email_at, last_modified_by_id, interview_request_notes, application_email_at, batch_application_email_at, is_strong_match, score, es_score, message, es_maybe);

索引生效后，数据库可以直接按索引的reviewed_at降序顺序扫描，每扫描一条就校验剩余筛选条件，凑够30条就停止，完全不需要全量排序。

• 改写查询为「先筛主表再关联」模式
  避免先关联所有表再排序的大开销，先从主表筛选出符合条件、排序后的30条主键，再用这30条主键关联其他所有表取字段，关联量级直接从百万/千万级降到30条：

SELECT /* 你需要查询的所有字段 */
FROM (
    SELECT id FROM auctions_opportunity
    WHERE /* 所有仅涉及auctions_opportunity表的筛选条件 */
    ORDER BY reviewed_at DESC LIMIT 30
) temp_opp
INNER JOIN auctions_opportunity ON temp_opp.id = auctions_opportunity.id
-- 后面保留原来所有的JOIN关联逻辑和涉及其他表的筛选条件

• 清理冗余低效的查询逻辑

  • 删掉重复的auctions_opportunity.employer_id = 4、NOT (auctions_opportunity.job_id IS NULL)条件，避免优化器做额外判断
  • 把低效的相关子查询NOT (id IN (SELECT U0.id ...))改写为NOT EXISTS (SELECT 1 FROM candidates_emailaction U1 WHERE U1.opportunity_id = auctions_opportunity.id AND U1.reply_email_at IS NULL)，执行效率提升数倍
  • 去掉多余的DISTINCT：你已经在WHERE中限定了所有LEFT JOIN的action表id为NULL，不会产生重复行，DISTINCT的去重开销完全是浪费
  • 把前后模糊匹配的LIKE %Delhi%改成全文索引或者预存的位置标签等值匹配，前后模糊匹配无法用到普通索引，筛选效率极低

• 验证执行计划
  执行EXPLAIN语句查看执行计划，确认没有Using filesort、Using temporary等耗时操作，确认复合索引被正常使用。

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