【每日八股】复习 MySQL Day3：锁

昨日内容复习

MySQL 使用 B+ 树作为索引的优势是什么？

B+ 树是一种多叉树，其非叶子结点仅存放索引，而叶子结点存放真正的数据，叶子结点之间通过双向链表连接，可以优化查询效率。

通过 B+ 树存储千万级别的数据，在访问时只需要进行 3 ～ 4 次的磁盘 I/O 操作。在对 B+ 树进行修改时，由于 B+ 树仅在叶子结点存放真正的数据，因此修改数据时不会修改树的结果。与 B 树相比，由于 B 树在叶子结点和非叶子结点都会存放数据，因此 B 树在修改时可能会涉及树的变形。

索引有哪几种？

• 单值索引：一个索引只包含一个列，一个表可以包含多个单值索引；
• 唯一索引：索引列的值必须唯一，但允许存在重复的 null；
• 主键索引：设定数据库表中某个键为主键时，该主键会自动成为主键索引；
• 复合索引：一个索引包含多个列；
• 前缀索引：对字符类型的前几个字符建立索引；

什么是最左匹配原则？

MySQL 的最左匹配原则是 B+ 树联合索引的核心特性，指查询时必须从索引的最左列开始，并按照联合索引的定义顺序依次匹配，否则联合索引失效。

需要注意的是，根据最左匹配原则，当复合索引当中出现范围查询时，该范围查询将会生效，但是范围查询之后的条件均不生效。

索引区分度？

当查询优化器发现所使用索引当中某个值出现的比例超过某个阈值（比如 30%）时，查询优化器将放弃走索引，转而进行全表扫描。

联合索引如何排序？

可以利用所有的有序性，在排序列和索引列建立联合索引。

使用索引有哪些缺陷？

索引本身也是一张表，建立索引需要占用存储空间。

此外，索引不宜建立在频繁增删改的字段上，增删改的字段如果建立了索引，那么原表和索引表都需要修改，增加额外的时间开销。

什么时候需要建立索引，什么时候不需要？

需要建立索引

• 表的主键：自动成为主键索引；
• 表当中某个字段需要唯一性约束：对该字段建立唯一索引；
• 直接进行条件查询的字段：经常使用 WHERE 查询的字段，建立索引可以提高效率；
• 宜对外键建立索引；
• 对排序字段建立索引可以大大提高排序速度；
• 常用于 GROUP BY 和 ORDER BY 的字段可以建立联合索引。

不宜建立索引的场景

• 表中某个需要频繁增删改的字段不宜建立索引；
• 表的记录很少时，可以直接走全表扫描；
• 数据重复且分布均匀的字段不宜建立索引；
• 经常和其他字段一起查询但其他字段的索引值较多的字段不宜建立索引。

使用索引的注意事项

• like 前导模糊查询会使索引失效；
• 负向查询不能使用索引；
• 联合查询注意最左匹配原则；
• 避免过度使用索引，因为建立索引会引入额外的存储与维护开销；
• 更新频繁的字段不宜建立索引；
• 避免在索引列引入算式或函数，如是会使索引列失效并退化为全表扫描；

复习 MySQL Day3：锁

MySQL 有哪些类型的锁？每种类型的锁当中又具体有哪些锁？

按锁的粒度分类

全局锁

• FLUSH TABLES WITH READ LOCK（FTWRL）：锁定整个数据库实例（注意是整个数据库，而不是某个表），所有数据表进入只读状态。全局锁主要用于数据备份的场景。

表级锁

表锁

• 基本表锁：LOCK TABLES ... READ/WRITE；
• 意向锁（Intention Locks）：进一步细分为意向共享锁（IS）和意向排他锁（IX）；
• 与行锁的功能类似，表锁的核心作用也是并发控制：协调多个会话对同一个表的并发访问，防止并发操作导致的数据不一致。表锁以整张表为单位进行锁定，是最简单之间的并发控制方式。
• 表锁的使用场景包括：数据库备份操作、大批量数据导入/导出、需要确保数据一致性的复杂多表操作。

元数据锁（MDL）

元数据锁：

• 自动加锁，用于保护表结构的变更；
• 读锁：查询时自动读取；
• 写锁：表结构更改时获取。

行级锁（InnoDB 特有）

记录锁（Record Locks）

记录锁用于锁定索引中的单条记录，是最基本的行锁类型。

间隙锁（Gap Locks）

• 记录锁用于锁定索引记录间的间隙。
• 可防止幻读现象的发生。
• 仅在「可重复读」隔离级别下生效。

临键锁（Next-Key Locks）

• 临键锁是记录锁和间隙锁的组合。
• 临键锁可以锁定当前记录及当前记录之前的间隙，具体来说，临键锁锁定的是一个「左开右闭」区间，上一条索引值到当前索引值之间的间隙将会被锁定，避免在读写期间有新的记录插入这个区间。
• 临键锁是 InnoDB 的默认行锁实现方式。

插入意向锁（Insert Intention Locks）

