oracle分页 最后几页很慢,数据量大时limit分页慢的问题

今天有朋友跟我天了一个数据量大的时候，limit分页慢的问题。

网上查了一些资料，说了很多方法，决定自己试一试。

0、准备数据

先建一张简单的表，只有id和name，id是主键

image.png

image.png

1、limit 0,100，速度很快(右下角有执行时间)

select id,name from test limit 0,100

image.png

2、limit 5000000,100，同样是查询100条，速度就慢多了

limit 5000000,100的意思扫描满足条件的5000100行，扔掉前面的500w行，返回最后的100行，当然慢了。

select id,name from test limit 5000000,100

image.png

所以实际上我们的sql就慢在计算第500w零1条的id

3、计算第500w零1条的id

1)如果id连续

假定我们的id是里连续的，那么用id>500w即可，只需要0.007秒，效率非常高

select id,name from test where id>5000000 limit 0,100

image.png

2)如果id不连续

事实是id一般不是连续的，比如我们突然删除(或者逻辑删除)了其中的几百条，那么id就断了，这种情况下就需要我们自己来计算id

sql计算id

很多材料都提到用select id from test limit 5000000,1的方式来计算id，如下，貌似会快一点点，但是也不稳，多次执行求平均，跟limit 500w,100差别并不大(会快一点点)。

select id,name from test where id>=(select id from test limit 5000000,1) limit 100

第一次执行

第二次执行

用一个字段缓存序列

既然id不连续，那我们就创造一个连续的字段，比如我这里增加了一个order_no，从1-500w，再次执行我就直接按照order_no来计算limit的开始数值，效率显然跟id没差。

image.png

使用order_no获取limit的起始值

当然你得给order_no建立索引，如果没索引就惨了，如下图：

order_no没索引的情况

不过这种方式看似美好，也带来一个更大的问题，我们需要他是连续的，这就意味着每次我们删除一条数据的时候，就需要批量更新order_no。但是一次更新800w条数据显然对数据库压力很大，每次删除都更新不太现实。

不过我们可以考虑定时来刷新，比如每天晚上更新一次。这样的结果是，如果你删除了某一条，该页展示的时候就会少一条数据，如果你删除了100条，某一页可能一条数据都没了，第二天才正常，看你能不能接受了。

这种方法，对于一些定期增加的历史表、转储表，个人感觉很合适。

4、限制页数

我们前面一直在说limit 500w,100会慢，但是从业务角度来说，这以为着什么呢？100条一页，500w，意味着第5w页，真的有用户会翻到第5w页去查看信息吗？所以我们完全可以限制用户可查询的页数，想看更多？请输入查询条件来筛选。

5、总结

所以我们大概有3种方式来解决limit分页慢的问题，请根据自己的数据量和业务需求进行选择：

1、id连续的情况下，直接用where id>500的方式来解决

2、id不连续的情况下，使用select id from test limit 5000000,1来获取limit起始值，但是实际测试发现效果不大

3、id不连续的情况下，新建一个order_no字段用来计算起始值，效率很高，但是需要解决order_no更新的问题

4、限制查询页数

欢迎大家回复，留下一些的意见，或者来一波关注可好，多谢多谢！