跳到主要内容

数据库设计相关笔记

· 阅读需 12 分钟

工业化生产环境下的数据库设计规范 + 慢 SQL 排查与索引优化流程(FastAPI + SQLAlchemy + Alembic),覆盖关系型数据库(MySQL / PostgreSQL / SQLite)+ Redis + MongoDB。 本笔记旧稿 仅含 2 个外链 + 4 行小结,已完整重构;旧稿目录已清理,当前以本文为准。 演示代码示例在 example/db-design/(慢查询埋点 + Alembic 加索引完整脚本 + Redis key 模板)。

目录​

  1. 生产环境数据库设计原则(核心原则)
  2. 关系型数据库表设计要点
  3. Redis 键设计规范
  4. MongoDB Schema 设计
  5. 慢 SQL 排查与索引优化
  6. 加索引:走代码层 Alembic Migration,不走裸 SQL

1. 生产环境数据库设计原则​

1.1 一句话原则​

ER 图保留表与表的关联(沟通用);DB 层不加外键约束(性能 / 扩展性);业务约束在代码层做。

1.2 DB 层保留 / 不保留的约束​

约束类型DB 层代码层原因
主键(PK)✅ 必须—物理寻址唯一标识
非空(NOT NULL)✅ 尽量—减少三值逻辑复杂度
唯一约束(UNIQUE)✅ 保留部分—DB 层是 race-condition 天然防线(防止并发重复)
外键(FK)❌ 不加✅ Service / ORM见 1.3
CHECK 约束❌ 不加✅ 代码层不同 DB 实现差异大
复杂业务约束(如余额 ≥ 0)❌ 不加✅ Service 层事务单条 SQL 内做约束容易死锁

1.3 不在 DB 层加外键的理由(业界共识)​

  1. DDL 锁表:外键级联检查会延长 INSERT/UPDATE 持锁时间,影响并发
  2. 分库分表后失效:跨库 / 分表场景下 FK 无法落地
  3. 微服务拆分后失效:跨服务调用根本无法做 DB 层 FK
  4. 性能损耗:每次写多一次约束校验(InnoDB 即时有 foreign_key_checks 开关)
  5. 循环依赖:ORM 双向关系 + DB FK 容易形成导入循环

1.4 ER 图怎么画​

  • 仍然在设计文档 / dbdiagram.io / draw.io 画出表与表关联
  • 用虚线箭头标注「逻辑外键」,明确写「DB 层不加约束」
  • 在 Service 层 / Repository 层做「插入前查存在性 + 事务兜底」
  • 必要时用兜底清理 job(孤儿数据检测 / 软删除关联)

2. 关系型数据库表设计要点​

2.1 主键策略​

方案适用注意
自增 BIGINT单库 OLTP 主流分库分表要换方案
雪花 ID / Sonyflake分布式、跨库13~53 bit 信息 + 趋势递增
UUID v7分布式 + 时序PG / MySQL 8 默认不支持,需应用生成
UUID v4几乎不推荐索引页分裂、存储膨胀

2.2 必备字段(按需)​

CREATE TABLE orders (
id BIGINT PRIMARY KEY,
-- 业务字段 ...
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL -- 软删除按场景
);
  • created_at / updated_at 必加,便于排查与排序
  • deleted_at 看业务是否需要软删除(物理删除有数据恢复 / 审计风险)

2.3 字段类型选择​

业务类型备注
金额DECIMAL(M, D) / NUMERIC永远不要 FLOAT / DOUBLE
状态字段TINYINT / ENUM(PG)比 VARCHAR 索引小
布尔TINYINT(1) / BOOLEANMySQL 中 BOOLEAN = TINYINT(1)
JSONMySQL 5.7+ JSON / PG JSONB(推荐)JSONB 支持索引(PG)
时间TIMESTAMP(到 2038) / DATETIME(MySQL) / TIMESTAMPTZ(PG 推荐)跨时区优先 TIMESTAMPTZ

2.4 NOT NULL 原则​

  • 能 NOT NULL 就 NOT NULL,少用 NULL(NULL 有三值逻辑:=NULL 永远 false,需 IS NULL)
  • 业务上「没有」就用空串 / 0 / 默认值,而不是 NULL

3. Redis 键设计规范​

