分库分表之跨表分页查询解决方案

分库与分表的目的在于，减小数据库的单库单表负担，提高查询性能，缩短查询时间。

热数据：使用MySQL进行存储，分库分表；

冷数据：ES 或 TiDB或Hive存储；

分库分表的基本概念

分库

数据表分布到不同库中，减轻单一库的压力，提高数据性能和安全性。

分库带来的问题和挑战：

数据迁移与扩容问题：一般做法是通过程序先读出数据，然后按照指定的分表策略再将数据写入到各个分表中。
分页与排序问题：需要在不同的分表中将数据进行排序并返回，并将不同分表返回结果集进行汇总和再次排序，最后再返回给用户。
分布式全局唯一ID：UUID、GUID等

分表（水平+垂直）

分表可以减少数据库的单表负担，将压力分散到不同的表上，减少单表数据量，提高查询性能，缩短查询时间和缓解表锁的问题。

水平分表（同列拆数据）：Hash取模分表就属于随机水平分表，而时间维度分表则属于连续水平分表（车辆轨迹）。

垂直分表（不同列拆列）：将不常用的字段单独拆分到另外一张扩展表. 将大文本的字段单独拆分到另外一张扩展表, 将不经常修改的字段放在同一张表中，将经常改变的字段放在另一张表中，将列表的共用字段放一起（车辆、摄像头、果壳箱告警）。

对于海量用户场景，可以考虑hash取模分表，数据相对比较均匀，不容易出现热点和并发访问的瓶颈。

库内分表仅仅是解决了单表数据过大的问题，但并没有把单表的数据分散到不同的物理机上，因此并不能减轻 MySQL 服务器的压力，仍然存在同一个物理机上的资源竞争和瓶颈，包括 CPU、内存、磁盘 IO、网络带宽等。

一张表分很多表后，每一个小表都是完整的一张表，对应三个文件（MyISAM引擎：.MYD数据文件，.MYI索引文件，.frm表结构文件）。

分片（跨库-水平分表）

分片是将一个逻辑上的数据库或表切分成多个物理上的数据库或表，每个分片都包含一部分数据。分片可以跨多个数据库服务器，甚至跨多个数据中心。分片通常基于某种策略（如哈希、范围等）将数据分散到不同的分片中。

MySQL5以后支持分区，但是不支持二级分区，并且单机MySQL的性能远远不如Oracle，所以分区并不能解决性能问题。

分片就是分库+分表，属于水平切分，将表中数据按照某种规则放到多个库中，既分表又分库。

分片关注于跨多个数据库服务器的水平扩展和数据隔离

水平扩展：通过增加数据库服务器数量，实现水平扩展，提高系统的整体性能。

数据隔离：不同的分片可以部署在不同的地理位置，实现数据的物理隔离和容灾备份。

降低单点故障风险：由于数据被分散到多个分片中，单个分片的故障不会影响整个系统的运行。

常见的分片策略有：

范围分片：根据数据的某个字段（如时间戳、用户ID等）的值范围将数据分散到不同的分片中。这种方法适用于连续增长的数据，但可能导致数据倾斜。

哈希分片：使用哈希函数将数据分散到不同的分片中。这种方法可以确保数据的均匀分布，但可能导致跨分片查询的性能问题。

目录分片：使用一个额外的目录表来记录数据在不同分片中的分布情况。这种方法可以提供更灵活的分片策略，但增加了查询的复杂度。

在实现分片时，可以使用中间件（如MyCAT、Sharding-JDBC等）或自定义代码来管理数据的路由和分片。中间件通常提供了丰富的分片策略和配置选项，可以方便地实现分片功能。

分区（单库-水平分表）

MySQL分区表是将一个表物理上分割成多个较小的、更易于管理的片段，这些片段称为分区。

分区主要关注于单个数据库服务器的性能提升和管理效率

MySQL支持多种分区类型，如RANGE、LIST、HASH、KEY等。

性能提升：通过减少I/O操作、提高查询缓存效率等方式，提升查询性能。

管理方便：可以单独对分区进行备份、恢复、删除等操作，提高管理效率。

减少锁争用：在并发查询时，不同的分区可以并行处理，减少锁争用。

应用

日志表：如MySQL的慢查询日志表，可以按时间范围进行分区，便于管理和查询。

历史数据表：对于按时间存储的历史数据，可以使用RANGE分区，将不同时间段的数据存储在不同的分区中。

大数据表：对于数据量非常大的表，可以使用HASH或KEY分区，将数据分散到多个分区中，提高查询性能。

分库分表的挑战

引入分库分表之后，会给系统带来什么挑战呢？

