戳上方蓝字“Java笔记虾”关注我
在项目中遇到的真实问题，以及我的解决方案，部分数据做了脱敏处理。
问题
最近在做项目时需要写sql做单表查询，每次查出来的数据有几百万甚至上千万条，公司用的数据库是MySQL5.7，做了分库分表，部分数据库设置了查询超时时间，比如查询超过15s直接报超时错误，如下图：
可以通过show variables like 'max_statement_time';命令查看数据库超时时间（单位：毫秒）：
方案1
尝试使用索引加速sql，从下图可以看到该sql已经走了主键索引，但还是需要扫描150万行，无法从这方面进行优化。
方案2
尝试使用limit语句进行分页查询，语句为：
SELECT * FROM table WHERE user_id = 123456789 limit 0, 300000;
像这样每次查30万条肯定就不会超时了，但这会引出另一个问题–查询耗时与起始位置成正比，如下图：
第二条语句实际上查了60w条记录，不过把前30w条丢弃了，只返回后30w条，所以耗时会递增，最终仍会超时。
方案3
使用指定主键范围的分页查询，主要思想是将条件语句改为如下形式（其中id为自增主键）：
WHERE user_id = 123456789 AND id > 0 LIMIT 300000;
WHERE user_id = 123456789 AND id > (上次查询结果中最后一条记录的id值) LIMIT 300000;
也可以将上述语句简化成如下形式（注意：带了子查询会变慢）：
WHERE user_id = 123456789 AND id >= (SELECT id FROM table LIMIT 300000, 1) limit 300000;
每次查询只需要修改子查询limit语句的起始位置即可，但我发现表中并没有自增主键id这个字段，表内主键是fs_id，而且是无序的。
这个方案还是不行，组内高工都感觉无解了。
方案4
既然fs_id是无序的，那么就给它排序吧，加了个ORDER BY fs_id，最终解决方案如下：
WHERE user_id = 123456789 AND fs_id > 0 ORDER BY fs_id LIMIT 300000;
WHERE user_id = 123456789 AND fs_id > (上次查询结果中最后一条记录的id值) ORDER BY fs_id LIMIT 300000;
效果如下图：
查询时间非常稳定，每条查询的fs_id都大于上次查询结果中最后一条记录的fs_id值。正常查30w条需要3.88s，排序后查30w条需要6.48s，确实慢了许多，但总算能把问题解决了。目前代码还在线上跑着哈哈，如果有更好的解决方案可以在评论区讨论哟。
来源｜juejin.cn/post/7209612932366270519
后端专属技术群 构建高质量的技术交流社群，欢迎从事编程开发、技术招聘HR进群，也欢迎大家分享自己公司的内推信息，相互帮助，一起进步！
文明发言，以
交流技术、职位内推、行业探讨为主广告人士勿入，切勿轻信私聊，防止被骗
加我好友，拉你进群
原文始发于微信公众号（Java笔记虾）：为什么 MySQL 最好不要只用 limit 做分页查询？
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/187324.html
