系统分析师笔记（二）

编码

模拟信号编码

见（一）:AM 载波幅度调制； FM 频率调制； PM 初始相位调制。

数字信号编码

见（一）：

NRZ
ME 和 DME
- 4B/5B ：用 5bit 二进制编码代表 4bit 二进制数据（额外开销 1bit）
- 8B/10B
- 64B/66B ; 128B/130B ; 128B/132B （额外开销 2bit 同步位）

差错控制（`校验码`）

随机错误 vs. 突发错误
通信过程中出现的差错可大致分为两类：
- 一类是由 热噪声（电子热运动）引起的随机错误，影响个别位
- 一类是由 冲击噪声（电磁干扰）引起的突发错误，影响位串

奇偶校验

随机性错误：在7位的ASCII代码后增加一位，使码字中1的个数成奇数（奇校验）或偶数（偶校验）
突发性错误：校验和，把数据块中的每个字节当作一个二进制整数
- 在发送过程中按模256 相加。数据块发送完后，把得到的和作为校验字节发送出去。
- 接收端在接收过程中进行同样的加法，用所得到的校验和与接收到的校验和比较, 从而发现是否出错。

海明码（Hamming)

用冗余数据位来检测和定位、纠正 单比特错误
下图是错的，领会精神即可

例如，对于6号位，由于6=110（二进制)，所以6号位参加第2位和第4位的奇偶校验，而不参加第1位的奇偶校验。
类似地，9号位参加第1位和第8位的校验，而不参加第2位或第4位的校验。
海明把奇偶校验分配在1、2、4、8等位置上,其他位放置数据。

实际芯片（如内存控制器 ECC纠错）中，上述查表、异或和反转操作全部由组合逻辑电路（XOR门阵列）在纳秒级完成，不占用CPU时间。这正是海明码在服务器、航天计算机中经久不衰的根本原因——用硬件面积换取可靠性。

循环冗余校验码 (Cyclic Redundancy Check,CRC)

容易用硬件实现，移位寄存器 + 异或门XOR
生成多项式
为适配不同场景下各类传输错误模式的检错需求，业界制定了多个标准化 CRC 生成多项式：

补充说明：CRC-32 广泛应用于以太网（局域网 LAN）数据帧校验。

网络体系结构、协议

不同机器上的同等功能层之间采用相同的协议
同一机器上的相邻功能层之间通过接口进行信息传递

OSI 体系结构

开放系统互连参考模型 (Open System Interconnection Reference Model, OSI/RM)
七层：自底向上，物理层-数据链路层-网络层-传输层-会话层-表示层-应用层

TCP/IP 协议簇

TCP/IP协议分为4层，由下至上，分别是网络接口层、网际层、传输层和应用层

网络拓扑结构

网络拓扑结构，主要有星形结构、总线结构、环形结构、网状结构。

（d）网状结构
- 很少用
- 任何节点彼此之间都会由一根物理通信线路相连, 任何节点出现故障都不会影响到其他节点
- 布线比较麻烦，而且网络建设的成本也很高，控制方法也很复杂

网络工程

网络规划

需求分析
可行性分析
对现有系统的分析（利旧）

网络设计

总体目标
设计原则
架构设计（网络拓扑、分层）
资源子网（服务器接入、子网连接）
设备选型
OS选择、服务器资源
- 1. 业务定 OS，OS 定硬件；
- 1. 核心业务做冗余容错，高并发上集群扩容。
网络安全设计

插播 -------------- 逻辑门 -------------------------

第一梯队：非、与非、或非

原则：上拉 PMOS，下拉 NMOS
非：反相器，2 个晶体管，串，A=1, 则 Y=0
与非：4个晶体管，上并下串，A=1, B=1, 则 Y=0
或非：4个晶体管，上串下并，A=0, B=0, 则 Y=1

TG （传输门）

不是BOOL运算，只做开关
核心实现：2个管子，PMOS 和 NMOS 并联；
- 但是，需要控制信号 C_bar ，必须就近实现，就近加一个反相器，获得控制 C 和 C_bar 信号
因此，常看到的 TG 是 4个晶体管

第二梯队：与、或、同或、异或

与：与非（NAND）+ 非（N）= 与（AND），6个晶体管
或：或非 + 非 = 或， 6个晶体管
异或：2个非（4管） + 2个TG（4管），共8个晶体管

编号	电路模块	包含的管子	数量	作用
①	非门（反相器）	1个PMOS + 1个NMOS（串联）	2个	把输入 A 变成 \(\bar{A}\)（反相控制信号）。
②	非门（反相器）	1个PMOS + 1个NMOS（串联）	2个	把输入 B 变成 \(\bar{B}\)（数据反相信号）。
③	传输门 TG1	1个PMOS + 1个NMOS（并联）	2个	由 A 和 \(\bar{A}\) 控制。当 A=0 时导通，让 B 直接通过。
④	传输门 TG2	1个PMOS + 1个NMOS（并联）	2个	由 A 和 \(\bar{A}\) 控制。当 A=1 时导通，让 \(\bar{B}\) 直接通过。
合计			8个

同或：异或 + 非，10个晶体管

数据库

数据库，是长期存储在计算机内的、有组织的、可共享的数据集合
由数据库、数据库管理系统（DataBase Management System，DBMS)、应用系统、数据库管理员（DataBase Administrator，DBA) 和用户构成

范式 NF

范式	核心检查条件
1NF	字段不可拆分（原子值）
2NF	无「非主属性」部分依赖候选键
3NF	无「非主属性」传递依赖候选键
BCNF	每一个决定因素 X 都是候选键
4NF	消除非平凡、非键的多值依赖
5NF	消除全部连接依赖

工程中到3NF就可以了，不满足就要拆表

做题判断范式的固定步骤

1.列出全部属性，找出所有候选键
2.分出主属性、非主属性
3.检查 1NF；不合格 = 1NF 都达不到
4.检查有没有非主属性部分依赖 → 有则最高 1NF；无则≥2NF
5.检查有没有非主属性传递依赖 → 有则最高 2NF；无则≥3NF
6.遍历全部函数依赖，查看左边 (X) 是否全都是候选键；不全则最高 3NF，全部满足即达到 BCNF
7.再排查多值依赖判断 4NF

数据库控制功能设计

1. 安全性方面，采用权限分级和视图隔离，防止越权操作；
1. 完整性方面，添加主外键约束和检查约束（Check），杜绝脏数据；
1. 并发方面，采用行级锁（或乐观锁）控制多用户同时修改账户余额；
1. 恢复方面，开启REDO日志和定期全量备份，确保故障时可恢复至最近状态；
1. 整体通过事务封装，确保账户扣减与日志记录的ACID特性。
- 事务封装：多条孤立操作指令，捆绑到一起

START TRANSACTION; -- 封装的开始

UPDATE Account SET balance = balance - 100 WHERE user = '你';
INSERT INTO TransactionLog (user, amount) VALUES ('你', -100);

COMMIT; -- 封装的结束。只有这两条全部成功，COMMIT 才执行

并发控制 - 锁协议

`并发`带来的问题：

