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秒返回结果
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2024-01-25，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。