跳到主要内容

数据库理论

第一章 数据库系统概论​

元数据是数据的数据

1.1 数据库发展与核心概念​

数据模型三个主要组成部分： 数据结构：静态数据特征，是数据模型的基础 数据操作：动态数据特征，插入、更新、删除、查询 数据约束：规定数据的有效性和完整性

模型类型特点代表产品
层次模型树形结构，一对多关系，查询效率高IBM IMS
网状模型多对多关系，图形结构IDMS
关系模型使用行和列表示数据，表格形式，支持 SQL 查询PostgreSQL、MySQL

  1. 数据库系统构成

    • 核心组件：数据库（存储数据）、DBMS（管理数据库的软件）、应用程序、数据库管理员（DBA）、用户。
    • DBMS 功能：数据定义（DDL）、数据操作（DML）、数据控制、数据库维护。
  • 数据库 (Database - DB): 它就像是图书馆里，书架上存放的所有书籍和资料。从技术上讲，数据库就是按照一定数据模型组织、描述和储存起来的、可以被各种用户共享的结构化数据的集合。它就是我们最终要存取的核心——信息本身。
  • 数据库管理系统 (Database Management System - DBMS): 它就像是整个图书馆的管理系统，包括图书的分类编目规则、借阅归还流程、安全检查系统等等。从技术上讲，DBMS 是一种大型软件，比如我们常用的 MySQL、Oracle、PostgreSQL 软件。它的核心职责是科学地组织和存储数据、高效地获取和维护数据；为我们屏蔽了底层文件操作的复杂性，提供了一套标准接口（如 SQL）来操纵数据，并负责并发控制、事务管理、权限控制等复杂问题。
  • 数据库系统 (Database System - DBS): 它就是整个正常运转的图书馆。这是一个更大的概念，不仅包括书(DB)和管理系统(DBMS)，还包括了硬件、应用和使用的人。
  • 数据库管理员 (Database Administrator - DBA ): 他就是图书馆的馆长，负责整个数据库系统正常运行。他的职责非常广泛，包括数据库的设计、安装、监控、性能调优、备份与恢复、安全管理等等，确保整个系统的稳定、高效和安全。

DB 和 DBMS 我们通常会搞混，这里再简单提一下：通常我们说“用 MySQL 数据库”，其实是用 MySQL（DBMS）来管理一个或多个数据库（DB）

1.2 数据库技术演进​

  • 第一代（60-70 年代）：层次/网状模型，解决文件系统冗余问题（如 IMS、IDMS）。
  • 第二代（70 年代后）：关系模型兴起，科德提出关系理论，代表产品 Oracle、MySQL。
  • 第三代（90 年代后）：面向对象数据库（如 ObjectStore）、对象关系数据库（PostgreSQL）。
  • 第四代（近年）：NoSQL（如 MongoDB）、分布式数据库（如 Cassandra）、大数据技术（Hadoop）。

1.3 DBMS分类​

类型特点代表产品
关系型(RDBMS)表格存储，支持 SQLPostgreSQL、MySQL
非关系型(NoSQL)灵活数据模型，适合非结构化数据MongoDB、Redis
内存数据库数据存于内存，读写速度快SAP HANA、Redis
分布式数据库数据分片，高扩展性CockroachDB、Cassandra

⭐非关系型数据库（NoSQL）有哪些​

它们的共同特点是为了极致的性能和水平扩展能力，在某些方面（通常是事务）做了妥协。 1. 键值数据库，代表是 Redis。

  • 特点： 数据模型极其简单，就是一个巨大的 Map，通过 Key 来存取 Value。内存操作，性能极高。
  • 适用场景： 非常适合做缓存、会话存储、计数器等对读写性能要求极高的场景。 2. 文档数据库，代表是 MongoDB。
  • 特点： 它存储的是半结构化的文档（比如 JSON/BSON），结构灵活，不需要预先定义表结构。
  • 适用场景： 特别适合数据结构多变、快速迭代的业务，比如用户画像、内容管理系统、日志存储等。 3. 列式数据库，代表是 HBase, Cassandra。
  • 特点： 数据是按列族而不是按行来存储的。这使得它在对大量行进行少量列的读取时，性能极高。
  • 适用场景： 专为海量数据存储和分析设计，非常适合做大数据分析、监控数据存储、推荐系统等需要高吞吐量写入和范围扫描的场景。 4. 图形数据库，代表是 Neo4j。
  • 特点： 数据模型是节点（Nodes）和边（Edges），专门用来存储和查询实体之间的复杂关系。
  • 适用场景： 在社交网络（好友关系）、推荐引擎（用户-商品关系）、知识图谱、欺诈检测（资金流动关系）等场景下，表现远超关系型数据库。

NewSQL数据库​

NewSQL 就是：分布式存储+SQL+事务 。NewSQL 不仅具有 NoSQL 对海量数据的存储管理能力，还保持了传统数据库支持 ACID 和 SQL 等特性。因此，NewSQL 也可以称为 分布式关系型数据库。

第二章 关系数据模型​

2.1 主要术语说明​

① 元组(tuple)：表中的一行 ② 属性(attribute)：表中的一列，列名即为属性名 ③ 域(domain)：属性的取值范围 ④ 分量(element)：元组中的一个属性值 ⑤ 候选键(candidate key)：在关系中能唯一标识元组、没有多余属性的的属性集，也叫候选码 ⑥ 主键(primary Key)：用户选作元组标识的一个候选键称为主键 ⑦ 关系(relation)：关系是多个元组的集合，且是规范化的二维表格。 ⑧ 关系模式(relation mode) ：用关系名（属性名 1，属性名 2，……，属性名 n）来表示称为关系模式 ⑨主属性：候选码中出现过的属性称为主属性。比如关系 工人（工号，身份h证号，姓名，性别，部门）. 显然工号和身份证号都能够唯一标示这个关系，所以都是候选码。工号、身份证号这两个属性就是主属性。如果主码是一个属性组，那么属性组中的属性都是主属性。

2.2 关系数据模型核心概念​

• 数据库的物理存储完全由 DBMS 自动完成 • 表以文件形式存储 • 有的 DBMS 一个表对应一个操作系统文件 • 不同 DBMS 有不同的文件存储结构

  1. 数据结构
    • 以二维表存储数据，由元组（行）和属性（列）组成，每个属性有固定域（取值范围）。
    • 键：
      • 候选键：唯一标识元组且无冗余属性的属性集（如学生表的“学号”）。
      • 主键：选中的候选键，唯一标识元组（如“学号”作为学生表主键）。
    • 关系模式：形如“关系名(属性 1, 属性 2,...)”（如学生(学号,姓名,性别)）。
  2. 关系性质
    • 列不可分解：每个属性为最小数据项（如“地址”不可拆分为省/市/区多列）。
    • 元组唯一性：无重复行。
    • 行列顺序无关：列序可交换，行序不影响数据逻辑。

2.3 关系运算​

  1. 传统集合运算
运算定义
并（∪）包含 R 和 S 的所有元组（去重）
交（∩）同时属于 R 和 S 的元组
差（-）属于 R 但不属于 S 的元组
笛卡尔积（×）R×S 生成所有可能的元组组合

  1. 专门关系运算

    • 选择（σ）：从行角度筛选满足条件的元组（如查询“性别=女”的学生）。
    • 投影（π）：从列角度选取属性（如查询学生“姓名”和“专业”）。
    • 连接 （Join，符号是横置的漏斗）： 从两个关系的笛卡尔积中选取属性间满足条件 AθB 的元组
      • 条件连接：基于相等条件关联两表（如 R.B=S.B），是条件连接的特例。
      • 等值连接：基于相等条件关联两表（如 R.B=S.B），是条件连接的特例。
      • 自然连接：自动匹配同名属性并去重（如学生表与选课表通过“学号”连接），等值连接的特例。
      • 外连接：保留不匹配元组（左外连接保留左表全部行，右外同理，全外保留双方）。如果一个关系中的该字段在另一关系中没有相等值的行，自然连接不会显示该行，而外连接则将以 NULL 值形式显示该行。
    • 除（÷）：筛选满足包含关系的元组（如查询选修所有课程的学生）。

