跨库分页

概述

跨库分页是一种在分布式数据库系统中常见的技术挑战。在使用 ShardingJDBC 或其他数据分片技术时，数据通常会被分散到多个数据库实例或表中。跨库分页需要在这些分片中进行数据的合并和排序，以便返回一个统一的分页结果集。

跨库分页的挑战

  1. 数据分布不均匀：由于数据分布在多个分片中，直接进行分页查询可能会导致部分分片返回过多或过少数据，影响整体分页结果的准确性。
  2. 排序和合并：各个分片返回的数据需要进行全局排序和合并，以确保分页结果的正确性。这涉及到跨库的数据传输和合并计算。
  3. 性能问题：跨库分页通常会涉及多个数据库实例的查询操作，可能会导致性能瓶颈，尤其是在数据量较大或网络延迟较高的情况下。

解决方案

全局视野法

这种方法主要是通过构建全局视野法模拟单库操作的方法解决跨库分页

操作步骤：

  1. 在每个分片上执行相同的分页查询，并将结果集返回到应用层。
  2. 应用层将各个分片的结果集进行全局排序和合并，然后再进行分页处理。

案例：

  1. 将order by time offset X limit Y，改写成order by time offset 0 limit X+Y
  2. 服务层将改写后的SQL语句发往各个分库：即例子中的各取3页数据
  3. 假设共分为N个库，服务层将得到N*(X+Y)条数据：即例子中的6页数据
  4. 服务层对得到的N*(X+Y)条数据进行内存排序，内存排序后再取偏移量X后的Y条记录，就是全局视野所需的一页数据

适用场景：适用于数据量较小或分页范围较小的场景（因为每个库返回了更多数据，需要二次排序，并且存在深分页问题）

针对于深分页问题可以进行相应优化：

  • 禁止跳页查询：每次传入上一页最大的那个排序字段，分页时where中添加判断条件（或者先查询出id范围再联表获取数据）
  • 前置条件：必须让他选择前置条件或者默认条件避免有很多的分页
  • 游标：但是游标性能很差，一行一行去获取的（很少使用）

二次查询法

二次查询法的核心思想是先进行一次简单的初步查询以获取目标页的分片键或主键列表，然后根据这些键进行第二次详细查询，从而避免直接在多个分片上进行复杂的全局排序和分页操作。通过分两次执行查询操作来减少跨库查询的复杂度和数据传输量，从而提高查询效率。该方法特别适用于数据量较大且需要频繁分页查询的场景。

查询案例：

  1. 将order by time offset X limit Y，改写成order by time offset X/N limit Y
  2. 找到三表返回数据中的排序字段的最小值time_min，以及每个库表中的最大值time_i_max
  3. between二次查询，order by time between $time_min and $time_i_max
  4. 找到time_min在各个分库的offset，从而得到time_min在全局的offset
  5. 得到了time_min在全局的offset，自然得到了全局的offset X limit Y

好处：可以精确的返回业务所需数据，每次返回的数据量都非常小，不会随着翻页增加数据的返回量。

缺点：需要二次查询，而且需要三个库都有相应的X/N个数据（空库则不适用）

预聚合与缓存

在分布式系统中，为了提高查询性能，尤其是跨库分页查询性能，预聚合和缓存是两种常用的优化技术。它们通过提前计算和存储部分或全部查询结果，减少了实时查询的计算负担和数据传输量，从而提高系统响应速度。

操作步骤：

  • 在每个分片上进行预聚合，计算出分页需要的数据范围，并缓存结果。
  • 应用层直接从缓存中读取结果，进行全局合并和排序，再进行分页处理。

适用场景：适用于高频分页查询的场景，能够减少查询延迟，但是可能会存在一定的一致性问题。