Mysql 千万级别数据数据查询
1.构建数据
--创建MyISAM模式表方便批量跑数据 CREATE TABLE `logs1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `logtype` varchar(255) DEFAULT NULL, `logurl` varchar(255) DEFAULT NULL, `logip` varchar(255) DEFAULT NULL, `logdz` varchar(255) DEFAULT NULL, `ladduser` varchar(255) DEFAULT NULL, `lfadduser` varchar(255) DEFAULT NULL, `laddtime` datetime DEFAULT NULL, `htmlname` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=1811 DEFAULT CHARSET=utf8 COMMENT='日志表';
--创建存储过程 DROP PROCEDURE IF EXISTS my_insert; CREATE PROCEDURE my_insert() BEGIN DECLARE n int DEFAULT 1; loopname:LOOP INSERT INTO `logs1`(`logtype`,`logurl`,`logip`,`logdz`,`ladduser` ,`lfadduser`,`laddtime`,`htmlname`) VALUES ( 2, '/index', '0:0:0:0:0:0:0:1', null, null, 'null', '2018-05-03 14:02:42', '首页'); SET n=n+1; IF n=10000000 THEN LEAVE loopname; END IF; END LOOP loopname; END; --执行存储过程 CALL my_insert();
--数据插入成功后修改表模式InnoDB 时间稍微久点 alter table `logs1` engine=InnoDB; 2.查询 (1)SELECT COUNT(id) FROM test.logs1 结果: 99999999 (2)SELECT * FROM test.logs1 ORDER BY id ASC LIMIT 100000,100
结果: [SQL] SELECT * FROM test.logs1 ORDER BY id ASC LIMIT 100000,100
受影响的行: 0
时间: 0.169s
(3)SELECT * FROM test.logs1 ORDER BY id ASC LIMIT 1000000,100
结果:[SQL] SELECT * FROM test.logs1 ORDER BY id ASC LIMIT 1000000,100
受影响的行: 0
时间: 4.644s
mysql 百万级别查询就会很慢
