MySQL 分页查询优化

今天写自己的项目的时候碰到了一个关于分页的问题,但是参考的那个分页写的很朴素(就是很费时~),正好以前也貌似听说过分页的优化.就自己动手测了一下优化前后的时间开销.

在 MySQL 中(为什么不谈 Oracle 或者 SQL Server? 因为我没用过阿~),我所查询到的分页常用的方案有使用limit子查询 俩种方案.

在我参考的那个项目中,针对与 MySQL 的分页直接用的 SELECT * FROM (table) LIMITE (num1),(num2) 进行分页. 但是我查了查这种分页方式有点垃圾不大好. 垃圾不好之处在于当偏移量增大后,查询速度会明显变慢. 先说有没有, 再说是不是. 为了避免 你抄我博文我抄你博文最后全抄错 的情况,我决定亲自试一下.


开局准备

建表语句如下:

1
2
3
4
5
6
7
8
9
create table test
(
id int auto_increment primary key,
col1 int null,
col2 varchar(20) null,
col3 varchar(20) null,
col4 varchar(20) null,
col5 varchar(20) null
);

然后我在里面插了一堆数据,大致都如下面这样

类似上面的数据,我共计插入了 10059564 行. 一般来说百万行就要进行分库分表了,这里为了测试,就写到了千万行.

注意

本次测试均在未建立索引,未排序情况下进行. 至于建立索引/顺序分页,等我要用到的时候再回来补充

Limit 方案

先试一下SELECT * FROM test LIMIT num1,num2, 看看它到底哪里慢.

执行结果如下:

num1num2执行时间(s)
10001000000.06399575
100001000000.07355725
1000001000000.1037665
10000001000000.324019
100000001000002.62835225
1000100000.01679925
10000100000.01331575
100000100000.03185775
1000000100000.2528165
10000000100003.19394325

由上表可以看出来, 确实是 当偏移量增大后,查询速度会明显变慢, 尤其是在行数到达千万级后,最佳方案是将行数维持在 100000 以内.换句话说, Limit 只适用于中小型表的分页.

使用子查询优化

接下来我们再试一下子查询优化. 既然时间花费在偏移量上,那我们就先找到偏移量,再使用 Limit 进行查询. SQL 如下:
SELECT * FROM test WHERE id >= (SELECT id FROM test LIMIT num1,1) LIMIT num2

然后再看一下时间开销

num1num2执行时间(s)
10001000000.07758550
100001000000.08230150
1000001000000.09752950
10000001000000.24955775
100000001000001.85480700
1000100000.00879800
10000100000.00879800
100000100000.02538050
1000000100000.15585200
10000000100001.76606600

总结

num1num2执行时间(Limit)执行时间(子查询)
10001000000.063995750.07758550
100001000000.073557250.08230150
1000001000000.10376650.09752950
10000001000000.3240190.24955775
100000001000002.628352251.85480700
1000100000.016799250.00879800
10000100000.013315750.00879800
100000100000.031857750.02538050
1000000100000.25281650.15585200
10000000100003.193943251.76606600

从测试结果来看,使用 子查询优化是不错的选择, 当然,在表较小的时候,使用 Limit 进行分页也无可厚非. 但是频繁分页访问的表还是优先推荐 子查询优化.

后记

其实,在 W3Cschool 上还有一种 以ID限定进行优化的方案,但是个人觉得连续递增ID这个条件很难实现(自增的时候总要删掉一些没用数据)故没有进行测试. 有兴趣的可以自己试一试

觉得文章不错的话可以请我喝一杯茶哟~
  • 本文作者: bestsort
  • 本文链接: https://bestsort.cn/2019/08/29/829/
  • 版权声明: 本博客所有文章除特别声明外,均采用 BY-SA 许可协议。转载请注明出处!并保留本声明。感谢您的阅读和支持!
-------------本文结束感谢您的阅读-------------