02_2_Mysql_引擎_锁_事物

MySQL 存储引擎详解

一、常见存储引擎对比
存储引擎事务支持外键支持锁粒度适用场景核心特性InnoDB✅✅行锁高并发事务、OLTP支持 ACID、MVCC、崩溃恢复、热备份MyISAM❌❌表锁读密集型、OLAP、日志高速查询、全文索引、压缩表Memory❌❌表锁临时表/缓存、快速读写数据存于内存，重启丢失，支持哈希索引Archive❌❌行锁（插入时）日志归档、审计数据高压缩比、只支持插入和查询（不支持删除/更新）CSV❌❌表锁CSV 数据导入导出数据以 CSV 格式存储，可直接用文本编辑器查看Blackhole❌❌无锁数据复制测试、日志过滤接收数据但不存储，直接丢弃Federated❌❌依赖远程引擎跨服务器数据聚合访问远程表数据（需网络连接）NDB✅（集群事务）❌行锁高可用集群分布式存储、自动分片、内存优化（需 MySQL Cluster 环境）

二、核心引擎特性详解

1. InnoDB
   核心优势：
   支持事务（ACID）和 MVCC（多版本并发控制）。
   行级锁 + 间隙锁，有效减少锁冲突。
   支持外键约束，保证数据完整性。
   适用场景：
   电商、金融等高并发事务系统。
   需要崩溃恢复和热备份的关键业务。
   示例配置：
   CREATE TABLE orders (
   id INT PRIMARY KEY AUTO_INCREMENT,
   user_id INT,
   amount DECIMAL(10,2),
   FOREIGN KEY (user_id) REFERENCES users(id)
   ) ENGINE=InnoDB;

---

2. MyISAM
   核心优势：
   全文索引（FULLTEXT）支持快速文本搜索。
   表级锁，适合读多写少场景。
   支持压缩表（
   myisampack），节省存储空间。
   缺点：
   不支持事务，崩溃后恢复困难。
   写操作会锁定整个表，并发性能差。
   适用场景：
   数据仓库、日志分析等读密集型场景。
   临时生成只读报表。
   示例用法：
   CREATE TABLE logs (
   id INT PRIMARY KEY AUTO_INCREMENT,
   content TEXT,
   FULLTEXT(content)
   ) ENGINE=MyISAM;

---

3. Memory
   核心优势：
   数据存储在内存，读写速度极快。
   支持哈希索引，适合等值查询。
   缺点：
   服务器重启后数据丢失。
   表级锁，并发写入性能差。
   适用场景：
   会话缓存、临时计算中间结果。
   频繁查询的小型字典表。
   示例用法：
   CREATE TABLE session_cache (
   session_id VARCHAR(36) PRIMARY KEY,
   data TEXT
   ) ENGINE=Memory;

---

4. Archive
   核心优势：
   数据压缩率高达 90%，节省存储空间。
   支持批量插入（INSERT DELAYED）。
   缺点：
   仅支持插入和查询，不支持更新/删除。
   无索引支持，查询性能低。
   适用场景：
   历史日志归档（如操作日志、审计日志）。
   需要长期存储但极少查询的数据。
   示例用法：
   CREATE TABLE audit_logs (
   id INT AUTO_INCREMENT PRIMARY KEY,
   action VARCHAR(50),
   created_at TIMESTAMP
   ) ENGINE=Archive;

---

三、其他引擎特性

1. CSV 引擎
   数据以 CSV 格式存储，可直接用 Excel 编辑。
   不支持索引，仅适合简单数据交换。
   CREATE TABLE csv_data (
   id INT,
   name VARCHAR(50)
   ) ENGINE=CSV;

2. Blackhole 引擎
   接收数据但不存储，用于复制链路测试或过滤日志。
   CREATE TABLE blackhole_table (
   id INT PRIMARY KEY
   ) ENGINE=Blackhole;

3. NDB（集群引擎）
   分布式存储，支持自动分片和高可用性。
   需配合 MySQL Cluster 使用，适合电信级应用。

---

四、如何选择存储引擎？
需求推荐引擎理由高并发事务（OLTP）InnoDB支持事务、行锁、崩溃恢复读密集型分析（OLAP）MyISAM查询速度快，支持压缩表临时数据/缓存Memory内存存储，极速访问归档存储Archive高压缩比，节省空间跨服务器数据聚合Federated支持访问远程表数据

