软考专题 · 数据库设计（选择题 25 + 案例答题套路 + 2 道完整模拟题 + 范式分解模板）

发表于2026-05-10|更新于2026-05-10|软考

|浏览量:

数据库是系统架构设计师综合知识里的重灾区——每年 6-8 道选择题（函数依赖 / 范式 / 事务 / 分布式 / 并发控制），案例分析几乎每年 1 道 25 分大题：传统是「ER 图 + 范式分解」，近年还高频出「关系库 + 缓存 / NoSQL 混合架构」综合题（Cache Aside、读写分离、反规范化、存储选型揉成一道大题）。这篇把高频选择题（带 ✅ 答案 + 解析）、案例答题套路、2 道完整模拟题（① ER+3NF 分解 ② MySQL+Redis+读写分离综合）、范式分解通用模板打包在一起。内容来自开源仓库 PeterGuy326/senior-software-architect-review。回到软考导览页看完整专题清单。

一、必背：核心理论

函数依赖与范式

部分依赖：X → Y，但 X 的真子集 X’ 也能 → Y → 违反 2NF
传递依赖：X → Y → Z → 违反 3NF
平凡依赖：Y ⊆ X 时 X → Y（可忽略）

范式	要求	常见违规
1NF	属性原子（不可再分）	一列存多值
2NF	1NF + 消除部分依赖（非主属性完全依赖候选键）	联合主键中只依赖一部分
3NF	2NF + 消除传递依赖	非主属性依赖非主属性
BCNF	3NF + 所有决定因素都是候选键	主属性对主属性的部分依赖

包含关系：BCNF ⊂ 3NF ⊂ 2NF ⊂ 1NF。

ER → 关系模式转换

1:1：合入任一方（选被依赖方）
1:N：N 端加外键指向 1 端（多方指向一方）
M:N：独立一张联系表，主键 = 双方主键的组合

事务 ACID + 隔离级别

ACID = 原子性（Atomicity）/ 一致性（Consistency）/ 隔离性（Isolation，保证并发互不干扰）/ 持久性（Durability）。

隔离级别	脏读	不可重复读	幻读
读未提交	✗	✗	✗
读已提交	✓ 解决	✗	✗
可重复读	✓	✓ 解决	✗（MySQL InnoDB 用 MVCC + 间隙锁解决）
串行化	✓	✓	✓ 解决

故障恢复依赖日志：undo log 回滚未提交事务，redo log 重做已提交事务。2PL（两阶段封锁） 保证可串行化调度，但不解决死锁。

分布式数据库

CAP = Consistency / Availability / Partition Tolerance，分布式必选 P，AP（MongoDB/Cassandra）vs CP（ZooKeeper/Etcd）。BASE = 基本可用 + 软状态 + 最终一致。分库分表：水平分片（按行，哈希/范围/按业务）、垂直分片（按列）——分片后跨片 JOIN 反而变慢，需避免。

二、高频选择题（25 题，✅ 为正确选项）

1. 关系模式 R(A,B,C,D)，F = {A→B, B→C, A→D}，R 的候选键为？

✅ A. A　B. AB　C. AC　D. AD

闭包 A+ = {A,B,C,D}，A 单独决定全部属性，唯一候选键。

2. 承上题，R 属于第几范式？

A. 1NF　✅ B. 2NF　C. 3NF　D. BCNF

候选键 A 单属性无部分依赖 → 2NF；存在传递依赖 A→B→C → 违反 3NF。

3. 将上题 R 分解为 3NF（保持依赖且无损连接）：

✅ A. R1(A,B,D), R2(B,C)　B. R1(A,B), R2(B,C), R3(A,D)　C. R1(A,B,C), R2(A,D)　D. R1(A,D), R2(B,C,D)

R1∩R2 = {B}，B 是 R2 的键 → 无损连接 ✓。

4. 事务的四个特性中，保证并发执行不相互干扰的是：

A. 原子性　B. 一致性　✅ C. 隔离性　D. 持久性

5. 下列隔离级别中不会出现”不可重复读”的是：

