02-MySQL进阶

MySQL 进阶特性

深入理解 MySQL 内部机制和高级特性

1. MySQL 架构

1.1 整体架构

┌─────────────────────────────────────┐
│          客户端                      │
└──────────────┬──────────────────────┘
               │
┌──────────────▼──────────────────────┐
│        连接器（Connection Pool）     │
├─────────────────────────────────────┤
│        查询缓存（MySQL 8.0 已移除）  │
├─────────────────────────────────────┤
│        分析器（词法、语法分析）       │
├─────────────────────────────────────┤
│        优化器（生成执行计划）         │
├─────────────────────────────────────┤
│        执行器（调用存储引擎 API）     │
├─────────────────────────────────────┤
│        存储引擎（InnoDB、MyISAM）    │
└─────────────────────────────────────┘

1.2 SQL 执行流程

1. 连接器：验证身份，管理连接
2. 分析器：词法分析、语法分析
3. 优化器：生成执行计划，选择索引
4. 执行器：调用存储引擎接口
5. 存储引擎：读写数据

---

2. 存储引擎

2.1 InnoDB vs MyISAM

特性	InnoDB	MyISAM
事务	✅ 支持	❌ 不支持
外键	✅ 支持	❌ 不支持
锁粒度	行锁	表锁
MVCC	✅ 支持	❌ 不支持
崩溃恢复	✅ 支持	❌ 不支持
全文索引	✅（5.6+）	✅
存储结构	聚簇索引	非聚簇索引
适用场景	事务、高并发写	只读、统计分析

2.2 查看和设置存储引擎

-- 查看支持的存储引擎
SHOW ENGINES;

-- 查看表的存储引擎
SHOW TABLE STATUS LIKE 'users';

-- 创建表时指定引擎
CREATE TABLE users (...) ENGINE=InnoDB;

-- 修改存储引擎
ALTER TABLE users ENGINE=MyISAM;

2.3 InnoDB 特点详解

聚簇索引

InnoDB 的主键索引就是数据：
- 叶子节点存储完整的行数据
- 二级索引叶子节点存储主键值

优点：
- 范围查询快
- 覆盖索引性能好

缺点：
- 主键不宜过大（影响二级索引）
- 插入顺序影响性能

自适应哈希索引

InnoDB 会自动为热点数据创建哈希索引
- 自动创建，无需手动干预
- 适用于等值查询

变更缓冲（Change Buffer）

非唯一二级索引的变更先缓存，后合并
- 减少随机 IO
- 提高写入性能

---

3. 索引原理

3.1 为什么使用 B+Tree？

对比其他数据结构：

数据结构	查询	插入	删除	范围查询	空间
数组	O(n)	O(n)	O(n)	✅	小
链表	O(n)	O(1)	O(1)	❌	小
哈希表	O(1)	O(1)	O(1)	❌	中
二叉搜索树	O(log n)	O(log n)	O(log n)	✅	中
B+Tree	O(log n)	O(log n)	O(log n)	✅	大

B+Tree 优势：

• 多路平衡，树高度低
• 所有数据在叶子节点，范围查询快
• 叶子节点有链表，便于遍历

3.2 B+Tree 结构

           [10, 20, 30]          ← 非叶子节点（只存键）
          /    |    |    \
         /     |    |     \
   [1-9]  [10-19] [20-29] [30-39]  ← 叶子节点（存键+数据）
     ↔       ↔       ↔       ↔       ← 双向链表

特点：

• 非叶子节点只存储键和指针
• 叶子节点存储完整数据
• 叶子节点通过链表连接
• 高度通常为 3-4 层

计算：

假设：
- 页大小 16KB
- 主键 BIGINT 8 字节
- 指针 6 字节
- 每个节点 1170 个键（16KB / (8+6)）

三层 B+Tree：
- 第一层：1 个节点
- 第二层：1170 个节点
- 第三层：1170 * 1170 = 1,368,900 个节点

可存储约 136 万条记录

3.3 聚簇索引 vs 非聚簇索引

