SQLjava算法

MySQL 分库分表中的聚合查询与扩容方案

探讨 MySQL 分库分表后的聚合查询难题及解决方案。针对商家维度查询，介绍了异构索引表（双写冗余）和大宽表配合 ElasticSearch 两种策略。同时分析了分表后全局 ID 生成（如美团 Leaf）、深分页优化（Seek 游标法）以及在线扩容（2 倍扩容 + 数据迁移）的常见坑点与应对方案，旨在帮助开发者构建高可用的分布式数据库架构。

GopherDev发布于 2026/3/28更新于 2026/9/1082 浏览

MySQL 分库分表中的聚合问题

一：问题的引入

你们公司的订单表有 2 亿数据，怎么做的分库分表？

如果我们按用户 ID（user_id）取模，分了 16 个库，每个库 64 张表，一共 1024 张表。这样用户查自己的订单特别快，直接定位到具体的表。

此时问题就来了，那商家（Seller）要查自己店铺的订单列表怎么办？商家又没有 user_id，按你的分法，商家查一次岂不是要扫描全部 1024 张表？这系统能不崩？

# ds0 是数据源名称，db0 是实际的数据库名称。在配置中：
# ds0 是一个逻辑名称，代表一个数据库连接配置
# db0 是 MySQL 中真实的数据库名
databaseName: order_system
dataSources:
ds0:
  url: jdbc:mysql://localhost:3306/db0
  username: root
  password: ******
ds1:
  url: jdbc:mysql://localhost:3306/db1
  username: root
  password: ******
# ... 一直到 ds15，共 16 个数据源
rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds$->{0..15}.t_order_$->{0..63}
      databaseStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: database_inline
      tableStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: table_inline
  shardingAlgorithms:

INSERT INTO t_order (order_id, user_id, amount)
VALUES(10001, 12345, 299.00);

user_id = 12345
// 计算目标库：user_id % 16 = 12345 % 16 = 9
// 所以目标库是 ds9
// 计算目标表：user_id % 64 = 12345 % 64 = 57
// 所以目标表是 t_order_57

-- 原 SQL
INSERT INTO t_order (order_id, user_id, amount) VALUES(10001, 12345, 299.00);
-- 改写后的 SQL
INSERT INTO ds9.t_order_57 (order_id, user_id, amount) VALUES(10001, 12345, 299.00);

ds9:
  # 逻辑数据源
  url: jdbc:mysql://localhost:3306/db9 # 实际数据库是 db9
  username: root
  password: ******

INSERT INTO t_order_57 (order_id, user_id, amount) VALUES(10001, 12345, 299.00);

逻辑数据源	实际数据库	包含的表
ds0	db0	t_order_0 ~ t_order_63
ds1	db1	t_order_0 ~ t_order_63
ds2	db2	t_order_0 ~ t_order_63
…	…	…
ds9	db9	t_order_0 ~ t_order_63
…	…	…
ds15	db15	t_order_0 ~ t_order_63

DDL：动结构（Create、Alter、Drop）操作后自动提交，不能回滚，影响数据库结构
DML：动数据（Insert、Update、Delete）操作表里的数据，可以回滚（在事务中）
DQL：查数据（Select）最常用的操作，不修改数据
DCL：控权限（Grant、Revoke）
TCL：管事务（Commit、Rollback）

# shardingsphere-config.yaml
databaseName: order_system
dataSources:
  # 用户库数据源 (16 个)
  user_ds0:
    url: jdbc:mysql://localhost:3306/user_db0
    username: root
    password: ******
  user_ds1:
    url: jdbc:mysql://localhost:3306/user_db1
    username: root
    password: ******
  # ... user_ds2 ~ user_ds15
  # 商家库数据源 (16 个)
  merchant_ds0:
    url: jdbc:mysql://localhost:3306/merchant_db0
    username: root
    password: ******
  merchant_ds1:
    url: jdbc:mysql://localhost:3306/merchant_db1
    username: root
    password: ******
  # ... merchant_ds2 ~ merchant_ds15
