分页SQL的优化

相信不少同学像我一样，在刚接触分页查询时，只知道在SQL中使用limit和offset就可以实现分页数据查询的效果。当然，并不是所有的数据库都支持limit、offset，但它们有各自的实现方式。比如，Oracle通常使用rowno，SQL Server通常使用top，还有其他数据库独特的实现方式。

这篇文章不讨论各种数据库是怎么通过SQL查询实现分页效果的，只说一下关于实现SQL分页查询可能会产生的效率问题，以及怎么尽可能的去规避这个问题。在这篇文章中，我使用PostgreSQL，通过limit，offset实现一个简单的分页查询效果。当然，这不仅限于使用limit和offset的SQL语句。

select * from mail_message order by id limit 100 offset 0;

上面这条SQL就模拟实现了第一页数据的查询（假定每页显示100条，按id升序）。offset是指跳过多少条，limit是指取多少条数据。

通常，分页的实现，都是在页面上记录当前页数current_page_no和每页记录数num_per_page。通过特定公式计算出limit和offset的值：

limit = num_per_page
offset = (current_page_no - 1) * num_per_page

每次前端换页，只要计算出这两个值，传到后端拼接为SQL即可实现查询。很多时候，开发者实现到这一步，到前端页面，点点按钮，看看数据对不对，就觉得差不多了。

但这样就匆忙地忽略了超大数据量带来的查询效率问题。这里先做一个查询，给同学们看看，当前的数据量：

sql> select count(*) from mail_message;
[2020-09-21 16:14:49] 1 row retrieved starting from 1 in 32s 491ms (execution: 32s 336ms, fetching: 155ms)

可以看到，这个表中有17572754条数据，查询耗时32秒。

接下来，我们模拟换页，分别查询第1页，第100页，和第10000页的数据。

第1页，用时 88ms：

sql> select * from mail_message order by id limit 100 offset (1-1)*100;
[2020-09-21 16:17:11] 100 rows retrieved starting from 1 in 149ms (execution: 88ms, fetching: 61ms

第100页，用时 331ms：

sql> select * from mail_message order by id limit 100 offset (100 - 1) * 100;
[2020-09-21 16:19:12] 100 rows retrieved starting from 1 in 430ms (execution: 331ms, fetching: 99ms)

第10000页，用时 1s 144ms：

sql> select * from mail_message order by id limit 100 offset (10000 - 1) * 100;
[2020-09-21 16:46:46] 100 rows retrieved starting from 1 in 1s 192ms (execution: 1s 144ms, fetching: 48ms)

当数据量达到亿级，你觉得前端会等待多长时间？

回过来，为什么会出现这种效率问题呢？单纯的使用limit、offset去做查询，在数据量大时，是很低效的，他会扫描整个表格，从0开始，扫描到第十万条、一百万条、一千万条后，才会继续limit的功能性查询。也就是说，limit可能很快，耗时的地方是offset产生的。

那么，如何优化？你可以尝试将offset去掉，换成使用where条件，改成下面这个样子：

select * from mail_message where id > 10000000 order by id limit 100;

这样做的前提是，你要在页面上，新增一个标记，记录下每次查询后的最大id（上面的代码中，10000000就是上一页查询得到的最大id）。

比较下面两条SQL查询的用时，（数据库id中有断号情况，所以第一条SQL查出来的最大ID是115150，但这并不影响第二条SQL查询）：

sql> select * from mail_message order by id limit 100 offset (1001 - 1) * 100;
[2020-09-21 17:50:12] 100 rows retrieved starting from 1 in 249ms (execution: 172ms, fetching: 77ms

sql> select * from mail_message where id > 115150 order by id limit 100;
[2020-09-21 17:51:32] 100 rows retrieved starting from 1 in 92ms (execution: 74ms, fetching: 18ms)

可以看到，查询同样的数据，用时相差还是很大的。最后总结：

建议在表中增加自增字段，方便分页使用
使用where条件替换offset，优化分页查询效率