MySQL 优化专题——分页查询优化（超详细）

公众号：技术小厨师

关注小厨师，烹饪美味的技术餐

公众号文章链接：mp.weixin.qq.com/s/3Pc88OtKr…

分页查询简介

可以说，如果读者参与过网络应用的开发，分页查询一定是家常便饭了。比如电商商品列表，论坛帖子列表等等。而一般来说，分页查询其实包括以下两种。

• 逻辑分页：在用户第一次访问时，将数据全部查出，添加到 Session 中，然后通过页码和页大小查出其中一部分返回给用户。缺点非常明显，如果数据量比较大的话，将耗费大量的内存；如果数据频繁更新，则内存中的数据容易过期，需要频繁更新。
• 物理分页：使用数据库自身的分页机制，每一次分页都会去查询数据库。

本文只重点介绍物理分页及其优化方案。

常见的分页查询语句如下，

select [col1, col2 ...] from [table_name] where cond1 [and/or] cond2 ... limit [offset], [length];

分页要关注只有两个参数，

• offset（可选）：偏移量，指从表第一行开始，可以理解为页起始位。
• length：目标行数，可以理解为页大小。

希望读者注意一点，分页不是指对表的所有行记录进行分页查询，而是对表中符合条件的所有行记录进行分页查询。

当然，如果没有条件或者表中行记录均符合条件的话，那就等价于对表中的所有行记录进行分页查询。

为什么这一点很重要？因为如果认为分页查询的对象是指表中的所有行，那么在后续的阅读中可能会陷入一个问题，

“为什么 MySQL 不能像数组那样直接定位 offset 的位置，而是一直遍历直到找到符合条件的行记录呢？”

准备工作

假设我们有这样一个场景，某电商系统使用了如下所示的表来存储商品信息，

create table good_info
(
    good_id     bigint        not null
        primary key,
    good_name   varchar(50)   not null,
    good_desc   varchar(200)  not null,
    state       tinyint       not null,
    create_time int     not null,
    sell_count  int default 0 not null
);

create index good_info_create_time_index
    on good_info (create_time desc);

各个字段的意义如下，

• good_id：商品唯一 id
• good_name：商品名称
• good_desc：商品描述
• state：商品状态，这里假定存在两种状态，正在出售和库存不足
• create_time：发布时间，为了方便后续的测试数据生成，我们使用整数来代替 timestamp 或 datetime
• sell_count：销售量

我们接下来使用存储过程来给它添加一点点数据，

定义随机字符串生成函数

CREATE FUNCTION rand_string(n int) RETURNS varchar(255)
begin
  declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
  declare return_str varchar(255) default '';
  declare i int default 0;
  while i < n do
      set return_str=concat(return_str,substring(chars_str,floor(1+rand()*62),1));
      set i= i+1;
  end while;
  return return_str;
end

定义数据生成存储过程

create
    definer = lwj@localhost procedure gen_data(IN n int)
BEGIN
  DECLARE i INT DEFAULT 1;
    WHILE (i <= n ) DO
      INSERT into good_info (good_id, good_name, good_desc, state, create_time) VALUEs (i, rand_string(20), rand_string(50), FLOOR(RAND() * 2) , FLOOR(RAND() * 1000000));
            set i=i+1;
    END WHILE;
END;

执行存储过程

这里作者选择随机生成 1 千万行数据，

call gen_data(10000000);

执行了 28 m 57 s，终于成功生成了 1 千万条测试数据，

读者可能会疑惑，为什么不选择其他外部的测试数据生成方法而选择存储过程。的确，外部生成方式更加灵活，但是性能远比存储过程差，存储过程自创建后就编译并存储在MySQL中。

分页查询的问题

统计

假如我们有以下的这个分页查询语句，我们调整 offset 和 length，统计查询的平均耗时，

select * from good_info order by create_time desc limit ?, ?;

如下为测试结果，

offset	length	查询耗时（ms）
100	20	25
1000	20	48
10000	20	809
100000	20	13762
1000000	20	18966

我们来 explain 一下各个语句，对于 offset 为 10000 及以下的情况如下，

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	good_info	NULL	index	NULL	good_info_create_time_index	4	NULL	120	100	NULL

对于 offset 为 100000 及以上的情况如下，

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	good_info	NULL	ALL	NULL	NULL	NULL	NULL	9933232	100	Using filesort

可以看到 offset 为 10000 及左右情况下，优化器还是会选择走索引的，但是像 100000 及以上的情况下，优化器会选择全表，这是为什么呢？为了揭晓这个答案我们可以使用 hint（优化器提示）来强制走索引，看看是怎么一回事儿。

对于 offset 为 100000 的情况，

select * from good_info force index(good_info_create_time_index) order by create_time desc limit 100000, 20;

统计结果耗时为 7922ms，这时候是比优化器选择的全表要快的。

对于 offset 为 1000000 的情况，

select * from good_info force index(good_info_create_time_index) order by create_time desc limit 1000000, 20

统计结果耗时为 1m 6s 4ms！

也就是说，在 offset 比较大的时候优化器会选择全表而不是查询，因为全表可能会比走索引更加快。

读者必须知道优化器并不总是精确的，它是一个综合了各项指标的经验机器，所以才会出现 100000 的情况下不走索引。

分析