2.4 完整性约束​

1. 实体完整性​

  • 规则：关系中的每个元组（行）必须有唯一标识，且主键（主码）值不可为空。主键作为实体唯一标识，若为空则无法区分元组，违反实体存在性（如学生表中“学号”作为主键，必须唯一且非空）。
  • 实现：由数据库管理系统（DBMS）自动强制，创建表时定义主键后，系统自动检查插入/更新操作，拒绝空值或重复值。

2. 参照完整性​

  • 规则：若关系 R 的外键 F 参照关系 S 的主键，则 R 中每个元组的 F 值要么为空（需允许空值），要么等于 S 中某个元组的主键值。确保表间关联的一致性（如选课表“学号”参照学生表主键，选课记录的“学号”必须存在于学生表中）。
  • 实现：
    • 创建外键约束时指定参照关系（如FOREIGN KEY (CollegeID) REFERENCES COLLEGE(CollegeID)）。
    • DBMS 自动检查插入/更新/删除操作，防止无效关联（如删除学生表记录时，若选课表存在对应外键记录，默认拒绝删除，可通过级联操作配置）。

3. 用户定义完整性​

  • 规则：根据具体业务需求定义的约束，如数据类型、非空、唯一、检查条件等（如教师表“性别”字段只能为“男”或“女”）。
  • 实现：
    • 非空约束（NOT NULL）：定义字段时指定（如StudentName NOT NULL）。
    • 唯一约束（UNIQUE）：确保字段值唯一（如“手机号” UNIQUE）。
    • 检查约束（CHECK）：通过表达式限制取值（如CHECK (TeacherGender IN ('男', '女'))）。
    • 默认值约束（DEFAULT）：指定字段默认值（如“性别”默认值为“男”）。DEFAULT '男'

约束作用​

  • 数据一致性：防止无效数据插入（如主键重复、外键悬空）。
  • 业务规则落地：通过约束直接在数据库层实现业务逻辑（如成绩范围限制），减少应用层校验压力。
  • 跨表联动控制：通过外键级联操作（如级联删除）自动维护数据关联（如删除学院时级联删除该学院所有教师记录）。

第三章 数据库 SQL 语言​

SQL查询执行流程图

3.1 SQL 语言基础​

按功能可分类为：

  • 数据定义语言（DDL）：创建/修改/删除数据库、表、索引等，CREATE（创建）、ALTER（修改）、DROP（删除）、CREATE INDEX（创建索引）等。。
  • 数据操纵语言（DML）：查询/增删改数据，SELECT（查询）、INSERT（插入）、UPDATE（更新）、DELETE（删除）。
  • 数据控制语言（DCL）：权限管理，GRANT（授权）、REVOKE（回收权限）、DENY（拒绝权限）。
  • 事务处理语言（TPL）：管理事务（如 BEGIN TRANSACTION、COMMITT、ROLLBACK）。
  • 游标控制语言（CCL）：用于数据库游标操作的语句（DECLARE CURSOR、FETCH INTO、CLOSE CURSOR）

3.2 表结构设计与操作​

  1. 数据类型
类型分类示例说明
数值型INTEGER、BIGINT、DECIMAL(3,1)整数、浮点数、精确小数
字符型VARCHAR(20)、CHAR(10)、TEXT变长字符串、定长字符串、长文本
日期时间型DATE、TIMESTAMP日期（YYYY-MM-DD）、时间戳
特殊类型SERIAL、BOOLEAN、POINT自增长主键、布尔值、二维坐标

  1. 表操作语句

    • 创建表（含约束）：

      CREATE TABLE students (
        student_id SERIAL PRIMARY KEY,
        student_number VARCHAR(20) UNIQUE NOT NULL,
        gender CHAR(1) CHECK (gender IN ('M', 'F')) DEFAULT 'M'
      );

    • 修改表：添加职称字段ALTER TABLE teachers ADD COLUMN title VARCHAR(50);。

    • 删除表：DROP TABLE IF EXISTS Register;。

3.3 SQL 查询核心应用​

SELECT 查询语句​

-- 【核心查询】从指定表中获取数据，并按条件过滤、排序、分组、限制返回行数
-- 整体执行顺序（重要）：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT/OFFSET
SELECT 
  column1,  -- 要查询的列名1（可写具体列名，或用 * 表示查询所有列）
  column2,  -- 要查询的列名2
  ...       -- 更多要查询的列（可搭配聚合函数，如 COUNT(column1)、SUM(column2)）
FROM 
  table_name  -- 指定要查询的数据库表名（必填）
WHERE 
  condition   -- 行级过滤条件：筛选表中符合条件的行（如：age > 18、name = '张三'）
              -- 注意：WHERE 不能使用聚合函数（如 COUNT、SUM），聚合过滤用 HAVING
GROUP BY 
  column1,    -- 按列1分组：将相同值的行合并为一组（通常配合聚合函数使用）
  column2,    -- 按列2分组（多列分组时，需满足 SELECT 列都在 GROUP BY 中或为聚合函数）
  ...         
HAVING 
  condition   -- 组级过滤条件：筛选分组后的结果（如：COUNT(*) > 5，仅保留数量大于5的组）
              -- 注意：HAVING 必须跟在 GROUP BY 之后，可使用聚合函数
ORDER BY 
  column1 ASC/DESC,  -- 按列1排序：ASC（升序，默认）、DESC（降序）
  column2 ASC/DESC,  -- 按列2排序（多列排序时，先按列1排，列1相同再按列2排）
  ...         
LIMIT 
  number      -- 限制返回的行数（如：LIMIT 10 只返回前10行）
OFFSET 
  number;     -- 跳过前 N 行（如：OFFSET 5 跳过前5行，常和 LIMIT 配合实现分页）
              -- 示例：LIMIT 10 OFFSET 20 → 取第21-30行（分页第3页，每页10行）

聚合函数​

  1. COUNT(column)：返回指定列的行数。

    SELECT COUNT(student_id) FROM students; -- 返回学生数量

  2. SUM(column)：返回指定列的总和。

    SELECT SUM(score) FROM enrollments; -- 返回成绩总和

  3. AVG(column)：返回指定列的平均值。

    SELECT AVG(score) FROM enrollments; -- 返回平均成绩

  4. MAX(column)：返回指定列的最大值。

    SELECT MAX(score) FROM enrollments; -- 返回最高成绩

  5. MIN(column)：返回指定列的最小值。

    SELECT MIN(score) FROM enrollments; -- 返回最低成绩

  6. 基础查询

    • 全列查询：SELECT * FROM students;。
    • 条件过滤：查询男生SELECT * FROM students WHERE gender = 'M';。
    • 排序与分页：按姓名升序SELECT * FROM students ORDER BY student_name ASC LIMIT 5 OFFSET 2;。

  7. 高级查询技术

    • 聚合函数与分组 Group By：统计每班人数SELECT class_id, COUNT(*) AS student_count FROM students GROUP BY class_id;。

    • 多表连接 JOIN（学生-选课-课程表）：

      左外连接、右外连接、全外连接 FULL OUTER JOIN。左连接会返回左表（students）中的所有记录，以及右表（courses）中匹配的记录。如果右表中没有匹配的记录，则结果中右表的列会显示为 NULL。其他同理。

 -- 默认是内连接
 SELECT s.student_name, c.course_name
 FROM students s
 JOIN enrollments e ON s.student_id = e.student_id
 JOIN courses c ON e.course_id = c.course_id;
 -- 另一种写法
 SELECT s.student_name, c.course_name
 FROM students s, enrollments e, courses c
 WHERE s.student_id = e.student_id
   AND e.course_id = c.course_id;
 -- 左外连接
 SELECT t.teacher_name, c.course_name, cc.semester, c.hours
 FROM courses c
 LEFT JOIN course_classes cc ON cc.course_id = c.course_id
 LEFT JOIN teachers t ON t.teacher_id = cc.teacher_id
 ORDER BY t.teacher_name;

  1. 特殊查询场景

    • 子查询：查询某班级学生SELECT * FROM students WHERE class_id IN (SELECT class_id FROM classes WHERE major = '计算机科学');。
    • CASE 语句：成绩分级CASE WHEN score >= 90 THEN '优秀' ELSE '及格' END。
    • 窗口函数：计算课程班内成绩排名RANK() OVER (PARTITION BY course_class_id ORDER BY score DESC)。
    • UNION 合并多个查询结果
    • 公共表达式（CTE）

