百万数据分页SQL语句实例(MySQL)

[boy]

取总数:
Select SQL_BUFFER_RESULT count(col1) as all_ID from foods;
开始: start = 0
每页长度: page_length = 16
总页数: pages = all_ID/page_length
最后一页的记录数: odd = all_ID%page_length

查询当前页:
set @rownum=0;
select SQL_BUFFER_RESULT id,title,url,img,price
from foods,
( select my_id from
( select id as my_id,(@rownum:=@rownum+1) as row_id from foods
order by modified desc
) as tmp
where row_id between '@{int:1+start}'
and '@{int:start+page_length}'
) as temp
where my_id=id

实际效果:
http://www.dfwgw.com/dGFva2UvbGlzdF9hbGwuaHRtbA==url.html

表结构参考:
CREATE TABLE `foods` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`url` varchar(100) NOT NULL,
`catalog` int(11) NOT NULL DEFAULT '0',
`img` varchar(120) DEFAULT NULL,
`price` double DEFAULT '0',
`des` text,
`modified` datetime NOT NULL,
`sold_num` int(11) NOT NULL DEFAULT '0',
`location` varchar(28) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `catalog` (`catalog`),
KEY `modified` (`modified`),
KEY `sold_num` (`sold_num`),
FULLTEXT KEY `title` (`title`)
)

[WWWWA]

用MYSQL？为什么不用LIMIT？

[boy]

这些方法都用过，但后果以惨，经过N次测试后才得出这SQL，所以拿出来共享一下，希望对大家有帮助！

[WWWWA]

OK，另外一种实现方法，学习

where row_id between '@{int:1+start}' and '@{int:start+page_length}'
能解释一下吗？

[boy]

where row_id between '@{int:1+start}' and '@{int:start+page_length}'
@{int:1+start}当前页的开始位加1，第一页时为1+0=1；
@{int:start+page_length}当前页的开始位加页长度，第一页时为0+16=16
第一页时就是1到16行，第二页时就是17到32行
相当于
where row_id between '1' and '16' 或 where row_id between '17' and '32'

[WWWWA]

哦，能在SQL语句直接这样写吗？主要是想问这个

[boy]

呵呵，当然不能，我们自己做了一个解析器，让前台开发人员省事！

[WWWWA]

[Quote=引用 7 楼 tx18 的回复:]
呵呵，当然不能，我们自己做了一个解析器，让前台开发人员省事！
[/Quote]
呵呵，还以为有一种新的方式，谢谢。

[老哥讲数据库]

这个方法减少了定位所需数据。limit x,y的话，若想数据库量真是百万级别的，x如果是80万的话，那可就慢了。

[nieyu20070322]

不错

[boy]

[Quote=引用 11 楼 kanon_lgt 的回复:]
这个方法减少了定位所需数据。limit x,y的话，若想数据库量真是百万级别的，x如果是80万的话，那可就慢了。
[/Quote]
不错，LIMIT X，Y 还需要大内存，tmp_table_size=128M都不够，而且会导致长时间锁表，系统就越来越慢。
select id as my_id,(@rownum:=@rownum+1) as row_id from foods order by modified desc 先保证查询到最少的数据，tmp_table_size=64M也能快速运行，接下来的操作都是在内存中进行，如果没有其他操作锁表，查询基本上能在一秒内完成。当然这跟硬件也有很大的关系，目前服务为 dell T300，内存加到4G，系统是64位的CentOS5.4，最高时支撑过500人在线，效果还行！

[军爷_010]

好像麻烦了许多？？？不解

[flairsky]

看不见测试效果，点进去是个卖东西的网站？

[懒得去死]

方法不错，值得鼓励！

[wwwwb]

学习，新的取数方法

[qdtianhao]

学习学习

[hitexam]

功力不够，直接没看懂

[qiuyepiaoling]

楼主你错了，
我做了一个试验，
当表中数据 有50多万条的时候 显然是limit快

当表中数据 有100多万条的 时候 仍然是limit快 而且快多了

我的表结构：
CREATE TABLE `i_userfriends` (
`user_id` int(10) unsigned NOT NULL DEFAULT '0',
`friend_id` int(10) unsigned NOT NULL DEFAULT '0',
`group_id` int(11) NOT NULL DEFAULT '0',
`remark` varchar(50) DEFAULT NULL,
`isOpen` tinyint(1) unsigned NOT NULL DEFAULT '1',
`seeType` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`user_id`,`friend_id`),
KEY `ix_user_id_remark` (`user_id`,`remark`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

[qiuyepiaoling]

由于mysql 没有行号
你的 select id as my_id,(@rownum:=@rownum+1) as row_id from foods
order by modified desc
还是执行 全表扫描

[qiuyepiaoling]

所有 速度 不可能快的

[tntooo]

hoho楼上黑马~ 虽然我不懂,但是我对这贴的气氛很感兴趣!