wehub-skill-sync
706 lines
23 KiB
Markdown
706 lines
23 KiB
Markdown
---
|
||
name: fullstack-dev-db-schema
|
||
description: "数据库表结构设计与迁移。适用于创建表、定义 ORM 模型、添加索引或设计关系。涵盖零停机迁移与多租户。"
|
||
license: MIT
|
||
metadata:
|
||
version: "1.0.0"
|
||
sources:
|
||
- PostgreSQL 官方文档
|
||
- Use The Index, Luke (use-the-index-luke.com)
|
||
- 设计数据密集型应用（Martin Kleppmann）
|
||
- 数据库可靠性工程（Laine Campbell & Charity Majors）
|
||
---
|
||
|
||
# 数据库表结构设计
|
||
|
||
ORM 无关的关系型数据库表结构设计指南。涵盖数据建模、规范化、索引、迁移、多租户以及常见应用模式。主要面向 PostgreSQL，但原则同样适用于 MySQL/MariaDB。
|
||
|
||
## 适用范围
|
||
|
||
**使用该技能的场景：**
|
||
- 为新项目或新功能设计表结构
|
||
- 在规范化和反规范化之间做决策
|
||
- 选择需要创建的索引
|
||
- 规划在线数据库的零停机迁移
|
||
- 实现多租户数据隔离
|
||
- 添加审计追踪、软删除或版本控制
|
||
- 诊断因表结构问题导致的慢查询
|
||
|
||
**不适用的场景：**
|
||
- 选择使用哪种数据库技术（→ `technology-selection`）
|
||
- PostgreSQL 特定的查询调优（请使用 PostgreSQL 性能文档）
|
||
- ORM 特定的配置（→ `django-best-practices` 或对应 ORM 的文档）
|
||
- 应用层缓存（→ `fullstack-dev-practices`）
|
||
|
||
## 所需上下文
|
||
|
||
| 必需 | 可选 |
|
||
|----------|----------|
|
||
| 数据库引擎（PostgreSQL / MySQL） | 预期数据量（行数、增长率） |
|
||
| 领域实体及其关系 | 读写比例 |
|
||
| 关键访问模式（查询） | 多租户需求 |
|
||
|
||
---
|
||
|
||
## 快速入门清单
|
||
|
||
设计新表结构：
|
||
|
||
- [ ] **识别领域实体**——每个实体映射一张表（而非每个类一张表）
|
||
- [ ] **主键**：公开 ID 使用 UUID，仅内部使用使用 serial/bigserial
|
||
- [ ] **外键**带有显式的 `ON DELETE` 行为
|
||
- [ ] **默认 NOT NULL**——仅在业务逻辑要求时才设为可空
|
||
- [ ] **时间戳**：每张表都包含 `created_at` + `updated_at`
|
||
- [ ] **索引**：为每个 WHERE、JOIN、ORDER BY 列创建索引
|
||
- [ ] **不提前反规范化**——从规范化开始，在有测量依据时才反规范化
|
||
- [ ] **命名约定**保持一致：`snake_case`，表名使用复数
|
||
|
||
---
|
||
|
||
## 快速导航
|
||
|
||
| 需要… | 跳转至 |
|
||
|----------|---------|
|
||
| 建模实体与关系 | [1. 数据建模](#1-数据建模关键) |
|
||
| 决定规范化还是反规范化 | [2. 规范化](#2-规范化-vs-反规范化关键) |
|
||
| 选择合适的索引 | [3. 索引](#3-索引策略关键) |
|
||
| 在在线数据库上安全运行迁移 | [4. 迁移](#4-零停机迁移高) |
|
||
| 设计多租户表结构 | [5. 多租户](#5-多租户设计高) |
|
||
| 添加软删除/审计追踪 | [6. 常见模式](#6-常见表结构模式中) |
|
||
| 对大表进行分区 | [7. 分区](#7-表分区中) |
|
||
| 查看反模式 | [反模式](#反模式) |
|
||
|
||
---
|
||
|
||
## 核心原则（7 条规则）
|
||
|
||
```
|
||
1. ✅ 从规范化（3NF）开始——只有在有测量证据时才反规范化
|
||
2. ✅ 每张表都有主键、created_at、updated_at
|
||
3. ✅ 对外公开的 ID 使用 UUID，内部连接键使用 serial
|
||
4. ✅ 默认 NOT NULL——null 是业务决策，而非偷懒的默认值
|
||
5. ✅ 为每个用在 WHERE、JOIN、ORDER BY 中的列创建索引
|
||
6. ✅ 外键在数据库中强制执行（而非仅在应用代码中）
|
||
7. ✅ 迁移是增量的——绝不在生产中一步完成删除/重命名，必须有多步骤计划
|
||
```
|
||
|
||
---
|
||
|
||
## 1. 数据建模（关键）
|
||
|
||
### 表命名
|
||
|
||
```sql
|
||
-- ✅ 复数、snake_case
|
||
CREATE TABLE orders (...);
|
||
CREATE TABLE order_items (...);
|
||
CREATE TABLE user_profiles (...);
|
||
|
||
-- ❌ 单数、大小写混用
|
||
CREATE TABLE Order (...);
|
||
CREATE TABLE OrderItem (...);
|
||
CREATE TABLE tbl_usr_prof (...); -- 晦涩的缩写
|
||
```
|
||
|
||
### 主键
|
||
|
||
| 策略 | 适用场景 | 优点 | 缺点 |
|
||
|----------|------|------|------|
|
||
| `bigserial`（自增） | 内部表、外键连接 | 紧凑、连接快 | 可枚举，不适合公开 ID |
|
||
| `uuid`（v4 随机） | 面向公开的资源 | 不可猜测、全局唯一 | 体积更大（16 字节）、B-Tree 随机 I/O |
|
||
| `uuid` v7（按时间排序） | 公开 + 需要排序 | 不可猜测 + 插入友好 | 较新，生态系统支持较少 |
|
||
| `text` slug | URL 友好的资源 | 人类可读 | 必须强制唯一性，更新成本高 |
|
||
|
||
**推荐的默认方案：**
|
||
|
||
```sql
|
||
CREATE TABLE orders (
|
||
id bigserial PRIMARY KEY, -- 内部外键目标
|
||
public_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE, -- API 对外
|
||
-- ...
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
updated_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
```
|
||
|
||
### 关系
|
||
|
||
```sql
|
||
-- 一对多：user → orders
|
||
CREATE TABLE orders (
|
||
id bigserial PRIMARY KEY,
|
||
user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
-- ...
|
||
);
|
||
CREATE INDEX idx_orders_user_id ON orders(user_id);
|
||
|
||
-- 多对多：orders ↔ products（通过连接表）
|
||
CREATE TABLE order_items (
|
||
id bigserial PRIMARY KEY,
|
||
order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
|
||
product_id bigint NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
|
||
quantity int NOT NULL CHECK (quantity > 0),
|
||
unit_price numeric(10,2) NOT NULL,
|
||
UNIQUE (order_id, product_id) -- 防止重复的行项目
|
||
);
|
||
|
||
-- 一对一：user → profile
|
||
CREATE TABLE user_profiles (
|
||
user_id bigint PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
|
||
bio text,
|
||
avatar_url text,
|
||
-- ...
|
||
);
|
||
```
|
||
|
||
### ON DELETE 行为
|
||
|
||
| 行为 | 适用场景 | 示例 |
|
||
|----------|------|---------|
|
||
| `CASCADE` | 子表数据脱离父表无意义 | 删除订单时同时删除 order_items |
|
||
| `RESTRICT` | 防止意外删除 | 被 order_items 引用的 products |
|
||
| `SET NULL` | 保留子表数据，清除引用 | 员工离职时 orders.assigned_to 置空 |
|
||
| `SET DEFAULT` | 回退为默认值 | 较少使用，用于状态列 |
|
||
|
||
---
|
||
|
||
## 2. 规范化 vs 反规范化（关键）
|
||
|
||
### 从规范化开始（3NF）
|
||
|
||
**实际中的范式：**
|
||
|
||
| 范式 | 规则 | 违反示例 |
|
||
|------|------|-------------------|
|
||
| 1NF | 无重复组、原子值 | 一列中存 `tags = "go,python,rust"` |
|
||
| 2NF | 无部分依赖（组合键） | `order_items.product_name` 仅依赖于 `product_id` |
|
||
| 3NF | 无传递依赖 | `orders.customer_city` 依赖于 `customer_id` 而非 `order_id` |
|
||
|
||
**1NF 违反修复：**
|
||
```sql
|
||
-- ❌ 标签作为逗号分隔的字符串
|
||
CREATE TABLE posts (id serial, tags text); -- tags = "go,python"
|
||
|
||
-- ✅ 单独的表（若简单也可用数组或 JSONB）
|
||
CREATE TABLE post_tags (
|
||
post_id bigint REFERENCES posts(id) ON DELETE CASCADE,
|
||
tag_id bigint REFERENCES tags(id) ON DELETE CASCADE,
|
||
PRIMARY KEY (post_id, tag_id)
|
||
);
|
||
|
||
-- ✅ 替代方案：PostgreSQL 数组（如果标签仅是字符串，无元数据）
|
||
CREATE TABLE posts (id serial, tags text[] NOT NULL DEFAULT '{}');
|
||
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);
|
||
```
|
||
|
||
### 何时反规范化
|
||
|
||
**仅在以下条件同时满足时才反规范化：**
|
||
1. 你已经**测量到**性能问题（EXPLAIN ANALYZE，而非「我觉得慢」）
|
||
2. 反规范化后的数据是**读密集**型（读写比例 > 100:1）
|
||
3. 你接受**一致性维护成本**（触发器、应用逻辑或物化视图）
|
||
|
||
**安全的反规范化模式：**
|
||
|
||
```sql
|
||
-- 模式 1：物化视图（计算后、可刷新）
|
||
CREATE MATERIALIZED VIEW order_summary AS
|
||
SELECT o.id, o.user_id, o.total,
|
||
COUNT(oi.id) AS item_count,
|
||
u.email AS user_email
|
||
FROM orders o
|
||
JOIN order_items oi ON oi.order_id = o.id
|
||
JOIN users u ON u.id = o.user_id
|
||
GROUP BY o.id, u.email;
|
||
|
||
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary; -- 非阻塞
|
||
|
||
-- 模式 2：缓存聚合列（由应用程序维护）
|
||
ALTER TABLE orders ADD COLUMN item_count int NOT NULL DEFAULT 0;
|
||
-- 通过触发器或在 order_item 插入/删除时的应用代码来更新
|
||
|
||
-- 模式 3：JSONB 快照（写入时冻结）
|
||
-- 在购买时存储产品信息的副本
|
||
CREATE TABLE order_items (
|
||
id bigserial PRIMARY KEY,
|
||
order_id bigint NOT NULL REFERENCES orders(id),
|
||
product_id bigint REFERENCES products(id),
|
||
quantity int NOT NULL,
|
||
unit_price numeric(10,2) NOT NULL, -- 冻结的价格
|
||
product_snapshot jsonb NOT NULL -- 冻结的名称、描述、图片
|
||
);
|
||
```
|
||
|
||
---
|
||
|
||
## 3. 索引策略（关键）
|
||
|
||
### 索引类型（PostgreSQL）
|
||
|
||
| 类型 | 适用场景 | 示例 |
|
||
|------|------|---------|
|
||
| **B-Tree**（默认） | 等值、范围、ORDER BY | `WHERE status = 'active'`、`WHERE created_at > '2025-01-01'` |
|
||
| **Hash** | 仅等值（很少使用，B-Tree 通常更好） | `WHERE id = 123`（大表，Postgres 10+） |
|
||
| **GIN** | 数组、JSONB、全文搜索 | `WHERE tags @> '{go}'`、`WHERE data->>'key' = 'val'` |
|
||
| **GiST** | 几何、范围、最近邻 | PostGIS、tsrange、ltree |
|
||
| **BRIN** | 带有自然顺序的超大表 | 按时间戳排序的时间序列数据 |
|
||
|
||
### 索引决策规则
|
||
|
||
```
|
||
规则 1：为 WHERE 子句中的每个列创建索引
|
||
规则 2：为 JOIN ON 条件中的每个列创建索引
|
||
规则 3：为 ORDER BY 中的每个列创建索引（如果与 LIMIT 一起查询）
|
||
规则 4：多列 WHERE 使用复合索引（最左前缀规则）
|
||
规则 5：仅过滤子集时使用部分索引（例如，仅活跃记录）
|
||
规则 6：使用覆盖索引（INCLUDE）以避免回表查询
|
||
规则 7：不要单独为低基数列建索引（例如布尔值）
|
||
```
|
||
|
||
### 复合索引：列顺序很重要
|
||
|
||
```sql
|
||
-- 查询：WHERE user_id = ? AND status = ? ORDER BY created_at DESC
|
||
-- ✅ 最优：从左到右匹配查询模式
|
||
CREATE INDEX idx_orders_user_status_created
|
||
ON orders(user_id, status, created_at DESC);
|
||
|
||
-- ❌ 顺序错误：无法高效用于该查询
|
||
CREATE INDEX idx_orders_created_user_status
|
||
ON orders(created_at DESC, user_id, status);
|
||
```
|
||
|
||
**最左前缀规则：** `(A, B, C)` 上的索引支持对 `(A)`、`(A, B)`、`(A, B, C)` 的查询，但不支持 `(B)`、`(C)` 或 `(B, C)` 的查询。
|
||
|
||
### 部分索引（仅索引所需数据）
|
||
|
||
```sql
|
||
-- 只有 5% 的订单是 'pending'，但频繁查询
|
||
CREATE INDEX idx_orders_pending
|
||
ON orders(created_at DESC)
|
||
WHERE status = 'pending';
|
||
|
||
-- 只有活跃用户对登录有意义
|
||
CREATE INDEX idx_users_active_email
|
||
ON users(email)
|
||
WHERE is_active = true;
|
||
```
|
||
|
||
### 覆盖索引（避免回表查询）
|
||
|
||
```sql
|
||
-- 查询只需要 id 和 status，无需读取表行
|
||
CREATE INDEX idx_orders_user_covering
|
||
ON orders(user_id) INCLUDE (status, total);
|
||
|
||
-- 现在该查询是仅索引查询：
|
||
SELECT status, total FROM orders WHERE user_id = 123;
|
||
```
|
||
|
||
### 何时不要建索引
|
||
|
||
```
|
||
❌ 很少用于 WHERE/JOIN/ORDER BY 的列
|
||
❌ 行数 < 1000 的表（顺序扫描更快）
|
||
❌ 单独来看基数值很低的列（例如布尔值 is_active）
|
||
❌ 写密集的表，索引维护成本超过读收益
|
||
❌ 重复索引（检查 pg_stat_user_indexes 中的未使用索引）
|
||
```
|
||
|
||
---
|
||
|
||
## 4. 零停机迁移（高）
|
||
|
||
### 黄金法则
|
||
|
||
```
|
||
绝不要在一个步骤中做破坏性变更。
|
||
始终：添加 → 迁移数据 → 移除旧数据（分多次部署）。
|
||
```
|
||
|
||
### 安全的迁移模式
|
||
|
||
**重命名列（3 次部署）：**
|
||
|
||
```
|
||
部署 1：添加新列
|
||
ALTER TABLE users ADD COLUMN full_name text;
|
||
UPDATE users SET full_name = name; -- 回填
|
||
-- 应用同时写入 name 和 full_name
|
||
|
||
部署 2：将读取切换到新列
|
||
-- 应用从 full_name 读取，仍然同时写入两个字段
|
||
|
||
部署 3：删除旧列
|
||
ALTER TABLE users DROP COLUMN name;
|
||
-- 应用仅使用 full_name
|
||
```
|
||
|
||
**添加 NOT NULL 列（2 次部署）：**
|
||
|
||
```sql
|
||
-- 部署 1：添加可空列，回填
|
||
ALTER TABLE orders ADD COLUMN currency text; -- 先设为可空
|
||
UPDATE orders SET currency = 'USD' WHERE currency IS NULL; -- 回填
|
||
|
||
-- 部署 2：添加约束（所有行回填完成后）
|
||
ALTER TABLE orders ALTER COLUMN currency SET NOT NULL;
|
||
ALTER TABLE orders ALTER COLUMN currency SET DEFAULT 'USD';
|
||
```
|
||
|
||
**无锁添加索引：**
|
||
|
||
```sql
|
||
-- ✅ CONCURRENTLY：不锁表，可在在线数据库上运行
|
||
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);
|
||
|
||
-- ❌ 不加 CONCURRENTLY：建索引期间锁住写入
|
||
CREATE INDEX idx_orders_status ON orders(status);
|
||
```
|
||
|
||
### 迁移安全清单
|
||
|
||
```
|
||
✅ 迁移在生产数据量级上运行时间 < 30 秒
|
||
✅ 无排他表锁（索引使用 CONCURRENTLY）
|
||
✅ 回滚方案已记录并测试
|
||
✅ 回填分批执行（而非一次巨型 UPDATE）
|
||
✅ 新列先以可空方式添加，稍后添加约束
|
||
✅ 旧列保留到所有代码引用移除为止
|
||
|
||
❌ 绝不在一次部署中重命名/删除列
|
||
❌ 绝不在大表上不做时间测试就执行 ALTER TYPE
|
||
❌ 绝不在事务中运行数据回填（大表上会 OOM）
|
||
```
|
||
|
||
### 分批回填模板
|
||
|
||
```sql
|
||
-- 以 10,000 行为一批进行回填（避免长时间运行的事务）
|
||
DO $$
|
||
DECLARE
|
||
batch_size int := 10000;
|
||
affected int;
|
||
BEGIN
|
||
LOOP
|
||
UPDATE orders
|
||
SET currency = 'USD'
|
||
WHERE id IN (
|
||
SELECT id FROM orders WHERE currency IS NULL LIMIT batch_size
|
||
);
|
||
GET DIAGNOSTICS affected = ROW_COUNT;
|
||
RAISE NOTICE 'Updated % rows', affected;
|
||
EXIT WHEN affected = 0;
|
||
PERFORM pg_sleep(0.1); -- 短暂暂停以降低负载
|
||
END LOOP;
|
||
END $$;
|
||
```
|
||
|
||
---
|
||
|
||
## 5. 多租户设计（高）
|
||
|
||
### 三种方案
|
||
|
||
| 方案 | 隔离性 | 复杂度 | 适用场景 |
|
||
|----------|-----------|------------|------|
|
||
| **行级**（共享表 + `tenant_id`） | 低 | 低 | SaaS MVP，租户数 < 1000 |
|
||
| **每租户独立 Schema** | 中 | 中 | 受监管行业，中等规模 |
|
||
| **每租户独立数据库** | 高 | 高 | 企业级，严格数据隔离 |
|
||
|
||
### 行级租户（最常见）
|
||
|
||
```sql
|
||
-- 每张表都有 tenant_id
|
||
CREATE TABLE orders (
|
||
id bigserial PRIMARY KEY,
|
||
tenant_id bigint NOT NULL REFERENCES tenants(id),
|
||
user_id bigint NOT NULL REFERENCES users(id),
|
||
total numeric(10,2) NOT NULL,
|
||
-- ...
|
||
);
|
||
|
||
-- 复合索引：租户在前（大多数查询按租户过滤）
|
||
CREATE INDEX idx_orders_tenant_user ON orders(tenant_id, user_id);
|
||
CREATE INDEX idx_orders_tenant_status ON orders(tenant_id, status);
|
||
|
||
-- 行级安全（PostgreSQL）
|
||
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
|
||
CREATE POLICY tenant_isolation ON orders
|
||
USING (tenant_id = current_setting('app.tenant_id')::bigint);
|
||
```
|
||
|
||
**应用层强制：**
|
||
|
||
```typescript
|
||
// 中间件：在每个请求上设置租户上下文
|
||
app.use((req, res, next) => {
|
||
const tenantId = req.headers['x-tenant-id'];
|
||
if (!tenantId) return res.status(400).json({ error: '缺少租户标识' });
|
||
req.tenantId = tenantId;
|
||
next();
|
||
});
|
||
|
||
// 仓库层：始终按租户过滤
|
||
async findOrders(tenantId: string, userId: string) {
|
||
return db.order.findMany({
|
||
where: { tenantId, userId }, // ← 每条查询中都有 tenant_id
|
||
});
|
||
}
|
||
```
|
||
|
||
### 规则
|
||
|
||
```
|
||
✅ 每张包含租户数据的表都有 tenant_id
|
||
✅ tenant_id 作为每个复合索引的第一列
|
||
✅ 应用中间件强制租户上下文
|
||
✅ 使用 RLS（PostgreSQL）作为纵深防御，而非唯一保护
|
||
✅ 用 2 个以上的租户进行测试以验证隔离
|
||
|
||
❌ 绝不允许在应用代码中进行跨租户查询
|
||
❌ 绝不在 WHERE 子句中遗漏 tenant_id（即便在管理工具中）
|
||
```
|
||
|
||
---
|
||
|
||
## 6. 常见表结构模式（中）
|
||
|
||
### 软删除
|
||
|
||
```sql
|
||
ALTER TABLE orders ADD COLUMN deleted_at timestamptz;
|
||
|
||
-- 所有查询过滤已删除记录
|
||
CREATE VIEW active_orders AS
|
||
SELECT * FROM orders WHERE deleted_at IS NULL;
|
||
|
||
-- 部分索引：仅索引未删除的行
|
||
CREATE INDEX idx_orders_active_status
|
||
ON orders(status, created_at DESC)
|
||
WHERE deleted_at IS NULL;
|
||
```
|
||
|
||
**ORM 集成：**
|
||
|
||
```typescript
|
||
// Prisma 中间件：自动过滤软删除记录
|
||
prisma.$use(async (params, next) => {
|
||
if (params.action === 'findMany' || params.action === 'findFirst') {
|
||
params.args.where = { ...params.args.where, deletedAt: null };
|
||
}
|
||
return next(params);
|
||
});
|
||
```
|
||
|
||
### 审计追踪
|
||
|
||
```sql
|
||
-- 方案 A：每张表上的审计列
|
||
ALTER TABLE orders ADD COLUMN created_by bigint REFERENCES users(id);
|
||
ALTER TABLE orders ADD COLUMN updated_by bigint REFERENCES users(id);
|
||
|
||
-- 方案 B：独立的审计日志表（更详细）
|
||
CREATE TABLE audit_log (
|
||
id bigserial PRIMARY KEY,
|
||
table_name text NOT NULL,
|
||
record_id bigint NOT NULL,
|
||
action text NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
|
||
old_data jsonb,
|
||
new_data jsonb,
|
||
changed_by bigint REFERENCES users(id),
|
||
changed_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
CREATE INDEX idx_audit_table_record ON audit_log(table_name, record_id);
|
||
CREATE INDEX idx_audit_changed_at ON audit_log(changed_at DESC);
|
||
```
|
||
|
||
### 枚举列
|
||
|
||
```sql
|
||
-- 方案 A：PostgreSQL 枚举类型（严格，但 ALTER TYPE 很麻烦）
|
||
CREATE TYPE order_status AS ENUM ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled');
|
||
ALTER TABLE orders ADD COLUMN status order_status NOT NULL DEFAULT 'pending';
|
||
|
||
-- 方案 B：文本 + CHECK 约束（更容易迁移）
|
||
ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'pending'
|
||
CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled'));
|
||
|
||
-- 方案 C：查找表（最灵活，适合 UI 驱动的列表）
|
||
CREATE TABLE order_statuses (
|
||
id serial PRIMARY KEY,
|
||
name text UNIQUE NOT NULL,
|
||
label text NOT NULL -- 显示名称
|
||
);
|
||
```
|
||
|
||
**推荐：** 大多数情况下使用方案 B（文本 + CHECK）。如果状态由非开发人员管理，则使用方案 C。
|
||
|
||
### 多态关联
|
||
|
||
```sql
|
||
-- ❌ 反模式：多态外键（无参照完整性）
|
||
CREATE TABLE comments (
|
||
id bigserial PRIMARY KEY,
|
||
commentable_type text, -- 'Post' 或 'Photo'
|
||
commentable_id bigint, -- 无法建立外键约束！
|
||
body text
|
||
);
|
||
|
||
-- ✅ 模式 A：单独的外键列（可空）
|
||
CREATE TABLE comments (
|
||
id bigserial PRIMARY KEY,
|
||
post_id bigint REFERENCES posts(id) ON DELETE CASCADE,
|
||
photo_id bigint REFERENCES photos(id) ON DELETE CASCADE,
|
||
body text NOT NULL,
|
||
CHECK (
|
||
(post_id IS NOT NULL AND photo_id IS NULL) OR
|
||
(post_id IS NULL AND photo_id IS NOT NULL)
|
||
)
|
||
);
|
||
|
||
-- ✅ 模式 B：独立的表（最清晰，适合不同表结构）
|
||
CREATE TABLE post_comments (..., post_id bigint REFERENCES posts(id));
|
||
CREATE TABLE photo_comments (..., photo_id bigint REFERENCES photos(id));
|
||
```
|
||
|
||
### JSONB 列（半结构化数据）
|
||
|
||
```sql
|
||
-- 好的用途：元数据、设置、灵活属性
|
||
CREATE TABLE products (
|
||
id bigserial PRIMARY KEY,
|
||
name text NOT NULL,
|
||
price numeric(10,2) NOT NULL,
|
||
attributes jsonb NOT NULL DEFAULT '{}' -- 颜色、尺寸、重量…
|
||
);
|
||
|
||
-- JSONB 查询的索引
|
||
CREATE INDEX idx_products_attrs ON products USING GIN(attributes);
|
||
|
||
-- 查询
|
||
SELECT * FROM products WHERE attributes->>'color' = 'red';
|
||
SELECT * FROM products WHERE attributes @> '{"size": "XL"}';
|
||
```
|
||
|
||
```
|
||
✅ 对真正灵活/可选的数据（元数据、设置、偏好）使用 JSONB
|
||
✅ 在查询 JSONB 列时使用 GIN 索引
|
||
|
||
❌ 绝不对本应作为列的数据（email、status、price）使用 JSONB
|
||
❌ 绝不用 JSONB 来逃避表结构设计（它不是 PostgreSQL 里的 MongoDB）
|
||
```
|
||
|
||
---
|
||
|
||
## 7. 表分区（中）
|
||
|
||
### 何时分区
|
||
|
||
```
|
||
✅ 表行数 > 1 亿且仍在增长
|
||
✅ 大多数查询按分区键过滤（日期范围、租户）
|
||
✅ 旧数据可按分区删除/归档（高效的 DELETE）
|
||
|
||
❌ 表行数 < 1000 万（开销不值得）
|
||
❌ 查询不按分区键过滤（扫描所有分区）
|
||
```
|
||
|
||
### 范围分区（时间序列）
|
||
|
||
```sql
|
||
CREATE TABLE events (
|
||
id bigserial,
|
||
tenant_id bigint NOT NULL,
|
||
event_type text NOT NULL,
|
||
payload jsonb,
|
||
created_at timestamptz NOT NULL DEFAULT now()
|
||
) PARTITION BY RANGE (created_at);
|
||
|
||
-- 按月分区
|
||
CREATE TABLE events_2025_01 PARTITION OF events
|
||
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
|
||
CREATE TABLE events_2025_02 PARTITION OF events
|
||
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
|
||
|
||
-- 使用 pg_partman 或 cron 自动创建分区
|
||
```
|
||
|
||
### 列表分区（多租户）
|
||
|
||
```sql
|
||
CREATE TABLE orders (
|
||
id bigserial,
|
||
tenant_id bigint NOT NULL,
|
||
total numeric(10,2)
|
||
) PARTITION BY LIST (tenant_id);
|
||
|
||
CREATE TABLE orders_tenant_1 PARTITION OF orders FOR VALUES IN (1);
|
||
CREATE TABLE orders_tenant_2 PARTITION OF orders FOR VALUES IN (2);
|
||
```
|
||
|
||
---
|
||
|
||
## 反模式
|
||
|
||
| # | ❌ 不要这样做 | ✅ 而应该这样做 |
|
||
|---|---------|--------------|
|
||
| 1 | 提前反规范化 | 从 3NF 开始，有测量依据时再反规范化 |
|
||
| 2 | 自增 ID 作为公开 API 标识符 | 公开用 UUID，内部用 serial |
|
||
| 3 | 无外键约束 | 始终在数据库中强制执行外键 |
|
||
| 4 | 默认可为空 | 默认 NOT NULL，需要时再设为可空 |
|
||
| 5 | 外键列上无索引 | 为每个外键列创建索引 |
|
||
| 6 | 一步完成破坏性迁移 | 分多次部署完成：添加 → 迁移 → 移除 |
|
||
| 7 | 不加 `CONCURRENTLY` 的 `CREATE INDEX` | 在线表上始终使用 `CONCURRENTLY` |
|
||
| 8 | 多态外键（`commentable_type + commentable_id`） | 单独的外键列或独立的表 |
|
||
| 9 | 什么都用 JSONB | 灵活数据用 JSONB，结构化数据用列 |
|
||
| 10 | 没有 `created_at` / `updated_at` | 每张表都配有时间戳对 |
|
||
| 11 | 一列中存逗号分隔的值 | 单独的表或 PostgreSQL 数组 |
|
||
| 12 | 不带长度验证的 `text` | CHECK 约束或应用层验证 |
|
||
|
||
---
|
||
|
||
## 常见问题
|
||
|
||
### 问题 1：「查询很慢，但我已经有索引了」
|
||
|
||
**症状：** 尽管已有索引，`EXPLAIN ANALYZE` 仍然显示顺序扫描。
|
||
|
||
**原因：**
|
||
1. **索引列顺序错误**——复合索引 `(A, B)` 对 `WHERE B = ?` 无效
|
||
2. **选择性低**——布尔列上的索引（50% 的行匹配），优化器更倾向于顺序扫描
|
||
3. **统计信息过时**——运行 `ANALYZE table_name;`
|
||
4. **类型不匹配**——用 `integer` 参数比较 `varchar` 列 → 无法使用索引
|
||
|
||
**修复方法：** 检查 `EXPLAIN (ANALYZE, BUFFERS)`，确认索引与查询模式匹配，运行 `ANALYZE`。
|
||
|
||
### 问题 2：「迁移锁住表数分钟」
|
||
|
||
**症状：** 执行期间 `ALTER TABLE` 阻塞所有写入。
|
||
|
||
**原因：** 添加 NOT NULL 约束、更改列类型、或者创建索引时未使用 `CONCURRENTLY`。
|
||
|
||
**修复方法：**
|
||
```sql
|
||
-- 无锁添加索引
|
||
CREATE INDEX CONCURRENTLY idx_name ON table(col);
|
||
|
||
-- 无锁添加 NOT NULL 约束（Postgres 12+）
|
||
ALTER TABLE t ADD CONSTRAINT t_col_nn CHECK (col IS NOT NULL) NOT VALID;
|
||
ALTER TABLE t VALIDATE CONSTRAINT t_col_nn; -- 非阻塞验证
|
||
```
|
||
|
||
### 问题 3：「多少个索引算太多？」
|
||
|
||
**经验法则：**
|
||
- 读密集表（报表、产品目录）：5-10 个索引没问题
|
||
- 写密集表（事件、日志）：最多 2-3 个索引
|
||
- 通过 `pg_stat_user_indexes` 监控——删除 `idx_scan = 0` 的索引
|
||
|
||
```sql
|
||
-- 查找未使用的索引
|
||
SELECT schemaname, relname, indexrelname, idx_scan
|
||
FROM pg_stat_user_indexes
|
||
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%pkey%'
|
||
ORDER BY pg_relation_size(indexrelid) DESC;
|