聚簇索引（主键索引）

InnoDB 的主键索引：
- 叶子节点存储完整行数据
- 一个表只有一个聚簇索引
- 如果没有主键，InnoDB 会自动创建隐藏主键

非聚簇索引（二级索引）

InnoDB 的二级索引：
- 叶子节点存储主键值
- 查询需要回表（先查二级索引得到主键，再查主键索引）

示例：
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    INDEX idx_username (username)
);

SELECT * FROM users WHERE username = 'alice';

执行流程：
1. 查询 idx_username 索引，得到主键 id = 1
2. 回表：查询主键索引，得到完整行数据

3.4 覆盖索引

-- 如果查询的字段都在索引中，无需回表

-- 创建联合索引
CREATE INDEX idx_username_email ON users(username, email);

-- 覆盖索引（无需回表）
SELECT username, email FROM users WHERE username = 'alice';

-- 需要回表
SELECT * FROM users WHERE username = 'alice';

3.5 最左前缀原则

-- 联合索引
CREATE INDEX idx_city_age_gender ON users(city, age, gender);

-- 会使用索引
WHERE city = 'Beijing'
WHERE city = 'Beijing' AND age = 25
WHERE city = 'Beijing' AND age = 25 AND gender = 'male'

-- 不会使用索引（没有最左列）
WHERE age = 25
WHERE gender = 'male'
WHERE age = 25 AND gender = 'male'

-- 部分使用索引
WHERE city = 'Beijing' AND gender = 'male'  -- 只用 city

---

4. 事务

4.1 ACID 特性

• A（Atomicity）原子性：要么全部成功，要么全部失败
• C（Consistency）一致性：事务前后数据一致
• I（Isolation）隔离性：并发事务互不干扰
• D（Durability）持久性：事务提交后永久保存

4.2 事务操作

-- 开启事务
START TRANSACTION;
-- 或
BEGIN;

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

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT sp1;
ROLLBACK TO sp1;

4.3 隔离级别

四种隔离级别

-- 读未提交（READ UNCOMMITTED）
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 读已提交（READ COMMITTED）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 可重复读（REPEATABLE READ，MySQL 默认）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 串行化（SERIALIZABLE）
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 查看隔离级别
SELECT @@transaction_isolation;

并发问题

-- 脏读（Dirty Read）
事务 A 读取了事务 B 未提交的数据

-- 不可重复读（Non-Repeatable Read）
事务 A 两次读取同一行，结果不同（因为事务 B 修改了数据）

-- 幻读（Phantom Read）
事务 A 两次查询，结果集数量不同（因为事务 B 插入或删除了数据）

InnoDB 如何解决幻读：

• MVCC（多版本并发控制）
• Next-Key Lock（行锁 + 间隙锁）

---

5. 锁机制

5.1 锁的分类

按粒度分

• 表锁：锁整个表，开销小，并发低
• 行锁：锁单行，开销大，并发高
• 页锁：锁一页数据

按类型分

• 共享锁（S Lock）：读锁，多个事务可同时持有
• 排他锁（X Lock）：写锁，只有一个事务可持有

InnoDB 锁

• 记录锁（Record Lock）：锁定单行记录
• 间隙锁（Gap Lock）：锁定记录之间的间隙
• Next-Key Lock：记录锁 + 间隙锁，防止幻读

5.2 加锁示例

-- 共享锁（读锁）
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- 排他锁（写锁）
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- UPDATE/DELETE 自动加排他锁
UPDATE users SET age = 26 WHERE id = 1;

5.3 死锁

什么是死锁？

-- 事务 A
START TRANSACTION;
UPDATE users SET age = 26 WHERE id = 1;  -- 锁住 id=1
UPDATE users SET age = 27 WHERE id = 2;  -- 等待 id=2 的锁

-- 事务 B
START TRANSACTION;
UPDATE users SET age = 28 WHERE id = 2;  -- 锁住 id=2
UPDATE users SET age = 29 WHERE id = 1;  -- 等待 id=1 的锁