3.1 命名约定(业界主流)​

  • 分隔符:冒号 :(兼容 RedisInsight、redis-cli 的展示)
  • 模板:业务:模块:业务ID:字段
  • 必备规则:
    • 必须设 TTL(防内存膨胀)
    • 长度可控(不要拿 JSON 整段当 key,存 hash / json 字符串作为 value)
    • 不用空格 / 换行 / 特殊字符
    • 统一小写

3.2 典型场景 key 模板​

场景模板示例
缓存cache:模块:keycache:user:profile:1000
Sessionsess:{sessionId}sess:abc123def456
限流ratelimit:{userId}:windowratelimit:{1000}:1m
分布式锁lock:resource:xxxlock:order:5678
计数器counter:场景:idcounter:views:homepage
Streamstream:events:topicstream:events:order_created
Pub/Subchannel:notifications:{userId}channel:notifications:{1000}

3.3 Cluster Hash Tag {...}(关键)​

Redis Cluster 用整个 key 做 CRC16 分 slot;多 key 操作(MGET / MSET / 事务 / Lua)必须在同一 slot,否则报错 CROSSSLOT。

# ❌ 不带 hash tag:key 落在不同 slot,MGET 报错
> MGET user:1000:profile user:1000:settings
(error) CROSSSLOT Keys in request don't hash to the same slot

# ✅ 用 {userId} 强制同一 slot
> SET user:{1000}:profile "Alice" OK
> SET user:{1000}:settings "dark_mode" OK
> MGET user:{1000}:profile user:{1000}:settings
1) "Alice"
2) "dark_mode"

规则:

  • 只识别第一个 {}:foo{{bar}}zap hash 的是 {bar}zap
  • 空 {} 忽略
  • 禁忌:避免 {global} 这种全局 tag(热点 shard);按业务 ID 散开

3.4 实际应用举例​

# 限流(原子 INCR + EXPIRE)
RATE_KEY = f"ratelimit:{{{user_id}}}:1m" # {1000}:1m
pipe = redis.pipeline()
pipe.incr(RATE_KEY)
pipe.expire(RATE_KEY, 60)
count, _ = pipe.execute()

# 分布式锁(带随机 token + Lua 释放)
LOCK_KEY = f"lock:order:{order_id}"
TOKEN = uuid4().hex
redis.set(LOCK_KEY, TOKEN, nx=True, ex=10)
# 释放用 Lua 脚本保证「只删自己持有的锁」

完整示例:example/db-design/redis_key_examples.py


4. MongoDB Schema 设计​

4.1 嵌入 vs 引用决策矩阵​

关系嵌入引用
1:1✅极少
1:少(few)✅视查询模式
1:多(many)❌✅
多:多❌✅(用 array of refs)

嵌入的条件:读写一起、数据有界、文档大小可控(< 16 MB)。

4.2 MongoDB 官方 6 大模式速查​

模式用途典型场景
Subset主文档只存最常用字段子集商品列表只存 name / price,详细页再查
Extended Reference引用对象 + 复制少量常用字段订单复制 user 的 name / phone 避免 join
Bucket时序分桶(按时间窗口)IoT 设备 1 分钟 1 个 bucket
Outlier异常大对象单独存99% 帖子 < 10 KB,1% 是 5 MB 长文
Computed预计算冗余字段评论数 +1 写入(避免每次 count)
Tree树形结构评论 / 分类树用 Materialized Path

4.3 反模式(绝对不要做)​

  • ❌ 无界数组:数组持续增长 → document 持续迁移 → 性能崩塌(最常见坑)
  • ❌ 巨量 collection:一个用户 / 租户一个 collection(应该加 tenantId 字段 + 索引分区)
  • ❌ 缺索引:每个查询字段(外键、过滤、排序)必须有索引
  • ❌ 把 MongoDB 当关系库用:坚持 join / 事务,丢失文档库优势

4.4 索引建议​

// 单字段
db.users.createIndex({ email: 1 }, { unique: true })

// 复合索引(注意 ESR 原则:Equality → Sort → Range)
db.orders.createIndex({ user_id: 1, status: 1, created_at: -1 })

// 部分索引(只索引部分文档)
db.orders.createIndex({ created_at: -1 }, { partialFilterExpression: { status: "active" } })

// TTL 索引(自动过期)
db.sessions.createIndex({ created_at: 1 }, { expireAfterSeconds: 3600 })

5. 慢 SQL 排查与索引优化​

