MySQL 8中ORDER BY结合LIMIT导致SELECT查询过慢的优化求助

问题背景 #

针对120GB、3900万条记录的xyz表，执行分页查询获取指定campaign下未作废的最新10条记录时，耗时超200秒——尽管已创建看似匹配的复合索引activityOverview，EXPLAIN显示扫描行数仅1626行，但实际执行效率极低。仅支持连续下一页跳转，无法进行大跨度分页。

查询语句：

select
    `xyz`.*
from
    xyz
where
    `xyz`.`fk_campaign_id` = 95870
    and `xyz`.`voided` = 0
order by
    `registration_id` desc
limit 10 offset 0

表关键结构：

CREATE TABLE `xyz` (
  `registration_id` int NOT NULL AUTO_INCREMENT,
  `fk_campaign_id` int DEFAULT NULL,
  `voided` tinyint unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`registration_id`),
  KEY `activityOverview` (`fk_campaign_id`,`voided`,`registration_id` DESC)
  -- 其他字段和索引省略
) ENGINE=InnoDB AUTO_INCREMENT=280614594 DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

可能的根因 #

1. 优化器选错索引：MySQL优化器依赖统计信息判断索引成本，大表统计信息过时或索引选择性评估偏差时，可能跳过activityOverview，改用主键索引扫描，导致大量回表随机IO。
2. 回表开销爆炸：查询*需要通过索引中的registration_id回表获取所有字段，大表数据碎片化严重时，磁盘随机IO耗时占比极高。
3. 统计信息失效：表数据量巨大且频繁写入，MySQL自动生成的统计信息滞后，导致优化器做出错误执行计划。

落地优化方案 #

1. 强制指定索引 #

直接告诉优化器使用activityOverview索引，避免选错执行计划：

select
    `xyz`.*
from
    xyz FORCE INDEX (activityOverview)
where
    `xyz`.`fk_campaign_id` = 95870
    and `xyz`.`voided` = 0
order by
    `registration_id` desc
limit 10 offset 0

2. 改用基于主键的范围分页（推荐，适配仅下一页场景） #

放弃OFFSET，利用上一页最后一条记录的registration_id做范围查询，彻底消除偏移量带来的无效扫描：

-- 第一页查询
select
    `xyz`.*
from
    xyz FORCE INDEX (activityOverview)
where
    `xyz`.`fk_campaign_id` = 95870
    and `xyz`.`voided` = 0
order by
    `registration_id` desc
limit 10;

-- 下一页查询（假设上一页最后一条的registration_id为280614500）
select
    `xyz`.*
from
    xyz FORCE INDEX (activityOverview)
where
    `xyz`.`fk_campaign_id` = 95870
    and `xyz`.`voided` = 0
    and `registration_id` < 280614500
order by
    `registration_id` desc
limit 10;

这种方式直接利用索引的有序性定位数据，无需扫描OFFSET对应的行数，性能提升显著。

3. 更新表统计信息 #

手动更新统计信息，让优化器能准确评估索引成本：

ANALYZE TABLE xyz;

4. 清理表碎片（低峰期操作） #

大表长期写入删除会导致数据碎片化，执行以下命令重建表（锁表，需业务低峰期操作）：

ALTER TABLE xyz ENGINE=InnoDB;

验证方法 #

执行EXPLAIN查看执行计划，确认：

• key字段为activityOverview
• type字段为ref或range
• Extra字段包含Using index condition

同时对比优化前后的执行耗时，确认性能提升。

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