35岁宝妈备考系统架构师（第三篇）
本章考情：一般占4-6分，重点考察关系代数、范式理论、事务并发控制
阅读时间：12-15分钟 | 难度：⭐⭐⭐⭐ | 技术深度：进阶
前言
数据库技术是系统架构师考试的核心考点之一,也是实际系统设计的重要基础。作为10年Java程序员,你可能已经熟练使用SQL,但软考中的"关系代数"、"范式理论"、"事务并发控制"等理论知识点,可能还需要系统梳理。
今天我用最生活化的语言,带你快速掌握这些"硬核"知识点!
💡 技术原理解码:为什么架构师要深入理解数据库?
从"会用"到"理解本质"
程序员的日常:
sql
SELECT * FROM users WHERE age > 18 AND status = 'active'
架构师的思考:
plaintext
这条SQL的执行计划是什么?
索引是否生效?
是否会全表扫描?
数据量多大时需要分库分表?
如何保证数据一致性?
核心区别:
- 程序员:关注"怎么用"
- 架构师:关注"为什么这么设计"
数据库在系统架构中的位置
plaintext
┌─────────────────────────────────────┐
│ 应用层 (Application) │
├─────────────────────────────────────┤
│ 业务逻辑层 (Business Logic) │
├─────────────────────────────────────┤
│ 数据访问层 (Data Access) │
├─────────────────────────────────────┤
│ 数据库层 (Database) │ ← 架构师的重点
│ - 数据模型设计 │
│ - 索引优化 │
│ - 事务管理 │
│ - 并发控制 │
│ - 分库分表 │
└─────────────────────────────────────┘
理解了数据库原理,你就能:
- 设计合理的数据模型
- 优化SQL性能
- 解决高并发问题
- 保证数据一致性
- 设计可扩展的系统架构
一、数据库基本概念
1.1 用"图书馆"理解数据库系统
DB（数据库）= 图书馆的书架
- 存储数据的地方
- 有组织的数据集合
DBMS（数据库管理系统）= 图书馆管理员
- 管理数据的软件
- MySQL、Oracle、SQL Server、PostgreSQL
DBS（数据库系统）= 整个图书馆
- DB + DBMS + 用户 + 应用程序
DBA（数据库管理员）= 图书馆长
- 负责数据库的设计、维护、优化
代码示例:
python
# 程序员视角
import mysql.connector
conn = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydb"
)
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
# 架构师视角思考:
# 1. 连接池是否合理配置?
# 2. SQL是否有索引?
# 3. 是否会产生锁等待?
# 4. 数据量达到多少需要分库分表?
# 5. 如何保证高可用?
1.2 数据模型的"进化论"
1. 层次模型 = 家族树
python
# 伪代码表示
class TreeNode:
def __init__(self, data):
self.data = data
self.children = []
# 层次模型结构
root = TreeNode("爷爷")
child1 = TreeNode("爸爸")
child2 = TreeNode("叔叔")
grandchild1 = TreeNode("我")
grandchild2 = TreeNode("弟弟")
grandchild3 = TreeNode("堂弟")
root.children = [child1, child2]
child1.children = [grandchild1, grandchild2]
child2.children = [grandchild3]
# 特点:一对多,有明确的父子关系
# 缺点:查询复杂,不够灵活
2. 网状模型 = 社交网络
python
# 伪代码表示
class Node:
def __init__(self, data):
self.data = data
self.neighbors = [] # 可以连接多个节点
# 网状模型结构
node_a = Node("A")
node_b = Node("B")
node_c = Node("C")
node_d = Node("D")
node_a.neighbors = [node_b, node_c, node_d]
node_b.neighbors = [node_a, node_c, node_d]
node_c.neighbors = [node_a, node_b, node_d]
# 特点:多对多,关系复杂
# 缺点:结构复杂,难以维护
3. 关系模型 = Excel表格 ⭐⭐⭐⭐⭐
sql
-- 关系模型:学生表
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
dept VARCHAR(50)
);
-- 插入数据
INSERT INTO students VALUES (1, '张三', 20, '计算机系');
INSERT INTO students VALUES (2, '李四', 21, '数学系');
-- 查询数据
SELECT * FROM students WHERE age > 18;
# 特点:简单、灵活、易维护
# 优点:SQL语言、理论基础完善
1.3 关系代数 vs SQL:本质映射
关系代数是SQL的"底层逻辑"
sql
-- SQL语句
SELECT name, age
FROM students
WHERE age > 18
AND dept = '计算机';
-- 对应的关系代数
πname,age(σage>18 ∧ dept='计算机'(students))
-- 执行步骤:
-- 1. σage>18 ∧ dept='计算机'(students): 选择操作
-- 从学生表中筛选年龄>18且系名为'计算机'的记录
--
-- 2. πname,age(...): 投影操作
-- 从结果中只取出name和age两列
就像:
plaintext
你用中文说"我要吃饭"
↑
底层逻辑:咀嚼 → 吞咽 → 消化 → 吸收
理解了关系代数,就能:
- 理解SQL的执行原理
- 优化SQL查询
- 编写更高效的代码
二、E-R模型:用"相亲"理解实体关系
2.1 E-R模型的三要素
1. 实体（Entity）= 相亲对象
plaintext
学生、课程、教师
2. 属性（Attribute）= 个人信息
plaintext
学生:学号、姓名、年龄、系名
课程:课程号、课程名、学分
教师:教师号、姓名、职称
3. 关系（Relationship）= 相亲关系
2.2 三种基本关系(代码实现)
1. 1:1关系 = 一夫一妻制
sql
-- 方式1:两个表都保存对方的主键
CREATE TABLE husbands (
husband_id INT PRIMARY KEY,
husband_name VARCHAR(50),
wife_id INT UNIQUE,
FOREIGN KEY (wife_id) REFERENCES wives(wife_id)
);
CREATE TABLE wives (
wife_id INT PRIMARY KEY,
wife_name VARCHAR(50),
husband_id INT UNIQUE,
FOREIGN KEY (husband_id) REFERENCES husbands(husband_id)
);
-- 方式2:合并成一个表
CREATE TABLE couples (
couple_id INT PRIMARY KEY,
husband_name VARCHAR(50),
wife_name VARCHAR(50)
);
2. 1:N关系 = 一个老师教多个学生
sql
-- 教师表
CREATE TABLE teachers (
teacher_id INT PRIMARY KEY,
teacher_name VARCHAR(50)
);
-- 学生表(保存教师ID)
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
teacher_id INT,
FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);
-- 查询某个教师的学生
SELECT s.* FROM students s
WHERE s.teacher_id = 1;
# 实现方式:在"N"端表保存"1"端的主键
3. M:N关系 = 学生选修课程
sql
-- 学生表
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50)
);
-- 课程表
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(50)
);
-- 选课表(中间表)
CREATE TABLE student_courses (
id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
-- 查询某个学生选的所有课程
SELECT c.* FROM courses c
JOIN student_courses sc ON c.course_id = sc.course_id
WHERE sc.student_id = 1;
# 实现方式:需要创建中间表(关联表)
2.3 E-R图设计原则
设计步骤:
- 确定实体（表）
- 确定属性（字段）
- 确定关系（1:1、1:N、M:N）
- 绘制E-R图
- 转换为关系模型
示例:学生-课程-教师系统
plaintext
实体:学生、课程、教师
属性:
- 学生:学号、姓名、年龄、系名
- 课程:课程号、课程名、学分
- 教师:教师号、姓名、职称
关系:
- 学生-课程:M:N(学生选修课程)
- 教师-课程:M:N(教师讲授课程)
E-R图:
学生 教师
┌──────┐ ┌──────┐
│ │ │ │
│ │ │ │
└──────┘ └──────┘
│ │
M:N M:N
│ │
└────→ 课程 ←────┘
三、关系代数:用"家庭关系"理解
3.1 关系代数概述
定义: 关系代数是关系数据库的数学基础,是SQL语言的底层逻辑
核心思想: 用数学运算对关系进行操作
3.2 五种基本运算
1. 选择（Selection）σ
定义: 从关系中选出满足条件的元组
生活类比: 从全家福中"挑选"穿红衣服的人
sql
-- 关系代数: σage>18(students)
-- SQL实现
SELECT * FROM students WHERE age > 18;
-- 代码实现(Python)
def select(students, condition):
return [student for student in students if condition(student)]
students = [
{'id': 1, 'name': '张三', 'age': 20},
{'id': 2, 'name': '李四', 'age': 15},
{'id': 3, 'name': '王五', 'age': 22}
]
result = select(students, lambda s: s['age'] > 18)
# 结果: [{'id': 1, 'name': '张三', 'age': 20}, {'id': 3, 'name': '王五', 'age': 22}]
# 记忆口诀:σ(西格玛)=选择=从全家福中"挑人"
2. 投影（Projection）π
定义: 从关系中选出指定的属性
生活类比: 给全家福"拍照",只拍头部(忽略身体)
sql
-- 关系代数: πname,age(students)
-- SQL实现
SELECT name, age FROM students;
-- 代码实现(Python)
def project(students, attributes):
return [{attr: student[attr] for attr in attributes} for student in students]
students = [ {'id': 1, 'name': '张三', 'age': 20}, {'id': 2, 'name': '李四', 'age': 15}]
result = project(students, ['name', 'age'])
# 结果: [{'name': '张三', 'age': 20}, {'name': '李四', 'age': 15}]
# 记忆口诀:π(派)=投影=只拍"头部"(指定列)
3. 并（Union）∪
定义: 两个关系的合并
生活类比: 两个水果篮"合并"成一个
sql
-- 关系代数: S1 ∪ S2
-- SQL实现
SELECT * FROM S1
UNION
SELECT * FROM S2;
-- 代码实现(Python)
def union(set1, set2):
return list(set(set1) | set(set2))
S1 = ['苹果', '香蕉']
S2 = ['橘子', '葡萄']
result = union(S1, S2)
# 结果: ['苹果', '香蕉', '橘子', '葡萄'](自动去重)
# 注意:自动去重(就像水果不会重复放)
4. 差（Difference）-
定义: 第一个关系减去第二个关系
生活类比: S1篮子去掉S2篮子"有"的水果
sql
-- 关系代数: S1 - S2
-- SQL实现
SELECT * FROM S1
EXCEPT
SELECT * FROM S2;
-- 代码实现(Python)
def difference(set1, set2):
return [item for item in set1 if item not in set2]
S1 = ['苹果', '香蕉', '橘子']
S2 = ['橘子', '葡萄']
result = difference(S1, S2)
# 结果: ['苹果', '香蕉']
# 注意:顺序很重要!(S1 - S2 ≠ S2 - S1)
5. 笛卡尔积（Cartesian Product）×
定义: 两个关系的所有可能组合
生活类比: 水果和饮料的"所有搭配"
sql
-- 关系代数: R × S
-- SQL实现
SELECT * FROM R, S;
-- 或者
SELECT * FROM R CROSS JOIN S;
-- 代码实现(Python)
def cartesian_product(set1, set2):
return [(a, b) for a in set1 for b in set2]
fruits = ['苹果', '香蕉']
drinks = ['可乐', '雪碧']
result = cartesian_product(fruits, drinks)
# 结果: [('苹果', '可乐'), ('苹果', '雪碧'), ('香蕉', '可乐'), ('香蕉', '雪碧')]
# 注意:结果数量 = S1数量 × S2数量
3.3 复合运算
1. 交（Intersection）∩
sql
-- 关系代数: R ∩ S
-- SQL实现
SELECT * FROM R
INTERSECT
SELECT * FROM S;
-- 等价于: R - (R - S)
2. 连接（Join）⨝
sql
-- 等值连接
SELECT * FROM R INNER JOIN S ON R.id = S.id;
-- 自然连接(自动连接相同属性名的列)
SELECT * FROM R NATURAL JOIN S;
-- 左外连接(保留左表所有记录)
SELECT * FROM R LEFT JOIN S ON R.id = S.id;
-- 右外连接(保留右表所有记录)
SELECT * FROM R RIGHT JOIN S ON R.id = S.id;
-- 全外连接(保留两表所有记录)
SELECT * FROM R FULL OUTER JOIN S ON R.id = S.id;
# 区别:
# - 等值连接:保留重复列(包办婚姻)
# - 自然连接:去掉重复列(自由恋爱)
3. 除（Division）÷
sql
-- 找出"选修了所有课程"的学生
-- 方法1:使用NOT EXISTS
SELECT s.* FROM students s
WHERE NOT EXISTS (
SELECT c.* FROM courses c
WHERE NOT EXISTS (
SELECT sc.* FROM student_courses sc
WHERE sc.student_id = s.id
AND sc.course_id = c.id
)
);
-- 方法2:使用COUNT
SELECT s.*
FROM students s
WHERE (
SELECT COUNT(DISTINCT sc.course_id)
FROM student_courses sc
WHERE sc.student_id = s.id
) = (
SELECT COUNT(*) FROM courses
);
# 记忆口诀:除法="全部匹配"(就像你要求"吃完全部菜"的人)
3.4 关系代数表达式示例
示例:查询选修了"数据库"课程的学生的姓名
sql
-- SQL实现
SELECT s.name
FROM students s
INNER JOIN student_courses sc ON s.id = sc.student_id
INNER JOIN courses c ON sc.course_id = c.id
WHERE c.name = '数据库';
-- 关系代数表达式
πname(σc.name='数据库'(students ⨝ student_courses ⨝ courses))
-- 执行步骤:
-- 1. students ⨝ student_courses ⨝ courses:三表连接
-- 2. σc.name='数据库'(...):筛选"数据库"课程
-- 3. πname(...):投影学生姓名
-- 优化后的SQL(先筛选再连接)
SELECT s.name
FROM students s
INNER JOIN (
SELECT sc.student_id
FROM student_courses sc
INNER JOIN courses c ON sc.course_id = c.id
WHERE c.name = '数据库'
) sc ON s.id = sc.student_id;
# 理解了关系代数,就能写出更高效的SQL!
四、关系数据库设计理论
4.1 函数依赖
定义: 在关系R中,如果属性X的值能唯一确定属性Y的值,则称X函数决定Y,记作X→Y
生活类比:
plaintext
学生表(学号, 姓名, 年龄, 系名, 系主任)
学号 → 姓名 (一个学号对应一个姓名)
学号 → 年龄 (一个学号对应一个年龄)
学号 → 系名 (一个学号对应一个系名)
系名 → 系主任 (一个系名对应一个系主任)
传递依赖:学号 → 系名 → 系主任
代码示例:
python
# 函数依赖验证
def check_functional_dependency(data, X, Y):
"""
检查X是否能函数决定Y
data:数据列表
X:决定因素(字段名)
Y:被决定因素(字段名)
"""
dependency_map = {}
for record in data:
x_value = record[X]
y_value = record[Y]
if x_value in dependency_map:
if dependency_map[x_value] != y_value:
return False # X不能函数决定Y
else:
dependency_map[x_value] = y_value
return True # X能函数决定Y
students = [
{'学号': '001', '姓名': '张三', '系名': '计算机系'},
{'学号': '002', '姓名': '李四', '系名': '数学系'},
{'学号': '003', '姓名': '王五', '系名': '计算机系'}
]
print(check_functional_dependency(students, '学号', '姓名')) # True
print(check_functional_dependency(students, '学号', '系名')) # True
print(check_functional_dependency(students, '系名', '姓名')) # False
函数依赖的类型:
1. 平凡依赖:
plaintext
X → Y,Y是X的子集
例如:(学号, 姓名) → 姓名(平凡)
2. 非平凡依赖:
plaintext
X → Y,Y不是X的子集
例如:学号 → 姓名(非平凡)
3. 完全依赖:
plaintext
X → Y,Y完全依赖于X
例如:(学号, 课程号) → 成绩(完全依赖)
4. 部分依赖:
plaintext
X → Y,Y只依赖于X的一部分
例如:(学号, 课程号) → 姓名(部分依赖,姓名只依赖学号)
5. 传递依赖:
plaintext
X → Y,Y → Z,则X → Z
例如:学号 → 系名 → 系主任(传递依赖)
4.2 候选键与超键
超键（Super Key）:
- 能唯一标识元组的属性集合
- 可能包含多余属性
候选键（Candidate Key）:
- 最小的超键
- 不包含多余属性
主键（Primary Key）:
- 从候选键中选择一个作为主键
代码示例:
python
def find_candidate_keys(data):
"""
找出候选键
"""
attributes = list(data[0].keys())
candidate_keys = []
# 检查所有属性组合
from itertools import combinations
for i in range(1, len(attributes) + 1):
for combo in combinations(attributes, i):
if is_unique(data, list(combo)):
# 检查是否是最小的
is_minimal = True
for j in range(1, len(combo)):
for sub_combo in combinations(combo, j):
if is_unique(data, list(sub_combo)):
is_minimal = False
break
if not is_minimal:
break
if is_minimal:
candidate_keys.append(list(combo))
return candidate_keys
def is_unique(data, attributes):
"""
检查属性组合是否能唯一标识元组
"""
values = set()
for record in data:
value = tuple(record[attr] for attr in attributes)
if value in values:
return False
values.add(value)
return True
students = [
{'学号': '001', '身份证号': '123', '姓名': '张三'},
{'学号': '002', '身份证号': '456', '姓名': '李四'}
]
candidate_keys = find_candidate_keys(students)
print(f"候选键: {candidate_keys}")
# 输出: 候选键: [['学号'], ['身份证号']]
# 主键:学号(选择学号作为主键)
4.3 范式理论(代码实现)
1NF（第一范式）:属性不可分
sql
-- 违反1NF的例子
CREATE TABLE bad_students (
id INT PRIMARY KEY,
name VARCHAR(50),
phones VARCHAR(100) -- 多个电话号码用逗号分隔
);
-- 插入数据
INSERT INTO bad_students VALUES (1, '张三', '123,456');
-- 符合1NF的例子
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE student_phones (
id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT,
phone VARCHAR(20),
FOREIGN KEY (student_id) REFERENCES students(id)
);
-- 插入数据
INSERT INTO students VALUES (1, '张三');
INSERT INTO student_phones (student_id, phone) VALUES (1, '123');
INSERT INTO student_phones (student_id, phone) VALUES (1, '456');
# 记忆口诀:1NF=原子性=一个人一个房间
2NF（第二范式）:消除部分依赖
sql
-- 违反2NF的例子
CREATE TABLE bad_scores (
student_id INT,
course_id INT,
score INT,
dept_name VARCHAR(50), -- 系名只依赖学号,不依赖课程号
PRIMARY KEY (student_id, course_id)
);
-- 符合2NF的例子
CREATE TABLE scores (
student_id INT,
course_id INT,
score INT,
PRIMARY KEY (student_id, course_id)
);
CREATE TABLE students (
student_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);
# 记忆口诀:2NF=完全依赖=孩子完全依赖父母
3NF（第三范式）:消除传递依赖
sql
-- 违反3NF的例子
CREATE TABLE bad_students (
student_id INT PRIMARY KEY,
name VARCHAR(50),
dept_name VARCHAR(50),
dept_head VARCHAR(50) -- 系主任通过系名传递依赖学号
);
-- 符合3NF的例子
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50),
dept_head VARCHAR(50)
);
# 记忆口诀:3NF=消除传递依赖=孙子不直接依赖爷爷
BCNF（Boyce-Codd范式）
sql
-- 违反BCNF的例子
CREATE TABLE bad_student_teacher_course (
student VARCHAR(50),
teacher VARCHAR(50),
course VARCHAR(50),
PRIMARY KEY (student, teacher)
);
-- 函数依赖:
-- (学生, 教师) → 课程
-- 教师 → 课程(教师是决定因素,但不是候选键)
-- 符合BCNF的例子
CREATE TABLE student_teacher (
student VARCHAR(50),
teacher VARCHAR(50),
PRIMARY KEY (student, teacher)
);
CREATE TABLE teacher_course (
teacher VARCHAR(50) PRIMARY KEY,
course VARCHAR(50)
);
# 记忆口诀:BCNF=决定因素都是候选键=每个决策者都有决策权
4.4 模式分解(代码实现)
无损连接性检查:
python
def is_lossless_join(R, F, R1, R2):
"""
检查分解是否是无损连接的
R:原关系
F:函数依赖集
R1, R2:分解后的关系
"""
intersection = set(R1) & set(R2)
R1_minus_R2 = set(R1) - set(R2)
R2_minus_R1 = set(R2) - set(R1)
# 检查 (R1 ∩ R2) → (R1 - R2) 或 (R1 ∩ R2) → (R2 - R1)
for dep in F:
if set(dep['X']) == intersection:
if set(dep['Y']).issubset(R1_minus_R2) or set(dep['Y']).issubset(R2_minus_R1):
return True
return False
# 示例
R = ['A', 'B', 'C']
F = [
{'X': ['A'], 'Y': ['B']},
{'X': ['B'], 'Y': ['C']}
]
R1 = ['A', 'B']
R2 = ['B', 'C']
print(is_lossless_join(R, F, R1, R2)) # True
五、事务管理
5.1 事务的概念
定义: 事务是数据库操作的逻辑工作单位,是一组操作序列
代码示例:
java
// Java JDBC事务示例
Connection conn = null;
try {
conn = dataSource.getConnection();
conn.setAutoCommit(false); // 关闭自动提交,开启事务
// 操作1:张三账户-1000
PreparedStatement stmt1 = conn.prepareStatement(
"UPDATE accounts SET balance = balance - 1000 WHERE id = ?"
);
stmt1.setInt(1, 1); // 张三的ID
stmt1.executeUpdate();
// 操作2:李四账户+1000
PreparedStatement stmt2 = conn.prepareStatement(
"UPDATE accounts SET balance = balance + 1000 WHERE id = ?"
);
stmt2.setInt(1, 2); // 李四的ID
stmt2.executeUpdate();
conn.commit(); // 提交事务
} catch (Exception e) {
if (conn != null) {
conn.rollback(); // 回滚事务
}
e.printStackTrace();
} finally {
if (conn != null) {
conn.close();
}
}
# 要么两个操作都成功,要么都失败!
5.2 ACID特性
A - Atomicity（原子性）
定义: 事务是不可分割的,要么全做,要么全不做
实现方式:
- 日志（Log）
- 恢复机制
- 回滚（Rollback）
python
# Python伪代码
class Transaction:
def __init__(self, connection):
self.connection = connection
self.operations = []
def add_operation(self, operation):
self.operations.append(operation)
def commit(self):
# 记录日志
self.log("BEGIN TRANSACTION")
# 执行所有操作
for operation in self.operations:
try:
operation.execute()
except Exception as e:
# 失败,回滚
self.rollback()
raise e
# 提交
self.log("COMMIT TRANSACTION")
def rollback(self):
# 回滚所有操作
for operation in reversed(self.operations):
operation.undo()
self.log("ROLLBACK TRANSACTION")
C - Consistency（一致性）
定义: 事务执行前后,数据库从一个一致性状态变到另一个一致性状态
实现方式:
- 完整性约束
- 触发器
- 外键约束
sql
-- 完整性约束示例
CREATE TABLE accounts (
id INT PRIMARY KEY,
name VARCHAR(50),
balance DECIMAL(10, 2) CHECK (balance >= 0) -- 余额不能为负
);
-- 触发器示例
CREATE TRIGGER update_balance
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
IF NEW.balance < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '余额不能为负';
END IF;
END;
I - Isolation（隔离性）
定义: 多个事务并发执行时,互不干扰
实现方式:
- 锁机制
- 隔离级别
- 多版本并发控制（MVCC）
python
# 伪代码:隔离性实现
class TransactionManager:
def __init__(self):
self.transactions = {}
self.locks = {}
def begin_transaction(self, transaction_id):
self.transactions[transaction_id] = {
'id': transaction_id,
'operations': [],
'timestamp': time.time()
}
def acquire_lock(self, transaction_id, resource, lock_type):
# 检查是否可以获取锁
if resource in self.locks:
existing_lock = self.locks[resource]
if existing_lock['type'] == 'X' or lock_type == 'X':
# 锁冲突,等待
return False
# 获取锁
self.locks[resource] = {
'transaction_id': transaction_id,
'type': lock_type
}
return True
D - Durability（持久性）
定义: 事务一旦提交,对数据库的修改就是永久的
实现方式:
- 日志
- 检查点（Checkpoint）
- 数据备份
python
# 伪代码:持久性实现
class Database:
def __init__(self):
self.log_file = open("transaction.log", "a")
def log(self, message):
# 写入日志
self.log_file.write(f"{time.time()}: {message}\n")
self.log_file.flush()
def commit(self, transaction):
# 记录提交日志
self.log(f"COMMIT: {transaction.id}")
# 将数据写入磁盘
self.flush_to_disk()
def recover(self):
# 从日志恢复
with open("transaction.log", "r") as f:
for line in f:
if "COMMIT" in line:
# 重做已提交的事务
self.redo_transaction(line)
5.3 并发控制
并发问题(代码示例)
1. 丢失更新（Lost Update）:
python
# 伪代码:丢失更新问题
# 账户余额:100元
# 事务A:读取100元
balance_A = account.get_balance() # 100
# 事务B:读取100元
balance_B = account.get_balance() # 100
# 事务A:100-50=50,写回50元
account.set_balance(balance_A - 50) # 50
# 事务B:100-30=70,写回70元
account.set_balance(balance_B - 30) # 70
# 结果:应该是20元,实际是70元!
# 丢了50元!
解决方案:加锁
python
# 伪代码:使用锁解决丢失更新
with account.lock(): # 获取锁
balance = account.get_balance() # 100
account.set_balance(balance - 50) # 50
# 释放锁
2. 不可重复读（Non-repeatable Read）:
python
# 伪代码:不可重复读问题
# 事务A
balance1 = account.get_balance() # 20
# 事务B:修改数据
account.set_balance(21)
# 事务A:再次读取
balance2 = account.get_balance() # 21
# 结果:同一事务内,两次读取结果不同
3. 脏读（Dirty Read）:
python
# 伪代码:脏读问题
# 事务A:修改数据(未提交)
account.set_balance(100)
# 事务B:读取数据
balance = account.get_balance() # 100
# 事务A:回滚
transaction_A.rollback()
# 结果:事务B读取的是"脏"数据
4. 幻读（Phantom Read）:
python
# 伪代码:幻读问题
# 事务A:查询有5条记录
records = account.get_all_records() # 5条
# 事务B:插入1条记录
account.insert_record(new_record)
# 事务A:再次查询
records = account.get_all_records() # 6条
# 结果:同一事务内,查询结果数量不同
锁机制(代码实现)
1. 共享锁（Shared Lock, S锁）:
python
# 伪代码:共享锁
class SharedLock:
def __init__(self):
self.lock = threading.Lock()
self.readers = 0
def acquire(self):
with self.lock:
self.readers += 1
if self.readers == 1:
# 第一个读者,获取写锁
self.write_lock.acquire()
def release(self):
with self.lock:
self.readers -= 1
if self.readers == 0:
# 最后一个读者,释放写锁
self.write_lock.release()
# 使用示例
with shared_lock.acquire(): # 获取共享锁
data = database.read()
# 释放共享锁
2. 排他锁（Exclusive Lock, X锁）:
python
# 伪代码:排他锁
class ExclusiveLock:
def __init__(self):
self.lock = threading.Lock()
def acquire(self):
self.lock.acquire()
def release(self):
self.lock.release()
# 使用示例
with exclusive_lock.acquire(): # 获取排他锁
database.write(data)
# 释放排他锁
隔离级别对比
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 性能 | 考试频率 | 使用场景 |
|---|---|---|---|---|---|---|
| 读未提交 | ❌ | ❌ | ❌ | 最高 | ⭐ | 几乎不用 |
| 读已提交 | ✓ | ❌ | ❌ | 高 | ⭐⭐⭐⭐⭐ | 大多数应用 |
| 可重复读 | ✓ | ✓ | ❌ | 中 | ⭐⭐⭐⭐⭐ | 银行、金融 |
| 可串行化 | ✓ | ✓ | ✓ | 最低 | ⭐⭐⭐ | 严格场景 |
MySQL默认:REPEATABLE READ****Oracle默认:READ COMMITTED
5.4 死锁
死锁检测(代码实现)
python
# 伪代码:死锁检测
class DeadlockDetector:
def __init__(self):
self.wait_for_graph = {} # 等待图
def add_edge(self, transaction1, transaction2):
"""
添加边:事务1等待事务2
"""
if transaction1 not in self.wait_for_graph:
self.wait_for_graph[transaction1] = []
self.wait_for_graph[transaction1].append(transaction2)
def detect_deadlock(self):
"""
检测死锁(使用深度优先搜索)
"""
visited = set()
recursion_stack = set()
def dfs(transaction):
visited.add(transaction)
recursion_stack.add(transaction)
if transaction in self.wait_for_graph:
for waiting_transaction in self.wait_for_graph[transaction]:
if waiting_transaction not in visited:
if dfs(waiting_transaction):
return True
elif waiting_transaction in recursion_stack:
# 发现环,存在死锁
return True
recursion_stack.remove(transaction)
return False
for transaction in self.wait_for_graph:
if transaction not in visited:
if dfs(transaction):
return True
return False
# 使用示例
detector = DeadlockDetector()
detector.add_edge('T1', 'T2') # T1等待T2
detector.add_edge('T2', 'T3') # T2等待T3
detector.add_edge('T3', 'T1') # T3等待T1
if detector.detect_deadlock():
print("发现死锁!")
# 选择一个事务回滚
六、数据库新技术
6.1 NoSQL
NoSQL的分类
1. 文档存储（Document Store）:
javascript
// MongoDB示例
db.users.insertOne({
name: "张三",
age: 20,
hobbies: ["编程", "阅读"],
address: {
city: "北京",
district: "朝阳区"
}
});
// 查询
db.users.find({age: {$gt: 18}});
# 典型产品:MongoDB、CouchDB
# 应用场景:Web应用、半结构化数据
# 优点:结构灵活
# 缺点:查询语法不统一
2. 键值存储（Key-Value Store）:
python
# Redis示例
import redis
r = redis.Redis(host='localhost', port=6379, db=0)
# 设置键值
r.set('user:1:name', '张三')
r.set('user:1:age', '20')
# 获取值
name = r.get('user:1:name') # b'张三'
age = r.get('user:1:age') # b'20'
# 典型产品:Memcached、Redis
# 应用场景:缓存、会话管理
# 优点:扩展性好、性能高
# 缺点:数据无结构
3. 列存储（Column Family Store）:
java
// HBase示例
Configuration config = HBaseConfiguration.create();
Connection connection = ConnectionFactory.createConnection(config);
Table table = connection.getTable(TableName.valueOf("users"));
// 插入数据
Put put = new Put(Bytes.toBytes("row1"));
put.addColumn(Bytes.toBytes("info"), Bytes.toBytes("name"), Bytes.toBytes("张三"));
put.addColumn(Bytes.toBytes("info"), Bytes.toBytes("age"), Bytes.toBytes("20"));
table.put(put);
// 查询数据
Get get = new Get(Bytes.toBytes("row1"));
Result result = table.get(get);
String name = Bytes.toString(result.getValue(Bytes.toBytes("info"), Bytes.toBytes("name")));
// 典型产品:BigTable、HBase、Cassandra
// 应用场景:分布式数据存储
// 优点:可扩展性强、查询快
// 缺点:不支持强一致性
4. 图存储（Graph Store）:
cypher
// Neo4j示例
// 创建节点
CREATE (p1:Person {name: '张三'})
CREATE (p2:Person {name: '李四'})
CREATE (p1)-[:FRIEND]->(p2)
// 查询
MATCH (p1:Person {name: '张三'})-[:FRIEND]->(p2:Person)
RETURN p2.name
// 典型产品:Neo4j、OrientDB
// 应用场景:社交网络、推荐系统
// 优点:支持复杂图算法
// 缺点:复杂性高
CAP理论
定义: 分布式系统不能同时满足以下三个特性:
1. 一致性（Consistency）:
- 所有节点在同一时间看到相同的数据
2. 可用性（Availability）:
- 每个请求都能得到响应
3. 分区容错性（Partition Tolerance）:
- 系统在部分节点失效时仍能运行
CAP权衡:
- CA:传统数据库(不支持分区)
- CP:NoSQL(牺牲可用性)
- AP:NoSQL(牺牲一致性)
python
# 伪代码:CAP理论示例
class CAPSystem:
def __init__(self, cap_type):
self.cap_type = cap_type # 'CA', 'CP', or 'AP'
self.data = {}
self.nodes = []
def write(self, key, value):
if self.cap_type == 'CA':
# CA:写操作必须同步到所有节点
for node in self.nodes:
node.write(key, value)
elif self.cap_type == 'CP':
# CP:写操作可能失败(牺牲可用性)
if self.can_write():
self.data[key] = value
else:
raise Exception("无法写入")
elif self.cap_type == 'AP':
# AP:写操作可能不一致(牺牲一致性)
self.data[key] = value
def read(self, key):
if self.cap_type == 'CA':
# CA:读取最新数据
return self.get_latest_data(key)
elif self.cap_type == 'CP':
# CP:读取一致的数据
return self.data[key]
elif self.cap_type == 'AP':
# AP:读取可能不一致的数据
return self.get_local_data(key)
BASE理论
BA（Basically Available）:
- 基本可用
S（Soft State）:
- 软状态(允许数据不一致)
E（Eventually Consistent）:
- 最终一致性
python
# 伪代码:BASE理论示例
class BASESystem:
def __init__(self):
self.primary = {} # 主节点
self.replicas = [] # 副本节点
def write(self, key, value):
# 写入主节点
self.primary[key] = value
# 异步同步到副本(最终一致性)
self.async_sync(key, value)
def read(self, key):
# 从副本读取(基本可用)
for replica in self.replicas:
if key in replica:
return replica[key]
# 副本没有,从主节点读取
return self.primary.get(key)
def async_sync(self, key, value):
"""
异步同步,实现最终一致性
"""
def sync():
for replica in self.replicas:
replica[key] = value
threading.Thread(target=sync).start()
6.2 数据仓库
OLTP vs OLAP:
| 对比维度 | OLTP | OLAP |
|---|---|---|
| 应用 | 事务处理 | 决策分析 |
| 用户 | 操作人员 | 管理人员 |
| 数据 | 当前的、详细的 | 历史的、聚合的 |
| 操作 | 增删改查 | 查询 |
| 数据量 | MB、GB | TB、PB |
数据仓库示例:
sql
-- 星型模型示例
-- 事实表
CREATE TABLE sales_fact (
sale_id INT PRIMARY KEY,
product_id INT,
customer_id INT,
time_id INT,
sales_amount DECIMAL(10, 2),
FOREIGN KEY (product_id) REFERENCES products(product_id),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (time_id) REFERENCES time_dim(time_id)
);
-- 维度表:产品维度
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
brand VARCHAR(50)
);
-- 维度表:客户维度
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
city VARCHAR(50),
country VARCHAR(50)
);
-- 维度表:时间维度
CREATE TABLE time_dim (
time_id INT PRIMARY KEY,
date DATE,
year INT,
quarter INT,
month INT
);
-- OLAP查询示例:按季度统计销售额
SELECT
t.year,
t.quarter,
SUM(sf.sales_amount) as total_sales
FROM sales_fact sf
JOIN time_dim t ON sf.time_id = t.time_id
GROUP BY t.year, t.quarter;
七、历年真题解析
真题1（2023年）
在关系代数中,π1,2,3,4,7,8(σ1=5 ∧ 2>7 ∧ 3=6(R×S))表示的是（）
- A. 选择
- B. 投影
- C. 连接
- D. 笛卡尔积
答案:B
解析:
plaintext
步骤分析:
1. R×S → 笛卡尔积
2. σ1=5 ∧ 2>7 ∧ 3=6(...) → 选择满足条件的行
3. π1,2,3,4,7,8(...) → 投影第1,2,3,4,7,8列
结论:这是一个投影操作
真题2（2022年）
关系模式STJ(S,T,J),F={SJ→T,T→J},则STJ最高属于（）
- A. 1NF
- B. 2NF
- C. 3NF
- D. BCNF
答案:B（2NF）
解析:
python
# 代码验证
# 步骤分析:
# 1. 找候选键:{SJ}(SJ能推导出T,T能推导出J)
# 2. 检查2NF:非主属性T完全依赖SJ,符合2NF ✓
# 3. 检查3NF:T→J,T是决定因素但不是候选键,不符合3NF ✗
# 结论:属于2NF
def find_candidate_keys(F):
"""
找出候选键
"""
# SJ能推导出T,T能推导出J
# 所以SJ能推导出{S,T,J}
return [{'S', 'J'}]
def check_2NF(F, candidate_keys):
"""
检查是否满足2NF
"""
# 非主属性T完全依赖SJ,符合2NF
return True
def check_3NF(F, candidate_keys):
"""
检查是否满足3NF
"""
# T→J,T是决定因素但不是候选键
# 不符合3NF
return False
candidate_keys = find_candidate_keys([{'SJ': 'T'}, {'T': 'J'}])
print(f"候选键: {candidate_keys}")
print(f"2NF: {check_2NF([{'SJ': 'T'}, {'T': 'J'}], candidate_keys)}")
print(f"3NF: {check_3NF([{'SJ': 'T'}, {'T': 'J'}], candidate_keys)}")
真题3（2021年）
事务的ACID特性中,表示事务执行前后数据库从一个一致性状态变到另一个一致性状态的是（）
- A. 原子性
- B. 一致性
- C. 隔离性
- D. 持久性
答案:B（一致性）
解析:
plaintext
A(原子性):不可分割
B(一致性):始终保持平衡 ✓
C(隔离性):互不干扰
D(持久性):永久保存
真题4（2020年）
MySQL默认的隔离级别是（）
- A. READ UNCOMMITTED
- B. READ COMMITTED
- C. REPEATABLE READ
- D. SERIALIZABLE
答案:C（REPEATABLE READ）
解析:
python
# 查看MySQL隔离级别
# SHOW VARIABLES LIKE 'transaction_isolation';
# MySQL默认:REPEATABLE READ
# Oracle默认:READ COMMITTED
# SQL Server默认:READ COMMITTED
mysql_isolation_levels = {
'MySQL': 'REPEATABLE READ',
'Oracle': 'READ COMMITTED',
'SQL Server': 'READ COMMITTED',
'PostgreSQL': 'READ COMMITTED'
}
print(f"MySQL默认隔离级别: {mysql_isolation_levels['MySQL']}")
八、备考策略
8.1 高频考点总结
plaintext
数据库技术高频考点
├── 关系代数(选择、投影、连接、并、差、笛卡尔积)
├── E-R模型(实体、属性、关系:1:1、1:N、M:N)
├── 函数依赖(完全依赖、部分依赖、传递依赖)
├── 范式理论(1NF、2NF、3NF、BCNF)
├── 事务管理(ACID)
├── 并发控制(锁机制、隔离级别)
├── 死锁(四个必要条件)
└── NoSQL(分类、CAP理论、BASE理论)
8.2 记忆口诀
关系代数:
plaintext
σ(选择)=挑人
π(投影)=拍照
⨝(连接)=联姻
∪(并)=合并
∩(交)=交集
-(差)=差集
×(笛卡尔积)=组合
÷(除)=筛选
范式理论:
plaintext
1NF:原子性(一个人一个房间)
2NF:完全依赖(孩子完全依赖父母)
3NF:消除传递(孙子不直接依赖爷爷)
BCNF:决定因素都是候选键(每个决策者都有权)
ACID:
plaintext
A(原子性):要么全有要么全无
C(一致性):始终保持平衡
I(隔离性):互不干扰
D(持久性):永久保存
隔离级别:
plaintext
读未提交:偷看日记
读已提交:看发表的日记
可重复读:看定稿的书
可串行化:一个一个看
锁机制:
plaintext
S锁(共享):公共厕所,大家都能看
X锁(排他):私人厕所,只有我能用
8.3 60天冲刺计划
| 阶段 | 时间 | 重点内容 | 目标 | 每日任务 |
|---|---|---|---|---|
| 基础阶段 | 15天 | 理解关系代数、E-R模型、函数依赖 | 掌握基础概念 | 每天1小时,画1张E-R图 |
| 范式阶段 | 20天 | 掌握1NF、2NF、3NF、BCNF | 掌握范式判断 | 每天1.5小时,判断5个关系模式的范式 |
| 事务阶段 | 15天 | 理解ACID、锁机制、隔离级别 | 掌握并发控制 | 每天2小时,分析3个并发问题 |
| 综合阶段 | 10天 | NoSQL、数据仓库、历年真题 | 提升综合能力 | 每天2.5小时,做1套模拟题 |
九、职场应用:这个知识点怎么用到工作中?
关系代数 → SQL优化
sql
-- 理解关系代数,就能写出更高效的SQL
-- 原始SQL
SELECT s.name
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id
INNER JOIN courses c ON sc.course_id = c.id
WHERE c.name = '数据库'
AND sc.score > 80;
-- 优化后的SQL(先筛选再连接)
SELECT s.name
FROM students s
INNER JOIN (
SELECT sc.student_id
FROM scores sc
INNER JOIN courses c ON sc.course_id = c.id
WHERE c.name = '数据库'
AND sc.score > 80
) sc ON s.id = sc.student_id;
# 优化原理:
# 1. 先筛选"数据库"课程和分数>80的记录
# 2. 再与学生表连接
# 3. 减少连接的数据量,提高性能
范式理论 → 数据库设计
python
# 范式理论指导数据库设计
# 场景:电商系统用户表设计
# 不符合规范的设计
class BadUserDesign:
"""
不符合3NF的设计
"""
user_id: int
user_name: str
order_id: int # 传递依赖
order_amount: decimal # 传递依赖
product_id: int # 传递依赖
product_name: str # 传递依赖
# 符合3NF的设计
class User:
"""
用户表
"""
user_id: int
user_name: str
class Order:
"""
订单表
"""
order_id: int
user_id: int # 外键
order_amount: decimal
class OrderProduct:
"""
订单商品表
"""
order_id: int # 外键
product_id: int # 外键
class Product:
"""
商品表
"""
product_id: int
product_name: str
# 优势:
# 1. 消除数据冗余
# 2. 避免更新异常
# 3. 提高查询效率
事务管理 → 系统设计
java
// 事务管理在系统设计中的应用
// 场景:订单支付系统
@Service
public class OrderService {
@Transactional
public void payOrder(Long orderId, BigDecimal amount) {
// 1. 扣除用户余额
accountService.deductBalance(userId, amount);
// 2. 更新订单状态
orderService.updateOrderStatus(orderId, "PAID");
// 3. 增加商家余额
merchantService.addBalance(merchantId, amount);
// 4. 记录交易日志
transactionLogService.log(orderId, amount);
// 使用@Transactional注解,保证ACID特性
// 要么全部成功,要么全部失败
}
}
# ACID特性应用:
# - 原子性:订单支付(要么成功,要么失败)
# - 一致性:账户总额不变
# - 隔离性:并发订单(互不干扰)
# - 持久性:订单记录(永久保存)
十、技术要点总结
数据库系统本质
plaintext
数据库 = 有组织的数据集合 + 管理软件 + 用户
关系模型 = 表格 + 关系 + 约束
关系代数 = 关系操作的数学基础
范式理论 = 数据库设计的规范化标准
事务管理 = 并发控制 + 恢复机制
NoSQL = 大数据时代的数据库解决方案
SQL vs NoSQL
| 对比维度 | SQL | NoSQL |
|---|---|---|
| 数据模型 | 关系模型 | 灵活模型 |
| 扩展性 | 垂直扩展 | 水平扩展 |
| 事务支持 | ACID | BASE |
| 查询语言 | SQL | 不统一 |
| 适用场景 | 结构化数据 | 大数据、高并发 |
写在最后
数据库技术是系统架构师考试的核心考点,也是实际系统设计的重要基础。
对于35岁的程序员来说,掌握关系代数、范式理论、事务管理,应对考试绰绰有余,更重要的是这些知识可以直接应用到实际工作中。
备考核心:理解关系代数、掌握范式判断、熟记ACID和隔离级别
记住:数据库就是"有组织的仓库",关系代数是"操作工具",范式是"设计规范",事务是"保护机制"!
#系统架构师 #软考 #数据库 #关系代数 #范式 #事务 #NoSQL #技术进阶 #程序员 #架构师
相关文章:
