Mysql关系数据库

1. 关系型数据库概述

关系型数据库是基于关系模型的数据存储系统，由E.F. Codd在1970年提出，常用SQL进行查询和管理。数据以表格形式组织，每个表格包含行（记录）和列（属性），通过主键和外键建立表间关系。这种结构适合需要复杂查询和数据完整性的应用，如银行系统或电商平台。

• 定义与特点：数据存储在表格中，行代表记录，列代表属性，表间通过键关联，支持高效查询和数据关系管理。
• 应用场景：财务系统、客户关系管理（CRM）、库存管理等。

2. 数据库三大范式

数据库规范化是减少冗余、提高数据完整性的过程，三大范式如下：

• 第一范式（1NF）：确保每个单元格只包含单一值，无重复组或数组。例如，订单表中客户地址不能是多值字段。
• 第二范式（2NF）：基于1NF，确保非键列完全依赖于整个主键，不能部分依赖。例如，订单详情表中订单号和产品ID为复合主键，单价应依赖整个主键，不能仅依赖产品ID。
• 第三范式（3NF）：基于2NF，确保无传递依赖，即非键列直接依赖主键，不能通过其他非键列间接依赖。例如，学生表中如果班级信息通过班级ID获取，则班级名称不应直接存储，应拆分到班级表。

3. MySQL的优点

MySQL作为开源关系型数据库管理系统，具有以下优势：

• 开源免费：降低使用成本，适合初创企业和中小型项目。
• 高性能与可扩展性：支持高并发，适合从小型网站到大型电商平台。
• 跨平台兼容：可在Windows、Linux、macOS等系统上运行。
• 易用性：界面友好，管理简单，文档丰富。
• 社区支持：拥有庞大用户社区，问题解决快速。
• 多存储引擎支持：如InnoDB、MyISAM，适应不同需求。
• 多语言兼容：支持PHP、Python、Java等编程语言集成。
• 安全特性：提供数据加密和权限管理，确保数据安全。

4. MySQL的存储引擎

MySQL支持多种存储引擎，每个引擎适合不同场景，以下是主要存储引擎：

存储引擎	特点
InnoDB	默认引擎，支持事务、行级锁、外键，适合高并发和数据完整性要求的应用。
MyISAM	非事务性，读性能高，支持全文搜索，适合读多写少的场景，如日志分析。
Memory	数据存储在内存，读写速度快，但重启后数据丢失，适合临时表或缓存。
Archive	压缩存储，只读，适合归档数据，节省空间。
CSV	以CSV格式存储数据，适合数据导入导出。
Merge	合并多个MyISAM表，适合管理多个相同结构的表。

存储引擎的选择影响数据库性能和功能，InnoDB是现代应用的首选。

5. MyISAM和InnoDB的选择

选择MyISAM还是InnoDB取决于应用需求：

• InnoDB：支持事务、行级锁和外键，适合需要数据完整性和高并发的场景，如电商订单系统。缺点是内存和磁盘占用较高。
• MyISAM：非事务性，表级锁，读性能优，适合读多写少的场景，如数据仓库或静态内容网站。缺点是无法保证数据一致性。

选择时需考虑：

• 是否需要事务支持：需事务选InnoDB。
• 读写比例：读多写少可选MyISAM。
• 数据规模与并发：高并发场景选InnoDB。

6. MySQL的索引

索引是提高查询效率的数据结构，类似于书中的目录，减少全表扫描。MySQL索引类型包括：

• B树索引：默认类型，支持范围查询，适用于大多数场景。
• 哈希索引：用于Memory表，快速查找，但不支持范围查询。
• 全文索引：MyISAM支持，用于全文搜索。
• 空间索引：InnoDB支持，用于地理数据。

索引分类：

• 主键索引：自动创建，确保唯一性。
• 唯一索引：确保列值唯一，可有重复。
• 非唯一索引：允许重复值。
• 复合索引：基于多列，适合多条件查询。

索引提高读性能，但增加写操作开销。

如果一个 InnoDB 表 既有主键索引（Primary Key），又有其他唯一索引（Unique Key），那么 MySQL 的底层存储和索引管理方式主要涉及 聚簇索引（Clustered Index） 和 二级索引（Secondary Index）。

1. InnoDB 索引存储结构

在 InnoDB 存储引擎中，索引主要分为 聚簇索引（Clustered Index） 和 二级索引（Secondary Index）：

1. 主键索引（Primary Key Index）
   • InnoDB 强制使用 主键索引（聚簇索引） 来存储表中的数据。
   • 索引的叶子节点 直接存储了 完整的行数据，因此查找主键索引等同于查找数据页。
   • 如果表没有显式主键，InnoDB 会：
     • 选择一个 唯一非空索引 作为主键（如果有多个，则选择最早创建的）。
     • 如果没有唯一索引，InnoDB 会自动生成一个隐藏的 6 字节 row_id 作为主键。
2. 二级索引（Secondary Index，包括 Unique Key）
   • InnoDB 允许创建多个二级索引，如 UNIQUE 约束的索引。
   • 二级索引的叶子节点 存储的是主键值，而不是行数据本身。
   • 查找 非主键索引（包括唯一索引） 时，需要先通过二级索引找到主键值，然后再通过主键索引找到完整的行数据（回表查询）。

示例表

