Mysql存储引擎、锁机制

Mysql存储引擎

InnoDB​（MySQL 5.5 及以后版本中的默认存储引擎）

​​事务支持​​：支持 ​​ACID 事务​​，适合需要高可靠性的场景（如支付、订单）。
​​锁机制​​：默认使用 ​​行级锁​​，支持高并发操作。
​​外键约束​​：支持外键，保证数据完整性。
​​崩溃恢复​​：通过 ​​redo log​​ 和 ​​undo log​​ 实现崩溃后的数据恢复。
​​存储结构​​：数据按主键聚簇索引存储，二级索引保存主键值。
​​适用场景​​：OLTP（在线事务处理）、高并发读写、需要事务的场景。

MyISAM​

事务支持​​：​​不支持事务​​，无法保证数据一致性。
​​锁机制​​：​​表级锁​​，并发写入性能差。
​​存储结构​​：数据文件（.MYD）、索引文件（.MYI）、表结构文件（.frm）分离。
​​特性​​：支持全文索引（FULLTEXT）、压缩表、空间索引（GIS）。
​​适用场景​​：读多写少（如日志分析）、静态数据、不需要事务的场景。

Memory（HEAP）​

数据存储​​：数据完全存储在内存中，重启后数据丢失。
​​锁机制​​：表级锁，并发性能一般。
​​特性​​：支持哈希索引和B树索引，查询速度极快。
​​适用场景​​：临时表、缓存表、快速查询中间结果。

Archive​

存储方式​​：数据高度压缩存储，适合归档。
​​写入性能​​：仅支持插入和查询，不支持删除和更新。
​​适用场景​​：日志归档、历史数据存储。

CSV​

存储方式​​：数据以纯文本CSV格式存储。
​​特性​​：不支持索引，适合导入/导出数据。
​​适用场景​​：与外部系统交换数据。

Blackhole​

特性​​：接收数据但不存储，直接丢弃。常用于数据复制的中继。

核心区别对比​

如何选择存储引擎

​​需要事务​​：必须选 ​​InnoDB​​。
​​读多写少​​：若不需要事务，可选 ​​MyISAM​​（注意表锁问题）。
​​临时数据/缓存​​：使用 ​​Memory​​，但需注意数据易失性。
​​归档数据​​：选择 ​​Archive​​ 或 InnoDB 压缩表。
​​高并发写入​​：优先 InnoDB（行级锁）。

操作示例​

​​查看表的存储引擎​​：

SHOW TABLE STATUS LIKE 'table_name';

​​修改表的存储引擎​​：

ALTER TABLE table_name ENGINE = InnoDB;

​​设置默认存储引擎​​（需在 my.cnf 中配置）：

[mysqld]
default-storage-engine = InnoDB

总结

InnoDB​​ 是 MySQL 默认引擎，适用于大多数场景，尤其是需要事务和高并发的场景。
​​MyISAM​​ 在只读或低并发写入场景下仍有价值，但逐渐被 InnoDB 替代。
其他引擎（如 Memory、Archive）在特定场景下能发挥独特优势。

MySQL 的锁机制

锁的粒度分类​

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

表级锁（Table-Level Lock）​

定义​​：直接锁定整张表，粒度最粗，并发度最低。
​​适用引擎​​：MyISAM、Memory、CSV 等。
​​特点​​：
​​共享锁（S）​​：允许其他事务读表，但禁止写。
​​排他锁（X）​​：禁止其他事务读写表。
​​操作示例​​：

-- MyISAM 隐式加表锁（自动管理）
SELECT * FROM table_name;          -- 加共享锁
INSERT INTO table_name VALUES (...); -- 加排他锁

页级锁（Page-Level Lock）​

定义​​：锁定数据页（一组连续的行），粒度介于表锁和行锁之间。
​​适用引擎​​：BDB（已被 MySQL 废弃）。

行级锁（Row-Level Lock）​

定义​​：锁定单行或多行数据，粒度最细，并发度最高。
​​适用引擎​​：InnoDB。
​​特点​​：
​​共享锁（S Lock）​​：允许其他事务读，但禁止修改该行。
​​排他锁（X Lock）​​：禁止其他事务读写该行。
​​操作示例​​：