A. 读未提交　B. 读已提交　✅ C. 可重复读　D. 都会出现

6. 关于数据库视图，下列说法错误的是：

A. 视图是虚表，不存储数据　B. 视图可以简化复杂查询　✅ C. 所有视图都可以直接更新　D. 视图可以提供数据安全隔离

涉及 JOIN / 聚合 / DISTINCT 的视图不可直接更新。

7. E-R 图中，多对多（M:N）联系转换为关系模式时应：

A. 合并到任一端实体　B. 合并到 M 端　C. 合并到 N 端　✅ D. 独立成一个关系，主键为两端主键的组合

8. 索引对数据库性能的影响描述错误的是：

A. 索引加速 SELECT　B. 索引增加 INSERT/UPDATE/DELETE 开销　✅ C. 索引越多越好　D. 过多索引占用存储

一般建议一张表索引不超过 6 个。

9. 分布式数据库中，CAP 理论指：

A. 一致性/原子性/持久性　✅ B. 一致性/可用性/分区容错性　C. 原子性/一致性/隔离性　D. 并发性/可靠性/性能

10. 下列 NoSQL 中，最适合存储社交关系网络的是：

A. Redis　B. MongoDB　C. HBase　✅ D. Neo4j

图数据库天然适合社交、反欺诈、推荐。

11. 数据仓库的数据特征不包括：

A. 面向主题　B. 集成性　✅ C. 实时更新　D. 随时间变化

数仓四特征：面向主题、集成、相对稳定（非实时）、反映历史变化。

12. 主键与外键描述正确的是：

A. 主键可以为 NULL　B. 一张表可以有多个主键　✅ C. 外键必须引用另一张表的主键或唯一键　D. 外键值必须唯一

13. 规范化理论中，属于 BCNF 的关系必然满足：

A. 1NF　B. 2NF 但不一定 3NF　✅ C. 3NF　D. 所有函数依赖被打破

14. 两阶段封锁协议（2PL）解决的问题是：

A. 死锁　✅ B. 事务可串行化　C. 数据冗余　D. 并发性能

2PL 保证可串行化，但可能产生死锁。

15. 关系代数中 σ、π、⋈ 分别表示：

A. 投影、选择、连接　✅ B. 选择、投影、连接　C. 连接、投影、选择　D. 选择、连接、投影

σ 选择（筛行）、π 投影（筛列）、⋈ 自然连接。

16. 分布式事务方案中，强一致性最强但性能最差的是：

A. 本地消息表　B. Saga　C. TCC　✅ D. 2PC/XA

2PC/XA 协调者阻塞保证强一致但性能差；TCC 强一致但业务侵入大。

17. TCC 模式中 Confirm 和 Cancel 操作必须满足：

A. 原子性　✅ B. 幂等性　C. 可见性　D. 隔离性

网络抖动可能多次调用，必须幂等。

18. 数据库的三级模式结构不包括：

A. 外模式（视图层）　B. 概念模式（逻辑层）　C. 内模式（物理层）　✅ D. 应用模式

三级模式 = 外模式 / 模式（概念）/ 内模式；两级映射保证数据独立性。

19. OLTP 与 OLAP 的区别描述错误的是：

A. OLTP 面向事务，OLAP 面向分析　B. OLTP 强调并发吞吐，OLAP 强调复杂查询　✅ C. OLTP 数据规模通常大于 OLAP　D. OLAP 常用星型/雪花模型

OLAP 数据量通常远大于 OLTP（含历史聚合）。

20. 以下对 MySQL InnoDB 与 MyISAM 对比错误的是：

A. InnoDB 支持事务，MyISAM 不支持　B. InnoDB 支持行锁，MyISAM 只有表锁　C. InnoDB 支持外键，MyISAM 不支持　✅ D. MyISAM 默认支持崩溃恢复，InnoDB 不支持

InnoDB 通过 redo log 支持崩溃恢复。

21. 数据库的故障恢复技术通常依赖：

✅ A. 日志文件（Log）　B. 索引　C. 视图　D. 触发器