⭐一条SQL查询语句是如何执行的？​

简要回答​
  1. 连接阶段：由服务器端的连接器组件负责，在客户端与 MySQL 服务器之间建立连接，并验证用户权限。
  2. 查询缓存（仅限 MySQL 8.0 前）：检查是否命中缓存，若有完全相同且有效的查询结果可以直接返回。
  3. 解析与预处理：解析 SQL 语法，检查语法是否正确，并生成抽象语法树，然后，预处理器进行一些语义检查，验证表和字段是否存在。
  4. 优化器：基于统计信息和成本模型，考虑多种执行方案，并选择最优执行计划（如索引选择、JOIN 顺序）。
  5. 执行器：根据选择的执行计划，调用存储引擎接口，并按执行计划读取数据并处理（排序、聚合等）。
  6. 存储引擎（如 InnoDB）：负责从磁盘或内存读取数据，返回给执行器。
  7. 返回结果：执行器进行必要的处理（如过滤、排序）后，将结果集返回客户端，可能分批次传输。
详细回答​
  1. 连接阶段：
    • 当我们在客户端（如命令行工具、应用程序）输入并执行一条 SQL 查询时，首先需要与数据库服务器建立一个网络连接。
    • 这个过程包括 TCP/IP 协议的三次握手，以及数据库层面的认证，比如验证用户名和密码。
    • 连接成功后，服务器会为这个连接分配一个独立的线程来处理后续的请求。
  2. 查询缓存（MySQL 8.0 前）：
    • 服务器接收到 SQL 语句后，会先检查 查询缓存。这是一个位于内存中的区域，存储了之前执行过的查询语句及其结果。
    • 如果当前查询与缓存中的某个查询完全一致（包括 SQL 语句本身、连接的数据库、客户端的协议版本等），并且缓存仍然有效（比如涉及的表没有被修改），那么服务器会直接从缓存中返回结果，无需执行后续的解析、优化和执行过程。
    • 需要注意的是，在 MySQL 8.0 及更高版本中，查询缓存功能已经被移除。 这是因为在并发写入场景下，查询缓存的维护成本很高，容易成为性能瓶颈。因此，在现代数据库系统中，通常不再依赖查询缓存进行优化。
  3. 解析与预处理：
    • 如果查询缓存未命中，服务器会将 SQL 查询语句发送给解析器。解析器会对 SQL 语句进行词法分析（将语句分解成一个个词法单元，如关键字、标识符、操作符等） 和 语法分析（根据 SQL 语法规则检查语句是否合法，生成一个抽象语法树 AST）。
    • 如果语法有错误，解析器会直接返回错误信息。如果语法没问题，预处理器根据抽象语法树，进一步检查 SQL 语句的合法性，例如，检查表名、字段名是否存在，是否有权限执行该查询等，它还会进行一些语义上的检查和转换。
  4. 优化器：
    • 优化器的目标是找到执行查询的最优执行计划。它会考虑多种可能的执行方式，并评估它们的成本（如 I/O 次数、CPU 消耗等）。
    • 优化器会利用统计信息（如表的大小、索引的选择性等）来做出决策。
    • 常见的优化策略包括： ① 选择合适的索引。 ② 决定表的连接顺序。 ③ 选择合适的连接算法（如嵌套循环连接、哈希连接、合并排序连接）。 ④ 改写查询语句，使其更高效。
    • 最终，优化器会生成一个最优的执行计划（Execution Plan），它描述了如何执行查询的步骤。
  5. 执行器：
    • 执行器根据优化器生成的执行计划，调用存储引擎的接口来执行查询。
    • 执行器会按照执行计划的步骤，从存储引擎获取数据，进行过滤、排序、连接等操作。例如，如果执行计划指示使用某个索引进行查找，执行器就会调用存储引擎的索引查找接口。
    • 执行器会逐步处理数据，并将结果返回给客户端。
  6. 存储引擎（以 InnoDB 为例）：
    • 存储引擎是数据库系统中负责数据存储和检索的核心组件。不同的存储引擎有不同的特点和优势（如 InnoDB、MyISAM 等）。
    • 执行器通过存储引擎的 API 来访问和操作数据文件。
    • 存储引擎负责数据的读取、写入、更新、删除以及事务管理、锁机制等。
  7. 返回结果：
    • 执行器将最终的查询结果返回给客户端。
    • 客户端应用程序接收到结果后，可以进行进一步的处理和展示。

3.4 数据库对象管理​

视图（View）​

​ 视图是一个虚拟表，它是基于 SQL 查询结果的命名查询。视图并不存储数据，而是存储查询的 SQL 语句。查询视图时，PostgreSQL 会动态执行视图定义中的 SQL 语句，并返回结果。

  • 作用：简化复杂查询、提升安全性，如CREATE VIEW student_course_info AS SELECT ...。
  • 限制：不存储数据，基于基表动态查询。
CREATE MATERIALIZED VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

物化视图（Materialized View）​

​物化视图（Materialized View）是在创建时会将查询结果存储在数据库中，需要定期刷新。 特点：存储查询结果，需手动刷新REFRESH MATERIALIZED VIEW student_course_mview;。 应用场景：大数据集复杂查询性能优化。

⭐索引（Index）​

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。 按照数据结构维度划分

  • BTree索引: MySQL里默认和最常用的索引类型。只有叶子节点存储value, 非叶子节点只有指针和key。存储引擎MyISAM和InnoDB实现BTree索引都是使用B+Tree, 但二者实现方式不一样（前面已经介绍了）。
  • 哈希索引: 类似键值对的形式, 一次即可定位。
  • RTree索引: 一般不会使用, 仅支持geometry数据类型, 优势在于范围查找, 效率较低, 通常使用搜索引擎如ElasticSearch代替。
  • GIN全文索引: 对文本的内容进行分词, 进行搜索。目前只有CHAR、VARCHAR、TEXT列上可以创建全文索引。一般不会使用, 效率较低, 通常使用搜索引擎如ElasticSearch代替。 按照底层存储方式角度划分：
  • 聚簇索引（聚集索引）: 索引结构和数据一起存放的索引, InnoDB中的主键索引就属于聚簇索引。
  • 非聚簇索引（非聚集索引）: 索引结构和数据分开存放的索引, 二级索引（辅助索引）就属于非聚簇索引。MySQL的MyISAM引擎, 不管主键还是非主键, 使用的都是非聚簇索引。 按照应用维度划分：
  • 主键索引: 加速查询 + 列值唯一（不可以有NULL）+ 表中只有一个。
  • 普通索引: 仅加速查询。
  • 唯一索引: 加速查询 + 列值唯一（可以有NULL）。
  • 覆盖索引: 一个索引包含（或者说覆盖）所有需要查询的字段的值。
  • 联合索引: 多列值组成一个索引, 专门用于组合搜索, 其效率大于索引合并。
  • 全文索引: 对文本的内容进行分词, 进行搜索。目前只有CHAR、VARCHAR、TEXT列上可以创建全文索引。一般不会使用, 效率较低, 通常使用搜索引擎如ElasticSearch代替。
  • 前缀索引: 对文本的前几个字符创建索引, 相比普通索引建立的数据更小, 因为只取前几个字符。
 CREATE INDEX idx_student_name ON students(student_name); -- 创建
 DROP INDEX idx_student_name; -- 删除

