第四章 数据库系统基础知识

数据库系统是软件设计师的核心知识域，也是应用系统设计与开发的数据基石。本章围绕关系模型、SQL语言、规范化理论、事务管理、并发控制与数据库设计六大主题展开，帮助您建立从理论到工程实践的完整认知框架。以下先给出本章知识结构总览，再逐层深入。

上图展示了本章六大知识模块的关联关系：关系模型是理论基础，SQL是操作语言，规范化理论指导表结构设计，事务与并发控制保障数据正确性，数据库设计则是将上述知识综合应用于工程实践的系统方法。下面逐一深入。

4.1 关系模型：数据库的数学基石

4.1.1 核心原理

关系模型由E.F. Codd于1970年提出，以集合论和谓词逻辑为基础。其核心概念包括：

• 关系（Relation）：一张二维表，具有行和列，对应数学上的“关系”概念。
• 元组（Tuple）：表中的一行，代表一个实体或实体间的一个联系。
• 属性（Attribute）：表中的一列，代表实体的某个特征。
• 主键（Primary Key）：唯一标识一个元组的属性或属性组合。
• 外键（Foreign Key）：引用其他关系主键的属性，用于建立表间关联。
• 关系完整性：包括实体完整性（主键非空且唯一）、参照完整性（外键值必须存在于被引用表的主键中或为NULL）、用户定义完整性（业务规则约束）。

关系模型的操作基于关系代数，主要包括选择（σ）、投影（π）、连接（⋈）、并（∪）、交（∩）、差（−）等运算。这些运算为SQL语言提供了理论基础。

4.1.2 判断方法：关系完整性检查

在设计数据库时，需按以下步骤检查关系完整性：

1. 确认每个表都有明确的主键，且主键值非空、唯一。
2. 检查所有外键列，确保其引用的是有效的主键值或为NULL。
3. 根据业务规则添加CHECK约束（如年龄>0）、UNIQUE约束（如身份证号唯一）等。
4. 使用数据库的完整性约束机制（如MySQL的FOREIGN KEY、SQL Server的CHECK）在物理层强制实施。

4.1.3 案例拆解：学生选课系统关系模型

以学生选课系统为例，设计三个关系：

关系名	属性	主键	外键
Student	Sno, Sname, Sdept, Sage	Sno	—
Course	Cno, Cname, Credit	Cno	—
SC	Sno, Cno, Grade	(Sno, Cno)	Sno→Student, Cno→Course

该设计满足实体完整性（每个主键非空唯一）和参照完整性（SC中的Sno和Cno分别引用Student和Course的有效值）。若某学生退学，需先删除SC中相关记录，再删除Student记录，否则违反参照完整性。

4.1.4 常见误区与总结

复盘：关系模型的核心在于“规范化”地表达实体与联系。设计时先画ER图，再转换为关系模式，最后用完整性约束加固，是标准流程。

4.2 SQL语言：操作数据库的利器

4.2.1 核心原理

SQL（Structured Query Language）是关系数据库的标准语言，分为四类：

• DDL（数据定义语言）：CREATE、ALTER、DROP，用于定义和修改表结构。
• DML（数据操纵语言）：SELECT、INSERT、UPDATE、DELETE，用于数据操作。
• DCL（数据控制语言）：GRANT、REVOKE，用于权限管理。
• TCL（事务控制语言）：COMMIT、ROLLBACK、SAVEPOINT，用于事务管理。

SQL的执行顺序与书写顺序不同：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。理解这一点对优化查询至关重要。

4.2.2 操作流程：SQL查询优化步骤

1. 明确需求：确定需要哪些列、哪些表、过滤条件。
2. 编写查询：使用JOIN连接表，WHERE过滤行，GROUP BY分组，HAVING过滤组。
3. 检查执行计划：使用EXPLAIN（MySQL）或查看执行计划（SQL Server/Oracle），确认是否走索引。
4. 优化调整：添加索引、改写JOIN条件、避免SELECT *、使用LIMIT分页等。