CREATE TABLE users (
    id INT PRIMARY KEY,      -- 主键索引（聚簇索引）
    email VARCHAR(255) UNIQUE, -- 唯一索引（二级索引）
    username VARCHAR(100) UNIQUE, -- 另一个唯一索引（二级索引）
    age INT
);

---

2. 索引查找过程

假设表 users 里有 100 万条数据，查询 email='test@example.com' 时：

1. 如果 email 不是主键：
   • MySQL 先通过唯一索引 email（二级索引） 查找 主键 id。
   • 再通过主键索引（聚簇索引） 回表查询完整的行数据。
2. 如果 id 是主键：
   • 直接通过聚簇索引查找数据，效率更高，不需要回表。
3. 如果查询 id（主键）：
   • 由于主键索引直接存储了行数据，所以不需要额外的查询步骤，性能最高。

流程示意图

查询 `email='test@example.com'`
  ↓
使用二级索引 `email` 查找 → 得到 `id = 123`
  ↓
使用 `id=123` 在主键索引中找到完整行数据

3. 索引覆盖（覆盖索引）

索引覆盖（Covering Index） 指的是查询的所有字段都可以从索引中获取，避免回表，提高查询性能。

例：

CREATE INDEX idx_user_email ON users (email, name);

SELECT email, name FROM users WHERE email = 'test@example.com';

• 由于 email 和 name 都在索引 idx_user_email 中，因此无需回表。

---

4. 对索引使用左或者左右模糊匹配

左模糊匹配（索引失效）：

SELECT * FROM users WHERE name LIKE '%abc';  -- 索引失效

• 由于 % 在前，索引无法进行前缀匹配，导致全表扫描。

右模糊匹配（索引生效）：

SELECT * FROM users WHERE name LIKE 'abc%';  -- 索引生效

• 索引仍然可以按前缀匹配，提高查询效率。

---

5. 对索引使用函数

• 索引失效示例：

  SELECT * FROM users WHERE LOWER(name) = 'john';  -- 索引失效
  

  MySQL 需要对每个 name 值执行 LOWER() 计算，导致索引失效。

• 优化方案：存储小写字段或使用 生成列（Generated Column）。

---

6. 对索引进行表达式计算

• 索引失效示例：

  SELECT * FROM users WHERE id + 1 = 100;  -- 索引失效
  

  id + 1 使索引列发生变化，MySQL 无法利用索引进行优化。

• 优化方案：调整查询条件，避免对索引列进行计算。

---

7. 对索引隐式类型转换

• 索引失效示例（字符串与数字比较）：

  SELECT * FROM users WHERE phone = 1234567890;  -- 索引失效
  

  phone 列是 VARCHAR 类型，而 1234567890 是 INT，MySQL 进行隐式转换导致索引失效。

• 优化方案：

  SELECT * FROM users WHERE phone = '1234567890';  -- 索引生效
  

---

8. WHERE 子句中的 OR（索引失效）

• 索引失效示例：

  SELECT * FROM users WHERE email = 'test@example.com' OR age = 30;
  

  email 和 age 各自有索引，但 OR 使 MySQL 不能有效利用单个索引。

• 优化方案：使用

  UNION ALL
  

  拆分查询。

  SELECT * FROM users WHERE email = 'test@example.com'
  UNION ALL
  SELECT * FROM users WHERE age = 30;
  

---

9. 联合索引非最左匹配

• 最左匹配原则（索引 (A, B, C)）：

  SELECT * FROM users WHERE A = 1 AND B = 2;  -- 索引生效
  SELECT * FROM users WHERE B = 2 AND C = 3;  -- 索引失效
  

• 索引必须从最左列开始匹配，否则索引不会生效。

• 优化方案：尽可能按最左匹配原则设计查询条件。

7. 事务的特点

事务是数据库操作的逻辑单元，确保数据操作可靠，遵循ACID原则：

• 原子性（Atomicity）：事务是不可分割的单元，要么全部执行，要么全部失败。
• 一致性（Consistency）：事务执行前后，数据库保持一致性，如转账前后总额不变。
• 隔离性（Isolation）：事务独立执行，中间状态对其他事务不可见。
• 持久性（Durability）：事务提交后，变化永久保存，即使系统故障也不丢失。

8. 并发事务带来的问题

并发事务可能导致以下问题，影响数据一致性：

• 脏读（Dirty Read）：一个事务读到另一个事务未提交的数据，若后者回滚，前者读到无效数据。
• 非重复读（Non-repeatable Read）：同一事务内两次读同一数据，值不同，因其他事务修改（针对同一条查询）。
• 幻读（Phantom Read）：范围查询结果因其他事务插入或删除行而变化（针对的是整个表）。
• 丢失更新（Lost Update）：两个事务更新同一数据，一个更新被覆盖。

这些问题通过隔离级别和并发控制机制解决。

9. MySQL的隔离级别

MySQL支持四种隔离级别，控制事务间可见性，解决并发问题：

隔离级别	特点
读未提交（READ UNCOMMITTED）	可读未提交数据，易发生脏读，非重复读和幻读。
读已提交（READ COMMITTED）	只读已提交数据，防止脏读，但可能非重复读和幻读。
可重复读（REPEATABLE READ）	事务内读数据一致，防止脏读和非重复读，但可能幻读（InnoDB默认）。
可串行化（SERIALIZABLE）	事务串行执行，防止所有并发问题，性能开销大。

隔离级别平衡了性能和数据一致性，选择需根据应用需求。

1. 读未提交（Read Uncommitted）

