MySQL-字节千万级数据分页查询优化方案

大家好，我是「云舒编程」，今天我们来聊聊MySQL分页查询优化那些事。

一、MySQL分页查询优化(基于limit offset，size)

1、表结构：

create table `test`(
  `id` int(11),
   `className` varchar(255) NOT NULL comment `班级名`,
   `name` varchar(255) NOT NULL comment `学生姓名`,
   primary KEY (`id`),
   KEY idx_className(`className`) USING BTREE
)

插入一千万条数据到表中，保证className相同的数据至少有100万条。

2、直接查询

直接分页查询耗时70s：

select * from test where class_name = "班级一" limit 900000,10;

看mysql执行计划，的确用到了索引，但是查询耗时长达70s。

一般分页查询有两种做法：

1、先查询出90万+10条记录的id，回表查询数据，再将90万+10条完整记录发给MySQL以便筛选最后10条；
2、先查询出90万+10条记录的id，筛选出最后10条记录的id再回表查询，最后返回10条完整记录给MySQL。
在回表次数很多（limit决定）的情况下，显然第二种方法是比较快的，但是MySQL默认采用了第一种。

3、优化方案

很明显mysql默认的limit处理方式问题在于回表次数太多了，那么如果降低了回表次数（减少IO次数），性能是否提升呢？

3.1、通过利用覆盖索引直接获取id，这里可以直接走索引，性能较高。再将筛选完的id到主键索引查询。

select * from test as t1 inner join (select id from test where class_name = "班级一" order by id desc limit 900000,10) t2 USING(id);

优化后的效果显著：从70秒变为了0.47秒。

3.2、利用上一次的最大id筛选数据

//上图的最大id是 998507,查询时让id > 998507,再直接limit 10就可以得到第91万页了
select * from test where class_name = "班级一" and id > 998507 limit 10;

查询效率依旧很快，0.01秒返回结果

推荐阅读

1、原来阿里字节员工简历长这样

2、一条SQL差点引发离职

3、MySQL并发插入导致死锁

---

如果你也觉得我的分享有价值，记得点赞或者收藏哦！你的鼓励与支持，会让我更有动力写出更好的文章哦！
更多精彩内容，请关注公众号「云舒编程」