核心模块

存储引擎

主要是它的存储引擎，存储引擎是什么呢？就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型

大部分都是用的InnoDB，因为它一方面能进行事务处理，另一方面支持行级锁和外键。

其他的存储引擎的话，最早的是MyISAM，都不支持事务，行级锁和外键约束的功能。但是呢也支持全文索引，查询快。

数据存储在内存中的是Memory，这种只适合临时存储数据。

还有一种不支持索引的是archive，这种只有select 和 insert语句，使用的地方大多是都是日志记录和聚合分析方面。

📌 为什么 InnoDB 是默认引擎？

  • 支持事务（ACID）

  • 支持行锁（并发性能更好）

  • 支持 MVCC（多版本并发控制）

  • 支持外键约束

日志模块

日志系统是 MySQL 数据安全性 和 事务管理 的核心。

主要日志类型：

  • Redo Log（重做日志）：用于崩溃恢复，保证事务持久化

  • Undo Log（回滚日志）：用于事务回滚和 MVCC

  • Binary Log（二进制日志）：用于数据复制、增量备份

  • Slow Query Log（慢查询日志）：用于性能优化，记录执行时间长的 SQL

  • General Log（通用日志）：记录所有 SQL 操作

📌 事务的四大特性（ACID）：

  • A（Atomicity，原子性）：事务要么全部执行，要么全部回滚

  • C（Consistency，一致性）：事务执行后，数据库状态保持一致

  • I（Isolation，隔离性）：事务之间相互独立（支持 4 种隔离级别）

  • D（Durability，持久性）：事务提交后，数据不会丢失

事务管理

