今天写自己的项目的时候碰到了一个关于分页的问题,但是参考的那个分页写的很朴素(就是很费时~),正好以前也貌似听说过分页的优化.就自己动手测了一下优化前后的时间开销.
在 MySQL 中(为什么不谈 Oracle 或者 SQL Server? 因为我没用过阿~),我所查询到的分页常用的方案有使用limit
和 子查询
俩种方案.
在我参考的那个项目中,针对与 MySQL 的分页直接用的 SELECT * FROM (table) LIMITE (num1),(num2)
进行分页. 但是我查了查这种分页方式有点垃圾不大好. 垃圾不好之处在于当偏移量增大后,查询速度会明显变慢. 先说有没有, 再说是不是. 为了避免 你抄我博文我抄你博文最后全抄错 的情况,我决定亲自试一下.
开局准备
建表语句如下:
1 | create table test |
然后我在里面插了一堆数据,大致都如下面这样
类似上面的数据,我共计插入了 10059564 行. 一般来说百万行就要进行分库分表了,这里为了测试,就写到了千万行.
注意
本次测试均在未建立索引,未排序情况下进行. 至于建立索引/顺序分页,等我要用到的时候再回来补充
Limit 方案
先试一下SELECT * FROM test LIMIT num1,num2
, 看看它到底哪里慢.
执行结果如下:
num1 | num2 | 执行时间(s) |
---|---|---|
1000 | 100000 | 0.06399575 |
10000 | 100000 | 0.07355725 |
100000 | 100000 | 0.1037665 |
1000000 | 100000 | 0.324019 |
10000000 | 100000 | 2.62835225 |
1000 | 10000 | 0.01679925 |
10000 | 10000 | 0.01331575 |
100000 | 10000 | 0.03185775 |
1000000 | 10000 | 0.2528165 |
10000000 | 10000 | 3.19394325 |
由上表可以看出来, 确实是 当偏移量增大后,查询速度会明显变慢, 尤其是在行数到达千万级
后,最佳方案是将行数维持在 100000 以内.换句话说, Limit
只适用于中小型表的分页.
使用子查询优化
接下来我们再试一下子查询优化. 既然时间花费在偏移量上,那我们就先找到偏移量,再使用 Limit
进行查询. SQL 如下:SELECT * FROM test WHERE id >= (SELECT id FROM test LIMIT num1,1) LIMIT num2
然后再看一下时间开销
num1 | num2 | 执行时间(s) |
---|---|---|
1000 | 100000 | 0.07758550 |
10000 | 100000 | 0.08230150 |
100000 | 100000 | 0.09752950 |
1000000 | 100000 | 0.24955775 |
10000000 | 100000 | 1.85480700 |
1000 | 10000 | 0.00879800 |
10000 | 10000 | 0.00879800 |
100000 | 10000 | 0.02538050 |
1000000 | 10000 | 0.15585200 |
10000000 | 10000 | 1.76606600 |
总结
num1 | num2 | 执行时间(Limit) | 执行时间(子查询) |
---|---|---|---|
1000 | 100000 | 0.06399575 | 0.07758550 |
10000 | 100000 | 0.07355725 | 0.08230150 |
100000 | 100000 | 0.1037665 | 0.09752950 |
1000000 | 100000 | 0.324019 | 0.24955775 |
10000000 | 100000 | 2.62835225 | 1.85480700 |
1000 | 10000 | 0.01679925 | 0.00879800 |
10000 | 10000 | 0.01331575 | 0.00879800 |
100000 | 10000 | 0.03185775 | 0.02538050 |
1000000 | 10000 | 0.2528165 | 0.15585200 |
10000000 | 10000 | 3.19394325 | 1.76606600 |
从测试结果来看,使用 子查询优化是不错的选择, 当然,在表较小的时候,使用 Limit
进行分页也无可厚非. 但是频繁分页访问的表还是优先推荐 子查询优化.
后记
其实,在 W3Cschool 上还有一种 以ID限定进行优化的方案,但是个人觉得连续递增ID这个条件很难实现(自增的时候总要删掉一些没用数据)故没有进行测试. 有兴趣的可以自己试一试