高性能MySQL：分页查询中隐藏的秘密

我们知道，很多时候业务上会有分页操作的需求，比如：

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

表示从表t1中取出第10001行开始的10行。看似只有10条记录参与，其背后则是10010条记录的默默无闻（查询后抛弃前10000条记录）。因此这样查询一张大表的靠后的数据时非常低效的。怎么办？
往下看吧！

use mu;                       /* 使用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、查询根据非主键字段排序的分页查询

1、根据自增且连续主键排序的分页查询

对表t1执行命令： select * from t1 limit 99000,2;
运行0.03s
因为发现主键自增并且连续的，所以我们可以这样写： select * from t1 where id > 99000 limit 2;
查询结果一致，但是这条命令运行了0.00s！！！

我们分别对其explain（不懂的看本专栏的上一篇），发现：第一条命令对全表搜索，走了99965行，而第二条命令对主键搜索，只走了1000行。
显然，改写后的SQL执行效率更高。

但是，却不实用！ 因为表中某些记录在被删除后，可能造成主键空缺，导致结果不一致。
这里抛给大家一个问题：对于表t1，如果主键自增的前提下，中间有部分记录被删掉了，也就是主键不连续了，下面这条SQL怎么优化？

select * from t1 limit 99000,2;

2、查询根据非主键字段排序的分页查询

先来看一下根据非主键字段排序的分页查询：

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

时间是0.8s。

explain一下：key字段对应值为null，并没有使用a字段的索引（ 扫描整个索引并查到没索引的行的成本高于扫描全表，所以优化器放弃使用索引 ）

怎么优化？

我们知道了不走索引的原因，则优化的关键是让排序时返回的字段尽可能少。
我们可以先让排序和分页操作查出主键，再根据主键去查找记录。

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

可以看到，查询结果与上条命令结果一致，执行时间为0.2s（少了3/4）。

explain一下，发现使用了索引排序！

总结

对于其它一些复杂的分页查询，也基本可以按照这两个思路去优化，尤其是第二种优化方式。 第一种优化方式需要主键连续，而主键连续对于一个正常业务表来说可能有点困难，总会有些数据行删除的，但是占用了一个主键 id。