分库分表下跨库join解决方案

congcongsweet

1064人浏览 · 2025-12-22 11:22:32

congcongsweet · 2025-12-22 11:22:32 发布

阿里：选用orderid分表，那我用userid来查询的很多，那不是所有的分表都要查？怎么处理以阿里订单系统为例（参考《企业IT架构转型之道：阿里巴巴中台战略思想与架构实现》），它选择了三个column作为三个独立的sharding column，即：order_id，user_id，merchant_code。user_id和merchant_code就是买家ID和卖家ID，因为阿里的订单系统中买家和卖家的查询流量都比较大，并且查询对实时性要求都很高。而根据order_id进行分库分表，应该是根据order_id的查询也比较多。

阿里订单系统的做法确实如你所说，在《企业IT架构转型之道》等资料中提到，它采用了“多维度分片 + 冗余索引表”的策略，即：

主表（订单主表）按 order_id 分片（保证写入性能和唯一性）
同时建立两个冗余索引表：
- 一个按 user_id（买家ID）分片
- 一个按 merchant_code（卖家ID）分片

具体实现逻辑如下：

1. 主表（Order Main Table）

按 order_id 哈希分片（例如 1024 个分表）
存储完整的订单详情（如商品、价格、状态、时间等）
所有写操作（下单、更新）都写入此表

2. 买家索引表（User Index Table）

按 user_id 哈希分片
每条记录只包含 user_id → order_id 的映射（可能加上少量常用字段如订单状态、下单时间用于过滤）
下单时，异步或同步写入该索引表

3. 卖家索引表（Merchant Index Table）

按 merchant_code 哈希分片
同样只存 merchant_code → order_id 映射
写入时机同上

查询流程示例（以 user_id 查询为例）：

用户请求：“查询 user_id=123 的所有订单”
系统定位到 买家索引表 中 user_id=123 所在的分片（只需查 1 张表）
从该分片中取出该用户的所有 order_id 列表（可能带分页、时间范围过滤）
根据这些 order_id，批量去主表查询完整订单信息
- 注意：这些 order_id 可能分布在多个主表分片中
- 但可以通过 并行查询 + 批量拉取 优化性能（如用 connection pool 并发查多个分片）

虽然第4步可能涉及多个分片，但由于 order_id 是已知的，不需要全表扫描，且通常一次只查几十~几百条，性能可控。

为什么这样做是可行的？

优势	说明
✅ 避免全表扫描	user_id 查询只查一个索引分片，而非遍历所有 order_id 分片
✅ 写入可扩展	主表按 order_id 分片，天然支持高并发写入（无热点）
✅ 读写分离清晰	索引表轻量，主表完整，职责分离
✅ 支持多维查询	买家、卖家、订单号三种高频查询路径都高效

补充技术细节

数据一致性：索引表和主表之间可能存在短暂不一致（如果异步写入），可通过 最终一致性 + 重试机制 保证。
存储成本：冗余了两份索引，但索引表字段极少，存储开销远小于查询性能收益。
分页处理：在索引表中做分页（如按时间排序），再回查主表，避免深度分页问题。
缓存优化：高频用户的 user_id → [order_id] 映射可缓存在 Redis，进一步减少数据库压力。

举例说明：

🎯 核心目标

写入高效：每笔订单只生成一次，要能快速写入。
查询高效：
- 通过 order_id 查订单（比如客服查单）
- 通过 user_id 查用户所有订单（比如“我的订单”）
- 通过 merchant_code 查商家所有订单（比如“卖家后台”）

如果只按 order_id 分片，那用 user_id 查询就得扫所有分片 —— 不可接受！

所以阿里用了 “1张主表 + 2张索引表” 的方案。

📊 三张表结构示例（简化版）

1️⃣ 主订单表（`order_main`）—— 按 `order_id` 分片

order_id (PK)	user_id	merchant_code	product_name	amount	status	create_time
O1001	U123	M456	iPhone	5999	paid	2025-12-22

分片规则：order_id % 1024 → 决定存到哪个分表（如 order_main_001, order_main_002...）
用途：存储完整订单数据，所有写操作都走这里。

2️⃣ 买家索引表（`order_by_user`）—— 按 `user_id` 分片

user_id	order_id	create_time	status
U123	O1001	2025-12-22	paid
U123	O1002	2025-12-21	shipped

分片规则：user_id % 1024 → 存到 order_by_user_001 等
字段少：只存查询需要的字段（避免回表时拉太多无用数据）
本质：这是一个“倒排索引”：从 user_id 找 order_id

3️⃣ 卖家索引表（`order_by_merchant`）—— 按 `merchant_code` 分片

merchant_code	order_id	create_time	status
M456	O1001	2025-12-22	paid
M456	O1003	2025-12-20	delivered

分片规则：merchant_code % 1024
同样是轻量索引表

✍️ 写入流程（下单时）

当用户 U123 在商家 M456 店铺下单，生成订单 O1001：

写主表
→ 计算 O1001 % 1024 = 789
→ 写入 order_main_789 表
写买家索引表
→ 计算 U123 % 1024 = 123
→ 写入 order_by_user_123 表：(U123, O1001, 2025-12-22, paid)
写卖家索引表
→ 计算 M456 % 1024 = 456
→ 写入 order_by_merchant_456 表：(M456, O1001, 2025-12-22, paid)