总结
MySQL 存储引擎的选择需结合业务场景和需求：
默认选择 InnoDB：满足大多数事务和并发需求。
特殊场景选专用引擎：如 Memory 用于缓存，Archive 用于归档。
避免使用废弃引擎：如 MySQL 8.0 已移除
MyISAM 的默认全文索引支持，建议迁移到 InnoDB。

MySQL锁机制与事务隔离级别详解

一、MySQL锁机制

1. 锁的维度分类
   分类维度锁类型说明使用场景性能乐观锁通过版本号或时间戳实现，提交时检测冲突（如SELECT … FOR UPDATE）高并发读多写少场景悲观锁默认锁策略，通过行锁/表锁提前加锁写操作频繁场景操作读锁（共享锁）LOCK IN SHARE MODE，允许其他读锁，阻塞写锁读取数据需保证一致性写锁（排他锁）FOR UPDATE，阻塞其他读写锁更新/删除数据时使用粒度表锁锁整张表，MyISAM默认锁全表操作（如DDL语句）行锁InnoDB默认锁，锁定单行或多行（需索引）高并发精准操作其他间隙锁（Gap）锁定索引记录间的间隙，防止幻读范围查询（WHERE age BETWEEN 20 AND 30）临键锁（Next-Key）行锁+间隙锁组合，锁定左开右闭区间InnoDB默认行锁策略

---

2. 死锁与优化
   死锁示例：
   – 事务1

BEGIN;
UPDATE users SET balance=balance-100 WHERE id=1; – 获取id=1的行锁
UPDATE users SET balance=balance+100 WHERE id=2; – 等待事务2释放id=2的锁
– 事务2

BEGIN;
UPDATE users SET balance=balance-100 WHERE id=2; – 获取id=2的行锁
UPDATE users SET balance=balance+100 WHERE id=1; – 等待事务1释放id=1的锁

解决方案：
检测与回滚：MySQL自动检测死锁，回滚代价较小的事务
优化策略：
按固定顺序访问资源（如先操作id小的记录）
减少事务粒度，避免长事务
使用
SHOW ENGINE INNODB STATUS分析死锁日志

二、事务隔离级别

1. 四种隔离级别对比
   隔离级别脏读不可重复读幻读实现机制读未提交可能可能可能无锁，直接读最新数据读已提交（RC）不可能可能可能每次查询生成新ReadView可重复读（RR）不可能不可能可能*事务首次查询生成ReadView，使用MVCC+临键锁串行化不可能不可能不可能全表加锁

注：InnoDB在RR级别通过临键锁（Next-Key Lock）解决了幻读问题。

---

2. MVCC多版本并发控制
   核心组件
   Undo版本链：
   每条记录包含
   DB_TRX_ID（事务ID）和
   DB_ROLL_PTR（回滚指针），形成多版本链表。

ReadView一致性视图：
事务启动时生成，包含：
m_ids：当前活跃事务ID集合
min_trx_id：最小活跃事务ID
max_trx_id：预分配的下一个事务ID
creator_trx_id：当前事务ID
可见性判断规则
若记录的
DB_TRX_ID < min_trx_id：可见（事务已提交）
若记录的
DB_TRX_ID >= max_trx_id：不可见（事务未开始）
若
DB_TRX_ID在
m_ids中：不可见（事务未提交）
否则：可见

不同隔离级别的实现差异
隔离级别ReadView生成时机幻读解决方案读已提交（RC）每次SELECT生成新ReadView无，可能发生幻读可重复读（RR）事务首次SELECT生成ReadView临键锁（Next-Key Lock）阻止插入示例：RR级别避免幻读
– 事务1

BEGIN;
SELECT * FROM users WHERE age > 20; – 生成ReadView，并加临键锁（20, +∞）
– 事务2

INSERT INTO users (age) VALUES (25); – 阻塞，直到事务1提交

---

三、实战建议

1. 隔离级别选择
   推荐使用RR：平衡性能与一致性，InnoDB的临键锁已解决幻读问题
   RC适用场景：需更高并发且接受不可重复读（如统计类查询）
