百万条数据如何进行分页查询

今天面试被问到一张表 有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单个索引