MySQL深度分页优化实战指南

最新推荐文章于 2025-11-25 12:08:46 发布

原创最新推荐文章于 2025-11-25 12:08:46 发布 · 1k 阅读

22 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

数据库专栏收录该内容

95 篇文章

订阅专栏

开篇语

哈喽，各位小伙伴们，你们好呀，我是喵手。运营社区：C站/掘金/腾讯云/阿里云/华为云/51CTO；欢迎大家常来逛逛

今天我要给大家分享一些自己日常学习到的一些知识点，并以文字的形式跟大家一起交流，互相学习，一个人虽可以走的更快，但一群人可以走的更远。

我是一名后端开发爱好者，工作日常接触到最多的就是Java语言啦，所以我都尽量抽业余时间把自己所学到所会的，通过文章的形式进行输出，希望以这种方式帮助到更多的初学者或者想入门的小伙伴们，同时也能对自己的技术进行沉淀，加以复盘，查缺补漏。

小伙伴们在批阅的过程中，如果觉得文章不错，欢迎点赞、收藏、关注哦。三连即是对作者我写作道路上最好的鼓励与支持！

前言

在上一期中，我们聊到了如何通过合理设计索引和查询优化器提升 MySQL 查询性能。但在实际开发中，当数据量逐渐庞大，进行深度分页查询时，数据库性能瓶颈就会显现出来。深度分页，尤其是 LIMIT m, n 这样的查询，当 m 值较大时，会导致数据库查询时间急剧上升。今天，我们将深入探讨如何优化 MySQL 的 深度分页查询，以高效应对大数据量场景。

摘要

本文主要介绍 MySQL 深度分页导致性能问题的原因，并提出几种常见的优化策略，包括：子查询优化、覆盖索引优化、延迟关联优化 和 缓存结果集 等方法。通过这些策略，可以有效减少深度分页查询的性能开销，提高数据库响应速度。

深度分页性能问题分析

1. 问题背景

分页查询在 MySQL 中通常通过 LIMIT 子句实现，例如：

SELECT * FROM articles ORDER BY created_at DESC LIMIT 10000, 10;

这里 LIMIT 10000, 10 表示跳过前 10000 条数据，从第 10001 条开始取 10 条数据。
当 m 值增大时，MySQL 会先扫描并丢弃前 m 条记录，再返回后续记录，导致性能急剧下降。

2. 问题根源

数据量大：跳过的记录无法利用索引直接定位，需要逐行扫描。
排序代价高：ORDER BY 会先对数据进行排序，然后再进行分页。

深度分页优化方案

方案一：子查询优化（基于主键定位）

核心思路是利用子查询的主键索引，先定位起始数据行的主键，再进行数据查询。

优化前：

SELECT * FROM articles ORDER BY created_at DESC LIMIT 10000, 10;

优化后：

SELECT * 
FROM articles 
WHERE id >= (SELECT id FROM articles ORDER BY created_at DESC LIMIT 10000, 1)
ORDER BY created_at DESC 
LIMIT 10;

实现原理：

子查询快速找到跳过数据的 起始行 主键。
外层查询基于主键进行检索，性能显著提升。

优点：

避免扫描大量无用数据行。
高效利用主键索引。

方案二：覆盖索引优化

覆盖索引是一种 索引列包含所有查询列 的场景，可以避免回表查询，提高性能。

优化前：

SELECT id, title, created_at 
FROM articles 
ORDER BY created_at DESC 
LIMIT 10000, 10;

覆盖索引设计：

ALTER TABLE articles ADD INDEX idx_created_at (created_at, id, title);

优化后：

SELECT id, title, created_at 
FROM articles FORCE INDEX(idx_created_at)
ORDER BY created_at DESC 
LIMIT 10000, 10;

实现原理：

使用覆盖索引时，查询只在索引树上完成，避免回表查询。
FORCE INDEX 强制使用覆盖索引，提高查询效率。

优点：

大幅减少 IO 操作。
提升分页查询速度。

方案三：延迟关联优化

核心思路是先通过索引查询主键列表，再关联主表获取完整数据，减少扫描量。

优化前：

SELECT * FROM articles ORDER BY created_at DESC LIMIT 10000, 10;

优化后：

SELECT a.*
FROM articles a
JOIN (
    SELECT id 
    FROM articles 
    ORDER BY created_at DESC 
    LIMIT 10000, 10
) AS b ON a.id = b.id;

实现原理：

第一步：通过索引快速获取主键列表。
第二步：利用主键关联主表，查询完整数据。

优点：

避免对大表进行全表扫描。
分步查询提高效率。

方案四：使用缓存结果集

对于热点数据或不频繁变化的数据，可以将分页结果缓存到 Redis 等缓存系统中，减少数据库压力。

实现步骤：

分页查询时写入缓存：

SELECT * FROM articles ORDER BY created_at DESC LIMIT 10000, 10;

Redis 缓存键设计：

key: articles:page:10000
value: [json_array_of_data]

读取缓存：

data = redis.get('articles:page:10000')
if not data:
    # 缓存未命中，从数据库查询

优点：

缓存热点分页数据，提升查询速度。
减少数据库查询压力。

方案五：前端分页与限制深度

对于非必要场景，前端可以设计 滑动加载 机制，逐页请求数据，避免用户直接跳转到深度分页。

方案对比与总结

方案	适用场景	优点	缺点
子查询优化	深度分页查询	快速定位跳过的数据行	子查询设计较复杂
覆盖索引	查询列较少	避免回表，性能显著提升	需要额外维护索引
延迟关联优化	主键索引可用	降低扫描数据量	需分步查询，增加代码复杂度
缓存结果集	热点数据或低频查询	查询速度极快	数据一致性问题，需更新缓存
前端限制与优化	用户体验优先	避免不必要的深度分页	非通用，需前后端配合

测试用例

我们可以通过 MySQL 查询耗时对比来验证深度分页优化效果。

基准测试代码：

-- 原始分页查询
SELECT * FROM articles ORDER BY created_at DESC LIMIT 10000, 10;

-- 子查询优化
SELECT * 
FROM articles 
WHERE id >= (SELECT id FROM articles ORDER BY created_at DESC LIMIT 10000, 1)
ORDER BY created_at DESC 
LIMIT 10;

-- 延迟关联优化
SELECT a.* 
FROM articles a 
JOIN (
    SELECT id FROM articles ORDER BY created_at DESC LIMIT 10000, 10
) AS b ON a.id = b.id;