数据库设计 — 基础原理、常见踩坑与设计原则

第一原则：范式 vs 反范式

-- 第三范式 (3NF)：消除传递依赖
-- ❌ 违反 3NF
CREATE TABLE orders (
    id INT, user_name VARCHAR(50),  -- user_name 依赖 user_id，不依赖 id
    user_city VARCHAR(50)           -- user_city 依赖 user_id，不依赖 id
);
-- ✅ 拆分
CREATE TABLE orders (id INT, user_id INT, ...);
CREATE TABLE users  (id INT, name VARCHAR(50), city VARCHAR(50));

范式	规则	效果
1NF	列原子性	每列不可再分
2NF	消除部分依赖	非主键列完全依赖主键
3NF	消除传递依赖	非主键列不依赖其他非主键列

什么时候反范式：报表查询 JOIN 太多 → 冗余一个 user_name 到订单表，空间换时间。

第二原则：索引要懂原理

B+ 树结构

         [30]
        /    \
    [10,20]  [40,50]
    /  |  \   /  |  \
  叶子: <--双向链表-->

查找 key=25: 根→[30]左→[10,20]右→叶子(20,30)之间→不存在
范围查询 key∈[15,35]: 定位 15→顺链表读到 35

索引失效的 6 种场景

-- 假设索引: idx(a, b, c)

-- ① 最左前缀缺失
SELECT * FROM t WHERE b = 1;          -- ❌ 没有 a，不走索引

-- ② 中间断档
SELECT * FROM t WHERE a = 1 AND c = 2; -- ⚠️ 只用 a，b 缺失后 c 失效

-- ③ 范围查询后的列失效
SELECT * FROM t WHERE a = 1 AND b > 5 AND c = 2;
-- ✅ a 精确 + b 范围 → 只用 a+b，c 不走索引

-- ④ 函数/运算
SELECT * FROM t WHERE YEAR(create_time) = 2025;  -- ❌
SELECT * FROM t WHERE create_time >= '2025-01-01'; -- ✅

-- ⑤ 隐式类型转换
SELECT * FROM t WHERE phone = 13800138000;  -- ❌ phone 是 VARCHAR

-- ⑥ LIKE 前导模糊
SELECT * FROM t WHERE name LIKE '%kiyose';  -- ❌
SELECT * FROM t WHERE name LIKE 'kiyose%';  -- ✅

EXPLAIN 速读

EXPLAIN SELECT * FROM orders WHERE user_id = 5;
-- type:   const > eq_ref > ref > range > index > ALL
--             好 ←─────────────────────→ 差
-- key:    NULL = 没走索引
-- rows:   扫描行数，越小越好
-- Extra:  Using filesort = 需要额外排序（加索引）
--         Using temporary = 需要临时表（改 SQL 结构）

第三原则：事务隔离级别

级别	脏读	不可重复读	幻读	性能
READ UNCOMMITTED	✅	✅	✅	最快
READ COMMITTED	❌	✅	✅	快
REPEATABLE READ (MySQL 默认)	❌	❌	⚠️ 部分	中
SERIALIZABLE	❌	❌	❌	最慢

-- MySQL 查看当前级别
SELECT @@transaction_isolation;

-- 设置
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

踩坑记录

坑 1：SQLite 并发写入

错误: SQLITE_BUSY: database is locked

原因: SQLite 同时只允许一个写者
解决:
  ① PRAGMA journal_mode=WAL      -- 读写可并发
  ② PRAGMA busy_timeout=5000      -- 等 5 秒不立即报错
  ③ 批量写入放在同一事务中

坑 2：MySQL 隐式锁升级

-- ❌ 大范围 UPDATE 导致锁表
UPDATE orders SET status = 'expired' WHERE create_time < '2024-01-01';
-- 可能锁住百万行 → 其他事务阻塞 → 连接池打满

-- ✅ 分批更新
UPDATE orders SET status = 'expired'
WHERE create_time < '2024-01-01' LIMIT 1000;
-- 循环执行直到 affected_rows = 0

坑 3：自增 ID 耗尽

-- INT 自增上限 ≈ 21 亿，高频插入 2-3 年用完
-- ✅ 用 BIGINT
CREATE TABLE events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ...
);

坑 4：NULL 的比较陷阱

-- NULL 不是值，不能用 =
SELECT * FROM t WHERE col = NULL;      -- ❌ 永远返回空
SELECT * FROM t WHERE col IS NULL;     -- ✅

-- NULL 在唯一索引中
CREATE UNIQUE INDEX idx ON t(col);     -- 允许多个 NULL！
-- 因为 NULL != NULL，唯一约束对 NULL 失效

坑 5：连接池配置不对

# ❌ 连接池太小 → 请求排队
pool_size = 5   # 默认值

# ❌ 连接池太大 → DB 连接数超限
pool_size = 1000

# ✅ 估算公式
pool_size = (核心数 * 2) + 有效磁盘数
# 或: 并发请求数 * 平均查询时间(秒)
# 例: 100 req/s * 0.02s = 2 → 连接池 10 就够

设计清单

☐ 主键选型：UUID？自增 BIGINT？雪花 ID？
☐ 索引设计：覆盖高频查询 WHERE + ORDER BY + JOIN
☐ 字符集：始终 utf8mb4（不是 utf8）
☐ 时间戳：BIGINT 毫秒 或 DATETIME(3)
☐ 软删除 vs 硬删除：is_deleted 字段 or 归档表
☐ 分页：LIMIT+OFFSET vs 游标分页 (>100 万行必须游标)
☐ 备份策略：全量频率 + binlog 保留天数
☐ 监控指标：慢查询阈值、连接数、Buffer Pool 命中率