背景在批量任务系统中经常需要批量查询数据库数据并落地写入文件用于批量处理等场景。项目初期直接采用经典 Limit Offset 分页方式遍历数据、批量落地文件。但在百万级数据场景下随着 Offset 偏移量越来越大数据库需要前置扫描、丢弃大量无效数据同时存在索引过滤不全、回表、排序等问题导致单页查询耗时较高批量导出整体效率低下。为彻底优化批量导出性能因此针对同一场景设计了方案对比业务背景数据表存量百万级明细数据单批处理数据量五万条数据查询条件no_id、type、state排序字段主键 seq唯一有序现有索引主键 seq、普通索引 no_id这里需要重点说明一个坑业务批量入库时同一批次部分数据的 create_time 会出现完全一致无法保证排序唯一性因此不能使用 create_time 作为分页排序字段只能依赖主键 seq 做有序分页。模拟场景场景一普通单索引 Offset 分页索引结构主键seq 普通索引 no_id执行 SQLselect * from a where no_id ‘?’ and type ‘?’ and state ‘?’ order by seq limit 1000 offset ?实际耗时单页平均 200ms问题原理分析该语句仅 no_id 命中索引type、state 无法通过索引过滤只能先通过 no_id 索引检索数据回表获取完整数据后再在内存中做二次条件过滤。同时由于where条件未完全命中索引排序无法走索引有序性。叠加 Offset 深度分页的偏移损耗每次查询需要扫描并丢弃大量前置数据整体查询效率最差。场景二联合索引优化 Offset 分页索引结构新建联合索引 (no_id,type,state,seq)执行 SQLselect * from a where no_id ‘?’ and type ‘?’ and state ‘?’ order by seq limit 1000 offset ?实际耗时单页平均100ms优化点与残留问题通过建立最左前缀联合索引让 where 查询条件全部命中索引且排序字段 seq 放在索引末尾查询可直接复用索引有序性彻底消除 Filesort大幅减少排序开销。但依然存在性能短板由于使用 select *索引无法覆盖全部字段查询完成索引过滤后仍需要回表查询聚簇索引获取完整数据且 Offset 深度分页的“前置扫描丢弃数据”问题依旧存在因此性能有所提升。场景三主键游标分页优化核心思路舍弃 Offset 分页采用 主键游标分页执行 SQLselect * from a where no_id ‘?’ and seq ‘?’ and type ‘?’ and state ‘?’ order by seq limit 1000实际耗时单页平均 30ms优化原理利用主键 seq 全局唯一、天然有序、索引层级极低的特性使用 seq 上一页最大值 的游标方式替代 Offset 偏移分页。数据库无需扫描前置数据、无需丢弃无效数据可精准定位分页起始位置。同时结合索引条件过滤查询条件命中率高、无需额外排序虽然 select * 仍存在少量回表开销但彻底解决了 深度分页偏移损耗 文件排序 两大核心性能问题在百万级数据批量导出场景下性能达到较优。优化总结在百万级数据批量查询、文件导出场景中通过三组方案对比可以得出优化结论普通 Offset 分页条件索引缺失会导致回表过滤、Filesort 排序深度偏移会大幅拖慢查询。联合索引全条件索引命中可消除文件排序、提升过滤效率但无法解决 Offset 深度分页的本质问题。主键游标分页依托有序主键精准定位数据无偏移浪费、无额外排序在保证业务逻辑正确的前提下性能提升数倍适合批量落地文件、批量数据处理场景。
)