rules:
- !SHARDING
  tables:
    # 用户视角的订单表配置
    t_order_user:
      actualDataNodes: user_ds$->{0..15}.t_order_user_$->{0..63}
      databaseStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: user_db_inline
      tableStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: user_table_inline
    # 商家视角的订单表配置
    t_order_merchant:
      actualDataNodes: merchant_ds$->{0..15}.t_order_merchant_$->{0..63}
      databaseStrategy:
        standard:
          shardingColumn: merchant_id
          shardingAlgorithmName: merchant_db_inline
      tableStrategy:
        standard:
          shardingColumn: merchant_id
          shardingAlgorithmName: merchant_table_inline
  shardingAlgorithms:
    # 用户库分片算法
    user_db_inline:
      type: INLINE
      props:
        algorithm-expression: user_ds$->{user_id % 16}
    user_table_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_user_$->{user_id % 64}
    # 商家库分片算法
    merchant_db_inline:
      type: INLINE
      props:
        algorithm-expression: merchant_ds$->{merchant_id % 16}
    merchant_table_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_merchant_$->{merchant_id % 64}

@Service
public class OrderService {
    @Autowired
    private RabbitTemplate rabbitTemplate;

    @Transactional
    public void createOrder(Order order) {
        // 1. 写入用户库（主库）
        orderMapper.insertToUserDb(order);
        // 2. 发送消息，异步同步到商家库
        rabbitTemplate.convertAndSend("order.exchange", "order.sync", order);
    }
}

@Component
@Slf4j
public class OrderSyncConsumer {
    @Autowired
    private MerchantOrderMapper merchantOrderMapper;

    @RabbitListener(queues = "order.sync.queue")
    public void syncToMerchantDb(Order order) {
        try {
            // 3. 异步写入商家库
            merchantOrderMapper.insertToMerchantDb(order);
        } catch (Exception e) {
            log.error("同步订单到商家库失败", e);
            // 记录失败日志，后续补偿处理
        }
    }
}

CREATE TABLE `leaf_alloc` (
  `biz_tag` varchar(128) NOT NULL DEFAULT '' COMMENT '业务标识，如 order',
  `max_id` bigint(20) NOT NULL DEFAULT '1' COMMENT '当前已分配的最大 ID',
  `step` int(11) NOT NULL COMMENT '号段步长，即每次批发的数量',
  `description` varchar(256) DEFAULT NULL COMMENT '描述',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`biz_tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 初始化订单服务的发号记录
INSERT INTO leaf_alloc(biz_tag, max_id, step, description)
VALUES('order', 1, 2000, '订单 ID 发号器');

# 开启号段模式
leaf.segment.enable=true
# 关闭雪花算法模式（两者不能同时开启）
leaf.snowflake.enable=false
# 数据库连接配置
leaf.jdbc.url=jdbc:mysql://你的数据库 IP:3306/leaf_db?useUnicode=true&characterEncoding=utf8
leaf.jdbc.username=你的用户名
leaf.jdbc.password=你的密码

MySQL 分库分表中的聚合查询与扩容方案

MySQL 分库分表中的聚合问题

一：问题的引入

MySQL 分库分表中的聚合查询与扩容方案

MySQL 分库分表中的聚合问题

一：问题的引入

更多推荐文章

相关免费在线工具

二：切开后怎么聚合

1：什么时候该分

2：多维查询的三种解法

2.1：异构索引表（双写冗余）

2.2：大宽表 + ElasticSearch

三：分表后 SQL 三大坑

1：分表后的全局 ID 怎么生成

2：分表后，怎么做深分页 (Limit 10000, 10)

3：扩容怎么办？原本 16 个库不够用了，要扩到 32 个

更多推荐文章

相关免费在线工具