数据库学习笔记

置顶呦，又写BUG呢

已于 2024-04-05 17:21:47 修改

阅读量1.1k

点赞数 1

分类专栏：数据库文章标签：数据库

于 2022-01-16 09:24:41 首次发布

本文链接：https://blog.csdn.net/qq_43686863/article/details/122490192

版权

数据库专栏收录该内容

13 篇文章 0 订阅

订阅专栏

文章目录

一、关系数据库理论
二、数据库基础
三、查询
四、数据库对象
五、函数依赖与范式理论
六、事务及其并发控制
- 6.1 事务的 ACID 特性
- 6.2 锁与事务隔离
七、MVCC
- 7.1 MVCC 主要思路
- 7.2 InnoDB 中的 MVCC 实现

一、关系数据库理论

1.1 关系代数定义

域是一组具有相同类型的值的集合，即每个属性的取值范围。
以两个集合为例，两个集合 $X$ 和 $Y$ 的笛卡尔积表示为 $\times Y$ ，即第一个对象是 $X$ 的成员而第二个对象是 $Y$ 的成员的所有可能有序对的其中一个，表达式为 $A×B = \{(x,y)|x∈A∧y∈B\}$ 。参与笛卡尔积的域的个数称为度，每个域中可取值的个数称为这个域的基，笛卡尔积的基是各个域的基相乘。
关系是笛卡儿积的有一定意义的、有限的子集，所以关系也是一个二维表。

1.2 关系代数的基本术语

关系就是一张二维表，一行称为一个元组或一条记录，一列叫做一个属性或一个字段，属性的取值叫做域。
如果一个属性（或属性集合）的值能唯一地标识一个关系的元组而其真子集不能，则称该属性（或属性集合）为关系的候选码，也称键，候选码中的属性称为主属性，不包含在任何候选码中的属性称为非主属性。
若一个关系有多个候选码，则选定其中一个为主码，也即主键。
如果关系模式的所有属性是这个关系模式的候选码，则称为全码。
设 $F$ 是基本关系 $R$ 中的属性（或属性集合），但不是关系 $R$ 的码，如果 $F$ 与基本关系 $S$ 的主码相对应，则称 $F$ 是基本关系 $R$ 的外码，也即外键。关系 $R$ 和 $S$ 不一定是不同的关系。

1.3 数据库中的连接运算

数据库中的连接运算

二、数据库基础

2.1 SQL

2.1.1 DDL（Data Definition Language）

DDL（Data Definition Language）是用于定义和管理数据库结构的语言，主要包括以下主要命令和操作：

CREATE：用于创建数据库对象，如表、索引、视图、存储过程等。
ALTER：用于修改数据库对象的结构，例如添加、修改或删除表的列，修改表的约束等。
DROP：用于删除数据库对象，包括删除表、索引、视图等，在删除表时不仅会清空数据还会删除表结构。
TRUNCATE：用于删除表中的所有数据，同时重置自增长计数器，但保留表的结构，在清空表中数据的时候使用。
RENAME：用于重命名数据库对象，如表、列等。

对于删除操作，DROP、TRUNCATE 和 DELETE 均可以完成对表的删除：

DROP 用于删除数据库对象，例如表、索引、视图等。它会完全删除这个对象，包括表的结构和数据，以及相关的约束和索引。
TRUNCATE 用于删除表中的数据，但保留表的结构。它会迅速删除表中的所有数据行，重置自增长列的计数器，但不删除表的结构、索引或约束。 TRUNCATE 是一个更快速的删除操作，适用于需要清空表数据但保留表结构的情况。
DELETE 也可以做到仅删除数据，但 TRUNCATE 更快，因为它不需要逐行删除数据，而只是将表的数据页标记为可重用状态。不过相比于 DROP 和 TRUNCATE，由于 DELETE 是 DML，所以删除的数据将存储在系统回滚段，支持在必要的时候回滚。

通过 TRUNCATE 删除数据并重置自增长计数器：

DROP TABLE IF EXISTS example;

