MySQL 深度分页优化指南：告别查询卡顿，拥抱高效数据检索

抖音热门 2025年09月07日 17:09 1 admin

SQL 引言：MySQL 深度分页之困

"系统又卡了！" 凌晨三点，运维小李的手机突然响起。排查后发现，某个用户在查询商品列表时，一口气翻到了第 1000 页，导致数据库 CPU 瞬间飙升到 100%，整个服务响应超时。

这种场景在实际开发中并不罕见。随着业务数据量增长，分页查询尤其是 "翻页到很后面" 的深度分页操作，往往成为数据库性能瓶颈。本文就带你彻底搞懂 MySQL 深度分页的优化之道，让你的系统告别卡顿，即使面对百万级数据也能从容应对。

一、深度分页问题剖析

（一）什么是深度分页

分页是 Web 开发中不可或缺的功能，当数据量超过单页展示上限时，我们通常会使用分页来提升用户体验。在 MySQL 中，最常见的分页方式是使用 LIMIT 和 OFFSET ：

SELECT * FROM products WHERE category_id = 1 ORDER BY create_time DESC LIMIT 10 OFFSET 10000;

这段 SQL 的意思是：从分类 ID 为 1 的商品中，按创建时间倒序排列，跳过前 10000 条，取 10 条记录。这就是典型的分页查询，而当 OFFSET 的值很大（比如超过 10000）时，我们就称之为 "深度分页"。

在数据量不大的情况下，这种查询方式简单高效，但当表中数据达到百万甚至千万级别时，深度分页就会带来严重的性能问题。

（二）深度分页问题的根源

要理解深度分页的性能问题，我们需要先了解 MySQL 是如何执行带 LIMIT 和 OFFSET 的查询的：

MySQL 会先根据WHERE条件过滤出符合条件的记录
然后按照ORDER BY指定的字段进行排序
接着跳过OFFSET指定的行数
最后返回LIMIT指定的行数

问题就出在第 3 步。当 OFFSET 的值很大时，比如 OFFSET 100000 ，MySQL 需要先扫描并排序出前 100010 条记录，然后丢弃前 100000 条，只返回最后 10 条。这就像你要从一本 10 万页的书中找最后 10 页，却不得不先把前面 10 万页都翻一遍，效率极低。

（三）深度分页常见性能问题

扫描数据量大 ：随着OFFSET增大，需要扫描和排序的数据量急剧增加，IO 和 CPU 消耗显著上升
排序性能下降 ：大量数据排序可能导致临时表和文件排序，进一步降低性能
内存消耗增加 ：排序操作需要大量内存，可能引发内存不足或频繁的内存交换
用户体验差 ：查询耗时过长，页面加载缓慢，甚至出现超时

某电商平台的真实案例显示，当商品表数据达到 500 万时，LIMIT 10 OFFSET 100000 的查询耗时达到了惊人的 4.8 秒，而优化后仅需 0.03 秒，性能提升了 160 倍！

二、深度分页优化策略详解

（一）索引优化

索引是提升查询性能的基础，对于分页查询更是如此。为查询中涉及的过滤字段和排序字段创建合适的索引，可以显著减少扫描和排序的开销。

优化前 ：没有合适的索引，查询需要全表扫描和排序

优化后 ：创建包含过滤和排序字段的复合索引

-- 为 category_id 和 create_time 创建复合索引 CREATE INDEX idx_category_create_time ON products(category_id, create_time);

注意：索引的字段顺序很重要，应将过滤性好（选择性高）的字段放在前面。

（二）使用覆盖索引

覆盖索引是指索引包含了查询所需的所有字段，这样 MySQL 就不需要回表查询数据，直接从索引中就能获取所需信息，大大提升查询效率。

优化前 ：ELECT * 需要回表查询所有字段

优化后 ：只查询需要的字段，且这些字段都包含在索引中

-- 创建包含所需字段的覆盖索引 CREATE INDEX idx_category_create_time_id_name_price ON products(category_id, create_time, id, name, price);-- 使用覆盖索引的查询 SELECT id, name, price, create_time FROM products WHERE category_id = 1 ORDER BY create_time DESC LIMIT 10 OFFSET 100000;

（三）延迟关联（子查询优化）

延迟关联是指先通过子查询获取符合条件的主键 ID，再通过主键关联查询完整数据。这种方式可以减少排序的数据量，提高查询效率。

优化前 ：直接查询所有字段并排序

优化后 ：先查主键，再关联查详情

-- 延迟关联优化 SELECT p.* FROM products pINNER JOIN ( SELECT id FROM products WHERE category_id = 1 ORDER BY create_time DESC LIMIT 10 OFFSET 100000) AS sub ON p.id = sub.idORDER BY p.create_time DESC;

