mysql：深度剖析MySQL深度分页问题及高性能解决方案

深度剖析MySQL深度分页问题及高性能解决方案

一、深度分页问题本质与性能瓶颈分析

在大型互联网应用中，分页查询是非常常见的需求，但当数据量达到千万甚至亿级别时，传统的LIMIT offset, size分页方式会出现严重的性能问题。这种现象我们称之为\"深度分页\"问题。

传统分页的性能瓶颈

SELECT * FROM large_table ORDER BY id LIMIT 1000000, 10;

这条看似简单的SQL语句在数据量大的情况下会产生以下问题：

1. 大量无用数据的传输：MySQL需要先读取1000010条记录，然后丢弃前1000000条
2. 排序成本高昂：ORDER BY操作需要对大量临时数据进行排序
3. 内存压力：大偏移量会导致大量数据加载到内存
4. 索引失效：即使id字段有索引，大偏移量下优化器可能放弃使用索引

系统流程图

#mermaid-svg-ONDu91hWgdoH67mv {font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-ONDu91hWgdoH67mv .error-icon{fill:#552222;}#mermaid-svg-ONDu91hWgdoH67mv .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-ONDu91hWgdoH67mv .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-ONDu91hWgdoH67mv .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-ONDu91hWgdoH67mv .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-ONDu91hWgdoH67mv .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-ONDu91hWgdoH67mv .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-ONDu91hWgdoH67mv .marker{fill:#333333;stroke:#333333;}#mermaid-svg-ONDu91hWgdoH67mv .marker.cross{stroke:#333333;}#mermaid-svg-ONDu91hWgdoH67mv svg{font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-ONDu91hWgdoH67mv .label{font-family:\"trebuchet ms\",verdana,arial,sans-serif;color:#333;}#mermaid-svg-ONDu91hWgdoH67mv .cluster-label text{fill:#333;}#mermaid-svg-ONDu91hWgdoH67mv .cluster-label span{color:#333;}#mermaid-svg-ONDu91hWgdoH67mv .label text,#mermaid-svg-ONDu91hWgdoH67mv span{fill:#333;color:#333;}#mermaid-svg-ONDu91hWgdoH67mv .node rect,#mermaid-svg-ONDu91hWgdoH67mv .node circle,#mermaid-svg-ONDu91hWgdoH67mv .node ellipse,#mermaid-svg-ONDu91hWgdoH67mv .node polygon,#mermaid-svg-ONDu91hWgdoH67mv .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-ONDu91hWgdoH67mv .node .label{text-align:center;}#mermaid-svg-ONDu91hWgdoH67mv .node.clickable{cursor:pointer;}#mermaid-svg-ONDu91hWgdoH67mv .arrowheadPath{fill:#333333;}#mermaid-svg-ONDu91hWgdoH67mv .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-ONDu91hWgdoH67mv .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-ONDu91hWgdoH67mv .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-ONDu91hWgdoH67mv .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-ONDu91hWgdoH67mv .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-ONDu91hWgdoH67mv .cluster text{fill:#333;}#mermaid-svg-ONDu91hWgdoH67mv .cluster span{color:#333;}#mermaid-svg-ONDu91hWgdoH67mv div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-ONDu91hWgdoH67mv :root{--mermaid-font-family:\"trebuchet ms\",verdana,arial,sans-serif;}是否客户端请求分页数据判断是否为深度分页使用优化方案使用常规LIMIT分页游标分页/延迟关联/子查询直接执行SQL返回优化后的结果

二、深度分页优化方案实战

方案1：游标分页（Cursor-based Pagination）

游标分页是解决深度分页最有效的方案之一，我们在字节跳动的内容推荐系统中广泛使用。

实现原理：

-- 第一页SELECT * FROM large_table WHERE id > 0 ORDER BY id LIMIT 10;-- 后续页（使用上一页最后一条记录的ID）SELECT * FROM large_table WHERE id > 上一页最后ID ORDER BY id LIMIT 10;

项目实战：
在抖音的评论系统中，我们采用游标分页实现了高性能分页。表结构如下：