-- 显式加行锁（InnoDB）
SELECT * FROM table_name WHERE id = 1 FOR SHARE;   -- 共享锁
SELECT * FROM table_name WHERE id = 1 FOR UPDATE;  -- 排他锁

​​子类型​​：
记录锁（Record Lock）​​：锁定索引中的一条记录。
​​间隙锁（Gap Lock）​​：锁定索引记录之间的间隙（防止幻读）。
​​临键锁（Next-Key Lock）​​：记录锁 + 间隙锁（锁定左开右闭区间）。
​​插入意向锁（Insert Intention Lock）​​：在插入前标记意向，避免间隙锁冲突。

InnoDB 的行级锁实现​

记录锁（Record Lock）​

作用​​：锁定索引中的一条记录。
​​示例​​：SELECT * FROM user WHERE id = 1 FOR UPDATE;

间隙锁（Gap Lock）​

锁定范围​​：索引记录之间的 ​​间隙​​（即两个索引值之间的区间）。
作用​​：锁定索引记录之间的间隙，防止其他事务插入数据。
特点​：
不锁定现有记录​​：仅锁定间隙，不影响已存在的记录修改或删除。
​​仅作用于非唯一索引​​：
—唯一索引的等值查询（如 WHERE id=10）​​不会加间隙锁​​（因唯一性保证不会有重复值）。
—唯一索引的范围查询（如 WHERE id > 10）仍会加间隙锁。
​​示例​​：

-- 表结构：id（主键）, age（普通索引）
-- 数据：id=1(age=10), id=2(age=20)-- 事务 A（RR 隔离级别）
SELECT * FROM users WHERE age = 15 FOR UPDATE;
-- 锁定间隙 (10,20)，阻止插入 age=15 的记录-- 事务 B 尝试插入
INSERT INTO users (age) VALUES (15); -- 被阻塞

临键锁（Next-Key Lock）​

间隙锁是临键锁的组成部分，临键锁 = 行锁（Record Lock） + 间隙锁（Gap Lock）。当临键锁仅锁定间隙（不锁定具体行）时，退化为纯间隙锁。
定义​​：行锁 + 间隙锁，锁定左开右闭区间（例如 (1,5]）。
​​目的​​：解决幻读问题（在 ​​RR 隔离级别​​ 下，InnoDB 对范围查询默认使用 ​​临键锁​​（Next-Key Lock），其锁定范围为 ​​左开右闭区间​​。）。
适用于所有索引​​：包括主键索引、唯一索引和普通索引。
​​示例​​：

-- 表结构：id（主键）, age（普通索引）
-- 数据：id=1(age=10), id=2(age=20)-- 事务 A（RR 隔离级别）
SELECT * FROM users WHERE age > 10 AND age <= 20 FOR UPDATE;
-- 加临键锁 (10,20]，阻止插入 age ∈ (10,20] 的数据-- 事务 B 尝试插入或修改
INSERT INTO users (age) VALUES (15); -- 被阻塞
UPDATE users SET age = 15 WHERE id = 1; -- 被阻塞（修改为 age=15）

插入意向锁（Insert Intention Lock）​

作用​​：在插入数据前设置，表示事务想在某个间隙插入数据，与间隙锁冲突。

按锁的行为分类​

共享锁（Shared Lock, S Lock； 又称读锁）​

​​作用​​：允许其他事务读，但禁止修改锁定的数据。
​​使用场景​​：读操作（如 SELECT … LOCK IN SHARE MODE或SELECT … FOR SHARE）。

​​语法​	说明​
SELECT … LOCK IN SHARE MODE	旧版语法（MySQL 5.7 及之前版本常用），用于为读取的数据行加共享锁。
SELECT … FOR SHARE	新版语法（MySQL 8.0 引入），替代 LOCK IN SHARE MODE，功能相同，但支持更多扩展选项（如 NOWAIT、SKIP LOCKED）。
​​兼容性​​：多个共享锁可共存。