丢失修改（Lost Update）：两个人同时改一个数，后改的把先改的覆盖了（结果少了1次累加）
读脏数据（Dirty Read）：一个事务读到另一个还没提交（但可能会回滚）的数据。万一对方回滚了，你读到的就是“幽灵数据”（结果引用了无效值）
不可重复读（Non-repeatable Read）：同一个事务里，读两次同一个数据，结果不一样（结果前后矛盾）

两种“锁”

X锁（排他锁/写锁）：“独占锁”。一个人拿了X锁，其他人既不能读（不能加S锁），也不能写（不能加X锁）。
S锁（共享锁/读锁）：“共享锁”。可以多人同时加S锁（一起读），但只要有任何一个人加了S锁，其他人不能加X锁（即不能修改）。

协议等级	加锁规则（教材原话翻译）	在案例中怎么表现？	解决了什么？	遗留了什么隐患？
一级封锁协议	`改数据`前加 X 锁，直到事务结束才释放。（读数据完全不加锁）	事务 A 要存钱，给余额加上 X 锁。此时事务 B 想查询，因为没有 S 锁的保护，B 依然可以读（读到旧的 100）。	防止了 “丢失修改”（如果 B 也想改，会被 A 的 X 锁挡住，不会覆盖）。	1. 可能读 “脏数据”（如果 A 改了但回滚，B 读到了 200）。2. 不可重复读（B 两次读，中间 A 改了并提交，结果变 200）。
二级封锁协议	一级 + 读数据前加 S 锁，`读完马上释放`。	事务 B 想查余额，先加 S 锁，读完 100 后立刻把 S 锁释放掉。此时 A 的 X 锁才能加上去改余额。	防止了 “读脏数据”（因为 B 加了 S 锁时，A 不能加 X 锁修改，所以 B 读到的只能是已提交的干净数据）。	依然 “不可重复读”（B 读完后释放了 S 锁，A 立刻改成了 200 并提交。B 再读时，发现变成了 200）。
三级封锁协议	一级 + 读数据前加 S 锁，直到`事务结束`才释放。	事务 B 想查余额，加 S 锁，直到 B 的事务彻底结束才释放这个 S 锁。在这期间，A 无法加 X 锁修改余额。	同时防止了 “脏数据” 和 “不可重复读”（B 在整个事务期间，锁住了数据，A 改不了）。	并发度大幅降低（如果 B 是一个耗时很长的报表查询，所有人都无法修改这个账户）。

两段锁协议（2PL，Two-Phase Locking）—— 专门对付`并发死锁`

教材的(1)(2)(3)解决的是“数据读写的正确性”，而(4)两段锁协议解决的是“多个锁加在一起会不会死锁”的问题。

所有事务必须分两个阶段走：
- 阶段一（扩张阶段/加锁期）：只能加锁，不能解锁。哪怕你卡住了，也得憋着，把所有的锁都申请完。
- 阶段二（收缩阶段/解锁期）：一旦你开始释放第一个锁，就再也不能申请新锁了。
  - 一次申请完所有的锁，再做释放操作，不要在申请锁中间做释放锁操作
实战例子（避免死锁）：
- 遵守两段锁：
  事务A先申请X锁（锁住账户1），再申请X锁（锁住账户2），全部拿到手之后，再开始释放解锁。保证了加锁和解锁不交叉。
- 不遵守两段锁（引发死锁）：
  事务A锁了账户1，想锁账户2；事务B锁了账户2，想锁账户1。如果A在还没拿到账户2的锁之前，就先释放了账户1的锁，这就违反了“解锁后不能再申请”的规则，容易产生循环等待。

数据库的完整性

1.完整性约束条件

数据库里的所有规矩，最终都可以翻译成类似于 IF (条件) THEN (必须满足) ELSE (报错) 的数学表达式
教材说“以具有真假的原子公式和连接词组成”。这句话是数学老师的说法，我翻译成程序员的说法：
- 原子公式：就是“最小的、能判断对错的数学比较”。
  - 比如：工资 > 5000（真/假），城市 = ‘北京’（真/假）。这些单个的表达式就是原子公式。
- 命题连接词：就是你编程时用的 AND（并且）、OR（或者）、NOT（否则/取反）。
  - 比如：工资 > 5000 AND 城市 = ‘北京’。

白话总结：数据库就是用 (比较1) AND/OR/NOT (比较2) 这种形式，来拼凑出极其复杂的业务规矩。

2.实体完整性

实体完整性要求主键中的任一属性不能为空
- 例如，对于学生关系 S(Sno, Sname，Ssex),其主键为 Sno,在插入某个元组时，就必须要求Sno不能为空。更加严格的DBMS还要求Sno不能与己经存在的某个元组的Sno相同。

3.参照完整性

R 中的外键只能对 S 中的主键引用，不能是 S 中主键没有的值。
例如，对于学生关系 S(Sno，Sname，Ssex)和选课关系 C(Sno, Cno, Grade)两个关系， C中的Sno是外键，它是S的主键，若C中出现了某个S中没有的Sno,即某个学生还没有注册，却己有了选课记录，这显然是不合理的。

4.用户定义完整性

五元组：D O A C P

元组	官方全称	一句话大白话（考试秒懂）	具体含义
D	Data（数据对象）	“管谁？”	约束作用于数据库中的哪个对象（通常是某张表的某一列）。
O	Operation（触发操作）	“啥时候管？”	执行什么数据库操作时会触发这个检查（如 INSERT, UPDATE, DELETE）。
A	Assertion（断言 / 约束条件）	“管成什么样？”	数据必须满足的数学逻辑（即具体的规则公式，如年龄 > 18）。
C	Condition（选择谓词）	“管哪几行？”	这是一个筛选条件（WHERE）。它决定约束是针对全表，还是只针对满足特定条件的行。
P	Procedure（违规处理过程）	“不听话咋办？”	当违反约束时，系统自动执行的补救措施（如拒绝执行 (ROLLBACK)、级联删除或置为 NULL）。

实例1
学校规定：“高三（3）班的学生，成绩不能低于 60 分。如果考试发现低于60分，直接不允许录入（拒绝插入）。”
- D：Student 表的 Score 列。（数据对象）
- O：INSERT 和 UPDATE。（只有在新增或修改成绩时触发）
- A（断言）：Score >= 60。（这是最核心的“质量要求”）
- C（条件）：Class_Name = '高三(3)班'。（这是范围限定！其它班级不受此规则约束）
- P：REJECT（拒绝操作，报错）
实例2
规则：年龄大于 18 岁且户籍在北京的员工，工资不得低于 5000。
- D: Employees 表的 salary 列
- O: INSERT 和 UPDATE（只要改变工资就查）
- C: age > 18 AND city = '北京'（只抓这批人）
- A: salary >= 5000（必须满足的合法条件）
- P: REJECT（不满足就拒绝）

5.触发器

在完整性约束功能中，当系统检查出数据中有违反完整性约束条件时，仅给出必要提示以通知用户。
而触发器的功能则不仅起到提示作用，还会引起系统自动进行某些操作，以消除违反完整性约束条件所引起的负面影响。

数据库的安全性

技术上依赖于两种方式：