✅ 这三步可以：

同步写（强一致，但性能略低）
异步写（最终一致，性能高，常用）

注意：不是一张表存三个分片键，而是三张独立的表，各自按自己的字段分片。

🔍 查询流程举例

场景1：用户查“我的订单”（user_id = U123）

计算 U123 % 1024 = 123
去 order_by_user_123 表查最近10条记录 → 得到 [O1001, O1002, ...]
拿这些 order_id 去主表查详情：
- O1001 → order_main_789
- O1002 → order_main_045
- ... → 并行查询多个分片，合并结果返回

✅ 只查了 1个索引分片 + N个主表分片（但N很小），而不是1024个分片！

场景2：客服查单（order_id = O1001）

O1001 % 1024 = 789
直接查 order_main_789 → 一条SQL搞定

场景3：商家查订单（merchant_code = M456）

M456 % 1024 = 456
查 order_by_merchant_456 → 得到 order_id 列表
回查主表拿详情

阿里的方式是：用多张表，每张表只用一个分片键，但内容有冗余。

这就像搜索引擎：

主文档库（按 doc_id 存全文）
倒排索引（按关键词 → doc_id）
用户索引（按 user_id → doc_id）

需求：按时间范围 + 多维度查询” 场景（比如运营要查“2025年11月所有订单”）

假设表结构

表名	分片键	字段示例	用途
`order_main`	`order_id`	order_id, user_id, merchant_id, amount, create_time, status	完整订单数据
`order_by_user`	`user_id`	user_id, order_id, create_time, status	用户查单
`order_by_merchant`	`merchant_id`	merchant_id, order_id, create_time, status	商家查单

❗问题：这些表都无法直接支持 “查整个月所有订单” —— 因为没有一张表是按时间分片的！

🔍 二、解决方案：4种可行路径（按推荐度排序）

✅ 方案1：增加“按时间分片”的归档表（推荐）

设计：

新建一张 order_monthly_archive 表，按月份分表，例如：
- order_202511（2025年11月）
- order_202512（2025年12月）
每张表包含完整订单字段（或核心字段）
数据来源：通过 Binlog 同步 or 定时任务 从 order_main 写入

查询示例：

1-- 查2025年11月所有订单
2SELECT * FROM order_202511
3WHERE create_time >= '2025-11-01'
4  AND create_time < '2025-12-01';

✅ 优点：

查询极快（单表扫描，可加索引）
支持任意条件（金额、状态、地区等）
适合报表、对账、BI分析

⚠️ 注意：

这张表是只读归档表，不用于在线交易
可用 Flink / Spark / 定时Job 构建

📌 这是阿里、美团、京东等公司处理“月度/年度报表”的标准做法。

✅ 方案2：在主表 `order_main` 上为 `create_time` 加索引（适用于中小规模）

如果订单总量不大（比如 < 1亿），且你必须实时查：

1-- 在每个 order_main 分片上执行
2CREATE INDEX idx_create_time ON order_main (create_time);

然后查询时：

1-- 需要遍历所有分片！
2SELECT * FROM order_main_{000} WHERE create_time BETWEEN '2025-11-01' AND '2025-11-30'
3UNION ALL
4SELECT * FROM order_main_{001} WHERE create_time BETWEEN '2025-11-01' AND '2025-11-30'
5...
6-- 共1024个分片

✅ 优点：数据实时
❌ 缺点：

性能极差（1024次查询 + 合并）
DB压力大，可能拖垮集群
不适合高频使用

⚠️ 仅建议在紧急排查问题时临时使用，不要用于日常报表。

✅ 方案3：用 Elasticsearch 做时间维度索引（推荐用于复杂查询）

将订单数据同步到 ES（通过 Binlog 或 MQ）
ES 按 create_time 建索引，支持快速范围查

1GET /orders/_search
2{
3  "query": {
4    "range": {
5      "create_time": {
6        "gte": "2025-11-01",
7        "lt": "2025-12-01"
8      }
9    }
10  }
11}

✅ 优点：

支持复杂条件（组合 user_id + 时间 + 金额区间）
查询快（秒级）
天然支持分页、聚合（如“11月总GMV”）

❌ 缺点：

数据有延迟（最终一致）
存储成本高
不适合强一致性场景

📌 美团、滴滴等公司广泛用 ES 做运营查询。

❌ 方案4：遍历所有 user_id / merchant_id 索引表（不推荐）

有人想：“既然有 user 索引表，能不能遍历所有用户？”
→ 理论可行，但实际不可行：

用户数可能上亿
每个用户查一次 → 亿级查询
数据重复（一个订单属于一个用户，但你要查所有用户）

完全不可行！

🛠结合业务场景

业务场景	推荐方案
运营日报/月报、财务对账	✅ 方案1：按月归档表（`order_YYYYMM`）
实时监控（如“今天订单数”）	✅ 方案3：Elasticsearch + 实时同步
客服临时查某天异常订单	✅ 方案2：主表加时间索引（限小范围）
BI 多维分析（用户+时间+地区）	✅ 方案3：ES 或导入数仓（Hive/Doris）