如何对一张有数十亿行的单表进行分区？

Question

如何对一张有数十亿行的单表进行分区？

IT Interview Practice · Accepted Answer

**按一个 partition key 把这张巨表拆成更小的物理子表，使每次查询只触及它所需的 partition（分区裁剪，partition pruning），而不是扫描数十亿行。**一张有 50 亿订单行的表仍然表现得像一张逻辑表，但当你的 `WHERE` 在 partition key 上过滤时，引擎会跳过其中的 99%。

## 一张分区表的形态

```text
                 orders (logical parent)
                        │  partition key = created_at
       ┌────────────────┼────────────────┬───────────────┐
       ▼                ▼                ▼               ▼
 orders_2024_q1   orders_2024_q2   orders_2024_q3   orders_2025_q1
  (child table)    (child table)    (child table)    (child, active)
       │                                                  │
   local index                                        local index
   (per child)                                        (per child)
```

## 选择分区策略

- **Range** —— 按日期/id 范围。最适合时间序列（订单、日志、事件）；近期数据保持热，旧范围易于删除。
- **Hash** —— 通过 `hash(key) % N` 在 N 个 partition 间均匀铺开。当没有天然范围而你想要均衡负载时使用（例如 `user_id`）。
- **List** —— 显式的桶（`region IN ('EU','US')`）。适合租户或地域隔离。

```sql
-- Postgres：按月做 range 分区，在 created_at 上裁剪
CREATE TABLE orders (id bigint, created_at date, amount numeric)
  PARTITION BY RANGE (created_at);

CREATE TABLE orders_2025_01 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

-- 这只扫描 orders_2025_01，而非整张表
SELECT sum(amount) FROM orders
 WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';
```

## 裁剪、index、维护

- **分区裁剪**只有在查询于 **partition key** 上过滤时才有效。在别的列上过滤，你就会扫描每一个 partition——头号错误。
- **本地 index（Local index）** = 每个子表一个 index（Postgres 中的默认）；随 partition 一起构建和删除都很廉价。**全局 index（Global index）**（Oracle）横跨所有 partition——有助于跨 partition 的唯一性查找，但当你删除一个 partition 时必须重建。
- **维护**变成 O(partition)：用 `DROP TABLE orders_2019` 归档旧数据（瞬时，无巨型 `DELETE`），并用 `ATTACH`/`DETACH` 实现滚动窗口。

## 面试视角

面试官考察你是否理解**分区不是索引（indexing）**——分区减少被扫描的数据，index 减少一次扫描中被检查的行；你往往两者都需要。好的回答会指出 range/hash/list；出色的回答会强调 **partition key 必须匹配你的主导查询过滤条件**，警告错误的键会毁掉裁剪，并提到通过 `DROP` 实现廉价归档。

**权衡：**跨 partition 查询（没有键过滤，或跨 partition 的唯一约束）会变慢，且唯一性的强制执行更难。不要对一张 1000 万行的表分区——你只是徒增运维复杂度而毫无收益。当表巨大**且**查询天然地以该键为范围时，才分区。

一张分区表的形态

选择分区策略

裁剪、index、维护

面试视角

相关问题