CREATE TABLE video_comments ( id BIGINT PRIMARY KEY, video_id BIGINT, user_id BIGINT, content TEXT, create_time DATETIME, INDEX idx_video_ct(video_id, create_time));

优化后的查询：

SELECT * FROM video_comments WHERE video_id = ? AND create_time < ? ORDER BY create_time DESC LIMIT 20;

方案2：延迟关联（Deferred Join）

我们在阿里商品管理系统中的实践：

SELECT t.* FROM large_table tJOIN (SELECT id FROM large_table ORDER BY id LIMIT 1000000, 10) tmpON t.id = tmp.id;

系统交互时序图

#mermaid-svg-1zd2MOLgm73DYJSV {font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-1zd2MOLgm73DYJSV .error-icon{fill:#552222;}#mermaid-svg-1zd2MOLgm73DYJSV .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-1zd2MOLgm73DYJSV .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-1zd2MOLgm73DYJSV .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-1zd2MOLgm73DYJSV .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-1zd2MOLgm73DYJSV .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-1zd2MOLgm73DYJSV .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-1zd2MOLgm73DYJSV .marker{fill:#333333;stroke:#333333;}#mermaid-svg-1zd2MOLgm73DYJSV .marker.cross{stroke:#333333;}#mermaid-svg-1zd2MOLgm73DYJSV svg{font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-1zd2MOLgm73DYJSV .actor{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-1zd2MOLgm73DYJSV text.actor>tspan{fill:black;stroke:none;}#mermaid-svg-1zd2MOLgm73DYJSV .actor-line{stroke:grey;}#mermaid-svg-1zd2MOLgm73DYJSV .messageLine0{stroke-width:1.5;stroke-dasharray:none;stroke:#333;}#mermaid-svg-1zd2MOLgm73DYJSV .messageLine1{stroke-width:1.5;stroke-dasharray:2,2;stroke:#333;}#mermaid-svg-1zd2MOLgm73DYJSV #arrowhead path{fill:#333;stroke:#333;}#mermaid-svg-1zd2MOLgm73DYJSV .sequenceNumber{fill:white;}#mermaid-svg-1zd2MOLgm73DYJSV #sequencenumber{fill:#333;}#mermaid-svg-1zd2MOLgm73DYJSV #crosshead path{fill:#333;stroke:#333;}#mermaid-svg-1zd2MOLgm73DYJSV .messageText{fill:#333;stroke:#333;}#mermaid-svg-1zd2MOLgm73DYJSV .labelBox{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-1zd2MOLgm73DYJSV .labelText,#mermaid-svg-1zd2MOLgm73DYJSV .labelText>tspan{fill:black;stroke:none;}#mermaid-svg-1zd2MOLgm73DYJSV .loopText,#mermaid-svg-1zd2MOLgm73DYJSV .loopText>tspan{fill:black;stroke:none;}#mermaid-svg-1zd2MOLgm73DYJSV .loopLine{stroke-width:2px;stroke-dasharray:2,2;stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);}#mermaid-svg-1zd2MOLgm73DYJSV .note{stroke:#aaaa33;fill:#fff5ad;}#mermaid-svg-1zd2MOLgm73DYJSV .noteText,#mermaid-svg-1zd2MOLgm73DYJSV .noteText>tspan{fill:black;stroke:none;}#mermaid-svg-1zd2MOLgm73DYJSV .activation0{fill:#f4f4f4;stroke:#666;}#mermaid-svg-1zd2MOLgm73DYJSV .activation1{fill:#f4f4f4;stroke:#666;}#mermaid-svg-1zd2MOLgm73DYJSV .activation2{fill:#f4f4f4;stroke:#666;}#mermaid-svg-1zd2MOLgm73DYJSV .actorPopupMenu{position:absolute;}#mermaid-svg-1zd2MOLgm73DYJSV .actorPopupMenuPanel{position:absolute;fill:#ECECFF;box-shadow:0px 8px 16px 0px rgba(0,0,0,0.2);filter:drop-shadow(3px 5px 2px rgb(0 0 0 / 0.4));}#mermaid-svg-1zd2MOLgm73DYJSV .actor-man line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-1zd2MOLgm73DYJSV .actor-man circle,#mermaid-svg-1zd2MOLgm73DYJSV line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;stroke-width:2px;}#mermaid-svg-1zd2MOLgm73DYJSV :root{--mermaid-font-family:\"trebuchet ms\",verdana,arial,sans-serif;}ClientAPIDB请求第一页数据执行优化查询(limit 20)返回数据+游标返回结果请求下一页(带游标)使用游标查询(where id>游标 limit 20)返回新数据+新游标返回结果ClientAPIDB