3.5 数据操作与维护​

  1. 插入数据
    • 单行：INSERT INTO classes (class_code, class_name) VALUES ('CS101', '计算机科学与技术1班');。
    • 多行：INSERT INTO teachers VALUES ('E001', '刘老师', 'M', '教授'), ('E002', '陈老师', 'F', '副教授');。
  2. 删除数据
    • 按条件删除：DELETE FROM students WHERE student_name = '李四';。
    • 快速清空表：TRUNCATE TABLE students;（不可回滚，不触发触发器）。

第四章 数据库设计与规范化​

4.1 数据库设计框架与流程​

  • 应用架构：分为单用户、集中式、客户/服务器、分布式四种模式。
  • 结构模型：
    • 概念数据模型（CDM）：面向用户，描述现实世界数据结构（如实体、属性、联系）。描述了系统的静态特性、动态特性以及完整性约束条件等，其中包括了数据结构、数据操作和完整性约束三部分。 ① 数据结构表达为实体（有唯一的标识符，称为主键）和属性; ② 数据操作表达为实体中的记录的插入、删除、修改、查询等操作; ③ 完整性约束表达为数据的自身完整性约束（如数据类型、检查、规则等）和数据间的完整性约束（如外键、联系、继承联系等）
    • 逻辑数据模型（LDM）：基于 CDM，考虑 DBMS 逻辑表示（如关系模型），从系统设计角度描述系统的数据对象组成及其关联结构，并考虑这些数据对象符合数据库对象的逻辑表示。。
    • 物理数据模型（PDM）：针对具体 DBMS，设计存储方式、索引等（如 MySQL 表结构），用于描述系统数据模型在具体 DBMS 中的数据对象组织、存储方式、索引方式、访问路径等实现信息。。
  • 访问方式：包括本地接口、标准接口（如 ODBC）、数据访问层框架（如 Hibernate）。

4.2 E-R 模型​

E-R 模型基础​

ER 图 全称是 Entity Relationship Diagram（实体联系图），提供了表示实体类型、属性和联系的方法。 ER 图由下面 3 个要素组成：

  • 实体：通常是现实世界的业务对象，当然使用一些逻辑对象也可以。比如对于一个校园管理系统，会涉及学生、教师、课程、班级等等实体。在 ER 图中，实体使用矩形框表示。
    • 弱实体：依赖强实体存在，无独立主键（如子女实体依赖职工实体）。
  • 属性：即某个实体拥有的属性，属性用来描述组成实体的要素，对于产品设计来说可以理解为字段。在 ER 图中，属性使用椭圆形表示。
  • 联系：即实体与实体之间的关系，在 ER 图中用菱形表示，这个关系不仅有业务关联关系，还能通过数字表示实体之间的数量对照关系。例如，一个班级会有多个学生就是一种实体间的联系。
    • 1:1：如班级与班主任（一个班级唯一对应一个班主任）。
    • 1:N：如班级与学生（一个班级包含多个学生）。
    • M:N：如学生与课程（一个学生选多门课，一门课被多学生选）。

PowerDesigner 操作流程​

  • 模型创建：新建项目 → 选择模型类型（如 CDM）→ 拖放实体工具 → 设计属性与主键。
  • 联系添加：选择“Relationship”工具，设置实体间基数（如班级到学生为“1..*”）。
  • 模型转换：CDM→LDM（逻辑模型）→PDM（物理模型），支持生成 SQL 语句。

4.3 数据库规范化​

关系模式是一种用于描述关系数据库中关系的结构，包括关系名、属性集合以及属性间的数据依赖等要素。规范化就是对所有的属性进行重新组合，使关系的结构更简洁、更规范。规范化的目的是： 优化关系模式，提高数据管理的效率 关系模型只支持简单、单值属性。

数据依赖​

  • 函数依赖：如学号 → 姓名（单值决定）。 X→Y，X 称为决定子，函数依赖类似于函数关系。
  • 平凡依赖：（学号，姓名）→ 姓名，而姓名包含于 （学号，姓名），因此，这就是平凡依赖。
  • 部分函数依赖：（学号，姓名）→ 班号，而 学号 → 班号，因此，班号 部分依赖于（学号，姓名）。
  • 传递函数依赖：学号 → 班号，班号 → 学院，则学院传递依赖于学号，或 学号传递决定学院。
  • 多值依赖：X→→Y。学生 A 选了课程数学和物理，他的兴趣爱好是绘画和音乐，那么不管他选了哪些课程，他的兴趣爱好都是绘画和音乐这一组值，不会因为课程的变化而变化；同样地，他选的课程也不会因为兴趣爱好的改变而改变。这种情况下，“兴趣爱好” 多值依赖于 “学生”，同时 “课程” 也多值依赖于 “学生”。

范式​

第一范式（1NF）：如果关系表 R 不存在复合属性及多值属性，即：属性是不可再分，则称 R 满足第一范式。对于不满足 1NF 的表，其解决办法：将复合属性用各子属性代替，称为简单属性，将含有多值属性的表分解成两张表，一张表由主键和简单属性构成，另外一张表由多值属性和主键。1NF 是所有关系型数据库的最基本要求 ，也就是说关系型数据库中创建的表一定满足第一范式。 第二范式（2NF）：如果关系表 R 满足 1NF，且所有非主属性都完全依赖于任一候选键，则称 R 满足 2NF。即增加了主键。 第三范式（3NF）：如果关系表 R 满足 1NF，且所有非主属性都非传递依赖于 R 的任一候选键，则称 R 满足第三范式，记作 R∈3NF。即 R 中不存在非主属性对键的传递函数依赖。推论：若 R 不存在非主属性，则一定满足 3NF

范式核心规则示例（学生表优化）
1NF属性不可分分解“工资”为基本工资、津贴等
2NF消除部分依赖主键（学号,课程号）→ 成绩，非主属性完全依赖
3NF消除传递依赖学号 → 班号 → 学院，分解为学生表与班级表
BCNF主属性完全依赖键教师 → 课程，候选键为（学生,教师），分解为授课表
4NF消除非平凡非函数依赖的多值依赖学生 - 课程 - 兴趣爱好表，分解为学生 - 课程表、学生 - 兴趣爱好表
5NF消除连接依赖复杂多属性表（如学生 - 课程 - 教师 - 教材），按需分解为多个表

⭐什么是数据库范式？1NF 到 3NF 的核心规则是什么？​

  • 范式：数据库设计的规范等级，用于消除数据冗余与异常，分为 1NF 至 5NF。
  • 1NF：属性不可再分（如“地址”分解为省、市、区）。
  • 2NF：消除非主属性对主键的部分依赖（选课表中成绩完全依赖（学号,课程号），而非单独依赖学号，所以成绩部分依赖于学号）。
  • 3NF：消除非主属性对主键的传递依赖（如学生表中学院通过班号传递依赖学号，需分解为学生表与班级表）。

⭐使用外键与级联的优缺点？​

优点：1、保证了数据库数据的一致性和完整性；2、级联操作方便，减轻了程序代码量； 缺点：增加了复杂性，测试与扩展不方便；分库分表下外键是无法生效的，级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度；不适合分布式、高并发集群

第五章 数据库实现与管理​

5.1 数据库的存储结构​

逻辑结构​

