大数据量的分页查询优化方案

大数据量的分页查询优化方案

背景

随着业务发展越来越快，原来的数据从几万突破到几百万，分页的查询策略是否需要调整一下？

数据准备

1、表名：order_history
2、描述：某个业务的订单历史表
3、主要字段：unsigned int id，tinyint(4) int type
4、字段情况：该表一共37个字段，不包含text等大型数据，最大为varchar(500)，id字段为索引，且为递增。
5、数据量：5709294
6、MySQL版本：5.7.16

select count(*) from orders_history;

返回结果：5709294

三次查询时间分别为：
◾8903 ms
◾8323 ms
◾8401 ms

一般我们会这么做

select * from orders_history where type=8 limit 1000,10;

select * from orders_history where type=8 order by id limit 10000,10;

三次查询时间分别为：
◾3040 ms
◾3063 ms
◾3018 ms

针对这种查询方式，下面测试查询记录量对时间的影响：

select * from orders_history where type=8 limit 10000,1;
select * from orders_history where type=8 limit 10000,10;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 10000,1000;
select * from orders_history where type=8 limit 10000,10000;

三次查询时间如下：

1、查询1条记录：3072ms 3092ms 3002ms
2、查询10条记录：3081ms 3077ms 3032ms
3、查询100条记录：3118ms 3200ms 3128ms
4、查询1000条记录：3412ms 3468ms 3394ms
5、查询10000条记录：3749ms 3802ms 3696ms

结论：
在查询记录量低于100时，查询时间基本没有差距，随着查询记录量越来越大，所花费的时间也会越来越多。

针对查询偏移量的测试：

select * from orders_history where type=8 limit 100,100;
select * from orders_history where type=8 limit 1000,100;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 100000,100;
select * from orders_history where type=8 limit 1000000,100;

三次查询时间如下：

1、查询100偏移：25ms 24ms 24ms
2、查询1000偏移：78ms 76ms 77ms
3、查询10000偏移：3092ms 3212ms 3128ms
4、查询100000偏移：3878ms 3812ms 3798ms
5、查询1000000偏移：14608ms 14062ms 14700ms

结论：
随着查询偏移的增大，尤其查询偏移大于10万以后，查询时间急剧增加。
这种分页查询方式会从数据库第一条记录开始扫描，所以越往后，查询速度越慢，而且查询的数据越多，也会拖慢总查询速度。

我们其实可以这么做

第一个锦囊
这种方式先定位偏移位置的 id，然后往后查询，这种方式适用于 id 递增的情况。

select * from orders_history where type=8 limit 100000,1;
select id from orders_history where type=8 limit 100000,1;
select * from orders_history where type=8 and 
id>=(select id from orders_history where type=8 limit 100000,1) 
limit 100;
select * from orders_history where type=8 limit 100000,100;

4条语句的查询时间如下：
◾第1条语句：3674ms
◾第2条语句：1315ms
◾第3条语句：1327ms
◾第4条语句：3710ms

总结：
1、比较第1条语句和第2条语句：使用 select id 代替 select * 速度增加了3倍
2、比较第2条语句和第3条语句：速度相差几十毫秒
3、比较第3条语句和第4条语句：得益于 select id 速度增加，第3条语句查询速度增加了3倍

这种方式相较于原始一般的查询方法，将会增快数倍。

第二个锦囊
这种方式假设数据表的id是连续递增的，则我们根据查询的页数和查询的记录数可以算出查询的id的范围，可以使用 id between and 来查询：

select * from orders_history where type=2 
and id between 1000000 and 1000100 limit 100;

查询时间：15ms 12ms 9ms

这种查询方式能够极大地优化查询速度，基本能够在几十毫秒之内完成。限制是只能使用于明确知道id的情况，不过一般建立表的时候，都会添加基本的id字段，这为分页查询带来很多便利。

还可以有另外一种写法：

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

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

select * from orders_history where id in
(select order_id from trade_2 where goods = 'pen')
limit 100;

特殊说明

一般情况下，在数据库中建立表的时候，强制为每一张表添加 id 递增字段，这样方便查询。

如果像是订单库等数据量非常庞大，一般会进行分库分表。这个时候不建议使用数据库的 id 作为唯一标识，而应该使用分布式的高并发唯一 id 生成器来生成，并在数据表中使用另外的字段来存储这个唯一标识。

总之

使用先使用范围查询定位 id （或者索引），然后再使用索引进行定位数据，能够提高好几倍查询速度。即先 select id，然后再 select *；