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 主要包含以下几个关键字段：

• max_trx_id：当前系统中分配过的最大事务 ID +1，即下一个事务的 ID。
• mid_trx_id：活跃事务列表中最小的事务 ID。
• m_ids：表示当前还未提交的所有事务 ID 列表。
• creator_trx_id: 表示当前事务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 的写操作从“随机写”变成了“顺序写”，大幅提升写入性能。

5. redolog文件写满了怎么办？

在 InnoDB 存储引擎中，默认存在一个 重做日志文件组（Redo Log Group），该文件组由两个固定大小且相同的日志文件组成。

假设每个文件大小为 1 GB，则整个日志组的总容量为 2 GB。

Redo Log 文件组采用 循环写（circular writing） 的方式进行工作： 从第一个文件的起始位置开始写入，写到末尾后再切换到下一个文件；当最后一个文件也写满时，又会回到第一个文件的开头，形成一个环状结构。因此，InnoDB 会先写入 ib_logfile0，当其写满后切换到 ib_logfile1；若 ib_logfile1 也写满，则再次切回 ib_logfile0。

Redo Log 的环形结构设计目的：

Redo Log 的设计初衷是为了防止 Buffer Pool 中的脏页在系统崩溃时丢失。当脏页被刷新（flush）到磁盘后，其对应的 Redo Log 记录就不再需要了，可以被擦除以腾出空间记录新的更新操作。

Redo Log 以环形结构组织，InnoDB 使用两个关键指针来管理其写入和擦除过程：

• write pos：表示当前写入位置；
• checkpoint：表示当前可以擦除的位置。

可以将 Redo Log 空间分为两部分：

• write pos → checkpoint（红色区域）：可写入新的更新操作；
• checkpoint → write pos（蓝色区域）：尚未刷新到磁盘的脏页对应记录。

Redo Log 写满时的处理机制：

当系统持续运行、不断产生新的更新操作时，write pos 会不断向前移动。一旦 write pos 追上了 checkpoint，意味着 Redo Log 文件已被写满 —— 此时不能再记录新的更新操作，MySQL 会被暂时阻塞。

为了解决这一问题，InnoDB 会执行以下步骤：

1. 暂停写入新的事务更新；
2. 将 Buffer Pool 中的部分脏页刷新到磁盘；
3. 标记已落盘的 Redo Log 记录为可覆盖；
4. 擦除旧的 Redo Log 记录，推动 checkpoint 向前移动（顺时针方向）；
5. 释放空间后，恢复写入新操作。

---

因此，一次 checkpoint 的过程本质上就是：

将脏页刷新到磁盘、标记对应的 Redo Log 记录可被覆盖，并推动 checkpoint 向前的过程。

为什么需要 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，兼顾一致性和存储效率。

为什么不能用 binlog 进行崩溃恢复

由于 binlog 记录的是写入性操作，如果想用 binlog 来恢复数据，我们必须知道现在得从哪里开始，因为一条语句多执行几次会带来数据上的错误。但是 binlog 虽然保存了所有的历史操作，但是它没有标识每条操作是否已经写入磁盘，所以我们无法确定从哪开始。 而 redo log 记录的是数据页的更改，并且刷盘完成的数据会从 redo log 中删除。

Redo Log 可以代替 Binlog 吗？

首先，Redo Log 是循环写入的，当写满后会擦除旧的日志记录来腾出空间，因此它并不是永久保存的日志。 而 Binlog 是追加写入的，不会被覆盖或擦除，用于持久记录数据库的所有更改，方便主从复制和数据恢复。

其次，虽然两者底层都采用二进制格式存储，但记录内容完全不同：

• Redo Log 属于物理日志，记录的是页级别的物理修改，比如“某个数据页的某个位置被改成了什么”；
• Binlog 属于逻辑日志，记录的是逻辑操作，比如“在某个表中插入了一条记录”或“更新了某个字段”。

所以从这两点可以看出，Redo Log 主要用于 崩溃恢复（crash recovery），而 Binlog 主要用于 复制和归档（replication & recovery），两者的功能互补，Redo Log 不能替代 Binlog。

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 产生更少的死锁。
• 减少事务的并发访问冲突，尽量使事务访问不同的数据集。
• 遵循固定的资源访问顺序，确保事务按照相同的顺序请求资源，避免循环等待。

