文章背景图

Day57-60-MySQL高级

Administrator
2026-06-04
1
- 字
- 分钟
|

Day57-60 - MySQL 高级

本部分涵盖 MySQL 高级知识，包括事务机制、并发控制（MVCC、锁）、主从复制和读写分离。

1. 事务基础

1.1 事务概念

事务是数据库操作的最小工作单元，一个事务包含一组数据库操作，这些操作要么全部成功，要么全部失败回滚。

1.2 ACID 特性

特性 说明 保证机制
Atomicity（原子性） 事务是最小执行单元，不可分割 Undo Log
Consistency（一致性） 事务执行前后，数据库状态保持一致 应用程序逻辑
Isolation（隔离性） 并发事务之间相互隔离，互不干扰 锁 + MVCC
Durability（持久性） 事务提交后，数据永久保存 Redo Log

1.3 事务控制语句

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

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 保存点
SAVEPOINT point_name;
ROLLBACK TO SAVEPOINT point_name;

1.4 事务提交模式

-- 查看事务提交模式
SHOW VARIABLES LIKE 'autocommit%';

-- 设置事务提交模式
SET autocommit = 0;  -- 手动提交
SET autocommit = 1;  -- 自动提交（默认）

2. 隔离级别

2.1 四种隔离级别

隔离级别 脏读 不可重复读 幻读
READ UNCOMMITTED 可能 可能 可能
READ COMMITTED 不可能 可能 可能
REPEATABLE READ（默认） 不可能 不可能 可能
SERIALIZABLE 不可能 不可能 不可能

2.2 设置隔离级别

-- 查看当前隔离级别
SHOW VARIABLES LIKE 'transaction_isolation%';

-- 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

2.3 隔离级别配置

[mysqld]
transaction-isolation = REPEATABLE-READ

3. 并发读现象

3.1 三种读现象

graph TB
    subgraph 脏读
        T1["事务T1"] --> W1["修改数据A=100"]
        T1 --> R1["读取A=100"]
        T1 --> RB1["回滚A恢复"]
        T2["事务T2"] --> R2["读取A=100"]
        R2 --> U2["使用A进行操作"]
    end
    
    subgraph 不可重复读
        T1 --> R3["读取B=50"]
        T1 --> U1["更新B=60"]
        T1 --> C1["提交"]
        T2 --> R4["读取B=60"]
    end
    
    subgraph 幻读
        T1 --> C1a["统计数量=5"]
        T2 --> I1["插入新行"]
        T2 --> C2["提交"]
        T1 --> C1b["再次统计=6"]
    end

3.2 脏读（Dirty Read）

  • 定义：事务 A 读取了事务 B 未提交的数据
  • 场景：READ UNCOMMITTED 隔离级别
  • 危害：事务 B 回滚后，事务 A 使用的数据是错误的
-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- 读取到1000

-- 事务B（并发）
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- 未提交

-- 事务A
SELECT balance FROM accounts WHERE id = 1;  -- 读取到500（脏数据）

-- 事务B
ROLLBACK;  -- 回滚，balance恢复1000

-- 事务A
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
-- 基于脏数据进行了错误的操作

3.3 不可重复读（Non-repeatable Read）

  • 定义：同一事务内，两次读取同一数据得到不同结果
  • 场景：READ COMMITTED 隔离级别
  • 原因：其他事务提交了修改
-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- 第一次读取1000

-- 事务B（并发）
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
COMMIT;

-- 事务A
SELECT balance FROM accounts WHERE id = 1;  -- 第二次读取500（不可重复读）
COMMIT;

3.4 幻读（Phantom Read）

  • 定义：同一事务内，两次查询得到不同数量的记录
  • 场景：同一事务内，前后两次查询结果不一致
  • 原因：其他事务插入了新数据
-- 事务A
BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 1;  -- 第一次查询得到5条

-- 事务B（并发）
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1, 100);
COMMIT;

-- 事务A
SELECT COUNT(*) FROM orders WHERE user_id = 1;  -- 第二次查询得到6条（幻读）
INSERT INTO orders (user_id, amount) VALUES (1, 200);  -- 可能报错主键冲突

4. MVCC 多版本并发控制

4.1 MVCC 核心原理

据《MySQL MVCC 多版本并发控制：核心原理、Read View、undo log 版本链》（CSDN），MVCC 的核心思想是通过保存数据的历史版本，实现读不加锁、读写不冲突。