DBMS本身提供的用户身份识别、视图、使用权限控制和审计等管理措施，大型DBMS均有此功能；
靠应用程序来实现对数据库访问进行控制和管理，数据的安全控制由应用程序里面的代码来实现。

1. 用户标识和鉴别（最外层的门卫）

官方说明：最外层的安全保护措施，可以使用用户账户、口令和随机数检验等方式。

大白话理解：数据库要先确认你是谁，再决定放不放你进门。光有账号密码还不够（容易被盗），现代系统会加上多因素认证。

真实工程例子：

静态密码：登录 MySQL 时输入的 -u root -p 账号密码登录方式。
动态验证：银行系统除密码外，额外要求手机短信验证码、指纹识别（随机数挑战-应答机制）。

软考考点：选项中出现「视网膜扫描」「U盾数字证书」「多因素认证」，均属于用户标识和鉴别范畴。

2. 存取控制（数据授权）

官方说明：对用户进行授权，包括操作类型（查找、更新、删除等）和数据对象的权限。

大白话理解：门卫放你进来了，但不代表可以操作所有数据。用户分为只读访客、普通操作员、超级管理员，各司其职，权限严格区分。

真实工程例子：

SQL 权限管控：GRANT SELECT ON employees TO userA;（用户A仅可查询员工表，无修改、删除权限）。
行级权限：销售经理仅可查询自己管辖区域的客户数据，通过 WHERE 区域=华南 行级安全策略实现数据隔离。

软考考点：核心考察 DAC（自主存取控制） 和 MAC（强制存取控制）

DAC自主控制：如 GRANT 授权，用户可将自身权限转让给他人，灵活性高。
MAC强制控制：系统预设机密/绝密等级，权限由系统强制规定，用户无权更改、转让。

3. 密码存储和传输加密

官方说明：对远程终端信息用密码传输，保障数据传输与存储安全。

大白话理解：远程连接数据库、数据网络传输过程中，易被黑客抓包窃听，因此需要对传输通道、数据库存储的敏感数据双重加密。

真实工程例子：

传输加密：MySQL 开启 SSL/TLS 加密连接，SQL语句、账号密码传输时均为密文，截获后无法读取明文。
存储加密：数据库不存储明文密码，仅存储加盐哈希值（bcrypt加密），即使数据泄露也无法破解原始密码。

软考考点：题干提问「防止数据传输窃听、保障远程访问安全」，答案优选 链路加密、SSL/TLS加密。

4. 视图的保护

官方说明：通过视图的方式进行精细化授权，隔离敏感数据。

大白话理解：视图是虚拟数据表，不存储真实数据。不直接开放原始数据表，通过视图隐藏敏感字段，仅开放用户可查看的字段，实现列级数据隔离。

真实工程例子：

-- 创建视图，隐藏工资等敏感列，仅暴露公开信息
CREATE VIEW v_employee_public AS 
SELECT id, name, department FROM employees;  

-- 为人事用户授权，仅可访问视图，无原始表操作权限
GRANT SELECT ON v_employee_public TO hr_user;

软考考点：核心陷阱——视图不存储数据，仅为虚拟查询窗口。题干提问「限制用户仅查看数据表特定列、屏蔽敏感字段」，答案为视图保护。

5. 审计

官方说明：使用专用文件或数据库，自动记录用户对数据库的所有操作，留存操作日志。

大白话理解：数据库安全日志，完整记录「谁、何时、哪个IP、执行了什么增删改查操作」，支持事后追溯、追责。

真实工程例子：

Oracle 审计功能：AUDIT UPDATE ON employees BY hr_user;，监控人事用户对员工表的修改操作，自动记入审计日志。
MySQL 日志体系：binlog二进制日志记录所有写操作，general_log记录全部查询操作，用于安全溯源。

软考考点：题干提问「事后追查非法操作、安全合规取证」，答案为审计机制。区别：审计是安全专用精细化记录，优先级高于普通系统日志。

💡 数据库安全防御「洋葱模型」（由外到内）

用户标识和鉴别  ← 最外层：身份验证（你是谁？能否准入）
      ↓
存取控制（授权）  ← 中上层：权限管控（你准入后能干什么）
      ↓
视图保护        ← 中层：数据过滤（你能看到哪些数据）
      ↓
加密传输/存储    ← 底层：数据防护（传输、存储数据不泄露）
      ↓
审计            ← 最内层：事后追溯（违规操作可取证追责）

考试万能话术
为确保数据库安全性，系统应采用多级防护策略：

入口处实施用户身份鉴别，拦截非法访问；

访问控制层结合自主存取控制与强制存取控制实现权限分级管理；

针对敏感数据，通过视图机制完成列级、行级数据隔离；

数据传输与存储阶段启用加密协议防止窃听与泄露；

全程开启审计功能，实现操作全程可追溯，满足安全合规与事后追责需求。

备份与恢复技术

1.物理备份
- 热备份
- 冷备份
2.逻辑备份
- 利用DBMS自带的工具软件备份和恢复数据库的内容
- 例如，Oracle的导出工具 exp，导入工具为 imp，可以按照表、表空间、用户和全库4个层次备份和恢复数据
3.日志文件
- 为了安全,先写日志文件
  - 首先将修改记录写到日志文件上，然后再写数据库的修改
- 每个记录包括的主要内容有
  - 执行操作的事务标识
  - 操作类型
  - 更新前数据的旧值（对插入操作而言此项为空值）
  - 更新后的新值（对删除操作而言此项为空值）

UPDATE 事务实例

基于 MySQL 8.0 / MariaDB 的 InnoDB 存储引擎源码，以下是上述 8 个核心步骤的具体实现代码及关键逻辑解析。

1. 事务启动：`trx_start_if_not_started`

源码位置：storage/innobase/trx/trx0trx.cc

// 简化的调用链路
trx_start_if_not_started(trx, true) 
  -> trx_start_low(trx, read_write)

trx_start_low 的核心逻辑：

void trx_start_low(trx_t *trx, bool read_write) {
    // 如果事务已经启动，直接返回
    if (trx->state != TRX_STATE_NOT_STARTED) return;

    // 读写事务才分配真正的事务ID
    if (read_write) {
        // 分配事务ID (从全局计数器获取)
        trx->id = trx_sys_get_new_trx_id();
        // 分配回滚段 (用于undo log)
        trx_assign_rseg(trx);
        // 将事务加入全局事务列表
        UT_LIST_ADD_FIRST(trx_sys->trx_list, trx);
    }
    
    // 设置事务状态为 ACTIVE
    trx->state = TRX_STATE_ACTIVE;
    trx->start_time = ut_time();
    
    // 只读事务创建Read View
    if (!read_write) {
        trx_sys->mvcc->view_open(trx->read_view, trx);
    }
}

关键点：BEGIN 或 START TRANSACTION 时，只读事务不分配事务ID，也不分配回滚段；真正的读写事务在执行第一条 UPDATE 或 INSERT 时才分配 ID。

2. 数据定位：`ha_innobase::index_read`

源码位置：storage/innobase/handler/ha_innodb.cc

ha_innobase 是 MySQL Server 层与 InnoDB 存储引擎之间的接口类（Handler）。

