MySQL深度分页（例如 LIMIT 1000000, 20）性能差的主要原因在于 OFFSET 需要扫描并跳过大量数据，即使这些数据最终并不返回。随着 OFFSET 增大，性能会急剧下降。

以下是优化深度分页的常用策略，根据场景选择最适合的方案：

🛠 1. 使用覆盖索引 + 延迟关联 (最常用且有效)

核心思想:
- 先利用覆盖索引快速找到目标分页行的主键（避免回表）。
- 再根据这些主键回表关联获取完整的行数据。

优化前 (性能差):

SELECT * FROM your_table ORDER BY sort_column LIMIT 1000000, 20;

优化后:

SELECT t.*
FROM your_table t
JOIN (SELECT id -- 只选择主键FROM your_tableORDER BY sort_column -- 确保有 (sort_column, id) 或类似索引LIMIT 1000000, 20
) AS tmp ON t.id = tmp.id; -- 通过主键关联回原表

为什么有效:
- 子查询 SELECT id ... LIMIT 1000000, 20 利用了覆盖索引（仅包含 sort_column 和 id 的索引）。数据库引擎只需扫描索引结构就能找到这 20 行的 ID，速度非常快（索引通常比表数据小得多，且在内存中可能性高）。
- 外层查询 SELECT t.* ... 只需要精确地根据这 20 个 ID 回表查询完整数据，效率极高。
关键:
- 必须创建合适的索引: 通常是 (sort_column, id) 或 (sort_column, other_columns_in_where)。确保子查询能够使用覆盖索引。如果 sort_column 本身是主键或唯一索引，直接用 (sort_column) 即可。
- 适用于排序字段相对稳定的情况。

🔍 2. 基于游标/连续分页 (Cursor-based Pagination / Keyset Pagination)

核心思想: 放弃使用 OFFSET，改为记住上一页最后一条记录的排序字段值（或多个字段值），作为下一页的起始点。

优化前:

-- Page 1
SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 20;
-- Page 2 (慢!)
SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 20;

优化后:

-- Page 1
SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 20;
-- 假设最后一条记录: created_at = '2023-10-25 14:30:00', id = 12345
-- Page 2 (快!)
SELECT * FROM orders
WHERE (created_at < '2023-10-25 14:30:00') OR(created_at = '2023-10-25 14:30:00' AND id < 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

为什么有效:
- 完全避免了 OFFSET 的扫描跳过操作。
- 查询利用了 (created_at DESC, id DESC) 索引进行高效的范围查找，只扫描需要的行。
关键:
- 需要一个唯一且稳定的排序键: 通常使用时间戳（如 created_at）或自增主键（如 id），或者它们的组合（如上例，防止 created_at 重复时顺序不确定）。
- 适用于连续浏览场景: 如无限滚动、上一页/下一页导航。不支持直接跳转到任意页码。
- 需要客户端存储"游标"（即上一页最后记录的排序键值）。
- 处理新增/删除数据时顺序变化相对稳定（取决于排序键）。

📊 3. 预先计算 & 物化视图 (Precomputation & Materialized Views)

核心思想: 对于复杂查询或聚合分页，将结果预先计算并存储在一个专门的分页表或物化视图中。
实现:
- 创建一个新表，包含原始表的主键、排序字段、以及其他分页需要的聚合/计算字段。
- 使用定时任务（Cron, Event Scheduler）或触发器（谨慎使用，性能开销大）或变更数据捕获（CDC）来维护这个表。
- 对这个新表进行分页查询（可以使用延迟关联或游标）。
为什么有效:
- 将复杂查询的开销分摊到预计算阶段。
- 分页查询的目标表更小、结构更简单、索引更优化。
适用场景:
- 报表分页、需要复杂聚合的分页、数据相对静态或可以接受一定延迟的场景。
- 不适合需要实时最新数据的场景。

🔄 4. 分区 (Partitioning)

核心思想: 将大表物理分割成更小的、更易管理的片段（分区）。分页查询可以限定在特定分区内进行。

实现:

按范围（如 created_at 年份、月份）或列表（如 region）分区。
在查询中显式指定分区或利用分区剪裁（WHERE 条件匹配分区键）。

-- 假设按年份分区
SELECT * FROM your_table PARTITION (p2023)
ORDER BY sort_column
LIMIT 1000000, 20; -- 即使有 OFFSET, 但扫描的数据量仅限 2023 分区

为什么有效:
- 显著减少单次查询需要扫描的数据量（从全表扫描变为分区扫描）。
关键:
- 分区键的选择至关重要，必须与分页查询的 WHERE 条件或排序强相关才能有效剪裁。
- 分区本身不能解决分区内深度分页的 OFFSET 问题，分区内数据量过大时仍需结合延迟关联或游标。
- 分区管理和维护有额外开销。

⚙️ 5. 其他考虑与权衡

避免 SELECT *: 只查询需要的列，减少数据传输和内存占用。
优化 WHERE 条件: 尽可能缩小初始数据集。有效的 WHERE 条件是所有优化的基础。
前端/产品设计:
- 限制可访问的页数（例如，只允许访问前 100 页）。
- 鼓励使用搜索/过滤缩小结果集，而不是无限制翻页。
- 对于"跳转到最后一页"这种需求，考虑显示总条目数并提供输入框跳转，但实现时可能需要估算或缓存总数。
分库分表 (Sharding): 终极方案，当单机容量和性能达到极限时。将数据分散到多个物理数据库/表中。分页查询会变得非常复杂，通常需要中间件或应用层聚合。
缓存: 对特定查询模式（如热门的前几页）进行结果缓存。