undo log 回滚未提交事务，redo log 重做已提交事务。

22. 以下关于分库分表描述错误的是：

A. 水平分片按行拆分，垂直分片按列拆分　B. 分库分表可降低单库压力　✅ C. 分片后 JOIN 性能一定提升　D. 常用分片策略：哈希、范围、按业务

跨片 JOIN 反而变慢，改用冗余字段或 ES 搜索。

23. 查询优化器的优化阶段通常不包括：

A. 语法解析　B. 逻辑优化　C. 物理优化　✅ D. 代码发布

语法解析 → 逻辑优化（谓词下推、子查询展开）→ 物理优化（索引选择、连接算法）→ 执行。

24. “读已提交”隔离级别解决的并发问题是：

✅ A. 仅脏读　B. 脏读 + 不可重复读　C. 脏读 + 幻读　D. 三者全部

25. 分布式系统中 BASE 理论的三要素是：

✅ A. 基本可用 / 软状态 / 最终一致　B. 原子/一致/持久　C. 批量/异步/松耦合　D. 强一致/可用/弹性

BASE = Basically Available + Soft state + Eventually consistent。

原文 + 解析：exam-bank/03-database.md

三、案例答题套路（必考题型，25 分）

问题 1：补全 ER 图

找题干中名词 → 实体
找动词短语 → 联系
属性分到实体内
弱实体用双框，多值属性用双椭圆

问题 2：ER → 关系模式

1:1：合入任一方（选被依赖方）
1:N：N 端加外键指向 1 端
M:N：独立一张联系表（主键 = 双方主键组合）
关系模式写法：学生(学号, 姓名, 系号)，主键加粗 / 加下划线

问题 3：判范式 + 分解（通用模板）

(1) 列函数依赖集 F
(2) 求候选键（闭包算法）
(3) 判范式：
    - 有非原子属性 → 1NF
    - 有部分依赖 → 违反 2NF
    - 有传递依赖 → 违反 3NF
(4) 分解：
    - 保持依赖、无损连接（两个性质都要明确说出）
    - 每个子模式都是 3NF 或 BCNF

问题 4：参照完整性

插入子表 → 要求父表有对应键
删除/更新父表 → 级联（CASCADE）、置空（SET NULL）、拒绝（RESTRICT）

万能高分句

“学生与课程之间是 多对多 联系，需引入选课联系表，主键为 (学号, 课号) 组合”
“当前模式存在传递依赖 学号→系号→系主任，违反 3NF，应分解为 学生(学号,姓名,系号) 和 系(系号,系主任)“
“为保证参照完整性，在子表的外键上定义 ON DELETE CASCADE，父记录删除时联动清理”

并发与分布式加分点

并发控制：悲观锁（SELECT ... FOR UPDATE）vs 乐观锁（版本号/时间戳）；问题：脏读 / 不可重复读 / 幻读 / 丢失更新
分布式：分片（范围/哈希）+ 复制（主从/多主）+ CAP/BASE + 2PC/3PC

综合题：关系库 + 缓存 + 读写分离怎么下笔（🔥 高频）

这类题给一段”读多写少 + 大促高并发”的场景（电商商品/详情/库存），问缓存设计、读写一致性、反规范化。按这个顺序铺答案，每点一句结论 + 一句理由/参数：

