SQLjava算法
MySQL 分库分表中的聚合查询与扩容方案
探讨 MySQL 分库分表后的聚合查询难题及解决方案。针对商家维度查询，介绍了异构索引表（双写冗余）和大宽表配合 ElasticSearch 两种策略。同时分析了分表后全局 ID 生成（如美团 Leaf）、深分页优化（Seek 游标法）以及在线扩容（2 倍扩容 + 数据迁移）的常见坑点与应对方案，旨在帮助开发者构建高可用的分布式数据库架构。
探讨 MySQL 分库分表后的聚合查询难题及解决方案。针对商家维度查询，介绍了异构索引表（双写冗余）和大宽表配合 ElasticSearch 两种策略。同时分析了分表后全局 ID 生成（如美团 Leaf）、深分页优化（Seek 游标法）以及在线扩容（2 倍扩容 + 数据迁移）的常见坑点与应对方案，旨在帮助开发者构建高可用的分布式数据库架构。
你们公司的订单表有 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 |
所以分库分表不仅仅是'把数据切开'这么简单，难点永远在于'切开后怎么聚合'。
面试里问'什么时候分库分表'，很多人上来就背：'阿里开发手册说单表超过 500 万行或者 2GB 就要分……'，显然过于教条了。现在的硬件（SSD + 大内存），单表跑个 1000 万数据，索引建好了照样飞快。
所有真正逼你分库分表的，通常不是'存储容量'，而是'连接数'和'维护成本'：
DDL：动结构（Create、Alter、Drop）操作后自动提交，不能回滚，影响数据库结构
DML：动数据（Insert、Update、Delete）操作表里的数据，可以回滚（在事务中）
DQL：查数据（Select）最常用的操作，不修改数据
DCL：控权限（Grant、Revoke）
TCL：管事务（Commit、Rollback）
既然切分维度不能兼顾，那就用空间换时间。
这种方案需要配置两个独立的 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 事务消息或本地事务表 + 定时轮询模式。
如果运营人员要按'下单时间'、'金额'、'地区'等奇葩条件筛选，MySQL 分库分表就彻底歇菜。
ES 不是实时强一致的（通常有 1 秒延迟）。
如果商家刚收到新订单通知，点进去却在 ES 查不到，怎么办？
在代码层做降级逻辑。当 ES 查不到结果（或数据明显滞后）时，系统会自动降级回 MySQL 的商家异构库进行点查。虽然后端压力大一点，但保证了用户体验的闭环。
绝对不能用自增主键。面试时推荐答美团 Leaf 的号段模式。相比于雪花算法（Snowflake），它不强依赖机器时钟，不会因为时钟回拨导致 ID 重复，更适合严谨的金融级业务。
双 buffer（双缓存区）是 Leaf 保证高可用和高性能的秘诀。每个业务在 Leaf 服务的内存中都有两个缓存区（Segment），当前一个 Buffer 的 ID 快用完时，会异步地去加载下一个 Buffer，确保了发号过程行云流水，不会卡顿。
从美团官方 GitHub 仓库克隆 Leaf 项目：https://github.com/Meituan-Dianping/Leaf
在你的 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 发号器');
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=你的密码
分布式数据库的'内存杀手'。中间件需要去每个分片取前 10010 条，聚合排序，性能极差。
WHERE id < last_id LIMIT 10 的方式查询，利用索引避开 Offset 扫描。采用2 倍扩容（Scale Out），且必须配合在线数据迁移。
微信公众号「极客日志」，在微信中扫描左侧二维码关注。展示文案：极客日志 zeeklog
使用加密算法（如AES、TripleDES、Rabbit或RC4）加密和解密文本明文。 在线工具，加密/解密文本在线工具，online
查找任何按下的键的javascript键代码、代码、位置和修饰符。 在线工具，Keycode 信息在线工具，online
JavaScript 字符串转义/反转义；Java 风格 \uXXXX（Native2Ascii）编码与解码。 在线工具，Escape 与 Native 编解码在线工具，online
使用 Prettier 在浏览器内格式化 JavaScript 或 HTML 片段。 在线工具，JavaScript / HTML 格式化在线工具，online
Terser 压缩、变量名混淆，或 javascript-obfuscator 高强度混淆（体积会增大）。 在线工具，JavaScript 压缩与混淆在线工具，online
在线格式化和美化您的 SQL 查询（它支持各种 SQL 方言）。 在线工具，SQL 美化和格式化在线工具，online