大家好，我是对线面试官。昨晚被一个P0故障电话惊醒：

• 商品列表页超时
• CPU 100%告警
• 数据库连接打满
• 运维差点被炸醒！

一、深分页为什么这么慢？

1. 执行原理分析

SELECT * FROM products 
WHERE status = 1
ORDER BY create_time DESC 
LIMIT 100000, 20;

MySQL执行步骤： 1 从头开始扫描表 2 过滤status=1的记录 3 根据create_time排序 4 丢弃前100000条 5 返回20条记录

问题在哪？ 必须先扫描10W条记录 可能产生临时表排序 大量无用的回表操作 内存占用可能爆炸

2. 执行计划分析

EXPLAIN SELECT * FROM products 
WHERE status = 1
ORDER BY create_time DESC 
LIMIT 100000, 20;

性能杀手：

type=ALL：全表扫描

Using filesort：需要排序

rows=100020：扫描行数巨大

二、优化方案原理解析

1. 延迟关联方案

SELECT p.* 
FROM products p
JOIN (
    SELECT id 
    FROM products 
    WHERE status = 1
    ORDER BY create_time DESC 
    LIMIT 100000, 20
) tmp ON p.id = tmp.id;

优化原理：

1 子查询只查id，减少数据量

2 利用覆盖索引避免回表

3 JOIN时只关联20条记录

4 显著减少内存占用

执行计划对比：

EXPLAIN SELECT p.* 
FROM products p
JOIN (
    SELECT id 
    FROM products FORCE INDEX(idx_create_time)
    WHERE status = 1
    ORDER BY create_time DESC 
    LIMIT 100000, 20
) tmp ON p.id = tmp.id;

2. 游标分页方案

public PageResult queryByCursor(Long lastId, int size) {
    return productMapper.selectList(
        new QueryWrapper()
            .lt("id", lastId)
            .orderByDesc("id")
            .last("LIMIT " + size)
    );
}

优化原理：

1 利用自增ID的单调性

2 每次查询都是范围查询

3 不需要计算偏移量

4 性能稳定不衰减

索引使用分析：

产品表索引结构：
PRIMARY KEY (id)
│
├── 1
│   └── data
├── 2
│   └── data
└── 3
    └── data

游标查询时：
WHERE id < last_id
直接在B+树中定位，效率稳定

3. ES实时同步方案

@Component
public class ProductSyncService {
    
    // ES分页原理
    public SearchResponse search(SearchRequest request) {
        // 1. ES内部使用from+size
        // 2. 分片内部排序
        // 3. 协调节点合并
        return esClient.search(s -> s
            .index("products")
            .from(request.getFrom())  // 偏移量
            .size(request.getSize()), // 页大小
            Product.class
        );
    }
}

ES优化原理：

1 倒排索引加速查询

2 分片内并行处理

3 文档预排序

4 内存映射技术

三、性能对比与原理分析

四、内存分析

1. 原始分页内存问题

问题点：
1. 临时表排序
   - sort_buffer_size限制
   - 可能触发磁盘排序

2. 扫描记录缓存
   - innodb_buffer_pool占用
   - 可能驱逐热数据

2. 优化方案内存分析

// 游标方案内存优势
public class CursorPageHandler {
    
    @Transactional(readOnly = true)
    public PageResult getPage(Long cursor) {
        // 1. 固定LIMIT大小
        // 2. 不需要临时表
        // 3. 索引扫描量稳定
        return productMapper.selectList(
            new QueryWrapper()
                .lt("id", cursor)
                .orderByDesc("id")
                .last("LIMIT " + size)
        );
    }
}

五、监控与预警

1. 慢查询监控

@Aspect
@Component
public class PageQueryMonitor {
    
    @Around("@annotation(PageQuery)")
    public Object monitorPageQuery(ProceedingJoinPoint pjp) {
        // 监控指标：
        // 1. 查询耗时
        // 2. 扫描行数
        // 3. 临时表大小
        // 4. 内存使用
    }
}

2. 性能基线

查询延迟 < 100ms

扫描行数 < 1000

临时表使用率 < 1%

内存使用率 < 80%

写在最后

深分页优化需要：

• 理解数据库执行原理
• 掌握索引使用规则
• 权衡业务实际需求
• 建立完善监控体系

#MySQL #性能优化 #分页查询 #实战经验