事务（Transaction）是指 一组数据库操作的集合，这些操作要么 全部成功提交(´｡• ᵕ •｡`) ♡，要么 全部回滚(҂⌣̀_⌣́)，不能出现只执行了一部分的情况。

事务的四大特性（ACID）

特性

说明

如何实现

原子性（Atomicity）

事务是最小的执行单元，不可分割

通过 Undo Log（回滚日志） 实现

一致性（Consistency）

事务执行前后，数据应处于一致状态

依赖于 原子性+隔离性 确保一致性

隔离性（Isolation）

事务之间相互独立，不能互相干扰

通过 锁机制 + MVCC 实现

持久性（Durability）

事务一旦提交，数据必须写入磁盘，不能丢失

通过 Redo Log（重做日志） 确保数据落盘

事务的控制语句

在 MySQL 中，事务主要由 InnoDB 存储引擎 提供支持（MyISAM 不支持事务）。可以使用以下 SQL 语句来管理事务：

-- 开启事务
START TRANSACTION;

-- 执行 SQL 操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;
-- 发生错误时回滚事务
ROLLBACK;

💡 注意：

  • START TRANSACTION 开启事务（或者 BEGIN）

  • COMMIT 提交事务，数据永久写入数据库

  • ROLLBACK 回滚事务，撤销未提交的修改

  • SAVEPOINT 用于创建回滚点，可部分回滚

隔离级别

多个事务并发执行时，可能会产生以下 并发问题：

  • 脏读（Dirty Read）：一个事务能读取另一个未提交事务的数据

  • 不可重复读（Non-Repeatable Read）：同一事务内多次读取，数据可能被修改

  • 幻读（Phantom Read）：同一事务内多次查询，可能读到新增的数据

MySQL 提供了 四种隔离级别 来控制事务的并发行为：

隔离级别

脏读

不可重复读

幻读

性能

READ UNCOMMITTED（未提交读）

✅ 可能

✅ 可能

✅ 可能

🚀 高

READ COMMITTED（提交读）

❌ 防止

✅ 可能

✅ 可能

🔥 中

REPEATABLE READ（可重复读，MySQL 默认）

❌ 防止

❌ 防止

✅ 可能

⚡ 中

SERIALIZABLE（串行化）

❌ 防止

❌ 防止

❌ 防止

🐢 低

MySQL 事务日志

事务管理中，日志系统 是核心。MySQL 使用 Undo Log 和 Redo Log 来保证事务的原子性和持久性。

🔹 Undo Log（回滚日志）

  • 作用：保证原子性（Atomicity），用于事务回滚

  • 记录内容：事务执行前的数据快照，可以撤销未提交的修改

  • 存储位置：InnoDB 的回滚段（Rollback Segment）

💡 示例：

UPDATE users SET age = 30 WHERE id = 1;

如果事务回滚，MySQL 会用 Undo Log 恢复 age 为原来的值。

🔹 Redo Log（重做日志）

  • 作用：保证持久性（Durability），用于崩溃恢复

  • 记录内容：已经提交的事务操作，保证数据不会丢失

  • 存储位置：ib_logfile0, ib_logfile1（InnoDB 日志文件）

💡 示例：

  1. 事务执行后，数据写入 Redo Log

  2. 发生崩溃时，MySQL 通过 Redo Log 恢复数据，确保事务完整

MVCC

数据库在并发事务环境下，通过维护数据的多个版本，使得读取数据时不需要加锁，从而提高并发性能。

MVCC 主要用于 READ COMMITTED 和 REPEATABLE READ 这两个隔离级别，它能：

  • 避免加锁操作，提高并发性能

  • 通过保存数据的多个版本，实现事务的隔离

  • 保证事务读取到的数据是符合一致性要求的（事务快照）

MVCC 主要依赖两个机制：

  • Undo Log（回滚日志）：用于存储数据的 历史版本，帮助事务读取旧数据

  • Read View（读视图）：事务启动时，生成的 数据可见性规则，决定事务能看到哪些数据版本

此外，InnoDB 还会用 隐藏列 维护事务 ID：

  • DB_TRX_ID（事务 ID）：每次修改数据时，都会记录 修改该数据的事务 ID

  • DB_ROLL_PTR（回滚指针）：指向 Undo Log 里存储的 上一版本数据

-- 事务 A（启动事务时的快照）
START TRANSACTION;
SELECT * FROM users WHERE id = 1; -- 看到的是事务启动时的数据快照

-- 事务 B（修改数据）
UPDATE users SET age = 25 WHERE id = 1;
COMMIT; -- 事务 B 提交，但事务 A 仍看到旧数据

💡 事务 A 看到的是自己启动时的“快照”，而不是事务 B 提交后的最新数据。

MVCC 的工作原理

🔹 读操作（两种方式）

在 InnoDB 里，读取数据有两种模式：

  1. 快照读（Snapshot Read）：不加锁，读取的是 历史版本数据

  2. 当前读（Current Read）：加锁，读取的是 最新版本数据

读操作

是否加锁

说明

SELECT * FROM users WHERE id = 1;

❌

快照读，使用 MVCC，读的是旧版本数据

SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

✅

当前读，加共享锁，保证数据不会被改

SELECT * FROM users WHERE id = 1 FOR UPDATE;

✅

当前读，加排他锁，阻止其他事务修改

🔹 写操作

当一个事务对数据进行 修改 时：

  1. 在 Undo Log 里保存旧数据（供其他事务快照读使用）

  2. 更新 DB_TRX_ID（记录修改数据的事务 ID）

  3. 提交事务后，新的事务可以看到修改后的数据

读视图（Read View） 是 MVCC 里非常重要的概念，它 决定了事务在快照读时，能看到哪些数据版本。

🔹 Read View 维护的关键信息：

  • 当前活跃的事务 ID 列表（trx_list）：记录当前还未提交的事务

  • 最小事务 ID（low_limit_id）：比这个 ID 小的事务，数据可见

  • 最大事务 ID（high_limit_id）：比这个 ID 大的事务，数据不可见

示例：

-- 事务 A（ID=10）开启
START TRANSACTION;
SELECT * FROM orders; -- 生成 Read View，记录当前活跃事务

-- 事务 B（ID=11）修改数据
UPDATE orders SET amount = 500 WHERE id = 1;
COMMIT; -- 提交事务

-- 事务 A 再次查询
SELECT * FROM orders;

💡 事务 A 只能看到 Read View 生成时的快照，事务 B 提交的修改不会影响事务 A。

Undo Log 与 MVCC

MVCC 依赖 Undo Log 实现快照读：

  • 当事务执行 SELECT 时，会读取 Undo Log 里的旧版本数据

  • 当事务 ROLLBACK 时，会使用 Undo Log 回滚数据

💡 示例：

START TRANSACTION;
UPDATE users SET age = 30 WHERE id = 1; -- 旧值写入 Undo Log
ROLLBACK; -- 事务回滚，Undo Log 恢复旧值

🔹 Undo Log 清理

  • MySQL 会使用 purge 机制 自动清理已经不需要的 Undo Log

  • 但如果长事务占用 Read View，Undo Log 可能会堆积（导致 history list length 变长）

  • undo log的持久化必须在数据持久化之前，这样才能保证系统崩溃时，可以用undo log来回滚事务

  • 保证数据的持久性，数据要在事务提交之前持久化

📌 MVCC 工作流程回顾

1️⃣ 事务开启时创建 Read View
2️⃣ 修改数据时，旧值存入 Undo Log
3️⃣ 查询数据时，根据 Read View 选择合适的版本
4️⃣ 事务提交后，新事务可见最新数据，旧事务仍读快照
5️⃣ 后台 Purge 线程定期清理 Undo Log

分布式事务

一个事务操作涉及多个数据库、服务或节点，需要保证数据一致性。还是有四大特性ACID。

典型实现方式

两阶段提交

事务少，但数据一致性要求较高。

原理： 由 协调者（Coordinator） 统一管理事务，确保事务跨多个数据库的一致性。

📌 过程：

  • 阶段 1（Prepare 预提交）

    • 协调者向所有参与者发送 “准备提交” 请求

    • 参与者执行事务操作，但 不提交，结果写入日志

    • 参与者回复 “可以提交” 或 “失败”

  • 阶段 2（Commit / Rollback 提交或回滚）

    • 若所有参与者都返回 “可以提交”，则执行 真正的提交并且释放资源

    • 若有任何一个参与者 失败，则 回滚所有操作并且释放资源

📌 问题：

  • 同步阻塞：所有事务必须等待提交，影响并发性能

  • 单点故障：协调者崩溃，事务可能卡住（解决方案：三阶段提交 3PC）

三阶段提交（3PC, Three-Phase Commit）

2PC 的改进版，通过 增加一个“Can Commit”阶段，减少阻塞，提高事务成功率。

  • Can Commit（询问阶段）：协调者询问是否可以执行事务

  • PreCommit（预提交）：与 2PC 的 Prepare 相同

  • Commit / Rollback（提交或回滚）：最终决定

📌 相比 2PC，3PC 额外引入了超时机制，减少事务卡死问题。

本地消息表（异步事务）

高吞吐场景，如 订单系统、支付系统等。
核心思想： 将事务拆分，利用消息队列（MQ）确保最终一致性。

📌 过程：

  1. 业务数据库写入事务记录

  2. 消息中间件（MQ）通知其他服务

  3. 其他服务处理事务，并标记完成

  4. 定期检查事务状态，确保最终完成

优点： ✅ 高吞吐，减少事务阻塞
✅ 异步执行，提高系统性能
✅ 适合 最终一致性（Eventual Consistency） 场景

缺点： ❌ 需要额外的 消息队列（如 Kafka、RabbitMQ）
❌ 可能出现 消息丢失，需要补偿机制（如重试、回查）

TCC（Try-Confirm-Cancel）补偿事务

需要部分回滚的场景，比如 酒店预订、机票预订。
核心思想： 将事务拆分为 Try、Confirm、Cancel 三个步骤。

📌 过程：

  1. Try（资源预留）：先 冻结资源，但不提交（比如锁定酒店房间）

  2. Confirm（事务确认）：所有操作成功后，正式提交

  3. Cancel（事务取消）：如果失败，则 释放资源

示例：酒店预订

  1. Try：先锁定房间（但不扣款）

  2. Confirm：支付成功，正式预订

  3. Cancel：支付失败，释放房间

📌 优点：

  • 解决了 2PC 的阻塞问题

  • 适合 跨服务事务，如 支付、库存管理

📌 缺点：

  • 需要业务实现 Try / Cancel 逻辑

  • 额外的 补偿逻辑，增加系统复杂度

在 实际工程中，大多数系统 不会使用 2PC / 3PC（太慢），而是采用 消息队列 + TCC 方式 来实现 最终一致性。

索引

底层索引（Index）就是 数据库中的数据结构，用于加速 WHERE 查询 和 ORDER BY 排序。

📌 简单理解：

  • 没有索引 → 需要 从头到尾扫描表（全表扫描）

  • 有索引 → 像翻书目录一样，直接找到数据所在的位置

数据结构

MySQL InnoDB 存储引擎中B+树是默认的数据结构，可以高效地 查找、插入、删除 数据。

📌 特点：

  • 有序结构，支持范围查询

  • 叶子节点存储数据的主键和行数据地址

  • 非叶子节点只存索引键值，不存储数据

查找：从 根节点 开始进行二分操作，找到一个key所在的指针，日后递归地在指针所指向的结点进行查找，知道查到叶子节点，然后在叶子节点上面进行二分查找，找到key 所对应的 data。

加速范围查询

假设执行：

SELECT * FROM users WHERE id BETWEEN 10 AND 25;

过程

1️⃣ 找到 10 的位置（B+ 树索引查找）
2️⃣ 由于 B+ 树 叶子节点是双向链表，可以直接 顺序遍历 10~25 之间的值
3️⃣ 不需要回到根节点，可以 一次性取出所有数据，性能非常高！

📌 为什么 B+ 树比 B 树好？

  • B+ 树的 叶子节点用链表连接，支持 范围查询 更高效

  • B+ 树的 非叶子节点只存索引值，不存数据，可以减少磁盘 I/O

MySQL 的索引类型

🔹 1. 主键索引（PRIMARY KEY）

特点：

  • 唯一性索引，一张表只能有一个 主键

  • 主键索引会 自动创建 一个 B+ 树索引

  • 叶子节点存储完整的行数据（聚簇索引）

示例

CREATE TABLE users (
    id INT PRIMARY KEY,  -- 主键索引
    name VARCHAR(100),
    age INT
);

🔹 2. 唯一索引（UNIQUE KEY）

特点：

  • 不允许重复值（可以有 NULL）

  • 和普通索引的查询效率一样，只是多了唯一性检查

示例

CREATE UNIQUE INDEX idx_email ON users(email);

或者：

ALTER TABLE users ADD UNIQUE (email);复制编辑

🔹 3. 普通索引（INDEX）

特点：

  • 最基础的索引，只用于加速查询，不保证唯一性

示例

CREATE INDEX idx_name ON users(name);

🔹 4. 组合索引（Composite Index）

特点：

  • 多个列组成一个索引，加速多条件查询

  • 遵循最左前缀匹配原则

示例

CREATE INDEX idx_name_age ON users(name, age);

📌 查询优化

SELECT * FROM users WHERE name = 'Alice' AND age = 25; -- ✅ 能用索引 SELECT * FROM users WHERE age = 25; -- ❌ 不能用索引

因为 idx_name_age 索引 name 在前面，如果查询时 不包含 name，索引就无法生效。

🔹 5. 全文索引（FULLTEXT）

特点：

  • 适用于长文本字段（TEXT, VARCHAR）

  • 支持模糊搜索，比 LIKE '%关键字%' 快很多

  • InnoDB 也支持 FULLTEXT（MySQL 5.6+）

示例

CREATE FULLTEXT INDEX idx_desc ON articles(content);

查询方式

SELECT * FROM articles WHERE MATCH(content) AGAINST('机器学习');

🔹 6. 覆盖索引（Covering Index）

如果索引本身就包含查询需要的 所有字段，MySQL 只需要扫描索引，而 不需要回表查询，这种优化叫做 覆盖索引。

示例

CREATE INDEX idx_name_age ON users(name, age);

查询：

SELECT name, age FROM users WHERE name = 'Alice';

📌 由于 idx_name_age 已经包含 name, age，查询时只访问索引，不访问数据表，查询速度更快！

索引优化与注意事项

✅ 索引优化技巧

1️⃣ 选择合适的列建索引（查询频率高的字段）
2️⃣ 避免在小表上建索引（小表直接全表扫描更快）
3️⃣ 避免索引过多（影响插入、更新性能）
4️⃣ 用组合索引优化多列查询（遵循最左前缀法则）
5️⃣ 使用 EXPLAIN 分析索引是否生效

什么是 EXPLAIN？

EXPLAIN 用于分析 SQL 语句的执行计划，帮助我们 优化查询。
它可以告诉我们： ✅ 查询是否使用了索引？
✅ 扫描了多少行数据？
✅ 查询的执行顺序？
✅ 索引的类型和效果？

📌 基本使用

EXPLAIN SELECT * FROM users WHERE id = 10;

执行 EXPLAIN 会返回以下重要字段：

字段

作用

id

查询的步骤编号（一般单表查询都是 1）

select_type

查询类型（SIMPLE, PRIMARY, SUBQUERY等）

table

查询的表名

type

访问类型（越左越快）

possible_keys

可能用到的索引

key

实际用到的索引

key_len

索引长度（索引字段的字节数）

ref

索引的匹配方式

rows

预计扫描的行数

Extra

额外优化信息

EXPLAIN 如何分析慢查询？

📌 核心思路：找到导致 全表扫描（type=ALL）、回表查询、索引未命中 的原因！

如何进一步优化慢查询？

✅ 使用 EXPLAIN ANALYZE

MySQL 8.0+ 提供了 EXPLAIN ANALYZE，可以执行 SQL 并输出真实执行计划：

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

它会显示：

  • 查询的真实执行时间

  • 索引使用情况

  • 优化建议

慢查询原因

解决方案

全表扫描（type = ALL)

建立索引

LIKE '%xxx%' 导致索引失效

改为 LIKE 'xxx%' 或 FULLTEXT

回表查询影响性能

使用覆盖索引（只查询索引中的字段）

索引顺序错误

遵循最左前缀原则

❌ 索引的常见误区

🚫 对低选择性字段建索引（如性别字段）

CREATE INDEX idx_gender ON users(gender);

性别只有 “男” / “女” 两种值，索引 无法有效加速查询，还会影响性能。

🚫 使用 LIKE '%xxx%' 查询

SELECT * FROM users WHERE name LIKE '%Alice%';

% 在前面，索引无法使用！正确方式：

SELECT * FROM users WHERE name LIKE 'Alice%';

📌 可以用 FULLTEXT 索引替代 LIKE '%xxx%' 查询

🚫 对变动频繁的字段建索引 如 update_time，因为频繁更新会 导致索引频繁维护，影响写入性能。

如何查看索引

📌 查看表的索引

SHOW INDEX FROM users;

📌 分析索引是否生效

EXPLAIN SELECT * FROM users WHERE name = 'Alice';

  • possible_keys：可能使用的索引

  • key：实际使用的索引

  • rows：扫描的行数（越少越好）

优化MySQL语句

减少查询的扫描行数，提高查询效率！🚀💡。我们可以从 索引、查询优化、SQL 语法优化、缓存、架构优化 这几个方面入手。

使用索引优化查询

索引 是 SQL 查询优化的关键！如果查询 扫描大量数据，就需要索引来加速。

✅ 使用合适的索引

示例：创建索引

CREATE INDEX idx_email ON users(email);   -- 普通索引
CREATE UNIQUE INDEX idx_userid ON users(user_id);  -- 唯一索引
CREATE INDEX idx_name_age ON users(name, age);  -- 组合索引

查询优化：

-- ❌ 慢查询：没有索引，会全表扫描
SELECT * FROM users WHERE email = 'alice@example.com';

-- ✅ 使用索引查询
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

👉 确保 EXPLAIN 结果中 key 字段有索引，如果 key=NULL，说明索引未生效！

⚠️ 避免索引失效

查询优化（减少扫描数据）

✅ SELECT 只取需要的字段

❌ 慢查询

SELECT * FROM users WHERE email = 'alice@example.com';

✅ 优化（只查询必要字段）

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

🌟 避免 SELECT *，只查询需要的字段，减少 IO！

✅ 覆盖索引（避免回表查询）

❌ 慢查询（需要回表）

EXPLAIN SELECT * FROM users WHERE name = 'Alice';

✅ 优化（索引覆盖查询）

EXPLAIN SELECT name FROM users WHERE name = 'Alice';

✔ EXTRA = Using index，直接从索引查询数据，不回表，提高查询速度！

✅ 适当使用 LIMIT

SELECT * FROM orders ORDER BY create_time DESC LIMIT 10;

🌟 LIMIT 限制返回数据行数，提高查询速度！

✅ 避免 OR 导致索引失效

SELECT * FROM users WHERE name = 'Alice'
UNION ALL
SELECT * FROM users WHERE age = 25;

🌟 OR 可能导致索引失效，改用 UNION ALL 更高效！

✅ 选择合适的数据类型

  • 使用 INT 代替 BIGINT（如果 ID 不需要太大）

  • 使用 VARCHAR(50) 代替 TEXT（文本字段不要滥用 TEXT）

  • 尽量使用 NOT NULL，避免额外的 NULL 处理开销

✅ EXISTS 代替 IN

SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

🌟 EXISTS 比 IN 更高效，尤其是子查询结果集较大时！

✅ JOIN 代替子查询

SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id;

🌟 JOIN 连接查询通常比子查询快！