为什么我建议你这样实现MySQL分页
前言
之前分享了关于MySQL分页实现方案的文章《如何优雅地实现分页查询》，有些读者觉得写得太浅显了，今天我们就继续探讨这个话题，当然由于能力有限，这篇文章也未必能够达到某些读者的预期，但我觉得只要有一部分哪怕只有几个读者读了我的文章有所收获，我就很满足了。当然如果有写得不好的地方，也请指正，我是很乐意跟大家探讨的。废话不多说了，今天我们主要从查询性能的角度来继续探讨MySQL分页这个话题。先来回顾下之前提到的MySQL分页的2种常见的方案：
第一种是基于limit的分页方案，如：
SELECT * FROM `user` ORDER BY id ASC LIMIT 100, 10;第二种是基于where的分页方案，如：
SELECT * FROM `user` WHERE id > 100 ORDER BY id ASC LIMIT 10;这里的WHERE id > 100中的100是上一次分页结果中最大的id，如果是第一页，那么可以直接去掉where子句，如：
SELECT * FROM `user` ORDER BY id ASC LIMIT 10;为了使得查询性能对比效果更加明显，本文使用的测试表的总记录数比较多，达到百万级别。
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
| 1521920 |
+----------+
1 row in set
mysql> select min(id) from user;
+---------+
| min(id) |
+---------+
| 1 |
+---------+
1 row in set
mysql> select max(id) from user;
+---------+
| max(id) |
+---------+
| 1521920 |
+---------+
1 row in set
mysql> select * from user limit 10;
+----+---------+
| id | name |
+----+---------+
| 1 | user_-4 |
| 2 | user_-3 |
| 3 | user_-2 |
| 4 | user_-1 |
| 5 | user_0 |
| 6 | user_1 |
| 7 | user_2 |
| 8 | user_3 |
| 9 | user_4 |
| 10 | user_5 |
+----+---------+
10 rows in set此外需要说明的是，不同的MySQL版本的实验结果可能不同，本文所做的实验的MySQL版本如下：
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.18-log |
+------------+
1 row in set2种分页方案的执行计划对比
我们先用explain看下2种分页方案的执行计划是怎样的：
mysql> explain select * from user order by id asc limit 400000,10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------+
| 1 | SIMPLE | user | NULL | index | NULL | PRIMARY | 8 | NULL | 400010 | 100 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------+
1 row in set
mysql> explain select * from user where id > 400000 order by id asc limit 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | user | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 732288 | 100 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set首先看下key字段，两种方案都是PRIMARY，说明都走了主键索引。再看rows字段，第一种方案的值是400010，第二种方案的值是732288，第一种方案预估需要扫描的行数比第二种方案预估需要扫描的行数少，由于这个数是预估的，不代表实际的扫描行数，所以只能作为参考。从以上结果看来，似乎基于limit的分页方案要优于基于where的分页方案。那究竟是不是这样子呢？我们做个实验就知道了。
2种分页方案的查询性能对比
为了保证试实验的公平性，我们分别查询排在比较靠前的数据、排在比较靠后的数据、以及排在比较中间的数据，以此来对比2种分页方案的查询性能。
同时，我们还要确保MySQL没有开启查询缓存，否则对于同一个SQL的多次查询有可能会命中缓存，这样一来实验就没有意义了。要确认MySQL有没有开启查询缓存，只需要查询下query_cache_type参数就行了：
mysql> SHOW GLOBAL VARIABLES WHERE variable_name = 'query_cache_type';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_type | OFF |
+------------------+-------+
1 row in set可以看到，MySQL查询缓存是关闭的，所以我们可以放心的开始试验了。
先来看下测试代码：
@Test
public void testMysqlPage() throws Exception{
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8", "root", "root");
PreparedStatement limitStmt = connection.prepareStatement("SELECT * FROM `user` ORDER BY id ASC LIMIT ?,?");
PreparedStatement whereStmt = connection.prepareStatement("SELECT * FROM `user` WHERE id >? ORDER BY id ASC LIMIT ?");
long limit=10;
int queryTimes=40;
long offset=0;
long time;
time = calculateQueryTime(offset, limit, queryTimes, limitStmt);
System.out.println(String.format("使用limit的分页平均查询时间为-{%s}ms，offset-{%s}，limit-{%s}",new Object[]{time,offset,limit}));
time=calculateQueryTime(offset,limit,queryTimes,whereStmt);
System.out.println(String.format("使用where的分页平均查询时间为-{%s}ms，offset-{%s}，limit-{%s}",new Object[]{time,offset,limit}));
offset=400000;
time = calculateQueryTime(offset, limit, queryTimes, limitStmt);
System.out.println(String.format("使用limit的分页平均查询时间为-{%s}ms，offset-{%s}，limit-{%s}",new Object[]{time,offset,limit}));
time=calculateQueryTime(offset,limit,queryTimes,whereStmt);
System.out.println(String.format("使用where的分页平均查询时间为-{%s}ms，offset-{%s}，limit-{%s}",new Object[]{time,offset,limit}));
offset=1000000;
time = calculateQueryTime(offset, limit, queryTimes, limitStmt);
System.out.println(String.format("使用limit的分页平均查询时间为-{%s}ms，offset-{%s}，limit-{%s}",new Object[]{time,offset,limit}));
time=calculateQueryTime(offset,limit,queryTimes,whereStmt);
System.out.println(String.format("使用where的分页平均查询时间为-{%s}ms，offset-{%s}，limit-{%s}",new Object[]{time,offset,limit}));
}
private static long calculateQueryTime(long offset,long limit,int queryTimes,PreparedStatement preparedStatement) throws Exception{
preparedStatement.setLong(1,offset);
preparedStatement.setLong(2,limit);
long start=System.currentTimeMillis();
for(int i=0;i<20;i++){
preparedStatement.executeQuery();
}
return System.currentTimeMillis()-start;
}代码比较简单，就是使用2种分页方案分别查询offset为0，offset为400000，offset为1000000的分页数据，每个查询都重复进行40次，最后取平均数作为该次查询的平均查询时间。由于只是试验目的，所以代码中省略了关闭资源以及异常捕获等逻辑。实验结果如下：
实验结果有点出乎意料，当offset为0时，2种分页方案的平均查询时间相差无几。当offset为400000的时候，基于limit的分页方案的查询时间是基于where的分页方案的查询时间的100倍左右。当offset为1000000的时候，基于limit的分页方案的查询时间是基于where的分页方案的查询时间的200倍左右。这结果与上面的执行计划对比结果大相径庭，因此也说明了执行计划并不能完完全全地反映SQL语句的执行过程。
总结
看了以上试验结果，我们来猜测一下（当然只是猜测）MySQL对于以上两种方案的执行过程是怎样的。首先来看基于limit的分页方案的实验结果。我们可以看到，随着offset的增大，平均查询时间呈线性增长了，所以可以猜测，MySQL对于这种分页是这样处理的：先查询出前(offset+pageSize)行记录，再排序，然后取出后pageSize条记录，因此需要扫描的行数会随着offset的增大而增加。
再来看基于where的分页方案的实验结果。我们可以看到，随着offset的增大，平均查询时间并没有呈现明显的线性增长，3个不同量级的offset的平均查询时间都是相同量级的，所以我们可以猜测（也仅仅是猜测），MySQL对于这种分页查询是先走索引查出offset所在的记录行，再利用B+tree索引的特点，通过遍历链表查询出offset+1到offset+pageSize的记录行，因此这种查询方案的查询速度取决于MySQL定位到第offset行的时间，理论上跟offset的关系不是呈线性关系的，因此随着offset的增大平均查询时间并没有明显的增长。
因此，如果表记录数比较多，不建议使用基于limit的分页方案，而要使用基于where的分页方案。不过，也不是任何时候都可以使用基于where的分页方案的，其适用性在这篇文章《如何优雅地实现分页查询》讲过了，不再赘述。
腾讯云开发者