排他锁（Exclusive Lock, X Lock；又称写锁、独占锁）​

​​作用​​：禁止其他事务读写锁定的数据。
​​使用场景​​：写操作（如 SELECT … FOR UPDATE、INSERT/UPDATE/DELETE）。
​​兼容性​​：与其他锁互斥。

意向锁（Intention Lock）​

​​目的​​：快速判断表级锁和行级锁的冲突，避免逐行检查。
​​类型​​：
​​意向共享锁（IS）​​：事务打算对某些行加共享锁。
​​意向排他锁（IX）​​：事务打算对某些行加排他锁。
​​兼容性​​：

按锁的实现方式分类​

乐观锁​

乐观锁则基于一种乐观的态度来处理并发问题，它假设数据冲突将不会频繁发生，因此在数据读取时不会加锁，而是在更新数据时检查数据是否被其他事务修改过。
​​实现​​：通过版本号或时间戳（应用层控制）。
适用场景​​：读多写少，冲突概率低。
​​示例​​：

UPDATE product SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = 5;

工作机制：

数据读取：

事务首先读取需要修改的数据及其版本号（或时间戳）。这个版本号是在数据库中额外维护的一个字段，用于跟踪数据的更新情况。

数据操作：

事务在本地对读取的数据进行修改，但不立即更新到数据库中。

提交前的检查：

在提交事务之前，事务会再次查询数据库中对应数据的版本号，并将其与第一步读取的版本号进行比较。

数据更新：

如果版本号相同，说明数据在读取和提交之间没有被其他事务修改过，事务可以安全地更新数据，并将版本号增加（或更新时间戳）。
如果版本号不同，说明数据在读取和提交之间已经被其他事务修改过，此时当前事务可以选择重试（重新读取数据并尝试更新）、回滚或向应用层报告错误。

提交事务：

如果数据更新成功，事务将提交，所做的修改将被保存到数据库中。

悲观锁​

悲观锁是假定冲突将频繁发生，因此在操作数据之前先锁定数据。在MySQL中，悲观锁可以通过行级锁（InnoDB存储引擎提供）或表级锁（MyISAM或InnoDB在某些情况下）来实现。
​​适用场景​​：写多读少，冲突概率高。
实现​​：
1.使用SELECT … FOR UPDATE）：
这是最常用的悲观锁实现方式。当事务使用SELECT … FOR UPDATE语句读取记录时，MySQL会对这些记录加锁，其他事务必须等待锁释放才能修改这些记录。

START TRANSACTION;
SELECT * FROM accounts WHERE id = 100 FOR UPDATE;
-- 在这里进行更新操作
UPDATE accounts SET balance = balance - 100 WHERE id = 100;
COMMIT;

注意：FOR UPDATE必须在一个事务中使用，否则会立即释放锁。
2.使用表锁
虽然表锁不是悲观锁的最佳实践（因为它会锁定整个表），但在某些情况下仍然可以使用。通过LOCK TABLES和UNLOCK TABLES命令可以显式地锁定和解锁表。

LOCK TABLES accounts WRITE;
-- 在这里进行更新操作
UPDATE accounts SET balance = balance - 100 WHERE id = 100;
UNLOCK TABLES;

索引类型与加锁规则​

在 ​​可重复读（RR）隔离级别​​ 下，SELECT * FROM users WHERE age = 25 FOR UPDATE; 的加锁行为取决于 age 字段的索引类型和数据分布。以下是不同场景下的详细分析：

age 是普通索引（非唯一索引）​

​​数据分布假设​​：
表中存在 age 为 20、25、30 的记录（假设 age 索引结构如下）：
age索引树：20 → 25 → 30
​​加锁范围​​：
​​行锁（Record Lock）​​：锁定所有 age=25 的记录。
​​间隙锁（Gap Lock）​​：锁定 age=25 前后的间隙：
左间隙：(20, 25)
右间隙：(25, 30)
​​Next-Key Lock​​：实际加锁为 ​​左开右闭区间​​（InnoDB 默认使用 Next-Key Lock），即：
(20, 25]（锁定 age=25 的行及其左间隙）
(25, 30)（锁定右间隙，防止插入新的 age=25）
​​效果​​：
其他事务无法插入或修改 age=25 的记录，也无法在 (20, 30) 区间插入 age=25 的新数据。
彻底避免幻读。

