逐道-Ethan.Yang
逐道-Ethan.Yang

# 数据库设计

作者：Ethan.Yang
博客：https://blog.ethanyang.cn (opens new window)

# 一、数据库选型与场景画像

先画像、再选型：数据规模、结构形态、读写模式、实时性、事务一致性。

关键问题

  • 数据规模：是否海量/大数据（TB 级、PB 级）？
  • 数据模型：行式（OLTP）还是列式（OLAP/分析）？是否宽表（字段数多）？
  • 数据属性：核心业务数据 vs. 辅助数据（日志、行为、埋点）。
  • 事务要求：是否需要强事务 ACID？
  • 实时性：写入延迟容忍度？读取时延要求？
  • 查询模式：大量记录的少数列 or 少量记录的全部列？
  • 一致性等级：强一致/读已提交/最终一致？
  • CRUD 比例：读多写少/写多读少/混合？

常见选型

  • 关系型：MySQL、PostgreSQL、Oracle、SQLite（99%业务结构化数据优先）。
  • 列式（分析）：ClickHouse、HBase、Apache Kudu（大宽表、聚合分析）。
  • 键值：Redis、Memcached（高速缓存、会话、计数）。
  • 文档：MongoDB（灵活 Schema、半结构化）。
  • 时序：InfluxDB、VictoriaMetrics（监控指标/时序）。
  • 搜索：Elasticsearch、OpenSearch（倒排索引、全文检索）。

SQL 与 NoSQL 是互补关系：用对场景，少即是多。

MySQL 引擎选择（速览）

  • InnoDB：行级锁、MVCC、事务、B+Tree 索引（Hash 索引定义会被转换/忽略为 B+Tree）。
  • MyISAM（不推荐新项目）：表级锁、不支持事务。

# 二、数据建模流程与范式/反范式

两条路径

  • 需求/领域模型 → 概念模型（ER）→ 逻辑模型（表/键/关系）→ 物理模型（分区/索引/存储）。
  • 先对象后关系（常见做法） vs. 先关系后对象（更稳健，扩展性更好）。

目标：字段斟酌是否引发不一致、冗余、CRUD 异常。

原则：先遵循范式，再按业务做反范式优化。

范式速记

  • 1NF：字段不可再分（原子性）。
  • 2NF：非主属性完全依赖于候选键（消除部分依赖）。
  • 3NF：非主属性不依赖其他非主属性（消除传递依赖）。
  • BCNF：每个决定因素都是候选键（3NF 加强版）。
  • 4NF：消除多值依赖（主键与非主键一对多需拆分）。
  • 5NF：任何连接依赖都由候选键推导（不可再无损分解）。

反范式的场景

  • 为减少多表 Join、加速热点查询：冗余字段、快照表、中间汇总表。
  • 代价：占用空间、写复杂、更新风险↑ → 用事务/任务保障一致性。

# 三、读写与性能：索引、锁、事务

# 1）索引设计

  • 原理：B+Tree（范围/排序友好）、Hash（等值快、区间差）。
  • 适用：主键、外键、常用过滤字段、排序/分组列、唯一约束。
  • 代价：空间↑、写入/更新变慢。
  • 反模式：选择性极低列（性别/删除标记）建 Hash 索引→碰撞高，收益低。

# 2）并发控制与锁

  • 乐观锁：版本号/时间戳/哈希校验；冲突时重试 → 提升并发、牺牲操作方计算。
  • 悲观锁：SELECT ... FOR UPDATE（X锁），LOCK IN SHARE MODE（S锁）。
  • 更新锁 (U锁)：先读后写的权衡（不同数据库支持语义不同）。
  • 锁粒度：行锁 < 页锁 < 表锁（并发能力从高到低）。

# 3）死锁与排查

  • 四条件：互斥/不可剥夺/请求保持/循环等待 → 打破其一。
  • 策略：固定加锁顺序、失败即释放重试、缩短锁持有时间、避免全表更新。
  • 排查：线程 dump（应用）、死锁日志（DB）。

# 4）事务与隔离

  • ACID：WAL（A）、约束/唯一/外键（C）、锁/MVCC（I）、日志回放（D）。
  • 并发异常：脏写/脏读/不可重复读/幻读。
  • 隔离级别：读未提交 < 读已提交 < 可重复读 < 串行化（隔离↑并发↓）。

# 四、大数据量优化路线

优化顺序：索引 → 表分区 → 分库分表 → 读写分离。

# 1）表分区（同库内物理分段）

  • 类型：Range / List / Hash / Key。
  • 目标：让查询尽量命中单分区，降低跨区合并。
  • 物理：可分盘、分备份、分恢复。

# 2）分库分表

  • 水平拆分：按用户/业务键哈希或范围；结构相同。
  • 垂直拆分：冷热字段/大字段分离；结构不同。
  • 原则：避免跨库表 Join；设计路由键与拼接查询方案。
  • 中间件：ShardingSphere、MyCat。

# 3）读写分离

  • 读走从库、写走主库；读多写少收益最大。
  • 路由规则：SELECT→读库；INSERT/UPDATE/DELETE/DDL→写库。

# 五、主从复制与一致性

复制模式

  • 异步：主写即返；传输/解析延迟可能导致短暂不一致。
  • 半同步：主写成功且至少一个从库确认收到日志再返回。
  • 全同步：所有从库确认后返回（时延大）。

Binlog 模式

  • STATEMENT：写语句（now() 等函数可能不一致）。
  • ROW：记录行变更。
  • MIXED：混合（常用）。

读一致性策略

  • 写后读延迟：
    • 记录写操作唯一标识，在 TTL 内强制读主；
    • 或基于时间阈值/版本号控制。

# 六、非关系存储的搭配

  • Redis：缓存、分布式锁、计数器、队列。
  • ClickHouse/HBase：宽表聚合、离线/近实时分析。
  • MongoDB：文档灵活结构、原型快速迭代。
  • InfluxDB：时序指标。
  • Elasticsearch：全文检索、日志检索。

用中间件补齐短板，但避免无节制“堆技术”。

# 七、工程化 Checklist（落地/面试可用）

建模

  • 领域对象 → ER 图 → 表/键/关系明确
  • 范式合规（1~3NF/BCNF）且有反范式说明
  • 主键策略（自增/雪花/UUID）与唯一约束
  • 外键：是否在 DB 强约束 or 由应用层保证

索引

  • 主外键、查询过滤列、排序/分组列
  • 覆盖索引/联合索引顺序（高选择性在前）
  • 避免低选择性列建索引

一致性与并发

  • 乐观/悲观锁选择与冲突处理
  • 事务隔离级别与性能基线
  • 死锁预防策略与日志排查路径

扩展性

  • 分区列选择与命中原则
  • 分库分表路由键与跨分片聚合方案
  • 读写分离与写后读一致性策略

复制与备份

  • 复制模式（异步/半同步/全同步）与故障演练
  • Binlog 模式（ROW/MIXED）
  • 备份/恢复与演练频率

混合栈

  • Redis 缓存策略（TTL/双删/预热/击穿防护）
  • ES 检索索引生命周期管理（ILM）
  • OLAP（ClickHouse）与 OLTP（MySQL）数据血缘

运维与可观测

  • 慢查询审计与索引体检
  • 连接池/线程池/容量规划（压测基线）
  • 指标/日志/链路追踪三位一体

结语：设计不是一次性工作，而是在约束与演化中做取舍。先范式保证正确性，再以反范式与工程手段追求效率与稳定。

← 可靠性与应用保护 →