我们有一个线上通行记录的表,由于数据量过大,进行了分库分表,当时分库分表初期经常产生一些问题。典型的就是通行记录查询中使用了深分页,通过一些工具如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
![](/images/780-200-2.jpg)