-- 结果：互相等待，形成死锁

如何避免死锁？

1. 固定加锁顺序：所有事务按相同顺序加锁
2. 缩小事务范围：减少持锁时间
3. 使用索引：减少锁定行数
4. 降低隔离级别：READ COMMITTED

查看死锁

-- 查看最近的死锁
SHOW ENGINE INNODB STATUS;

-- 查看当前锁等待
SELECT * FROM information_schema.innodb_locks;
SELECT * FROM information_schema.innodb_lock_waits;

---

6. MVCC（多版本并发控制）

6.1 MVCC 原理

MVCC 通过保存数据的历史版本，实现读写不阻塞。

核心概念：

• 事务 ID：每个事务有唯一 ID
• 隐藏字段：
  • DB_TRX_ID：最后修改该行的事务 ID
  • DB_ROLL_PTR：回滚指针，指向 undo log
  • DB_ROW_ID：隐藏主键（如果没有主键）

6.2 Read View（读视图）

Read View 记录事务开始时的活跃事务列表：
- m_ids: 活跃事务 ID 列表
- min_trx_id: 最小活跃事务 ID
- max_trx_id: 下一个要分配的事务 ID
- creator_trx_id: 创建 Read View 的事务 ID

可见性规则：
1. trx_id < min_trx_id: 可见（已提交）
2. trx_id >= max_trx_id: 不可见（未开始）
3. min_trx_id <= trx_id < max_trx_id:
   - 在 m_ids 中: 不可见（活跃中）
   - 不在 m_ids 中: 可见（已提交）

6.3 MVCC + 锁解决幻读

-- REPEATABLE READ 隔离级别下

-- 事务 A
START TRANSACTION;
SELECT * FROM users WHERE age > 18;  -- 查询 10 条

-- 事务 B
INSERT INTO users (age) VALUES (20);  -- 被 Next-Key Lock 阻塞

-- 事务 A
SELECT * FROM users WHERE age > 18;  -- 仍然 10 条（MVCC）

---

7. 日志系统

7.1 redo log（重做日志）

作用：保证事务的持久性

原理：

1. 事务执行时，先写 redo log（顺序 IO）
2. 定期将 redo log 刷新到磁盘（随机 IO）
3. 崩溃恢复时，重放 redo log

优势：
- WAL（Write-Ahead Logging）
- 顺序写，性能高

配置：

-- 刷盘策略
innodb_flush_log_at_trx_commit = 1  -- 每次事务提交都刷盘（最安全）
innodb_flush_log_at_trx_commit = 0  -- 每秒刷盘（性能好，可能丢 1 秒数据）
innodb_flush_log_at_trx_commit = 2  -- 每次提交写 OS 缓存，每秒刷盘

7.2 undo log（回滚日志）

作用：

• 回滚事务
• MVCC 读取历史版本

原理：

记录数据的旧版本：
- INSERT: 记录主键，回滚时删除
- UPDATE: 记录旧值，回滚时恢复
- DELETE: 记录完整行，回滚时插入

版本链：
row → undo1 → undo2 → undo3 → ...

7.3 binlog（二进制日志）

作用：

• 主从复制
• 数据恢复
• 审计

格式：

-- STATEMENT：记录 SQL 语句
binlog_format = STATEMENT

-- ROW：记录数据变化（推荐）
binlog_format = ROW

-- MIXED：混合模式
binlog_format = MIXED

操作：

-- 查看 binlog 列表
SHOW BINARY LOGS;

-- 查看 binlog 内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

-- 启用 binlog（my.cnf）
log-bin = mysql-bin
server-id = 1

7.4 三种日志对比

日志	作用	格式	层级
redo log	崩溃恢复	物理日志	InnoDB
undo log	回滚、MVCC	逻辑日志	InnoDB
binlog	复制、恢复	逻辑日志	Server

---

8. 主从复制

8.1 主从架构

┌─────────┐
│  Master │ (读写)
└────┬────┘
     │ binlog
  ┌──┴──┬──────┐
  │     │      │