5.1 数据库层:MySQL 慢日志​

# my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2 # 秒,默认 10 太松
log_queries_not_using_indexes = 1 # 没走索引也记
min_examined_row_limit = 100 # 扫描行太少不算
log_output = FILE # 或 TABLE(写 mysql.slow_log)

运行时开启:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 1;

分析工具:

# 内置(最常用入门)
mysqldumpslow -s c -t 20 /var/log/mysql/slow.log # 调用次数 Top 20
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log # 平均耗时 Top 10

# Percona Toolkit(更强,推荐生产用)
pt-query-digest /var/log/mysql/slow.log > digest.txt

5.2 数据库层:PostgreSQL pg_stat_statements​

优势:无需开启慢日志,聚合等价 SQL(字面量归一为 $1),直接 SELECT 排序。

启用:

# postgresql.conf + 重启
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = top
CREATE EXTENSION pg_stat_statements;

-- Top 5 慢查询
SELECT round((100 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2) AS percent,
calls,
round(mean_exec_time::numeric, 2) AS mean_ms,
query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

5.3 应用层:SQLAlchemy 慢查询埋点(FastAPI 推荐)​

数据库层慢日志是「事后」工具;应用层埋点能拿到业务上下文(哪个 endpoint、哪个 user、哪个 trace_id)。

完整示例:example/db-design/slow_query_logger.py

核心思路(SQLAlchemy Core / ORM 共用 Engine event):

from sqlalchemy import create_engine, event
import time, logging

engine = create_engine("postgresql+psycopg2://...")

@event.listens_for(engine, "before_cursor_execute")
def _before(conn, cursor, statement, parameters, context, executemany):
context._query_start_time = time.perf_counter()

@event.listens_for(engine, "after_cursor_execute")
def _after(conn, cursor, statement, parameters, context, executemany):
elapsed_ms = (time.perf_counter() - context._query_start_time) * 1000
if elapsed_ms >= 200: # 阈值,业界常用 200ms 报警 / 1s P0
logging.warning("slow query (%.1f ms): %s", elapsed_ms, statement)

ORM 层补充(SQLAlchemy 2.0):

from sqlalchemy.orm import Session

@event.listens_for(Session, "do_orm_execute")
def _slow_orm(state):
start = time.perf_counter()
state.invoke_statement()
elapsed = (time.perf_counter() - start) * 1000
if elapsed >= 200:
logging.warning("slow ORM (%.1f ms): %s", elapsed, state.statement)

注意:do_orm_execute 不包含 flush 阶段的 SQL;flush 用 before_cursor_execute 兜底。

5.4 阈值参考(业界常用)​

阈值等级
> 100 ms一般查询警告
> 200 ms慢查询埋点
> 500 ms性能问题
> 1 sP0 工单
> 3 s几乎一定有索引问题

阈值根据业务调整(OLTP 200ms,OLAP 数秒级合理)。

5.5 EXPLAIN 分析(MySQL / PostgreSQL 通用)​

拿到慢 SQL 后第一步是 EXPLAIN:

EXPLAIN SELECT * FROM orders WHERE user_id = 1000 AND created_at > '2026-01-01' ORDER BY id;

关键看:

字段含义危险信号
type访问类型ALL = 全表扫描 = 缺索引
key实际使用的索引NULL = 没走索引
rows预估扫描行数远大于实际返回 = 索引选错
Extra附加信息Using filesort / Using temporary / Using where 无 Using index

5.6 索引决策流程​

发现慢 SQL
↓
EXPLAIN 看执行计划
↓
判断:
├── type=ALL 且 rows 巨大 → 缺索引,加索引
├── type=range 但 Extra 有 filesort → 复合索引顺序错(排序字段加索引列末位)
├── type=index 但慢 → 索引列选择性差(基数太低),考虑删除或换列
├── 已用索引但仍慢 → 看是否回表过多(覆盖索引 / 索引下推 ICP)
└── 大数据量 → 考虑分库分表 / 读写分离

6. 加索引:走代码层 Alembic Migration,不走裸 SQL​

6.1 为什么必须走 Alembic(业界共识)​

方式DDL 进版本控制团队协作升级 / 回滚对称生产事故
Alembic migration✅✅✅可回滚
裸 SQL / Navicat 直接改库❌❌ 别人不知道❌上线后才发现,灾难

反例:开发 A 用裸 SQL 在生产加了索引,开发 B 跑 alembic upgrade 时会因索引已存在而失败(或反之,索引漏加导致线上慢 SQL)。

6.2 Alembic 索引操作速查​

# alembic/versions/xxxx_add_orders_user_created_idx.py
from alembic import op
from sqlalchemy import text

def upgrade():
# 单列索引
op.create_index(
"ix_orders_user_id", "orders", ["user_id"],
)
# 复合索引(顺序:等值字段 → 排序字段 → 范围字段)
op.create_index(
"ix_orders_user_created", "orders", ["user_id", "created_at"],
)
# 唯一索引
op.create_index(
"ix_users_phone", "users", ["phone"], unique=True,
)
# 函数索引
op.create_index(
"ix_users_email_lower", "users", [text("lower(email)")],
)
# 条件索引(PG)
# op.execute("CREATE INDEX ix_orders_active ON orders (created_at) WHERE status = 'active';")

def downgrade():
# 必须对称,否则回滚失败
op.drop_index("ix_orders_user_id", table_name="orders")
op.drop_index("ix_orders_user_created", table_name="orders")
op.drop_index("ix_users_phone", table_name="users")
op.drop_index("ix_users_email_lower", table_name="users")

SQLite 注意点:SQLite 对 ALTER TABLE 支持有限,DDL 用 batch_alter_table:

with op.batch_alter_table("users") as batch_op:
batch_op.create_index("ix_users_email", ["email"])

6.3 完整脚本示例(从慢 SQL 到加索引)​

完整可读脚本:example/db-design/alembic_add_index.py

展示真实场景:

场景:运营报障「我的订单列表加载慢」,抓到的慢 SQL:

SELECT * FROM orders WHERE user_id = 12345 ORDER BY created_at DESC LIMIT 20;

EXPLAIN 发现 type=ALL,扫 50 万行。决定加复合索引 (user_id, created_at)。

完整 alembic revision 文件见 demo。

6.4 大表在线加索引(生产必备)​

直接 CREATE INDEX 在大表上会长时间锁表(MySQL 5.6 之前 = 全程锁,PG = CONCURRENTLY 选项)。生产常用:

数据库工具 / 选项原理
MySQLpt-online-schema-change(Percona)建影子表 + 触发器 + 增量拷贝
MySQLgh-ost(GitHub)binlog + 影子表,无触发器
MySQLALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE(5.6+)部分索引支持在线
PostgreSQLCREATE INDEX CONCURRENTLY不锁表,但 alembic 内要特殊处理

Alembic + PG CONCURRENTLY 注意事项:

# Alembic 默认每个 migration 一个事务,CONCURRENTLY 不能在事务里
# alembic.ini:
# transaction_per_ddl = False
# 或者用 op.execute("COMMIT") 切事务
op.execute("COMMIT")
op.execute("CREATE INDEX CONCURRENTLY ix_orders_user_created ON orders (user_id, created_at)")

6.5 索引禁忌(不要做的事)​

反模式后果替代方案
给低基数列加索引(gender、is_deleted boolean)索引选择性差,几乎没用用复合索引带上
单表索引 > 5~6 个写放大(每次 INSERT/UPDATE 维护所有索引)看慢 SQL 按需加,定期清理未用索引
索引列做函数 / 类型转换索引失效用函数索引 / 改 SQL
不看 EXPLAIN 盲目加可能加错索引 / 加重复索引先 EXPLAIN,再决定
每次 release 都加索引索引膨胀加索引前先看是否有现成的能用

6.6 加索引 checklist​

[ ] 慢 SQL 已用 pt-query-digest / pg_stat_statements 确认是热点
[ ] EXPLAIN 已看,确认 type=ALL / rows 巨大 / 没走索引
[ ] 复合索引顺序已按 ESR 原则(等值 → 排序 → 范围)
[ ] 唯一索引确认 DB 现有数据无重复
[ ] 大表确认是否走在线 DDL(pt-osc / gh-ost / CONCURRENTLY)
[ ] migration 脚本已写完整的 downgrade
[ ] 已在测试环境跑过 upgrade + downgrade
[ ] 已在 staging 观察索引生效、慢 SQL 消失
[ ] 已通知 DBA / 团队 review migration

关联笔记​

标签:#mysql #postgres #redis