2. 锁优化策略
   避免全表扫描（无索引的更新/删除会升级为表锁）
   使用
   SELECT … FOR UPDATE NOWAIT（非阻塞获取锁）
   监控锁等待：
   SHOW STATUS LIKE ‘innodb_row_lock%’
3. MVCC调优
   控制长事务，避免Undo版本链过长
   定期清理历史版本：
   SET GLOBAL innodb_purge_batch_size=300;

---

总结
锁机制与事务隔离级别是数据库并发控制的核心，理解MVCC的版本链和ReadView机制是优化高并发场景的关键。选择合理的隔离级别，结合索引与锁策略，才能实现性能与一致性的平衡。

---

数据库并发问题详解：脏读、不可重复读、幻读

1. 脏读（Dirty Read）
   定义：
   事务 A 读取了事务 B 未提交的修改数据，若事务 B 回滚，事务 A 读取的数据即为“脏数据”。
   示例：
   – 事务 A

BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1; – 未提交
– 事务 B

BEGIN;
SELECT balance FROM account WHERE id = 1; – 读到未提交的 balance（脏读）

后果：
数据不一致性，可能导致业务逻辑错误（如基于脏数据计算）。
解决方案：
使用 读已提交（Read Committed） 隔离级别，确保只能读取已提交的数据。
2. 不可重复读（Non-Repeatable Read）
定义：
同一事务内，多次读取同一数据结果不一致（因其他事务提交了修改或删除）。
示例：
– 事务 A

BEGIN;
SELECT balance FROM account WHERE id = 1; – 第一次读：100
– 事务 B

BEGIN;
UPDATE account SET balance = 200 WHERE id = 1;
COMMIT;
– 事务 A

SELECT balance FROM account WHERE id = 1; – 第二次读：200（不可重复读）

后果：
事务内逻辑可能依赖多次读取的一致性（如统计、校验）。
解决方案：
使用 可重复读（Repeatable Read） 隔离级别，通过 MVCC 保证事务内一致性视图。
3. 幻读（Phantom Read）
定义：
同一事务内，多次按相同条件查询，结果集的行数发生变化（因其他事务插入或删除了符合条件的数据）。
示例：
– 事务 A

BEGIN;
SELECT * FROM account WHERE balance > 500; – 第一次查：3 条
– 事务 B

BEGIN;
INSERT INTO account (balance) VALUES (600);
COMMIT;
– 事务 A

SELECT * FROM account WHERE balance > 500; – 第二次查：4 条（幻读）

后果：
影响范围操作（如批量处理或统计），导致逻辑错误。
解决方案：
MySQL 可重复读（RR）隔离级别：通过 间隙锁（Gap Lock） 和 临键锁（Next-Key Lock） 阻止其他事务插入符合条件的数据。
串行化（Serializable） ：彻底禁止并发，牺牲性能。

4. 对比总结
   问题本质隔离级别解决方案实现机制脏读读未提交数据读已提交（RC）事务提交后数据才可见不可重复读同一数据被修改/删除可重复读（RR）MVCC 一致性视图，保留历史版本幻读结果集行数变化（新增）可重复读（RR）+ 间隙锁临键锁锁定索引范围，阻止插入

---

5. MySQL InnoDB 的特殊实现
   可重复读（RR）级别下：
   MVCC：事务首次查询生成一致性视图（ReadView），后续读取基于此视图。
   临键锁（Next-Key Lock） ：锁住索引记录及其间隙，防止其他事务插入。
   因此 InnoDB 的 RR 级别实际可避免幻读（与 SQL 标准不同）。

---

6. 隔离级别选择建议
   隔离级别脏读不可重复读幻读适用场景读未提交（RU）✅✅✅测试环境，无需一致性读已提交（RC）❌✅✅高并发读，接受不可重复读（如统计）可重复读（RR）❌❌❌*默认选择，保证事务内一致性串行化（S）❌❌❌强一致性，低并发场景

注：InnoDB 的 RR 级别通过锁机制解决了幻读，但需索引支持。若无索引，可能退化为表锁。

---

总结
脏读是读到了未提交的“临时数据”。
不可重复读是同一数据被其他事务修改后多次读不一致。
幻读是结果集因其他事务插入/删除而变化。
InnoDB 的 RR 隔离级别通过 MVCC 和间隙锁，实际可避免所有三种问题。