order by + limit 改写优化

3,472 total views, 1 views today

order by +limit 常用于分页功能。本案例中,用ID列进行排序,除了查询ID列外,还查询了value和stuffing列。这种情况容易出现性能问题或者不是性能最优的SQL写法。

测试中发现,如果t_limit为MySIAM。很容易出现性能问题,执行计划走全表扫描不走索引。

现在几乎不用MySIAM,所以本文测试结果基于InnoDB。

在InnoDB引擎前提下,执行上面的SQL,速度也不慢,0.04秒完成。

information_schema.INNODB_BUFFER_PAGE 记录了innodb buffer pool中的每个page的信息。

查看此次查询用到的主键page个数,共12827个pages。(重启MySQL,去除干扰)。

下面,进行SQL改写优化。子查询获取到需要的ID号,然后拿着ID号再和原有表t_limit通过ID列关联,最终得出结果。

执行速度较第一种SQL写法快了0.01秒。

查看执行计划

查看此次查询用到的page个数。

为了防止之前的操作干扰,需重启MySQL,并确保InnoDB buffer pool中没有t_limit表的缓存page。

执行改写后的SQL

查看此次查询用到主键的page总数为4889个,比SQL改写前减少了7938个!

t_limit的主键创建在id列上,那么它会占多少InnoDB buffer pool呢?

我分别用这两种方式,将整个主键缓存到InnoDB buffer pool中,最终占用的page个数相同,都是13546。

 

t_limit主键为13546个pages,SQL在改写前共需要缓存12827个pages,改写减少到4889个pages。

这说明,SQL改写后,需要扫描的索引pages数量减少,提高了SQL执行效率。所以,再次遇到order by + limit 分页SQL时,请按照上面的改写方法书写,能提升不少性能。

 

参考:

https://explainextended.com/2011/02/11/late-row-lookups-innodb/

https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/

https://www.cnblogs.com/zhangyachen/p/8030252.html

发表评论

必填项已用*标注