换种思路写分页查询

雨点打透心脏的1/2处 2023-03-13 11:44 32阅读 0赞

通常我们做分页查询的时候，会有如下的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执行计划：  
![在这里插入图片描述][20200513132530844.png]  
![在这里插入图片描述][20200513132539963.png]  
可以看出，优化后的SQL的rows明显变少，而且使用了主键，优化后的SQL执行效率更高。

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

*  一旦删除某一行数据，主键就不连续了，导致查询的结果就不一致了；
 *  如果原SQL是order by非主键的字段，一旦改写后也会导致两条SQL的结果不一致；  
    所以改写必须满足以下两个条件：
 *  主键自增且连续
 *  结果是按照主键排序的

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

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

select * from t1 order by a limit 10000, 10

SQL执行计划如下：  
![在这里插入图片描述][20200513133736451.png]  
明明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执行计划如下：  
![在这里插入图片描述][2020051313501463.png]  
可以看出，原SQL是filesort排序，优化后的SQL是索引排序。  
所以，这种优化方式的适用性更加广泛，但是写法更加复杂。

## 3. 总结 ##

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

[20200513132530844.png]: /images/20230312/5d615c691258467d9d44b40ffbfad554.png
[20200513132539963.png]: https://img-blog.csdnimg.cn/20200513132539963.png
[20200513133736451.png]: https://img-blog.csdnimg.cn/20200513133736451.png
[2020051313501463.png]: https://img-blog.csdnimg.cn/2020051313501463.png