数据库设计相关笔记
工业化生产环境下的数据库设计规范 + 慢 SQL 排查与索引优化流程(FastAPI + SQLAlchemy + Alembic),覆盖关系型数据库(MySQL / PostgreSQL / SQLite)+ Redis + MongoDB。 本笔记旧稿 仅含 2 个外链 + 4 行小结,已完整重构;旧稿目录已清理,当前以本文为准。 演示代码示例在
example/db-design/(慢查询埋点 + Alembic 加索引完整脚本 + Redis key 模板)。
目录
- 生产环境数据库设计原则(核心原则)
- 关系型数据库表设计要点
- Redis 键设计规范
- MongoDB Schema 设计
- 慢 SQL 排查与索引优化
- 加索引:走代码层 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 层加外键的理由(业界共识)
- DDL 锁表:外键级联检查会延长 INSERT/UPDATE 持锁时间,影响并发
- 分库分表后失效:跨库 / 分表场景下 FK 无法落地
- 微服务拆分后失效:跨服务调用根本无法做 DB 层 FK
- 性能损耗:每次写多一次约束校验(InnoDB 即时有
foreign_key_checks开关) - 循环依赖: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) / BOOLEAN | MySQL 中 BOOLEAN = TINYINT(1) |
| JSON | MySQL 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:模块:key | cache:user:profile:1000 |
| Session | sess:{sessionId} | sess:abc123def456 |
| 限流 | ratelimit:{userId}:window | ratelimit:{1000}:1m |
| 分布式锁 | lock:resource:xxx | lock:order:5678 |
| 计数器 | counter:场景:id | counter:views:homepage |
| Stream | stream:events:topic | stream:events:order_created |
| Pub/Sub | channel: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}}zaphash 的是{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 s | P0 工单 |
| > 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 选项)。生产常用:
| 数据库 | 工具 / 选项 | 原理 |
|---|---|---|
| MySQL | pt-online-schema-change(Percona) | 建影子表 + 触发器 + 增量拷贝 |
| MySQL | gh-ost(GitHub) | binlog + 影子表,无触发器 |
| MySQL | ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE(5.6+) | 部分索引支持在线 |
| PostgreSQL | CREATE 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笔记 · PostgreSQL笔记 · SQLite笔记 · 关系型数据库选型对比笔记
- NoSQL:MongoDB笔记 · Redis笔记
- Web 框架:FastAPI笔记
- Demo:
example/db-design/slow_query_logger.py(SQLAlchemy 慢查询埋点)example/db-design/alembic_add_index.py(加索引完整 alembic revision)example/db-design/redis_key_examples.py(7 种典型 key 模板 + Hash Tag)
标签:#mysql #postgres #redis