先判什么数据该缓存：读多写少 + 容忍秒级一致 + 计算/JOIN 贵 + 热点集中 → 适合缓存；写极频繁的字段（如库存，每秒扣几十次）单独一个小 key，别和详情聚合对象绑在一起，否则每次扣减失效整个对象、命中率塌。
缓存粒度 = 二级 key 拆分：xxx:base:{id}（低频写，改了整删）+ xxx:hot:{id}（高频写，只动这个小 key），读时应用层组装。
读路径 = Cache Aside：查缓存 → 未命中加分布式锁防并发回源（SET lock NX PX）→ 查（从）库 → 回填（TTL = base + 随机抖动）→ 返回。
写路径 = 先更 DB（主库）再删缓存——不是”更新缓存”（删才幂等、避免中间态），不是”先删缓存再更 DB”（并发读会回写旧值造成长期脏数据）；高并发再叠”延迟双删“或”Canal 监听 binlog“兜底。
三大问题对位写：穿透 → 布隆过滤器 + 缓存空值；击穿 → 互斥锁 / 逻辑过期 + 异步刷新；雪崩 → TTL 打散 + 多级缓存（本地 Caffeine + Redis）+ 熔断降级 + 集群高可用。
读写分离的坑必答：写主读从、主从异步复制 → “改完立刻刷新读到旧值”；解法给对比表 + 推荐组合：强制读主（Hint Master，简单强一致但加主库压力）/ 写后双写缓存（不加主库压力但有极小窗口）/ 等复制位点（半同步、读带 GTID，一致性强但写延迟上升）。
反规范化收尾拔高：冗余稳定字段（类目名/品牌名）省 JOIN，代价是更新放大 + 违反 3NF + 一致性维护上移到应用层；适用条件——读远多于写、被冗余字段极少变更、能接受用 binlog 监听或定时任务做最终一致；一句话——“反规范化 = 用空间和一致性维护成本换查询性能，工程上常’规范化设计 + 针对性反规范化 + 缓存’三层叠加”。

这些缓存细节（穿透/击穿/雪崩的解法与参数、Cache Aside 陷阱、分布式锁失效场景、热点 Key 治理）的标准句式见消息中间件与缓存专题的”案例答题套路”一节；完整模拟题见本文 §四模拟题 2。

常见陷阱

❌	✅
M:N 联系不建表	必须独立表
联系表主键只用一个	主键 = 双方主键组合
分解只说结果不给函数依赖	必须列出 F 再推
不说保持依赖 / 无损连接	两个性质必须明确
外键方向错	多方指向一方

四、完整模拟案例题（2 道，25 分/道）

⚠️ 自主命题改编，建议严格 25 分钟限时作答。仓库里这一题型共 5 道完整模拟题（含题干、参考答案、评分要点）：ER+3NF 分解 · 医院 HIS 弱实体 · 跨境电商分库分表 · MySQL+Redis+读写分离综合 · 多类型存储选型，见 case-types/02-database-design.md。下面摘录最经典的两道。

模拟题 1 · 在线教育选课系统 ER + 3NF 分解

【题干简述】 某在线教育平台选课系统，涉及学生、教师、课程、班级、选课记录、成绩等实体。一个学生可选多门课，一门课可被多个学生选；一个教师可教多门课；一门课属于一个学科。初始设计把所有信息放在一张大表 选课表(学号, 姓名, 课号, 课名, 学分, 教师号, 教师名, 学科号, 学科名, 成绩)，存在大量数据冗余和更新异常。

问 1（8 分）：画出该选课系统的 ER 图（标出实体、关键属性、联系类型与基数）。
问 2（10 分）：写出 选课表 的函数依赖集 F，求候选键，判断它属于第几范式，并将其无损连接、保持依赖地分解到 3NF。
问 3（7 分）：说明该设计存在哪些更新异常（插入/删除/修改异常），并指出分解后如何消除；为保证学生删除时联动清理其选课记录，应在外键上设置什么约束。

【参考答案要点】

问 1 ER 图：实体 = 学生(学号, 姓名)、教师(教师号, 教师名)、课程(课号, 课名, 学分)、学科(学科号, 学科名)。联系：学生 ——选课(成绩)—— 课程为 M:N；教师 ——讲授—— 课程为 1:N；学科 ——包含—— 课程为 1:N。基数：学生(0,N)选课，课程(0,N)被选；教师(0,N)讲授，课程(1,1)被讲授；学科(1,N)包含，课程(1,1)属于。

问 2 F = {学号→姓名, 课号→课名, 课号→学分, 课号→教师号, 教师号→教师名, 课号→学科号, 学科号→学科名, (学号,课号)→成绩}。候选键 = (学号, 课号)（闭包覆盖全部属性）。范式：非主属性”姓名”只依赖”学号”（候选键的真子集）→ 部分依赖 → 违反 2NF，只属于 1NF。无损连接 + 保持依赖分解到 3NF：