• 特点：
  • 事务可以读取其他事务未提交的数据（脏读）。这意味着即使另一个事务正在修改数据，当前事务也能看到这些未提交的修改。
  • 这种隔离级别的并发性能最好，但一致性最差。
• 潜在问题：
  • 脏读（Dirty Read）：事务A修改数据并未提交，事务B读取事务A的未提交数据，这些数据可能在事务A回滚时被撤销。
  • 不可重复读（Non-repeatable Read）：如果一个事务读取了某一行数据，另一个事务修改了该行数据并提交，第一个事务再次读取时，数据发生了变化。
• 应用场景：对于对数据一致性要求较低且对性能要求极高的场景，可能使用这个隔离级别。

2. 读已提交（Read Committed）

• 特点：
  • 事务只能读取已提交的数据，即事务只能读取其他事务已经提交的修改数据。这样可以避免脏读问题。
  • 然而，这个级别仍然可能发生不可重复读，因为在同一个事务中，某个数据行可以在两次读取之间被其他事务修改。
• 潜在问题：
  • 不可重复读（Non-repeatable Read）：同一事务内读取同一数据行时，如果该行在读取之间被另一个事务修改，则当前事务读取的数据会发生变化。
• 应用场景：适用于对数据一致性要求高于读未提交，但对并发性能要求较高的场景。典型应用如在线查询系统，但不能容忍脏读。

3. 可重复读（Repeatable Read）（MySQL默认隔离级别）

• 特点：
  • 保证同一个事务内多次读取同一数据时，读取的数据总是一样的，即 防止不可重复读。
  • 该级别通过锁定被读取的数据行来确保数据不被其他事务修改。
  • MySQL InnoDB 实现 可重复读 还通过 间隙锁（Gap Lock） 来防止 幻读（Phantom Read），即某个查询返回的数据集在同一事务中因其他事务的插入操作而发生变化。
• 潜在问题：
  • 幻读（Phantom Read）：事务A在查询某一范围的数据时，事务B插入了符合该范围的新数据。事务A再次执行查询时，发现查询结果中多了数据。
• 应用场景：这是MySQL的默认隔离级别，适用于大多数 OLTP 应用。它平衡了性能和一致性，避免了脏读和不可重复读，也通过间隙锁来防止幻读。

4. 串行化（Serializable）

• 特点：
  • 最强的事务隔离级别，通过强制事务串行执行，确保一个事务完成后，另一个事务才能开始。它完全避免了脏读、不可重复读和幻读。
  • MySQL实现方式是通过 范围行级锁 来实现事务的串行化，这会导致锁竞争和性能降低，尤其在高并发环境下。
• 潜在问题：
  • 性能较差，由于所有事务需要顺序执行，可能会造成严重的性能瓶颈，尤其在高并发环境中。
• 应用场景：适用于对数据一致性要求极高的场景，比如金融系统、银行转账等，确保每个事务能够按严格顺序执行，避免任何并发问题。

10. 隔离级别的实现

MySQL的隔离级别实现依赖存储引擎：

• InnoDB：使用锁和MVCC（多版本并发控制）实现。READ COMMITTED和REPEATABLE READ主要靠MVCC，SERIALIZABLE用额外锁防止幻读。
• MyISAM：不支持事务，依赖表级锁，隔离级别概念有限。

MVCC通过维护数据多个版本，读写不阻塞，提高并发性。

11. MVCC 详解

MVCC（Multi-Version Concurrency Control，多版本并发控制）是一种提高数据库事务并发能力的机制，主要用于解决脏读、不可重复读问题，避免加锁会带来的性能损失。在 MySQL 的 InnoDB 存储引擎中，MVCC 主要通过隐藏字段、Read View、Undo Log 这三部分来实现，使得事务可以实现非锁定的一致性读（快照读），提高数据库的吞吐量。

1. MVCC 关键组件

MVCC 主要依赖 InnoDB 存储引擎中的以下机制：

1.1 隐藏字段

InnoDB 在每行数据后面会添加三个隐藏字段：

• DB_TRX_ID（事务 ID）：记录最后一次对该行数据进行修改（INSERT、UPDATE、DELETE）时的事务 ID。
• DB_ROLL_PTR（回滚指针）：指向 Undo Log 中存储的该行数据的上一个版本，从而支持事务回滚和一致性读。
• DB_ROW_ID（行 ID）：仅在表没有主键或唯一非空索引时才会自动生成，并作为聚簇索引的主键。

---

1.2 Read View

Read View 是 MVCC 读取数据时的核心概念，它是事务可见性判断的关键。

• 当一个事务启动并执行第一个 SELECT 语句时，InnoDB 会创建一个 Read View，它表示该事务在当前时刻应该看到的数据库状态。
• Read View 主要维护当前系统中活跃的事务列表（即尚未提交的事务），通过该列表判断某一行数据是否对当前事务可见。

Read View 主要包含以下几个关键字段：

• low_limit_id：当前系统中分配过的最大事务 ID +1，即下一个事务的 ID。
• up_limit_id：活跃事务列表中最小的事务 ID。
• trx_ids：表示当前还未提交的所有事务 ID 列表。

1.3 Undo Log

Undo Log 主要用于存储数据的历史版本，并支持事务回滚和一致性读。

