跨库分页

Wu Jun 2020-01-09 05:43:49
Categories: > Tags:

原文:https://mp.weixin.qq.com/s/cMAXYBxmevFV_fwysZqP8w

需求缘起

分页需求

分页拉取需求有这样一些特点:

  1. 有一个业务主键id
  2. 分页排序是按照非业务主键id来排序的,业务中经常按照时间time来排序order by

在数据量不大时,可以通过在排序字段time上建立索引,利用SQL提供的offset/limit功能就能满足分页查询需求:

select * from T order by time offset X limit Y
分库需求

随着数据量的增大,数据库需要进行水平切分。
大部分的业务场景,会使用业务主键id作为“分库依据”,主键id取模分库。

问题的提出

分库后,数据库层失去了排序的全局视野,如何满足“跨越多个水平切分数据库,且分库依据与排序依据为不同属性,并需要进行分页”的查询需求?

方法一:全局视野法

  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

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

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

order by time offset X limit Y,改写成order by time offset X/N limit Y/N

技术方案的复杂度便大大降低了,既不需要返回更多的数据,也不需要进行服务内存排序了。

方法四:终极武器-二次查询法

即能够满足业务的精确需要,无需业务折衷,又高性能的方法

  1. order by time offset X limit Y,改写成order by time offset X/N limit Y
  2. 找到最小值time_min
  3. between二次查询,order by time between $time_min and $time_i_max
  4. 设置虚拟time_min,找到time_min在各个分库的offset,从而得到time_min在全局的offset
  5. 得到了time_min在全局的offset,自然得到了全局的offset X limit

优点是:可以精确的返回业务所需数据,每次返回的数据量都非常小,不会随着翻页增加数据的返回量。
不足是:需要进行两次数据库查询。