MySQL分页查询优化

当需要从数据库查询的表有上万条记录的时候，一次性查询所有结果会变得很慢，特别是随着数据量的增加特别明显，这时需要使用分页查询。对于数据库分页查询，也有很多种方法和优化的点。下面简单说一下我知道的一些方法。

准备工作

为了能够列举出一些优化测试，下面对测试表进行一些说明

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

运行三次下面的语句：

1	select count(*) from orders_history;

得到结果：5709294

三次执行的时间分别是：5903,5323,8401

一般分页查询

1	SELECT * FROM table LIMIT [offset,] rows \| rows OFFSET offset

LIMIT子句可以被用于指定 SELECT 语句返回的记录数。需注意以下几点：

第一个参数指定第一个返回记录行的偏移量
第二个参数指定返回记录行的最大数目
如果只给定一个参数：它表示返回最大的记录行数目
第二个参数为 -1 表示检索从某一个偏移量到记录集的结束所有的记录行
初始记录行的偏移量是0(而不是 1)

我们进行一个简单的测试

1	SELECT * FROM order_history WHERE type = 8 LIMIT 1000,10;

这个语句就是表示获取第1000条数据开始，后面的十条数据。

执行三次，查询时间如下：

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条记录：3072ms 3092ms 3002ms
查询10条记录：3081ms 3077ms 3032ms
查询100条记录：3118ms 3200ms 3128ms
查询1000条记录：3412ms 3468ms 3394ms
查询10000条记录：3749ms 3802ms 3696ms

我们可以发现，在查询的数据量比较少的时候，时间基本上没有太多的差距，在数据量比较大的时候，查询时间瞬间飙升。

我们再运行下面的语句（偏移量不同，数据量相同）

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;

三次查询时间如下：

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

随着查询偏移的增大，尤其查询偏移大于10万以后，查询时间急剧增加。

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

使用子查询优化

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;