• 当事务对某条记录进行更新时，InnoDB 不会直接覆盖数据，而是先将旧值存入 Undo Log，然后修改数据行。
• 这样，如果有其他事务需要获取该行数据的历史版本，就可以通过 DB_ROLL_PTR 指针找到 Undo Log 并获取旧版本数据。

Undo Log 可分为两种：

1. Insert Undo Log：
   • 用于 INSERT 操作的 Undo Log，只在事务回滚时需要，事务提交后可以立即丢弃。
2. Update Undo Log：
   • 用于 DELETE 和 UPDATE 操作的 Undo Log，不仅在事务回滚时需要，还用于 MVCC 读取历史版本，只有当不再被任何快照引用时，才能被清理（由 Purge 线程完成）。

---

2. MVCC 读取流程

MVCC 主要用于 快照读（即 SELECT 语句不加锁时），确保事务可以读取到一致性的历史数据版本，而不会被其他事务的并发修改影响。其流程如下：

1. 事务启动时，创建 Read View

当事务执行 SELECT 语句（且不加锁）时，InnoDB 会为当前事务 创建一个 Read View，记录当前活跃事务的列表，用于判断数据的可见性。

2. 事务查询数据时，根据 DB_TRX_ID 判断可见性

每一行数据都包含一个隐藏字段 DB_TRX_ID，表示最后一次修改该数据的事务 ID。事务读取数据时，会将 DB_TRX_ID 与 Read View 进行比较，判断该行数据对当前事务是否可见：

(1). 访问的是自己修改的记录

• DB_TRX_ID == creator_trx_id
• 说明当前事务正在读取自己修改的数据，可见，直接返回。

(2). 事务在 Read View 生成之前已提交

• DB_TRX_ID < min_trx_id
• 说明修改该行数据的事务在当前事务生成 Read View 之前就已提交，因此该数据对当前事务 可见，直接返回。

(3). 事务在 Read View 生成之后才开启

• DB_TRX_ID ≥ max_trx_id
• 说明该版本数据的事务在当前事务生成 Read View 之后才开始，不可见，需要沿着 DB_ROLL_PTR 指向的 Undo Log，继续查找历史版本的数据，直到找到可见的版本。

(4). 事务在 Read View 生成时仍然活跃

• min_trx_id ≤ DB_TRX_ID < max_trx_id
• 需要判断 DB_TRX_ID 是否在 trx_ids（活跃事务列表） 中：
  • 如果 DB_TRX_ID 在 trx_ids 中：
    • 说明该事务在创建 Read View 时仍然活跃（尚未提交），该版本数据 不可见，需要沿着 DB_ROLL_PTR 指向的 Undo Log，继续查找历史版本。
  • 如果 DB_TRX_ID 不在 trx_ids 中：
    • 说明该事务在创建 Read View 之前已提交，该版本数据 可见，直接返回。

---

3. 如果数据不可见，沿着 Undo Log 查找历史版本

如果当前事务无法直接读取最新版本的数据，那么 InnoDB 会根据 DB_ROLL_PTR 指向的 Undo Log（回滚日志），找到该数据的上一个版本，并重新执行可见性判断，直到找到可见的版本数据。

Undo Log 的作用：

• 存储旧数据版本：当 UPDATE 或 DELETE 操作修改数据时，原始数据不会被直接删除，而是存储在 Undo Log 里。
• 形成版本链：每条记录可能会有多个旧版本，形成链式结构，DB_ROLL_PTR 指向前一个版本。
• 沿 Undo Log 回溯：
  • 如果最新版本不可见，则查找 Undo Log 里的前一个版本。
  • 依次检查每个版本的 DB_TRX_ID 是否符合 Read View 的可见性规则。
  • 直到找到可见版本的数据，返回结果。

---

3. 事务隔离级别与 MVCC

MySQL 支持四种事务隔离级别，而 MVCC 主要适用于 REPEATABLE READ（可重复读）和 READ COMMITTED（读已提交）：

• READ COMMITTED（读已提交）：
  • 每次查询都会生成一个新的 Read View，可能会读取到其他事务提交的新数据。
  • 解决了脏读问题，但仍然可能出现不可重复读（即同一事务中两次查询可能得到不同的结果）。
• REPEATABLE READ（可重复读，MySQL 默认）：
  • 事务首次查询时生成 Read View，后续查询都基于该 Read View，不会看到其他事务提交的新数据。
  • 解决了不可重复读问题，但可能会出现幻读。
• SERIALIZABLE（串行化）：
  • 不使用 MVCC，而是通过行锁或表锁实现最高级别的事务隔离，导致并发性能下降。

---

4. MVCC 的优势

• 无锁读：避免了加锁，提高了数据库并发能力。
• 提高性能：读操作无需阻塞写操作，写操作也不会阻塞读操作。
• 避免死锁：由于大部分读操作不使用锁，减少了死锁的可能性。

---

5. 可能的缺点

• Undo Log 开销：需要维护多个版本的数据，占用存储空间，并可能增加回滚成本。
• 垃圾回收（Purge 线程）：需要后台定期清理 Undo Log，可能影响性能。

12. 幻读的解决

幻读是范围查询结果因其他事务插入或删除行而变化的问题：

• 发生场景：READ COMMITTED和REPEATABLE READ可能出现，SERIALIZABLE可防止。
• 解决方法：InnoDB在SERIALIZABLE隔离级别用范围锁（Next-Key Lock）或间隙锁（gap Lock），锁定范围防止插入新行。
• 实践建议：对于需严格一致性的场景（如财务系统），用SERIALIZABLE；否则需额外锁机制。