int ha_innobase::index_read(uchar *buf, const uchar *key, uint key_len,
                            ha_rkey_function find_flag) {
    // 获取预构建的查询结构体
    prebuilt_t* prebuilt = m_prebuilt;
    
    // 如果是全表扫描，则定位到第一个索引页
    if (find_flag == HA_READ_AFTER_KEY) {
        // 通过B+树索引定位
        row_search_mvcc(buf, PAGE_CUR_G, prebuilt, ...);
    } else {
        // 通过索引键值定位
        row_search_mvcc(buf, PAGE_CUR_GE, prebuilt, ...);
    }
    
    // 返回0表示成功，否则返回错误码
    return error;
}

row_search_mvcc 是真正的 B+ 树索引遍历入口，它在 row/row0sel.cc 中实现。

3. 加锁：`lock_rec_lock`

源码位置：storage/innobase/lock/lock0lock.cc

dberr_t lock_rec_lock(lock_mode_t mode, buf_block_t* block, ulint heap_no,
                      trx_t* trx, ...) {
    // 1. 快速路径：尝试无等待加锁
    err = lock_rec_lock_fast(mode, block, heap_no, trx);
    
    if (err == DB_SUCCESS) return err;
    
    // 2. 慢速路径：需要检查兼容性
    err = lock_rec_lock_slow(mode, block, heap_no, trx, ...);
    
    return err;
}

lock_rec_lock_fast 的逻辑：

检查目标记录所在页的锁位图（bitmap）
如果该行还没有任何锁，直接设置锁位并返回

lock_rec_lock_slow 的逻辑：

调用 lock_rec_has_expl 检查该行是否已有相同或更强的锁
通过兼容性矩阵判断当前锁请求是否与现有锁冲突
若冲突，将事务加入等待队列；若不冲突，授予锁

行锁在内存中用 lock_rec_t 结构体表示，通过位图（bitmap） 记录哪些行被锁住。

4. 写入 Undo Log：`trx_undo_report_row_operation`

源码位置：storage/innobase/trx/trx0rec.cc

dberr_t trx_undo_report_row_operation(ulint flags, ulint op_type,
                                      que_thr_t* thr, dict_index_t* index,
                                      const dtuple_t* entry, ...) {
    // 1. 判断操作类型 (INSERT / UPDATE / DELETE)
    // 2. 分配回滚段 (临时表用临时回滚段，普通表用普通回滚段)
    // 3. 写入undo日志到undo page
    if (op_type == TRX_UNDO_INSERT_REC) {
        // INSERT操作：只记录主键信息
        trx_undo_page_report_insert(...);
    } else {
        // UPDATE/DELETE操作：记录完整旧值，包含所有索引列
        trx_undo_page_report_modify(...);
    }
    
    // 4. 返回回滚指针 (roll_ptr)，供后续挂在DB_ROLL_PTR隐藏列上
    return DB_SUCCESS;
}

类型区别：

Insert Undo Log：只记录主键，事务提交后立即删除（对其他事务不可见）
Update Undo Log：记录完整旧版本，事务提交后不能立即删除，需等待 Purge 线程清理（用于 MVCC）

5. 修改数据：`row_upd_clust_step`

源码位置：storage/innobase/row/row0upd.cc

void row_upd_clust_step(upd_node_t* node, que_thr_t* thr) {
    // 1. 获取待更新的聚簇索引记录 (old_rec)
    rec_t* old_rec = node->old_rec;
    dict_index_t* index = node->index;
    mtr_t mtr;  // Mini-Transaction
    
    mtr_start(&mtr);
    
    // 2. 判断是否更新了主键
    if (row_upd_changes_primary_key(node->update)) {
        // 主键更新 = 删除旧记录 + 插入新记录
        row_upd_clust_rec_by_insert(node, index, &mtr);
    } else {
        // 非主键更新 = 原地修改 (In-Place Update)
        // 分配新记录空间，复制未修改列，应用更新
        btr_cur_upd_lock_and_alloc(...);
        
        // 关键：更新三个隐藏列
        rec_set_trx_id(new_rec, trx->id);         // 当前事务ID
        rec_set_roll_ptr(new_rec, roll_ptr);      // 指向Undo Log的指针
        
        // 旧版本通过 roll_ptr 形成版本链
    }
    
    // 3. Mini-Transaction提交 (生成Redo Log)
    mtr_commit(&mtr);  // 
}

隐藏列版本链：

新记录 (name='b', trx_id=A, roll_ptr→Undo)
              ↑
Undo记录 (name='a', trx_id=老, roll_ptr→NULL)

6. 生成 Redo Log：`mtr_commit`

源码位置：storage/innobase/mtr/mtr0mtr.cc

void mtr_t::commit() {
    // 1. 如果mtr没有修改任何数据，直接释放资源
    if (m_impl->m_log.m_size == 0) {
        release_all();
        return;
    }
    
    // 2. 将mtr私有日志拷贝到全局Redo Log Buffer
    log_buffer_write(m_impl->m_log);
    
    // 3. 将修改的脏页加入Flush List
    //    供后续Page Cleaner线程刷盘
    for_each_block_in_reverse([](buf_block_t* block) {
        buf_flush_note_modification(block, ...);
    });
    
    // 4. 释放所有锁定的页 (Page Latch)
    release_all();
}

mtr_commit 是一个宏定义：

#define mtr_commit(m) (m)->commit()

Mini-Transaction 的原子性保证：一个 mtr 内的所有修改要么全部成功（Redo Log 全部写入），要么全部失败。

7. 两阶段提交：`ha_commit_trans`

源码位置：sql/handler.cc

int ha_commit_trans(THD* thd, bool all, bool ignore_global_read_lock) {
    // 1. Phase 1: Prepare (所有引擎执行prepare)
    for (each involved storage engine) {
        // 调用InnoDB的prepare: innobase_xa_prepare
        // 将事务状态设置为 TRX_STATE_PREPARED
        // Redo Log刷新到磁盘 (参数innodb_flush_log_at_trx_commit控制)
        error = ht->prepare(ht, thd, all);
    }
    
    // 2. Write Binlog
    // 将事务的变更事件写入binlog文件 (mysql-bin.xxxxxx)
    tc_log->log_xid(thd, xid);  // MYSQL_BIN_LOG对象
    
    // 3. Phase 2: Commit
    for (each involved storage engine) {
        // 调用InnoDB的commit: innobase_commit
        // 更新Undo段状态: INSERT可释放, UPDATE/DELETE待Purge
        // 释放行锁 (X锁)
        // 通知事务提交成功
        error = ht->commit(ht, thd, all);
    }
}

关键点：ha_commit_trans 是两阶段提交的协调器，它确保 Binlog（Server层）和 Redo Log（InnoDB层）的一致性。整个过程由 TC_LOG 类协调。

8. 后台刷脏：`page_cleaner_thread`

源码位置：storage/innobabe/buf/buf0flu.cc

