# 一：数据库架构设计之”这都是啥“（概念篇）🦆
1.1 引言：
我认为我们的设计经历了两个阶段的发展变化
之前是
'''
**talk is cheap ，show me you code**
'''
现在是
'''
**code is cheap， show me you mind**
'''
但是无论时代如何变化，工具又如何发展，我们的一个整体设计思想还是与时俱进慢慢进步的。 这就是为什么我们还要去研读SQL—2008这个经典之作，汲取的是思想迸发的力量。 长篇大论的讨论是枯燥的，于是我想用自己的方法用例子去讲解，希望在备考的同时能够帮助到其他的人。
1.2 需求分析阶段建模
数据建模：
所谓数据建模，面向的是数据这个对象。
而对于数据这个对象，我们有概念模型比如说E-R建模方法来描述数据对象之间的联系与数据之间的关联。
E-R概念模型分为四个主要构成{
1：实体与实体集
实体：客观存在并且能够相互区分的事物
实体集： 例如 实体张三 《-- 实体集 （ 学生 ）
2：属性与域：
属性：用于描述一个【实体集】具有的性质
域：用于描述一个属性可取值
【常见的题型：
规划一个实体集，人员统计名单，属性有姓名，性别。
性别可以用BOOL，域（0-1），满足存储空间效率】
3：联系：
用于将现实世界中实体或实体集之间的关联关系抽象为 1：1 1：n n：m这样的关系
4：码：
用于在实体集中区分一个实体，可以是一个属性，或者是属性组。
【考点：BENF范式：主键不可以被任何决定，甚至是主键的一部分。】
【考点：3NF范式】
}
现在我们得到了一个用于描述数据模型的建模方式，然后我们得到了数据模型。数据模型又可以分为
- 概念模型：比如说E-R建模方法。用于抽象现实的数据关系
- 逻辑模型：具体的数据结构（比如说表格）【考点：表格是对象，而数据不是】
- 物理模型：面向底层，比如说用什么构建索引。B+；hash.......
功能建模
所谓功能建模旨在描述，系统“做什么”，即系统内部的数据处理过程、数据的流动方向以及数据的逻辑处理功能。
换句话说，它关心的是输入数据是如何被加工并最终转化为输出数据的。
通常使用DFD（Data Flow Diagram，数据流图）建模方法
DFD的主要构成{
1：外部实体（External Entity）：
存在于系统之外，是数据的来源或最终去向（例如“用户”、“外部接口”）。
2：处理过程（Process）：
对数据进行变换的操作或逻辑（例如“核算成绩”、“生成报表”）。
3：数据流（Data Flow）：
数据在系统中的传输路径，带有明确的流向。
【考点：数据流必须至少有一端连接到“处理过程”，
不能直接从外部实体流向数据存储，也不能在两个数据存储之间直接流动！】
4：数据存储（Data Store）：
静态存储的数据（例如“学生档案库”）。
这是功能模型和数据模型（E-R图）产生交集的地方，
也是后续建表的依据之一。
【常见的题型：给出一个业务场景，要求找出缺失的数据流，
或者判断DFD图中的错误（如黑洞、奇迹等不合理加工）。】
【考点：父图与子图的平衡原则。
子图的输入/输出数据流必须与父图中对应加工的输入/输出数据流完全一致。】
}；
又有字典（数据字典）：
DFD虽然直观，但缺乏对数据的精确描述。
数据字典（DD）是对DFD中出现的所有数据元素、数据流、数据存储、处理过程的严格文字定义。
它是进行后续数据库逻辑设计的核心参考依据。
行为建模
所谓行为建模旨在描述，系统在运行过程中的“动态特征”，
即系统如何响应各种事件，以及系统状态随时间发生的演变。
如果说数据和功能是系统的骨架与肌肉，那么行为就是系统的生命周期。
常见的方法有UML（统一建模语言）建模方法，
在数据库或软件需求分析中，我们主要关注以下几个维度{
1：状态机图（State Machine Diagram）：
用于描述一个特定实体在其生命周期内经历的各种状态，以及引起状态转换的事件。
【常见的题型：以“电商订单”为例，画出状态图。
订单会经历“未支付”、“已支付”、“已发货”、“已完成”等状态，触发条件就是“用户付款”、“商家发货”等动作。】
【考点：这会直接指导数据库设计中的状态字段（如Status）的枚举值，以及业务代码中允许的数据更新逻辑。】
2：顺序图（Sequence Diagram）：
强调消息传递的时间顺序
描述多个对象（或系统组件）之间是如何按时间先后顺序进行交互来完成一个业务用例的。
3：活动图（Activity Diagram）：
类似于传统的流程图，展示了业务逻辑的执行流向，特别是用于描述并发过程或复杂的分支逻辑。
}；
1.3 来点实例：🦆🦆🦆
我们计划按照 引语；概念；实例；考点。 这四个部分来组织我们的笔记架构设计接下来是一个简单的例子，但是能够涵盖常见的考点。
设计一个成绩分析系统。
从现实规律我们中能挖掘到以下观点
1：成绩有好有坏，一个学生能够考很多科目。如果考不及格，那么要么重修，要么补考，要么挂起。
2：系统需要区别身份，假设存在两个身份，一个是老师，一个是学生，二者权限不同。
3：学生可以查看到自己的成绩，以及能够查看到整个年级的平均成绩；或者某个科目的平均成绩。
#TAGS [数据库需求分析阶段][存储过程对象][事件与触发器对象][ROLE-权限模型][视图对象]
第一阶段：需求分析阶段
概念： 明确系统要处理的数据对象、业务逻辑（功能）以及状态流转（行为）。
实例：
- 数据需求：需要记录学生信息、教师信息、课程信息以及核心的成绩信息。
- 功能需求：教师能录入/修改成绩；学生只能查询个人成绩和聚合数据（班级/科目平均分）。
- 行为需求：成绩具有状态机属性（正常 -> [低于60分] -> 补考 -> [补考不通过] -> 重修/挂起）。
考点：
- 能否准确画出DFD图，区分“教师”和“学生”作为外部实体，数据流向的不同。
- 识别出隐藏的状态字段（成绩表不能只有一个“分数”字段，还需要一个“状态”字段）。
第二阶段：概念结构设计阶段
概念： 脱离具体数据库（如MySQL/Oracle），用E-R图建立抽象的业务模型。
实例：
定义【学生】、【教师】、【课程】三大实体。
- 联系：【学生】与【课程】之间是典型的
n:m（多对多）联系，我们命名为“选修”联系；【教师】与【课程】通常是1:n或n:m的“讲授”联系。
考点：
- E-R图的画法（矩形表实体，椭圆表属性，菱形表联系）。
- 准确判断并标注基数比例（1:1, 1:n, n:m）。
第三阶段：逻辑结构设计阶段
概念： 将E-R图转化为具体的二维表结构，并利用范式（Normal Form）消除数据冗余和更新异常。
实例：
- 【学生表】（学号，姓名，性别）
- 【课程表】（课程号，课程名，学分）
- 针对
n:m的“选修”联系，必须单独拆分成一张表： 【成绩表】（学号，课程号，分数，成绩状态）—— 联合主键为（学号，课程号） 。
考点：
- 转换规则：多对多（m:n）联系如何转化为独立的关系模式？（必须将两端实体的主键作为新表的联合主键）。
- 范式审查：检查【成绩表】是否满足3NF或BCNF，确保“成绩状态”只依赖于（学号，课程号），而不依赖于其他非主属性。
第四阶段：物理结构设计阶段
- 概念： 面向底层存储，设计索引、表空间和存储引擎，以优化查询性能。
实例：
- 因为学生经常查询“某个科目的成绩”，我们在【成绩表】的
课程号上建立非聚簇索引（B+树）。 - 频繁查询的平均分可以考虑建立物化视图或使用Redis等缓存。
考点：
-
聚簇索引（Clustered Index）与非聚簇索引的区别。
感觉这里有必要讲解索引的相关知识。
我们现在大都是用ES去建立全文索引，
或者使用PSQL,ora{ps：没有人觉得这个像一个小人在下跪吗？🦆}等自带的索引工具。
在关系型数据库（如 MySQL、PostgreSQL、Oracle）中，
索引不仅仅是一个概念或规则，它是一段实实在在占据磁盘空间的数据结构。
- 它有自己的元数据（记录在系统表中）。
- 它需要数据库引擎去维护（每次增删改查时都会消耗 CPU 和 I/O 去更新索引树）。
- 在很多数据库中，你可以像创建表一样显式地创建、修改和删除索引（`CREATE INDEX`, `DROP INDEX`）。
### 索引核心知识点详解
#### 1. 按数据结构分类（底层是怎么存的？）
- B+树索引 (B+ Tree) ：
- 最主流的索引结构
- 特点：非叶子节点只存键值不存数据，所有真实数据都在叶子节点，且叶子节点之间用双向链表连接。
- 优势：非常适合范围查询（比如 `成绩 > 80`），找到了起点后，顺着链表往后遍历即可，不需要重新从根节点找。
- 哈希索引 (Hash) ：
- 特点：将键值通过 Hash 算法计算出对应位置。
- 优势：等值查询极快（如 `WHERE id = 10`），时间复杂度接近 O(1)。
- 劣势：不支持范围查询和排序（因为 Hash 计算后是无序的）。
- 位图索引 (Bitmap) ：
- 特点：用一串二进制位（0和1）来表示某个属性的有无。Oracle 支持较好。
- 适用场景：属性域小（低基数）的字段。
- 倒排索引 (Inverted Index) ：
- 就是我们提到的 **ES (Elasticsearch)** 的核心。
它建立的是“词汇到文档 ID”的映射，是做全文搜索的工具。
{我计划后期为大家讲解X的EARLY BIRD索引以及ANN算法尽请期待吧}
#### 2. 按物理存储结构分类（核心考点：数据和索引分不分家？）
- 聚簇索引（Clustered Index / 聚集索引） ：
- 概念：索引的逻辑顺序与磁盘上数据的物理存储顺序完全一致。
- 特点：找到索引就直接找到了数据所在的行（叶子节点直接包含整行数据）。
一个表只能有一个聚簇索引（通常是主键）。
- 非聚簇索引（Non-Clustered Index / 辅助索引 / 二级索引） ：
- 概念：索引的顺序与数据的物理顺序无关。
- 特点：叶子节点不存整行数据，而是存聚簇索引的值（如主键 ID）或数据物理地址的指针。
- 【考点：回表】通过辅助索引查到主键 ID，再去聚簇索引里查出整行数据，这个过程叫“回表”。
#### 3. 按逻辑/功能分类（我们在 SQL 里怎么用？）
- 主键索引 (Primary Key) ：一种特殊的唯一索引，不允许为空（NOT NULL），通常默认成为聚簇索引。
- 唯一索引 (Unique)：索引列的值必须唯一，但允许有空值（NULL）。
- 普通索引 (Normal) ：最基本的索引，没有任何限制，纯粹为了加速查询。
- 联合索引 (Composite Index) ：包含多个列的索引（例如：在 `课程号, 成绩` 上建一个索引）。
- 【考点：最左前缀原则】。查询时必须从索引的最左边列开始匹配，索引才会生效。
* * *
### 为什么“频繁更新的字段”不宜建过多索引？
1. **维护成本高：索引是一棵树（B+树）。当你对表进行 `INSERT`, `UPDATE`, `DELETE` 时，
为了保持树的平衡和有序，数据库引擎必须同时修改这棵索引树（涉及页分裂、页合并等底层操作）。
1. **降低写性能：索引越多，写入数据时需要同步更新的目录就越多，导致写操作变慢。
1. **占用磁盘空间：每个索引都是一个物理对象，建的索引越多，占用的硬盘空间越大。
总结索引建立的原则：
- **适合建索引：经常出现在 `WHERE`、`ORDER BY`、`GROUP BY`、`JOIN` 后的字段。
- **不适合建索引：频繁更新的字段、表数据量极小的表、
区分度极低（域小）的字段（除非用位图索引，否则在B+树中建性别字段的索引毫无意义）。
第五阶段：数据库实施阶段
编写DDL、DML以及高级数据库对象来强制实现业务规则和安全控制。
实例：
- [视图对象] ：学生需要看年级平均分，但不能看别人明细。创建视图
CREATE VIEW View_AvgScore AS SELECT 课程号, AVG(分数) FROM 成绩表 GROUP BY 课程号;。 - [ROLE-权限模型] ：通过RBAC控制权限。创建角色
CREATE ROLE Student_Role;，并授权GRANT SELECT ON View_AvgScore TO Student_Role;，最后将学生用户分配给该角色。
感叹一下RBAC还是太强了【角色扮演什么的太棒了呢（@qwq@）】
在数据库存储控制规则当中
用于描述主体（自动化程序；脚本；用户）对于客体（数据库；数据库对象；数据库系统的）
称之为操作{ps：我也觉得这个很蠢，但是没办法，概念就是概念}
操作有三个主流方式
1：自主存取DAC（个人开发者首选；相当于我们自己宿主机内的环境，我们拥有ROOT权限！）
2：强制存取MAC （ora最喜欢考，对于不同的客体以及主体分级，对于客体给予保密等级，
对于主体给予许可证，只有许可证通过了，客体才能被访问或者修改等）
3：RBAC（最强的选择！）
- [事件与触发器对象] ：实现行为模型中的状态流转。写一个
AFTER INSERT OR UPDATE触发器：当老师录入成绩时，如果NEW.分数 < 60，自动将成绩状态字段更新为“补考”。 - [存储过程对象] ：期末结算时，需要批量计算绩点、判断是否需要重修。封装成存储过程
CALL Proc_Calculate_Semester_GPA(学期号);，减少网络网络传输，提高批量处理效率。
考点：
- 触发器中
NEW和OLD关键字的区别。 - 视图的更新限制（带有聚合函数
AVG的视图是不可更新的）。 GRANT与REVOKE权限语句的语法。
第六阶段：数据库运行与维护阶段
概念： 监控性能，数据备份，随业务演进进行表结构变更。
实例：
- 五年后数据量庞大，将往届毕业生的成绩数据迁移到历史归档库。
- 定期进行全量备份和增量备份，防止由于硬件损坏导致成绩丢失。
考点：
- 数据库管理员（DBA）的日常职责。
- 事务日志（Redo/Undo）在恢复中的作用。
在这里我们讲解一下数据库的备份
在经典的SQL-2008中
系统数据库，master，model，msdb，tempdb，和用户数据库
#### 1. 认识系统数据库
在制定备份策略前，必须了解数据库自身的结构。SQL Server 包含几个关键的系统数据库：
master：(最重要)
记录 SQL Server 实例的所有系统级别信息（如登录账户、系统配置设置、所有其他数据库的位置等）。
如果 master 损坏，整个数据库服务将无法启动。
model：(模板数据库)
它是创建新用户数据库的模板。模板数据库具有用户数据库的基本结构、默认大小和配置。
如果你希望以后创建的每个数据库都自带某些特定的表、视图或权限，只需在 model 库中建好即可。
msdb：(代理与调度库)
主要被 SQL Server Agent 用来计划警报、作业（定时任务）以及记录备份和还原的历史信息。
tempdb：(临时工作区)
存放临时对象或中间结果集（如复杂的 `GROUP BY` 或 `ORDER BY` 产生的临时表）。
注意：每次重启 SQL Server 时，tempdb 都会被重新创建，因此不需要备份tempdb。
#### 2. 三大核心备份类型
合理的备份策略通常是这三者的组合：
全量备份 (Full Backup)：
概念： 备份整个数据库的所有数据以及部分事务日志（足以保证数据一致性）。
特点：恢复最快，但耗时最长，占用存储空间最大。通常在业务低峰期（如周末或深夜）进行。
差异备份 (Differential Backup)：
概念：备份自上一次全量备份以来发生改变的数据。
特点：恢复速度和备份时间介于全量和日志备份之间。
注：很多数据库（如 MySQL）更常提“增量备份（只备份上次备份后改变的数据）”，
而 SQL Server 的经典体系中强调的是“差异备份”。
事务日志备份 (Transaction Log Backup)：
概念：备份自上次日志备份以来的所有事务日志记录。
作用： 它是实现时间点恢复（Point-in-Time Recovery）的关键！
结合全量备份，可以让你把数据库恢复到崩溃前的任意一秒钟（这也是考点中 Redo/Undo 机制的实际应用场景）。
#### 3.接下来才是我们的核心考点REDO/UNDO 与WAL！
概念说明：----WAL---- 预写式日志思想（指导思想，修仙阶的内功心法）
-UNDO LOG-- 回滚日志
-REDO LOG-- 重做日志
3.1. WAL (Write-Ahead Logging) - 预写式日志思想
核心内容：
“先写日志，再写磁盘数据”。任何对底层数据页的修改，在真正刷入磁盘之前。
必须先保证其对应的日志（尤其是 Redo Log）已经安全写入磁盘。
将极其耗时的“数据随机写”替换为极快的“日志顺序写（Sequential I/O）”，
在保障数据安全的同时，最大化了写性能。
3.2 UNDO LOG -回滚日志
核心内容：
逻辑日志。记录数据被修改**前**的状态
（例如：`INSERT` 对应 `DELETE`，`UPDATE` 变成把新值改回旧值的 `UPDATE`）。
-事务回滚：事务执行失败或主动 `ROLLBACK` 时，逆向执行日志，恢复数据。
-MVCC（多版本并发控制）：当一个事务正在修改数据时
其他读事务可以通过 Undo Log 查看到修改前的历史版本，实现“读写不冲突”。
3.3 Redo Log - 重做日志
核心内容:
物理日志.记录数据被修改**后**的物理状态
（例如：“在数据页 A 的偏移量 B 处，把值改成了 C”）。
只要事务提交时 Redo Log 成功落盘，哪怕内存里的脏数据还没来得及刷入物理磁盘，
断电重启后，数据库依然能根据 Redo Log 把数据原样“重做”出来，保证数据绝对不丢。
我们将 WAL、Undo 和 Redo 串联起来，看看当你执行 UPDATE 成绩表 SET 分数=90 WHERE 姓名='张三'（原分数为 60）时，底层发生了什么：
- 加载数据入内存： 数据库去磁盘找到“张三”所在的物理数据页，加载到内存缓冲池（Buffer Pool）中。
- 生成 Undo Log： 在内存中记录一条 Undo Log：“张三原来的分数是 60”。
- 修改内存数据（产生脏页）： 在内存中，将张三的分数从 60 改为 90。此时，内存和磁盘的数据不一致，内存中的这一页变成了脏页（Dirty Page） 。
- 生成 Redo Log： 在内存的日志缓冲区（Log Buffer）中记录一条 Redo Log：“将某数据页的张三分数修改为了 90”。
- 事务提交（触发 WAL 法则）： 用户执行
COMMIT。此时，WAL 强制介入，它要求系统立刻把第 4 步生成的 Redo Log 从内存顺序追加写入磁盘的日志文件中。 - 返回成功： 只要 Redo Log 顺利落盘，数据库就告诉用户：“修改成功！”（注意：此时磁盘里的真实数据文件，依然是 60 分）。
- 后台异步刷脏（最终落盘）： 数据库的后台线程会在空闲时，悄悄把内存里 90 分的“脏页”覆盖到磁盘的真实数据文件上。
graph TD
Start((开始:执行UPDATE)) --> S1
subgraph 内存区域_Memory
S1[1.加载数据入内存: 从磁盘读入Buffer Pool]
S2[2.生成 Undo Log: 记录旧值 60]
S3[3.修改内存数据: 改为新值 90,产生脏页]
S4[4.生成 Redo Log: 记录修改到Log Buffer]
end
subgraph 客户端控制
S5[5.事务提交: 用户执行 COMMIT]
S6[6.返回成功: 告知用户修改成功]
end
subgraph 磁盘区域_Disk
S5_WAL[WAL强制介入: RedoLog顺序写入日志文件]
S7[7.后台异步刷脏: 90分脏页覆盖真实数据文件]
end
%% 主干同步流程
S1 --> S2
S2 --> S3
S3 --> S4
S4 --> S5
S5 -->|触发 WAL 法则| S5_WAL
S5_WAL -->|只要日志成功落盘| S6
S6 --> End((结束))
%% 异步流程
S5_WAL -.->|系统空闲时异步执行| S7
系统断电重启后，数据库如何根据这套体系自救？它会严格执行以下两步：
- 第一阶段：前滚（Redo）—— 宁杀错，不放过 数据库启动时，扫描磁盘上的 Redo Log。不管事务有没有 COMMIT，只要 Redo Log 里有记录，数据库就无脑把这些物理修改全部在内存中重新执行一遍。这保证了已经提交的事务绝对不会丢失。
- 第二阶段：回滚（Undo）—— 拨乱反正 前滚完成后，内存里可能混入了一些断电时还没来得及 COMMIT 的脏数据（因为 Redo 是无脑重做）。此时，数据库扫描 Undo Log，找到所有状态为“未提交”的事务，利用 Undo Log 强行把它们的数据改回原样。这保证了未提交的事务绝对不会生效。
1.4 结束语与挖矿填坑：🦆🦆🦆
接下来一个星期应该会抽出2个小时去做这个笔记吧，可能我的质量不是很好，希望大家能够多多指正 然后我在笔记中说到的去分析一下X的EARLY BIRD索引算法，以及群，ANN会在四月份为大家献上的！
爱你们 beybey明天见@微光
2026/3/32
