今天面试被问到一张表 有500w条数据,如何进行分页查询,瞬间不知道怎么回答,平时工作中没接触到这么大的数据量。
所以回家自己去实验一下:
创建一张user表
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(100) DEFAULT NULL COMMENT '用户名',
`password` varchar(100) DEFAULT NULL COMMENT '密码',
`name` varchar(100) DEFAULT NULL COMMENT '姓名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1862452 DEFAULT CHARSET=utf8;
接着我们给他添一点数据:
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=1000000 DO
INSERT INTO user(username,password,name) VALUES(CONCAT(i,'老李'),'2222','老李');
SET i = i+1;
END WHILE;
END $
CALL proc_initData();
我添加了100w条数据我的电脑比较差用了十几分钟,当然你也可以选择不建主键,这样添加的话速度会快很多(主键会建主键索引)。
现在我们来开始测试查询:
使用传统的
select * from user LIMIT 800000,20
ok现在看一下sql的执行过程:
全表扫描 不走索引的
将sql改造一下使用子查询
select * FROM user where id >= (select id from user LIMIT 800000,1) LIMIT 20
效果很明显ok 速度提升了好4倍多
ok我们现在看看 执行sql过程:
很明显走了索引 子查询走了全表索引扫描index 主sql走的是range
当然这个还有另外一张写法
select * FROM user a
join
(select id from user LIMIT 800000,20) b
on a.id=b.id
效率和上面的差不多
ok现在做个简单的改造
select * FROM user where id >= 800000 LIMIT 20
这个效率提高了很多
range单个索引