分页查询：数据分布到多个表中，导致分页查询受限，需要扫描各个分表再聚合结果。还涉及到深度分页问题。
join 操作：同一个数据库中的表分布在了不同的数据库中，导致无法使用 join 操作。这样就导致我们需要手动进行数据的封装，比如你在一个数据库中查询到一个数据之后，再根据这个数据去另外一个数据库中找对应的数据。

建议尽量不使用 join 操作。因为效率低，并且会对分库分表造成影响。对于需要用到 join 操作的地方，可以采用多次查询业务层进行数据组装的方法。不过，这种方法需要考虑业务上多次查询的事务性的容忍度。
事务问题：同一个数据库中的表分布在了不同的库中，操作涉及到多个数据库，数据库自带的事务就无法满足我们的要求了。这个时候，我们就需要引入分布式事务了。
分布式 ID：分库之后，数据遍布在不同服务器上的数据库，数据库的自增主键已经没办法满足生成的主键唯一了。
跨库聚合查询：分库分表会导致常规聚合查询操作，如 group by，order by 等变得异常复杂。这是因为这些操作需要在多个分片上进行数据汇总和排序。为了实现这些操作，需要编写复杂的业务代码，或者使用中间件来协调分片间的通信和数据传输。这样会增加开发和维护的成本，以及影响查询的性能和可扩展性。

分库分表的建议

遇到下面几种场景可以考虑分库分表：

单表的数据达到千万级别以上，数据库读写速度比较缓慢。

数据库中的数据占用的空间越来越大，备份时间越来越长。

应用的并发量太大（应该优先考虑其他性能优化方法，而非分库分表）。

不过，分库分表的成本太高，如非必要尽量不要采用。而且，并不一定是单表千万级数据量就要分表。

分页方案选型建议

业务场景	推荐方案	说明
移动端下拉加载	游标分页 or 禁止跳页	性能最佳，用户体验好
后台管理系统（需跳页）	二次查询法 + 限制最大页码	平衡功能与性能
企业级复杂分页	ShardingSphere / MyCat	透明化，减少开发成本
海量数据实时排序	优先队列流式合并	适合推荐、排行榜场景
数据分析/报表	异步导出 or 预计算	避免在线分页，改用离线处理

避免深分页：前端限制最大页码（如 ≤ 100页），或引导用户用筛选条件缩小范围。

索引优化：确保排序字段、分片键有联合索引。

缓存分页结果：对前几页数据缓存到 Redis（尤其热门查询）。

异步预加载：用户浏览第1页时，后台预加载第2页。

数据归档：历史数据迁移到冷库，减少分片数据量。

分库分表的解决方案

常见分页查询方案对比

方案	适用场景	优点	缺点
1. 全局有序ID + 游标分页	主键/时间戳有序	性能好、无跳页	不支持跳页、需有序字段
2. 二次查询法（排序归并）	任意排序字段	支持任意排序、跳页	性能较差、内存压力大
3. 禁止跳页 + 页码缓存	App/小程序下拉场景	用户体验好、性能高	不支持传统页码跳转
4. 全局汇总中间件	复杂聚合分页	功能强大	架构复杂、成本高
5. 并行查询 + 优先队列	大数据量排序分页	实时性好	实现复杂、资源消耗大

方案1：游标分页（推荐）

适用于：主键自增、时间戳、全局唯一有序字段（如订单号、创建时间）

不使用 OFFSET，而是记录上一页最后一条记录的排序值（如 id > 1000）。

下一页从该值之后开始查，每页固定大小。

-- 第一页
SELECT * FROM order_shard_01 
WHERE create_time >= '2024-01-01' 
ORDER BY create_time DESC, id DESC 
LIMIT 20;

-- 假设最后一条记录是 create_time='2024-06-01 10:00:00', id=5000
-- 第二页
SELECT * FROM order_shard_01 
WHERE (create_time < '2024-06-01 10:00:00') 
   OR (create_time = '2024-06-01 10:00:00' AND id < 5000)
ORDER BY create_time DESC, id DESC 
LIMIT 20;

// 请求参数
class PageRequest {
    Long lastId;      // 上一页最后一条ID（游标）
    Integer pageSize = 20;
}

// 响应
class PageResult {
    List<Order> data;
    Long nextCursor;  // 下一页游标（最后一条ID）
}