（四）适当的分页限制

大多数用户不会翻到非常靠后的页面，因此可以设置一个合理的最大分页限制，当超过这个限制时提示用户使用其他方式（如搜索）查找内容。

private static final int MAX_PAGE_NUM = 1000; // 最大允许的页码 public List<Product> getProductsWithPageLimit(int categoryId, int pageNum, int pageSize) { // 检查页码是否超过最大限制 if (pageNum > MAX_PAGE_NUM) { throw new IllegalArgumentException("页码过大，请使用搜索功能查找特定内容"); } int offset = (pageNum - 1) * pageSize; String sql = "SELECT * FROM products WHERE category_id = ? ORDER BY create_time DESC LIMIT ? OFFSET ?"; return jdbcTemplate.query(sql, new Object[]{categoryId, pageSize, offset}, new BeanPropertyRowMapper<>(Product.class) );}

（五）基于业务逻辑的预加载

对于热门数据或访问频率高的分页内容，可以提前计算并缓存结果，当用户查询时直接从缓存获取，避免频繁查询数据库。

Java 代码示例（使用 Redis 缓存） ：

@Autowiredprivate StringRedisTemplate redisTemplate;public List<Product> getProductsWithCache(int categoryId, int pageNum, int pageSize) { String cacheKey = "products:category:" + categoryId + ":page:" + pageNum; // 尝试从缓存获取 String cachedData = redisTemplate.opsForValue().get(cacheKey); if (cachedData != null) { return new ObjectMapper().readValue(cachedData, new TypeReference<List<Product>>() {}); } // 缓存未命中，从数据库查询 int offset = (pageNum - 1) * pageSize; String sql = "SELECT * FROM products WHERE category_id = ? ORDER BY create_time DESC LIMIT ? OFFSET ?"; List<Product> products = jdbcTemplate.query(sql, new Object[]{categoryId, pageSize, offset}, new BeanPropertyRowMapper<>(Product.class) ); // 存入缓存，设置过期时间（如10分钟） redisTemplate.opsForValue().set(cacheKey, new ObjectMapper().writeValueAsString(products), 10, TimeUnit.MINUTES); return products;}

（六）游标分页（基于主键或时间分页）

游标分页是一种更高效的分页方式，它使用上一页的最后一条记录的某个字段（通常是主键或时间戳）作为 "游标"，来获取下一页数据。这种方式避免了使用 OFFSET ，查询性能更加稳定。

优化前 ：使用 OFFSET 进行分页

优化后 ：使用游标进行分页

-- 游标分页查询（假设上一页最后一条记录的 ID 是 lastId，创建时间是 lastCreateTime）SELECT * FROM products WHERE category_id = 1 AND (create_time < ? OR (create_time = ? AND id < ?))ORDER BY create_time DESC, id DESCLIMIT 10;

Java 代码示例：

// 初始查询（第一页）public PageResult<Product> getFirstPageProducts(int categoryId, int pageSize) { String sql = "SELECT * FROM products WHERE category_id = ? " + "ORDER BY create_time DESC, id DESC LIMIT ?"; List<Product> products = jdbcTemplate.query(sql, new Object[]{categoryId, pageSize}, new BeanPropertyRowMapper<>(Product.class) ); return createPageResult(products);}// 后续页查询（需要上一页的最后一条记录作为游标）public PageResult<Product> getNextPageProducts(int categoryId, int pageSize, Timestamp lastCreateTime, long lastId) { String sql = "SELECT * FROM products WHERE category_id = ? " + "AND (create_time < ? OR (create_time = ? AND id < ?)) " + "ORDER BY create_time DESC, id DESC LIMIT ?"; List<Product> products = jdbcTemplate.query(sql, new Object[]{categoryId, lastCreateTime, lastCreateTime, lastId, pageSize}, new BeanPropertyRowMapper<>(Product.class) ); return createPageResult(products);}// 创建分页结果，包含下一页所需的游标信息 private PageResult<Product> createPageResult(List<Product> products) { PageResult<Product> result = new PageResult<>(); result.setData(products); if (!products.isEmpty()) { Product lastProduct = products.get(products.size() - 1); result.setHasNext(true); result.setLastCreateTime(lastProduct.getCreateTime()); result.setLastId(lastProduct.getId()); } else { result.setHasNext(false); } return result;}// 分页结果类 public static class PageResult<T> { private List<T> data; private boolean hasNext; private Timestamp lastCreateTime; private long lastId; // getter 和 setter 省略}

游标分页的优点是性能稳定，无论翻到多少页，查询效率都基本一致。但它也有局限性，比如不支持直接跳转到指定页码，只适合 "上一页 / 下一页" 的场景。