数据库性能实战：MySQL 千万级数据“深分页”性能崩塌与 4 种优化解法

一、 为什么深分页会拖垮数据库？

SELECT * FROM t_order 
WHERE user_id = 10086 
ORDER BY id DESC 
LIMIT 1000000, 20;

1. 扫描总量大：存储引擎需要按索引顺序扫描出前 $1,000,020$ 条记录。
   
   
2. 海量无用回表：如果查询使用了非聚簇索引（二级索引），且包含了非索引字段（如 SELECT *），引擎每扫描到一条二级索引记录，都需要拿着主键 ID 到主键聚簇索引树上做一次回表查询（Table Lookup）拉取完整行数据。
   
   
3. 内存抛弃：在 Server 层拿到这 $1,000,020$ 条完整行数据后，将前 $1,000,000$ 条全部丢弃，仅返回最后 20 条。
   
   

二、 4 种高吞吐优化方案

方案 1：延迟关联（Deferred Join）—— 压榨覆盖索引

-- 原始慢 SQL（耗时 ~ 4.2s）
SELECT * FROM t_order WHERE user_id = 10086 ORDER BY id DESC LIMIT 1000000, 20;

-- 优化后：延迟关联（耗时 ~ 0.08s）
SELECT o.* 
FROM t_order o
INNER JOIN (
    -- 子查询仅在覆盖索引树上完成百万级扫描，完全不发生回表
    SELECT id 
    FROM t_order 
    WHERE user_id = 10086 
    ORDER BY id DESC 
    LIMIT 1000000, 20
) AS tmp ON o.id = tmp.id;

• 原理解析：子查询只提取主键 id，命中了联合索引 (user_id, id) 的覆盖索引（Covering Index）特性，所有排序和过滤全在索引树上完成；外层主查询通过主键等值关联，仅发生 20 次回表，I/O 消耗骤降几个数量级。
  
  

方案 2：基于游标的主键寻址（Seek Method / Keyset Pagination）

-- 客户端记录上一页最后一条记录的 id 为 9876543
SELECT * 
FROM t_order 
WHERE user_id = 10086 AND id < 9876543 
ORDER BY id DESC 
LIMIT 20;

• 原理解析：借助 B+ 树的快速点查能力，直接跳过目标范围外的百万行数据，扫描行数恒定为 20 行，查询性能不受页码深度影响，执行耗时恒定在毫秒级。
  
  

方案 3：子查询限制起始 ID

SELECT * FROM t_order 
WHERE id <= (
    SELECT id FROM t_order WHERE user_id = 10086 ORDER BY id DESC LIMIT 1000000, 1
) 
ORDER BY id DESC 
LIMIT 20;

方案 4：架构分流与产品约束

1. 产品交互层兜底（UX 约束）：限制最大翻页深度（如电商列表页通常只允许展示前 100 页，超出部分提示“请精确搜索条件”）。
   
   
2. 读写分离与专业引擎承接：通过 Canal 或 CDC 工具将 MySQL 数据实时同步至 Elasticsearch 或 ClickHouse，利用倒排索引或列式存储高效支撑多维筛选与聚合查询。
   
   

三、 生产压测基准数据对比

四、 生产排查与落地清单

1. 检查执行计划：使用 EXPLAIN 分析 SQL 时，重点观察 Extra 字段。若出现 Using index 说明命中了覆盖索引；若出现 Using filesort 且伴随大量数据扫描，应优先调整索引覆盖范围或重写为延迟关联。
   
   
2. 防御大分页攻击：在 API 网关层严格校验前端传入的 pageSize 与 pageNo，对单次请求数据量设置硬上限（如 pageSize <= 100，pageNo * pageSize <= 10000），避免恶意爬虫爬取深层数据打崩数据库连接池。