// 服务层
public PageResult listOrders(PageRequest req) {
    List<Order> allResults = new ArrayList<>();
    
    // 并行查询所有分片
    for (String shardTable : getShardTables()) {
        List<Order> shardData = jdbcTemplate.query(
            "SELECT * FROM " + shardTable + 
            " WHERE id < ? ORDER BY id DESC LIMIT ?",
            req.lastId == null ? Long.MAX_VALUE : req.lastId,
            req.pageSize + 1,  // 多查1条用于判断是否有下一页
            orderRowMapper
        );
        allResults.addAll(shardData);
    }

    // 全局排序
    allResults.sort((a, b) -> Long.compare(b.getId(), a.getId()));

    // 截取分页
    List<Order> pageData = allResults.stream()
        .limit(req.pageSize)
        .collect(Collectors.toList());

    // 计算下一页游标
    Long nextCursor = pageData.size() >= req.pageSize ? 
                      pageData.get(pageData.size() - 1).getId() : null;

    return new PageResult(pageData, nextCursor);
}

优点：

性能极佳（走索引，无 OFFSET）

天然避免数据重复/遗漏

支持无限下拉（App/小程序常用）

缺点：

不支持“跳到第100页”

要求排序字段全局唯一或组合唯一（避免边界重复，如 create_time + id），确保唯一性。

要求客户端传入上一页最后一条的完整排序字段值。

方案2：二次查询法（排序归并分页）

适用于：必须支持“跳页”、排序字段无序或非全局唯一

第一轮：每个分片查“可能相关”的数据（带冗余）

比如要第3页（每页10条），则每个分片查前 30+10=40 条（offset=0, limit=40）

第二轮：内存中合并排序，取全局第21~30条

// Step 1: 并行查询每个分片的前 N 条（N = offset + limit）
List<Future<List<Record>>> futures = shards.stream()
    .map(shard -> executor.submit(() -> 
        query("SELECT * FROM " + shard + " ORDER BY score DESC LIMIT " + (offset + limit))
    )).collect(toList());

// Step 2: 合并所有结果，全局排序
List<Record> allRecords = mergeAndSort(futures.get());

// Step 3: 截取最终分页
return allRecords.subList(offset, offset + limit);

优点：

支持任意跳页

适用任意排序字段

缺点：

性能随页码增大急剧下降（深分页问题），可设置最大可跳页数（如最多100页），避免恶意深分页。

内存压力大（需缓存 offset+limit 条记录），使用缓存中间页结果（如 Redis 缓存第1~10页数据）。

数据量大时可能 OOM

方案3：禁止跳页 + 页码缓存（App常用）

适用于：移动端、信息流、下拉加载场景

前端不提供“跳页”按钮，只提供“加载更多”。

后端使用游标分页（方案1）。

可选：缓存前N页数据到 Redis，加速重复访问。

优点：

性能最优

用户体验流畅

实现简单

缺点：不满足传统“跳页”需求（如后台管理系统）

方案4：全局汇总中间件（企业级方案）

适用于：复杂业务、需透明分页、不想自己实现逻辑。如ShardingSphere、MyCat

中间件自动路由查询到各分片。

自动合并、排序、分页（通常采用“二次查询法”）。

对应用透明，SQL 仍写 LIMIT 100, 10。

优点：

开发无感知，SQL 兼容性好

支持复杂聚合、排序、分页

缺点：

中间件有性能开销

深分页仍慢

需维护中间件集群

ShardingSphere 示例配置：

rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..3}
      tableStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: table_inline
  shardingAlgorithms:
    table_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_${user_id % 4}

1
2
3

-- 应用层 SQL：
SELECT * FROM t_order ORDER BY create_time DESC LIMIT 100, 10;
-- ShardingSphere 自动分发+归并

方案5：并行查询 + 优先队列（高性能方案）

通过大数据量实时分页，适用于：海量数据、实时排序分页（如推荐系统、排行榜）

每个分片返回一个“排序迭代器”。

使用堆（优先队列）动态合并各分片TopK数据。

类似“外排序”思想，内存占用可控。

优点：

内存占用稳定（只缓存 heap.size = limit）

支持深分页（性能不随 offset 增大而剧降）

实时性好

缺点：

实现复杂（需自研或使用高级中间件）

对数据库连接数压力大

开源参考：

Apache ShardingSphere 的 StreamMerge 模式

Elasticsearch 的 search_after

分库分表查询案例（订单）

案例：订单表根据 userId 分表（userId % 1024）后

如何根据 orderId 快速查询订单信息？

方案一：扫描所有分表（效率最差）

方案二：建立中间路由表（数据冗余）

维护分片和 orderId 的映射，首先查询该表来确认分表，再查询数据（需要两次查询，且数据冗余，可能出现不一致的情况）

// 伪代码
Long orderId = 10086L;
Route route = routeMapper.selectByOrderId(orderId); // 查路由表
String targetTable = "order_" + route.getTableIndex();
Order order = orderMapper.selectByIdFromTable(orderId, targetTable); // 精准查询