void page_cleaner_thread() {
    // 1. 初始化page_cleaner结构
    //    - 协调线程 (coordinator) 也参与刷脏
    //    - 多个工作线程 (workers) 并行刷脏
    
    while (srv_shutdown_state == SRV_SHUTDOWN_NONE) {
        // 2. 计算需要刷新的脏页数量
        //    根据: Redo Log空间 / 检查点 / Buffer Pool空闲比例
        
        // 3. 分配刷新任务给各工作线程
        for (each buffer pool) {
            assign_slot_for_flush(buf_pool);
        }
        
        // 4. 执行刷新: 将脏页写入磁盘数据文件(.ibd)
        buf_flush_page_cleaner(...);
        
        // 5. 更新检查点 (Checkpoint)
        //    使得对应的Redo Log可以被覆盖复用
        
        // 6. 休眠等待下一次触发
        os_event_wait(pc_event);
    }
}

page_cleaner_t 结构管理所有刷新任务的状态，包括 n_slots_requested（待刷新的槽位数）和 n_slots_flushing（正在刷新的槽位数）。

触发条件：

Redo Log 空间不足
Buffer Pool 脏页比例过高
检查点（Checkpoint）触发

📌 代码位置速查表

步骤	函数	源码文件
事务启动	`trx_start_if_not_started`	`trx/trx0trx.cc`
数据定位	`ha_innobase::index_read`	`handler/ha_innodb.cc`
加锁	`lock_rec_lock`	`lock/lock0lock.cc`
写入Undo Log	`trx_undo_report_row_operation`	`trx/trx0rec.cc`
修改数据	`row_upd_clust_step`	`row/row0upd.cc`
生成Redo Log	`mtr_commit`	`mtr/mtr0mtr.cc`
两阶段提交	`ha_commit_trans`	`sql/handler.cc`
后台刷脏	`page_cleaner_thread`	`buf/buf0flu.cc`

B+ 树

待补充 ... ...

数据库优化

在数据库优化中，SQL 语句怎么写，比怎么建索引、怎么调参数更重要。它是“最后一关”，也是最容易见效的一关。

0. 为什么说“查询优化是最后一个环节，同时也是最重要的一个环节”？

这句话里有两个层次的意思，你需要同时理解才能答对题：

层次一：为什么是“最后一个环节”？

数据库性能优化的顺序是固定的，不能上来就改 SQL。正确的优化顺序是：

硬件/OS层：CPU、内存、磁盘 I/O、网络。
架构层：读写分离、分库分表、引入缓存（Redis）。
数据库配置层：innodb_buffer_pool_size、max_connections 等参数调优。
表结构层：字段类型选择（你之前问的 SMALLINT vs INT）、索引设计。
SQL 查询层：重写低效的查询语句。

如果你的硬件没达标、索引没建对，光改 SQL 是没用的，所以查询优化要放在最后做。

层次二：为什么说它“最重要”？

因为前四层优化大多是一次性投入，改完后收益固定；而 SQL 优化是持续发生的，每条 SQL 都可能拖慢系统，且改一条 SQL 的投入产出比远高于加一块 SSD 硬盘或买一台新机器。这是 DBA 日常工作里见效最快、最立竿见影的环节。

1. 建立物化视图或尽可能减少多表查询

教材原意：减少表与表之间的连接（JOIN）次数，尤其是复杂的多表关联。

工程实践：

物化视图（Materialized View）：把复杂的多表 JOIN 结果提前算好存成一张物理表，然后定期刷新。适用于统计报表、BI 看板等实时性要求不高的场景。
减少多表查询：不是让你不 JOIN，而是让你控制 JOIN 的数量和顺序。比如，能三张表 JOIN 完成的，不要连五张；能用小表驱动大表的，不要让大表做驱动表。

为什么有效：
多表 JOIN 的本质是多层嵌套循环（Nested Loop），数据量一大就会把数据库拖垮。你把结果提前算好存成一张表，读的时候就变成单表查询，复杂度从 O(n*m) 降到 O(log n)。

2. 以不相干子查询替代相干子查询

教材原意：把“逐行执行”的子查询改成“一次性计算”的子查询。

工程实践：

相干子查询（Correlated Subquery）：子查询的 WHERE 条件里引用了外层查询的列，导致外层每扫描一行，子查询就要重新执行一次。这是典型的性能杀手。

-- 相干子查询（性能差）
SELECT * FROM orders o 
WHERE o.price > (SELECT AVG(price) FROM orders WHERE customer_id = o.customer_id);

外层有多少行，内层 AVG 就重新算多少遍。

不相干子查询（Non‑correlated Subquery）：子查询完全独立，只执行一次，然后外层直接拿来用。

-- 不相干子查询（性能好）
SELECT * FROM orders 
WHERE customer_id IN (SELECT id FROM customers WHERE status = 'VIP');

为什么重要：
相干子查询的时间复杂度是 外行数 × 内行数，数据量一大就会呈指数级增长。这是 SQL 优化里最容易被忽略但危害最大的坑。

3. 只检索需要的属性

教材原意：不要用 SELECT *，只取你真正需要的列。

工程实践：

SELECT * 会把不需要的字段也拉回内存，增加网络传输和内存占用。
如果表上有覆盖索引（Covering Index），只查索引包含的字段可以直接走索引返回结果，不用回表查数据行（即“索引覆盖”），性能提升是数量级的。

反面例子：

-- 坏写法：拉回全行数据，字段越多越浪费
SELECT * FROM users WHERE age > 18;

-- 好写法：只取需要展示的列
SELECT id, name FROM users WHERE age > 18;

4. 用带 IN 的条件子句等价替换 OR 子句

教材原意：IN (...) 比 OR 更容易走索引，性能更好。

工程实践：

-- 坏写法：多个 OR，优化器容易判断错
SELECT * FROM orders WHERE status = 'PENDING' OR status = 'PROCESSING' OR status = 'SHIPPED';

-- 好写法：IN 走索引
SELECT * FROM orders WHERE status IN ('PENDING', 'PROCESSING', 'SHIPPED');

原理：
在 MySQL 中，IN 列表会被优化器视为多个等值条件的集合，倾向于使用索引；而多个 OR 有时会被优化器误判为需要全表扫描。当然，现代版本的优化器已经能在某些场景下自动转换，但 IN 的写法依然是工程上的最佳实践。

5. 经常提交（COMMIT），以尽早释放锁等

教材原意：不要让事务拖得太长，及时提交，释放行锁和资源。

工程实践：

-- 坏写法：事务里做大量循环操作，迟迟不提交
START TRANSACTION;
FOR i IN 1..10000 LOOP
    UPDATE inventory SET stock = stock - 1 WHERE id = i;
END LOOP;
COMMIT;  -- 事务持有锁直到最后，大量资源被占用

-- 好写法：分批提交
FOR i IN 1..10000 LOOP
    START TRANSACTION;
    UPDATE inventory SET stock = stock - 1 WHERE id = i;
    COMMIT;  -- 每行提交一次，尽早释放锁
END LOOP;

但需要注意：频繁提交也会带来额外的 I/O 和网络开销（每个 COMMIT 都要触发一次 Redo Log 刷盘和 Binlog 写入），所以工程上通常采用批量提交（比如每 1000 行提交一次），在“锁持有时间”和“提交开销”之间取得平衡。

💡 一句话总结（应对考试的选择题避坑指南）