CREATE TABLE example
(
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO example (name) VALUES ('John');
INSERT INTO example (name) VALUES ('Alice');
INSERT INTO example (name) VALUES ('Bob');
SELECT * FROM example;
# +--+-----+
# |id|name |
# +--+-----+
# |1 |John |
# |2 |Alice|
# |3 |Bob  |
# +--+-----+

# 使用 TRUNCATE TABLE 清空 "example" 表中的所有数据，并重置自增长计数器
TRUNCATE TABLE example;
INSERT INTO example (name) VALUES ('Eve');
SELECT * FROM example;
# +--+----+
# |id|name|
# +--+----+
# |1 |Eve |
# +--+----+

2.1.2 DML（Data Manipulation Language）

DML（Data Manipulation Language）是用于操作数据库中的数据的语言，主要包括以下主要命令和操作：

INSERT：用于将新数据插入到表中。
DELETE：用于从表中删除数据。
UPDATE：用于修改表中已存在的数据。
SELECT：用于从数据库中检索数据，执行查询操作。

2.1.3 DCL（Data Control Language）

DCL（Data Control Language）主要用于管理数据库的访问权限和安全性，主要包括以下两个命令：

GRANT：用于向用户或用户组授予特定的数据库操作权限，例如 SELECT、INSERT、UPDATE、DELETE 等。GRANT 语句可以指定授权级别、对象（表或视图等）以及被授权的用户或用户组。
REVOKE：用于从用户或用户组收回已授予的数据库操作权限，REVOKE 语句指定了要回收的权限、对象和用户或用户组。

2.1.4 TCL（Transaction Control Language）

TCL（Transaction Control Language）用于管理数据库事务，主要包括以下三个命令：

COMMIT：用于提交事务，将事务中的所有操作永久保存到数据库中，一旦提交，事务将不可逆转。
ROLLBACK：用于回滚事务，将事务中的所有操作撤销，回滚到事务开始之前的状态。
SAVEPOINT：用于设置保存点，以便在事务中的特定位置之后可以回滚到该位置。

2.2 DATE、DATETIME 和 TIMESTAMP

DATE、DATETIME 和 TIMESTAMP 是 MySQL 中用于存储日期和时间信息的不同数据类型，它们用于满足不同的需求。

DATE 类型用于存储日期信息，但不包括时间，格式为 yyyy-MM-dd。适用于需要存储日期而不涉及具体时间的情况，如生日、日期范围等。
DATETIME 类型用于存储日期和时间信息，格式为 yyyy-MM-dd HH:mm:ss。适用于需要同时存储日期和时间的情况，如记录事件发生时间等。
TIMESTAMP 数据类型也用于存储日期和时间信息，但与 DATETIME 不同的是，TIMESTAMP 的值在插入或更新时会受到 MySQL 服务器的时区设置影响。因此 TIMESTAMP 通常用于记录时间戳，以及跟踪数据的创建和修改时间。

DROP TABLE IF EXISTS events;

CREATE TABLE events
(
    event_id        INT PRIMARY KEY AUTO_INCREMENT,
    event_name      VARCHAR(50) NOT NULL,
    event_date      DATE,
    event_datetime  DATETIME,
    event_timestamp TIMESTAMP
);

INSERT INTO events (event_name, event_date, event_datetime, event_timestamp)
VALUES ('Meeting', NOW(), NOW(), NOW()),
       ('Birthday Party', NOW(), NOW(), NOW()),
       ('Conference', NOW(), NOW(), NOW());

SELECT * FROM events;
# +--------+--------------+----------+-------------------+-------------------+
# |event_id|event_name    |event_date|event_datetime     |event_timestamp    |
# +--------+--------------+----------+-------------------+-------------------+
# |1       |Meeting       |2023-09-25|2023-09-25 23:25:29|2023-09-25 23:25:29|
# |2       |Birthday Party|2023-09-25|2023-09-25 23:25:29|2023-09-25 23:25:29|
# |3       |Conference    |2023-09-25|2023-09-25 23:25:29|2023-09-25 23:25:29|
# +--------+--------------+----------+-------------------+-------------------+a

2.3 数据库架构

下图为 CMU 数据库课程组开源的关系型数据库 BusTub 整体架构：

在这里插入图片描述

三、查询

3.1 模糊查询

LIKE 操作符可以匹配一个指定模式的文本，它通常与通配符 % 和 _ 一起使用：

%：匹配零个或多个字符。
_：匹配一个字符。
\：用于进行转义。

DROP TABLE IF EXISTS student;

CREATE TABLE student
(
    student_id   INT PRIMARY KEY,
    student_name VARCHAR(50) UNIQUE,
    gender       ENUM ('Male', 'Female') DEFAULT 'Male',
    major        VARCHAR(50) NOT NULL,
    birthdate    DATE        NOT NULL
);

INSERT INTO student (student_id, student_name, gender, major, birthdate)
VALUES (1, 'Alice', 'Female', 'Computer Science', '2000-05-15'),
       (2, 'Andrew', 'Male', 'Engineering', '1999-09-20'),
       (3, 'Maria', 'Female', 'Mathematics', '2001-03-10'),
       (4, 'Samantha', 'Female', 'Physics', '2002-01-25'),
       (5, 'Ana', 'Female', 'Chemistry', '1998-11-05'),
       (6, 'A_', 'Male', 'Biology', '1997-06-30'),
       (7, 'AA_', 'Female', 'History', '1996-04-15');

# 返回所有以字母 "A" 或 "a" 开头的学生名字，如 "Alice" 和 "Andrew"
SELECT *
FROM student
WHERE student_name LIKE 'A%';
# +----------+------------+------+----------------+----------+
# |student_id|student_name|gender|major           |birthdate |
# +----------+------------+------+----------------+----------+
# |6         |A_          |Male  |Biology         |1997-06-30|
# |7         |AA_         |Female|History         |1996-04-15|
# |1         |Alice       |Female|Computer Science|2000-05-15|
# |5         |Ana         |Female|Chemistry       |1998-11-05|
# |2         |Andrew      |Male  |Engineering     |1999-09-20|
# +----------+------------+------+----------------+----------+

# 返回所有名字中包含字母 "A" 或 "a" 的学生名字，如 "Maria" 和 "Samantha"
SELECT *
FROM student
WHERE student_name LIKE '%A%';
# +----------+------------+------+----------------+----------+
# |student_id|student_name|gender|major           |birthdate |
# +----------+------------+------+----------------+----------+
# |1         |Alice       |Female|Computer Science|2000-05-15|
# |2         |Andrew      |Male  |Engineering     |1999-09-20|
# |3         |Maria       |Female|Mathematics     |2001-03-10|
# |4         |Samantha    |Female|Physics         |2002-01-25|
# |5         |Ana         |Female|Chemistry       |1998-11-05|
# |6         |A_          |Male  |Biology         |1997-06-30|
# |7         |AA_         |Female|History         |1996-04-15|
# +----------+------------+------+----------------+----------+

# 返回所有名字中包倒数第三个字符是 "A" 或 "a" 的学生名字，如 "Ana"
SELECT *
FROM student
WHERE student_name LIKE '%A__';
# +----------+------------+------+---------+----------+
# |student_id|student_name|gender|major    |birthdate |
# +----------+------------+------+---------+----------+
# |5         |Ana         |Female|Chemistry|1998-11-05|
# |7         |AA_         |Female|History  |1996-04-15|
# +----------+------------+------+---------+----------+

# 返回所有以 "A_" 或 "a_" 开头的学生名字，如 "A_"
SELECT *
FROM student
WHERE student_name LIKE 'A\_%';
# +----------+------------+------+-------+----------+
# |student_id|student_name|gender|major  |birthdate |
# +----------+------------+------+-------+----------+
# |6         |A_          |Male  |Biology|1997-06-30|
# +----------+------------+------+-------+----------+

3.2 聚合函数

聚合函数用于对表中的数据进行聚合计算，这些函数可以用于执行诸如求和、计数、平均值、最大值和最小值等操作。

聚合函数	功能
`COUNT()`	计算所选数据（记录）的个数
`SUM()`	计算某一数值列的和
`AVG()`	计算某一数值列的平均值
`MAX()`	求（字符、日期、数值列）的最大值
`MIN()`	求（字符、日期、数值列）的最小值

3.3 分组查询

分组查询是通过使用 GROUP BY 子句来实现的，它允许将查询结果按照一个或多个列的值进行分组，并对每个分组应用聚合函数来执行计算。

DROP TABLE IF EXISTS student;

CREATE TABLE student
(
    student_id   INT PRIMARY KEY,
    student_name VARCHAR(50) UNIQUE,
    gender       ENUM ('Male', 'Female') DEFAULT 'Male',
    major        VARCHAR(50) NOT NULL,
    birthdate    DATE        NOT NULL
);

INSERT INTO student (student_id, student_name, gender, major, birthdate)
VALUES (1, 'Alice', 'Female', 'Computer Science', '2000-05-15'),
       (2, 'Andrew', 'Male', 'Engineering', '1999-09-20'),
       (3, 'Maria', 'Female', 'Mathematics', '2001-03-10'),
       (4, 'Samantha', 'Female', 'Physics', '2002-01-25'),
       (5, 'Ana', 'Female', 'Chemistry', '1998-11-05'),
       (6, 'A_', 'Male', 'Biology', '1997-06-30'),
       (7, 'AA_', 'Female', 'History', '1996-04-15');

# 按性别分组，并计算每个性别的学生数目
SELECT gender, COUNT(*) AS student_count
FROM student
GROUP BY gender;
# +------+-------------+
# |gender|student_count|
# +------+-------------+
# |Female|5            |
# |Male  |2            |
# +------+-------------+

# 按专业分组，并计算每个专业的平均年龄
SELECT major, AVG(YEAR(NOW()) - YEAR(birthdate)) AS average_age
FROM student
GROUP BY major;
# +----------------+-----------+
# |major           |average_age|
# +----------------+-----------+
# |Computer Science|23.0000    |
# |Engineering     |24.0000    |
# |Mathematics     |22.0000    |
# |Physics         |21.0000    |
# |Chemistry       |25.0000    |
# |Biology         |26.0000    |
# |History         |27.0000    |
# +----------------+-----------+

# 按性别和专业分组，并计算每个性别每个专业的学生数目
SELECT gender, major, COUNT(*) AS student_count
FROM student
GROUP BY gender, major;
# +------+----------------+-------------+
# |gender|major           |student_count|
# +------+----------------+-------------+
# |Female|Computer Science|1            |
# |Male  |Engineering     |1            |
# |Female|Mathematics     |1            |
# |Female|Physics         |1            |
# |Female|Chemistry       |1            |
# |Male  |Biology         |1            |
# |Female|History         |1            |
# +------+----------------+-------------+

WHERE 和 HAVING 是用于筛选数据的两个不同的子句，它们通常与 SELECT 语句一起使用，但用途略有不同：

WHERE 子句用于在检索数据之前对表中的行进行过滤。它通常用于筛选行，使得只有满足特定条件的行会包含在查询结果中。WHERE 子句可以用于筛选表中的数据，而不需要进行聚合操作。
HAVING 子句通常与 GROUP BY 子句一起使用，用于在聚合数据之后对分组进行过滤。它通常用于筛选分组，使得只有满足特定条件的分组会包含在查询结果中。HAVING 子句用于筛选聚合函数的结果，而不是原始表中的数据。

DROP TABLE IF EXISTS student;

CREATE TABLE student
(
    student_id   INT PRIMARY KEY,
    student_name VARCHAR(50) UNIQUE,
    gender       ENUM ('Male', 'Female') DEFAULT 'Male',
    major        VARCHAR(50) NOT NULL,
    birthdate    DATE        NOT NULL
);

INSERT INTO student (student_id, student_name, gender, major, birthdate)
VALUES (1, 'Alice', 'Female', 'Computer Science', '2000-05-15'),
       (2, 'Andrew', 'Male', 'Engineering', '1999-09-20'),
       (3, 'Maria', 'Female', 'Mathematics', '2001-03-10'),
       (4, 'Samantha', 'Female', 'Physics', '2002-01-25'),
       (5, 'Ana', 'Female', 'Chemistry', '1998-11-05'),
       (6, 'A_', 'Male', 'Biology', '1997-06-30'),
       (7, 'AA_', 'Female', 'History', '1996-04-15');

-- 使用 WHERE 子句筛选年龄大于 20 岁的学生，并按性别分组
SELECT gender, COUNT(*) AS student_count
FROM student
WHERE YEAR(NOW()) - YEAR(birthdate) > 23
GROUP BY gender;
# +------+-------------+
# |gender|student_count|
# +------+-------------+
# |Male  |2            |
# |Female|2            |
# +------+-------------+

-- 将学生按性别分组，然后通过 HAVING 子句筛选出学生数大于 2 的性别分组
SELECT gender, COUNT(*) AS student_count
FROM student
GROUP BY gender
HAVING student_count > 2;
# +------+-------------+
# |gender|student_count|
# +------+-------------+
# |Female|5            |
# +------+-------------+

3.4 排序

使用 ORDER BY 排序时，主排序在前，次排序在后，只有当主排序字段相等时次排序才会起作用。

DROP TABLE IF EXISTS student;

CREATE TABLE student
(
    student_id   INT PRIMARY KEY,
    student_name VARCHAR(50) UNIQUE,
    gender       ENUM ('Male', 'Female') DEFAULT 'Male',
    major        VARCHAR(50) NOT NULL,
    birthdate    DATE        NOT NULL
);

INSERT INTO student (student_id, student_name, gender, major, birthdate)
VALUES (1, 'Alice', 'Female', 'Computer Science', '2000-05-15'),
       (2, 'Andrew', 'Male', 'Engineering', '1999-09-20'),
       (3, 'Maria', 'Female', 'Mathematics', '2001-03-10'),
       (4, 'Samantha', 'Female', 'Physics', '2002-01-25'),
       (5, 'Ana', 'Female', 'Chemistry', '1998-11-05'),
       (6, 'A_', 'Male', 'Biology', '1997-06-30'),
       (7, 'AA_', 'Female', 'History', '1996-04-15');

-- 按学号降序生日升序对数据进行排序
SELECT *
FROM student
ORDER BY student_id DESC, birthdate ASC;
# +----------+------------+------+----------------+----------+
# |student_id|student_name|gender|major           |birthdate |
# +----------+------------+------+----------------+----------+
# |7         |AA_         |Female|History         |1996-04-15|
# |6         |A_          |Male  |Biology         |1997-06-30|
# |5         |Ana         |Female|Chemistry       |1998-11-05|
# |4         |Samantha    |Female|Physics         |2002-01-25|
# |3         |Maria       |Female|Mathematics     |2001-03-10|
# |2         |Andrew      |Male  |Engineering     |1999-09-20|
# |1         |Alice       |Female|Computer Science|2000-05-15|
# +----------+------------+------+----------------+----------+

3.5 子查询

子查询是嵌套在其他查询中的 SQL 查询，也称内部查询，包含子查询的语句也称为外部查询。简单来说，子查询就是指将一个 SELECT 查询（子查询）的结果作为另一个 SQL 语句（主查询）的数据来源或者判断条件。

DROP TABLE IF EXISTS student;

CREATE TABLE student
(
    student_id   INT PRIMARY KEY,
    student_name VARCHAR(50) UNIQUE,
    gender       ENUM ('Male', 'Female') DEFAULT 'Male',
    major        VARCHAR(50) NOT NULL,
    birthdate    DATE        NOT NULL
);

INSERT INTO student (student_id, student_name, gender, major, birthdate)
VALUES (1, 'Alice', 'Female', 'Computer Science', '2000-05-15'),
       (2, 'Andrew', 'Male', 'Engineering', '1999-09-20'),
       (3, 'Maria', 'Female', 'Mathematics', '2001-03-10'),
       (4, 'Samantha', 'Female', 'Physics', '2002-01-25'),
       (5, 'Ana', 'Female', 'Chemistry', '1998-11-05'),
       (6, 'A_', 'Male', 'Biology', '1997-06-30'),
       (7, 'AA_', 'Female', 'History', '1996-04-15');

# 查找年龄最小的学生的姓名和出生日期
SELECT student_name, birthdate
FROM student
WHERE birthdate = (SELECT MAX(birthdate)
                   FROM student);
# +------------+----------+
# |student_name|birthdate |
# +------------+----------+
# |Samantha    |2002-01-25|
# +------------+----------+

# 查询每个专业的人数
SELECT major,
       (SELECT COUNT(*)
        FROM student s
        WHERE s.major = student.major) AS student_count
FROM student
GROUP BY major;
# +----------------+-------------+
# |major           |student_count|
# +----------------+-------------+
# |Computer Science|1            |
# |Engineering     |1            |
# |Mathematics     |1            |
# |Physics         |1            |
# |Chemistry       |1            |
# |Biology         |1            |
# |History         |1            |
# +----------------+-------------+

# 查找与 Alice 性别相同的学生
SELECT student_name, gender
FROM student
WHERE gender = (SELECT gender
               FROM student
               WHERE student_name = 'Alice');
# +------------+------+
# |student_name|gender|
# +------------+------+
# |Alice       |Female|
# |Maria       |Female|
# |Samantha    |Female|
# |Ana         |Female|
# |AA_         |Female|
# +------------+------+

3.6 查询优化

查询优化

3.7 深度分页

MySQL 深度分页

四、数据库对象

4.1 索引

索引

4.2 视图

视图

4.3 存储过程

存储过程是由过程化 SQL 语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，因此称它为存储过程，使用时只要调用即可。

DROP TABLE IF EXISTS student;
DROP PROCEDURE IF EXISTS InsertStudents;

CREATE TABLE student
(
    student_id   INT PRIMARY KEY,
    student_name VARCHAR(50) UNIQUE,
    gender       ENUM ('Male', 'Female') DEFAULT 'Male',
    major        VARCHAR(50) NOT NULL,
    birthdate    DATE        NOT NULL
);

# 用于插入一千条数据的存储过程
CREATE PROCEDURE InsertStudents()
BEGIN
    DECLARE counter INT DEFAULT 1;

    WHILE counter <= 1000
        DO
            INSERT INTO student (student_id, student_name, gender, major, birthdate)
            VALUES (counter, CONCAT('Student', counter), IF(counter % 2 = 0, 'Male', 'Female'), 'Computer Science',
                    NOW());
            SET counter = counter + 1;
        END WHILE;
END;

# 执行存储过程
CALL InsertStudents;

SELECT *
FROM student;
# +----------+------------+------+----------------+----------+
# |student_id|student_name|gender|major           |birthdate |
# +----------+------------+------+----------------+----------+
# |1         |Student1    |Female|Computer Science|2023-09-24|
# |2         |Student2    |Male  |Computer Science|2023-09-24|
# |3         |Student3    |Female|Computer Science|2023-09-24|
# |4         |Student4    |Male  |Computer Science|2023-09-24|
# |5         |Student5    |Female|Computer Science|2023-09-24|
# |6         |Student6    |Male  |Computer Science|2023-09-24|
# |7         |Student7    |Female|Computer Science|2023-09-24|
# |8         |Student8    |Male  |Computer Science|2023-09-24|
# |9         |Student9    |Female|Computer Science|2023-09-24|
# |10        |Student10   |Male  |Computer Science|2023-09-24|
# |11        |Student11   |Female|Computer Science|2023-09-24|
# ...
# |996       |Student996  |Male  |Computer Science|2023-09-24|
# |997       |Student997  |Female|Computer Science|2023-09-24|
# |998       |Student998  |Male  |Computer Science|2023-09-24|
# |999       |Student999  |Female|Computer Science|2023-09-24|
# |1000      |Student1000 |Male  |Computer Science|2023-09-24|
# +----------+------------+------+----------------+----------+

4.4 触发器

触发器用于在特定数据库事件发生时自动触发执行一系列 SQL 语句或存储过程。触发器通常与 INSERT、UPDATE、DELETE 等操作相关联，允许在数据库操作前或后执行自定义的逻辑。

行级触发器和语句级触发器：

行级触发器的每一次执行对应一条记录的修改，而且行级触发器可以通过 NEW.属性 和 OLD.属性 获得记录修改前和修改后的属性值。
语句级触发器的每一次执行对应一条语句的执行。

DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS student_log;
DROP PROCEDURE IF EXISTS StudentUpdateTrigger;

CREATE TABLE student
(
    student_id   INT PRIMARY KEY,
    student_name VARCHAR(50) UNIQUE,
    gender       ENUM ('Male', 'Female') DEFAULT 'Male',
    major        VARCHAR(50) NOT NULL,
    birthdate    DATE        NOT NULL
);

INSERT INTO student (student_id, student_name, gender, major, birthdate)
VALUES (1, 'Alice', 'Female', 'Computer Science', '2000-05-15'),
       (2, 'Andrew', 'Male', 'Engineering', '1999-09-20'),
       (3, 'Maria', 'Female', 'Mathematics', '2001-03-10'),
       (4, 'Samantha', 'Female', 'Physics', '2002-01-25'),
       (5, 'Ana', 'Female', 'Chemistry', '1998-11-05'),
       (6, 'A_', 'Male', 'Biology', '1997-06-30'),
       (7, 'AA_', 'Female', 'History', '1996-04-15');

CREATE TABLE student_log
(
    action VARCHAR(1000)
);

# 用于更新操作日志的触发器
CREATE TRIGGER StudentUpdateTrigger
    BEFORE UPDATE
    ON student
    FOR EACH ROW
BEGIN
    INSERT INTO student_log
    VALUES (CONCAT('Old Student Name: ', OLD.student_name,
                   ', Old Major: ', OLD.major,
                   ', New Student Name: ', NEW.student_name,
                   ', New Major: ', NEW.major,
                   '.'));
END;

UPDATE student
SET student_name = 'Updated Name',
    major        = 'Updated Major'
WHERE student_id = 1;

SELECT *
FROM student_log;
# +---------------------------------------------------------------------------------------------------------------+
# |action                                                                                                         |
# +---------------------------------------------------------------------------------------------------------------+
# |Old Student Name: Alice, Old Major: Computer Science, New Student Name: Updated Name, New Major: Updated Major.|
# +---------------------------------------------------------------------------------------------------------------+

4.5 日志

4.5.1 undo log（回滚日志）

undo log 用于保证事务的原子性。事务还未提交时，InnoDB 就会把已经发生的修改记录到 undo log 中，同时由于数据库缓冲池的存在， undo log 会先于数据被持久化，这样如果在事务提交前或事务提交时数据库发生故障，通过 undo log 即可将数据恢复。

由于 MySQL 的自动事务提交，除了通过 begion 或 start transaction 声明的事务，每条 insert、delete 以及 update 的执行都对应着事务的开始和提交。举例来说，如果我们插入了一条数据，那么就可以将这条数据的主键值记录在 undo log 中，这样回滚时只需要把这个主键值对应的记录删掉就好了。同样的，对于删除和更新操作我们也可以记录修改前的旧记录值，并在回滚时对相应记录进行复原。

SHOW VARIABLES LIKE 'autocommit';
# +-------------+-----+
# |Variable_name|Value|
# +-------------+-----+
# |autocommit   |ON   |
# +-------------+-----+

此外，undo log 还用于实现 MVCC，undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

4.5.2 redo log（重做日志）

redo log 用于保证事务的持久性。redo log 记录了事务修改后的数据，它使得 InnoDB 可以在事务提交后优先把 redo log 持久化，而暂时不持久化脏数据页。当数据库系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，MySQL 重启后可以根据 redo log 的内容，将所有数据恢复到最新的状态。

这种先写日志后写数据的的技术被称为预写式日志（Write-Ahead Logging，WAL），之所以采用 WAL 是因为 redo log 的写入是通过追加来实现的，所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，磁盘操作是随机写。磁盘顺序写比随机写高效的多，因此 redo log 写入磁盘的开销更小，进而提升了数据库的执行效率。

当然，redo log 产生后也不会立即写入磁盘，因为这会产生大量的 I/O 操作，同时内外存的速度也有着较大差异。实际上，InnoDB 的后台线程会通过单独的 redo log buffer 内存缓冲区在缓冲区内容超过一半时、事务提交时或定时将 redo log 写到磁盘，具体策略由 innodb_flush_log_at_trx_commit 参数控制。

undo log 和 redo log 看似记录的都是与修改相关的数据，但实际上二者有很大不用：

首先，undo log 记录的是事务提交前的旧数据，它使得事务失败时可以对数据进行回滚，保证了事务的原子性。而 redo log 记录的则是事务提交后的新数据，它使得事务只要成功提交，那么数据就一定能够被持久化，保证了事务的持久性。
其次，undo log 本质上也是一个数据页，也位于数据库缓冲池中，也需要通过 redo log 保证持久化。而 redo log 有自己的缓冲池，会单独进行持久化。

4.5.3 binlog （归档日志）

binlog 主要用于备份恢复和主从复制。binlog 记录的也是数据库中的修改，但与 undo log 和 redo log 不同的是，binlog 属于 MySQL 的 Server 层，因此适用于所有的数据库引擎，而 undo log 和 redo log 是 InnoDB 所独有的。

此外，binlog 还支持三种不同的日志格式，可以使用 SET GLOBAL binlog_format = '参数'; 来指定：

STATEMENT（语句格式）：这是默认的 binlog 格式。在此格式下，binlog 将记录实际执行的 SQL 语句，这对于简单的事务非常有效。但在涉及到 NOW() 等动态函数时，由于动态函数每次的执行结果都不相同，因此可能会导致复制时的数据不一致。
ROW（行格式）：在此格式下，binlog 将记录数据行的更改，而不是 SQL 语句本身。行格式避免了动态函数导致的不一致问题，但是由于每条修改的数据都需要被记录，这会使得 binlog 过大。
MIXED（混合格式）：在此格式下，MySQL会根据情况选择语句格式或行格式。通常，它会尝试使用语句格式，但对于无法安全地使用语句格式（如存在动态函数）的情况下使用行格式。这是一种折衷的选择，通常可以在大多数情况下提供较好的性能和可靠性。

4.5.4 两阶段提交

事务提交后，redo log 和 binlog 都要持久化到磁盘，但二者的持久化是相互独立的，这就很有可能出现二者之间的不一致，而且 binlog 还对应着从库中的数据，二者的不一致还会导致主从不一致。MySQL 为了解决这个问题，提出了两阶段提交。

prepare 阶段：在此阶段，MySQL 会把事务操作记录到 redo log 中并将其标记为 prepare 状态。
commit 阶段：在此阶段，MySQL 会把事务操作记录到 binlog 中并将 redo log 标记为 commit 状态。

这样，不管数据库在哪个阶段崩溃，数据库重启后首先都会检查 redo log 日志。如果 redo log 被标记为 commit 状态，这表明数据库在 commit 阶段执行成功后才崩溃，此时直接提交数据即可，如果 redo log 被标记为 prepare 状态，那么就根据 redo log 中的事务 id 去 binlog 中查找，如果查询成功，表明 binlog 也完成了更新，直接提交数据即可，如果查询失败，表明 binlog 还未完成更新，为了保证主从数据库的一致性，需要对数据进行回滚。

为了提高写入性能和降低磁盘 I/O 开销，数据库可能会采用组提交的方式，即将多个事务的写入操作合并或批量提交。如果采用组提交，commit 阶段会被分为三个步骤：

flush 阶段：多个事务按进入的顺序将 binlog 缓存至内核缓冲区。
sync 阶段：多个事务的 binlog 合并一次持久化到磁盘。
commit 阶段：各个事务按顺序对 redo log 做 commit 操作。

五、函数依赖与范式理论

六、事务及其并发控制

事务（Transaction）用于确保数据库操作的一致性和完整性，它是一组 SQL 操作，它们被视为一个单独的工作单元，要么全部成功执行，要么全部失败回滚。

6.1 事务的 ACID 特性

关系型数据库中的事务都具有 ACID 特性，其中一致性是目的，其它三个是手段。

原子性（Atomicity）是指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。
一致性（Consistency）是指执行的结果必须使数据库从一个一致性状态转换到另外一个一致性状态，一致性与原子性密切相关。
隔离性（Isolation）是指一个事务的执行不能被其他事务干扰，即一个事务的内部操作及使用的数据对其他并发事务是隔离的。
持续性（Durability）是指一个事务一旦被提交，它对数据库中数据的改变就应该是永久性的，接下来的其他操作或故障不应该对其执行结果有任何影响。

6.2 锁与事务隔离

锁与事务隔离

七、MVCC

多版本并发控制（Multi-Version Concurrency Control，MVCC）主要用于控制并发事务。MVCC 通过在数据库中维护多个版本的数据来允许多个事务同时进行读和写操作，而不会出现冲突。

7.1 MVCC 主要思路

普通 SELECT 读操作（不包括 SELECT ... FOR SHARE 和 SELECT ... FOR UPDATE）：在读已提交级别下，可以通过快照读在每次读取时直接从该条记录的版本链中选择一条可见的快照记录进行读取，而不需要获取记录上的行锁。在可重复读级别下，如果采用快照读，那么就不能在每次查询时都获取一次快照，而是在首次查询时获取，并在事务中一直沿用该快照，从而保证事务中多次读取的数据一致。而如果采用当前读，则需要对记录加锁，从而避免不可重复读。
INSERT、UPDATE 和 DELETE 写操作：当一个事务执行写操作时，它会生成一个新的数据版本，并将修改后的数据写入数据库。而原始版本的数据仍然存在，供其他事务使用快照读取，这保证了其他事务不受当前事务的写操作影响。
事务提交和回滚：当一个事务提交时，它所做的修改将成为数据库的最新版本，并且对其他事务可见。当一个事务回滚时，它所做的修改将被撤销，对其他事务不可见。
版本的回收：为了防止数据库中的版本无限增长，MVCC 会定期进行版本的回收。回收机制会删除已经不再需要的旧版本数据，从而释放空间。

7.2 InnoDB 中的 MVCC 实现

InnoDB 中 MVCC 的实现依赖于隐藏字段、Read View 和 undo log。InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改。

InnoDB 为每行数据添加了三个隐藏字段：

DB_TRX_ID（6字节）：表示最后一次插入、删除或更新该行的事务 id。
DB_ROLL_PTR（7字节）：回滚指针，指向该行的 undo log 版本链，如果该行从未被更新则为空。版本链即当前记录通过 DB_ROLL_PTR 串联起来的 undo log 链表。
DB_ROW_ID（6字节）：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引。

在这里插入图片描述

Read View 主要用于判断可见性，实现快照隔离。Read View 中除了包括当前事务 id、当前事务可以看到的最早的数据版本对应的事务 id 和当前事务可以看到的最新的数据版本对应的事务 id，还存储了创建该 Read View 时其他活跃的事务 id，这意味着如果后续它们修改了数据并进行了提交，对于当前事务也是不可见的。

undo log 在 MVCC 中则主要负责与 DB_ROLL_PTR 一起构成版本链，从而在执行快照读时，根据事务的 Read View 中的信息，顺着 undo log 的版本链找到满足其可见性的记录。

参考：

https://javaguide.cn/database/mysql
https://www.xiaolincoding.com/mysql/log/how_update.html

在这里插入图片描述