数据库系列之MySQL中的分页查询优化

分页查询的效率在数据量大的时候尤为重要，影响到前端响应和用户体验。本文简要测试了几种分页查询优化的方法，适用于数据库开发性能优化场景。

---

1、MySQL中分页方法

1.1 limit m,n分页语句

传统的分页查询使用“select c1,c2,… from t1 limit n,m”语句，工作原理就是先读取前面n条记录，然后抛弃前n条，读后面m条想要的，所以n越大，偏移量越大，性能就越差。

1.1.1 limit分页和总页数公式

1）limit分页公式

limit  (curPage-1)*pageSize,pageSize

其中curPage是当前第几页；pageSize是一页多少条记录

2）总页数公式

int totalPageNum = (totalRecord +pageSize-1)/pageSize;

其中totalRecord是总记录数，通过SELECT COUNT(*) FROM t1获得；pageSize是一页分多少条记录。

1.1.2 limit n,m测试

分页语句limit n,m使用全表扫描速度会很慢，而且数据库结果集返回不稳定，适用于数据量较少的情况。如下测试，起始页不断增加查看SQL的执行时间：

mysql> select * from sbtest1 limit 10000,10 ;
mysql> select * from sbtest1 limit 100000,10 ;
mysql> select * from sbtest1 limit 1000000,10 ;
mysql> select * from sbtest1 limit 5000000,10 ;

从测试结果中可以看到，随着起始位置的增加，查询时间也不断增加

mysql> show profiles;
+----------+-------------+----------------------------------------+
| Query_ID | Duration    | Query                                  |
+----------+-------------+----------------------------------------+
|       1 |  0.00861825 | select * from sbtest1 limit 10000,10   |
|       2 |  0.68741175 | select * from sbtest1 limit 100000,10  |
|       3 |  5.62566875 | select * from sbtest1 limit 1000000,10 |
|       4 | 18.33333200 | select * from sbtest1 limit 5000000,10 |
+----------+-------------+----------------------------------------+

1.2 limit m语句实现上一页下一页

翻页查询中上一页和下一页功能实现，一般会记录查询的信息，比如当前页数、id最大值、id最小值和需要到的页数。假设当前页面大小为m，当前页数为no1，则页面最大值为max=(no1+1)m-1，最小值为min=no1m，可以使用以下SQL实现上一页和下一页：

select * from sbtest1 where id>max order by id asc limit n;//下一页
select * from sbtest1 where id<min order by id desc limit n;//上一页

这种方式不管翻多少页只需要扫描n条数据，虽然扫描的数据少了，但是在需要跳转到多少页的场景中无法实现。

为了减少扫描的数据量，可以给这条语句加上一个条件限制，使得每次扫描不用从第一条开始。例如：每页10条数据，当前是第10页，当前条目ID的最大值是109，最小值是100，即当前页为100~109

那么跳到第9页：
select * from sbtest1 where id<100 order by id desc limit 0,10;   //倒序
那么跳到第8页：
select * from sbtest1 where id<100 order by id desc limit 10,10;
那么跳到第11页：
select * from sbtest1 where id>109 order by id asc limit 0,10;

2、分页查询优化

2.1 使用子查询优化

子查询优化原理：https://www.jianshu.com/p/0768ebc4e28d

1）select * from sbtest1 where k=504878 limit 100000,5;的查询过程：

首先会查询到索引叶子节点数据，然后根据叶子节点上的主键值去聚簇索引上查询需要的全部字段值。像下图左边这样，需要查询100005次索引节点，查询100005次聚簇索引的数据，最后再将结果过滤掉前100000条，取出最后5条。MySQL耗费了大量随机I/O在查询聚簇索引的数据上，而有100000次随机I/O查询到的数据是不会出现在结果集当中的。

既然一开始是利用索引的，为什么不先沿着索引叶子节点查询到最后需要的5个节点，然后再去聚簇索引中查询实际数据。这样只需要5次随机I/O，类似于上图右边的过程。这就是子查询优化，这种方式先定位偏移位置的id，然后往后查询，这种方式适用于id递增的情况。如下所示：

mysql> select *  from sbtest1 where k=5020952 limit 50,1;
mysql> select id  from sbtest1 where k=5020952 limit 50,1;
mysql> select * from sbtest1 where k=5020952 and id>=( select id  from sbtest1 where k=5020952 limit 50,1) limit 10;
mysql> select * from sbtest1 where k=5020952 limit 50,10;

