数据库分页查询：避免性能陷阱的技巧
数据库分页查询是许多应用中的基础功能，但不当实现可能导致严重的性能问题。本文直接剖析常见陷阱，并提供实用技巧，助你优化查询效率。
理解分页查询的性能核心
分页查询的本质是从大型数据集中提取一小部分结果。常见方法如LIMIT/OFFSET，在数据量少时看似高效，但当偏移量增大时，数据库仍需扫描并丢弃前N行，导致延迟飙升。例如，查询第100000页时，即使只返回20条记录，数据库可能处理了200万行数据。
避免性能陷阱的第一步是认清：分页不单是“取数据”，而是“跳过数据”。每次跳过都消耗I/O和CPU资源。因此，设计分页策略时，必须优先考虑如何最小化扫描范围。
陷阱一：无视大偏移量的OFFSET
为何OFFSET会成为瓶颈？
在MySQL或PostgreSQL中，LIMIT 20 OFFSET 1000000会强制数据库从表开头遍历到偏移位置，即便索引存在，也需处理大量行。这种“跳过模式”在社交Feed或日志查询中尤为危险，用户翻页越深，响应越慢。
**解决方案**：用“键集分页”（Keyset Pagination）替代OFFSET。通过记录上一页最后一条记录的唯一键（如ID或时间戳），直接定位起始点。例如：SELECT * FROM posts WHERE id > last_id ORDER BY id LIMIT 20。这种方法让查询始终聚焦于少量数据，避免了扫描无关行。
陷阱二：忽略索引覆盖与排序一致性
索引缺失如何拖慢分页？
分页查询常伴随ORDER BY，若排序字段无索引，数据库需执行全表排序，耗时剧增。更隐蔽的问题是：当分页参数（如时间戳）与索引字段不匹配时，数据库可能回表获取数据，导致性能雪崩。
**优化技巧**：为分页查询创建复合索引，覆盖WHERE条件、ORDER BY字段和SELECT列。例如，按时间倒序分页，建索引(created_at, id)，并确保查询只返回索引包含的列。这能避免回表，让查询在内存中快速完成。
陷阱三：盲目使用COUNT(*)计算总页数
为何COUNT(*)会成为性能杀手？
许多前端分页器需要显示总页数，开发者常执行SELECT COUNT(*) FROM table。对于大表，这需要全表扫描或索引统计，可能比分页查询本身还慢。特别是在写密集场景下，统计结果还会因并发更新而不准确。
**更优方案**：若总行数非必须，可改用“加载更多”模式，仅返回“是否有下一页”的布尔值。例如，查询LIMIT 21，若返回21行，则说明有下一页。若必须显示总数，可依赖数据库的近似统计值（如MySQL的SHOW TABLE STATUS），或缓存计数结果并定期更新。
陷阱四：忽视数据库特性与查询上下文
不同数据库的差异如何应对？
不同数据库对分页的优化差异明显。例如，PostgreSQL的WITH TIES特性可避免分页重复行，但可能增加复杂度；MongoDB的skip()与OFFSET类似，同样有偏移陷阱。此外，在OLTP（在线事务处理）场景中，频繁分页会加剧锁竞争。
**实践建议**：根据数据库类型选择策略。对MySQL，优先使用键集分页并配合覆盖索引；对PostgreSQL，可考虑游标分页；对NoSQL数据库，依赖其原生分页API（如文档ID范围）。同时，将分页查询放在只读副本上执行，避免影响主库写性能。
总结：从根源避免分页陷阱
数据库分页查询的性能优化，核心在于减少不必要的数据扫描。避免OFFSET大偏移量、确保索引覆盖排序、精简计数逻辑、适配数据库特性，这四步能显著提升响应速度。实际开发中，建议先评估数据规模与访问模式，再选择分页策略——对于百万级数据，键集分页几乎总是优于传统偏移量方法。记住，分页设计的本质不是“取第N页”，而是“从上次位置继续”。