网易一面：select 分页要调优 100 倍，说说你的思路？

Estimated reading: 1 minute 13 views

一、背景与问题分析

在MySQL中，使用LIMIT start, count进行深分页时，随着start增大，查询速度会显著变慢。例如：

SELECT * FROM product LIMIT 1000000, 20 需0.682秒
LIMIT 3000000, 20 需1.368秒

原因：
MySQL会先扫描前start+count条数据，再丢弃前start条，导致大量无效扫描。深分页时，扫描行数可能达到百万级，性能瓶颈明显。

二、MySQL分页基础语法

SELECT * FROM 表名 LIMIT [offset,] rows;

offset：起始偏移量（从0开始），表示跳过的行数
rows：返回的行数
示例：
LIMIT 6,5：跳过前6行，取5行（返回第7-11行）
LIMIT 5：等价于LIMIT 0,5，取前5行

三、深分页优化方案

方案1：利用主键/唯一索引定位（推荐）

原理：通过索引快速定位起始点，避免全表扫描。
语法：

SELECT * FROM 表名 WHERE 主键ID > (pageNum-1)*pagesize ORDER BY 主键ID LIMIT pagesize;

示例（每页20条，查询第1001-1020条）：

SELECT * FROM product WHERE id > 1000 ORDER BY id LIMIT 20;

条件：

数据按主键自增且连续（无删除导致的空洞）
查询结果需按主键排序

优势：索引扫描速度快，复杂度接近O(1)。

方案2：子查询+索引覆盖（性能最优）

步骤：

先通过子查询获取起始主键ID
再通过主键查询完整数据
语法：

SELECT * FROM product 
WHERE id >= (SELECT id FROM product LIMIT 866613, 1) 
LIMIT 20;

示例效果：

深分页查询耗时从37秒降至0.2秒，提升约100倍！

方案3：JOIN子查询优化

原理：通过JOIN预计算的索引子集减少扫描范围。
语法：

SELECT * FROM product AS t1 
JOIN (SELECT id FROM product LIMIT 866613, 20) AS t2 
ON t1.id = t2.id;

优势：利用索引覆盖快速定位，适用于非连续主键场景。

方案4：复合索引优化（带WHERE条件）

场景：查询包含过滤条件（如WHERE vtype=1）的深分页。
索引设计：创建复合索引(过滤字段, 主键)

ALTER TABLE collect ADD INDEX idx_vtype_id (vtype, id);

优化后查询：

SELECT id FROM collect 
WHERE vtype=1 
ORDER BY id 
LIMIT 90000, 10;  -- 耗时从8秒降至0.04秒

关键：

过滤字段（vtype）需在索引最左
仅查询主键字段（索引覆盖）

方案5：PREPARE预编译语句

语法：

PREPARE stmt FROM 'SELECT * FROM product WHERE id > ? ORDER BY id LIMIT ?';
SET @pageStart = (pageNum-1)*20;
SET @pageSize = 20;
EXECUTE stmt USING @pageStart, @pageSize;

优势：减少SQL解析开销，适用于高频分页场景。

四、方案对比与适用场景

方案	核心原理	优势	缺点	适用场景
主键索引定位	索引快速定位起始点	简单高效，复杂度低	需主键自增且连续	简单分页，数据无空洞
子查询+索引覆盖	先查主键再查完整数据	性能提升100倍，通用	需两次查询	百万级深分页
JOIN子查询	预计算索引子集	支持非连续主键	语法较复杂	复杂排序场景
复合索引	过滤字段+主键联合索引	带条件查询性能优	需索引覆盖（仅查主键）	带WHERE条件的分页
PREPARE预编译	减少SQL解析开销	提升查询效率	需预编译语句	高频重复分页

五、总结与最佳实践

避免全表扫描：永远通过索引（主键/唯一键）定位数据，禁止直接使用LIMIT start, count处理深分页。
索引覆盖优先：仅查询必要字段，避免回表查询（如只查id而非*）。
复合索引设计：过滤条件字段+排序字段+主键，形成最左前缀索引。
数据归档：对历史数据进行分库分表或归档，减少单表数据量。

面试回答框架：

指出深分页性能问题的本质（全表扫描+数据丢弃）
分层给出优化方案：
- 基础方案：主键索引定位
- 进阶方案：子查询+索引覆盖
- 复杂场景：复合索引+JOIN
结合具体数据量和业务场景选择方案，强调索引设计的核心作用。