分页算法概览与挑战
偏移量分页（OFFSET-LIMIT）
在处理海量数据时，偏移量分页的核心原理是通过 LIMIT 与 OFFSET 来定位结果集的起始位置，然而随着 分页偏移量的增大，MySQL 需要扫描并返回大量无用行，导致扫描成本线性上升，性能下降显著。
为了把握性能，理解分页页码的访问模式很关键。通常在前几页访问较快，而后续页的响应时间会逐步拉长，这是因为数据库需要处理更多的跳过行。
SELECT id, username, created_at
FROM users
ORDER BY created_at ASC
LIMIT 10 OFFSET 1000;此外，总记录数的统计方式也影响性能，若频繁统计总页数，可能引入额外代价；在设计阶段需要权衡实时性与开销，避免把统计成本作为常态查询的瓶颈。
Keyset分页（游标分页）
Keyset 分页的核心在于以上一页的最后一条记录的排序键作为起点，避免全表扫描，从而在每一页只检索少量行。该方式更适合大规模数据的持续滚动加载。
典型模式是：WHERE 关键字段 > 上一页的键值，并且 ORDER BY 关键字段 ASC/L DESC，LIMIT N。这种写法在高并发场景下稳定性更高。
SELECT id, username, created_at
FROM users
WHERE (created_at, id) > (?, ?)
ORDER BY created_at ASC, id ASC
LIMIT 10;为了避免出现重复页，需要确保排序字段具有唯一性，或在关键字段之外增加辅助字段来实现唯一排序，以维持分页的确定性。
混合分页策略与场景适配
在某些场景中，可以采用前期使用偏移量，后续切换到 Keyset的混合策略，以兼顾实现简单性与性能稳定性。设计阶段需确保排序字段与筛选条件在索引中的对齐，从而实现更低的 I/O 与 CPU 开销。
综合来看，分页策略的选择应结合数据分布与访问模式，在热区与冷区之间采取差异化的处理，以提升整体响应速率。
索引设计在分页性能中的角色
联合索引与排序字段
对于分页查询，联合索引能够同时覆盖筛选与排序条件，从而避免回表或额外的排序开销。若查询使用 ORDER BY 与 WHERE 条件的列组合，优先创建能够覆盖这组字段的联合索引。
实践中应将排序字段置于联合索引的前列，确保索引顺序与查询的排序顺序保持一致，以实现快速定位与排序。
CREATE INDEX idx_users_created_id
ON users (created_at, id);当查询需要稳定的顺序且跨页读取时，联合索引能显著降低 回表次数，并有助于实现 覆盖查询，从而提升执行效率。
覆盖索引的价值
覆盖索引是指查询所需的所有列都在索引本身中就能返回，不需要再回表*检索行数据，这对分页查询尤为有利，因为 I/O 成本可被显著削减。
在设计时可尝试通过包含性字段或将需要返回的列尽量放入索引列中，实现查询结果的完全覆盖，从而降低磁盘访问的频率。
-- 覆盖查询示例
SELECT id, created_at
FROM users
WHERE created_at > '2024-01-01'
ORDER BY created_at ASC
LIMIT 20;如果该查询的结果列都在索引中，数据库将避免额外的行级回表，提升整体吞吐量与响应速度。
分区与数据分布对分页的影响
分区裁剪提升扫描效率
将大表按时间、范围或其他维度进行分区，可以在分页时实现分区裁剪，即只在相关分区中执行查询，减少无效数据扫描。分区裁剪的效果在大数据集下尤为显著。
在查询中通过分区约束条件，数据库会优先定位到相关分区，降低 I/O，提升响应速度；这在历史数据归档与实时热数据共存的场景中尤为关键。
SELECT id, name, created_at
FROM users
PARTITION (p_202401, p_202402)
WHERE created_at > '2024-01-01'
ORDER BY created_at ASC
LIMIT 50;需要注意的是，分区键应参与筛选条件，以确保分区裁剪的有效性，从而实现更高的查询吞吐。
分区键与索引协同
分区设计应与索引策略配合，分区键在 WHERE 条件中的利用程度决定了裁剪效率。若分区键与排序字段没有很好的对齐，分页性能仍可能受限。
在监控阶段可观察分区命中情况，若分区裁剪未达到预期，可考虑调整分区策略或重建联结的索引，以提升命中率。
ALTER TABLE users
PARTITION BY RANGE (TO_DAYS(created_at)) (PARTITION p_202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),PARTITION p_202402 VALUES LESS THAN (TO_DAYS('2024-03-01'))
);从偏移量到Keyset的迁移实战
评估现有查询
在迁移前先对现有分页查询进行分析，使用 EXPLAIN 查看执行计划，明确是否存在全表扫描、回表或排序成本高的问题。慢查询日志也能帮助定位瓶颈。
评估要点包括：扫描行数与返回行数的比值、是否存在排序前后无法利用索引、以及是否有重复分页的风险。
EXPLAIN FORMAT=JSON
SELECT id, username, created_at
FROM users
ORDER BY created_at ASC
LIMIT 10 OFFSET 1000;逐步优化步骤
第一步：引入合适的联合索引，确保排序字段与筛选条件可以覆盖；第二步：对高成本的 OFFSET 查询，尝试改写为 Keyset 分页；第三步：在必要时引入分区以实现裁剪，降低扫描范围；第四步：对查询改动进行基线监控，关注响应时间与吞吐量变化。
迁移过程中应保持原有接口的稳定性，逐步替换分页实现，以便在不影响业务的情况下逐步提升性能。
性能指标与监控要点
EXPLAIN 与慢查询日志
在评估分页性能时，持续使用 EXPLAIN 查看执行计划，并关注是否有回表、全表扫描或排序成本过高的迹象。慢查询日志可帮助定位关键问题。
结合指标如 每秒请求数、平均响应时间、命中率与缓存命中率，可以直观地观察分页性能的改善效果。
EXPLAIN SELECT id, name
FROM users
WHERE created_at > '2024-01-01'
ORDER BY created_at ASC
LIMIT 20;监控关键指标与容量规划
在持续运行阶段，关注 每页返回的行数与实际 I/O，以及 分区裁剪命中率，以确保分页查询的稳定性。将分页策略与数据库缓存策略结合，可以进一步提升重复请求的响应速度。
通过设定阈值和告警，可以在分页性能出现回落时快速定位原因，例如索引失效、统计信息过时或数据分布发生剧烈变化。
