本篇笔记聚焦 MySQL 进阶核心知识点，涵盖事务 (ACID / 隔离级别)、锁机制、存储引擎、慢查询日志、重做日志及并发事务问题，是数据库开发与优化的必备基础，内容干货满满，适合复习巩固！

七、事务（Transaction）

核心特性

事务是一组 SQL 操作的最小执行单元，遵循「要么全部成功，要么全部失败」原则，用于保证数据的一致性和完整性，支持COMMIT（提交）和ROLLBACK（回滚）。

---

ACID 属性（必背！）

1. 原子性（Atomicity）事务不可分割，所有操作要么全部执行成功，要么全部回滚，不允许部分执行。
2. 一致性（Consistency）事务执行前后，数据库的完整性约束不被破坏，始终保持一致状态。
3. 隔离性（Isolation）并发事务之间相互隔离，一个事务未提交的修改，对其他事务不可见。
4. 持久性（Durability）事务一旦提交，对数据的修改永久生效，即使数据库崩溃也不会丢失。

---

基础语法

-- 开启事务
START TRANSACTION;
-- 执行SQL操作（增删改）
INSERT INTO users (username) VALUES ('john_doe');
UPDATE orders SET user_id = LAST_INSERT_ID() WHERE order_id = 123;
-- 提交事务（成功执行）
COMMIT;
-- 回滚事务（执行失败）
ROLLBACK;

实战案例：银行账户转账

1. 创建数据表

CREATE DATABASE bank;
USE bank;
CREATE TABLE accounts (
    account_number INT PRIMARY KEY,  -- 账号
    account_name VARCHAR(100),       -- 账户名
    balance DECIMAL(15, 2)           -- 余额（保留两位小数）
);

2. 编写事务存储过程

-- 修改语句结束符为//
DELIMITER //  
CREATE PROCEDURE TransferFunds(
    IN sourceAccount INT,    -- 源账户
    IN destinationAccount INT, -- 目标账户
    IN transferAmount DECIMAL(15,2) -- 转账金额
)
BEGIN
    START TRANSACTION;  -- 开启事务
    SET autocommit=0;   -- 关闭自动提交
    
    -- 定义变量
    SET @source = sourceAccount;
    SET @dest = destinationAccount;
    SET @amount = transferAmount;
    
    -- 查询源账户余额
    SELECT @balance := balance FROM accounts WHERE account_number = @source;
    
    -- 余额判断
    IF @balance < @amount THEN
        ROLLBACK;  -- 回滚
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足，转账失败';
    ELSE
        -- 扣减源账户、增加目标账户
        UPDATE accounts SET balance = balance - @amount WHERE account_number = @source;
        UPDATE accounts SET balance = balance + @amount WHERE account_number = @dest;
        COMMIT;    -- 提交事务
    END IF;
END //
-- 恢复结束符为;
DELIMITER ; 

3. 调用存储过程

CALL TransferFunds(101, 102, 100);

事务隔离级别（解决并发问题）

表格

隔离级别	脏读	不可重复读	幻读	说明
READ UNCOMMITTED（读未提交）	✅	✅	✅	最低级别，可读取未提交数据
READ COMMITTED（读已提交）	❌	✅	✅	仅读取已提交数据，避免脏读
REPEATABLE READ（可重复读）	❌	❌	✅	MySQL 默认级别，避免脏读、不可重复读
SERIALIZABLE（串行化）	❌	❌	❌	最高级别，事务串行执行，性能极低

事务应用场景

1. 金融交易：银行转账、股票交易
2. 订单处理：订单创建 + 库存扣减 + 支付记录
3. 数据一致性维护：多表关联更新

八、锁（Lock）

核心作用

MySQL 中控制并发事务访问资源的机制，防止数据被并发修改导致不一致，保证数据安全。

---

锁的分类

1. 按锁粒度划分

• 表锁：锁定整张表，粒度大、并发低，MyISAM 默认使用。
• 行锁：锁定单行数据，粒度小、并发高，InnoDB 默认使用。
• 间隙锁：锁定索引间隙，防止幻读，InnoDB 可重复读级别下使用。

2. 按锁性质划分

• 共享锁（读锁）：多个事务可同时加读锁，允许并发读取，禁止写入。语法：SELECT ... LOCK IN SHARE MODE;
• 排它锁（写锁）：独占锁，一个事务加锁后，其他事务无法读写。语法：SELECT ... FOR UPDATE;、UPDATE/DELETE自动加锁。