在 MySQL 的 InnoDB 存储引擎中，为了保证 事务的隔离性，特别是在 REPEATABLE READ 和 SERIALIZABLE 隔离级别下，会使用 多种锁机制 来控制并发访问。其中，范围锁（Next-Key Lock） 和 间隙锁（Gap Lock） 是解决 幻读 的核心机制。

---

1. 间隙锁（Gap Lock）

定义：

• 间隙锁（Gap Lock）是一种 锁住索引记录“之间的空隙” 的锁，而不锁定具体的记录。
• 作用是防止其他事务在该范围内插入新数据，从而避免幻读问题。

间隙锁的作用：

1. 防止“幻读”现象（即同一事务内两次查询结果不同）。
2. 不影响已有数据的修改，只影响插入新数据。

示例： 假设 students 表有以下数据：

+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | Alice | 60    |
|  2 | Bob   | 80    |
|  3 | Carol | 100   |
+----+-------+-------+

• 事务 A：

  START TRANSACTION;
  SELECT * FROM students WHERE score > 80 FOR UPDATE;
  

  这个查询会对 score > 80 这部分的记录（Carol）加锁，同时 对 (80, 100) 的空隙加上“间隙锁”。

• 事务 B：

  INSERT INTO students (id, name, score) VALUES (4, 'David', 85);
  

  事务 B 会被阻塞，因为 85 落在 (80, 100) 这个被间隙锁锁住的范围内，不能插入。

---

2. 范围锁（Next-Key Lock）

定义：

• 范围锁（Next-Key Lock）= 记录锁（Record Lock）+ 间隙锁（Gap Lock）
• 它不仅锁住索引上的某条记录，还会锁住它前面的间隙，从而避免幻读。

Next-Key Lock 解决幻读

• Next-Key Lock 通过锁定查询范围，确保同一事务内查询的结果不会发生变化。
• 任何 新的插入、删除、修改 影响查询范围的操作，都会被阻塞。

示例： 假设 students 表数据如下：

+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | Alice | 60    |
|  2 | Bob   | 80    |
|  3 | Carol | 100   |
+----+-------+-------+

• 事务 A：

  START TRANSACTION;
  SELECT * FROM students WHERE score > 80 FOR UPDATE;
  

  事务 A 执行此查询后：

  • 锁住 score = 100 这条记录（记录锁）。
  • 同时锁住 (80, 100] 这个区间（间隙锁）。
  • 这样就形成了 Next-Key Lock。

• 事务 B：

  INSERT INTO students (id, name, score) VALUES (4, 'David', 85);
  

  事务 B 依然被阻塞，因为 85 落入了 (80, 100] 这个被锁定的范围。

---

3. 间隙锁 vs. 范围锁（Next-Key Lock）

锁类型	锁定范围	主要作用	是否锁定具体记录
间隙锁（Gap Lock）	仅锁定记录之间的“间隙”	防止新插入数据，避免幻读	❌ 不锁定具体记录
范围锁（Next-Key Lock）	记录锁 + 间隙锁（锁住记录和前面的间隙）	既防止新插入数据，也防止修改	✅ 锁定具体记录

13. 表级锁与行级锁

锁是控制并发访问的机制，MySQL中两种主要锁：

• 表级锁：锁整表，其他事务无法访问，简单但并发低，MyISAM使用。
• 行级锁：锁单行，其他事务可访问其他行，并发高但开销大，InnoDB使用。

锁类型	特点	适用场景
表级锁	锁整表，简单，读写性能高但并发低	读多写少，数据仓库场景
行级锁	锁单行，并发高，开销大，需事务支持	高并发OLTP系统，如电商订单处理

选择锁类型需权衡性能和并发需求。

14. 外键使用建议

阿里巴巴手册不推荐在高并发、高负载的场景下使用外键，主要原因如下：

1. 性能问题：外键需要数据库在每次插入、更新或删除时检查父表和子表的关系，这增加了额外的开销，影响性能。
2. 锁竞争：外键操作可能涉及对表的锁定，导致并发事务之间的锁竞争，影响系统的吞吐量。
3. 复杂性：外键约束增加了数据库设计的复杂性，尤其在进行表结构更改或处理级联操作时，可能带来意外问题。
4. 分布式环境问题：在分布式数据库中，外键约束很难高效实现，可能导致系统扩展性差。

因此，阿里巴巴更推荐通过 应用层逻辑 来保证数据一致性，而不是依赖数据库中的外键约束。

15. 为什么 MySQL 用 B+ 树作为底层数据结构？

MySQL 的 InnoDB 存储引擎默认使用 B+ 树作为索引的数据结构（例如主键索引和二级索引），这并不是偶然的选择，而是基于数据库操作的特性和性能需求优化而来的。相比 B 树，B+ 树在数据库场景下有显著的优势，以下是核心原因：

1. B+ 树只在叶子节点存储数据，层级更浅，查询效率更高

• B 树的特点：B 树的每个节点（包括中间节点）都存储键值和数据。这意味着在同样的内存空间下，B 树每个节点能容纳的键数量会减少，因为数据本身占用了空间。
• B+ 树的特点：B+ 树将所有数据存储在叶子节点，中间节点只存储键值和指向子节点的指针。由于中间节点不存数据，每个节点可以容纳更多的键（扇出更大），从而使树的深度更小。
• 影响：树的深度直接影响磁盘 I/O 的次数。在数据库中，数据通常存储在磁盘上，I/O 是性能瓶颈。B+ 树的深度更小，意味着从根节点到叶子节点的路径更短，查询所需的磁盘 I/O 次数更少，因此检索速度更快。

