分页查询优化

1. 分页查询

使用limit offset, size 或者 limit size进行分页，比如下面的sql语句

select * from page order by id limit 10;
select * from page order by id limit 10, 10;

2. 常见问题

在数据量较大，查询较后的数据就会变慢，比如现在查询500w后面的10条数据

select * from page order by id limit 5000000, 10

server层会调用innodb的接口，由于这次的offset=5000000，会在innodb里的主键索引中获取到第0到（5000000 + 10）条完整行数据，返回给server层之后根据offset的值挨个抛弃，最后只留下最后面的size条，也就是10条数据，放到server层的结果集中，返回给客户端。

可以看出，当offset非0时，server层会从引擎层获取到很多无用的数据，而获取的这些无用数据都是要耗时的，
limit offset, size自然比 limit size慢

3. 优化方法

3.1 基于主键索引的limit执行

上面分析到，当offset非0时，server层会从引擎层获取到很多无用的数据，而当select后面是*号时，就需要拷贝完整的行信息

因为前面的offset条数据最后都是不要的，索性前5000000条数据我们就只查id好了，将sql语句修改成下面这样

select * from page where id >= (select id from page order by id limit 5000000,1) order by id limit 10;

上面这条sql语句，里面先执行子查询 select id from page order by id limit 5000000, 1, 这个操作，其实也是将在innodb中的主键索引中获取到5000000+1条数据，然后server层会抛弃前5000000条，只保留最后一条数据的id。

但不同的地方在于，在返回server层的过程中，只会拷贝数据行内的id这一列，而不会拷贝数据行的所有列，当数据量较大时，这部分的耗时还是比较明显的。

3.2 基于非主键索引的limit执行

select * from page where name = 'xxx' limit 0, 10;

常常我们遇到的分页查询都是带条件的，一般很少根据主键查找，在走非主键索引查找的时候，server层会调用innodb的接口，在innodb里的非主键索引中获取到第0条数据对应的主键id后，回表到主键索引中找到对应的完整行数据，然后返回给server层，server层将其放到结果集中，返回给客户端。比起主键索的limit执行，多了回表的消耗

优化方法与3.1相似，只查找主键id，通过与原表id匹配进行主键索引查找，绕开回表的消耗

select * 
from page as p1, (select id from page where name='xxx' limit 5000000, 10) as p2
where p1.id = p2.id