浅谈MySQL分页优化

这里介绍MySQL分页查询时，在排序稳定、深分页等方面的优化方案

分页结果不稳定

MySQL在进行分页查询时，查询结果可能会发生记录重复、遗漏的问题，即所谓的排序不稳定。为更好理解该现象，这里举例进行说明。假设存在下图的数据

进行分页查询时

-- 查询第一页数据
select * from db3.stu_info limit 0, 2;

-- 查询第二页数据
select * from db3.stu_info limit 2, 2;

当进行两次分页查询，如果重复出现了id为22的记录时，即是所谓的分页结果不稳定

需要注意的是，这里的数据示例实际上并不能复现该问题，此处仅为了更好的解释该现象

事实上，对于该问题，MySQL官方文档中已有解释

# MySQL官方文档：关于Limit的查询优化
https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html

…
If an index is not used for ORDER BY but a LIMIT clause is also present, the optimizer may be able to avoid using a merge file and sort the rows in memory using an in-memory filesort operation.
If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan.
In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.
…

简单来说，出现该现象的本质就在于MySQL每次返回行的顺序是不确定的。故在分页查询时会有一定的可能和概率，出现上述这种记录重复、遗漏的问题。解决该问题的思路也很简单。分页查询时，使用order by语句添加一个具有唯一性的排序字段。之所以使用唯一性字段进行排序，是因为如果排序字段使用非唯一性字段。由于该字段的值存在相同的可能，进而又会导致在排序时出现顺序不确定的问题

-- 查询第一页数据，使用非唯一性字段age进行排序无法保证分页结果稳定
select * from db3.stu_info order by age limit 0, 2;

-- 查询第一页数据，使用唯一性字段id进行排序可以保证分页结果稳定
select * from db3.stu_info order by id limit 0, 2;

同时在分页查询中，如果业务上需要使用非唯一性字段进行排序。为保证分页结果稳定，可以在order by语句最后追加一个唯一性字段来保证分页结果稳定。道理同上

-- 查询第一页数据，并按年龄升序排序，无法保证分页结果稳定
select * from db3.stu_info order by age limit 0, 2;

-- 查询第一页数据，并按年龄、id升序排序，可以保证分页结果稳定
select * from db3.stu_info order by age, id limit 0, 2;

深分页

数据模拟

这里通过自定义函数、存储过程来进行数据模拟

-- 创建数据库
create database db1;

-- 创建数据表
create table db1.stu_info (
    id int not null auto_increment,
    first_name varchar(100) null comment '名字',
    last_name varchar(100) null comment '姓氏',
    nation varchar(100) null comment '民族',
    age int null comment '年龄',
    sex varchar(100) null comment '性别',
    iphone varchar(100) null comment '电话',
    birthday varchar(100) null comment '出生年月',
    primary key (id),
    index index_nation_age (nation, age)
) comment='学生信息表';

-- 自定义函数：从给定字符中生成指定长度的字符串
delimiter //
create
    function db1.gen_str(length int, chs varchar(255))
    returns varchar(100)
    comment '从给定字符中生成指定长度的字符串'
    no sql
begin
    declare res varchar(255) default '';
    declare tempStr varchar(255) default '';
    declare i int default 0;

    while i < length do
        set tempStr = substring(chs, floor( rand()*CHAR_LENGTH(chs)+1 ), 1);
        set res = concat(res, tempStr);
        set i = i+1;
    end while;

    return res;
end;
//
delimiter ;

-- 创建存储过程，用于向表中添加数据
delimiter //
create procedure db1.addStuData(in count int) comment '向stu_info学生信息表中添加数据'
modifies sql data
begin
    declare i int default 0;
    declare names varchar(255) default '长林王发定时要耳根子中奖概率将手动啥沙发为日期望总结注解热额维吾尔人生气哦这里发如今巨峰减少技术接收暗恋拉家带口文旅局将孙楠我年终节点更大';
    declare nations varchar(255) default '汉回藏苗壮满白侗水傣';

    declare my_first_name varchar(100);
    declare my_last_name varchar(100);
    declare my_nation varchar(255);
    declare my_age int;
    declare my_sex varchar(100);
    declare my_iphone varchar(255);
    declare my_birthday varchar(100);

    while i < count do
        set my_first_name = gen_str(rand()*3+1, names);
        set my_last_name = gen_str( 1, names);
        set my_nation = gen_str(1, nations);
        set my_age = ceil( rand()*100 );
        if rand() < 0.5 then
            set my_sex = '男';
        else
            set my_sex = '女';
        end if;
        set my_iphone = cast( ceil(rand()*100000000000) as char );
        set my_birthday = concat( floor(rand()*100+1900), '-', floor(rand()*11+1) );

        insert into stu_info(first_name, last_name, nation, age, sex, iphone, birthday)
            values(my_first_name, my_last_name, my_nation, my_age, my_sex, my_iphone, my_birthday);
        set i = i+1;
    end while;
end
//
delimiter ;

然后调用该存储过程，向stu_info学生信息表中添加1500w条数据

-- 插入 1500w 数据
call db1.addStuData(1500 * 10000);

问题概述

MySQL进行分页查询时，会使用limit语句完成。所谓深分页指的是分页查询的页码过大，即offset值过大

# 相当于 limit N offset M，意为 跳过M条记录, 返回N条记录
limit M, N

这里我们分别执行如下两个分页查询

select * from db1.stu_info order by id limit 20 offset 100;

-- 深分页
select * from db1.stu_info order by id limit 20 offset 4320000;

不难发现在执行第二个SQL深分页时，execution time明显增大，性能退化非常明显

再比如，下述两个SQL的查询效率对比

select * from db1.stu_info where nation='汉' order by id limit 20 offset 100;

