• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

使用延迟关联对LIMIT语句进行分页性能优化

MySQL SQL 彭东稳 8年前 (2016-06-28) 32571次浏览 已收录 0个评论

在SQL语句中,limit语句经常会使用到,在程序中分页都是使用limit语句来完成的。但是如果你只是粗暴地使用select * from table limit start,count;来完成分页操作,那么随着数据量的增大,一次分页操作将会成本将会不断增大。但是如果正确使用limit语句的话,将大大提高查询速度。下面来讲讲如何正确使用limit语句,测试表百万行。

测试环境

先让我们熟悉下基本的sql语句,来查看下我们将要测试表tabletest的基本信息。

查询结果:

从上图中我们可以看到表的基本信息:

表行数:4798670行

平均每行的数据长度:827字节

单表大小:3970957312字节(expr 3970957312 / 1024 / 1024 = 3787M)

tabletest表中字段各种类型都有varchar、datetime、text等,说几个关键表字段:表字段id、mid、bp,其中id为primary key、mid为key、bp没有索引。

测试实验

单表分页语句比较简单,一般就是用index来减少排序,必要的时候需要用到延时关联。我这里为了简化,就没有加入排序这个动作,正常情况下排序后分页才是规范写法。

1)直接用limit start, count分页语句

当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开始分页的执行时间(每页取20条), 如下:

PS:第一次执行完毕后缓冲池就会缓冲部分数据,所以你第二次执行会看到时间下降很多。

我们已经看出随着起始记录的增加,时间也随着增大, 这说明分页语句limit跟起始页码是有很大关系的。

从中我们也能总结出两件事情:

1)limit语句的查询时间与起始记录的位置成正比;

2)MySQL的limit语句是很方便,但是对记录很多的表并不适合直接使用;

2)对limit分页问题的性能优化方法-延迟关联

推荐使用“延迟关联”的方法来优化排序操作,何谓”延迟关联” :通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据来加速分页查询。

我们都知道,利用了索引查询的语句中如果只包含了那个索引列(覆盖索引),那么这种情况会查询很快。因为利用索引查找有优化算法,且数据就在查询索引上面,不用再去找相关的数据地址了,这样节省了很多时间。

在我们的例子中,我们知道id字段是主键,自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何。

这次我们利用覆盖索引,只包含id列,如下:

执行计划:

可以看出查询速度提升很多,那么如果我们也要查询所有列,有两种方法,一种是id>=的形式,另一种就是利用join,看下实际情况:

执行计划:

另一种写法,延时join的方式。

先让索引找到1000001-1000020行的主键,然后拿这20条主键跟表进行join,可以减少回表的数据。

同样,对于多表分页语句的优化,也需要利用到索引的排序特性,但是只能是order by驱动表上的列才能使用索引来避免排序。


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (2)
[资助本站您就扫码 谢谢]
分享 (0)

您必须 登录 才能发表评论!