09. 数据库系统¶

体系结构、数据仓库、数据库设计、关系代数、规范化、SQL 与并发控制。

配套阅读：数据库系统：课件笔记，含课程配图、例题和参考解析。

本章目录¶

体系结构与数据库设计¶

--- markmap: height: 760 initialExpandLevel: 2 autoFit: true colorFreezeLevel: 2 maxWidth: 280 spacingHorizontal: 80 spacingVertical: 12 nodeMinHeight: 24 paddingX: 10 --- # 数据库体系结构与设计 ## 系统体系结构 ### 集中式数据库 - 数据和主要管理功能集中在同一系统 ### 客户端 / 服务器 - 客户端承担交互与应用逻辑，服务器提供数据库服务 ### 分布式数据库 - 数据物理分布，逻辑上统一管理 - 各站点可具有局部自治能力，并协作完成全局操作 - 分片透明：用户无需了解数据怎样拆分 - 位置透明：用户无需了解数据存放在哪个站点 - 复制透明：用户无需显式维护多个副本 ### 并行数据库 - 通过多个处理器、磁盘或节点并行执行任务 - 共享内存、共享磁盘、无共享等体系结构 ## 三级模式与两级映射 ### 外模式 - 用户或应用看到的数据视图，可有多个 ### 概念模式 - 数据库整体逻辑结构、联系与约束 ### 内模式 - 数据的物理存储组织、索引和访问路径 ### 外模式 / 概念模式映射 - 支持逻辑数据独立性 - 概念模式改变时，通过调整映射尽量保持外模式不变 ### 概念模式 / 内模式映射 - 支持物理数据独立性 - 存储结构改变时，通过调整映射保持逻辑结构不变 ## 数据仓库 - 面向主题：围绕分析对象组织数据 - 集成：统一多个系统的数据口径和表示 - 相对稳定：主要面向查询分析，而非频繁事务更新 - 反映历史变化：保留带时间维度的数据 ## 数据库设计流程 ### 需求分析 - 确定数据、处理、性能和安全需求 - 数据流图、数据字典、需求规格说明 ### 概念结构设计 - 建立与具体 DBMS 无关的 E-R 模型 - 抽象实体、属性和联系 - 合并局部视图，消除属性、命名与结构冲突 ### 逻辑结构设计 - 将 E-R 模型转换为关系模式 - 确定键、约束、视图，进行关系规范化 ### 物理设计 - 结合 DBMS、硬件和操作系统选择存储与索引 - 评估空间占用、查询性能和更新成本 ### 实施与运行 - 建表、导入数据、测试和部署 - 监控、备份与持续优化 ## E-R 模型 ### 实体与属性 - 实体：矩形；弱实体：双矩形 - 属性：椭圆；可分为单值、多值、复合、派生属性 - 联系：菱形；属性也可属于联系 - 特殊化：从一般实体抽取子类，常用 ISA 层次表示 ### 联系 - 实体集内部的自联系 - 二元联系：1:1、1:n、m:n - 三元及更高元联系 - 聚集：把联系及参与实体看作整体，再参与其他联系 ## E-R 转换为关系模式 ### 实体 - 一个普通实体集通常转换为一个关系模式 - 选择能唯一标识元组的候选键 - 多值属性通常另建关系，并保留所属实体的键 ### 1:1 联系 - 可独立建表，也可把外键及联系属性放入一端 - 通过唯一约束体现一对一，结合参与约束选择合并端 ### 1:n 联系 - 通常在 n 端加入 1 端的键及联系属性 ### m:n 联系 - 单独建立联系表，包含各端的键及联系属性 - 两端键的组合通常作为候选键 ### 多元联系 - 通常单独建立关系，包含各实体键和联系属性 - 候选键由联系的基数约束确定 ### 弱实体 - 使用所属强实体的键与自身部分键共同标识 ## 关系模型的键与约束 - 超键：能够唯一标识元组的属性集合 - 候选键：不含冗余属性的超键 - 主键：从候选键中选定一个 - 外键：引用另一关系或本关系的候选键；SQL 中由被引用的主键或唯一约束支撑 - 实体完整性：主键唯一且非空 - 参照完整性：外键值符合被引用关系和空值约束 - 用户定义完整性：取值范围、业务规则等

关系代数与规范化¶

