MySQL 分页查询优化

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

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

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

开局准备

建表语句如下:

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, 看看它到底哪里慢.

执行结果如下:

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这个条件很难实现(自增的时候总要删掉一些没用数据)故没有进行测试. 有兴趣的可以自己试一试