一、传统分页为何成为性能杀手？

典型的LIMIT分页写法：

SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 10 OFFSET 10000;

执行过程解析：

1. 扫描全表定位到第10000条记录
2. 向后读取10条数据
3. 丢弃前10000条结果性能瓶颈：OFFSET值越大，需要跳过的无效数据越多，磁盘IO暴增！

二、四大优化方案实战

方案1：覆盖索引法（黄金法则）

-- 创建联合索引
ALTERTABLE orders ADDINDEX idx_time_status (create_time, status);

-- 改写查询（确保查询字段被索引覆盖）
SELECTid, create_time, status
FROM orders 
ORDERBY create_time DESC
LIMIT10OFFSET10000;

优势：减少回表查询，索引即数据
局限：需严格匹配索引字段

方案2：子查询延迟关联

SELECT * FROM orders 
INNER JOIN (
    SELECT id FROM orders
    ORDER BY create_time DESC
    LIMIT 10 OFFSET 10000
) AS tmp USING(id);

原理：先快速定位ID，再精准获取数据
性能提升：某电商平台实测，500万数据下查询从2.3s降至0.05s

方案3：游标分页法（Seek Method）

-- 第一页
SELECT * FROM orders 
WHERE create_time <= '2023-08-20'
ORDERBY create_time DESC
LIMIT10;

-- 下一页
SELECT * FROM orders 
WHERE create_time < '上次最后一条时间'
ORDERBY create_time DESC
LIMIT10;

优势：消除OFFSET，线性时间复杂度
场景：移动端无限下拉刷新

方案4：预计算分页（空间换时间）

-- 创建分页辅助表
CREATE TABLE page_helper (
    page_num INT PRIMARY KEY,
    first_id INT,
    last_id INT
);

-- 查询时直接定位
SELECT * FROM orders 
WHERE id BETWEEN (SELECT first_id FROM page_helper WHERE page_num=100) 
AND (SELECT last_id FROM page_helper WHERE page_num=100);

适用场景：静态数据的分页查询（如历史订单归档）

三、进阶优化技巧

1. 冷热分离：将历史数据归档到独立表
2. 分布式方案：ShardingSphere分库分表
3. 缓存策略：Redis缓存前N页热点数据
4. 业务妥协：限制最大翻页深度（如最多显示100页）

四、实战性能对比

结语：分页优化没有银弹，关键要理解数据特性和业务场景。当你在深分页场景下遇到性能瓶颈时，不妨尝试本文的优化方案。记住：最好的优化，往往是从业务逻辑层面减少深分页需求！