┌─▼─┐ ┌─▼─┐ ┌──▼──┐
│Slave│Slave│Slave │ (只读)
└────┘ └───┘ └─────┘

8.2 复制原理

1. Master 执行 SQL，写入 binlog
2. Slave 的 IO 线程读取 Master 的 binlog
3. Slave 的 IO 线程写入 relay log
4. Slave 的 SQL 线程读取 relay log 并执行

8.3 配置主从

Master 配置

# my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW

-- 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- 查看 Master 状态
SHOW MASTER STATUS;

Slave 配置

# my.cnf
[mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1

-- 配置主从
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

-- 启动复制
START SLAVE;

-- 查看 Slave 状态
SHOW SLAVE STATUS\G

8.4 复制延迟问题

-- 查看延迟
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 延迟秒数

-- 解决方案
1. 并行复制（MySQL 5.7+）
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 4

2. 半同步复制
3. 读写分离时从缓存读

---

9. 高可用方案

9.1 半同步复制

-- Master 安装插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- Slave 安装插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

原理：

1. Master 执行事务，写 binlog
2. 等待至少一个 Slave 确认接收
3. Master 返回成功

优势：数据更安全
缺点：性能略降低

9.2 组复制（MGR）

MySQL Group Replication：
- 多主模式：所有节点可读写
- 单主模式：一个主节点，其他只读
- 基于 Paxos 协议
- 自动故障转移

9.3 MHA（Master High Availability）

MHA 是第三方高可用方案：
- 监控 Master
- 自动故障转移
- 补齐 binlog 差异

---

10. 高频问题

Q1: InnoDB 和 MyISAM 的区别？

答案： 见上面 2.1 节对比表，重点：事务、锁粒度、崩溃恢复。

Q2: 为什么 InnoDB 使用 B+Tree 而不是 B Tree？

答案：

1. B+Tree 所有数据在叶子节点：

   • 非叶子节点只存键，可存储更多键，树高度更低
   • 范围查询快（叶子节点有链表）

2. B Tree 数据分散在所有节点：

   • 树高度更高
   • 范围查询需要多次随机 IO

Q3: 什么情况下索引会失效？

答案：

1. 使用函数或表达式：

WHERE YEAR(created_at) = 2024  -- 失效
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'  -- 生效

2. 隐式类型转换：

WHERE phone = 13800138000  -- 失效（phone 是 VARCHAR）
WHERE phone = '13800138000'  -- 生效

3. LIKE 左模糊：

WHERE name LIKE '%alice'  -- 失效
WHERE name LIKE 'alice%'  -- 生效

4. OR 条件：

WHERE age = 25 OR city = 'Beijing'  -- 可能失效（需两个字段都有索引）

5. NOT、!=、<>：

WHERE age != 25  -- 可能失效

6. 联合索引不遵循最左前缀：

-- 索引：idx_city_age
WHERE age = 25  -- 失效（没有 city）

Q4: MVCC 的实现原理？

答案： 见上面 6.1、6.2 节，核心：

• 隐藏字段（DB_TRX_ID、DB_ROLL_PTR）
• undo log 版本链
• Read View 可见性判断

Q5: MySQL 主从复制原理？

答案： 见上面 8.2 节，核心：

• Master 写 binlog
• Slave IO 线程读取 binlog → relay log
• Slave SQL 线程执行 relay log

---

总结

MySQL 进阶核心：

1. ✅ MySQL 架构和执行流程
2. ✅ 存储引擎（InnoDB vs MyISAM）
3. ✅ 索引原理（B+Tree、聚簇索引、覆盖索引）
4. ✅ 事务（ACID、隔离级别）
5. ✅ 锁机制（表锁、行锁、死锁）
6. ✅ MVCC（多版本并发控制）
7. ✅ 日志系统（redo log、undo log、binlog）
8. ✅ 主从复制
9. ✅ 高可用方案

下一步：学习性能优化和调优技巧！