--- markmap: height: 760 initialExpandLevel: 2 autoFit: true colorFreezeLevel: 2 maxWidth: 280 spacingHorizontal: 80 spacingVertical: 12 nodeMinHeight: 24 paddingX: 10 --- # 关系代数与规范化 ## 关系代数 ### 集合运算 - 并、交、差要求两关系具有相容的属性数目和对应域 - 笛卡尔积：组合两关系的所有元组 ### 选择与投影 - 选择 σ：按条件筛选行 - 投影 π：保留指定列，经典关系代数会去重 ### 连接 - θ 连接：笛卡尔积后按条件选择 - 等值连接：连接条件为属性相等 - 自然连接：按所有同名属性等值连接，合并重复连接列 - 外连接：保留不匹配元组，缺失侧用空值补齐 ### 除法 - 表达“与给定集合中的所有值都有关联” - 例如查找选修了指定全部课程的学生 ### 重命名 - 为关系或属性改名，便于自连接并避免歧义 ## 函数依赖 ### X → Y - 任意合法关系中，X 值相同的元组，其 Y 值也相同 - 是模式上的语义约束，不能只凭某次数据样本认定 ### 平凡依赖 - Y 是 X 的子集，则 X → Y 恒成立 ### 完全与部分依赖 - 完全依赖：去掉 X 中任何属性后都不能决定 Y - 部分依赖：X 的某个真子集仍能决定 Y ### 传递依赖 - 经中间属性集推导出的依赖 - 规范化关注非主属性通过非超键产生的传递依赖 ## Armstrong 公理 - 自反律：若 Y ⊆ X，则 X → Y - 增广律：若 X → Y，则 XZ → YZ - 传递律：若 X → Y 且 Y → Z，则 X → Z - 合并律：X → Y 且 X → Z，可得 X → YZ - 分解律：X → YZ，可得 X → Y 和 X → Z ## 属性闭包与候选键 - X⁺：在函数依赖集下由 X 能推导出的全部属性 - 求闭包：从 X 出发，反复应用左部已包含的依赖，直到不再增长 - 若 X⁺ 包含全部属性，X 是超键 - 若 X 的任何真子集都不是超键，X 是候选键 - 主属性：出现在至少一个候选键中的属性 ## 范式 ### 1NF - 每个属性值在所采用的数据模型中是原子值 ### 2NF - 满足 1NF - 每个非主属性完全依赖每个候选键，消除部分依赖 ### 3NF - 对每个非平凡依赖 X → A，X 是超键，或 A 是主属性 - 在 2NF 基础上消除非主属性对候选键的传递依赖 ### BCNF - 每个非平凡函数依赖的决定因素都是超键 - 比 3NF 更严格，也约束涉及主属性的依赖 ## 模式分解 ### 无损连接 - 分解后投影结果自然连接，恰好还原原关系 - 避免产生原来不存在的伪元组 - 二元分解 R₁、R₂ 无损的条件：交集能函数决定 R₁ 或 R₂ ### 保持函数依赖 - 各子模式上投影依赖的并集，能推出原依赖集 - 检查约束时无需重新连接全部子表 ### 权衡 - 3NF 综合算法可同时保证无损连接与依赖保持 - BCNF 分解可保证无损连接，但不一定保持全部依赖 - 规范化减少冗余及插入、删除、更新异常

SQL¶