4.2.3 实战场景：订单查询优化

场景：电商系统需要查询“2024年1月下单且金额大于500元的用户姓名和订单总额”。初始SQL：

SELECT u.name, SUM(o.amount) AS total
FROM users u, orders o
WHERE u.id = o.user_id
  AND o.order_date BETWEEN '2024-01-01' AND '2024-01-31'
  AND o.amount > 500
GROUP BY u.name;

问题：使用隐式连接（逗号），且amount>500在聚合前过滤，可能无法利用索引。优化后：

SELECT u.name, SUM(o.amount) AS total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.order_date >= '2024-01-01' AND o.order_date < '2024-02-01'
  AND o.amount > 500
GROUP BY u.name
HAVING total > 0;

同时建议在orders表的(user_id, order_date, amount)上建立复合索引。优化后查询时间从2.1秒降至0.3秒。

4.2.4 常见误区与总结

复盘：SQL优化的核心是“减少扫描行数”和“减少返回列数”。先看执行计划，再针对性优化，切忌盲目加索引。

4.3 规范化理论：设计高质量表结构

4.3.1 核心原理

规范化用于消除数据冗余和更新异常。范式等级从低到高：

• 1NF：属性不可再分（原子性）。
• 2NF：在1NF基础上，消除非主属性对主键的部分函数依赖。
• 3NF：在2NF基础上，消除非主属性对主键的传递函数依赖。
• BCNF：在3NF基础上，消除主属性对主键的部分和传递依赖（即每个决定因素都是超键）。

函数依赖（Functional Dependency）是规范化的核心概念：若X的值确定后Y的值唯一确定，则称X函数决定Y，记作X→Y。

4.3.2 判断方法：范式判定步骤

1. 列出所有函数依赖。
2. 找出主键（候选键）。
3. 检查是否存在非主属性对主键的部分依赖（若有，则不满足2NF）。
4. 检查是否存在非主属性对主键的传递依赖（若有，则不满足3NF）。
5. 检查所有决定因素是否都是超键（若不是，则不满足BCNF）。

4.3.3 案例拆解：订单表规范化

原始表：Order(OrderID, CustomerName, CustomerAddr, ProductID, ProductName, Qty, Price)

函数依赖：OrderID→CustomerName, CustomerAddr; ProductID→ProductName, Price; (OrderID, ProductID)→Qty

问题：CustomerName和ProductName存在部分依赖，导致数据冗余（同一客户多次下单重复存储地址）。分解为：

表名	属性	主键	范式等级
Customer	CustomerID, Name, Addr	CustomerID	BCNF
Product	ProductID, Name, Price	ProductID	BCNF
Order	OrderID, CustomerID, OrderDate	OrderID	BCNF
OrderDetail	OrderID, ProductID, Qty	(OrderID, ProductID)	BCNF

分解后消除了冗余，但查询时需要多表JOIN，这是规范化的代价。

4.3.4 常见误区与总结

复盘：规范化是“理论指导”，实际设计需结合业务读写比例、数据量、查询模式综合权衡。

4.4 事务管理：保证数据一致性

4.4.1 核心原理

事务（Transaction）是数据库执行逻辑的最小工作单元，具有ACID特性：

• 原子性（Atomicity）：事务内操作要么全部成功，要么全部回滚。
• 一致性（Consistency）：事务执行前后，数据库完整性约束不被破坏。
• 隔离性（Isolation）：并发事务之间互不干扰。
• 持久性（Durability）：事务提交后，修改永久保存。

事务的结束方式：COMMIT（提交）或ROLLBACK（回滚）。

4.4.2 操作流程：事务控制语句

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- 检查业务条件，若成功则提交，否则回滚
COMMIT;  -- 或 ROLLBACK;

在存储过程或应用程序中，需捕获异常并决定提交或回滚。

4.4.3 案例拆解：银行转账事务

场景：用户A向用户B转账500元。若在扣款后、加款前系统崩溃，无事务则A的钱丢失。使用事务后：