分页查询的问题本质上其实是 offset 的问题，首先我们要知道分页查询的过程中，SQL 的执行流程。

SQL 走索引的执行流程如图所示，

在分页查询中，对于每一行来说都要经过以下的过程，

1. 通过二级索引查找找到对应的行的主键
2. 通过主键找到对应的行记录
3. 通过行记录判断该行是否符合 where 条件

也就是说，默认的分页查询中会产生非常多次数的回表查询，offset 为 10000，length 为 20 的情况下，SQL 走索引至少要回表查询 10020 次。

在表中的数据量日渐庞大的情况下，回表查询的代价也是不断上升的，最终会导致分页走索引的代价比全表查询要高，所以优化器才会在 offset 较大的情况下选择走全表而不是索引。

优化分页查询的思路

id 范围查询

理想状态下，如果读者想要优化的表具备以下特点，则可以使用 id 范围查询来直接定位到需要的数据页范围，

• 主键是严格自增的
• 表中的数据一经 insert，就不会 delete 的
• 主键列不会被修改

优化前：

select * from good_info order by create_time desc limit 1000000, 20;

耗时 13762ms

优化后：

select * from good_info where good_id between 1000000 and 1000020 order by create_time desc;

耗时 10ms，简直是质的飞跃啊！explain 一下，

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	good_info	NULL	range	PRIMARY	PRIMARY	8	NULL	21	100	Using where; Using filesort

进一步，因为 id 总是自增的，所以后发布的商品其 id 总是更加大一些，所以如果我们可以根据 good_id 来进行排序，

explain select * from good_info where good_id between 1000000 and 1000020 order by good_id desc;

explain 一下就发现 Extra 中的 filesort 变成为了 Backward index scan。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	good_info	NULL	range	PRIMARY	PRIMARY	8	NULL	21	100	Using where; Backward index scan

这两者效率有什么不同？简单来说，filesort 需要做一次额外的排序工作，而 Backward index scan 利用了主键列索引双向链表直接获得排序后的结果，无需额外的排序工作。

InnoDB 主键列 B+ 树叶子结点按照主键的大小升序排序，而我们的查询要求降序排序，于是查询会使用叶子结点双向链表倒序扫描来获取结果。

标签法

id 范围查询的条件是非常理想的，如果服务有分布式的需求的话，可能会将 id 变更为分布式一致性 id，比如雪花 id 等。在这种情况下主键就不是严格自增的了，也就无法使用上述的 id 范围查询来优化。本节介绍的标签法则也是一种比较好的方法，能克服上述问题。

标签法利用历史分页记录来实现，结合了实际的应用场景，我们想象一下在社交论坛列表或者电商商品列表中，用户的行为总是向下滑。换言之，这些场景下的分页查询中，用户请求总是请求”下一页“。

于是，我们可以用另外的表记录用户的“上一页”的位置，

create table user_page
(
    user_id     int not null
        primary key,
    next_offset int not null
);

• user_id：用户的业务 id
• next_offset：该用户下一次分页查询的起始位置

然后在分页查询的时候使用“历史”表，

select * from good_info where good_id >= (select next_offset from user_page where user_id = ?) limit 20;
update user_page set next_offset = next_offset + 20;

这里通过子查询得到了下一次分页 good_id 的起始位置，limit 参数只用于指定页大小。每次分页查询结束后，更新下一次用户的起始位置。

耗时 29 + 16 ms。也是一种非常不错的方案。

延迟关联

如果你的表存在非尾部插入、删除中间行和二级索引树叶子节点值经常变动的情况，那么上面的两种办法都是不行的，比如对商品销售量降序分页查询。

这时候我们就可以采用使用场景最广的分页查询优化方案——延迟关联。

所谓延迟关联，其实就是为了避免分页的每一行都进行回表查询，而是通过一种办法一次性找到需要行后再进行回表查询。下面直接给出延迟关联的查询语句，

SELECT * FROM good_info as g1 JOIN (SELECT good_id FROM good_info order by create_time desc LIMIT 1000000, 20) as g2 on g1.good_id = g2.good_id

耗时：135 ms

explain 一下，

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	NULL	ALL	NULL	NULL	NULL	NULL	1000020	100	NULL
1	PRIMARY	g1	NULL	eq_ref	PRIMARY	PRIMARY	8	g2.good_id	1	100	NULL
2	DERIVED	good_info	NULL	index	NULL	good_info_create_time_index	4	NULL	1000020	100	Using index

我们可以看到，自查询中使用了覆盖索引来获取符合条件的 good_id，再通过 good_id 去查询主键树来得到结果。最终回表查询次数缩减为页大小，也就是 20 行。

如果一个索引包含（覆盖）我们所需要查询的所有字段值，我们就称之为覆盖索引。比如 create_time 的索引树叶子结点存在我们需要的主键的值，那么子查询就不需要回表查询。

其实还有一种子查询优化的方法，和延迟关联的思路其实是一样的，都是减少回表查询次数，这里不再赘述。

总结

分页查询的优化是非常重要的， 对于线上系统来说，差的分页查询将会成为系统的瓶颈。而本文只从 MySQL 层面谈及了分页查询的多种优化方法，还有一类通过外部缓存实现的分页查询优化，我们以后有机会再说。如果你觉得我写得还不错，可以为点赞和关注～～