优化分页查询

前言

很多时候，我们写分页查询的时候，只是单纯的想把结果查询出来就好了，但是有没有想过，自己写的分页查询效率会怎么，数据少的是没太大影响，但是多了就会有影响了，所以这篇简单介绍下分页查询的一些基本优化

比如下面的sql

select a,b,c from t1 limit 10000,10;

表示从表 t1 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的.

为了方便验证，首先创建测试表并写入数据：

use muke;                       /* 使用muke这个database */
drop table if exists t1;        /* 如果表t1存在则删除表t1 */

CREATE TABLE `t1` (             /* 创建表t1 */
  `id` int(11) NOT NULL auto_increment,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`),
  KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;	

drop procedure if exists insert_t1; /* 如果存在存储过程insert_t1，则删除 */
delimiter ;;
create procedure insert_t1()        /* 创建存储过程insert_t1 */
begin
  declare i int;                    /* 声明变量i */
  set i=1;                          /* 设置i的初始值为1 */
  while(i<=100000)do                  /* 对满足i<=100000的值进行while循环 */
    insert into t1(a,b) values(i, i); /* 写入表t1中a、b两个字段，值都为i当前的值 */
    set i=i+1;                      /* 将i加1 */
  end while;
end;;
delimiter ;                 /* 创建批量写入100000条数据到表t1的存储过程insert_t1 */
call insert_t1();           /* 运行存储过程insert_t1 */

这里主要介绍两种场景的分页优化技巧
1 根据连续且自增的主键进行的排序分页查询
2 根据非主键字段进行的排序分页查询

根据连续且自增的主键进行的分页查询

首先来看一个根据自增且连续主键排序的分页查询的例子：

select * from t1 limit 99000,2;

该 SQL 表示查询从第 99001开始的两行数据，没添加单独 order by，表示通过主键排序。我们再看表 t1，因为主键是自增并且连续的，所以可以改写成按照主键去查询从第 99001开始的两行数据，如下：

select * from t1 where id >99000 limit 2;

可以看出查询的结果是一样的，但是上面在许多情况下是不适应的，所以如果用上面的方法，必须满足两个条件：
1 主键自增且连续
2 结果是按照主键排序的

根据非主键字段进行的排序分页查询

再看一个根据非主键字段排序的分页查询，SQL 如下：

select * from t1 order by a limit 99000,2;

查询时间是 0.08 秒。

我们来看下这条 SQL 的执行计划：

可以看出并没有走a字段的索引。

原因：扫描整个索引并查找到没索引的行的成本比扫描全表的成本更高，所以优化器放弃使用索引。

优化：
主要关键点就是让排序时返回的字段尽可能减少，解决办法就是让排序和分页操作先查出主键，然后根据主键查到对应的记录

select * from t1 f inner join (select id from t1 order by a limit 99000,2)g on f.id = g.id;

需要的结果与原 SQL 一致，执行时间 0.02 秒，是原 SQL 执行时间的四分之一，我们再对比优化前后的执行计划：

原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序。

总结

本节讲到了两种分页查询场景的优化：
对于其它一些复杂的分页查询，也基本可以按照这两个思路去优化，尤其是第二种优化方式，第一种限制比较多，一般业务员场景都不满足。

参考资料

该文为本人学习的笔记，方便以后自己复习。参考
《MySQL 5.7 Reference Manual》8.2.1.17 LIMIT Query Optimization：https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html
《深入浅出 MySQL》（第 2 版）：18.4.7 优化分页查询
《高性能 MySQL》 （第 3 版）：6.7.5 优化 LIMIT 分页
慕课网专栏：https://www.imooc.com/read/43
取其精华整合而成。