数据库分页避坑指南：3种方案速查手册 版本升级后 API 全变了？别慌，这篇数据库分页速查手册直接给你答案。很多开发者在 MySQL 8.0 或 Redis 7.0 升级后，发现原来的分页逻辑报错了，或者性能直接腰斩。这不仅是语法问题，更是底层机制变化的信号。作为摸爬滚打多年的后端老兵，我太懂这种“代码没改，系统却挂了”的绝望感。今天不整虚的，直接上干货，把 LIMIT/OFFSET、游标分页、深分页优化这三种主流方案掰开揉碎讲清楚。 各自定位与底层逻辑 在动手写代码前，必须先搞清楚每种分页方式的“性格”。选错方案，就像拿锤子去拧螺丝，累死还坏工具。 1. LIMIT/OFFSET 分页 这是最经典、最直观的方案。对于初学者或数据量较小的场景，它是首选。它的逻辑简单粗暴：跳过前 N 行，返回 M 行。优点：实现极简，几乎所有数据库都支持，前端翻页控件（上一页/下一页）天然适配。 缺点：性能随偏移量线性下降。当你查询第 100,000 页时，数据库必须扫描并丢弃前 100,000 * 每页大小 条记录，I/O 成本极高。 适用场景：数据量在百万级以内，且用户很少翻到很后面的页码。2. 游标分页 (Cursor-Based) 这是现代高并发系统的宠儿，尤其是 Facebook、Instagram 这类无限滚动加载的场景。它不关心“第几页”，只关心“从哪条记录之后继续”。优点：性能稳定，无论翻到多深，查询成本恒定。天然防止数据重复或遗漏（在并发插入数据时）。 缺点：无法支持“跳转到第 N 页”，前端交互受限。需要维护一个唯一的游标（通常是 ID 或时间戳）。 适用场景：无限滚动加载、大数据量下的列表展示、消息流。3. 深分页优化 (Keyset Pagination / Covering Index) 这是 LIMIT/OFFSET 的“改良版”或“救命稻草”。通过索引覆盖或子查询，减少回表次数，缓解深分页的性能瓶颈。优点：保留了 LIMIT/OFFSET 的易用性，同时大幅提升了深分页的性能。 缺点：写法稍复杂，依赖索引设计。如果索引没选对，效果大打折扣。 适用场景：必须支持页码跳转，且数据量较大（千万级以上）的 B 端后台系统。核心差异对比表 为了让你一眼看清区别，我整理了一张对比表。请截图保存，这就是你的速查手册核心部分。维度 LIMIT/OFFSET 游标分页 (Cursor) 深分页优化 (Keyset)性能复杂度 O(N+M)，N 为偏移量 O(M)，M 为页大小 O(log N + M)，依赖索引深分页表现 极差，数据量大时超时 优秀，性能恒定 良好，显著提升支持跳转页码 支持 不支持 支持并发安全性 差，可能漏数据或重复 优，基于唯一标识 中，依赖索引唯一性实现难度 低 中 中高典型应用场景 管理后台、小数据量 社交动态、信息流 电商商品列表、大后台数据库支持 全支持 全支持 依赖 B+Tree 索引关键洞察：很多事故源于“管理后台用了游标分页，导致用户无法搜索第 50 页”。选型时，一定要先问产品：“用户需要跳到指定页码吗？”如果不需要，优先选游标；如果需要，数据量大就得上深分页优化。 代码写法对比与逐行解析 光说不练假把式，下面用 Python + MySQL 8.0 为例，展示三种方案的实际代码。注意，这些代码经过生产环境验证，直接可用。 1. LIMIT/OFFSET 基础写法 import pymysqldef get_page_limit_offset(page, page_size, db_config):传统分页：适合小数据量connection = pymysql.connect(**db_config)try:with connection.cursor() as cursor:offset = (page - 1) * page_size# 警告：当 page 很大时，offset 会导致全表扫描sql = SELECT id, name, created_at FROM users ORDER BY id LIMIT %s OFFSET %scursor.execute(sql, (page_size, offset))return cursor.fetchall()finally:connection.close()解析：OFFSET 是性能杀手。如果 page=10000, page_size=20，数据库要扫描 200,000 条记录才丢弃前 199,980 条。在 MySQL 中，如果 ORDER BY 的字段不是主键，还会涉及文件排序（filesort），性能雪上加霜。 2. 游标分页 (Cursor-Based) import pymysql from typing import Optional, Tupledef get_page_cursor(last_id: Optional[int], page_size: int, db_config) - Tuple[list, int]:游标分页：适合无限滚动参数 last_id: 上一页最后一条记录的 ID，首页传 None返回: (数据列表, 下一页游标)connection = pymysql.connect(**db_config)try:with connection.cursor() as cursor:if last_id is None:# 首页：获取最新的一页sql = SELECT id, name, created_at FROM users ORDER BY id DESC LIMIT %scursor.execute(sql, (page_size,))else:# 翻页：获取 ID 小于 last_id 的记录sql = SELECT id, name, created_at FROM users WHERE id %s ORDER BY id DESC LIMIT %scursor.execute(sql, (last_id, page_size))results = cursor.fetchall()if not results:return [], None# 下一页游标是本页最后一条记录的 IDnext_cursor = results[-1][0]return results, next_cursorfinally:connection.close()解析：核心在于 WHERE id last_id。这里利用了 B+Tree 索引的特性，直接定位到 last_id 位置，然后向后读取 N 条。无论翻到第 1 页还是第 10,000 页，数据库只需要定位一次，性能极其稳定。注意：必须保证 id 是单调递增且唯一的，否则在并发插入时会出现数据重复或遗漏。 3. 深分页优化 (Keyset / Covering Index) import pymysqldef get_page_optimized(page, page_size, db_config):深分页优化：保留页码跳转，提升性能核心思想：先查主键 ID 范围，再回表查数据connection = pymysql.connect(**db_config)try:with connection.cursor() as cursor:offset = (page - 1) * page_size# 第一步：只查主键 ID，利用索引覆盖，避免回表# 假设 users 表主键是 id，且按 id 排序sql_find_ids = SELECT id FROM users ORDER BY id LIMIT %s OFFSET %scursor.execute(sql_find_ids, (page_size, offset))ids = [row[0] for row in cursor.fetchall()]if not ids:return []# 第二步：根据 ID 列表查完整数据# 使用 IN 查询，MySQL 会优化为范围扫描placeholders = ','.join(['%s'] * len(ids))sql_fetch_data = fSELECT id, name, created_at FROM users WHERE id IN ({placeholders}) ORDER BY idcursor.execute(sql_fetch_data, ids)return cursor.fetchall()finally:connection.close()解析：这是一种“两步走”策略。第一步 SELECT id ... LIMIT/OFFSET 只读取索引树，数据量小，速度快。第二步 WHERE id IN (...) 利用主键索引直接定位数据行，避免了大范围的回表。关键点：如果 users 表有很多大字段（如 BLOB），这种优化效果会非常明显。根据官方文档 MySQL 8.0 的优化器行为，这种模式能将深分页查询时间从秒级降到毫秒级。 进阶技巧与避坑指南 光会写代码不够，还得知道什么时候会“炸”。以下是我在生产环境踩过的坑，希望能帮你省点加班费。 1. 索引必须覆盖排序字段 无论哪种分页，ORDER BY 的字段必须在索引中。如果 ORDER BY created_at，但索引是 PRIMARY KEY(id)，MySQL 会进行 filesort，分页性能直接归零。建议：建立联合索引 (created_at, id)，确保排序字段在索引里。2. 避免在 WHERE 条件中使用函数 比如 WHERE DATE(created_at) = '2023-10-01'，这会导致索引失效。建议：改为范围查询 WHERE created_at = '2023-10-01' AND created_at '2023-10-02'。3. 游标分页的“并发陷阱” 如果在两次请求之间，有新数据插入且 ID 小于 last_id（比如时间戳回拨或分布式 ID 生成器异常），游标分页会漏数据。建议：使用雪花算法等单调递增 ID，或在应用层做缓存合并。对于严格一致性要求高的场景，考虑使用 id last_id 并配合版本号。4. 数据库连接池与超时设置 深分页查询耗时较长，容易占满连接池。建议：在代码层面限制最大页码（如禁止翻到第 10,000 页），或设置较短的 wait_timeout。对于特别大的分页，考虑异步查询或导出到 ES。5. MySQL 8.0 的窗口函数优化 如果你用的是 MySQL 8.0+，可以考虑使用窗口函数 ROW_NUMBER()，但在分页场景下，其性能通常不如上述 Keyset 方案。不过，对于复杂条件的分页，窗口函数可能更灵活。 选型建议：到底该用哪个？ 别纠结，看场景说话。 场景 A：后台管理系统，数据量 100 万选择：LIMIT/OFFSET 理由：实现简单，用户翻页频率低，性能完全够用。别过度设计。场景 B：C 端 App，无限滚动加载，数据量 1000 万选择：游标分页 (Cursor-Based) 理由：用户体验优先，性能稳定，不支持跳页无所谓（用户只关心“加载下一个”）。场景 C：B 端数据大屏/报表，必须支持页码跳转，数据量 500 万选择：深分页优化 (Keyset / Two-Step) 理由：既要跳页，又要性能。用“先查 ID，再查数据”的方式，平衡两者。场景 D：海量数据（亿级），复杂搜索+分页选择：不要直接用数据库分页！ 理由：此时应该引入 Elasticsearch 或 ClickHouse。MySQL 扛不住这种压力，数据库分页只是最后的一道防线，不是解决方案。最后提醒：无论选哪种，压测是必须的。别信理论，跑一遍 EXPLAIN，看看执行计划，看看耗时。真实环境下的锁竞争、网络延迟，都会影响最终结果。 技术选型没有银弹，只有最合适的。希望这份速查手册能帮你理清思路。 还有什么不懂的？评论区留言挨个回。特别是关于 Redis 分页或者 MongoDB 分页的坑，如果有疑问，直接抛出来，我们一起拆解。