-- 深分页
select * from db1.stu_info where nation='汉' order by id limit 20 offset 1320000;

即使，我们查看该深分页的执行计划。不难发现，其已经使用了index_nation_age二级索引

-- 查看执行计划
explain select * from db1.stu_info where nation='汉' order by id limit 20 offset 1320000

对于分页查询而言，其会先扫描 offset+n 条记录，然后再丢弃掉前offset条记录，最后返回n条记录。故对于深分页场景而言，查询效率会严重下降的原因就在于需要读取大量最终会丢弃掉的前offset条记录。即使添加、使用了二级索引，像上文的SQL那样也无法显著提高效率。因为其会通过二级索引对 offset+n 条记录全部进行回表，而不仅仅是对n条记录进行回表

优化方案

分页游标

简单来说就在我们分页查询时，将第2页查询数据作为第1页的查询条件。具体地，查询当前页的上一页、下一页的SQL方式如下所示。这里使用id排序同时也可以保证分页结果的稳定

-- 查询第N+1页，其中 last_max_id 为第N页时查询结果中最大的ID
select * from db1.stu_info where id > [last_max_id] order by id asc limit 20;

-- 查询第N-1页，其中 last_min_id 为第N页时查询结果中最小的ID
select * from db1.stu_info where id < [last_min_id] order by id desc limit 20;

现在我们通过下述SQL来验证查询效率

-- 深分页
select * from db1.stu_info order by id limit 20 offset 4320000;

-- 基于分页游标：查询下一页
select * from db1.stu_info where id > 4320000 order by id asc limit 20;

-- 基于分页游标：查询上一页
select * from db1.stu_info where id < 4320000 order by id desc limit 20;

业务上如果有需要进行排序的需求，需要建立二级索引实现排序，避免发生filesort

-- 需要支持业务排序（按age排序）
select * from db1.stu_info where id > 4320000 order by age limit 20;

-- 需要支持业务排序（按nation排序）
select * from db1.stu_info where id > 4320000 order by nation limit 20;

按age排序进行深分页，由于不满足index_nation_age二级索引的最左匹配原则。执行计划中的Extra列体现了filesort。故该SQL基于分页游标进行查询效率依然低；而按nation排序进行深分页时，由于可以利用index_nation_age二级索引进行排序，故总体效率非常高

-- 查看执行计划
explain select * from db1.stu_info where id > 4320000 order by age limit 20;

当然如果我们将上述按age排序进行深分页的SQL改成符合最左匹配原则时，其效率就会明显提升

select * from db1.stu_info where id > 4320000 and nation='汉' order by age limit 20;

结合上文，在基于游标分页查询时，如果我们在order by语句中追加唯一性排序字段（例如id字段）来保证分页结果稳定时，也需要注意二级索引的建立。例如如下两个SQL语句

-- 需要支持业务排序（按 nation 排序）
select * from db1.stu_info where id > 4320000 order by nation, id limit 20;

-- 需要支持业务排序（依次按 nation、age 排序）
select * from db1.stu_info where id > 4320000 order by nation, age, id limit 20;

查询效率如下所示，第一个SQL相对于第二个SQL来说慢很多

查看第一个SQL的执行计划可以发现，其未使用二级索引index_nation_age进行排序，而是通过filesort进行排序的。究其本质，当二级索引中各索引字段均相同时，其会按主键进行排序。故对于第一个SQL中的排序条件（order by nation, id）来说，是不满足最左匹配原则的；而对于第二个SQL中的排序条件（order by nation, age, id）来说，是满足最左匹配原则的，可以充分利用二级索引实现排序

explain select * from db1.stu_info where id > 4320000 order by nation, id limit 20;

综上所述：基于游标的分页查询可以显著提高在深分页场景下的查询效率。但其局限性也比较明显：

• 该方案适合瀑布流式的业务场景，通过向上、向下滑动来翻页。不适合需要支持跳页查询的场景，比如上一次查询第5页数据，这次查询第27页数据
• 该方案下要求表的主键是单调递增。不适合使用UUID等作为主键的表
• 该方案可通过建立二级索引的方式来保障进行业务排序的查询效率。但如果存在需要使用多个业务字段进行排序时，需考虑各种排序字段的组合场景及建立相应索引的成本

子查询

该方案下，先通过子查询查出符合条件的主键，再用主键id做条件查出相应字段。该方案和下述的延迟关联方案，均适用于传统的表单分页场景。其可以很好的支持跳页查询

select * from stu_info order by id limit 7250000, 20;

-- 基于子查询的深分页优化
select * from stu_info where id >= (
    select id from stu_info order by id limit 7250000, 1
) order by id limit 20;

效果如下所示

而对于如下的深分页sql

select * from stu_info where nation='汉' order by id limit 250000, 20;

如果期望用子查询方式进行优化，会得到下述sql

select * from stu_info where id in (
    select id from stu_info where nation='汉' order by id limit 250000, 20
);

但不幸地是上述sql执行会报错：This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’。即，在MySQL 8.0版本中不支持在子查询中使用limit语句。解决办法也很简单。只需在limit的子查询外层再加一层子查询即可，如下所示

-- 基于子查询的深分页优化
select * from stu_info where id in (
    select id from (
        select id from stu_info where nation='汉' order by id limit 250000, 20
    ) as t
);

效果如下所示

延迟关联

延迟关联的优化思路，本质上和子查询的优化思路其实是一样的。只不过其是把子查询的id结果作为一张临时表，然后和原表进行关联查询

-- 深分页sql
select * from db1.stu_info where nation='汉' order by id limit 250000, 20;

-- 基于延迟关联的深分页优化
select * from stu_info
    inner join (select id from stu_info where nation='汉' order by id limit 250000, 20) as t2
    on stu_info.id = t2.id;

效果如下所示