相信大家肯定遇到这种情景：查询要求分页并且按某一字段排序

工作中解决方案一般都是查询添加order by 然后在使用mybatis的PageHelper插件进行分页。工作中一直使用oracle也没感觉什么，但是自己瞎鼓捣的时候都是用的mysql。

PageHelper的插件对于oracle和mysql的区别大家应该知道：

对于oracle是嵌套一层

select * from (  sql  ) where rownum <= 每页数量

或者嵌套

select * from ( 
    select row_.*,rownum rownum_ from (  
        sql 
    )row_ where rownum <= 上次偏移量 + 每页数量 
)where rownum_ > 上次偏移量

但是mysql没有，只能用 limit 来实现分页

即嵌套一层

我很好奇limit的速度

于是测试

为了测试最快速度，这里用主键进行排序，主键有主键索引，相比之下速度应该更快一些

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

该条语句将会从表 orders 中查询offset: 1000开始之后的10条数据，也就是第1001条到第1010条数据（1001 <= id <= 1010）

查询时间大约在 3040 ms 左右

到底是偏移量影响速度还是查询数量影响的速度呢？

测试查询数量

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

三次查询时间如下：

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

从查询时间来看，基本可以确定，在查询记录量低于100时，查询时间基本没有差距，随着查询记录量越来越大，所花费的时间也会越来越多。

测试偏移量

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

三次查询时间如下：

• 查询100偏移：25ms 24ms 24ms
• 查询1000偏移：78ms 76ms 77ms
• 查询10000偏移：3092ms 3212ms 3128ms
• 查询100000偏移：3878ms 3812ms 3798ms
• 查询1000000偏移：14608ms 14062ms 14700ms

这种分页查询方式会从数据库第一条记录开始扫描，所以越往后，查询速度越慢，而且查询的数据越多，也会拖慢总查询速度。

但是我发现

select * from orders  where type=8 limit 100000,1;
select id from orders where type=8 limit 100000,1;

查全部要 3600ms ，而光查id仅需要 1320ms 左右，所以能不能使用这种优化一下呢？

测试

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

时间也是 1330ms 左右

优化

假设数据表的id是连续递增 的，则我们根据查询的页数和查询的记录数可以算出查询的id的范围，可以使用 id between and 来查询：

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

查询时间：15ms 12ms 9ms

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

还可以有另外一种写法：

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

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

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

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