一共有四个层级，分别是表空间、段、页面、记录。

  • 表空间：逻辑存储容器，可将不同数据（如用户数据、索引、临时数据）分配到不同物理路径存储（例：CREATE TABLESPACE datatablespace LOCATION 'C:\PostgreSQL\tablespaces\'），实现数据隔离与性能优化。表空间是数据库对象（表、索引）的逻辑分组，可跨物理设备分布，提升 I/O 效率与安全性。在删除表空间之前，需要先删除或移动该表空间中的所有对象（比如表和索引）。
  • 段：表空间内的逻辑存储单元，每个段可以包含一个或多个页面。分为：
    • 表段：存储表数据（如学生表的行记录）；
    • 索引段：存储索引数据（如 B+树索引节点）；
    • 临时段：存储临时表或查询中间结果。
  • 页面：数据存储的最小物理单位（PostgreSQL 默认 8KB），每个页面可存储多条记录。I/O 操作以页面为单位，页面头部记录块信息（如链接指针、修改时间戳），剩余空间存储记录数据。
  • 记录：表中的一行数据，数据库中存储的基本数据单元，分为：
    • 定长记录：字段长度固定，存储效率高但灵活性低（如学生表中学号、性别字段长度固定）；
    • 变长记录：包含固定区（存储字段偏移量）和变长区（存储实际数据），适合含文本、大对象的场景（如备注字段）。

物理结构​

DBMS 负责将逻辑结构转换为物理结构。有三种类型的文件。

  • 数据文件：实际存储数据的磁盘文件，按块组织（块大小通常为 4KB~8KB，与磁盘扇区对齐） 定长记录直接存储，变长记录通过分槽结构（Slot）管理偏移量与数据块。
  • 日志文件：记录事务操作（如插入、更新、删除），用于故障恢复。通过重做（Redo）未提交事务和回滚（Undo）已失败事务，确保数据一致性。
  • 控制文件：存储数据库元数据，包括表空间配置、数据库结构、版本信息等（如 PostgreSQL 的pg_control文件），是数据库启动和恢复的关键。 大对象数据通常采用单独文件方式存储，并将其指针放入数据库的数据文件块中存储
在数据文件中如何删除记录​

在数据文件的第一个块头区定义文件头中， 存储自由链表指针。 该自由链表指针指向被删除的第一个记录的地址。 第一个记录的块头区中也存储第二个被删除记录的地址。 以此类推， 在数据文件中， 形成一个被删除记录的自由链表

5.2 ⭐索引的底层数据结构！​

MySQL索引详解 数据库索引是一种数据记录定位结构， 它将查询条件键值作为输入， 能快速从索引结构 (如索引表) 获取该条件键值对应数据记录的位置指针， 通过位置指针从关系表找出结果集记录。在没有索引的情况下，数据库系统执行查询时通常需要对整个表进行扫描，也就是遍历每一条记录。

B+树索引​

B 树也称 B- 树，全称为 多路平衡查找树，B+ 树是 B 树的一种变体。B+树结点的最大孩子个数称为树的阶，通常用 m 表示。对于非根的所有结点，至少拥有⌈m/2⌉ 棵子树，即至少拥有 ⌈m/2⌉ 个关键字。 查询时间复杂度稳定为 O(log⁡n)。 叶节点中将关键字按大小顺序排列，并且相邻叶节点按大小顺序相互链接起来。 是数据库（如 MySQL InnoDB 主键索引）的默认索引结构，基于多路平衡查找树实现：

  • 树结构分层：叶子节点存储全部数据指针 + 排序后的键值，非叶子节点仅存键值和子节点指针，减少磁盘 IO；
  • 有序性：所有键值在叶子节点按顺序排列，且叶子节点间通过链表相连，支持范围遍历。
  • 操作机制
    • 插入：若节点已满（达到阶数 m），触发分裂，中间键值上移至父节点（如插入键值 95 时，叶节点分裂并调整父节点键值）。
    • 删除：若节点键值数小于⌈m/2⌉，需向兄弟节点借键值或合并节点（如删除键值 51 后，向兄弟节点借取键值 59）。 B+树索引结构图
为什么不是B树​
  • B 树的所有节点既存放键(key)也存放数据(data)，而 B+ 树只有叶子节点存放 key 和 data，其他内节点只存放 key。
  • B 树的叶子节点都是独立的；B+ 树的叶子节点有一条引用链指向与它相邻的叶子节点。
  • B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+ 树的检索效率就很稳定，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
  • 在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+ 树的范围查询，只需要对链表进行遍历即可。 综上，B+ 树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

哈希索引​

使用哈希表，用哈希函数将查询键值映射为固定的哈希值，哈希值直接对应数据存储的 “桶（Bucket）” 地址（位置指针），查询时只需计算键值的哈希值，就能直接定位数据位置。

  • 极致高效：等值查询（=/IN）速度接近 O (1)，远超全表扫描；
  • 局限性：无法支持范围查询（如 >/</BETWEEN）、排序 / 分组，因哈希值是无序的；且哈希冲突（不同键值映射到同一桶）会轻微降低效率。
  • 适用场景：仅需等值查询的场景（如内存数据库、缓存索引），MySQL 的 Memory 引擎、Redis 键值索引均基于哈希实现。

哈希索引结构图

5.3 ⭐事务管理与并发控制​

事务管理与并发控制

事务特性（ACID）​

原子性（Atomicity）

  • 事务是最小执行单元，要么全部成功提交，要么全部回滚（如转账操作中扣款与入账必须同时成功或失败）。通过日志记录操作前/后状态，确保失败时能撤销所有变更。
-- 原子性示例：转账操作
BEGIN / START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 若操作都成功则提交
COMMIT;
-- 若出现错误则回滚
-- ROLLBACK;

一致性（Consistency）

  • 事务执行前后，数据库从一个合法状态转换为另一个合法状态（如转账前后账户总余额不变）。依赖约束（如主键唯一、外键关联）和业务逻辑保证数据正确性。 隔离性（Isolation）
  • 多个事务并发执行时互不干扰，通过隔离级别控制不同事务间的可见性（如读提交避免脏读，可重复读避免不可重复读）。
-- 隔离性示例：设置隔离级别为读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

持久性（Durability）

  • 已提交事务的修改永久保存在磁盘，即使系统崩溃也不丢失。通过日志预写（WAL）机制确保数据落盘。

并发读写时的四个问题​

  1. 脏读

    • 问题：事务 A 读取到事务 B 未提交的修改，若 B 回滚，A 读取的数据无效。
    • 解决：设置隔离级别为“读已提交”或更高，确保只读取已提交数据。

  2. 不可重复读

    • 问题：事务 A 两次读取同一数据，期间事务 B 修改并提交，导致 A 两次结果不一致。
    • 解决：隔离级别设为“可重复读”，通过行级锁或 MVCC（多版本并发控制）保证读一致性。
    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    START TRANSACTION;
    SELECT balance FROM accounts WHERE id = 1;
    -- 再次查询
    SELECT balance FROM accounts WHERE id = 1;
    COMMIT;

  3. 幻读

    • 问题：事务 A 按条件查询数据时，事务 B 插入新符合条件的记录，导致 A 再次查询结果增多。
    • 解决：隔离级别设为“序列化”，通过范围锁（Next-Key Lock）防止插入冲突。
    SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    START TRANSACTION;
    SELECT * FROM accounts WHERE balance > 1500;
    -- 再次查询
    SELECT * FROM accounts WHERE balance > 1500;
    COMMIT;

  4. 丢失更新

    • 问题：两个事务同时修改同一数据，后提交的覆盖先提交的变更（如两人同时修改同一商品库存）。
    • 解决：使用排他锁（X 锁）或乐观锁（版本号机制）确保更新原子性。
    START TRANSACTION;
    SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
    UPDATE accounts SET balance = balance + 200 WHERE id = 1;
    COMMIT;

调度​

调度等价：如有两个调度 S1 和 S2，在 DB 的任意相同初始状态下，所有读出的数据都是一样的，留给 DB 的最终状态也是一样的，则称 S1 和 S2 是调度等价的。

可串行化调度：对一事务集，如一个并发调度与一个串行调度等价，则称此并发调度是可串行化的(Serializable)。可串行化则代表正确调度正确，保持了一致性。现有数据库主要采用加锁方式来保证调度执行的可串行化。

