过滤后键集分页性能优化方案咨询

问题分析 #

你当前的查询存在两个核心问题：

1. 逻辑条件偏差：原WHERE子句逻辑为(title = 'some title' AND time < 10) OR (time = 10 AND id > 5)，后半部分分页条件未限制title，不符合「按title过滤后分页」的需求。
2. 索引适配性不足：现有两个索引无法高效支撑目标查询：
   • idx_title仅能过滤title，后续分页条件需回表处理，性能低下；
   • idx_time_id会先按time/id过滤再匹配title，当符合time/id条件的记录量较大时，会出现大量无效扫描，甚至触发全表扫描。

优化步骤 #

1. 修正查询逻辑 #

将分页条件整体与title过滤条件做AND关联，确保返回记录均符合title要求：

EXPLAIN SELECT id, time, title 
FROM test
WHERE title = 'some title'
  AND (time < 10 OR (time = 10 AND id > 5))
ORDER BY time DESC, id, title
LIMIT 10;

2. 创建适配的联合覆盖索引 #

创建(title, time desc, id)的联合索引，完美匹配查询的过滤、排序和字段需求：

CREATE INDEX idx_title_time_id ON test (title, time DESC, id);

优化原理 #

• 精准过滤：索引首字段为title，可快速定位所有符合title='some title'的记录，避免无关数据扫描；
• 分页与排序复用：索引后续字段time desc, id完全匹配键集分页条件，同时与ORDER BY顺序一致，无需额外排序；
• 无回表开销：索引包含查询所需的id、time、title全部字段，属于覆盖索引，直接从索引读取数据即可，无需回表访问原表。

优化后，数据库会直接利用idx_title_time_id完成过滤、排序和数据读取，彻底避免全表扫描与无效数据处理。

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