(一)
mysql limit大数据量分页优化方法
首先创建一个表
CREATE TABLE `ipdatas` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(8) NOT NULL DEFAULT '0',
`ipaddress` varchar(50) NOT NULL,
`source` varchar(255) DEFAULT NULL,
`track` varchar(255) DEFAULT NULL,
`entrance` varchar(255) DEFAULT NULL,
`createdtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`createddate` date NOT NULL DEFAULT '0000-00-00',
PRIMARY KEY (`id`),
KEY `uid` (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=68293856 DEFAULT CHARSET=utf8;
插入大量数据100万
begin
declare i int;
set i = 10001 ;
myLoop: LOOP
if i = 100000 then
leave myLoop;
end if;
/* do something */
-- 循环输出信息
insert into `ipdatas`(`uid`,`ipaddress`,`source`,`track`,`entrance`,`createdtime`,`createddate`)
values(i,'127.0.0.1','wanglitao','guoyanhui','127.0.0.1',now(),now());
set i = i +1;
/* 循环结束 */
end loop myLoop;
end
优化语句
原语句
select * from ipdatas order by id limit 900000,100
受影响的行: 0
时间: 16.408s
优化语句
Select * From ipdatas Where id>=(
Select id from ipdatas order by id limit 900000,1
)limit 100;