换种思路写分页查询

通常我们做分页查询的时候，会有如下的SQL语句：

select * from t1 limit 10000, 10;

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

针对两种分页场景的优化技巧如下：

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

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

select * from t1 limit 10000, 10;

该SQL没有单独加order by，表示通过主键排序。因为表t1主键是自增且连续的，所以SQL可以改写成：

select * from t1 where id > 10000 limit 10;

我们再看看SQL执行计划：


可以看出，优化后的SQL的rows明显变少，而且使用了主键，优化后的SQL执行效率更高。

但是优化后的SQL缺点也很明显：

• 一旦删除某一行数据，主键就不连续了，导致查询的结果就不一致了；

• 如果原SQL是order by非主键的字段，一旦改写后也会导致两条SQL的结果不一致；
  所以改写必须满足以下两个条件：

• 主键自增且连续

• 结果是按照主键排序的

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

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

select * from t1 order by a limit 10000, 10

SQL执行计划如下：

明明a是有索引的，但是依然没有走索引，为什么呢？因为扫描整个索引并查找没有索引的字段的成本比扫描全表的成本更高，所以优化器放弃使用索引。
所以，我们可以通过索引的角度进行优化：让排序时返回的字段尽可能少，优化SQL如下：

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

SQL执行计划如下：

可以看出，原SQL是filesort排序，优化后的SQL是索引排序。
所以，这种优化方式的适用性更加广泛，但是写法更加复杂。

3. 总结

• 第一种优化方式需要主键连续自增且只能以主键排序，要求的条件非常苛刻；
• 第二种优化方式适用性更加广泛；