学生(学号, 姓名)
课程(课号, 课名, 学分, 教师号, 学科号)
教师(教师号, 教师名)
学科(学科号, 学科名)
选课(学号, 课号, 成绩)

每个子模式：学生/教师/学科均为单候选键无传递依赖（3NF/BCNF）；课程的教师号、学科号直接依赖候选键课号（3NF）；选课的成绩完全依赖联合主键（3NF）。各分解片函数依赖之并 = 原 F（保持依赖），且可通过自然连接还原（无损）。

问 3 更新异常：①插入异常——新开一门还没人选的课，无法插入（学号为空，联合主键不允许）；②删除异常——删除某课最后一个选课记录，连带丢失该课的课名/学分/教师等信息；③修改异常——某教师改名，需更新该教师所教所有课的所有选课记录的”教师名”，遗漏即不一致。分解后：课程信息独立存于课程表、教师信息存于教师表，每个事实只存一份，三类异常消除。学生删除联动清理选课记录：在选课表的外键 学号 上设置 ON DELETE CASCADE。

模拟题 2 · 电商商品中心：MySQL + Redis 缓存 + 读写分离综合（🔥 高频综合题）

这就是你常听说的「数据库 + 缓存一起考」的那种综合大题——把数据库设计、缓存模式、读写分离、反规范化揉进一道 25 分题。完整版（含更细评分要点）见 case-types/02-database-design.md 模拟题 4。

【题干简述】 生鲜电商「鲜达优选」商品中心，MySQL 8.0 一主两从、读写分离。核心表 spu(spu_id, spu_name, category_id, brand_id, detail_html, status)、sku(sku_id, spu_id, spec_json, price, stock, status)、category(category_id, category_name, parent_id, level)、brand(brand_id, brand_name)。商品详情页要展示 SPU 信息 + 其下所有 SKU 的规格/价格/库存 + 类目路径 + 品牌名（一次请求 JOIN 4 表）。SPU 120 万、SKU 400 万；详情页读 QPS 大促峰值 8 万，写（改价/改库存/上下架）峰值约 300，读写比约 250:1；P99 < 150ms。痛点：① JOIN 4 表大促把 DB CPU 打到 95%；② 运营改价后部分用户刷新仍看到旧价；③ 黑产用不存在的 spu_id 爬接口；④ 大促 0 点商品集中上架后缓存集中失效打垮 DB。架构师拟引入 Redis Cluster 缓存 SPU 详情聚合对象、读走 Cache Aside、写走”先更 MySQL 主库再删缓存”、TTL 30min；DBA 质疑：(a) 库存每秒扣几十次，整聚合对象一起缓存命中率会不会塌？(b) 改价后”读自己的写”怎么保证？(c) 把 category_name/brand_name 冗余进 spu 表能不能省掉 JOIN？

问 1（6 分）：哪些数据适合放进 Redis、缓存粒度怎么定？针对质疑 (a) 给出聚合缓存 vs 字段级缓存的取舍方案（含 key 设计）。
问 2（7 分）：描述本系统 Cache Aside 的读/写路径，说明”先更 DB 再删缓存”相比”先删缓存再更 DB””更新缓存”的优势。
问 3（6 分）：针对痛点 ③（穿透）和痛点 ④（雪崩）各给至少 2 种解法 + 在本架构的落地参数。
问 4（6 分）：分析痛点 ②（改价读到旧价）的根因，给出读写分离下”读己之写一致性”方案（≥2 种对比）；评价质疑 (c) 反规范化的利、弊及适用条件。

【参考答案要点】