三、大厂面试深度追问与解决方案

追问1：如何处理非连续主键的分页场景？

场景分析：
在实际业务中，我们经常遇到使用UUID或其他非连续主键的场景，传统的游标分页可能失效。

解决方案：

1. 复合游标方案：

-- 使用create_time作为次要排序条件SELECT * FROM orders WHERE (create_time, id) < (?, ?)ORDER BY create_time DESC, id DESC LIMIT 10;

2. 覆盖索引优化：

CREATE INDEX idx_comp_pagination ON orders(create_time DESC, id DESC);-- 查询优化SELECT o.* FROM orders oJOIN ( SELECT id FROM orders ORDER BY create_time DESC, id DESC LIMIT 10000, 10) tmp ON o.id = tmp.id;

3. 业务层缓存方案：
   在商品搜索系统中，我们实现了两级缓存：

• 本地缓存热门页数据
• Redis有序集合存储分页键

// 伪代码public Page<Product> searchProducts(SearchCondition cond, PageRequest page) { String cacheKey = buildCacheKey(cond); if(page.getPageNum() < 5) { // 前5页走缓存 List<Product> cached = redisTemplate.opsForZSet().range(cacheKey, start, end); if(!cached.isEmpty()) return cached; } // 深度分页走DB return productRepository.search(cond, page);}

追问2：分页场景下的数据一致性如何保证？

问题分析：
在用户翻页过程中，如果底层数据发生变化，可能导致：

• 重复数据（新增记录出现在多页）
• 丢失数据（删除记录导致跳过数据）
• 排序变化（更新操作影响排序）

解决方案：

1. 快照分页技术：

-- 创建临时快照表CREATE TEMPORARY TABLE user_search_snapshot ASSELECT id FROM users WHERE status = 1 ORDER BY score DESC;-- 分页查询SELECT u.* FROM users uJOIN user_search_snapshot s ON u.id = s.idWHERE s.id > ? LIMIT 10;

2. MVCC+游标方案：
   在金融交易系统中，我们采用事务隔离+游标的方案：