age 是唯一索引（Unique Index）​

​​数据分布假设​​：
age 是唯一索引，表中存在 age=25 的记录。
​​加锁范围​​：
​​仅行锁​​：锁定 age=25 的记录。
​​无间隙锁​​：唯一索引的唯一性保证了其他事务无法插入 age=25 的新数据，因此无需加间隙锁。
​​例外情况​​：
如果 age=25 ​​不存在​​，则会锁定该值所在的间隙。例如：
现有 age=20 和 age=30，则锁定 (20, 30) 间隙，阻止插入 age=25。

age 无索引​

​​加锁范围​​：
​​全表扫描​​：所有记录的行锁 + 所有间隙的间隙锁（即锁全表）。
​​效果​​：其他事务无法插入或修改表中任何数据，性能极差。
​​建议​​：
必须为 WHERE 条件字段添加索引，避免全表锁。

InnoDB 特有的锁​

自增锁（AUTO-INC Lock）​

作用​​：
确保自增列（AUTO_INCREMENT）的值连续且唯一。
​​模式​​：
​​传统模式​​：表级锁，事务完成后释放（影响并发插入性能）。
​​连续模式​​（MySQL 8.0 默认）：轻量级锁，仅保证自增值的连续性。

隐式锁（Implicit Lock）​

触发条件​​：插入操作时，对新插入的数据自动加隐式排他锁。
​​作用​​：避免其他事务在插入完成前访问未提交的数据。

死锁

死锁（Deadlock）是指两个或多个事务相互等待对方释放锁资源，导致无限阻塞的现象。MySQL 中死锁通常由 ​​行级锁（Record Lock）​​、​​间隙锁（Gap Lock）​​ 和 ​​Next-Key Lock​​ 的冲突引起，尤其在 ​​可重复读（RR）​​ 隔离级别下更易发生

导致死锁的锁类型​

锁类型​	描述​	死锁场景​
行锁（Record Lock）​	锁定某一行记录，其他事务无法修改或删除。	多个事务以不同顺序请求同一批行锁时，形成循环等待。
间隙锁（Gap Lock）​	锁定索引记录之间的间隙，防止其他事务插入数据。	多个事务在相同间隙范围内插入数据，互相等待对方释放间隙锁。
Next-Key Lock​	行锁 + 间隙锁，锁定一个左开右闭的区间（如 (5,10]）。	事务以相反顺序锁定不同范围的 Next-Key Lock，形成环路。
插入意向锁（Insert Intention Lock）​	一种特殊的间隙锁，表示事务准备在某个间隙插入数据。	多个事务在同一间隙的不同位置插入数据，互相等待对方的间隙锁释放。

典型死锁场景与原理​

行锁冲突（交叉更新顺序）​

场景​​：
事务 A 和事务 B 以相反顺序更新两条记录。
​​示例​​：

-- 事务 A
UPDATE users SET name = 'A' WHERE id = 1;  -- 锁定 id=1 的行
UPDATE users SET name = 'B' WHERE id = 2;  -- 尝试锁定 id=2 的行（被事务 B 阻塞）-- 事务 B
UPDATE users SET name = 'B' WHERE id = 2;  -- 锁定 id=2 的行
UPDATE users SET name = 'A' WHERE id = 1;  -- 尝试锁定 id=1 的行（被事务 A 阻塞）

死锁原因​​：
事务 A 持有 id=1 的行锁，等待 id=2 的行锁；事务 B 持有 id=2 的行锁，等待 id=1 的行锁，形成循环依赖。

间隙锁冲突（并发插入相同间隙）​

​​场景​​：
事务 A 和事务 B 在同一个间隙内插入数据，但插入位置不同。
​​示例​​：