多一次查询（可缓存优化）

路由表需与订单表事务一致（写入订单时同时写路由）

方案三（推荐）：重新维护 orderId，跟 userId 关联

最后4位数据拼接 userId 的二进制前四位，然后可以对 orderId % 1024 来确定分表（需要保证分表数量为 2的次方个，且重写雪花算法，向其借四位）

// 1bit | 41bit时间戳 | 10bit机器/分片ID | 12bit序列号
public ShardInfo parseFromSnowflake(long orderId) {
    long shardBits = (orderId >> 12) & 0x3FF; // 取中间10位
    int dbIndex = (int) (shardBits / 16);     // 假设每库16表
    int tableIndex = (int) (shardBits % 16);
    return new ShardInfo(dbIndex, tableIndex);
}

无需路由表，直接计算

ID全局唯一、趋势递增、包含分片信息

方案四（推荐）：让 orderId 本身包含分片信息

DD20250405_03_000000001
  │    │    │    └── 自增序列（保证唯一）
  │    │    └────── 分库分表标识（如 03 表示 db0.table3）
  │    └─────────── 日期（可选，用于归档）
  └────────────── 业务前缀（如 DD=订单）

或更简洁：
2025040503000000001

public class OrderIdParser {
    // orderId = "DD20250405_03_000000001"
    public static ShardInfo parse(String orderId) {
        String[] parts = orderId.split("_");
        String shardPart = parts[1]; // "03"
        int dbIndex = Integer.parseInt(shardPart) / 4;  // 假设每库4表
        int tableIndex = Integer.parseInt(shardPart) % 4;
        return new ShardInfo(dbIndex, tableIndex);
    }
}

方案五：使用分库分表中间件（推荐）

使用 ShardingSphere，配置分片规则后，应用直接写 SQL，中间件自动路由：

# ShardingSphere 根据 order_id 值自动计算分片，只查询目标表。
rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..3}
      databaseStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: db_inline
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: table_inline

  shardingAlgorithms:
    db_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${order_id.substring(10,11).toInteger() / 2} # 示例：从第10位取1位算库
    table_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_${order_id.substring(10,11).toInteger() % 4}

如何分页查询所有订单？

根据条件扫描所有分表中前面的所有数据，再代码过滤聚合（需要查大量无效数据，且性能低，涉及深度分页）

全局表查询：维护一个涉及查询参数的全局表，数据列少（包含分表信息），查询后再二次检索原数据。
最值限制过滤（可行）：根据上页最大值做过滤，如：ID或日期，扫描每个分表范围内数据，当客户端拉取不到任何数据的时候，即可停止分页。缺点是不能跳页查询，只能倒序排序。
大数据集成法（推荐）：ES或其它大数据组件。缺点是引入新的组件，会涉及数据同步的挑战。
三方分库分表组件（可行）：自动完成分表分页查询，只需要做好配置（如：ShardingSphere，其原理也是拆分查询）
分布式数据库（推荐）：既有关系数据库的优点又可无限扩表，支持数据库间无障碍迁移，如国产TiDB。
按日期的二次查询法（常用）：通过两次查询来确保分页查询的精度和性能，特别适用于数据量大且需要精确分页的场景。
二次查询法的基本思想是：将全局分页查询拆分为多个子查询，并通过两次查询来获取所需的数据。
- 第一次查询：将全局分页查询改写为多个子查询，每个子查询在各个分库中执行。例如，假设有三个分库，查询第200页的数据，每页5条记录，原始SQL为：SELECT * FROM T ORDER BY time OFFSET 1000 LIMIT 5;
- 找到最小时间戳：比较所有分库返回结果集中的最小时间戳，作为二次查询的起点。例如，假设三个分库返回的最小时间戳分别为1487501123、1487501133和1487501143，则最小时间戳为1487501123。
- 第二次查询：根据最小时间戳和各分库返回结果集的最大时间戳，构造新的查询条件。例如，第一个分库返回的最大时间戳为1487501523，则新的查询条件为：SELECT * FROM T WHERE time BETWEEN 1487501123 AND 1487501523;
- 合并结果集：将所有分库的查询结果合并，并根据全局偏移量计算最终的分页结果。
优点：
- 精确分页：能够精确返回所需的分页数据。
- 高性能：每次查询的数据量较小，不会随着翻页增加而显著增加。
缺点：
- 两次查询：需要进行两次数据库查询，增加了查询的复杂度。
- 适用场景有限：适用于数据量大且需要精确分页的场景，不适用于所有情况