2. B+ 树叶子节点使用双向链表，范围查询和排序更高效

• B 树的特点：B 树的叶子节点之间没有链接，范围查询需要回溯到父节点，重新遍历子树，效率较低。
• B+ 树的特点：B+ 树的叶子节点通过双向链表连接，所有数据按顺序存储在叶子节点中。无论是顺序扫描还是范围查询（例如 WHERE id BETWEEN 100 AND 200），都可以直接通过链表顺序读取数据，无需额外跳转。
• 影响：数据库中范围查询和排序操作非常常见（例如 ORDER BY、GROUP BY），B+ 树的双向链表结构天然支持这种需求，极大提高了效率。

3. B+ 树更适合数据库的写操作优化

• 在插入和删除操作中，B+ 树只需要调整叶子节点和少量中间节点的键值，而 B 树可能需要调整多个节点的键值和数据，复杂度更高。B+ 树的这种特性减少了写操作的开销，尤其是在需要维护索引的情况下。

16. Undo Log、Bin Log 与 Redo Log

MySQL 作为广泛使用的关系型数据库管理系统，采用多种日志机制来保证数据的可靠性和一致性。其中，Undo Log、Redo Log 和 Binlog 是最重要的三种日志类型。本文将详细介绍它们的作用、存储方式及其在事务管理中的应用。

• Undo Log（回滚日志）：由 InnoDB 存储引擎生成，保证事务的原子性（Atomicity），主要用于事务回滚和 MVCC（多版本并发控制）。
• Redo Log（重做日志）：由 InnoDB 存储引擎生成，保证事务的持久性（Durability），主要用于掉电等故障后的数据恢复。
• Binlog（归档日志）：由 Server 层生成，主要用于数据备份和主从复制，支持数据的增量恢复。

这三种日志在 MySQL 事务管理和数据安全方面发挥着各自的关键作用，共同确保数据库的稳定运行。

为什么需要 Undo Log？

在 MySQL 中，每次执行 增、删、改（INSERT、DELETE、UPDATE） 操作时，即使我们没有手动开启事务，MySQL 也会 隐式 启动一个事务。这是由 autocommit 参数决定的，默认情况下，该参数是 开启 的，即每条 SQL 语句都会被视为一个独立的事务执行。

1. 事务的原子性与崩溃恢复

既然 MySQL 自动开启事务，那么就必须保证 事务的原子性（Atomicity）。如果事务在提交前，MySQL 发生崩溃，如何恢复数据呢？

• 事务的原子性要求 要么全部执行成功，要么全部回滚，因此需要有一种机制来 记录事务修改前的数据，以便在回滚或崩溃恢复时使用。
• 这时，Undo Log（回滚日志） 就发挥作用了！
• 工作原理：每次事务执行时，修改前的数据 都会被保存到 Undo Log 中，并通过 版本链 进行管理（与数据库表的 三个隐藏字段 相关）。

一旦事务 回滚，MySQL 便可以 利用 Undo Log 恢复到修改前的数据，从而保证事务的原子性。

2. Undo Log 在 MVCC 中的作用

除了 事务回滚，Undo Log 还在 多版本并发控制（MVCC） 中发挥关键作用，以支持不同的事务隔离级别：

• READ COMMITTED（读已提交）
  • 事务读取的总是 最新提交版本，每次查询都会生成一个新的 ReadView。
  • 依赖 Undo Log 维护历史数据版本，确保读取的是 事务提交后的最新数据。
• REPEATABLE READ（可重复读）
  • 事务在开始时创建 ReadView，并在整个事务过程中保持不变。
  • 通过 Undo Log + ReadView 机制，实现 MVCC，确保同一事务内多次读取的结果一致。

为什么需要 Redo Log？

要理解 Redo Log（重做日志） 的必要性，需要从 MySQL 的存储 机制展开分析。

---

1. MySQL 存储与 Buffer Pool

MySQL 的数据存储基于 磁盘，当执行 增、删、改 操作时，MySQL 需要先从 磁盘 读取数据到 内存，再修改后写入磁盘。然而，频繁的磁盘随机写入会极大影响性能。因此，InnoDB 存储引擎 引入了 Buffer Pool（缓冲池），用于在 内存 中缓存数据页，提高查询和修改的效率。

• 查询操作：
  • 如果 Buffer Pool 中已有数据，则直接返回，提高查询效率。
  • 如果 Buffer Pool 没有数据，则从 磁盘 读取并存入 Buffer Pool 以供后续访问。
• 修改操作：
  • 先修改 Buffer Pool 中的数据页，并标记为 脏页（Dirty Page）。
  • 数据不会立即写入磁盘，而是由后台线程在合适的时机将脏页刷入磁盘。

但是，如果在数据刷入磁盘之前 MySQL 发生宕机，未写入的内存中的脏页将导致数据丢失！
为了解决这个问题，WAL 技术（Write-Ahead Logging） 与 Redo Log 应运而生。

---

2. WAL 技术与 Redo Log 的作用

WAL（Write-Ahead Logging） 的核心思想是 先写日志，再写磁盘，以确保数据安全性和性能：

