验证使用子查询提高MySQL分页效率

      很久以前,我写过一篇文章,说明了MySQL下的几种分页方式。这些天,陆续有几个人问过我其中的子查询方式,并对子查询分页的高效率表示质疑。今天我特意做了一个试验来验证这一点。

      我选择了公司一个Discuz测试论坛作为试验体,其cdb_posts的记录数接近10000000行。

      先验证最基本的分页方式:

      在PhpMyAdmin里执行如下SQL:

SELECT * FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 30

      多执行几次,避免Cache等影响,取平均值,其执行时间大约为:6.6140 秒

      再验证子查询的分页方式:

      在PhpMyAdmin里执行如下SQL:

SELECT * FROM `cdb_posts` WHERE pid >= (
    SELECT pid FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 1
) LIMIT 30

      同样多执行几次,避免Cache等影响,取平均值,其执行时间大约为:0.6049 秒

      =================

      其实效率的差别就在于以下两种方式的差别:

SELECT * FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 1(6.7732 秒
SELECT pid FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 1
0.5838 秒

      有网友说如果是MySQL静态表的话,两个查询的速度应该基本一样,到底是不是我再做实验验证一下,同样是上面所用的表,只是删除了所有的varchar, text之类的变长度字段,以保证其是静态表,然后执行:

SELECT * FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 1(2.1303 秒
SELECT pid FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 1
0.5532 秒

    可以发现,转换成静态表之后,SELECT *后的速度确实快了一些,但查询速度仍然处于秒的级别,可以说还是很慢的。

      综上所述:当限定了字段,并且这个字段是一个索引的时候,LIMIT可以直接在索引文件中查找,而不是在实际的数据文件中查找,所以需要“跨越”的数据块体积要小很多。不过感觉这本应该可以在MySQL内部得到透明的优化才对,或许是因为MySQL的优化器比较笨吧。

      补充:http://www.mysqlperformanceblog.com/2008/09/24/four-ways-to-optimize-paginated-displays/

觉得文章有用?立即: 和朋友一起 共学习 共进步!

猜您喜欢

文章评论: “验证使用子查询提高MySQL分页效率

  1. Pingback: MySQL limit 優化,百萬至千萬級快速分頁:複合索引 - 程序員的後花園

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

*

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>