-- 表结构：id 是主键，当前存在 id=10 和 id=20 的记录。
-- 事务 A
INSERT INTO users (id) VALUES (15);  -- 尝试获取间隙 (10,20) 的插入意向锁-- 事务 B
INSERT INTO users (id) VALUES (18);  -- 尝试获取间隙 (10,20) 的插入意向锁

​​死锁原因​​：
事务 A 和事务 B 都试图在间隙 (10,20) 插入数据。
插入意向锁与已存在的间隙锁（如 Next-Key Lock）冲突，导致互相等待。

Next-Key Lock 冲突（范围查询与插入）​

场景​​：
事务 A 范围查询加 Next-Key Lock，事务 B 在范围内插入数据。
​​示例​​：

-- 事务 A（RR 隔离级别）
SELECT * FROM users WHERE id > 10 AND id < 20 FOR UPDATE;  -- 加 Next-Key Lock (10,20]-- 事务 B
INSERT INTO users (id) VALUES (15);  -- 尝试获取插入意向锁，被事务 A 阻塞-- 事务 A 再次操作
INSERT INTO users (id) VALUES (15);  -- 尝试获取插入意向锁，被事务 B 阻塞

死锁原因​​：
事务 A 持有 (10,20] 的 Next-Key Lock，事务 B 等待插入；事务 A 随后尝试插入同一条数据，被事务 B 阻塞，形成死锁。

死锁产生条件​

互斥​​：资源被独占。
​​请求与保持​​：事务持有锁并请求新锁。
​​不可剥夺​​：锁只能由持有者释放。
​​循环等待​​：事务间形成环形等待。

死锁处理​

检测​​：InnoDB 使用等待图（wait-for graph）检测死锁。
​​解决​​：强制回滚代价较小的事务。

查看死锁日志​​：

SHOW ENGINE INNODB STATUS;  -- 查看 LATEST DETECTED DEADLOCK 部分

查找输出中的 LATEST DETECTED DEADLOCK 部分，这里会显示导致死锁的具体事务信息，包括涉及的表、行、锁和事务 ID。
​​关键信息解析​​：
​​TRANSACTION​​：参与死锁的事务 ID 和状态。
​​HOLDS THE LOCK(S)​​：事务当前持有的锁类型和范围。
​​WAITING FOR THIS LOCK​​：事务正在等待的锁类型和范围。
​​WE ROLL BACK TRANSACTION​​：被回滚的事务 ID。

查询MySQL整体的锁状态：

show status like 'innodb_row_lock_%';

Innodb_row_lock_current_waits：当前正在阻塞等待锁的事务数量。
Innodb_row_lock_time：MySQL启动到现在，所有事务总共阻塞等待的总时长。
Innodb_row_lock_time_avg：平均每次事务阻塞等待锁时，其平均阻塞时长。
Innodb_row_lock_time_max：MySQL启动至今，最长的一次阻塞时间。
Innodb_row_lock_waits：MySQL启动到现在，所有事务总共阻塞等待的总次数。

使用 INFORMATION_SCHEMA 表获取详细信息：

可以查询 INFORMATION_SCHEMA 表来获取当前进行的事务和连接信息。例如，使用以下 SQL 语句获取活动中的事务信息：

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

查询进程列表

使用 SHOW PROCESSLIST 命令可以看到当前所有连接和执行中的 SQL 语句：

SHOW PROCESSLIST;

输出将包括每个连接的线程 ID、USER、HOST、DB、COMMAND、TIME、STATE 和 INFO 字段，其中 INFO 字段显示正在执行的 SQL 语句。

终止导致死锁的事务

一旦确认了具体的事务和 SQL 语句，下一步是终止这个事务。
根据 SHOW ENGINE INNODB STATUS 和 SHOW PROCESSLIST 得到的 ID，可以使用 KILL 命令终止相应的连接。

-- 从SHOW PROCESSLIST结果中获取具体线程ID
KILL 12345;

避免死锁​

保持事务简短，减少锁持有时间。
按固定顺序访问表和行。
合理设计索引，减少锁范围。
使用 SELECT … FOR UPDATE 时明确指定索引。