文章很长，且持续更新，建议收藏起来，慢慢读！疯狂创客圈总目录 博客园版 为您奉上珍贵的学习资源 ：

免费赠送 :《尼恩Java面试宝典》 持续更新+ 史上最全 + 面试必备 2000页+ 面试必备 + 大厂必备 +涨薪必备 免费赠送 :《尼恩技术圣经+高并发系列PDF》 ，帮你 实现技术自由，完成职业升级， 薪酬猛涨！加尼恩免费领 免费赠送 经典图书:《Java高并发核心编程（卷1）加强版》 面试必备 + 大厂必备 +涨薪必备 加尼恩免费领 免费赠送 经典图书:《Java高并发核心编程（卷2）加强版》 面试必备 + 大厂必备 +涨薪必备 加尼恩免费领 免费赠送 经典图书:《Java高并发核心编程（卷3）加强版》 面试必备 + 大厂必备 +涨薪必备 加尼恩免费领

免费赠送 资源宝库： Java 必备 百度网盘资源大合集 价值>10000元 加尼恩领取

尼恩社群中，很多小伙伴反馈， Sharding-JDBC 分页查询的速度超级慢， 怎么处理？

反馈这个问题的小伙伴，很多很多。

而且这个问题，也是面试的核心难题。前段时间，有小伙伴反馈，在面试中遇到了这个问题

分库分表后，分页查询太慢了，如何优化？

尼恩提示：分库分表的知识，既是面试的核心知识，又是开发的核心知识。

所以，这里尼恩给大家做一下系统化、体系化的梳理，使得大家可以充分展示一下大家雄厚的 “技术肌肉”，让面试官爱到 “不能自已、口水直流”。

也一并把这个题目以及参考答案，收入咱们的 《尼恩Java面试宝典PDF》V120版本，供后面的小伙伴参考，提升大家的 3高 架构、设计、开发水平。

需要《尼恩 架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》的PDF，请关注本公众号【技术自由圈】获取

分库分表后，查询太慢了，如何优化？大致三种方法：

第一种方式，需要引入 es，这里不做展开，有兴趣的可以来尼恩的《技术自由圈》社群交流。

这里就梳理一下，第二种禁止跳页、第三种二次查询法。

Sharding-JDBC从多个数据库获取分页数据，与单数据库的场景是不同的。

假设每10条数据为一页，取第2页数据。

在分片环境下获取LIMIT 10, 10，归并之后再根据排序条件取出前10条数据是不正确的。

举例说明，若SQL为：

下图展示了不进行SQL的改写的分页执行结果：

通过图中所示，想要取得两个表中共同的按照分数排序的第2条和第3条数据，理论上，应该是95和90。

实际上呢？

由于执行的SQL只能从每个表中获取第2条和第3条数据，即从t_score_0表中获取的是90和80；

从t_score_0表中获取的是85和75。

因此进行结果归并时，只能从获取的90，80，85和75之中进行归并，那么，无论怎么实现，结果归并之后，都不可能获得正确的结果。

正确的做法是将分页条件改写为LIMIT 0, 3，取出所有前两页数据，再结合排序条件计算出正确的数据。 下图展示了进行SQL改写之后的分页执行结果。

注意，这里有个大问题：

为了结果不出错，归并之前的查询，是0开始， 结果才可能是对的。

然而，查询偏移量过大的分页会导致数据库获取数据性能低下，

以MySQL为例：

如果不是分库分表，这句SQL会使得MySQL在无法利用索引的情况下跳过1000000条记录后，再获取10条记录，其性能可想而知。

然而， 在分库分表的情况下（假设分为2个库），为了保证数据的正确性，SQL会改写为：

即将偏移量前的记录全部取出，并仅获取排序后的最后10条记录。

这会在数据库本身就执行很慢的情况下，进一步加剧性能瓶颈。

因为原SQL仅需要传输10条记录至客户端，而改写之后的SQL则会传输1,000,010 * 2的记录至客户端。

（1）采用流式处理 + 归并排序的方式来避免内存的过量占用。

由于SQL改写不可避免的占用了额外的带宽，但并不会导致内存暴涨。

与直觉不同，大多数人认为Sharding-JDBC会将1,000,010 * 2记录全部加载至内存，进而占用大量内存而导致内存溢出。

但由于每个结果集的记录是有序的，因此Sharding-JDBC每次仅获取各个分片的当前结果集记录，驻留在内存中的记录仅为当前路由到的分片的结果集的当前游标指向而已。 对于本身即有序的待排序对象，归并排序的时间复杂度仅为O(n)，性能损耗很小。

（2）Sharding-JDBC对仅落至多分片的查询进行进一步优化。

落至单分片查询的请求并不需要改写SQL也可以保证记录的正确性，因此在此种情况下，Sharding-JDBC并未进行SQL改写，从而达到节省带宽的目的。

一般情况下，性能慢，都是第一种情况。流式查询看上去很好，但也有大大的弊端。

采用游标查询的方式的缺点很明显。

流式（游标）查询需要注意：当前查询会独占连接！必须先读取（或关闭）结果集中的所有行，然后才能对连接发出任何其他查询，否则将引发异常！

执行一个流式查询后，数据库访问框架就不负责关闭数据库连接了，需要应用在取完数据后需要自己关闭。

由于MySQL_Server不知道客户端什么时候将数据消费完，而自身的对应表可能会有DML写入操作，此时MySQL_Server需要建立一个临时空间来存放需要拿走的数据。

因此对于当你启用useCursorFetch读取大表的时候，会看到MySQL上的几个现象：

这就是，尼恩社群很多小伙伴反馈， 分表后 分页查询慢的原因。基本上是分页越大后续的查询越耗资源

如何解决呢？

第一种方式，需要引入 es，这里不做展开，有兴趣的可以来尼恩的《技术自由圈》社群交流。

由于LIMIT并不能通过索引查询数据，因此如果可以保证ID的连续性，通过ID进行分页是比较好的解决方案：

或通过记录上次查询结果的最后一条记录的ID进行下一页的查询：

如果不是id列， 假设排序的列为col，禁止跳页查询法的两个步骤大致如下：

（1）用正常的方法取得第一页数据，并得到第一页记录的 max_col 最大值；

（2）每次翻页，将

order by col offset X limit Y;

改写成

order by col where col>$time_max limit Y;

以保证每次只返回一页数据，性能为常量。

假设排序的列为col，二次查询法的两个步骤大致如下：

（1）SQL改写，将

order by col offset X limit Y;

改写成

order by col offset X/N limit Y;

（2）多页返回，找到最小值col_min；

（3）between二次查询

order by col between col_min and col_i_max;

（4）设置虚拟col_min，找到col_min在各个分库的offset，从而得到col_min在全局的offset；

（5）得到了col_min在全局的offset，自然得到了全局的offset X limit Y；

例子：分表结构

有这样的三个表，student_time_0, student_time_1, student_time_2, 以 user_id 作为分表键，根据表数量取模作为分表依据 这里先构造点数据，

主要是为了保证 create_time 唯一，比较好说明问题，