千万级数据分页: limit 1000000, 10性能堪忧用延迟关联改写SQL引言: 一个随页码增大而崩溃的查询几乎所有开发者都写过这样的分页SQL:SELECT * FROM users ORDER BY id LIMIT 1000000, 10;前几页运行飞快但当你翻到第10万页时查询突然变得奇慢无比甚至超时。问题出在哪不是索引没用上而是LIMIT的offset机制要求数据库必须扫描并丢弃前N条数据。本节将完整拆解深分页的性能瓶颈并提供延迟关联、游标分页、覆盖索引子查询三种优化方案让你面对千万级数据也能轻松应对。一、LIMIT深分页的性能瓶颈1.1 LIMIT的工作机制-- 典型分页SQL SELECT * FROM users ORDER BY id LIMIT 1000000, 10;聚簇索引(主键)二级索引MySQL Server客户端聚簇索引(主键)二级索引MySQL Server客户端大量无效回表!loop[前1000000条]终于到达offset位置LIMIT 1000000, 10扫描id索引(如果有)回表获取完整行数据丢弃(因为offset未到)回表获取第1000001~1000010行返回10条结果1.2 性能问题的本质public class DeepPaginationProblem { public static void main(String[] args) { System.out.println( LIMIT深分页的性能瓶颈 \n); System.out.println(问题核心: 数据库必须扫描并丢弃OFFSET条记录\n); System.out.println(LIMIT 1000000, 10 的执行过程:); System.out.println(1. 通过索引扫描前1,000,010条记录); System.out.println(2. 对这1,000,010条记录全部回表); System.out.println( - 每条回表都是一次随机IO); System.out.println( - 从二级索引获取主键ID); System.out.println( - 再到聚簇索引读取完整行); System.out.println(3. 丢弃前1,000,000条); System.out.println(4. 返回最后10条\n); System.out.println(为什么慢?); System.out.println(- 100万次回表随机IO(这是最慢的操作!)); System.out.println(- 传输和丢弃100万条完整数据); System.out.println(- MySQL需要临时存储这些数据\n); System.out.println(数据量计算:); System.out.println(- 假设每行1KB); System.out.println(- 100万行 1GB数据被扫描和丢弃!); } }1.3 性能对比测试-- 创建测试表 CREATE TABLE users ( id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100), age INT, city VARCHAR(50), created_at DATETIME, INDEX idx_age (age), INDEX idx_created_at (created_at) ) ENGINEInnoDB; -- 插入1000万测试数据 -- (省略插入过程) -- 测试: 不同offset的性能对比 -- 第1页: 很快 SELECT * FROM users ORDER BY id LIMIT 0, 10; -- 执行时间: ~0.001s -- 第1000页: 还能接受 SELECT * FROM users ORDER BY id LIMIT 10000, 10; -- 执行时间: ~0.05s -- 第10万页: 明显变慢 SELECT * FROM users ORDER BY id LIMIT 1000000, 10; -- 执行时间: ~2.5s -- 第100万页: 无法接受 SELECT * FROM users ORDER BY id LIMIT 10000000, 10; -- 执行时间: ~25s 或超时二、方案一: 延迟关联(推荐)2.1 延迟关联的原理延迟关联Step1: 只扫描主键利用覆盖索引不回表Step2: 获取10个IDStep3: 通过ID关联获取10行回表仅10次!传统LIMIT分页扫描100万行回表100万次丢弃100万行返回10行2.2 延迟关联SQL实现-- 方案1: 延迟关联(最通用) -- 原始SQL(慢): SELECT * FROM users ORDER BY id LIMIT 1000000, 10; -- 延迟关联SQL(快): SELECT u.* FROM users u INNER JOIN ( SELECT id FROM users ORDER BY id LIMIT 1000000, 10 ) AS tmp ON u.id tmp.id; -- 为什么快? -- 子查询只扫描索引(id是主键索引覆盖) -- 子查询不需要回表! -- 外层查询只对10个ID做回表2.3 性能对比-- 原始SQL: SELECT * FROM users ORDER BY id LIMIT 1000000, 10; -- 执行时间: 2.5s -- 扫描行数: 1,000,010 -- 回表次数: 1,000,010 (全部回表!) -- 延迟关联: SELECT u.* FROM users u INNER JOIN ( SELECT id FROM users ORDER BY id LIMIT 1000000, 10 ) tmp ON u.id tmp.id; -- 执行时间: 0.05s (快50倍!) -- 子查询扫描行数: 1,000,010 (只扫索引不回表) -- 外层回表次数: 10 (仅10次!) -- 使用EXPLAIN验证 EXPLAIN SELECT u.* FROM users u INNER JOIN ( SELECT id FROM users ORDER BY id LIMIT 1000000, 10 ) tmp ON u.id tmp.id; -- Extra列会显示: -- 子查询: Using index (覆盖索引) -- 外层: eq_ref (主键关联)/** * 延迟关联性能分析 */ public class DelayedJoinAnalysis { public static void main(String[] args) { System.out.println( 延迟关联性能分析 \n); System.out.println(延迟关联为什么快?\n); System.out.println(1. 子查询使用覆盖索引:); System.out.println( - SELECT id (只取主键)); System.out.println( - id上有主键索引); System.out.println( - 扫描只在索引树上完成); System.out.println( - 不需要回表!\n); System.out.println(2. 扫描成本对比:); System.out.println( 原SQL: 扫描100万行数据页(每页16KB)); System.out.println( 延迟关联: 扫描100万行索引页(只有id值)); System.out.println( 索引页可存储更多行IO更少\n); System.out.println(3. 回表成本对比:); System.out.println( 原SQL: 100万次回表); System.out.println( 延迟关联: 10次回表); System.out.println( 减少了99.999%的回表!\n); System.out.println(4. 适用条件:); System.out.println( - 必须有主键或唯一索引); System.out.println( - ORDER BY的列在索引中); System.out.println( - SELECT的列包含非索引列(需要回表)); } }三、方案二: 游标分页(推荐用于滚动加载)3.1 游标分页原理-- 方案2: 游标分页(Cursor-Based Pagination) -- 传统分页: 按页码 -- 第1页: LIMIT 0, 10 -- 第2页: LIMIT 10, 10 -- 第3页: LIMIT 20, 10 -- 游标分页: 按上一页最后一条记录的ID -- 第1页: SELECT * FROM users ORDER BY id LIMIT 10; -- 返回: id 1~10 -- 第2页: SELECT * FROM users WHERE id 10 ORDER BY id LIMIT 10; -- 返回: id 11~20 -- 第3页: SELECT * FROM users WHERE id 20 ORDER BY id LIMIT 10; -- 返回: id 21~30 -- 核心: 用 WHERE id 上一页最后ID 代替 LIMIT offset3.2 游标分页实现/** * 游标分页的代码实现 */ public class CursorPagination { public static void main(String[] args) { System.out.println( 游标分页实现 \n); System.out.println(SQL模板:); System.out.println( SELECT * FROM users); System.out.println( WHERE id ? -- 上一页最后一条的ID); System.out.println( ORDER BY id); System.out.println( LIMIT ?\n); System.out.println(优点:); System.out.println(1. 每次只扫描10行不回表100万行); System.out.println(2. 性能恒定不受页码影响); System.out.println(3. 适合无限滚动(Infinite Scroll)); System.out.println(4. 没有OFFSET机制不会丢数据或重复\n); System.out.println(缺点:); System.out.println(1. 不支持跳页(只能上一页/下一页)); System.out.println(2. 需要自增连续的主键); System.out.println(3. 如果主键不连续(删除过)需要调整逻辑); } } // 实际代码示例 // public ListUser getNextPage(Long lastId, int pageSize) { // String sql SELECT * FROM users WHERE id ? ORDER BY id LIMIT ?; // return jdbcTemplate.query(sql, userMapper, lastId, pageSize); // }3.3 游标分页的EXPLAIN验证-- 游标分页的EXPLAIN EXPLAIN SELECT * FROM users WHERE id 1000000 ORDER BY id LIMIT 10; -- 输出: -- type: range (范围扫描) -- key: PRIMARY (使用主键) -- rows: 10 (只扫描10行!) -- Extra: Using where -- 对比传统分页: EXPLAIN SELECT * FROM users ORDER BY id LIMIT 1000000, 10; -- type: ALL 或 index -- rows: 1000010 -- Extra: Using filesort (可能)四、方案三: 覆盖索引子查询4.1 使用覆盖索引避免回表-- 方案3: 覆盖索引子查询 -- 场景: 按非主键排序(如按创建时间排序) -- 有索引: idx_created_at (created_at) -- 原始SQL(慢): SELECT * FROM users ORDER BY created_at DESC LIMIT 1000000, 10; -- 覆盖索引子查询: SELECT u.* FROM users u INNER JOIN ( SELECT id, created_at FROM users ORDER BY created_at DESC LIMIT 1000000, 10 ) tmp ON u.id tmp.id; -- 注意: 子查询中需要id created_at都来自索引 -- 可以创建联合索引: idx_created_at_id (created_at, id) -- 创建优化索引 ALTER TABLE users ADD INDEX idx_created_at_id (created_at, id); -- 再次执行子查询将使用覆盖索引 EXPLAIN SELECT id, created_at FROM users ORDER BY created_at DESC LIMIT 1000000, 10; -- Extra: Using index (覆盖索引!)4.2 各种索引策略对比/** * 不同索引策略对深分页的影响 */ public class IndexStrategyComparison { public static void main(String[] args) { System.out.println( 索引策略对比 \n); System.out.println(场景1: 主键排序); System.out.println( 索引: PRIMARY KEY (id)); System.out.println( 延迟关联: SELECT u.* FROM users u); System.out.println( JOIN (SELECT id FROM users ORDER BY id LIMIT N,10) tmp); System.out.println( ON u.id tmp.id); System.out.println( 子查询使用覆盖索引(主键本身就是覆盖索引)\n); System.out.println(场景2: 非主键排序); System.out.println( 索引: idx_created_at (created_at)); System.out.println( 问题: 子查询需要id和created_at); System.out.println( 解决: 建联合索引 idx_created_at_id (created_at, id)); System.out.println( 这样子查询就能使用覆盖索引\n); System.out.println(场景3: 多条件查询); System.out.println( WHERE status1 ORDER BY created_at DESC); System.out.println( 优化索引: idx_status_created_at_id (status, created_at, id)); System.out.println( 子查询: SELECT id FROM users); System.out.println( WHERE status1); System.out.println( ORDER BY created_at DESC); System.out.println( LIMIT N,10); System.out.println( 完全使用覆盖索引!); } }五、三种方案适用场景对比5.1 方案选择决策是否只需上下翻页主键排序非主键排序有无优点优点优点深分页优化是否支持跳页?排序字段?方案2: 游标分页WHERE id lastId性能最优方案1: 延迟关联JOIN子查询通用性强是否有联合索引?方案3: 创建覆盖索引idx_sort_col_id再用延迟关联恒定性能无offset支持跳页通用性强适用任意排序但需加索引5.2 方案速查表| 方案 | 适用场景 | 性能 | 跳页 | 实现复杂度 ||------|---------|------|------|-----------|| 延迟关联 | 主键排序 任意字段 | 优秀 | 支持 | 中等 || 游标分页 | 主键连续 不需跳页 | 最优 | 不支持 | 低 || 覆盖索引子查询 | 非主键排序 | 优秀 | 支持 | 高(需建索引) |六、实际项目中的最佳实践6.1 完整的深分页解决方案/** * 完整的分页查询Service */ Service public class UserPaginationService { Autowired private JdbcTemplate jdbcTemplate; /** * 方案1: 延迟关联分页(支持跳页) */ public PageResultUser getUsersByPage(int page, int pageSize) { int offset (page - 1) * pageSize; // 延迟关联SQL String sql SELECT u.* FROM users u INNER JOIN ( SELECT id FROM users ORDER BY id LIMIT ?, ? ) tmp ON u.id tmp.id ; ListUser users jdbcTemplate.query( sql, new UserRowMapper(), offset, pageSize ); // 查询总数(如果不需要精确总数可以去掉) long total jdbcTemplate.queryForObject( SELECT COUNT(*) FROM users, Long.class ); return new PageResult(users, total, page, pageSize); } /** * 方案2: 游标分页(适合滚动加载) */ public ListUser getUsersByCursor(Long lastId, int pageSize) { String sql SELECT * FROM users WHERE id ? ORDER BY id LIMIT ? ; return jdbcTemplate.query( sql, new UserRowMapper(), lastId null ? 0L : lastId, pageSize ); } /** * 方案3: 按时间排序的分页(覆盖索引) */ public PageResultUser getUsersByTime(int page, int pageSize) { int offset (page - 1) * pageSize; // 前提: 已创建索引 idx_created_at_id (created_at, id) String sql SELECT u.* FROM users u INNER JOIN ( SELECT id, created_at FROM users ORDER BY created_at DESC LIMIT ?, ? ) tmp ON u.id tmp.id ; ListUser users jdbcTemplate.query( sql, new UserRowMapper(), offset, pageSize ); return new PageResult(users, -1, page, pageSize); } } /** * 分页结果封装 */ class PageResultT { private ListT data; private long total; private int page; private int pageSize; public PageResult(ListT data, long total, int page, int pageSize) { this.data data; this.total total; this.page page; this.pageSize pageSize; } // getters... }6.2 COUNT(*)的优化-- 深分页常常伴随着COUNT(*)的困扰 -- 方案1: 如果不需要精确总数不查COUNT -- 很多场景(如App的信息流)不需要总页数 -- 方案2: 使用EXPLAIN估算 EXPLAIN SELECT COUNT(*) FROM users; -- rows列显示估算行数 -- 方案3: 使用缓存 -- 将COUNT结果缓存到Redis定期更新 -- 方案4: 条件允许时使用SHOW TABLE STATUS SHOW TABLE STATUS LIKE users; -- Rows列显示近似行数(不精确但有参考价值)七、总结7.1 深分页优化口诀深分页不要慌延迟关联来帮忙。 子查询取主键覆盖索引快如光。 只需上下翻页时游标分页性能强。 WHERE id大于上一条永不扫描重复行。7.2 面试应答模板问: 千万级数据表LIMIT 1000000,10 很慢怎么优化? 答: 核心问题是数据库必须扫描并丢弃100万条数据 每条都要回表。三种优化方案: 1. 延迟关联(最通用): SELECT * FROM t JOIN (SELECT id FROM t ORDER BY id LIMIT 1000000,10) tmp ON t.id tmp.id; 子查询只扫索引不回表外层只回表10次。 2. 游标分页(性能最优): SELECT * FROM t WHERE id 1000000 ORDER BY id LIMIT 10; 直接用主键定位不需要OFFSET。 3. 覆盖索引子查询(非主键排序): 建联合索引(idx_sort_col, id) 子查询用覆盖索引再关联回表。 推荐优先使用游标分页(如果不需要跳页) 否则用延迟关联。