锁协议与隔离级别​

传统锁​

排它锁（eXclusive，也称 X 锁，用于写操作）某数据对象在没有加任何锁的情况下，一个事务可以对其加 X 锁，而其他事务就不 能对其再加任何锁。 共享锁（Share，也称 S 锁，用于读操作）一个事务对某数据对象加了 S 锁后，其他事务就不能对其加 X 锁，但可以加 S 锁。 U 锁：事务在做更新时，分两步：先读后写。即先读老内容，在内存中修改后；然后再写入修改后的内容。事务要更新数据对象时，先申请该对象的 U 锁。对象加了 U 锁，允许其他事务对它加 S 锁。在最后写入时，再将 U 锁升级为 X 锁。

死锁与活锁​
  • 活锁：多个事务同时申请数据封锁时，因选择策略问题导致长时间等待（如每次都让新事务先加锁，老事务一直等待），表现为 “忙等” 而非 “阻塞”，可通过 “先来先服务” 策略解决。
  • 死锁（面试高频）：
    • 定义：两个或多个事务互相持有对方需要的锁，且都不释放，导致永久阻塞。
    • 示例：事务 A 持有数据 1 的 X 锁，申请数据 2 的 X 锁；事务 B 持有数据 2 的 X 锁，申请数据 1 的 X 锁。
    • 解决方式：数据库层面（如 InnoDB 自动检测死锁并回滚代价小的事务）；业务层面（固定加锁顺序、缩短事务时长、避免大事务）。
乐观锁 / 悲观锁​

悲观锁（Pessimistic Lock）

  • 核心思想：假定并发冲突一定会发生，在整个数据处理过程中，将数据处于锁定状态，阻止其他事务对数据的操作。
  • 实现方式：依赖数据库自身的锁机制（即前面提到的排它锁 X 锁、共享锁 S 锁、U 锁），通常通过 SELECT ... FOR UPDATE（加 X 锁）、SELECT ... FOR SHARE（加 S 锁）实现。
  • 适用场景：并发写冲突概率高的场景（如库存扣减、资金转账），避免频繁重试导致性能损耗。加锁会阻塞其他事务，并发量高时容易导致性能瓶颈、死锁。

乐观锁（Optimistic Lock）

  • 核心思想：假定并发冲突不会发生，整个数据处理过程中不加锁，仅在提交更新时检查数据是否被其他事务修改。

  • 实现方式：

    • 版本号法：给数据表新增 version 字段，更新时判断版本号是否匹配（UPDATE goods SET stock=stock-1, version=version+1 WHERE id=1 AND version=old_version）。
    • 时间戳法：类似版本号，用 update_time 字段替代版本号。

  • 适用场景：并发读多写少的场景（如商品详情页、用户信息查询），减少锁阻塞提升并发性能。

  • 面试考点：

    • 缺点：冲突发生时需要重试（通常业务层实现），冲突频率高时重试成本高。
    • 注意：不能用 SELECT 获取版本号后再 UPDATE（存在 ABA 问题），必须在同一条 UPDATE 语句中校验版本号。
封锁协议​
  • 一级协议：写数据前加 X 锁，事务结束释放，防止丢失更新（但允许脏读）。
  • 二级协议：读数据前加 S 锁，读完释放，避免脏读（但可能不可重复读）。
  • 三级协议：读数据前加 S 锁，事务结束释放，解决脏读、不可重复读（但可能存在幻读）。
  • 两阶段锁协议（2PL）：事务分加锁阶段和解锁阶段，确保可串行化调度，但可能引发级联回滚（通过严格两阶段锁优化）。

两级锁协议的级联回滚现象

隔离级别​
级别脏读不可重复读幻读性能
读未提交允许允许允许最高
读已提交禁止允许允许较高
可重复读禁止禁止允许中等
序列化禁止禁止禁止最低

PostgreSQL 默认隔离级别为“可重复读”，通过 MVCC 实现高并发下的读一致性。

5.4 安全与权限管理​

1. 用户管理​

创建用户、 更新用户属性（如密码、权限、连接数）、删除用户

2. 角色管理​

角色是权限集合，用于批量管理具有相同权限的用户组，支持继承机制。

  • 创建角色：向角色批量授予对象权限（如表的查询、插入、更新权限）。
  • 用户加入角色：将用户关联到角色，继承角色权限。

3. 权限控制​

超级用户（如postgres）拥有最高权限，可管理所有对象；普通用户权限由超级用户或角色分配。

  • 权限类型
    • 对象权限：表/视图的 SELECT、INSERT、UPDATE、DELETE 等。
    • 系统权限：创建数据库、角色、表空间等（如CREATEDB）。
  • 授权与回收

5.5 备份与恢复​

备份类型​

全量备份：复制整个数据库（含结构和数据），用于初始恢复或完整数据副本（如每周一次全量备份）。 增量备份： 仅备份自上次全量备份后变更的数据，存储空间小但恢复需依赖全量备份（如每日增量备份）。 差异备份：备份自上次全量备份后所有变更（区别于增量备份的“上次任意备份”），恢复时仅需全量+最新差异备份。 日志备份：持续备份事务日志，用于将数据库恢复到特定时间点（如故障前 10 分钟），需结合全量备份使用。 热备份与冷备份：

  • 热备份：数据库运行中备份（如 PostgreSQL 的pg_dump），不中断服务。
  • 冷备份：停机状态下备份，确保数据一致性（如关闭数据库后复制文件）。
  • 文件备份：是指将数据库系统中的系统文件、数据文件和日志文件复制到另一个位置或存储介质。

恢复​

（1）事务故障的数据恢复 （2）系统崩溃的数据恢复 （3）存储介质损坏的数据恢复 利用数据库备份文件和数据库事务日志文件，一般采用前滚（redo 重做）事务方式或回滚（rollback）事务方式恢复数据库。

第六章 ⭐缓存​

缓存是将热点数据临时存储在高速介质中，减少对底层存储的访问。 缓存设计原则 - 热点数据识别：基于访问频率筛选热点 Key；过期策略：TTL（生存时间）、LRU（最近最少使用）/LFU（最不经常使用）/FIFO 淘汰算法；序列化：选择高效的序列化方式（如 Redis 的 Protobuf）。 核心目标：在内存不足时淘汰 Key，或按预设时间过期 Key，需兼顾淘汰效率和命中率。

功能模块核心数据结构典型实现（Redis）
热点 Key 筛选哈希表、跳表、环形队列、布谷鸟过滤器Zset（跳表 + Hash）、Hash 表 + 计数器
TTL 过期哈希表（过期字典）、时间轮过期字典（Hash）+ 惰性 / 定期删除
LRU 淘汰哈希表 + 双向链表 / 时间戳数组近似 LRU（Hash + 随机采样数组）
LFU 淘汰哈希表 + 对数计数器Hash + 8 位频率计数器 + 衰减时间戳
FIFO 淘汰哈希表 + 队列 / 循环数组Hash + 队列
Protobuf 序列化结构化字节数组 + 字段索引 Hash 表字节数组存储数据，Hash 表解析字段映射

缓存分类​

  • 本地缓存：存储在应用进程内存中，如 Guava Cache/Caffeine，优点是访问速度最快，缺点是无法跨节点共享、内存容量有限，适用于单机热点数据（如配置信息）。
  • 分布式缓存：独立于应用的缓存服务，如 Redis/Memcached，优点是可共享、容量大，支持高并发，适用于分布式系统的热点数据（如商品详情、用户会话）。
  • 多级缓存：本地缓存 + 分布式缓存 + CDN 的组合，如用户请求先查 CDN → 本地缓存 → 分布式缓存 → 数据库，逐级降级。