18. 执行一条 select 语句，期间发生了什么？

“执行一条简单的 SELECT 语句，MySQL 内部到底发生了什么？” 这是每一个学习数据库系统时必问的问题，今天我们就通过一次完整的流程，从客户端发出 SQL，到结果返回，一层层深入，全面了解 MySQL 查询执行的内部机制。

---

1. 整体流程概览

MySQL 的执行架构可以分为两大部分：

1. Server 层：处理 SQL 的解析、优化、执行计划等通用逻辑。
2. 存储引擎层（如 InnoDB）：负责数据的真正存取，支持事务、MVCC、索引等。

查询流程可以总结为：

客户端 SQL 请求 → Server 层处理（解析 + 优化 + 执行） → 调用 InnoDB 引擎 → 读取数据 → 返回结果

2. 详细执行流程拆解

我们来逐步拆解一条 SQL 查询语句的执行过程：

SELECT name FROM users WHERE id = 1;

假设 id 是主键，我们以 MySQL 8.0 为背景，深入看看 MySQL 是如何一步步处理这条语句的。

---

1️⃣ 连接器：建立连接、校验用户

在你执行 SQL 之前，MySQL 需要先通过 连接器（Connection Manager） 和客户端建立连接：

• 通过 TCP 三次握手建立连接；
• 校验客户端提交的 用户名、密码；
• 验证通过后，从权限系统中加载该用户对应的权限信息，供后续语句使用。

---

2️⃣ 查询缓存（MySQL 5.7 及以前，8.0 已废弃）

在旧版本 MySQL 中（5.7 及之前），查询缓存（Query Cache） 是一个优化模块，用于缓存 SELECT 查询的执行结果。

• 缓存结构：以 SQL 语句为 key，查询结果为 value 存储在内存中；
• 命中缓存：直接返回结果，跳过后续步骤；
• 未命中缓存：继续向下执行，并将结果写入缓存中。

❗ 问题：查询缓存命中率极低，尤其是数据更新频繁的表，因为表只要有更新，相关的查询缓存就会全部失效。

MySQL 8.0 起，彻底移除了查询缓存模块，因为其负面影响大于收益。

---

3️⃣ 解析器：词法 + 语法分析

接下来，SQL 会进入解析器阶段：

解析器首先进行词法分析会将 SQL 拆解为最小的“语法单元”（Token），例如：

SELECT name FROM users WHERE id = 1;

会被拆分为：

类型	Token
关键字	SELECT
标识符	name
关键字	FROM
标识符	users
关键字	WHERE
标识符	id
运算符	=
字面量	1

然后，解析器会根据语法规则检查语句是否合法，若语法正确，将构建一棵 SQL 语法树（Parse Tree），用于后续阶段快速提取：

• SQL 类型（如 SELECT、INSERT）；
• 所操作的表名：users；
• 查询字段：name；
• 过滤条件：id = 1 等等。

⚠️ 注意：此阶段不会判断表或字段是否存在，仅校验 SQL 结构是否符合语法。

---

4️⃣ 预处理器：表字段校验、权限判断

语法树构建完成后，MySQL 会进入 预处理器（Preprocessor） 阶段，执行如下任务：

• 校验 SQL 中引用的 表名、字段名是否存在；
• 检查当前用户是否对这些对象有相应的权限（如 SELECT 权限）；
• 对视图、子查询、别名进行解析与展开。

如果表或字段不存在、或用户权限不足，会在这个阶段抛出错误。

---

5️⃣ 优化器：执行路径选择器

SQL 校验无误后，MySQL 会进入 优化器（Optimizer） 阶段 —— 它的任务是为你生成一条“代价最小的执行路径”。

例如：

• 是否使用索引？使用哪个索引？
• 表连接顺序如何排？
• 是否可以改写为更高效的查询？

MySQL 会基于统计信息（如索引基数、数据分布）评估各种执行方案的成本，选择最优方案。

