大果冻-博客详情

针对 MySQL 单表一千万数据的分页查询优化，传统 `LIMIT offset, size` 在深度分页时性能急剧下降（需扫描 `offset+size` 行）。

分析

在MySQL中，对于单表一千万条数据的分页查询，传统的LIMIT offset, size方式在翻到后面的页数时（即offset很大时）性能会急剧下降。这是因为MySQL需要扫描大量数据然后丢弃前面的offset条记录，只取后面的size条。

为了优化大表的分页查询，可以采取以下几种方案：

### 方案1：使用主键（或唯一索引）进行分页（推荐）

利用主键（或唯一索引）来记录上一页的最大ID，然后查询下一页时，直接定位到该ID之后的数据。

例如，假设表结构如下（主键为id）：

```sql

SELECT id, name, ... FROM your_table ORDER BY id LIMIT 1000000, 10;

```

当翻页到后面时，可以改为：

```sql

SELECT id, name, ... FROM your_table WHERE id > 上一页的最大id ORDER BY id LIMIT 10;

```

这样，每次查询都是通过主键索引直接定位，避免了扫描大量数据。

注意：这种方式要求排序字段是主键或唯一索引，且排序是连续的（比如按主键递增）。如果排序字段有重复值，需要结合其他字段来保证顺序唯一性。

### 方案2：使用覆盖索引

如果查询的字段都在某个索引上，那么可以使用覆盖索引来减少回表操作，提高查询效率。但是，这种方法在翻到后面的页时，仍然需要扫描大量的索引。

### 方案3：使用子查询优化

先通过子查询获取到当前页的起始主键值，然后再根据主键去查询数据。例如：

```sql

SELECT * FROM your_table

WHERE id >= (SELECT id FROM your_table ORDER BY id LIMIT 1000000, 1)

ORDER BY id LIMIT 10;

```

这样，子查询只返回主键，然后根据主键再去查询数据，可以避免扫描大量的数据行。

### 方案4：使用游标（Cursor）或记录上一页最后一条记录

类似于方案1，但适用于非主键字段排序的情况。每次查询时，记录上一页最后一条记录的排序字段值和主键值，然后下一页查询时，根据排序字段和主键定位。

例如，按非主键字段create_time排序：

```sql

-- 第一页

SELECT id, create_time, ...

FROM your_table

ORDER BY create_time, id -- 加上id保证顺序唯一

LIMIT 10;

-- 第二页及以后：记录上一页最后一条记录的create_time和id

SELECT id, create_time, ...

FROM your_table

WHERE (create_time, id) > (上一页最后一条的create_time, id)

ORDER BY create_time, id

LIMIT 10;

```

这里利用了复合索引(create_time, id)来快速定位。

### 方案5：使用延迟关联（Deferred Join）

先通过子查询获取当前页的主键，然后再关联原表查询数据。这样可以减少回表的数据量。

例如：

```sql

SELECT *

FROM your_table

INNER JOIN (

SELECT id

FROM your_table

ORDER BY create_time

LIMIT 1000000, 10

) AS tmp USING (id);

```

这里子查询只查询主键，由于主键索引通常较小，而且覆盖索引可能更快，然后再通过主键关联回原表获取完整数据。

### 方案6：使用搜索引擎

如果数据量非常大，且分页查询需求复杂，可以考虑使用Elasticsearch、Solr等搜索引擎来分担数据库的压力。

### 总结

- 如果排序字段是主键或唯一索引，方案1是最简单高效的。

- 如果排序字段不是主键，可以考虑方案4（游标）或方案5（延迟关联）。

- 避免使用传统的LIMIT offset, size方式进行深分页。

### 注意事项

- 建立合适的索引是优化分页查询的关键。

- 在业务设计上，尽量避免深分页，比如只允许用户查看前几百页，或者使用滚动加载（无限滚动）的方式，每次加载下一页，而不提供跳转到任意页的功能。

根据实际场景选择合适的方案，并结合索引优化，才能在大数据量下保证分页查询的性能。

以下是高效解决方案：

方案 1：基于主键/索引键的游标分页（推荐）

原理：记录上一页最后一条记录的 ID（或排序键），直接定位起始点 适用场景：排序字段为主键或唯一索引 示例 SQL：

-- 第一页（常规查询）
SELECT id, name, ... 
FROM your_table 
ORDER BY id DESC  -- 按主键排序
LIMIT 10;

-- 后续分页（使用上一页末尾的ID）
SELECT id, name, ... 
FROM your_table 
WHERE id < last_max_id  -- 上一页最后一条记录的ID
ORDER BY id DESC 
LIMIT 10;

优点：时间复杂度 O(10)，与 offset 无关缺点：不支持跳页，需连续翻页

方案 2：覆盖索引 + 延迟关联

原理：先通过覆盖索引快速定位主键，再回表查询数据 适用场景：排序字段有非主键索引****步骤：

创建复合索引 (sort_column, id)
分页查询：

SELECT t.* 
FROM your_table t
JOIN (
    SELECT id 
    FROM your_table 
    ORDER BY sort_column, id  -- 确保排序唯一性
    LIMIT 1000000, 10        -- 仅扫描索引
) AS tmp USING (id);         -- 回表取数据

优点：避免全表扫描，索引内完成排序缺点：需维护额外索引，深度分页仍有延迟

方案 3：范围分页（分区键分页）

原理：利用数据分区键（如时间范围）缩小查询范围 示例 SQL：

SELECT *
FROM your_table 
WHERE create_time BETWEEN '2023-01-01' AND '2023-01-31' 
ORDER BY id 
LIMIT 10;

优点：结合业务减少扫描量缺点：依赖数据分布特征

方案 4：业务层优化

禁止深度跳页
- 仅允许 "上一页/下一页" 操作，禁止直接跳转至末页
滚动分页（Seek Method）
- 客户端缓存当前页最后一条记录的排序键值
- 下页请求携带 WHERE sort_key > last_value ORDER BY sort_key LIMIT 10
数据归档
- 将历史数据迁移到归档表，减少主表体积

性能对比

方案	时间复杂度	适用场景
传统 LIMIT	O(offset+size)	小数据量前几页
游标分页	O(size)	连续翻页（主键/索引排序）
覆盖索引+延迟关联	O(offset+size)*	需索引支持，中深度分页
范围分页	O(分区数据量)	有明显分区特征的数据

注：延迟关联的 offset 在索引内扫描，速度快于全表

强制建议

索引优化：
- 排序字段必须建立索引（单字段或复合索引）
- 确保 ORDER BY + WHERE 字段被索引覆盖
避免 SELECT \*：只返回必要字段减少 I/O
读写分离：分页查询走从库，避免主库压力
缓存策略：高频访问页缓存（如 Redis）

极端场景方案

数据量极大且频繁深度分页：引入 Elasticsearch/Sphinx 等搜索引擎，将分页逻辑转移到专用检索引擎。

示例架构：

总结：

优先选择游标分页（方案1）
非主键排序用 延迟关联（方案2）
结合业务设计 范围分页（方案3）
终极方案：搜索引擎 + 分库分表

针对 MySQL 单表一千万数据的分页查询优化，如何处理

针对 MySQL 单表一千万数据的分页查询优化，传统 LIMIT offset, size 在深度分页时性能急剧下降（需扫描 offset+size 行）。