Mysql中limit分页大偏移量的原因分析与优化

Mysql中limit分页大偏移量的原因分析与优化

文章内容截取掘金作者：Planeswalker23文章
原作者地址连接：https://juejin.cn/post/6862530465472741384

场景：
平时写的分页查询：(sex是索引列，非线上sql，只是为了分析)
select * from gen_studentinfo where sex = 1 limit $start,${end}

  数据量少时，性能没有什么影响。随着数据量变大，start值的增加，执行时间会越来越长。

原因分析:

1. 由于sex列是非聚簇索引，索引树中都存储的是主键id的值。查询所有的值，会发生一个回表的情况。在命中sex索引树中值为1的数据后，拿着它叶子节点上的值(主键id的值)去主键索引树上查询这一行其他列的值，最后返回到结果集中，这样第一行数据就查询成功。

2. 最后这句sql要求limit
   start,end,也就是查询第start行到end行的数据。但是mysql会查询前start行，然后将前start行抛弃，最后结果集中就只剩下第start行到end行，执行结束。

   综上所述，造成limit大量便宜执行时间便就的原因有：
   1.查询多有列导致回表
   2.limit start，end会查询前start + end条数据，然后丢弃前start条数据。
   mysql花费大量时间在回表上，而其中a次回表的结果又不会出现在结果集中，这才导致查询时间越来越长。

优化方案：

1. 覆盖索引
   理由：减少回表次数。
   我们受限得到start+1到start + end条数据的id，然后再进行回表获取其他列数据，那么就减少start次回表操作，速度会快上不少。
   覆盖索引：及时从非主聚簇索引中就能查到的想要数据，而不需要通过回表从主键索引中查询其他列，能够现主提升性能。
   聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据。
   非聚簇索引：索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引。
   select * from gen_studentinfo a join (select id from gen_studentinfo where sex = 1 limit start,end) b on a.id = b.id;
2. 条件过滤
   select * from gen_studentinfo where sex = 1 and id > (select id from gen_studentinfo where sex = 1 limit start,1) limit 10;
   使用这种方式优化是有条件的：主键id必须是有序的，在有序的条件下，也可以使用比如创建时间等其他字段来代替主键id，但是前提是这个字段是建立了索引。