分页查询的优化

本文主要是介绍分页查询的优化，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

我们有分页查询订单详细信息的需求，订单表目前有300多万的数据，而且order表在orderNo列上有索引，传统的写法可能使用limit offset, pageSize的写法来实现，但是有一个问题，在offset非常大的时候，查询会很慢，因为会使Mysql扫描大量的无用行，然后再扔掉，例如下面这样的语句。

select orderNo, state, payType, ctime 
from `order` 
order by orderNo limit 2000000, 20;

结果用了13秒才查出来，性能上是无法接受的，我们可以优化一下

一、使用索引优化：

由于上面的问题最重要的原因是扫描了大量无用的行，所以我们就从这里入手，我们知道orderNo列是索引的，我们可以想过办法通过索引覆盖查询来查出必要的需要扫描的行，然后再去扫描实际的数据行

例如，可以改成下面这样

select orderNo, state, payType, ctime 
from `order` inner join (
  select orderNo 
  from `order`
  order by orderNo limit 2000000, 20
) as o using(orderNo);

通过上面的改写，原本13秒的查询只需要0.5秒便实现了，效果相当明显

这是因为inner join的表通过索引覆盖查询直接通过索引找到了需要返回的数据行，然后order表通过与这个派生表进行关联，只扫描20行数据就可以了。

上述的using命令作用等价于join操作中的on，例如a和b根据id字段关联，那么以下等价 using(id) ~ on a.id=b.id。

二、使用子查询优化

这种方式先定位偏移位置的 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条语句和第2条语句：使用 select id 代替 select * 速度增加了3倍
• 比较第2条语句和第3条语句：速度相差几十毫秒
• 比较第3条语句和第4条语句：得益于 select id 速度增加，第3条语句查询速度增加了3倍

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

三、使用id限定优化

这种方式假设数据表的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;

这篇关于分页查询的优化的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！