缓存的四个问题​

  • 缓存穿透：请求不存在的数据，导致穿透到数据库。解决方案：布隆过滤器（提前过滤不存在的 Key）、缓存空值（设置过期时间）。
  • 缓存击穿：热点 Key 失效瞬间，大量请求穿透到数据库。解决方案：互斥锁（Redis SETNX）、热点数据永不过期、逻辑过期。
  • 缓存雪崩：大量 Key 同时失效，导致数据库压力骤增。解决方案：过期时间加随机值、多级缓存、缓存集群（避免单点故障）。
  • 缓存一致性：数据库与缓存数据不一致。解决方案：更新策略（先更数据库，再删缓存；或先删缓存，再更数据库 + 延迟双删）、最终一致性（异步同步）。

SQL查询缓存​

  • 缓存 Key：完整的 SQL 查询语句（字节级完全匹配，包括空格、大小写）；
  • 缓存 Value：查询结果集；
  • 触发逻辑：当执行 SELECT 语句时，数据库先检查缓存中是否有完全匹配的 SQL 及其结果，有则直接返回，无则执行 SQL 并缓存结果；
  • 失效逻辑：一旦缓存对应的表发生任何写操作（INSERT/UPDATE/DELETE），该表的所有查询缓存会被全部清空。 查询缓存是完全存储在内存中的，效率高，但是太容易失效了，这是致命问题，并且在高并发压力环境中查询缓存会导致系统性能的下降，甚至僵死。

缓存读写策略​

Cache Aside Pattern（旁路缓存模式）​

最常用、最经典的一种模式，几乎是互联网应用缓存方案的事实标准，尤其适合读多写少的业务场景。这个模式之所以被称为旁路”(Aside),是因为应用程序的写操作完全绕过了缓存，直接操作数据库。应用程序扮演了数据流转的“指挥官”，需要同时维护 Cache 和 DB 两个数据源。 写操作 ：

  1. 应用先更新 DB。
  2. 然后直接删除 Cache中对应的数据。 读操作：
  3. 应用先从 Cache 读取数据。
  4. 如果命中(Hit)，则直接返回。
  5. 如果未命中(Miss)，则从 DB 读取数据，成功读取后，将数据写回 Cache，然后返回。

1. 为什么写操作是“先更新 DB，后删除 Cache”？顺序能反过来吗？ 答： 绝对不能。如果“先删 Cache，后更新 DB”，在高并发下会引入经典的数据不一致问题。

  • 时序分析 (请求 A 写, 请求 B 读):
    1. 请求 A: 先将 Cache 中的数据删除。
    2. 请求 B: 此时发现 Cache 为空，于是去 DB 读取旧值，并准备写入 Cache。
    3. 请求 A : 将新值写入 DB。
    4. 请求 B: 将之前读到的旧值写入了 Cache。
  • 结果： DB 中是新值，而 Cache 中是旧值，数据不一致。

2. 那“先更新 DB，后删除 Cache”就绝对安全吗？ 答案： 也不是绝对安全的！因为这样也可能会造成 数据库和缓存数据不一致的问题。

  • 时序分析 (请求 A 读, 请求 B 写):
    1. 请求 A : 缓存未命中，从 DB 读取到旧值。
    2. 请求 B: 迅速完成了 DB 的更新，并将 Cache 删除。
    3. 请求 A : 将自己之前拿到的旧值写入了 Cache。
  • 结果： DB 中是新值，Cache 中又是旧值。
  • 为什么概率极小？ 这个问题本质上是一个并发时序问题：只要“读 DB → 写 Cache”这段时间窗口内，恰好有写请求完成了 DB 更新，就有可能产生不一致。在大多数业务里，这个窗口时间相对较短，而且还需要与写请求并发“撞车”，所以发生概率不算高，但绝不是不可能。 3. 为什么是“删除 Cache”，而不是“更新 Cache”？
  • 性能开销： 写操作往往只更新了对象的部分字段，如果为了“更新 Cache”而去重新查询或计算整个缓存对象，开销可能很大。相比之下，“删除”是一个轻量级操作。
  • 懒加载思想： “删除”操作遵循懒加载原则。只有当数据下一次被真正需要（被读取）时，才触发从 DB 加载并写入缓存，避免了无效的缓存更新。
  • 并发安全： “更新缓存”在高并发下可能出现更新顺序错乱的问题导致脏数据的概率会更大。

Read/Write Through Pattern（读写穿透）​

应用程序将Cache 视为唯一的、主要的存储。所有的读写请求都直接打向 Cache，而 Cache 服务自身负责与 DB 进行数据同步。性能低，且分布式缓存 Redis 本身并没有提供 Cache 将数据写入 DB 的功能。

Write Behind Pattern（异步缓存写入）​

 Cache 服务来负责 Cache 和 DB 的读写，但是Write Behind 则是只更新缓存，不直接更新 DB，而是改为异步批量的方式来更新 DB。  数据一致性差，不适用于需要强一致性的场景（如交易、库存），但是写性能非常强。  使用场景示例：  - MySQL 的 InnoDB Buffer Pool 机制： 数据修改先在内存 Buffer Pool 中完成，然后由后台线程异步刷写到磁盘。

  • 操作系统的页缓存（Page Cache）： 文件写入也是先写到内存，再由操作系统异步刷盘。
  • 高频计数场景： 对于文章浏览量、帖子点赞数这类允许短暂数据不一致、但写入极其频繁的场景，可以先在 Redis 中快速累加，再通过定时任务异步同步回数据库

第七章 数据库编程​

数据库应用程序框架

数据库应用程序框架

pgSQL 过程语言​

PL/pgSQL 是 PostgreSQL 的过程化编程语言，用于扩展 SQL 功能，支持变量声明、流程控制（如 IF 语句、CASE 语句、循环语句）、复合类型（行类型、RECORD 类型）及异常处理。

  • 变量与类型：支持标量变量、数组、复合类型（如CREATE TYPE student_type AS (id INT, name TEXT)）和 RECORD 动态记录类型，可通过%ROWTYPE引用表结构（如student_record students%ROWTYPE）。
  • 流程控制：
    • 条件判断：IF-ELSIF-ELSE 语句用于分支逻辑（如判断学生性别输出对应信息）。
    • 循环结构：支持 LOOP、WHILE、FOR 循环，可遍历查询结果集（如使用FOR student_record IN SELECT * FROM students LOOP）。
  • 异常处理：通过EXCEPTION块捕获特定异常（如unique_violation），并执行回滚或提示操作。

数据库函数编程​

数据库函数是存储在服务器端的可复用程序，适用于简单的计算、数据转换和过滤等操作，支持参数传递和返回值，分为无参数函数、带参数函数和返回表值的函数。

