MySQL 分页优化：让查询速度飞起来！

在当今数据驱动的时代，MySQL 作为一款广泛使用的关系型数据库，分页查询功能是日常开发中高频使用的操作。然而，随着分页页码的增加，查询耗时往往急剧上升。这不仅影响用户体验，还可能成为系统性能的瓶颈。本文将深入探究 MySQL 分页的底层机制，学习如何优化分页查询，显著提升数据库查询效率。

一、常见分页方式与问题：揭开性能瓶颈的面纱

（一）传统分页查询的“痛点”

通常，我们使用 ORDER BY 配合 LIMIT offset, count 的方式进行分页查询。例如：

SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10;

或者不带条件的分页查询：

SELECT * FROM `t1` ORDER BY id DESC LIMIT 100, 10;

然而，这种方式在分页偏移量（offset）较大时，性能问题暴露无遗。以下是两个不同起始值的分页 SQL 执行耗时对比：

yejr@imysql.com> SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 500, 10;
...
10 rows in set (0.05 sec)

yejr@imysql.com> SELECT * FROM `t1` WHERE ftype=6 ORDER BY id DESC LIMIT 935500, 10;
...
10 rows in set (2.39 sec)

可以看到，仅仅是起始偏移值的变化，查询耗时就增加了数十倍！这显然不符合高效查询的期望。

（二）性能问题根源剖析

为了深入理解这个问题，我们查看相关表的 DDL、数据量以及查询 SQL 的执行计划。假设表 t1 结构如下：

yejr@imysql.com> SHOW CREATE TABLE `t1`;
CREATE TABLE `t1` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 ...
 `ftype` tinyint(3) unsigned NOT NULL,
 ...
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表中数据量为：

yejr@imysql.com> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 994584   |
+----------+

对于深分页查询 SELECT * FROM t1 WHERE ftype=1 ORDER BY id DESC LIMIT 935500, 10 的执行计划如下：

yejr@imysql.com> EXPLAIN SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500, 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 935510
        Extra: Using where

从执行计划可以看出，虽然使用了主键索引进行扫描，但第二个 SQL 需要扫描的记录数过大。它需要先扫描约 935,510 条记录，丢弃前面的结果，然后再根据排序结果取 10 条记录，这无疑是非常耗时的操作。

注：此处建议绘制一个简单的示意图，展示传统分页查询的流程：从全表/索引扫描到筛选出起始位置，再到获取指定数量的记录，以便读者更直观地理解性能损耗的原因。

二、优化思路与方法：突破性能瓶颈的关键

（一）优化方向确定

针对上述问题，优化思路主要集中在以下两点：

利用索引获取数据：尽可能从索引中直接获取数据，避免或减少直接扫描行数据（回表）的频率，减少不必要的数据读取操作。
减少扫描记录数：先确定起始的范围（如主键 ID 范围），再往后取 N 条记录。通过缩小扫描范围，显著降低查询的时间复杂度。

（二）子查询优化法

优化策略阐述
采用子查询的方式优化。在子查询里先从索引获取到最大 ID，然后倒序排，再取结果集。需要注意的是，由于逻辑上采用了两次倒序排列，在取 LIMIT 的偏移值时，需比原来的值增加 count 值（即 935500 + 10 = 935510），否则结果将和原来不一致。

优化后的 SQL 与执行计划

yejr@imysql.com> EXPLAIN SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: t1
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 973192
        Extra: Using where
*************************** 3. row ***************************
           id: 3
  select_type: SUBQUERY
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 935511
        Extra: Using where

性能提升效果

yejr@imysql.com> SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) T ORDER BY id DESC;
...
rows in set (1.86 sec)
# 采用子查询优化，从 profiling 的结果来看，相比原来的那个 SQL 快了：28.2%

（三）INNER JOIN 优化法

优化策略阐述
采用 INNER JOIN 优化。JOIN 子句里优先从索引获取 ID 列表，然后直接关联查询获得最终结果。此方法不需要像子查询法那样调整偏移量。

优化后的 SQL 与执行计划

yejr@imysql.com> EXPLAIN SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500,10) t2 USING (id)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 935510
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: t2.id
         rows: 1
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 973192
        Extra: Using where

性能提升效果

yejr@imysql.com> SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500,10) t2 USING (id);
...
10 rows in set (1.83 sec)
# 采用 INNER JOIN 优化，从 profiling 的结果来看，相比原来的那个 SQL 快了：30.8%

（四）不带过滤条件的分页 SQL 优化对比

原始 SQL 与执行计划

yejr@imysql.com> EXPLAIN SELECT * FROM `t1` ORDER BY id DESC LIMIT 935500, 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 935510
        Extra: NULL

yejr@imysql.com> SELECT * FROM `t1` ORDER BY id DESC LIMIT 935500, 10;
...
10 rows in set (2.22 sec)

子查询优化后的 SQL 与执行计划及性能提升

