MySQL分库分表的聚合问题踩坑实录_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:
    database_inline:
      type: inline
      props:
        algorithm-expression: ds$->{user_id % 16}
    table_inline:
      type: inline
      props:
        algorithm-expression: t_order_$->{user_id % 64}

内部执行情况如下：假设我们要插入一条订单数据：

insert into t_order (order_id, user_id, amount) 
values (10001, 12345, 299.00);

第1步：shardingsphere 接收 sql

应用层发送 sql 到 shardingsphere，它作为中间件拦截了这个 sql。

第2步：分片路由计算

根据配置的分片算法计算：

user_id = 12345

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

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

第3步：sql 路由改写

shardingsphere 将原始 sql 改写为：

-- 原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);

第4步：查找数据源配置

shardingsphere 查找 ds9 对应的实际数据库连接：

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

第5步：执行 sql

shardingsphere 连接到 localhost:3306/db9 数据库，执行：

insert into t_order_57 (order_id, user_id, amount) 
values (10001, 12345, 299.00);

第6步：返回结果

数据库执行成功，返回结果给 shardingsphere，再返回给应用。

逻辑数据源	实际数据库	包含的表
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

二：切开后怎么聚合

所以分库分表不仅仅是“把数据切开”这么简单，难点永远在于 “切开后怎么聚合”

1：什么时候该分

面试里问“什么时候分库分表”，很多人上来就背：“阿里开发手册说单表超过 500 万行或者 2gb 就要分……”，显然过于教条了，现在的现在的硬件（ssd + 大内存），单表跑个 1000 万数据，索引建好了照样飞快。

所有真正逼你分库分表的，通常不是 “存储容量”，而是 “连接数” 和 “维护成本”：

连接数瓶颈：一个 mysql 实例的连接数是有限的（通常几千个）。当并发 qps 极高，所有请求都打到一个主库，数据库连接池瞬间被打爆，这时候必须 “分库” 来分摊并发写压力。
ddl 痛苦：给一张 5000 万行的表加个字段，你试试？锁表能锁到你怀疑人生，业务直接停摆。这时候必须 “分表” 来降低单表大小。

ddl：动结构（create、alter、drop）
	 操作后自动提交，不能回滚，影响数据库结构
dml：动数据（insert、update、delete）
	 操作表里的数据，可以回滚（在事务中）
dql：查数据（select）
	 最常用的操作，不修改数据
dcl：控权限（grant、revoke）
tcl：管事务（commit、rollback）

2：多维查询的三种解法

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

既然切分维度不能兼顾，那就 用空间换时间

c 端（用户视角） ：主库依然按 user_id % 16 分片。用户查订单，快如闪电。
b 端（商家视角） ：再搞一套“商家库”，数据按 merchant_id % 16 分片。
同步机制：用户下单写入“用户库”成功后，异步把数据同步一份到“商家库”。

这种方案需要配置两个独立的 shardingsphere 规则，一个用于用户库，一个用于商家库：

# 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);
            // 记录失败日志，后续补偿处理
        }
    }
}

如果消息发送失败，或者消费者挂了，商家库岂不是一直少订单？

为了保证最终一致性，我们通常采用 rocketmq 事务消息或本地事务表 + 定时轮询模式

事务消息： 利用 mq 的半消息机制，确保‘本地订单入库’和‘消息发送’要么同时成功，要么同时失败。
兜底重试： 配合定时任务扫描未确认的消息，确保 at least once（至少投递一次），保证商家库绝对不会丢单。”

2.2：大宽表 + elasticsearch

如果运营人员要按“下单时间”、“金额”、“地区”等奇葩条件筛选，mysql 分库分表就彻底歇菜了。

mysql 只负责核心交易链路（存、取、改状态），按 user_id 分片。
把数据通过 canal + mq 实时同步到 elasticsearch (es)。
商家查询、运营后台查询、复杂报表，全部走 es。

es 不是实时强一致的（通常有 1 秒延迟）。

如果商家刚收到新订单通知，点进去却在 es 查不到，怎么办？

在代码层做降级逻辑。当 es 查不到结果（或数据明显滞后）时，系统会自动降级回 mysql 的商家异构库进行点查。虽然后端压力大一点，但保证了用户体验的闭环。

三：分表后sql三大坑

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

绝对不能用自增主键。面试时推荐答美团 leaf 的号段模式。相比于雪花算法（snowflake），它不强依赖机器时钟，不会因为时钟回拨导致 id 重复，更适合严谨的金融级业务。

数据库自增id：就像每次做蛋糕都要去总部申请一个唯一的编号，做完一个申请一个。总部很忙，一旦总部出问题，整个蛋糕店就得停工。
leaf号段模式：相当于总部一次性批发给蛋糕店一批连续编号的蛋糕券（比如 #1001-#2000）。蛋糕店自己用券卖蛋糕，速度极快。当券只剩10%时，就派个小弟异步去总部领下一批券（比如 #2001-#3000）。这样，即使总部暂时联系不上，蛋糕店也能靠手里的券继续营业

双buffer（双缓存区）是leaf保证高可用和高性能的秘诀。每个业务在leaf服务的内存中都有两个缓存区（segment），当前一个buffer的id快用完时，会异步地去加载下一个buffer，确保了发号过程行云流水，不会卡顿

1️⃣ 从美团官方github仓库克隆leaf项目：https://github.com/meituan-dianping/leaf

2️⃣ 在你的mysql中创建一个数据库（例如 leaf_db），执行建表语句并初始化

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发号器');

3️⃣ 修改 leaf-server/src/main/resources/leaf.properties 文件，开启号段模式并配置数据库连接

# 开启号段模式
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=你的密码

4️⃣ 启动leaf服务。你可以将它作为一个独立的发号中心集群来部署，以提高可用性

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

分布式数据库的‘内存杀手’。中间件需要去每个分片取前 10010 条，聚合排序，性能极差

业务规避：禁止跳页，只允许‘下一页’。
seek 游标法：利用 where id < last_id limit 10 的方式查询，利用索引避开 offset 扫描。
es scroll：如果是极其复杂的深分页，直接走 es。”

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

采用 2 倍扩容（scale out） ，且必须配合在线数据迁移

全量同步：将旧库数据全量搬运到新库。
增量追平：利用 canal/dts 追平迁移期间产生的增量数据。
数据校验：全量比对一致后。
切流：短暂切断写入（秒级），更新路由规则，将流量切到新库。

总结

到此这篇关于mysql分库分表聚合问题踩坑实录的文章就介绍到这了,更多相关mysql分库分表聚合内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

MySQL分库分表的聚合问题踩坑实录

2026年04月27日 • Mysql •我要评论