4.2 隐藏字段

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

字段 说明
DB_TRX_ID 最近修改该行的事务 ID
DB_ROLL_PTR 回滚指针，指向 undo log 中的历史版本

4.3 Undo Log 版本链

mermaid
graph LR
    subgraph 版本链
        V0["当前版本<br/>trx_id=100"]
        V0 -->|ROLL_PTR| V1["undo log v1<br/>trx_id=90<br/>值=旧数据1"]
        V1 -->|ROLL_PTR| V2["undo log v2<br/>trx_id=80<br/>值=旧数据2"]
    end

4.4 Read View 机制

-- Read View 包含四个关键字段
-- m_ids: 活跃事务ID列表
-- min_trx_id: 最小活跃事务ID
-- max_trx_id: 最大事务ID+1
-- creator_trx_id: 当前事务ID

4.5 可见性判断规则

据《MySQL 的 MVCC 核心原理》（掘金），可见性判断口诀：

自己改的 ✓ 我开启前提交的 ✓ 我开启后创建的 ✗ 同期间未提交的 ✗

-- 可见性判断流程
IF trx_id == creator_trx_id THEN
    RETURN 可见（当前事务自己修改）
ELSE IF trx_id < min_trx_id THEN
    RETURN 可见（事务已提交）
ELSE IF trx_id >= max_trx_id THEN
    RETURN 不可见（事务在当前事务之后创建）
ELSE IF trx_id IN m_ids THEN
    RETURN 不可见（事务活跃未提交）
ELSE
    RETURN 可见（事务已提交）

4.6 RC vs RR 隔离级别

隔离级别 区别
READ COMMITTED 每次 SELECT 生成新 Read View
REPEATABLE READ 第一次 SELECT 生成 Read View，后续复用 
-- RC 隔离级别
-- 事务A: SELECT 1 (生成ReadView) → 事务B提交 → SELECT 2 (新ReadView)
-- 两次查询结果可能不同

-- RR 隔离级别
-- 事务A: SELECT 1 (生成ReadView) → 事务B提交 → SELECT 2 (复用ReadView)
-- 两次查询结果相同

5. 锁机制

5.1 锁的分类

graph TB
    subgraph 按操作分类
        S["锁"]
        S --> R["读锁/共享锁<br/>S锁"]
        S --> W["写锁/排他锁<br/>X锁"]
    end
    
    subgraph 按粒度分类
        S --> T["表锁"]
        S --> B["行锁"]
        S --> G["间隙锁"]
    end

5.2 共享锁与排他锁

-- 共享锁（S锁）- 读锁
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- 事务获得共享锁后，其他事务可以获取共享锁，但不能获取排他锁

-- 排他锁（X锁）- 写锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 事务获得排他锁后，其他事务不能获取任何锁

5.3 表锁

-- 手动加表锁
LOCK TABLES users READ;    -- 读锁
LOCK TABLES users WRITE;  -- 写锁

-- 解锁
UNLOCK TABLES;

5.4 行锁

-- 记录锁（Record Lock）
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 锁定 id=1 这一行

-- 临键锁（Next-Key Lock）
SELECT * FROM users WHERE id BETWEEN 1 AND 10 FOR UPDATE;
-- 锁定范围 (1, 10]，包含记录和间隙

-- 间隙锁（Gap Lock）
SELECT * FROM users WHERE id > 5 AND id < 10 FOR UPDATE;
-- 锁定间隙 (5, 10)，防止幻读

5.5 意向锁

-- 意向共享锁（IS）
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- 意向排他锁（IX）
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 作用：告诉其他事务该表有行锁，无需遍历每行检查

5.6 死锁与处理

-- 查看死锁日志
SHOW ENGINE INNODB STATUS;

-- 死锁示例
-- 事务A: UPDATE users SET name='A' WHERE id=1; (锁定id=1)
-- 事务B: UPDATE users SET name='B' WHERE id=2; (锁定id=2)
-- 事务A: UPDATE users SET name='A2' WHERE id=2; (等待id=2)
-- 事务B: UPDATE users SET name='B2' WHERE id=1; (死锁)

-- 解决策略
-- 1. InnoDB 自动检测并回滚小事务
-- 2. 按固定顺序访问资源
-- 3. 减少事务持锁时间

