摘要："系统又卡了！" 凌晨三点，运维小李的手机突然响起。排查后发现，某个用户在查询商品列表时，一口气翻到了第 1000 页，导致数据库 CPU 瞬间飙升到 100%，整个服务响应超时。
"系统又卡了！" 凌晨三点，运维小李的手机突然响起。排查后发现，某个用户在查询商品列表时，一口气翻到了第 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 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 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> {}); } // 缓存未命中，从数据库查询 int offset = (pageNum - 1) * pageSize; String sql = "SELECT * FROM products WHERE category_id = ? ORDER BY create_time DESC LIMIT ? OFFSET ?"; List 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_timeJava 代码示例：
// 初始查询（第一页）public PageResult getFirstPageProducts(int categoryId, int pageSize) { String sql = "SELECT * FROM products WHERE category_id = ? " + "ORDER BY create_time DESC, id DESC LIMIT ?"; List products = jdbcTemplate.query(sql, new Object{categoryId, pageSize}, new BeanPropertyRowMapper(Product.class) ); return createPageResult(products);}// 后续页查询（需要上一页的最后一条记录作为游标）public PageResult getNextPageProducts(int categoryId, int pageSize, Timestamp lastCreateTime, long lastId) { String sql = "SELECT * FROM products WHERE category_id = ? " + "AND (create_time products = jdbcTemplate.query(sql, new Object{categoryId, lastCreateTime, lastCreateTime, lastId, pageSize}, new BeanPropertyRowMapper(Product.class) ); return createPageResult(products);}// 创建分页结果，包含下一页所需的游标信息 private PageResult createPageResult(List products) { PageResult 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 { private List data; private boolean hasNext; private Timestamp lastCreateTime; private long lastId; // getter 和 setter 省略}游标分页的优点是性能稳定，无论翻到多少页，查询效率都基本一致。但它也有局限性，比如不支持直接跳转到指定页码，只适合 "上一页 / 下一页" 的场景。
没有放之四海而皆准的优化方案，需要根据具体业务场景选择合适的优化策略。例如：
来源：墨码行者