---

锁的常见问题

1. 死锁多个事务互相等待对方释放锁，无限僵持。✅ 解决：MySQL 自动检测并回滚小事务；开发中统一加锁顺序。
2. 锁等待超时事务等待锁超时自动回滚。✅ 解决：优化 SQL，缩短事务执行时间。

---

锁的应用场景

• 票务系统：防止超卖
• 金融系统：账户取款并发控制
• 事务隔离：实现不同隔离级别

九、数据库存储引擎

MySQL 支持多种存储引擎，InnoDB 是默认首选，不同引擎适用场景不同。

---

常用引擎对比

表格

引擎	事务支持	锁粒度	适用场景
InnoDB	✅	行锁	高并发、金融、电商、订单系统
MyISAM	❌	表锁	读多写少、日志、静态数据
Memory	❌	表锁	临时数据、内存缓存
NDB	✅	行锁	分布式集群、高可用系统

---

InnoDB 核心特性（重点）

1. 支持完整 ACID 事务
2. 行级锁 + MVCC（多版本并发控制），高并发性能强
3. 支持故障恢复，数据安全性高
4. 支持 B + 树索引、外键约束

---

常用操作命令

-- 查看所有支持的引擎
SHOW ENGINES;

-- 查看表使用的引擎
SHOW CREATE TABLE accounts;

-- 查询指定库下表的引擎
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'bank';

InnoDB 关键配置（my.cnf）

# 默认存储引擎
default-storage-engine = InnoDB
# 缓冲池大小（建议服务器内存70%）
innodb_buffer_pool_size = 4G
# 事务日志大小
innodb_log_file_size = 512M
# 每个表独立表空间
innodb_file_per_table = ON
# 事务提交日志策略（1=最高安全）
innodb_flush_log_at_trx_commit = 1

十、慢查询日志

核心作用

记录执行时间超过阈值的 SQL 语句，用于数据库性能优化，定位慢 SQL。

---

核心配置参数

表格

参数	作用	默认值
slow_query_log	开启 / 关闭慢日志	OFF
long_query_time	慢查询阈值（秒）	10
log_queries_not_using_indexes	记录未使用索引的 SQL	OFF
slow_query_log_file	慢日志文件路径	系统默认

---

配置方式

1. 配置文件永久生效

slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = ON
min_examined_row_limit = 1000

2. SQL 动态生效（重启失效）

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = ON;

# 系统工具分析慢日志（按执行时间排序）
mysqldumpslow -s t /var/log/mysql/mysql-slow.log

十一、重做日志（Redo Log）

核心作用

InnoDB 专属日志，保证事务持久性，数据库崩溃后自动恢复数据。

---

工作原理

1. 事务修改数据时，先写入重做日志缓冲区
2. 事务提交时，日志写入磁盘
3. 数据库崩溃重启后，根据重做日志恢复未持久化的数据

---

关键配置

# 日志文件大小
innodb_log_file_size = 512M
# 日志文件数量
innodb_log_files_in_group = 2
# 日志缓冲区大小
innodb_log_buffer_size = 64M

十二、并发事务常见问题

并发执行事务时，会出现数据不一致问题，通过隔离级别 + 锁解决。

---

1. 更新丢失

多个事务修改同一数据，最后提交的事务覆盖之前的修改。✅ 解决：加排它锁、使用事务隔离。

2. 脏读

读取到其他事务未提交的数据，若对方回滚，数据无效。✅ 解决：隔离级别≥READ COMMITTED。

3. 不可重复读

同一事务内，多次读取同一数据，结果不一致（被其他事务修改）。✅ 解决：隔离级别≥REPEATABLE READ。

4. 幻读

同一事务内，范围查询结果行数变化（其他事务插入 / 删除数据）。✅ 解决：REPEATABLE READ + 间隙锁 / SERIALIZABLE。

---

隔离级别设置语法

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

学习总结

1. 事务是数据安全的核心，牢牢掌握 ACID 和隔离级别，金融 / 订单场景必用。
2. InnoDB是生产环境首选引擎，支持事务、行锁、故障恢复，适配高并发。
3. 锁用于控制并发，行锁提升性能，表锁简单安全，间隙锁解决幻读。
4. 慢日志 + 重做日志是数据库优化和数据安全的双保障，必须熟练配置。
5. 并发问题通过隔离级别 + 锁解决，MySQL 默认 REPEATABLE READ 满足绝大多数场景。