教材策略	工程上的本质	考试陷阱
减少多表查询	用小表驱动大表，避免笛卡尔积	不要理解成“不用 JOIN”，而是要“减少不必要的 JOIN”
不相干子查询替代相干子查询	把子查询提到外部，只执行一次	相干子查询不是“错误写法”，而是“性能差”
只检索需要的属性	走覆盖索引，减少回表和网络传输	`SELECT *` 不一定是灾难，但一定是不推荐的工程习惯
`IN` 替换 `OR`	让优化器更容易走索引	不是所有 `OR` 都能被 `IN` 等价替换，要看具体条件
经常 COMMIT	尽早释放行锁和 Redo Log 空间	不是“越频繁越好”，而是在锁等待和提交开销之间取平衡点

1.规划 -> 2.需求分析 -> 3.概念设计(ER图)

略

4.逻辑设计

用图书管理系统的例子，把教材里的这段描述一句句落地，让你看到从“ER图”到“MySQL建表语句”的具体操作过程。

一、教材原文的三层含义拆解

“逻辑设计的任务是将概念模型转化为某个特定的 DBMS 上的逻辑模型。”

这句话包含三个操作步骤：

选定一个逻辑模型（关系模型、网状模型、层次模型）。在现代工程中，几乎全部选择关系模型，因为它是关系数据库（如 MySQL、PostgreSQL、Oracle）的理论基础。
将概念模型转化为该逻辑模型。把之前画的实体、属性、联系，转换成具体的表、列、主键和外键。
优化逻辑模型。通常通过规范化（Normalization）来消除数据冗余和更新异常。

二、具体操作：从 ER 图到 MySQL 建表语句

我们沿用上一轮图书管理系统的全局 ER 图。以下是图中的四个实体及它们之间的关系：

读者（读者编号、姓名、身份证号、联系电话、注册日期、会员等级）
图书（图书编号、书名、作者、出版社、出版日期）
馆藏副本（副本编号、书架位置、入库日期、状态）
罚款记录（罚款编号、金额、是否已缴）

实体间的联系：

读者 ↔ 馆藏副本 → 借阅关系（多对多）
读者 → 罚款记录 → 一对多

第一步：实体转表

概念模型中的每个实体都转换为一张关系表。实体的属性转换为表的列，主键直接迁移。

概念模型（ER 图）	逻辑模型（关系表）
读者实体	`reader` 表
图书实体	`book` 表
馆藏副本实体	`copy` 表
罚款记录实体	`fine` 表

第二步：确定主键

每个实体的唯一标识符（主键）直接成为表的主键。

读者：reader_id
图书：book_id
馆藏副本：copy_id
罚款记录：fine_id

第三步：处理联系（这是核心操作）

概念模型中的“联系”需要根据其基数类型，转换成关系模型中的外键或连接表。

（1）一对多联系（读者 → 罚款记录）

概念模型：一个读者可以有多条罚款记录，但一条罚款记录只属于一个读者。

转换规则：在“多”方表中添加“一”方的主键作为外键。

-- 在 fine 表中添加 reader_id，指向 reader 表
CREATE TABLE fine (
    fine_id INT PRIMARY KEY AUTO_INCREMENT,
    reader_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    is_paid BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (reader_id) REFERENCES reader(reader_id)
);

（2）多对多联系（读者 ↔ 馆藏副本 → 借阅）

概念模型：一个读者可以借阅多本馆藏副本，一本馆藏副本也可以被多个读者先后借阅（借阅关系本身还携带了“借书日期”“应还日期”“实际归还日期”等属性）。

转换规则：不能把外键放在任意一方（否则会产生大量冗余）。正确的做法是创建一个独立的连接表来承载这个关系及其附属属性。

-- 连接表 borrow_record
CREATE TABLE borrow_record (
    borrow_id INT PRIMARY KEY AUTO_INCREMENT,
    reader_id INT NOT NULL,
    copy_id INT NOT NULL,
    borrow_date DATE NOT NULL,
    due_date DATE NOT NULL,
    actual_return_date DATE,
    FOREIGN KEY (reader_id) REFERENCES reader(reader_id),
    FOREIGN KEY (copy_id) REFERENCES copy(copy_id)
);

这张连接表在概念设计中对应的是“借阅关系”这一联系，它自身带有属性（借书日期、应还日期、实际归还日期），因此在逻辑设计阶段被映射为一张独立的物理表。

第四步：属性映射

概念模型中的属性直接映射为表的列，但需要根据 DBMS 的具体要求选择合适的数据类型。

概念模型属性	映射为 MySQL 数据类型	说明
读者编号	`INT`	自增整数，作为主键
姓名	`VARCHAR(50)`	可变长度字符串
身份证号	`VARCHAR(18)`	18位中国身份证号
联系电话	`VARCHAR(20)`	可能存在空格或分机号
注册日期	`DATE`	只需年月日，不需要时分秒
会员等级	`ENUM('gold','silver','normal')`	固定选项，使用 ENUM 更紧凑
借书日期	`DATETIME`	需要精确到时分秒（便于精确计算超时）
金额	`DECIMAL(10, 2)`	精确小数，避免浮点数误差
是否已缴	`BOOLEAN`	存储 TRUE/FALSE

第五步：最终 MySQL 建表语句

经过以上步骤，我们得到以下四张逻辑表：

-- 读者表
CREATE TABLE reader (
    reader_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    id_card VARCHAR(18) UNIQUE NOT NULL,
    phone VARCHAR(20),
    register_date DATE NOT NULL,
    membership ENUM('gold', 'silver', 'normal') DEFAULT 'normal'
);

-- 图书表
CREATE TABLE book (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100) NOT NULL,
    publisher VARCHAR(100),
    publish_date DATE
);

-- 馆藏副本表
CREATE TABLE copy (
    copy_id INT PRIMARY KEY AUTO_INCREMENT,
    book_id INT NOT NULL,
    shelf_location VARCHAR(50),
    entry_date DATE NOT NULL,
    status ENUM('available', 'borrowed', 'lost') DEFAULT 'available',
    FOREIGN KEY (book_id) REFERENCES book(book_id)
);

-- 罚款记录表
CREATE TABLE fine (
    fine_id INT PRIMARY KEY AUTO_INCREMENT,
    reader_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    is_paid BOOLEAN DEFAULT FALSE,
    issued_date DATETIME NOT NULL,
    FOREIGN KEY (reader_id) REFERENCES reader(reader_id)
);

-- 借阅记录表（连接表）
CREATE TABLE borrow_record (
    borrow_id INT PRIMARY KEY AUTO_INCREMENT,
    reader_id INT NOT NULL,
    copy_id INT NOT NULL,
    borrow_date DATETIME NOT NULL,
    due_date DATE NOT NULL,
    actual_return_date DATE,
    FOREIGN KEY (reader_id) REFERENCES reader(reader_id),
    FOREIGN KEY (copy_id) REFERENCES copy(copy_id)
);

三、回答你可能马上会问的问题

问题1：概念模型和逻辑模型最本质的区别是什么？

概念模型：从业务视角描述数据，使用实体、属性和联系的语言，不涉及任何数据库产品。
逻辑模型：从数据库技术视角描述数据，使用表、列、主键、外键的语言，且必须符合特定 DBMS（如 MySQL、Oracle）的语法规则。

