Mysql之分页查询优化(分析两种情况的分页查询、 根据自增且连续主键排序的分页查询、查询根据非主键字段排序的分页查询)

很多时候，业务上会有分页操作的需求，对应的 SQL 类似下面这条：

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

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

分析两种情况的分页查询

• 根据自增且连续主键排序的分页查询
• 查询根据非主键字段排序的分页查询

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

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

select * from t1 limit 99000,2;

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

select * from t1 where id >99000 limit 2;

查询的结果是一致的。我们再对比一下执行计划：
原 SQL 中 key 字段为 NULL，表示未走索引，rows 显示 99965，表示扫描的行数 99965行；
改写后的 SQL key 字段为 PRIMARY，表示走了主键索引，扫描了1000行。

显然改写后的 SQL 执行效率更高。

但是，这条 SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致，如下图的实验（整个实验过程为：先删除一条前面的记录，然后再测试原 SQL 和优化后的 SQL）：

可以发现两条 SQL 的结果并不一样，因此，如果主键不连续，不能使用上面描述的优化方法。
另外如果原 SQL 是 order by 非主键的字段，按照上面说的方法改写会导致两条 SQL 的结果不一致。所以这种改写得满足以下两个条件：

• 主键自增且连续
• 结果是按照主键排序的

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

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

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

查询时间是 0.08 秒。

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

其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL 改写如下

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 执行时间的四分之一，我们再对比优化前后的执行计划：
对于其它一些复杂的分页查询，也基本可以按照这两个思路去优化，尤其是第二种优化方式。第一种优化方式需要主键连续，而主键连续对于一个正常业务表来说可能有点困难，总会有些数据行删除的，但是占用了一个主键 id。