在子查询优化中，谓词中k是否有索引，对查询效率有很大影响，上述语句没有使用索引走全表扫描需要24.2s，走了索引后只需要0.67s。

mysql> explain  select * from sbtest1 where k=5020952 and id>=( select id  from sbtest1 where k=5020952 limit 50,1) limit 10;
+----+-------------+---------+------------+-------------+---------------+------------+---------+-------+------+----------+------------------------------------------+
| id | select_type | table   | partitions | type        | possible_keys | key        | key_len | ref   | rows | filtered | Extra                                    |
+----+-------------+---------+------------+-------------+---------------+------------+---------+-------+------+----------+------------------------------------------+
|  1 | PRIMARY     | sbtest1 | NULL       | index_merge | PRIMARY,c1    | c1,PRIMARY | 8,4     | NULL  |   19 |   100.00 | Using intersect(c1,PRIMARY); Using where |
|  2 | SUBQUERY    | sbtest1 | NULL       | ref         | c1            | c1         | 4       | const |   88 |   100.00 | Using index                              |
+----+-------------+---------+------------+-------------+---------------+------------+---------+-------+------+----------+------------------------------------------+
2 rows in set, 1 warning (0.11 sec)

2.2 使用id限定优化

假设数据库中表的id是连续递增的，则可以根据查询的页数和查询的记录数计算出查询的id的范围，然后根据id between and语句来查询。id的范围可以通过分页公式计算得到，比如说当前页面大小为m，当前页数为no1，则页面最大值为max=(no1+1)m-1，最小值为min=no1m，SQL语句可以表示为id between min and max。

select * from sbtest1 where id between 1000000 and 1000100 limit 100;

这种查询方式能够极大地优化查询速度，基本能够在几十毫秒之内完成。限制是需要明确知道id的情况，不过一般在分页查询的业务表中，都会添加基本的id字段，这为分页查询带来很多便利。上述SQL还有另一种写法：

select * from sbtest1 where id >= 1000001 limit 100;

可以看到执行时间上的差异：

mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                        |
+----------+------------+--------------------------------------------------------------------------------------------------------------+
|        6 | 0.00085500 | select * from sbtest1 where id between 1000000 and 1000100 limit 100                                         |
|        7 | 0.12927975 | select * from sbtest1 where id >= 1000001 limit 100                                                          |
+----------+------------+--------------------------------------------------------------------------------------------------------------+

还可以使用in的方式来进行查询，这种方式经常用在多表关联的时候进行查询，使用其他表查询的id集合，来进行查询：

select * from sbtest1 where id in (select id from sbtest2 where k=504878) limit 100;

使用in查询的方式要注意某些mysql版本不支持在in子句中使用limit。

2.3 基于索引再排序

基于索引再排序是利用索引查询中有优化算法，通过索引再去找相关的数据地址，避免全表扫描，这样节省了很多时间。另外Mysql中也有相关的索引缓存，在并发高的时候利用缓存效果会更好。在MySQL中可以使用如下语句：

SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M

这种方法适用于数据量多的情况(元组数上万)，最好ORDER BY后的列对象是主键或唯一索引，使得ORDER BY操作能利用索引被消除但结果集是稳定的。比如下面两个语句：

mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                        |
+----------+------------+--------------------------------------------------------------------------------------------------------------+
|        8 | 3.30585150 | select * from sbtest1 limit 1000000,10                                                                       |
|        9 | 1.03224725 | select * from sbtest1 order by id limit 1000000,10                                                           |
+----------+------------+--------------------------------------------------------------------------------------------------------------+

对索引字段id使用order by语句后，性能有了明显的提升。

分页查询其它优化方法还有比如使用复合索引将谓词字段和id加入到索引中、使用临时存储的表来记录分页的id然后进行in查询等。

---

参考资料：

1. https://zhuanlan.zhihu.com/p/92552787
2. https://blog.csdn.net/bandaoyu/article/details/89844673
3. https://blog.csdn.net/HADEWOKE/article/details/53996110
4. https://www.jianshu.com/p/0768ebc4e28d

---

转载请注明原文地址：https://blog.csdn.net/solihawk/article/details/120571308
文章会同步在公众号“牧羊人的方向”更新，感兴趣的可以关注公众号，谢谢！