🔧 你可以使用 EXPLAIN 查看优化器选择了怎样的执行计划。

---

6️⃣ 执行器：真实执行查询

最后，SQL 来到 执行器（Executor），MySQL 会按照优化器的执行计划，逐步完成真正的查询：

• 调用存储引擎（InnoDB）访问表数据；
• 根据主键查找（id=1）并返回对应的行；
• 按字段筛选，只返回 name；
• 将结果集返回给客户端。

此过程中，如果 InnoDB 没有命中 Buffer Pool，会触发磁盘 I/O 加载数据页，再进行解析。

18. MySQL Explain 执行计划详解

Explain 是 MySQL 提供的一个非常实用的诊断工具，通过在 SQL 语句前添加EXPLAIN关键字，MySQL 会模拟执行这条 SQL 语句，并返回该语句的执行计划信息。这些信息可以帮助我们分析 SQL 语句的执行过程，识别潜在的性能瓶颈，从而进行优化。

18.1字段解释

---

1. id

• 含义： 查询的 SELECT 标识符。代表当前 SELECT 的执行顺序。
• 规则：
  • 同一个查询中，id 值不同，值越大优先级越高，越先执行。
  • 如果某行引用其他行的并集结果，则该行 id 可能为 NULL。
  • 当 id 相同时，执行顺序一般自上而下。

---

2. select_type

• 含义： 查询类型，反映当前 SELECT 的角色。
• 常见取值：
  • SIMPLE：简单查询，不包含子查询或 UNION。
  • PRIMARY：外层查询（包含子查询时）。
  • SUBQUERY：子查询中的第一个 SELECT。
  • UNION：UNION 语句中，UNION 之后的 SELECT。
  • DERIVED：FROM 子句中的子查询，生成派生表。
  • UNION RESULT：UNION 合并后的结果集。

---

3. table

• 含义： 当前步骤所访问的表。
• 可能的值：
  • 普通表名。
  • <unionM,N>：引用了 id 为 M 和 N 的行的 UNION 结果。
  • <derivedN>：引用 id=N 的派生表。
  • <subqueryN>：引用 id=N 的物化子查询结果。

---

4. type

• 含义： 连接类型，表示 MySQL 查找记录的方式。它是性能的关键指标。

• 从优到劣排序：

  system > const > eq_ref > ref > fulltext > ref_or_null 
         > index_merge > unique_subquery > index_subquery 
         > range > index > ALL

• 常见类型说明：

  • system：表只有一行记录（极少见）。
  • const：常量级查询，通常是主键/唯一索引等值匹配。
  • eq_ref：唯一索引等值连接，每次匹配唯一一行。
  • ref：普通索引等值匹配，可能返回多行。
  • range：范围查询（BETWEEN、>、<、IN 等）。
  • index：全索引扫描（比 ALL 快，但仍需扫描整个索引树）。
  • ALL：全表扫描，性能最差，需要重点优化。

---

5. possible_keys

• 含义： 查询可能使用到的索引集合。
• 注意：
  • 如果为 NULL，说明语句中涉及的字段没有可用索引。
  • 可以考虑在 WHERE、GROUP BY、ORDER BY 涉及的字段上建索引。

---

6. key

• 含义： 查询实际使用到的索引。
• 注意：
  • 如果为 NULL，表示没有使用索引。
  • 对比 possible_keys 与 key 可以看出优化器的选择。

---

7. key_len

• 含义： 实际使用的索引长度（字节数）。
• 作用：
  • 用于判断复合索引是否被完全利用。
  • 越短越精确，表示索引条件使用得越充分。
  • 如果 key 为 NULL，此列也为 NULL。

---

8. rows

• 含义： MySQL 根据统计信息估算的扫描行数。
• 提示：
  • 数值越小越好。
  • 可以结合 filtered 字段（某些版本才有）估算最终输出行数。

---

9. Extra