yejr@imysql.com> EXPLAIN SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: t1
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 973192
        Extra: Using where
*************************** 3. row ***************************
           id: 3
  select_type: SUBQUERY
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 935511
        Extra: Using index

yejr@imysql.com> SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id from `t1` ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC;
...
10 rows in set (2.01 sec)
# 采用子查询优化，从 profiling 的结果来看，相比原来的那个 SQL 快了：10.6%

INNER JOIN 优化后的 SQL 与执行计划及性能提升

yejr@imysql.com> EXPLAIN SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1` ORDER BY id DESC LIMIT 935500,10) t2 USING (id)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: 
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 935510
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: t1.id
         rows: 1
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 973192
        Extra: Using index

yejr@imysql.com> SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1` ORDER BY id DESC LIMIT 935500,10) t2 USING (id);
...
10 rows in set (1.70 sec)
# 采用 INNER JOIN 优化，从 profiling 的结果来看，相比原来的那个 SQL 快了：30.2%

（五）利用覆盖索引优化

优化原理
覆盖索引（Covering Index）是指索引包含了满足查询语句中字段与条件的数据。在分页查询中，如果能够建立合适的覆盖索引，就可以直接从索引中获取查询所需的数据，而无需回表查询数据行，从而大大提高查询效率。
建立覆盖索引示例
假设我们经常根据 ftype 和 id 进行分页查询，可以为这两个字段建立联合索引：
```
CREATE INDEX idx_ftype_id ON t1 (ftype, id);
```
这样，在执行分页查询时，如果查询语句只涉及 ftype 和 id 字段（例如 SELECT ftype, id FROM t1 WHERE ftype=1 ORDER BY id DESC LIMIT 935500, 10），就可以直接从索引中获取数据，避免了对数据行的扫描。
性能提升效果
经过测试，在一些情况下，使用覆盖索引进行分页查询可以将查询速度提升数倍甚至更多，尤其是在数据量较大且查询字段较少的情况下，效果更为显著。

（六）延迟关联优化

优化原理
延迟关联（Deferred Join）是先通过索引筛选出符合条件的主键值，然后再通过主键关联查询所需的其他字段。这种方式可以减少不必要的数据读取，提高查询性能。
优化后的 SQL 示例
```
SELECT * FROM t1
INNER JOIN (
    SELECT id FROM t1 WHERE ftype = 1 ORDER BY id DESC LIMIT 935500, 10
) AS t2 ON t1.id = t2.id;
```
首先，子查询通过索引快速筛选出符合条件的 id 值，然后再通过主键关联查询出完整的记录。
性能提升效果
在实际应用中，延迟关联优化可以有效减少数据扫描量，提高查询速度，特别是对于大分页查询且查询字段较多的情况，性能提升明显。

三、优化效果总结与最佳实践：选择最优方案

（一）提升比例汇总

通过对各种场景下的分页查询优化效果进行测试和分析，得到以下提升比例数据：

优化方式	大分页，带 WHERE	大分页，不带 WHERE	大分页平均提升比例	小分页，带 WHERE	小分页，不带 WHERE	总体平均提升比例
子查询优化	28.20%	10.60%	19.40%	24.90%	554.40%	154.53%
INNER JOIN 优化	30.80%	30.20%	30.50%	156.50%	11.70%	57.30%
覆盖索引优化	显著提升	显著提升	-	显著提升	显著提升	-
延迟关联优化	明显提升	明显提升	-	明显提升	明显提升	-

说明：覆盖索引与延迟关联的具体提升比例视数据分布和查询字段而定，此处定性描述为“显著提升”或“明显提升”。

（二）最佳实践推荐

从上述数据可以看出，尤其是针对大分页的情况，INNER JOIN 方式在平均提升比例上表现更优。然而，在实际应用中，我们应根据具体的业务场景和数据特点选择合适的优化方法：

覆盖索引：如果查询字段较少且能够建立覆盖索引，这可能是最佳选择。
延迟关联：如果查询涉及较多字段且分页较大，延迟关联优化也能带来显著的性能提升。
INNER JOIN：这是一种通用且高效的方法，适用于大多数深分页场景。

在数据经过预热后，查询效率会一定程度提升，但上述相应的效率提升比例基本保持一致。

希望通过本文的学习，你能够掌握 MySQL 分页优化的多种技巧，根据实际情况灵活运用，显著提升数据库查询性能。在实际开发中，合理运用这些优化方法，为用户提供更快速、流畅的体验。让我们一起告别缓慢的分页查询，迎接高效数据库操作的新时代！

说明：本文基于 MySQL 5.x/8.x 通用特性整理，执行计划输出格式可能因版本不同略有差异，但优化原理通用。

本文地址：https://1diff.fun/archives/mysql-fen-ye-you-hua--rang-cha-xun-su-du-fei-qi-lai.html

如果对本文有什么问题或疑问都可以在评论区留言，我看到后会尽量解答。