1. 事务执行时：
   • 修改 Buffer Pool 中的数据页，标记为 脏页（Dirty Page）。
   • 同时，将修改操作写入 Redo Log Buffer（内存中的日志缓冲区），但此时并未写入磁盘，而是通过后台进程策略来定时写入page cache并调用fsycn()进行落盘。
2. 事务提交时：
   • 先将 Redo Log Buffer 写入 Redo Log 文件（参数设置1，默认进行刷新刷盘）。
   • 此时事务即认为已提交，可以向客户端返回成功信息。
3. 稍后，在合适的时机：
   • InnoDB 后台线程 通过 checkpoint 机制 逐步将 脏页写入磁盘（随机写，性能较低）。

但如果事务已提交、数据尚未刷新到磁盘时 MySQL 崩溃，该怎么办？
这时候，Redo Log 派上用场！

Redo Log 是一种 物理日志，记录 对数据页的修改，例如：

• “对表空间 XXX 的数据页 YYY 偏移量 ZZZ 位置执行了 AAA 更新。”

只要事务提交时 Redo Log 已持久化到磁盘，就能保证即使 MySQL 崩溃，数据仍可恢复。

当 MySQL 重启 时：

• 检查 Redo Log，找到未写入磁盘的数据。
• 重新执行日志中的修改，恢复数据到最新状态。

Redo Log 让 MySQL 具备了数据恢复能力，即确保已提交事务的数据不会因宕机而丢失。

---

3. Redo Log 何时真正持久化？

InnoDB 通过innodb_flush_log_at_trx_commit 共有 3 种刷盘策略，分别对应不同的性能与数据安全性权衡：

• 0：每次事务提交时 不立即写入磁盘，仅存于 Redo Log Buffer。这种方式性能最高，但最不安全，如果 MySQL 宕机，最近 1 秒内的事务可能丢失。
• 1（默认值）：每次事务提交时 立刻将 Redo Log 持久化到磁盘，确保数据不会丢失。尽管性能相对较低，但这是最安全的方式，符合事务的持久性要求。
• 2：每次事务提交时 仅将 Redo Log 写入操作系统的 Page Cache（文件系统缓存），不立即刷入磁盘。该方式在性能和安全性上介于 0 和 1 之间，若系统崩溃，可能会丢失 Page Cache 中尚未写入磁盘的日志数据。

InnoDB 存储引擎还有一个后台线程，每隔 1 秒 将 Redo Log Buffer 的内容写入 Page Cache，然后调用 fsync 将数据同步到磁盘，以减少单次事务刷盘的开销。

上图可以看到，一个没有提交事务的 redo log 记录，也可能会刷盘，为后面的二阶段埋下伏笔

4. 直接写数据到磁盘不就好了吗？为什么要额外写 Redo Log？

原因：磁盘顺序写比随机写快得多！

• 数据写入磁盘 需要先找到数据页，再更新内容，这是 随机写，速度慢。
• Redo Log 采用追加方式写入磁盘，是 顺序写，速度快。

举个例子：

你在一个本子上写字，按照顺序 一页一页写，肯定比 每次都翻到某个特定的页码写一个字 要快很多！

这正是 WAL 技术的另一大优势： ✅ 将 MySQL 的写操作从“随机写”变成了“顺序写”，大幅提升写入性能。

为什么需要 Binlog？

在介绍 Binlog 之前，我们先回顾 MySQL 的其他日志系统，比如 Redo Log 和 Undo Log，这两个日志都是 InnoDB 存储引擎 生成的，主要用于 事务回滚（Undo Log）和 崩溃恢复（Redo Log）。

但是，MySQL 的 Server 层 也需要记录一份日志，确保数据的 归档、备份 和 主从复制 能够顺利进行。这就是 Binlog 诞生的原因。

Binlog 主要用于以下几个场景：

• 数据恢复（Point-in-Time Recovery, PITR）：结合 全量备份 + Binlog，可以实现 任意时间点的数据库恢复。
• 主从复制（Replication）：Binlog 是 MySQL 主从复制的基础，主库 记录 Binlog，从库 通过 Binlog 进行数据同步。
• 增量备份：不同于 Redo Log 只记录事务的物理变更，Binlog 记录的是 逻辑操作（SQL 语句），可以用于 增量数据恢复。

1. Binlog 的工作原理

MySQL 采用 两阶段提交 机制来保证 Binlog 和 Redo Log 之间的一致性。其大致流程如下：

1. 事务执行：当一个事务执行时，修改的数据先写入 Buffer Pool（缓冲池），并生成相应的 Redo Log（未提交状态），以及事把日志写到binlog cache。
2. 写入 Binlog：在事务提交时，MySQL Server 层 先将binlog cache写到 binlog 文件中（写入page cache，调用fsycn()进行刷盘）。
3. 刷新 Redo Log 并提交事务：InnoDB 引擎此时会将 Redo Log 的状态标记为 提交（commit），并将数据落盘。
4. 事务完成：此时事务真正提交成功，数据已持久化到磁盘。

之前我们提到redolog会有后台线程进行刷盘，那么这个时候在没提交事务的时候发生了宕机，可能照成没有binlog日志产生数据不一致

使用两阶段提交后，写入 binlog 时发生异常也不会有影响，因为 MySQL 根据 redo log 日志恢复数据时，发现 redo log 还处于prepare阶段，并且没有对应 binlog 日志，就会回滚该事务。