• 含义： MySQL 执行查询时的额外信息。
• 常见取值：
  • Using filesort：需要额外排序（没有用到索引顺序），常见于 ORDER BY。
  • Using temporary：需要临时表保存中间结果，多见于 GROUP BY、DISTINCT。
  • Using index：覆盖索引，查询结果只依赖索引即可完成，无需回表。
  • Using index condition：索引条件下推（Index Condition Pushdown，ICP）。
  • Using where：存在 WHERE 条件，需要对行做过滤。⚠️ 不代表一定没用索引，具体要结合 type 和 key 判断。
  • Using join buffer (Block Nested Loop)：连接时被驱动表未命中索引，MySQL 需用 Join Buffer 做嵌套循环。
• 优化提醒：
  • 如果出现 Using filesort 或 Using temporary，说明性能可能存在问题，应优先考虑优化索引或改写 SQL。

18.2 实战分析

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  order_no VARCHAR(50) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  status TINYINT NOT NULL, -- 0:待支付,1:已支付,2:已取消
  create_time DATETIME NOT NULL
);

-- 插入测试数据（10万条）
INSERT INTO orders (user_id, order_no, amount, status, create_time)
VALUES (FLOOR(RAND()*10000), CONCAT('ORD', FLOOR(RAND()*1000000)), 
        RAND()*1000, FLOOR(RAND()*3), NOW() - INTERVAL FLOOR(RAND()*365) DAY);

案例 1：简单查询的执行计划分析

需求：查询id=10086的订单详情。

EXPLAIN分析sql

EXPLAIN SELECT * FROM orders WHERE id = 10086;

输出结果（关键字段）

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	orders	const	PRIMARY	PRIMARY	4	const	1	NULL

字段解读

• type=const：通过主键查询，效率极高（只匹配 1 行）。
• key=PRIMARY：实际使用了主键索引。
• rows=1：MySQL 估计只需要扫描 1 行。
• 结论：此查询无需优化，执行效率最优。

案例 2：避免Using temporary（临时表）和 Using filesort (内存排序)

需求：统计每个status的订单数量（GROUP BY 操作）。

初始 SQL（未优化索引）

EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY STATUS;

输出结果（关键字段）

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	orders	ALL	NULL	NULL	NULL	NULL	100000	Using temporary; Using filesort

问题分析

• Extra=Using temporary：MySQL 需要创建临时表存储 GROUP BY 的中间结果，效率低。
• Using filesort：临时表还需要排序，双重性能损耗。

优化方案：为GROUP BY字段创建索引sql

CREATE INDEX s_key ON orders (status)

优化后结果

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	orders	index	idx_orders_status	idx_orders_status	1	NULL	100000	Using index

优化效果

• Extra=Using index：使用了覆盖索引（索引包含 GROUP BY 所需的status字段），无需回表查数据。
• Using temporary和Using filesort消失：直接通过索引分组，效率大幅提升。

案例3: 索引下推分析Using index condition

索引下推（Index Condition Pushdown, ICP）是 MySQL 在 使用二级索引进行范围扫描 时的一种优化策略。 在没有启用 ICP 时，存储引擎会先根据索引定位到可能满足条件的记录，再将这些记录返回给 Server 层，由 Server 层进行条件过滤。 而启用 ICP 后，部分过滤条件会“下推”到存储引擎层执行，从而减少回表次数，提高查询效率。

---

2. 示例

创建复合索引：

CREATE INDEX s_u_key ON orders (user_id, status);

执行查询：

EXPLAIN
SELECT id, amount
FROM orders
WHERE user_id > 5000 AND status = 1;

执行计划结果：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	orders	range	idx_orders_status	idx_orders_userid_status	4	NULL	500	Using index condition

---

3. 分析

• 查询条件中 user_id > 5000 符合索引的第一个字段，因此走范围扫描。
• status = 1 虽然不符合左匹配原则，无法直接利用索引做等值匹配，但通过 索引下推，优化器会把该条件下推到存储引擎，在扫描 user_id 范围时同时判断 status。
• 执行计划的 Extra 字段显示了 Using index condition，说明 MySQL 启用了索引下推。

4. 总结

• 索引下推的优势在于减少了回表数据量，提高查询效率。
• 在复合索引中，如果后续字段不满足左匹配原则，仍可能通过 ICP 得到优化。