--- markmap: height: 760 initialExpandLevel: 2 autoFit: true colorFreezeLevel: 2 maxWidth: 280 spacingHorizontal: 80 spacingVertical: 12 nodeMinHeight: 24 paddingX: 10 --- # SQL ## 数据定义 DDL - CREATE：创建表、视图、索引等对象 - ALTER：修改对象结构 - DROP：删除对象 - PRIMARY KEY、FOREIGN KEY、UNIQUE、NOT NULL、CHECK：表达约束 ## 普通查询 ### 查询骨架 - SELECT 列 FROM 表 WHERE 条件 - DISTINCT：去除重复结果 - ORDER BY：排序；未指定时不保证结果顺序 ### 条件 - 比较：=、<>、<、>、<=、>= - 组合：AND、OR、NOT - BETWEEN、IN、LIKE、EXISTS - 空值判断使用 IS NULL / IS NOT NULL - SQL 空值参与比较常得到 UNKNOWN，WHERE 仅保留 TRUE ### 连接与子查询 - INNER JOIN：匹配条件的行 - LEFT / RIGHT / FULL OUTER JOIN：保留指定侧或两侧未匹配行 - 相关子查询：引用外层查询的属性 - EXISTS：判断子查询是否返回至少一行 ### 集合操作 - UNION：合并并去重；UNION ALL：保留重复 - INTERSECT、EXCEPT：交集与差集，具体支持依 DBMS ## 分组查询 ### 聚合 - COUNT(*)：统计行数 - COUNT(列)：统计该列非空值的数量 - SUM、AVG、MAX、MIN 通常忽略 NULL ### GROUP BY 与 HAVING - WHERE：分组前筛选行 - GROUP BY：把行按指定属性分组 - HAVING：分组后筛选组，可使用聚合条件 - 示例：SELECT dept, COUNT(*) FROM employee GROUP BY dept HAVING COUNT(*) >= 5 ### 概念处理顺序 - FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY - 逻辑顺序用于理解语义，实际物理执行由优化器决定 ## 数据操作 DML - INSERT INTO：插入 - UPDATE … SET … WHERE：更新 - DELETE FROM … WHERE：删除行 - UPDATE / DELETE 未写 WHERE 通常作用于全部行 ## 权限控制 DCL - GRANT：授予用户或角色权限 - REVOKE：撤销权限 - WITH GRANT OPTION：允许被授权者继续转授权 - 最小权限：按实际任务授予必要访问能力 ## 事务控制 - START TRANSACTION / BEGIN：开始事务，语法依 DBMS - COMMIT：提交 - ROLLBACK：回滚 - SAVEPOINT：设置局部回滚点 ## 视图与索引 - 视图：基于查询定义的逻辑表，可用于限制暴露的数据 - 视图是否可更新取决于定义及 DBMS 规则 - 索引：加快部分访问路径，也增加存储与更新成本

事务、并发与恢复¶

--- markmap: height: 760 initialExpandLevel: 2 autoFit: true colorFreezeLevel: 2 maxWidth: 280 spacingHorizontal: 80 spacingVertical: 12 nodeMinHeight: 24 paddingX: 10 --- # 事务、并发与恢复 ## ACID - 原子性：事务全部完成或全部撤销 - 一致性：在正确事务逻辑及约束保证下，从一个合法状态到另一个 - 隔离性：并发事务之间的可见性和干扰受隔离级别约束 - 持久性：成功提交的修改在故障恢复后仍应保留 ## 并发问题 - 丢失更新：后写覆盖先前更新而未纳入其结果 - 脏读：读取其他事务尚未提交的数据 - 不可重复读：重复读取同一行得到不同值 - 幻读：按相同条件查询时，符合条件的行集合发生变化 ## 共享锁 S 与排他锁 X - S / S 兼容：多个事务可同时读 - S / X、X / X 不兼容 - 读前申请共享锁，写前申请排他锁 - 锁粒度越小，并发性通常越高，管理开销也越大 ## 传统三级封锁协议 - 一级：对要修改的数据加 X 锁并持有到事务结束 - 读后计算再写回时，X 锁须覆盖读取旧值、计算和写回整个更新过程，才能防止丢失更新 - 若先无锁读取旧值，再依次加锁写回，仍可能覆盖其他事务的更新 - 二级：在一级基础上读前加 S 锁，读完即可释放，防止脏读 - 三级：在一级基础上读前加 S 锁并持有到事务结束，防止不可重复读 - 仅对已有记录加锁不能普遍防止幻读，还需范围锁等机制 ## 两阶段封锁 2PL ### 扩展阶段 - 可以申请锁，不能释放锁 ### 收缩阶段 - 可以释放锁，不能再申请新锁 ### 性质 - 遵守 2PL 可保证冲突可串行化 - 可串行化调度不一定都遵守 2PL - 2PL 仍可能产生死锁 - 严格 2PL 将写锁持有到提交或回滚，避免读取未提交修改及级联回滚 ## 死锁 ### 预防 - 统一加锁顺序，或一次申请所需锁 - 基于时间戳的等待 / 回滚策略 ### 检测与解除 - 等待图中检测环 - 选择事务作为牺牲者回滚，释放锁并重试 - 超时是一种工程策略，超时本身不证明存在死锁 ## 隔离级别 - 读未提交：可能发生脏读 - 读已提交：避免脏读，仍可能不可重复读 - 可重复读：避免不可重复读；是否避免幻读取决于实现 - 可串行化：并发结果等价于某个串行执行 - MVCC 利用多版本实现快照读取，具体行为由数据库实现决定 ## 恢复 - 日志记录事务修改与提交状态 - WAL：相关日志先于数据页持久化 - UNDO：撤销未完成事务；REDO：重做需要恢复的已提交修改 - 检查点缩短恢复时需扫描的日志范围 - 备份与日志配合，用于介质故障或指定时点恢复