limit分页优化

mysql 查询使用 select 命令，通常配合 limit ， offset 参数可以读取指定范围的记录，我们经常用来分页。

但是，你有发现吗？

问题：当 offset （偏移量）越大时，sql 的查询速度就越慢。也就是说，当数据很大时：

select * from tb_a limit 0, 100;

select * from tb_a limit 1000000, 100;

前者的查询效率会高于后者，因为，offset 越大，（索引）扫描页越多，所以导致变慢。

 

假如我们使用了索引呢？ 在 number 字段加上索引：

select * from tb_a limit 100000, 100;

select * from tb_a where number=1 limit 100000, 100;

结果还是 前者的查询效率会高于后者，因为，number 字段是非聚簇索引，在 InnoDB 中，查询过程为：

• 通过二级索引（ number 索引）查到主键值（找出所有 number=1 的 id )。
• 再根据查到的主键值，通过主键索引找到相应的数据块（根据 id 找出对应的数据块内容）。
• 根据 offset 的值，查询 100100 次主键索引的数据，最后将之前的 100000 条丢弃，取出最后 100 条。

从结果可以知道，即使加了索引，虽然查询时索引起了作用，但是这也导致了查询次数增加，增多了访问数据块的I/O操作，查询效率也就下降了。

 

从上面得知，在使用 limit 的时候，加了索引的查询效率还低于没加索引，是不是我们就不加索引了。那肯定不是啦！

索引确实能提高查询效率，之所以查询效率低，只是因为使用的方法不正确，才会导致即使索引起作用，查询速度还慢的情况。

 

解决方法： 

对 limit 分页问题的性能优化方法，可以利用表的 覆盖索引 来加速分页查询，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。因为利用索引查找有优化算法，且数据块就在查询索引上面，不用再去找相关的数据块，这样节省了很多时间，也就是说，查询的数据就在索引上，不用再经过 回表 的操作。例如：

select id from tb_a where number=1 limit 100000, 100;

id 是主键索引（聚簇索引），number 是二级索引（非聚簇索引），二级索引的叶子结点上存储的是主键索引值，而我们只需要查询主键即可，因此就不用 回表 查询多一次。详细可见：数据库索引原理

 

使用子查询优化：

将上面的语句改为

select * from tb_a where number = 1 id >= (select id from tb_a where number = 1 limit 100000, 1) limit 100;

使用 join 查询优化：

select * from tb_a as a inner join (select id from tb_a where number = 1 limit 100000, 100) as b on a.id = b.id where a.number = 1;

使用 between ... and 优化：

select * from tb_a where number = 1 and id between 100000 and 100100 limit 100; 

使用 in 优化：

select * from tb_a where number = 1 and id in (select id from tb_a where number = 1 limit 100000, 100) limit 100; 

有没有发现，上面4种优化方式都是和 id 主键相关的，也就是说，这需要 id 是连续递增的，这种方式先定位偏移位置的 id 值（如子查询、join、in查询优化），然后再往后查询，而 between ... and 查询优化的限制是只能使用于明确知道 id 的情况。

通用的解决方法是：先使用范围查询定位 id （或者索引），然后再使用索引进行定位数据，换个说法就是，先查出偏移后的主键，再根据主键索引查询数据块的所有内容，这样能够提高好几倍查询速度。即先 select id ，然后再 select * ；

但是在一些场景下，比如使用历史表的时候，或者出现过数据缺失问题时，可以考虑使用临时存储的表来记录分页的 id ，使用分页的 id 来进行 in 查询。

当 mysql 的分页 offset 过大时还是建议用主键分页吧！

 

PS:

InnoDB中有 innodb_buffer_page ，用于存放最近访问过的数据页，包括数据页和索引页。

select index_name, count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('primary', 'number') and TABLE_NAME like '%tb_a%' group by index_name;

经过优化后的 sql 语句减少了多次通过主键索引访问数据块的I/O操作，提高执行效率。也证实了在 InnoDB 中，offset 过大影响性能的原因是多次通过主键索引访问数据块的I/O操作。而查询所有字段会导致主键索引多次访问数据块造成的I/O操作。