Mysql的深度分页查询优化

原创已于 2025-04-24 15:49:56 修改 · 621 阅读

12 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

于 2025-04-24 15:42:58 首次发布

一、深度分页为什么慢？

当执行 SELECT * FROM orders ORDER BY id LIMIT 1000000, 10 时：

MySQL 会扫描前 1,000,010 行，丢弃前 100 万行，仅返回 10 行。
偏移量（offset）越大，扫描行数越多，时间复杂度为 O(N+M)（N = 偏移量，M = 每页条数）。

核心痛点：LIMIT 本质是 “跳过” 数据，而非 “直接定位”，导致全表扫描。

二、5 大优化方案（按优先级排序）

🔍 方案 1：覆盖索引 + 延迟关联（通用最优解）

原理：先通过索引定位主键，再批量回表，减少无效扫描。
实现：

sql

-- 1. 创建覆盖索引（包含排序字段+主键）
ALTER TABLE orders ADD INDEX idx_created_at_id (created_at, id);

-- 2. 优化查询（子查询只查索引，主查询回表）
SELECT o.* 
FROM orders o
INNER JOIN (
    SELECT id  -- 仅需主键（覆盖索引）
    FROM orders 
    ORDER BY created_at, id  -- 利用索引排序
    LIMIT 1000000, 10  -- 仅扫描 10 行索引
) AS tmp ON o.id = tmp.id;

效果：扫描行数从 1,000,010 降至 10，速度提升 10 万倍！
适用场景：所有带排序的分页（如订单、日志）。

📌 方案 2：游标分页（适合连续翻页）

原理：记录上一页最后一条的 id，避免 OFFSET。
示例：

sql

-- 第 1 页（初始查询）
SELECT * FROM orders ORDER BY id DESC LIMIT 10;  -- 假设最后一条 id=1000

-- 第 2 页（基于游标）
SELECT * 
FROM orders 
WHERE id < 1000  -- 定位起始点
ORDER BY id DESC 
LIMIT 10;  -- 仅扫描 10 行

优点：时间复杂度 O(M)，与页码无关；适合无限滚动（如抖音刷视频）。
缺点：不支持随机跳页（如直接跳转到第 1000 页）。

🗂️ 方案 3：范围分片（依赖有序数据）

原理：按时间 / ID 分段，缩小查询范围。
示例：

sql

-- 按月份分区查询（假设数据按月递增）
SELECT * 
FROM orders 
WHERE created_at BETWEEN '2023-10-01' AND '2023-10-31'
ORDER BY id 
LIMIT 10;

适用场景：时间序列数据（如报表、日志），用户可接受 “按范围筛选”。

💡 方案 4：预计算分页（冷数据专用）

原理：提前计算分页书签，存入汇总表。
实现：

sql

-- 创建汇总表（每日凌晨更新）
CREATE TABLE order_pager (
  page_num INT PRIMARY KEY,
  min_id BIGINT,
  max_id BIGINT
);

-- 插入分页书签（每页 100 条）
INSERT INTO order_pager
SELECT 
  FLOOR((id-1)/100) + 1 AS page_num,
  MIN(id) AS min_id,
  MAX(id) AS max_id
FROM orders
GROUP BY page_num;

-- 查询第 1000 页
SELECT * 
FROM orders 
WHERE id BETWEEN (SELECT min_id FROM order_pager WHERE page_num=1000) 
              AND (SELECT max_id FROM order_pager WHERE page_num=1000)
LIMIT 100;

优点：查询时间稳定，适合历史数据（如年报）；缺点：存储冗余。

🚀 方案 5：Elasticsearch 兜底（海量数据）

原理：利用 ES 的 search_after 避免深度分页性能衰减。
示例：

json

GET /orders/_search
{
  "size": 10,
  "sort": ["_id"],  -- 按主键排序
  "search_after": [1000000],  -- 上一页最后一条的 _id
  "query": { ... }
}

适用场景：百万级以上数据，需复杂查询（如全文搜索）。

三、索引设计黄金法则

场景	索引建议	示例
时间 + 主键排序	复合索引（时间，主键）	`(created_at, id)`
多条件过滤 + 排序	最左匹配索引（WHERE > ORDER BY）	`(status, created_at, id)`
字符串排序	前缀索引（平衡长度与选择性）	`name(20)` （取前 20 字符）

⚠️ 注意：索引非越多越好，单表索引不超过 5 个，避免冗余。

四、方案对比与选择指南

方案	时间复杂度	适用场景	推荐指数
覆盖索引 + 延迟关联	O(M)	通用场景（90% 业务首选）	⭐⭐⭐⭐⭐
游标分页	O(M)	连续翻页（如 App 列表）	⭐⭐⭐⭐
预计算分页	O(1)	冷数据历史查询	⭐⭐⭐
Elasticsearch	O(logN)	海量数据 + 复杂查询	⭐⭐⭐⭐

决策树：

数据量 < 10 万：直接 LIMIT，无需优化。
10 万～100 万：方案 1（覆盖索引）。
100 万～1000 万：方案 1 + 方案 2（游标）。
千万级以上：方案 5（ES）+ 方案 1 兜底。

五、实战避坑指南

** 避免 SELECT ***：只查必要字段，减少回表数据量。

监控执行计划：

sql

EXPLAIN SELECT * FROM orders ...;  -- 重点看 `rows` 列，理想值接近 LIMIT 数量。

慢查询日志：捕获真实慢查询（如 LIMIT 500000,10 超过 1 秒）。
业务妥协：限制最大分页深度（如 App 最多显示 500 页），避免极端场景。

总结

深度分页的核心优化思路是 “跳过扫描，直接定位”：

热数据：用覆盖索引 + 延迟关联，确保每次查询只扫描少量索引。
冷数据：预计算分页或归档到 ES，牺牲空间换时间。
业务层：结合游标分页和分页深度限制，避免数据库被拖垮。

通过这一套组合拳，可将深度分页的耗时从 “秒级” 优化到 “毫秒级”，轻松应对百万级数据分页！