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;