• 插入意向锁是特殊的间隙锁，需要注意的是，插入意向锁是行锁，要与表级的意向锁区分开来。
• 一个事务在向数据表中插入一条数据时，需要先判断插入位置是否有间隙锁（注意，临键锁也包含间隙锁）。如果有，那么当前插入操作会被阻塞，直到拥有间隙锁的事务提交。在当前事务阻塞期间，会生成一个插入意向锁，表明事务想在某个区间插入新记录，但是目前处于等待状态。

按锁的模式分类

共享锁（S锁）

• 共享锁又称读锁，通过 SELECT ... LOCK IN SHARE MODE 的方式加锁。
• 共享锁允许多个事务同时获取。

排他锁（X锁）

• 排他锁又称写锁，通过 SELECT ... FOR UPDATE 的方式获取。
• 排他锁一次只能由一个事务持有。

特殊锁类型

自增锁（Auto Inc Locks）

自增锁用于自增列的插入操作，它是特殊的表级锁。

谓词锁（Predicate Locks）

在串行化隔离级别下生效，锁定满足特定搜索条件的行。

意向锁的作用是什么？什么时候需要加意向锁？

意向锁是 InnoDB 中特殊的表级锁，它在行锁和表锁之间起到了关键的协调作用。

意向锁的核心作用

• 多粒度锁定协调：解决表锁和行锁的共存问题，使不同粒度的锁能够高效协同工作；
• 快速冲突检测：提供一种“预先声明”机制，避免在加表锁时还需要检查行锁状态。

意向锁的工作机制
首先，意向锁是表锁。之后，意向锁分为意向共享锁（IS）和意向排他锁（IX）。具体来说：

• IS：表示事务准备在表的某些行加共享锁；
• IX：表示事务准备在表的某些行加排他锁。

何时会加意向锁

• IS：当事务需要加行级 S 锁时，会首先自动加表级的 IS 锁；
• IX：当事务需要加行级 X 锁时，会首先自动加表级的 IX 锁。

意向锁的价值

• 避免在加表锁时检查索引行的状态来查看是否有行已经加表锁，直接查看意向锁这个表级锁就可以得知当前表中是否有行被加锁。
• 意向锁可以预防死锁：在加行锁之前，必须先获取意向锁，然后才能够加行锁。

MySQL 的全局锁有什么作用？

MySQL 全局锁的作用是做全库逻辑备份，加全局锁之后整个数据库处于只读状态，增删改会被阻塞。

全局锁的缺陷在于当数据库数据过多时，全局备份时间较慢，由于不能增删改，因此会使业务停滞。一个优化的方法是通过可重复读隔离级别下的 MVCC。

MySQL 如何加锁？

下面以 InnoDB 引擎为例，简述 MySQL 的加锁过程。

加锁的基本流程

锁的触发时机

• 自动加锁：DML 语句（INSERT / DELETE / UPDATE）时自动获取；
• 手动加锁：SELECT ... FOR UPDATE 加排他锁，SELECT ... LOCK IN SHARE MODE 加排他锁；
• DDL 加锁：表的结构变更时自动加锁。

加锁的基本步骤

1. 解析 SQL 以确定需要访问的表以及行；
2. 获取意向锁（表级）；
3. 根据隔离级别和查询条件确定锁的类型以及查询范围；
4. 在存储引擎层加实际的行锁 / 表锁；
5. 记录锁信息到内存结构。

不同操作的加锁规则

SELECT 语句

• 普通 SELECT（快照读）：不加锁；
• SELECT ... FOR UPDATE：加排他锁；
• SELECT ... LOCK IN SHARE MODE：加共享锁。

DML 语句

• INSERT：排他锁 + 插入意向锁；
• UPDATE：先加共享锁查找，再加排他锁修改；
• DELETE：加排他锁。

InnoDB 行锁的加锁过程

基于索引的加锁

-- 假设有索引 idx_age
UPDATE users SET name='yggp' WHERE age=25;

加锁步骤：

1. 通过 idx_age 找到 age=25 的记录；
2. 获取对应行的排他锁；
3. 如果当前隔离级别是可重复读，那么还会加间隙锁防止幻读。

无索引时加锁

-- 无合适索引的列
UPDATE users SET name='yggp' WHERE phone='123456';

加锁步骤：

• 全表扫描：对所有扫描到的行加锁；
• 风险：容易导致大量锁冲突和性能问题。

锁的升级与转换

锁升级条件
当单个事务锁定的行超过 innodb_change_buffer_max_size 时，系统自动将行锁升级为表锁。

锁转换场景
指的是先加共享锁的行如果后续需要修改，那么共享锁将升级为排他锁。UPDATE 语句的加锁过程就是先通过共享锁找到对应的记录，再加排他锁对数据进行修改。

-- 事务内的锁转换示例
BEGIN;
SELECT * FROM accounts WHERE id=1 LOCK IN SHARE MODE;	-- 获取 S 锁
-- 下面执行 UPDATE, S -> X
UPDATE accounts SET balance=100 WHERE id=1;				-- S -> X