mysql分页问题
时间：2008-05-29 来源：剑心通明
相信很多人对于mysql分页的处理都是沉迷于limit m, n。其中包括我自己。而且我还为此写了一个分页类，那个分页类功能算是十分完善了吧。
但是当我写一个会员管理系统使用我那个类时却发现，当列表页数在1000(每页20行记录)以内的时候，速度还是很快，但当超过5000页之后，速度开始变慢。到50000页的时候，查询时间已经超过27秒。到60000页则完全超时.....数据表记录数为140多万.
试过几次之后，发觉是order by的问题。为了让最新的记录显示在列表前面，我使用了order by `id` desc。当我把order by去掉的时候，发现查询速度大概竟是原来的5倍! 刚开始我还在分页类里面找原因，但很快就否定了。可笑的是，在PHP论坛版块贴出相关贴子的时候，有个人还说:"我只用自己写的分页类，帖子多少都一样...其实什么分页类的，就一个25行函数就可以了，帖子多少都一样，可能思路不一样吧"。而那个人在很多人眼里也算是高手了吧，平时看他的言论也看出他对自己的技术很有自信。可是，高手都是这样思考问题的吗？很明显，问题是出在select语句而不是分页类。
为了达到效果，order by 显然还是不能舍弃的。但实际情况却是，当你有超过几十万行数据的时候，列表出来就很就要遇到上面说的问题了。
怎么办呢？
经过一番思考之后(思考过程忽略不说)，我做了一个测试:从15584009行记录的table中列出最后30行记录。
第一条语句：SELECT * FROM `table` LIMIT 1558380 , 30;
用时5.2694 秒,速度一般，但是，我需要的是倒序的列表，于是我又将查询语句改了。
第二条语句：SELECT * FROM `table` order by `id` DESC LIMIT 1558380 , 30;
用时......很遗憾，超时了。
再经过一番胡思乱想后，我把查询语句改成了如下：
SELECT * FROM `table` WHERE `id` IN ( 15584009, 1558408, 1558407, 1558406, 1558405, 1558404, 1558403, 1558402, 1558401, 1558400, 1558399, 1558398, 1558397, 1558396, 1558395, 1558394, 1558393, 1558392, 1558391, 1558390, 1558389, 1558388, 1558387, 1558386, 1558385, 1558384, 1558383, 1558382, 1558381, 1558380 );
这个语句出来的数据与第一条语句是一样的，但是，用时却是0.0028秒!!! 整整快了1880倍!那与第二条语句相比呢？
看到这里，大家也应该会想了吧？没什么想法也没关系，下面就听我说说我的想法。
假如，在一个有庞大数据的表里面，字段id是由1到最大id而不间断，我们分页的时候就可以用上面的第三种方法了。而其中"字段id是由1到最大id而不间断"我们是可以自己掌握的，比如id用auto_increment，当要删除去其中某一项记录的时候，把大于那记录的id数全部减1，然后再OPTIMIZE TABLE。这样就保证了id是不间断的了。当然，实际处理起来可能会有很多比较麻烦的情况出现。比如群操作删除数据的时候。这些有就待各人的细化处理了，这里不作讨论。还有一点要提的是，设计数据库结构的时候，表关联字段最好就不用要用id关联了，如用户表的话另外定一个userid。论坛的数据库结构设计是很关键的，我看过大部分的结构，都不理想，不信的话你找一个超过100万贴子的论坛栏目试一下速度。
说到这里，你是否打算写一个针对于id不间断的mysql分页类了呢？原理很简单呢？写不出来？懒得写，哈哈，那还是用我上面那个大众化的分页类吧!对于数据量不是庞大的分页来说，它真的很好呢!不过新的分页类我已经写好了，却不一定适用大伙，我就不提供出来了。因为如果看明白了我上面说的，要写是很简单的事了. 能提高1880倍效率的事，是不是很有诱惑力呢？
最后希望各位高手也发表一下自己的意见，主要是从效率上看问题。
上面的文字贴出去后，引起了一番讨论。
有朋友提出：“如果要整体改动一批id（比如删除了某id，后面id向前整体移动)，当数据到达100M时是致命的。 ”
经过测试，得出update 100000行记录用时是6.9595 秒, update 10000行记录用时是0.5556 秒.如果有100多万行记录，我想应该可以在php中放入循环进行update，这样就避免了超时操作。考虑到用delete的几率很低，所以update的方法还是可行的
还有就是鼓励用BETWEEN, 如第三种方法就可以写成
SELECT * FROM `table` WHERE `id` BETWEEN 1558380 and 15584009 order by `id` desc
以面的文字已经作过几次改动了。现在我突然想推翻上面的某些观点。
首先，order by当然是不可弃的，我们说使用它慢，是指当整个表的数据理很庞大而没有使用where或where使用不当的时候。
其次，慢的问题不在于使用limit m, n而在于只使用limit m, n .
后来有一个网友提出“用表的尾部数据填充删除除后的空白数据”。想一下，果然是好办法，这样delete一行数据的话就只是update一行数据，那不就实现了id的不间断吗？
只要id不间断，一切都好办了。^_^
但是又有一个问题了,OPTIMIZE TABLE很耗资源，搞不好数据库down .
革命仍未成功.
但是当我写一个会员管理系统使用我那个类时却发现，当列表页数在1000(每页20行记录)以内的时候，速度还是很快，但当超过5000页之后，速度开始变慢。到50000页的时候，查询时间已经超过27秒。到60000页则完全超时.....数据表记录数为140多万.
试过几次之后，发觉是order by的问题。为了让最新的记录显示在列表前面，我使用了order by `id` desc。当我把order by去掉的时候，发现查询速度大概竟是原来的5倍! 刚开始我还在分页类里面找原因，但很快就否定了。可笑的是，在PHP论坛版块贴出相关贴子的时候，有个人还说:"我只用自己写的分页类，帖子多少都一样...其实什么分页类的，就一个25行函数就可以了，帖子多少都一样，可能思路不一样吧"。而那个人在很多人眼里也算是高手了吧，平时看他的言论也看出他对自己的技术很有自信。可是，高手都是这样思考问题的吗？很明显，问题是出在select语句而不是分页类。
为了达到效果，order by 显然还是不能舍弃的。但实际情况却是，当你有超过几十万行数据的时候，列表出来就很就要遇到上面说的问题了。
怎么办呢？
经过一番思考之后(思考过程忽略不说)，我做了一个测试:从15584009行记录的table中列出最后30行记录。
第一条语句：SELECT * FROM `table` LIMIT 1558380 , 30;
用时5.2694 秒,速度一般，但是，我需要的是倒序的列表，于是我又将查询语句改了。
第二条语句：SELECT * FROM `table` order by `id` DESC LIMIT 1558380 , 30;
用时......很遗憾，超时了。
再经过一番胡思乱想后，我把查询语句改成了如下：
SELECT * FROM `table` WHERE `id` IN ( 15584009, 1558408, 1558407, 1558406, 1558405, 1558404, 1558403, 1558402, 1558401, 1558400, 1558399, 1558398, 1558397, 1558396, 1558395, 1558394, 1558393, 1558392, 1558391, 1558390, 1558389, 1558388, 1558387, 1558386, 1558385, 1558384, 1558383, 1558382, 1558381, 1558380 );
这个语句出来的数据与第一条语句是一样的，但是，用时却是0.0028秒!!! 整整快了1880倍!那与第二条语句相比呢？
看到这里，大家也应该会想了吧？没什么想法也没关系，下面就听我说说我的想法。
假如，在一个有庞大数据的表里面，字段id是由1到最大id而不间断，我们分页的时候就可以用上面的第三种方法了。而其中"字段id是由1到最大id而不间断"我们是可以自己掌握的，比如id用auto_increment，当要删除去其中某一项记录的时候，把大于那记录的id数全部减1，然后再OPTIMIZE TABLE。这样就保证了id是不间断的了。当然，实际处理起来可能会有很多比较麻烦的情况出现。比如群操作删除数据的时候。这些有就待各人的细化处理了，这里不作讨论。还有一点要提的是，设计数据库结构的时候，表关联字段最好就不用要用id关联了，如用户表的话另外定一个userid。论坛的数据库结构设计是很关键的，我看过大部分的结构，都不理想，不信的话你找一个超过100万贴子的论坛栏目试一下速度。
说到这里，你是否打算写一个针对于id不间断的mysql分页类了呢？原理很简单呢？写不出来？懒得写，哈哈，那还是用我上面那个大众化的分页类吧!对于数据量不是庞大的分页来说，它真的很好呢!不过新的分页类我已经写好了，却不一定适用大伙，我就不提供出来了。因为如果看明白了我上面说的，要写是很简单的事了. 能提高1880倍效率的事，是不是很有诱惑力呢？
最后希望各位高手也发表一下自己的意见，主要是从效率上看问题。
上面的文字贴出去后，引起了一番讨论。
有朋友提出：“如果要整体改动一批id（比如删除了某id，后面id向前整体移动)，当数据到达100M时是致命的。 ”
经过测试，得出update 100000行记录用时是6.9595 秒, update 10000行记录用时是0.5556 秒.如果有100多万行记录，我想应该可以在php中放入循环进行update，这样就避免了超时操作。考虑到用delete的几率很低，所以update的方法还是可行的
还有就是鼓励用BETWEEN, 如第三种方法就可以写成
SELECT * FROM `table` WHERE `id` BETWEEN 1558380 and 15584009 order by `id` desc
以面的文字已经作过几次改动了。现在我突然想推翻上面的某些观点。
首先，order by当然是不可弃的，我们说使用它慢，是指当整个表的数据理很庞大而没有使用where或where使用不当的时候。
其次，慢的问题不在于使用limit m, n而在于只使用limit m, n .
后来有一个网友提出“用表的尾部数据填充删除除后的空白数据”。想一下，果然是好办法，这样delete一行数据的话就只是update一行数据，那不就实现了id的不间断吗？
只要id不间断，一切都好办了。^_^
但是又有一个问题了,OPTIMIZE TABLE很耗资源，搞不好数据库down .
革命仍未成功.
相关阅读 更多 +
