1、背景介绍

受限于mysql客户端的传输限制及实际处理数据的需求，在处理大表数据时我们通常会使用分页操作。但是在某次分页查询过程中却发现了一个有趣的问题。

某次我们计划将一个千万级数据表迁移到另外一个数据库中，由于涉及到跨库迁移数据，因此我写了一个简单的迁库脚本，每次迁移1000条数据。简单测试后发现单次迁移需要耗时大概60ms。我们的数据库总量为2千万，按照这样的速度我们预估整个迁移时间大概为20分钟左右。于是我开始了愉快的数据迁移工作。

在经过了20多分钟之后，查看了一下插入结果，发现数据插入并没有结束。进一步分析，发现数据插入速度越来越慢了。我猜测，应该是随着数据库逐渐变大，数据插入速度变慢或者数据读取速度变慢。于是我暂停了迁库脚本，手动插入部分数据发现速度并没有受到明显的影响，那就是查询速度变慢了？

于是我们又测试了分页查询的速度，具体如下：

select * from tb_test limit 1000 offset 1000;
1000 rows in set (0.02 sec)

select * from tb_test limit 1000 offset 100000;
1000 rows in set (0.23 sec)

select * from tb_test limit 1000 offset 1000000;
1000 rows in set (1.79 sec)

select * from tb_test limit 1000 offset 10000000;
1000 rows in set (16.67 sec)

分页查询的速度会随着offset字段的增大线性地变慢。通过查阅资料发现：

当通过limit M offset N参数执行分页查询时，mysql将扫描全部的M+N条记录，然后丢弃前N条数据，然后返回M条数据，因此，当N越大，处理时间越长就很好理解了。

2、解决方案

(1)、索引覆盖

通过之前的分析我们发现分页查询变慢是由数据的全量扫描导致的，那么只扫描索引列是否是优化分页查询的效果呢？实验结果如下：

select id from tb_test limit 1000 offset 10000000;
1000 rows in set (6.64 sec)

select * from tb_test where id >= (select id from tb_test limit 1 offset 10000000) limit 1000;
1000 rows in set (6.80 sec)

我们发现优化之后是有一定效果的，但是由于数据量很大，查询结果依然非常慢。

注：由于在实验表中id是索引列，所以默认会按照id值进行排序，否则需要显式声明 order by field desc/asc,否则当排序规则无法限制数据顺序唯一性时将导致数据重复，具体可以参考mysql分页读取-数据重复问题。

(2)、索引替换

limit offset命令的实现方式就注定了将拥有较大的开销，如果条件允许，在数据量很大的情况下，尽量不使用该方法实现分页。例如，如果id字段是自增id且不存在被删除的可能时（如果被删除的话。where id >= 1000 limit 1000，的返回结果可能不满足1000<=id<2000,下次执行where id >= 2000 limit 1000时，会取到和上次重复的数据），可以优化为：

select * from tb_test where id >= 10000000 limit 1000;
1000 rows in set (0.01 sec)

由于借助了索引字段id,返回时间可以忽略不记。

如果无法保证id字段的连续性，建议将每次返回结果的最后一条数据的id作为参数传递回去。从而保证数据的连续性。