为什么mysql最好不要只用limit做分页查询？

在项目中遇到的真实问题，以及我的解决方案，部分数据做了脱敏处理。

问题

最近在做项目时需要写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条需要4.06s，排序后查30w条需要6.48s，确实慢了许多，但总算能把问题解决了。目前代码还在线上跑着哈哈，如果有更好的解决方案可以在评论区讨论哟。