2. Binlog 与 Redo Log 的区别

虽然 Binlog 和 Redo Log 都记录了数据库的修改，但它们的作用完全不同：

对比项	Binlog	Redo Log
日志层级	Server 层（MySQL 通用）	InnoDB 存储引擎层
记录内容	逻辑日志（SQL 语句）	物理日志（数据页的变更）
作用	数据备份、主从复制、增量恢复	崩溃恢复，保证事务持久性
存储方式	追加写（Append-Only）	循环写（Fixed-Size）
崩溃恢复	不支持（仅用于归档和复制）	支持（保证 crash-safe）
删除策略	需要手动清理或配置保留策略	根据 checkpoint 机制自动清理

最初，MySQL 只有 MyISAM 引擎，它不具备 crash-safe 能力，Binlog 仅用于 归档。而后，InnoDB 作为 插件引擎 引入，使用 Redo Log 实现 crash-safe 机制，以确保数据的完整性。

这也是为什么 MySQL 需要同时使用 Binlog 和 Redo Log，两者各司其职，互相配合。

3. Binlog 的格式

Binlog 共有 三种格式，可以通过 binlog_format 参数进行配置：

1. STATEMENT（SQL 语句模式）：
   • 记录 原始 SQL 语句，体积小，但某些语句（如 NOW()、RAND()）可能在主从库执行结果不一致。
2. ROW（行模式，默认）：
   • 记录 数据行的变化，不记录 SQL 语句，保证主从一致性，但日志体积较大。
3. MIXED（混合模式）：
   • 结合 STATEMENT 和 ROW，大部分使用 STATEMENT，特殊情况下使用 ROW，兼顾一致性和存储效率。

17. 数据库死锁（Deadlock in Database）

数据库死锁是指两个或多个事务在等待对方持有的资源，而无法继续执行，导致永久性阻塞的现象。死锁通常发生在多个事务同时访问相同的数据资源，并且由于锁的获取顺序不同，导致相互等待，最终陷入死锁状态。

1. 产生死锁的必要条件（四要素）

数据库死锁的发生需要满足以下四个必要条件（即“柯林斯四要素”）：

• 互斥条件（Mutual Exclusion）：某些资源一次只能被一个事务占用。
• 占有并等待条件（Hold and Wait）：事务已经占有某些资源，并且在等待其他资源的同时不释放已占有的资源。
• 不可抢占条件（No Preemption）：事务获取的资源不能被强制夺走，只能由事务自己主动释放。
• 循环等待条件（Circular Wait）：存在一个事务等待环，即 T1 等待 T2 持有的资源，T2 等待 T3 持有的资源，……，Tn 等待 T1 持有的资源，形成一个闭环。

只要系统满足这四个条件，就可能发生死锁。

2. 数据库死锁的示例

假设有两个事务 T1 和 T2，分别需要访问两张表 A 和 B，事务的执行如下：

1. T1 先锁住 A，T2 先锁住 B。
2. T1 试图获取 B 的锁，而 B 已被 T2 占用，因此 T1 进入等待状态。
3. T2 试图获取 A 的锁，而 A 已被 T1 占用，因此 T2 进入等待状态。
4. T1 和 T2 互相等待对方释放锁，形成死锁。

3. 数据库死锁的检测与预防

数据库通常采用以下方法来避免或解决死锁问题：

（1）死锁预防（Prevention）

在事务执行前，采取策略防止系统进入死锁状态：

• 一次性申请所有资源：事务在开始时就获取它需要的所有资源，避免后续的等待。
• 有序资源分配：所有事务按固定的顺序申请资源，确保不会形成循环等待。
• 等待超时机制：如果事务等待超时（如 WAIT_TIMEOUT 超过一定时间），则主动回滚该事务，释放资源。

（2）死锁检测（Detection）

数据库管理系统（DBMS）会定期检测死锁是否发生，并采取措施进行处理：

• 等待图（Wait-for Graph）算法：数据库会维护一个事务等待图，发现循环等待时选择一个事务进行回滚，解除死锁。
• 超时机制：如果某个事务等待时间超过设定的阈值，数据库会主动终止该事务。

（3）死锁恢复（Recovery）

当死锁发生后，系统通常采用以下方法进行恢复：

• 回滚事务（Rollback Transaction）：选择一个代价最小的事务回滚，以释放资源并解除死锁。
• 部分回滚（Partial Rollback）：只回滚事务的一部分操作，而不是整个事务。
• 事务重试（Transaction Retry）：被回滚的事务可以重新执行，以尝试避开死锁。

4. 常见数据库的死锁处理机制

不同数据库对死锁的处理有所不同：

• MySQL（InnoDB）：
  • 通过 wait-for graph 机制进行死锁检测。
  • 事务等待时间超时（innodb_lock_wait_timeout）。
  • 在 REPEATABLE READ 或 SERIALIZABLE 级别下更容易发生死锁，因为这些隔离级别可能会增加锁竞争。

5. 解决死锁的最佳实践

• 合理设计索引，减少不必要的锁竞争。
• 减少锁持有时间，及时提交事务，避免长时间持有锁。
• 使用合适的事务隔离级别，如 READ COMMITTED 可能比 SERIALIZABLE 产生更少的死锁。
• 减少事务的并发访问冲突，尽量使事务访问不同的数据集。
• 遵循固定的资源访问顺序，确保事务按照相同的顺序请求资源，避免循环等待。