CREATE [ OR REPLACE ] FUNCTION name
( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
[ RETURNS retype | RETURNS TABLE ( column_name column_type [, ...] ) ]
AS $$ //$$用于声明函数的实际代码的开始
DECLARE
-- 声明段
BEGIN
--函数体语句
END;
$$ LANGUAGE lang_name; //$$ 表明代码的结束, LANGUAGE后面指明所用的编程语言
（1）name：要创建的函数名；  （2）OR REPLACE ：覆盖同名的函数；
（3）argmode：函数参数的模式可以为IN、OUT或INOUT，缺省值是IN。
（4）argname：形式参数的名字。
（5）RETURNS：返回值；RETURNS TABLE：返回二维表

调用
select into 自定义变量 from 函数名（参数）
  • 参数模式：IN（输入）、OUT（输出）、INOUT（输入输出），支持默认参数。
  • 调用方式：可在 SQL 查询中直接调用（如SELECT discount(100.00)）或在存储过程中通过PERFORM调用。

游标编程​

游标（Cursor）是一种临时的数据库对象，包括：SQL 语言的查询结果，指向特定记录的指针，用来存放从数据库表中查询返回的数据记录，允许逐行访问数据，提供了从结果集中提取并分别处理每一条记录的机制，游标总是与一条 SQL 查询语句相关联，适用于需要逐条处理数据的场景（如批量更新或复杂业务逻辑）。

  1. 声明游标：CURSOR curStudent FOR SELECT * FROM student;
  2. 打开游标：OPEN curStudent;
  3. 获取数据：FETCH curStudent INTO sid, sname;
  4. 关闭游标：CLOSE curStudent;
DECLARE
  student_record RECORD;
BEGIN
  FOR student_record IN SELECT * FROM students LOOP
    RAISE NOTICE 'Student: %, %', student_record.sid, student_record.sname;
  END LOOP;
END;

存储过程编程​

存储过程是一组预编译的 SQL 语句集合，当客户端连接到数据库时，用户通过指定存储过程的名字并给出参数，数据库就可以找到相应的存储过程予以调用，支持事务控制和批量操作，预编译能够提升性能，适用于复杂的业务逻辑。但是，存储过程难以调试和扩展，而且没有移植性，还会消耗数据库资源，不推荐使用。

CREATE OR REPLACE PROCEDURE procedureName([IN | OUT | INOUT] Pname dataType, ... )
AS $$
DECLARE
变量1 数据类型 := 初始值1;
变量2 数据类型 := 初始值2;
......
BEGIN
-- 程序执行体语句写在这里;
END;
$$ LANGUAGE plpgsql //指明存储过程结束，告诉编译器使用PL/pgSQL语言实现的
（1）procedureName：存储过程名；  （2）OR REPLACE ：覆盖同名的存储过程；
（3）IN、OUT或INOUT参数模式。IN为输入参数；OUT为输出参数缺省值是IN。
（4）Pname：形式参数的名字。
（5）dataType：该存储过程参数的数据类型。

（1）修改存储过程的名字
ALTER PROCEDURE name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
RENAME TO new_name;
（2）修改存储过程的所有者
ALTER PROCEDURE name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
OWNER TO new_owner;
（3）修改存储过程所属模式
ALTER PROCEDURE name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
SET SCHEMA new_schema;

删除
DROP PROCEDURE [ IF EXISTS ] name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] [, ...];

需要使用 EXEC 或 EXECUTE 关键字来调用

触发器编程​

触发器器是特殊类型的存储过程，主要由操作事件(INSERT、UPDATE、DELETE) 触发而被自动执行。可以实现比约束更复杂的数据完整性，经常用于加强数据的完整性约束和业务规则。 触发器本身是一个特殊的事务单位是绑定在表上的自动执行代码，不能直接调用，也不能传递或接受参数，自动响应 INSERT、UPDATE、DELETE 事件。

  • 语句级触发器：每条 SQL 语句触发一次（如FOR EACH STATEMENT）。
  • 行级触发器：每行数据变更触发一次（如FOR EACH ROW）。
  • 触发时机：BEFORE（前触发）、AFTER（后触发）、INSTEAD OF（替代触发）。
CREATE TRIGGER 触发器名
{ BEFORE | AFTER | INSTEAD OF }
ON 表名
[ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE 存储过程名 ( 参数列表 )
（1）指明所定义的触发器名
（2） BEFORE | AFTER | INSTEAD OF 指明触发器被触发的时间
（3） ON 表名 指明触发器所依附的表
（4） FOR EACH { ROW | STATEMENT } 指明触发器被触发的次数
（5） EXECUTE PROCEDURE 存储过程名 ( 参数列表 ) 指明触发时所执行的存储过程

使用 JDBC 访问数据库​

JDBC（Java Database Connectivity）是 Java 提供的标准化数据库访问接口，通过驱动程序（如 PostgreSQL 的org.postgresql.Driver）实现 Java 应用与数据库的连接、数据操作和结果处理。其核心架构分为三层：

  1. JDBC API：提供连接（Connection）、语句执行（Statement/PreparedStatement）、结果集（ResultSet）等接口。
  2. 驱动管理器（DriverManager）：管理数据库驱动的注册与连接创建。
  3. 数据库驱动：实现 JDBC 接口，与具体数据库通信（如 PostgreSQL 驱动负责解析 SQL 并与数据库交互）。

使用步骤​

import java.sql.*;

/**
 * 整合所有 JDBC 操作步骤的完整示例
 * 功能：演示加载驱动、建立连接、执行 SQL、处理结果、释放资源的完整流程
 */
public class JdbcCompleteExample {
    public static void main(String[] args) {
        // 数据库连接信息
        String url = "jdbc:postgresql://localhost:5432/testDB";
        String username = "myuser";
        String password = "sa";

        // 步骤1：加载驱动程序（Java 6+ 可省略，驱动会自动注册）
        // 显式加载仅作演示，实际开发中可注释掉
        try {
            Class.forName("org.postgresql.Driver");
            System.out.println("驱动加载成功");
        } catch (ClassNotFoundException e) {
            System.err.println("驱动加载失败：" + e.getMessage());
            return;
        }

        // 步骤2：建立数据库连接 + 步骤3：执行SQL + 步骤4：处理结果集 + 步骤5：释放资源
        // 使用 try-with-resources 自动关闭 Connection/Statement/ResultSet，无需手动 close
        try (
            // 步骤2：建立数据库连接
            Connection conn = DriverManager.getConnection(url, username, password);

            // 示例1：使用 Statement 执行静态 SQL（插入操作）
            Statement stmt = conn.createStatement();

            // 示例2：使用 PreparedStatement 执行参数化 SQL（查询操作）
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM students WHERE sid = ?")
        ) {
            System.out.println("数据库连接成功");

            // ------------------------------
            // 步骤3-1：使用 Statement 执行插入 SQL
            // ------------------------------
            String insertSql = "INSERT INTO students (sid, sname) VALUES ('14101', '张三')";
            int affectedRows = stmt.executeUpdate(insertSql);
            System.out.println("插入操作影响行数：" + affectedRows);

            // ------------------------------
            // 步骤3-2：使用 PreparedStatement 执行查询 SQL（参数化，防 SQL 注入）
            // ------------------------------
            ps.setString(1, "14101"); // 设置参数（索引从 1 开始）
            ResultSet rs = ps.executeQuery(); // 执行查询，返回结果集

            // ------------------------------
            // 步骤4：处理结果集
            // ------------------------------
            System.out.println("查询结果：");
            while (rs.next()) { // 遍历结果集（rs.next() 移动到下一行，无数据时返回 false）
                String sid = rs.getString("sid");       // 通过字段名获取字符串类型值
                String sname = rs.getString("sname");   // 通过字段名获取姓名
                int age = rs.getInt("age");             // 通过字段名获取整数类型年龄（若字段为 null 会返回 0）
                // 也可通过列索引获取：rs.getString(1)（对应 SELECT 第1列）、rs.getInt(3)（对应 SELECT 第3列）
                System.out.printf("学号：%s，姓名：%s，年龄：%d%n", sid, sname, age);
            }

        } catch (SQLException e) {
            // 捕获所有 JDBC 异常（连接/执行/结果处理失败）
            System.err.println("JDBC 操作异常：" + e.getMessage());
            e.printStackTrace();
        }
        // 步骤5：资源释放（try-with-resources 已自动完成，无需手动关闭）
        System.out.println("操作完成，资源已自动释放");
    }
}

嵌入式 SQL 数据库访问编程​

嵌入式 SQL 是将 SQL 语句直接嵌入宿主语言（如 Java、C、COBOL）的编程方式，通过专门的预处理器（Preprocessor）先将嵌入的 SQL 语句转换成宿主语言可识别的函数 / 方法调用，再编译执行，实现数据库操作与应用逻辑的融合。开发流程复杂，强耦合，不够灵活，维护成本太高了，拉得没边。 预编译处理：预处理器扫描源程序，识别嵌入式 SQL 语句（如EXEC SQL SELECT ... INTO ...），将其转换为宿主语言的函数调用（如 JDBC 的executeQuery）。

EXEC SQL BEGIN DECLARE SECTION; // 声明主变量
String sid;
String sname;
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT sname INTO :sname FROM students WHERE sid = :sid; // 嵌入式SQL
加载评论中...