问题2：教材里提到“对逻辑模型进行优化”，这通常怎么做？

最常见的优化手段是规范化。例如，如果一开始把读者姓名和借阅记录放在同一张表里，就会产生大量数据冗余，后续还可能引发更新异常。通过规范化，将表拆分为更合理的结构，使其满足第三范式（3NF）甚至更高范式的要求。

问题3：“关系模型”为什么是现在默认的选择？

关系模型的核心优势是数据独立性。在层次模型或网状模型时代，修改数据结构的成本极高。关系模型提供了简单统一的数据表示方式（一张张表）和强大的查询能力（SQL），极大地降低了数据管理的复杂度，因此成为了工业界的事实标准。

好的，我们继续用图书管理系统的例子，把这段教材描述逐句拆解。

一、物理设计在整个数据库设计流程中的位置

先回顾完整流程，以便理解物理设计的上下文：

需求分析 → 概念设计（ER图） → 逻辑设计（关系表） → 物理设计（存储结构） → 数据库实施 → 运行维护

物理设计是“设计阶段”的最后一环。在概念设计阶段，我们确定了系统需要存储哪些数据以及它们之间的关系；在逻辑设计阶段，我们确定了表结构和字段类型；而物理设计阶段要回答的问题是：“这些表和数据在磁盘上到底怎么存？”

二、教材原文的五步拆解

第（1）步：设计存储记录结构

“包括记录的组成、数据项的类型和长度，以及逻辑记录到存储记录的映射。”

这句话在说什么？

在逻辑设计阶段，我们只定义了表结构，但没有规定物理存储细节。物理设计阶段需要决定记录在磁盘上的具体存放方式。

工程实践：

以 MySQL / InnoDB 为例，我们为图书管理系统创建一张表：

CREATE TABLE reader (
    reader_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    id_card VARCHAR(18) UNIQUE NOT NULL,
    phone VARCHAR(20),
    register_date DATE NOT NULL,
    membership ENUM('gold', 'silver', 'normal') DEFAULT 'normal'
);

逻辑设计只关注字段名称和类型，物理设计则需要决定：

记录的组成：每条记录是否按字段顺序紧密排列，还是允许某些字段单独存放？
- InnoDB 的行格式（Row Format） 可以选择 COMPACT、DYNAMIC 或 COMPRESSED。COMPACT 格式会把定长字段紧挨着存放，DYNAMIC 格式则允许变长字段溢出到单独的页中。
数据项的类型和长度：虽然逻辑设计已经指定了 VARCHAR(50)，但在物理层面需要知道：
- VARCHAR(50) 在实际存储时是占用 50 个字符的固定空间，还是只占用实际数据长度的空间？
- 在 InnoDB 的 ROW_FORMAT=DYNAMIC 模式下，VARCHAR 字段只占用实际数据的存储空间加 1~2 字节的长度前缀。
逻辑记录到存储记录的映射：这张 reader 表在磁盘上位于哪个表空间？数据文件具体是哪个？
- MySQL 中，表默认存储在 ibd 文件中（如 reader.ibd）。逻辑上的表 reader 与物理文件 reader.ibd 之间的映射由 InnoDB 引擎自动维护。

第（2）步：确定数据存储安排

“确定数据存储安排。”

这句话在说什么？

决定数据以何种方式分布在物理存储介质上。

工程实践：

表和索引的分离存储：数据和索引是否需要分开存放？
- InnoDB 中，数据是按主键聚集存储的。索引是独立的 B+ 树结构，叶子节点存储主键值而非完整数据行。
表空间管理：是使用共享表空间还是独立表空间？
- 独立表空间（innodb_file_per_table=ON）：每张表一个独立的 .ibd 文件，便于回收空间。
- 共享表空间：所有表共用一个 ibdata 文件，管理方便但无法回收碎片空间。
分区策略：对于大表，是否采用分区？
- 例如，borrow_record 表如果数据量极大，可以按年份分区：PARTITION BY RANGE (YEAR(borrow_date))，将不同年份的数据存放在不同物理分区中。

第（3）步：设计访问方法

“为存储在物理设备上的数据提供存储和检索的能力。”

这句话在说什么？

访问方法就是索引。物理设计阶段需要决定为哪些字段建立索引、使用什么类型的索引。

工程实践：

-- 在 borrow_record 表上设计索引
CREATE INDEX idx_borrow_date ON borrow_record(borrow_date);
CREATE UNIQUE INDEX idx_reader_copy ON borrow_record(reader_id, copy_id);

物理设计中需要考虑的索引问题：

索引类型：使用 B+ 树索引还是哈希索引？
- InnoDB 默认使用 B+ 树索引，支持范围查询。MEMORY 引擎才使用哈希索引。
索引覆盖：某个查询能否只走索引而不回表？
- 如果查询只涉及 reader_id 和 copy_id 两列，可以直接走联合索引，无需回表读取完整数据行。
索引选择性：某一列是否适合建索引？
- status 只有 available、borrowed、lost 三种取值，选择性差，不适宜单独建索引。相反，id_card 取值几乎唯一，适合建唯一索引。
访问路径设计：
- 系统中最常用的查询是“按读者编号查找所有借阅记录”还是“按日期范围统计借阅数量”？针对不同的查询模式，设计不同的索引结构以优化访问速度。

第（4）步：完整性和安全性的分析与设计

“进行完整性和安全性的分析与设计。”

这句话在说什么？

在物理层面落实数据约束和权限控制。

完整性设计（保证数据正确）：

-- 域完整性：字段的取值范围约束
ALTER TABLE reader ADD CONSTRAINT chk_age CHECK (age BETWEEN 0 AND 150);

-- 实体完整性：主键和唯一约束已在逻辑设计阶段完成
-- 参照完整性：外键约束
ALTER TABLE borrow_record 
ADD CONSTRAINT fk_borrow_reader FOREIGN KEY (reader_id) REFERENCES reader(reader_id)
ON DELETE RESTRICT;  -- 如果读者有未还书，不允许删除该读者

安全性设计（控制谁有权访问什么数据）：

-- 创建不同角色
CREATE USER 'librarian'@'localhost' IDENTIFIED BY 'xxx';
CREATE USER 'member'@'localhost' IDENTIFIED BY 'yyy';

-- 授予不同权限
GRANT SELECT, INSERT, UPDATE ON library.borrow_record TO 'librarian'@'localhost';
GRANT SELECT ON library.reader TO 'member'@'localhost';  -- 会员只能查询自己的信息

第（5）步：数据库程序设计

“数据库程序设计。”

这句话在说什么？

在完成表结构、索引和权限设计后，编写与数据库交互的程序。

工程实践：

这一步主要包括：

存储过程：将某些复杂逻辑封装在数据库端，减少网络往返次数。

触发器：在数据变更时自动执行某些操作。

-- 当借阅记录插入时，自动更新馆藏副本的状态为已借出
CREATE TRIGGER trg_borrow_update_status 
AFTER INSERT ON borrow_record
FOR EACH ROW
BEGIN
    UPDATE copy SET status = 'borrowed' WHERE copy_id = NEW.copy_id;
