javaMySQL面试题-应用崩溃 | 分库分表优化

我们有一个线上通行记录的表，由于数据量过大，进行了分库分表，当时分库分表初期经常产生一些问题。典型的就是通行记录查询中使用了深分页，通过一些工具如MAT、Jstack追踪到是由于sharding-jdbc内部引用造成的。

通行记录数据被存放在两个库中。如果没有提供切分键，查询语句就会被分发到所有的数据库中，比如查询语句是 limit 10、offset 1000，最终结果只需要返回 10 条记录，但是数据库中间件要完成这种计算，则需要 (1000+10)*2=2020 条记录来完成这个计算过程。如果 offset 的值过大，使用的内存就会暴涨。虽然 sharding-jdbc 使用归并算法进行了一些优化，但在实际场景中，深分页仍然引起了内存和性能问题。

这种在中间节点进行归并聚合的操作，在分布式框架中非常常见。比如在 ElasticSearch 中，就存在相似的数据获取逻辑，不加限制的深分页，同样会造成 ES 的内存问题。

业界解决方案：

方法一：全局视野法

(1)将order by time offset X limit Y，改写成order by time offset 0 limit X+Y

(2)服务层对得到的N*(X+Y)条数据进行内存排序，内存排序后再取偏移量X后的Y条记录

这种方法随着翻页的进行，性能越来越低。

方法二：业务折衷法-禁止跳页查询

(1)用正常的方法取得第一页数据，并得到第一页记录的time_max

(2)每次翻页，将order by time offset X limit Y，改写成order by time where time>$time_max limit Y

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

方法三：业务折衷法-允许模糊数据

(1)将order by time offset X limit Y，改写成order by time offset X/N limit Y/N

方法四：二次查询法

(2)将order by time offset X limit Y，改写成order by time offset X/N limit Y

(3)找到最小值time_min

(4)between二次查询，order by time between timeminandtime_i_max

(5)设置虚拟time_min，找到time_min在各个分库的offset，从而得到time_min在全局的offset

(6)得到了time_min在全局的offset，自然得到了全局的offset X limit Y