如何为两列值相同的OR查询创建索引优化分页性能？

关于OR子句的索引方案 #

你不能直接创建单一索引来高效覆盖这个OR条件查询，但可以通过创建两个联合索引让数据库利用「索引合并（Index Merge）」策略加速查询，具体索引语句如下：

-- 覆盖source_location_id过滤+created_at排序
CREATE INDEX idx_transfers_source_created ON transfers (source_location_id, created_at DESC);
-- 覆盖destination_location_id过滤+created_at排序
CREATE INDEX idx_transfers_dest_created ON transfers (destination_location_id, created_at DESC);

这两个索引的作用：

• 每个索引直接匹配查询中的一个过滤条件，同时包含排序字段created_at DESC，数据库能快速从两个索引中筛选出符合条件的记录
• 索引本身已按created_at DESC排序，合并后的结果集无需额外全量排序，大幅降低CPU和IO开销
• 数据库会自动合并两个索引的结果并去重（同一条记录若同时满足source和destination为123，只会返回一次，与原OR查询逻辑一致）

进一步优化：覆盖索引减少回表 #

如果查询不需要返回所有字段（原查询用了SELECT *），可以把需要的字段加入索引做成覆盖索引，避免回表查询主键索引的开销：

• MySQL示例（将需要的非索引字段追加到索引末尾）：

CREATE INDEX idx_transfers_source_created_covering ON transfers (source_location_id, created_at DESC, product_id, quantity);
CREATE INDEX idx_transfers_dest_created_covering ON transfers (destination_location_id, created_at DESC, product_id, quantity);

• PostgreSQL示例（用INCLUDE语法实现覆盖索引）：

CREATE INDEX idx_transfers_source_created_covering ON transfers (source_location_id, created_at DESC) INCLUDE (product_id, quantity);

解决分页性能问题：替换OFFSET为游标分页 #

原查询OFFSET 400 LIMIT 100在2000万数据的表中效率极低，因为数据库需要扫描并跳过前400条记录。推荐改用游标分页（Keyset Pagination）：记住上一页最后一条记录的created_at和id（避免created_at重复导致分页错误），直接定位到下一页起始位置：

SELECT * 
FROM transfers 
WHERE (source_location_id = 123 OR destination_location_id = 123)
  AND (created_at < '上一页最后记录的created_at' OR (created_at = '上一页最后记录的created_at' AND id < '上一页最后记录的id'))
ORDER BY created_at DESC, id DESC
LIMIT 100

这种方式能完全利用之前创建的联合索引，直接定位目标数据，分页性能不受偏移量大小影响，且大多数ORM都支持该查询方式（仅需在应用层记录上一页的游标值）。

额外检查 #

确保数据库开启了索引合并功能（比如MySQL的optimizer_switch中index_merge参数默认开启，若未开启可通过SET optimizer_switch='index_merge=on'临时开启，或修改配置文件永久生效）。

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