@Transactional(isolation = Isolation.REPEATABLE_READ)public Page<Transaction> getTransactions(Long accountId, String cursor, int size) { // 使用可重复读隔离级别保证一致性 return transactionRepo.findByAccountIdAndIdGreaterThan( accountId, cursor, PageRequest.of(0, size));}

3. ES搜索方案：
   对于商品搜索这类场景，我们使用Elasticsearch的search_after参数：

SearchRequest request = new SearchRequest(\"products\");request.source().searchAfter(lastSortValues);request.source().size(10);// 设置排序规则request.source().sort(\"score\", SortOrder.DESC);request.source().sort(\"id\", SortOrder.ASC);

四、高级优化方案与实战经验

1. 分布式环境下的分页挑战

在阿里全球电商平台中，我们面临跨地区数据分页的挑战：

解决方案：

• 区域化分片：按用户区域路由查询
• 异步预加载：预测用户可能访问的下一页
• 布隆过滤器：快速跳过无效数据

// 区域化分页查询示例public Page<Product> getRegionalProducts(Region region, Pageable page) { ShardingKey shardingKey = ShardingKey.regionalKey(region); try (Connection conn = shardingDataSource.getConnection(shardingKey)) { // 使用区域化连接执行分页查询 return jdbcTemplate.query(conn, \"SELECT...\", page); }}

2. 混合存储方案

在字节跳动的内容系统中，我们采用热温冷数据分离存储：

1. 热数据（近3天）：Redis集群
2. 温数据（近1月）：MySQL+缓存
3. 冷数据（历史数据）：TiDB/HBase

#mermaid-svg-ckVBROKkMbrUDKDP {font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-ckVBROKkMbrUDKDP .error-icon{fill:#552222;}#mermaid-svg-ckVBROKkMbrUDKDP .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-ckVBROKkMbrUDKDP .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-ckVBROKkMbrUDKDP .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-ckVBROKkMbrUDKDP .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-ckVBROKkMbrUDKDP .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-ckVBROKkMbrUDKDP .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-ckVBROKkMbrUDKDP .marker{fill:#333333;stroke:#333333;}#mermaid-svg-ckVBROKkMbrUDKDP .marker.cross{stroke:#333333;}#mermaid-svg-ckVBROKkMbrUDKDP svg{font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-ckVBROKkMbrUDKDP .label{font-family:\"trebuchet ms\",verdana,arial,sans-serif;color:#333;}#mermaid-svg-ckVBROKkMbrUDKDP .cluster-label text{fill:#333;}#mermaid-svg-ckVBROKkMbrUDKDP .cluster-label span{color:#333;}#mermaid-svg-ckVBROKkMbrUDKDP .label text,#mermaid-svg-ckVBROKkMbrUDKDP span{fill:#333;color:#333;}#mermaid-svg-ckVBROKkMbrUDKDP .node rect,#mermaid-svg-ckVBROKkMbrUDKDP .node circle,#mermaid-svg-ckVBROKkMbrUDKDP .node ellipse,#mermaid-svg-ckVBROKkMbrUDKDP .node polygon,#mermaid-svg-ckVBROKkMbrUDKDP .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-ckVBROKkMbrUDKDP .node .label{text-align:center;}#mermaid-svg-ckVBROKkMbrUDKDP .node.clickable{cursor:pointer;}#mermaid-svg-ckVBROKkMbrUDKDP .arrowheadPath{fill:#333333;}#mermaid-svg-ckVBROKkMbrUDKDP .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-ckVBROKkMbrUDKDP .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-ckVBROKkMbrUDKDP .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-ckVBROKkMbrUDKDP .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-ckVBROKkMbrUDKDP .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-ckVBROKkMbrUDKDP .cluster text{fill:#333;}#mermaid-svg-ckVBROKkMbrUDKDP .cluster span{color:#333;}#mermaid-svg-ckVBROKkMbrUDKDP div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-ckVBROKkMbrUDKDP :root{--mermaid-font-family:\"trebuchet ms\",verdana,arial,sans-serif;}热数据温数据冷数据客户端请求判断数据范围Redis查询MySQL查询TiDB查询返回结果

五、性能对比与压测数据

我们在阿里商品库进行的压测对比（1000万数据）：

方案 偏移量 QPS 平均响应时间 CPU使用率 传统LIMIT 10 1200 8ms 15% 传统LIMIT 10000 45 220ms 75% 延迟关联 10000 850 12ms 20% 游标分页 N/A 1500 6ms 12% ES搜索 N/A 1800 5ms 30%

六、总结与最佳实践

1. 优先使用游标分页：适合大多数有序场景
2. 复杂查询用延迟关联：减少回表操作
3. 考虑数据一致性需求：选择合适的事务隔离级别
4. 混合存储架构：根据数据热度分层存储
5. 监控与调优：持续关注慢查询和分页性能

在阿里和字节的实际项目中，我们通常采用组合方案：

• 前几页：缓存+常规分页
• 中间页：游标分页
• 深度分页：延迟关联+特殊优化
• 搜索场景：ES专业分页

通过以上方案的综合运用，我们成功将淘宝商品列表页的深度分页查询性能提升了20倍以上，字节跳动评论系统的分页接口P99从原来的800ms降低到了50ms以内。