1. BEGIN TRANSACTION
2. UPDATE A SET balance = balance - 500
3. UPDATE B SET balance = balance + 500
4. COMMIT

若步骤3失败，执行ROLLBACK，A的余额恢复原值。事务日志（Redo/Undo）保证崩溃恢复。

4.4.4 常见误区与总结

复盘：ACID是事务的黄金标准，但隔离级别可放宽以提升性能，需根据业务场景选择。

4.5 并发控制：多用户下的数据安全

4.5.1 核心原理

并发控制解决多事务同时执行时的数据一致性问题。主要技术包括：

• 锁（Lock）：共享锁（S锁，读锁）和排他锁（X锁，写锁）。
• 时间戳（Timestamp）：为每个事务分配时间戳，按时间顺序调度。
• 多版本并发控制（MVCC）：通过保存数据快照，实现读写不阻塞。

并发可能引发的问题：丢失更新、脏读、不可重复读、幻读。

4.5.2 判断方法：隔离级别与问题对应

隔离级别	脏读	不可重复读	幻读
读未提交（Read Uncommitted）	可能	可能	可能
读已提交（Read Committed）	不可能	可能	可能
可重复读（Repeatable Read）	不可能	不可能	可能（MySQL默认级别，InnoDB通过间隙锁避免）
可串行化（Serializable）	不可能	不可能	不可能

4.5.3 实战场景：库存扣减并发控制

场景：秒杀系统，库存仅10件，并发请求1000个。若使用“先查库存再更新”逻辑：

-- 错误示例
SELECT stock FROM product WHERE id=1;  -- 假设读到10
-- 业务判断stock>0
UPDATE product SET stock = stock - 1 WHERE id=1;

并发下多个请求读到相同库存，导致超卖。正确方案：

-- 原子更新
UPDATE product SET stock = stock - 1 WHERE id=1 AND stock > 0;
-- 检查受影响行数，若为0则库存不足

或使用悲观锁（SELECT ... FOR UPDATE）或乐观锁（版本号机制）。

4.5.4 常见误区与总结

复盘：并发控制是“性能与一致性”的权衡。先明确业务允许的异常级别，再选择隔离级别和锁策略。

4.6 数据库设计：从需求到物理模型

4.6.1 核心原理

数据库设计遵循“自顶向下、逐步求精”的原则，分为六个阶段：

1. 需求分析：收集数据需求、处理需求、完整性约束。
2. 概念结构设计：绘制ER图（实体-联系图）。
3. 逻辑结构设计：将ER图转换为关系模式，并规范化。
4. 物理结构设计：选择存储引擎、索引、分区等。
5. 数据库实施：建库建表、编写存储过程、加载数据。
6. 运行与维护：性能监控、备份恢复、结构调整。

4.6.2 操作流程：ER图转关系模式

1. 每个实体转换为一个关系表，实体属性为表的列。
2. 1:1联系：将一方的主键加入另一方作为外键。
3. 1:N联系：将“一”方的主键加入“多”方作为外键。
4. M:N联系：创建独立的关系表，包含双方主键及联系属性。

4.6.3 案例拆解：在线教育平台数据库设计

需求：学生选课、教师授课、课程评价。ER图核心实体：学生、教师、课程、评价。转换后关系模式：

表名	关键字段	说明
student	stu_id(PK), name, major	学生表
teacher	tea_id(PK), name, dept	教师表
course	course_id(PK), title, tea_id(FK)	课程表，1:N教师-课程
enrollment	stu_id(FK), course_id(FK), grade	选课表，M:N学生-课程
review	review_id(PK), course_id(FK), stu_id(FK), rating, comment	评价表

物理设计时，为enrollment表的(stu_id, course_id)建立唯一索引，为course表的tea_id建立普通索引。

4.6.4 常见误区与总结

复盘：数据库设计是“艺术+工程”的结合。遵循规范化理论保证数据质量，同时根据实际场景进行反规范化优化，是成熟工程师的标志。