问 1 适合缓存的数据：读多写少、对一致性容忍秒级、计算/JOIN 贵、热点集中——spu_name/detail_html/类目路径/brand_name（几乎只读）✅、sku.price（改价低频）✅；sku.stock（每秒扣几十次）❌ 不该和详情聚合在一起。取舍：二级 key 拆分——spu:base:{spu_id} 存 SPU 基础信息 + 类目路径 + 品牌名 + SKU 骨架（含价格），TTL = 1800s + rand(0,600s)，改价/改类目/上下架时删；sku:stock:{sku_id} 单独存库存（或直接 Redis 原子计数器预扣），扣库存只动这个小 key。详情页 = GET spu:base + MGET sku:stock:* 组装。→ 低频写失效 base、高频写只动 stock，互不干扰，命中率高（回应质疑 a）。

问 2 读：GET spu:base:{id} 命中→组装返回；未命中→SET lock:spu:{id} {uuid} NX PX 10000 防并发回源→查从库 JOIN→组装→SET spu:base EX (1800+rand)→DEL lock→返回（没抢到锁的 sleep 20ms 重试）。写：UPDATE MySQL 主库 → DEL spu:base:{id}（改库存还要同步 sku:stock）。“先更 DB 再删缓存”优势：① 优于”先删缓存再更 DB”——后者”删缓存→更 DB 完成”窗口里并发读会查从库（旧值）回写缓存→长期脏数据；前者窗口仅 ms 级且后续读自动修正。② 优于”更新缓存”——删幂等；更新聚合对象要重新 JOIN 计算、写路径变重；并发两个写算的聚合对象可能互相覆盖成中间态；被改的 SPU 短期未必被读，更新是浪费——删 + 懒加载更经济。

问 3 痛点 ③ 穿透：① 布隆过滤器——全量上架 SPU ID（120 万）灌 RedisBloom（误判率 0.1% ≈ 200KB），查询前先 BF.EXISTS，每日凌晨全量重建兜底；② 缓存空值——查不到的 spu_id 写 spu:base:{id}="NULL"，TTL 60s + rand(0,30s)，配合网关层 spu_id 格式/范围校验。痛点 ④ 雪崩：① TTL 打散——基础 1800s + rand(0,600s)，上架流程主动 SET 预热；② 多级缓存 + 熔断降级——本地 Caffeine（容量 1 万、TTL 60s）挡热点，Sentinel 在 Redis 异常率 > 30% 或 RT > 50ms 时熔断、降级为本地缓存 + 限流 + 静态兜底页，Redis 6 主 6 从 + 跨可用区；③ 互斥锁回源兼防击穿。

问 4 根因：读写分离下写走主库、读走从库，主从复制异步（大促延迟 50~800ms）；运营在主库改完价立刻刷新，请求路由到尚未同步的从库→读到旧值（删了缓存回源从库照样旧）。读己之写方案对比：① 强制读主（Hint Master）——写后一小段时间内该商品的读强制路由主库（ShardingSphere HintManager）：简单强一致，但主库读压力上升；② 写后双写缓存——改价时不只删缓存、直接 SET 新值进 Redis，读优先读缓存→立即可见：不增加主库压力，但缓存与 DB 仍有极小不一致窗口；③ 等待复制位点/半同步复制——关键写后等从库 ACK 或读请求带 GTID 路由到已追上的从库：一致性强但写延迟上升、改造成本高。推荐组合：改价时”先更主库 + 立即 SET 新值进 Redis + 异步再 DEL 一次兜底”，C 端读缓存即得新值；运营预览页用强制读主。反规范化 (c)：利——列表/详情不用 JOIN category/brand，单表查询、高 QPS 下显著降负载；弊——数据冗余（类目/品牌改名要更新所有相关 spu 行，更新放大）、违反 3NF（category_name 传递依赖 category_id）、一致性维护成本上移到应用层。适用条件——读远多于写、被冗余字段极少变更、能接受用 Canal 监听 binlog 或定时任务做最终一致；本场景读写比 250:1、类目品牌名基本不变 → 可以做，但只冗余稳定字段、改名时用 binlog 异步刷新、仍以 category_id/brand_id 为准并保留外键。本质是”反规范化 = 用空间和一致性维护成本换查询性能“——工程上常”规范化设计 + 针对性反规范化 + 缓存”三层叠加。