[Mysql]涉及分表场景下的分页排序查询场景

Eric223
659 阅读6分钟

近期系统要接入一个新的服务商，涉及到一个列表页的查询接口，主要逻辑和大部分admin的分页查询逻辑是一致的。

大致逻辑如下:

  • 按照一定条件排序
  • 分页查询，用户翻到第几页，就查第几页数据

实现起来也非常简单

select * from T order by xxx offset X limit Y

但是针对分库分表的场景，这种查询就会有问题了

  • 一般业务场景下，我们会对id取膜分表，或者时间分表又或者一些其他规则分表
  • 分表查询如果还按照上面这个语句查询后再归并排序取前几，就会出错
  • 如下表数据，取x=1，y=3
    • table-1: [3,5,7]
    • table-2: [2,4,8]
    • 结果: [2，3，4，5，7，8] -> [2,3,4]
    • 预期结果:[1,1,2,3,4,5,7,8 ...] -> [1,2,3]

image.png

本文章主要就是想讨论一下分表场景下的分页排序查询的技术方法

一般而言，解决这种场景的方法有以下几种

  • 全局视野法
  • 禁止跳页法
  • 二次查询法

还有一些其他运行精度损失的方法，本文就不讲了

全局视野法

分表场景的查询不好做的原因是不同分表之间数据隔离，不好做排序，可能会漏掉本应该被查出来的数据。

那么解决方法就是 宁杀错，不放过，全查出来

image.png

如上图,原有的sql可以由 offset x limit y 改成 limit x+y

select * from T order by xxx offset 1 limit 5

select * from T order by xxx limit 6

然后再在内存中进行排序，选出offset 1，limit 5的数据 -> [1,2,3,4,5]

这种方式的优点很明确

  • 简单易理解，实现起来也方便
  • 相当于有了全局视野，把多个表的数据当成一个表进行操作，绕过了分表的缺陷，精度没有损失 同样的，缺点也很明显
  • 查出了大量无用数据，增加性能损耗
  • 还需要在内存中进行二次排序，消耗资源
  • 在深分页情况下(offset 10000000000 limit 2),性能会急剧下降

虽然说缺点明显，但这种方式的使用其实还是很普遍的，如一些数据库工具(ShardingSphere这些)，都有用到这个方法进行分页排序，但他们也对此进行了一些改进，避免大量无用数据加载到内存。

image.png 参考[shardingsphere.apache.org/document/5.…]

禁止跳页法

很多时候，解决复杂问题的办法就是加限制条件；禁止跳页法就是

  • 1、限制了用户的操作，必须按顺序翻页
  • 2、前端每次返回上次查询的最大id值
  • 3、查询带上这个最大id的限制条件，减少返回数据量
  • 4、内存里排序，返回期望数据

如 第1页的最大id为10，那么翻第二页时的查询语句就会修改为

select * from T order by xxx offset 10 limit 10

select * from T where id >10 order by xxx limit 10

对于这种方式，本质和全局视野法是一致的，但是因为加了这个最大id的限制，不再需要返回前面所有页的数据，进而减少了数据量，提高性能。

二次查询法(重点)

二次查询法，是既可以满足业务精度要求，又不需要额外增加用户限制，性能还高的方法。

二次查询的基本步骤为

  • SQL 改写，offset m limit x,n个分表，改写成 offset m/n limit x，每个分表查询
  • 捞出的数据做排序，找到最大值/最小值(根据业务选择)
  • 修改SQL进行第二次查询, 如第二步查出的最大值为max, 每个分表第二步查出的数据最小值为local_min,修改sql 为 xxx between(local_min,max),每个分表做一次查询
  • 聚合数据，找到max在全局数据的排序，进而定位出 目标数据的位置

下面举个例子介绍一下这个方法；网上的介绍都是一些升序排序的例子，我这里用个降序排序的例子来做介绍吧

首先，二次查询这个方法适合 数据分布相对均匀场景，比如 use_id 取膜分表的场景

我们的查询条件为 创建时间倒序排序，offset 7，limit 3的场景

步骤1: 首先需要将查询语句的偏移改写成 「m/n」(m 为原有offset 5，n为分表数 2，向下取整-多取点避免漏数据)

select * from T order by xxx offset 7 limit 3

select * from T order by xxx offset 3 limit 3

image.png

步骤2: 找出最大数据为2024-07-21

步骤3:改写sql

select * from T order by xxx between (2024-07-15,2024-07-21) select * from T order by xxx between (2024-07-14,2024-07-21)

查出的结果如下

image.png 步骤2查出的最大值，我们作为一个虚拟值插入到每个表里，找到他在每个表里的虚拟坐标

  • 第一个表他的offset 是3
  • 第二个表，由于 第一次查询可知，2024-07-18的 offset为3，虚拟值插入后，反推出 虚拟坐标为1(当然也可能为0，那就证明所有页的数据都被查出来了)
  • 那么在全局范围内，这个虚拟值的offset就是 3 + 1 = 4
  • 那么排序后，我们可以计算出预期结果(offset 7 limit 3) 为 [ 7-18,7-15,7-14]

image.png

总结一下上面的查询步骤：

  • 我们第一次查询，把offset 变小，是为了取出可能有用的数据，但同时过滤掉一些用不上的数据
    • 第一步聚合数据时去的最大值，全局offset 一定小于等于 目标offset (数学原理证明怎么证明就不写了)
    • 在极限情况下，所需数据就在第一步查询出的结果中，那么offset页刚好是 m/n * n，取出来的数据也是够的
  • 正常情况下offset过滤掉的数据里可能有有用数据，就会通过第二步再捞出来
  • 然后通过虚拟坐标计算的方式，找到这个标记值在全局视野的位置，再推算出目标数据的位置

现在回到前面说的，这种查询情况适用于数据相对均匀的场景，如果对于时间分表场景，其实这种方法的性能也不是很高，但也能用

性能不高的原因就是我们在第二次查询数据时，会把所有页的数据都查出来，性能会有所降低

image.png

avatar
文章
阅读
粉丝