6. 主从复制

6.1 主从复制原理

sequenceDiagram
    participant Client as 客户端
    participant Master as 主库
    participant Binlog as Binlog
    participant Slave as 从库
    participant RelayLog as Relay Log
    
    Client->>Master: 写入数据
    Master->>Master: 执行事务
    Master->>Binlog: 写入 Binlog
    Master->>Client: 返回成功
    
    Note over Master,Slave: 异步复制
    Master->>Slave: 发送 Binlog 事件
    
    Slave->>Slave: I/O线程接收Binlog
    Slave->>RelayLog: 写入 Relay Log
    Slave->>Master: 返回 ACK
    
    Slave->>Slave: SQL线程重放
    Note over Slave: 数据同步完成

据《MySQL 主从复制全解：底层原理、复制模式差异》（CSDN），主从复制的核心流程：

  1. 主库 binlog dump 线程：主库将 binlog 发送给从库
  2. 从库 I/O 线程：接收 binlog 并写入 relay log
  3. 从库 SQL 线程：读取 relay log 并重放事件

6.2 异步复制

-- 主库配置
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW

-- 从库配置
[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;
SHOW SLAVE STATUS\G;

6.3 半同步复制

据《MySQL 半同步复制原理与配置详解》（腾讯云），半同步复制确保数据至少同步到一个从库才返回成功。

-- 安装半同步插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

-- 启用半同步
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- 配置参数
SET GLOBAL rpl_semi_sync_master_wait_for_slave_count = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  -- 超时ms

6.4 AFTER_SYNC vs AFTER_COMMIT

模式 说明 数据安全
AFTER_SYNC 等待 ACK 后提交事务 更高（MySQL 5.7+默认）
AFTER_COMMIT 提交事务后等待 ACK 可能丢失 
-- 查看当前模式
SHOW VARIABLES LIKE 'rpl_semi_sync_master_wait_point%';

7. 读写分离

7.1 读写分离架构

graph TB
    subgraph 应用层
        App["应用服务"]
    end
    
    subgraph 代理层
        Proxy["ProxySQL<br/>读写分离"]
    end
    
    subgraph 数据层
        Master["主库<br/>写"]
        Slave1["从库1<br/>读"]
        Slave2["从库2<br/>读"]
    end
    
    App --> Proxy
    Proxy --> Master
    Proxy --> Slave1
    Proxy --> Slave2

7.2 ProxySQL 配置

# 安装 ProxySQL
yum install -y proxysql

# 配置用户
INSERT INTO mysql_users (username, password, default_hostgroup) 
VALUES ('app_user', 'app_pass', 0);

-- 配置读写分离规则
INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup)
VALUES (1, '^SELECT.*FOR UPDATE$', 0);  -- 写

INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup)
VALUES (2, '^SELECT', 1);  -- 读

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

7.3 读写分离常见问题

问题 解决方案
主从延迟 使用延迟感知连接、强制走主库
数据一致性 最终一致性、可选同步写
热点数据 缓存 + 主从双写

8. 日志系统

8.1 错误日志

-- 查看错误日志位置
SHOW VARIABLES LIKE 'log_error%';

8.2 查询日志

-- 开启查询日志
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/query.log';

8.3 Binlog

-- 查看 binlog
SHOW BINARY LOGS;
SHOW MASTER STATUS;

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

-- 清理 binlog
PURGE BINARY LOGS BEFORE '2024-01-01';

8.4 Redo Log 与 Undo Log

日志 作用 内容
Redo Log 物理日志，恢复已提交事务 记录页修改
Undo Log 逻辑日志，回滚未提交事务 记录逆向操作
Binlog 归档日志，主从复制 记录数据变更 
graph LR
    subgraph 事务过程
        T["事务"]
        T --> W["写 Redo Log<br/>（prepare）"]
        W --> C["提交事务"]
        C --> F["写 Redo Log<br/>（commit）"]
    end
    
    subgraph 崩溃恢复
        F --> RC["Redo 恢复"]
    end
原创

Day57-60-MySQL高级

本文链接： Day57-60-MySQL高级

本文采用 CC BY-NC-SA 4.0 许可协议，转载请注明出处。

上一篇 Day68-Redis与MySQL双写一致性
下一篇 Day01-02-计算机组成原理与操作系统
评论交流

文章目录