Use of SQL statements in big data and SQL statements in Data

Use of SQL statements in big data and SQL statements in Data

(1) mysql limit large data volume paging Optimization Method

First create a table

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;

Insert a large amount of data 1 million

Begindeclare I int; set I = 10001; myLoop: LOOP if I = 100000 then leave myLoop; end if; /* do something */-- insert into 'ipdatas' ('uid', 'ipaddress', 'source', 'track', 'enables', 'createdtime ', 'createddate') values (I, '2017. 0.0.1 ', 'wanglitao', 'guoyanhui', '2017. 0.0.1 ', now (), now (); set I = I + 1;/* loop end */end loop myLoop; end

Optimization statement

Original statement

Select * from ipdatas order by id limit 900000,100

Affected rows: 0
Time: 16.408 s

Optimization statement

Select * From ipdatas Where id> = (
Select id from ipdatas order by id limit 900000,1
) Limit 100;