END;

应用程序中的 DAO（数据访问对象）代码：Java/Python/Go 等语言中编写的数据库访问层代码。

注意：这一步虽然在教材中被列为物理设计的一部分，但在现代分层架构中，它通常被视为应用程序开发的一部分，而非纯粹的数据库设计工作。考试时按教材原文作答即可。

三、一张表总结物理设计的五项任务

教材步骤	一句话说明	在图书管理系统中的具体体现
（1）设计存储记录结构	决定数据在磁盘上的组织形式	选择 InnoDB 的行格式（`COMPACT`/`DYNAMIC`），确定字段存储方式
（2）确定数据存储安排	决定数据文件的分布策略	开启独立表空间，按年份对借阅记录表进行分区
（3）设计访问方法	设计索引和查询路径	为 `borrow_date` 和 `(reader_id, copy_id)` 建立 B+ 树索引
（4）完整性与安全性设计	确保数据正确且访问受控	添加 CHECK 约束、外键约束，分配不同用户的查询权限
（5）数据库程序设计	编写数据库端业务逻辑	编写触发器和存储过程，实现自动更新状态等功能

四、物理设计的核心权衡（系统分析师视角）

在考试或实际项目中，物理设计始终在以下三个目标之间做权衡：

查询性能：索引越多，查询越快。
存储空间：索引越多，占用的磁盘空间越大。
维护成本：索引越多，插入、更新、删除操作的代价越高。

物理设计的本质是“基于对业务查询模式的预判，在上述三角关系中寻找最优平衡点”。这也是为什么索引策略、表空间规划、分区策略都需要在理解业务特性的前提下进行设计，而不能脱离业务场景随意选择。

数据库分类

2. 数据库四大主流分类（标准工程划分）

类型	代表产品	核心数据结构	一句话定义（面试标准话术）	典型应用场景
① 关系型数据库	MySQL, PostgreSQL, Oracle	二维表（行+列）	用表格来存储数据，通过SQL查询，强ACID（原子性、一致性、隔离性、持久性）	事务支持，数据高度结构化。
② Key-Value（键值对）	Redis, Memcached, Etcd	哈希表（Hash）	一个Key（键）唯一对应一个Value（值），读写速度极快，通常常驻内存。	缓存登录态（Session）、分布式锁、计数器。
③ 文档数据库	MongoDB, Elasticsearch	JSON 文档	存储半结构化的 JSON 或 BSON 文档，Schema（模式）灵活，字段可以随时增删。	商品详情页（字段多变）、日志存储、内容管理系统（CMS）。
④ 列式数据库	HBase, ClickHouse, Cassandra	按列族存储	数据按列（而非行）聚集存储，压缩比极高，适合对海量数据进行聚合分析（SUM/AVG）。	数据仓库、用户行为分析、物联网时序数据。（这是大数据岗位必问考点）
⑤ 图数据库	Neo4j, NebulaGraph	节点（点） + 边（关系）	存储实体和实体间的关系，遍历多度关系（如“朋友的朋友”）性能极高。	社交好友推荐、反欺诈资金链路追踪、知识图谱。

数据库主要分为 SQL 关系型和 NoSQL 非关系型两大类。关系型以 MySQL 为代表，重事务；NoSQL 按数据结构又可细分为 Key-Value 型（如 Redis）、文档型（如 MongoDB）、列存型（如 ClickHouse）和图型（如 Neo4j）。列存型在大数据聚合分析、图型在社交关系推荐上都有不可替代的作用。

posted on 2026-08-02 15:02 中年二班阅读(12) 评论(0) 收藏举报

刷新页面返回顶部

系统分析师笔记（二）

编码

模拟信号编码

数字信号编码

差错控制（校验码）

奇偶校验

海明码（Hamming)

循环冗余校验码 (Cyclic Redundancy Check,CRC)

网络体系结构、协议

OSI 体系结构

TCP/IP 协议簇

网络拓扑结构

网络工程

网络规划

网络设计

插播 -------------- 逻辑门 -------------------------

第一梯队： 非、与非、或非

TG （传输门）

第二梯队：与、或、同或、异或

数据库

范式 NF

做题判断范式的固定步骤

数据库控制功能设计

并发控制 - 锁协议

并发带来的问题：

两种“锁”

两段锁协议（2PL，Two-Phase Locking）—— 专门对付并发死锁

数据库的完整性

1.完整性约束条件

2.实体完整性

3.参照完整性

4.用户定义完整性

5.触发器

数据库的安全性

1. 用户标识和鉴别（最外层的门卫）

2. 存取控制（数据授权）

3. 密码存储和传输加密

4. 视图的保护

5. 审计

💡 数据库安全防御「洋葱模型」（由外到内）

备份 与 恢复 技术

UPDATE 事务 实例

1. 事务启动：trx_start_if_not_started

2. 数据定位：ha_innobase::index_read

3. 加锁：lock_rec_lock

4. 写入 Undo Log：trx_undo_report_row_operation

5. 修改数据：row_upd_clust_step

6. 生成 Redo Log：mtr_commit

7. 两阶段提交：ha_commit_trans

8. 后台刷脏：page_cleaner_thread

📌 代码位置速查表

B+ 树

数据库优化

0. 为什么说“查询优化是最后一个环节，同时也是最重要的一个环节”？

层次一：为什么是“最后一个环节”？

层次二：为什么说它“最重要”？

1. 建立物化视图或尽可能减少多表查询

2. 以不相干子查询替代相干子查询

3. 只检索需要的属性

4. 用带 IN 的条件子句等价替换 OR 子句

5. 经常提交（COMMIT），以尽早释放锁等

💡 一句话总结（应对考试的选择题避坑指南）

1.规划 -> 2.需求分析 -> 3.概念设计(ER图)

4.逻辑设计

一、教材原文的三层含义拆解

二、具体操作：从 ER 图到 MySQL 建表语句

第一步：实体转表

第二步：确定主键

第三步：处理联系（这是核心操作）

（1）一对多联系（读者 → 罚款记录）

（2）多对多联系（读者 ↔ 馆藏副本 → 借阅）

第四步：属性映射

第五步：最终 MySQL 建表语句

三、回答你可能马上会问的问题

问题1：概念模型和逻辑模型最本质的区别是什么？

问题2：教材里提到“对逻辑模型进行优化”，这通常怎么做？

问题3：“关系模型”为什么是现在默认的选择？

一、物理设计在整个数据库设计流程中的位置

二、教材原文的五步拆解

第（1）步：设计存储记录结构

差错控制（`校验码`）

第一梯队：非、与非、或非

`并发`带来的问题：

两段锁协议（2PL，Two-Phase Locking）—— 专门对付`并发死锁`

备份与恢复技术

UPDATE 事务实例

1. 事务启动：`trx_start_if_not_started`

2. 数据定位：`ha_innobase::index_read`

3. 加锁：`lock_rec_lock`

4. 写入 Undo Log：`trx_undo_report_row_operation`

5. 修改数据：`row_upd_clust_step`

6. 生成 Redo Log：`mtr_commit`

7. 两阶段提交：`ha_commit_trans`

8. 后台刷脏：`page_cleaner_thread`