MySQL 基础架构

SQL 语句的执行过程

查询语句

select * from student A where A.age='18' and A.name='张三';


• 连接器：通过客户端/服务器通信协议与 MySQL 建立连接。并查询是否有权限；
• 查询缓存：MySQL 8.0 之前需要看是否开启了缓存，开启了 Query Cache 且命中完全相同的 SQL 语句，则将查询结果直接返回给客户端；
• 解析器：由解析器进行语法语义解析，并生成解析树。如查询是 select、表名 tb_student、条件是 id = 1；
• 优化器：查询优化器生成执行计划。根据索引看看是否可以优化；
• 执行器：查询执行引擎执行 SQL 语句，根据存储引擎类型，得到查询结果。

MySQL 存储引擎

MySQL 支持哪些存储引擎？

MySQL 支持多种存储引擎，可以通过 show engines 命令来查看 MySQL 支持的存储引擎

常用的有 MEMORY、MyISAM、InnoDB。

MySQL 存储引擎架构了解吗？

MySQL 存储引擎采用的是插件架构，支持多种存储引擎。存储引擎是基于表的，而不是数据库的。

MyISAM 和 InnoDB 有什么区别？

这些特性使得 InnoDB 更适用于需要事务处理和数据完整性的应用，而 MyISAM 可能更适用于读操作频繁但不需要事务支持的应用。

MySQL 索引

索引概述

索引是在数据库表的一列或者多列上建立的，用于快速查询数据。当你对表进行索引时，MySQL 会创建一个指向数据库表中数据的指针列表。这类似于书的目录，使得数据检索更加快速和直接。

索引的优缺点

优点

• 提高查询效率，减少 IO 次数。

缺点

• 创建索引和维护索引需要消耗时间；
• 索引也需要使用物理文件存储。

索引的分类

功能分类

• 主键索引：表中每行数据唯一标识的索引，强调列值的唯一性和非空性。
• 唯一索引：保证数据列中每行数据的唯一性，但允许有空值。
• 普通索引：基本的索引类型，用于加速查询。
• 全文索引：特定于文本数据的索引，用于提高文本搜索的效率。

数据结构

• B+ 树索引
• 哈希索引

存储位置

• 聚簇索引：聚簇索引的叶子节点保存了一行记录的所有列信息。也就是说，聚簇索引的叶子节点中，包含了一个完整的记录行。
• 非聚簇索引：它的叶子节点只包含一个主键值，通过非聚簇索引查找记录要先找到主键，然后通过主键再到聚簇索引中找到对应的记录行，这个过程被称为回表。

为什么使用索引会加快查询？

• 避免全表扫描，减少了磁盘 I/O 操作的次数；
• 数据结构优势，MySQL 的 InnoDB 存储引擎默认使用 B+ 树来作为索引的数据结构，而 B+ 树的查询效率非常高，时间复杂度为 O(logN)

创建索引有哪些注意点？

• 选择合适的列作为索引
  • 查询频繁的列
  • 区分度低的字段不要建索引，例如性别
  • 频繁更新的列不要作为索引
  • 不建议用无序的值（例如身份证、UUID）作为索引，当主键具有不确定性，会造成叶子节点频繁分裂，出现磁盘存储的碎片化
• 避免过多的索引
  • 索引需要占用额外的磁盘空间
  • 数据更新时，维护索引也需要消耗资源
• 利用前缀索引和索引列的顺序
  • 对于字符串类型的列，可以考虑使用前缀索引来减少索引大小。
  • 在创建复合索引时，应该根据查询条件将最常用作过滤条件的列放在前面。

索引在哪些情况下会失效？

• 非最左前缀使用：大多数数据库的索引（特别是符合索引）是基于最最左前缀原则构建的。如果查询不从索引的最左列开始，索引可能不会被使用。例如，索引是在 (lastname, firstname) 上，但查询只有关于 firstname 的条件
• 使用 LIKE 运算符时的前导通配符：如果查询使用 LIKE 且模式以通配符开始（如 LIKE '%Smith'），则索引可能不能使用，因为数据库无法利用索引有效地查找这种模式。
• 数据类型不匹配：如果查询条件中的数据类型与索引列的数据类型不匹配，数据库可能需要进行隐式类型转换，这可能导致索引失效。例如，如果索引列是整数类型，但查询条件用字符串来比较，如WHERE column = '123'。
• NULL 值：某些数据库处理包含 NULL 值的列的索引时可能会有问题。如果索引列有很多 NULL 值，或者查询条件包含对 NULL 值的比较，索引的效果可能会降低。
• OR 条件：使用OR连接的查询条件如果不是每个条件都在同一个索引中，可能导致索引失效。例如，如果有两个条件分别使用不同的索引，数据库可能决定放弃使用索引。
• 使用不等式运算符：虽然索引可以用于不等式查询（如>、<等），但如果这样的查询返回表中大部分数据，数据库优化器可能决定全表扫描更有效，因此不使用索引。
• 排序和聚合操作：如果查询包括排序（ORDER BY）或聚合（如GROUP BY、MAX()、COUNT()等）操作，且这些操作所涉及的列没有相应的索引支持，那么执行这些操作时索引可能不被使用。

索引不适合哪些场景呢？

• 数据表很小：如果数据表的记录数量非常少，数据库进行全表扫描的成本可能比维护和查询索引的成本要低。在这种情况下，索引可能不会带来性能提升。
• 频繁更新的索引列：如果一个列的值经常更改，每次更改都可能导致索引的重建或重排，这可以增加大量的性能负担。

索引是不是建的越多越好？

不是。

• 索引会占据磁盘空间
• 每当在表中插入、删除或更新数据时，数据库系统都需要更新所有相关的索引。

为什么 InnoDB 要使用 B+ 树作为索引？

• 更高效的磁盘 IO，因为它减少了磁盘寻道时间和页的加载次数
• 支持范围查询，与 B 树相比，B+ 树的叶子结点通过指针连接成一个链表，这使得查询范围变得非常高效。在 B+ 树上执行范围查询可以简单地从范围的起始点开始，然后沿着链表向后遍历，直到结束点。
• 查询性能稳定，B+ 树的所有查找操作都要查到叶子节点，这使得所有的查询操作都有着相同的访问深度，因此查询性能非常稳定。不像某些其他数据结构，如 B 树，其查询性能因为数据存在所有的节点上导致深度不一致，性能不稳定。

MySQL 日志

MySQL 日志文件有哪些？分别介绍下作用？

• 错误日志（Error Log）：记录 MySQL 服务器启动、运行或停止时出现的问题。
• 慢查询日志（Slow Query Log）：记录执行时间超过 long_query_time 值的所有 SQL 语句。这个时间值是可配置的，默认情况下，慢查询日志功能是关闭的。可以用来识别和优化慢 SQL。
• 一般查询日志（General Query Log）：记录所有 MySQL 服务器的连接信息及所有的 SQL 语句，不论这些语句是否修改了数据。
• 二进制日志（Binary Log）：记录了所有修改数据库状态的 SQL 语句，以及每个语句的执行时间，如 INSERT、UPDATE、DELETE 等，但不包括 SELECT 和 SHOW 这类的操作。

以及两个 InnoDB 存储引擎特有的日志文件：

• 重做日志（Redo Log）：记录了对于 InnoDB 表的每个写操作，不是 SQL 级别的，而是物理级别的，主要用于崩溃恢复。

• 回滚日志（Undo Log，或者叫事务日志）：记录数据被修改前的值，用于事务的回滚。支持事务回滚，可以用来实现 MVCC，即多版本并发控制。

请说说 binlog

binlog 主要用于复制（Replication）和数据恢复（Data Recovery）。

• 支持主从复制，主服务器（master）上的二进制日志可以被从服务器（slave）读取和恢复。
• 在发生数据丢失或损坏时，binlog 可以用来恢复数据。结合全量备份和 binlog 的增量备份，可以将数据库恢复到特定的时间点（Point-In-Time Recovery）

binlog 包括两类文件

• 二进制索引文件（.index）
• 二进制日志文件（.00000*）

binlog 默认是没有启用的。要启用它，需要在 MySQL 的配置文件（my.cnf 或 my.ini）中设置 log_bin 参数，并指定 binlog 文件的存储位置。

# 设置此参数表示启用binlog功能，并制定二进制日志的存储目录
log-bin=/home/mysql/binlog/
 
# mysql-bin.*日志文件最大字节（单位：字节）
# 设置最大100MB
max_binlog_size=104857600
 
# 设置了只保留7天BINLOG（单位：天）
expire_logs_days = 7
 
# binlog日志只记录指定库的更新
# binlog-do-db=db_name
 
# binlog日志不记录指定库的更新
# binlog-ignore-db=db_name
 
# 写缓冲多少次，刷一次磁盘，默认0
sync_binlog=0

参数作用：

• max_binlog_size=104857600

  该配置设置了每个 binlog 文件额最大大小为 100MB（104857600 字节）。当 binlog 文件达到这个大小时，MySQL 会关闭当前文件并创建一个新的 binlog 文件。这有助于管理大量的 binlog 文件和限制单个文件的大小。

• expire_logs_days=7

  该配置设置了 binlog 文件的自动过期时间为 7 天。过期的 binlog 文件将被自动删除。这有助于管理磁盘空间，防止长时间累积的 binlog 文件占用过多存储空间。

• binlog-do-db=db_name

  规定哪些数据库表的更新应该记录

• binlog-ignore-db=db_name

  指定忽略哪些数据库表的更新

• sync_binlog=0

  该配置设置了每多少次 binlog 写操作会触发一次磁盘同步操作。默认值 0 表示 MySQL 不会主动触发同步操作，而是依赖操作系统的磁盘缓存策略；1 表示每次 binlog 写操作后都会同步到磁盘，这可以提高数据安全性，但可能会对性能产生影响。

一旦启动 binlog 所有的数据修改操作就会被记录到 binlog 文件中。可以通过 show variables like '%log_bin%'; 查看 binlog 是否开启。不过需要注意的是，随着数据库的操作，binlog 文件可能会不断增长，因此需要定期进行清理或归档，防止占用过多磁盘空间。

binlog 和 redo log 有什么区别

为什么要两阶段提交呢？

什么是事务

事务是数据库中一组原子性的操作，要么全部成功，要么全部失败。事务具有四个特性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），简称 ACID。

在 MySQL 中，我们可以使用 begin 或 start transaction 命令开启一个事务，使用 commit 命令提交一个事务，或者使用 rollback 命令回滚一个事务。例如：

begin;
update account set balance = balance - 100 where id = 1;
update account set balance = balance + 100 where id = 2;
commit;

上面的例子是一个转账的场景，我们将 id 为 1 的账户的余额减少 100，将 id 为 2 的账户的余额增加 100。这两个操作要么同时成功，要么同时失败，不能出现中间状态。

MySQL 中的日志

为了保证事务的 ACID 特性，MySQL 需要记录一些日志来辅助事务的执行和恢复。MySQL 中主要有两种日志：redo log 和 binlog。

redo log 是 InnoDB 存储引擎特有的日志，用于记录数据页的物理修改，保证事务的持久性和原子性。redo log 是循环写入的，由两部分组成：一块固定大小的内存区域（redo log buffer）和一组固定大小的磁盘文件（redo log file）。当事务对数据进行修改时，会先将修改记录到 redo log buffer 中，然后在适当的时机将其刷新到 redo log file 中。这样即使数据库发生异常重启，也可以根据 redo log 恢复数据。

binlog 是 MySQL Server 层的日志，用于记录 SQL 语句的逻辑修改，保证事务的一致性。binlog 是追加写入的，由一个 binlog 文件序列和一个索引文件组成。当事务提交时，会将 SQL 语句记录到 binlog 中。binlog 主要用于数据备份、恢复和主从复制。

为什么需要两阶段提交

如果只有 redo log 或者只有 binlog，那么事务就不需要两阶段提交。但是如果同时使用了 redo log 和 binlog，那么就需要保证这两种日志之间的一致性。否则，在数据库发生异常重启或者主从切换时，可能会出现数据不一致的情况。

例如，假设我们有一个事务 T，它修改了两行数据 A 和 B，并且同时开启了 redo log 和 binlog。如果我们先写 redo log 再写 binlog，并且在写完 redo log 后数据库发生了宕机，那么在重启后，根据 redo log 我们可以恢复 A 和 B 的修改，但是 binlog 中没有记录 T 的信息，导致备份或者从库中没有 T 的修改。反之，如果我们先写 binlog 再写 redo log，并且在写完 binlog 后数据库发生了宕机，那么在重启后，根据 redo log 我们无法恢复 A 和 B 的修改，但是 binlog 中有记录 T 的信息，导致备份或者从库中有 T 的修改。

为了避免这种情况，MySQL 引入了两阶段提交的机制。两阶段提交就是将一个事务分成两个阶段来提交： prepare 阶段和 commit 阶段

• 在 prepare 阶段：事务会先写 redo log 并将其标记为 prepare 状态，然后写 binlog；

• 在 commit 阶段：事务会将 redo log 标记为 commit 状态，并将 binlog 落盘。

这样，无论数据库在哪个时刻发生宕机，都可以根据 redo log 和 binlog 的状态来判断事务是否提交，并保证数据的一致性。

两阶段提交的流程

MySQL采用了如下的二阶段提交流程：

1. 在准备阶段，MySQL 先将数据修改写入 redo log，并将其标记为 prepare 状态，表示事务还未提交。然后将对应的 SQL 语句写入 bin log。
2. 在提交阶段，MySQL 将 redo log 标记为 commit 状态，表示事务已经提交。然后根据 sync_binlog 参数的设置，决定是否将 binlog 刷入磁盘。

通过这样的流程，MySQL 可以保证在任何时刻，redo log 和 binlog 都是逻辑上一致的。如果 MySQL 发生崩溃，可以根据 redo log 恢复数据页的状态，也可以根据 bin log 恢复 SQL 语句的执行。

下面是一个简单的示例:

假设我们有一个表 test_backup 如下：

CREATE TABLE `test_backup` (
  `id` int (11) NOT NULL AUTO_INCREMENT,
  `name` varchar (255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后我们执行以下语句：

insert into test_backup values (1,'tom');
insert into test_backup values (2,'jerry');
insert into test_backup values (1,'herry');

这时候，MySQL会按照以下步骤进行二阶段提交：

1. 将第一条插入语句写入 redo log，并标记为 prepare 状态。
2. 将第一条插入语句写入 bin log。
3. 将 redo log 标记为 commit 状态。
4. 如果 sync_binlog=1，则将 bin log 刷入磁盘。
5. 重复以上步骤，直到所有插入语句都完成。

如果在这个过程中发生了崩溃，比如在第三步之前，那么 MySQL 重启后会根据 redo log 发现有一个 prepare 状态的事务，然后会去查找 bin log 中是否有对应的 SQL 语句。如果有，则说明该事务已经写入了bin log，可以提交；如果没有，则说明该事务还没有写入 bin log，需要回滚。这样就可以保证数据的一致性。

总结

MySQL 的两阶段提交是为了保证同时使用 redo log 和 binlog 的情况下，数据的一致性。两阶段提交将一个事务分成 prepare 阶段和 commit 阶段，在 prepare 阶段写 redo log 和 binlog，在 commit 阶段修改 redo log 的状态并落盘 binlog。这样可以避免数据库发生异常重启或者主从切换时出现数据不一致的情况。

redo log 怎么刷入磁盘的知道吗？

https://github.com/Snailclimb/JavaGuide/blob/main/docs/database/mysql/mysql-logs.md (opens in a new tab)

MySQL 事务

何为事务？

事务逻辑上是一组操作，要么都执行，要么都不执行

事务的特性？

• 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
• 一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
• 隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
• 持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

并发事务带来了哪些问题？

脏读（Dirty read）

一个事务读取数据并且对数据进行了修改，这个修改对其他事务来说是可见的，即使当前事务没有提交。这时另外一个事务读取了这个还未提交的数据，但第一个事务突然回滚，导致数据并没有被提交到数据库，那第二个事务读取到的就是脏数据，这也就是脏读的由来。

例如：事务 1 读取某表中的数据 A=20，事务 1 修改 A=A-1，事务 2 读取到 A = 19,事务 1 回滚导致对 A 的修改并未提交到数据库， A 的值还是 20。

丢失修改（Lost to modify）

在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 先修改 A=A-1，事务 2 后来也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。

不可重复读（Unrepeatable read）

指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 再次读取 A =19，此时读取的结果和第一次读取的结果不同。

幻读（Phantom read）

幻读与不可重复读类似。它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

例如：事务 2 读取某个范围的数据，事务 1 在这个范围插入了新的数据，事务 2 再次读取这个范围的数据发现相比于第一次读取的结果多了新的数据。

总结

脏读：一个事务读取了另一个事务尚未提交的数据，如果这些数据随后被回滚，该读取的数据将变为无效，导致脏读问题。

丢失修改：两个事务都对同一数据进行更新，一个事务的修改会被另一个事务的提交覆盖，导致前一个事务的修改丢失。

不可重复读：在同一个事务中，前后两次读取同一数据却得到了不同的结果，因为在两次读取之间，另一个事务对该数据进行了修改并提交。

幻读：一个事务读取多条满足条件的数据后，另一个事务插入了新的符合条件的数据，再次读取时，会发现多出了一些“幻影”行。

不可重复读和幻读有什么区别？

• 不可重复读的重点是内容修改或者记录减少。比如多次读取一条记录发现其中某些记录的值被修改；
• 幻读的重点在于记录新增。比如多次执行同一条查询语句（DQL）时，发现查到的记录增加了。

幻读其实可以看作是不可重复读的一种特殊情况，单独把幻读区分出来的原因主要是解决幻读和不可重复读的方案不一样。

举个例子：执行 delete 和 update 操作的时候，可以直接对记录加锁，保证事务安全。而执行 insert 操作的时候，由于记录锁（Record Lock）只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁（Gap Lock）。也就是说执行 insert 操作的时候需要依赖 Next-Key Lock（Record Lock + Gap Lock） 进行加锁来保证不出现幻读。

并发事务的控制方式有哪些？

MySQL 中并发事务的控制方式无非就两种：锁 和 多版本并发控制（MVCC，Multiversion concurrency control）。

• 锁可以看作是悲观控制的模式；

• MVCC 可以看作是乐观控制的模式。

锁 控制方式下会通过锁来显式控制共享资源而不是通过调度手段，MySQL 中主要是通过 读写锁 来实现并发控制。

• 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
• 排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁（锁不兼容）。

读写锁可以做到读读并行，但是无法做到写读、写写并行。另外，根据根据锁粒度的不同，又被分为 表级锁(table-level locking) 和 行级锁(row-level locking) 。InnoDB 不光支持表级锁，还支持行级锁，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类。

MVCC 是多版本并发控制方法，即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。

MVCC 在 MySQL 中实现所依赖的手段主要是: 隐藏字段、read view、undo log。

• undo log : undo log 用于记录某行数据的多个版本的数据。
• read view 和 隐藏字段 : 用来判断当前版本数据的可见性。

关于 InnoDB 对 MVCC 的具体实现可以看这篇文章：InnoDB 存储引擎对 MVCC 的实现 (opens in a new tab) 。

SQL 标准定义了哪些事务隔离级别?

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是不可重复读或幻读仍有可能发生。
• REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL 的默认隔离级别是什么?

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看，MySQL 8.0 该命令改为SELECT @@transaction_isolation;

关于 MySQL 事务隔离级别的详细介绍，可以看看我写的这篇文章：MySQL 事务隔离级别详解 (opens in a new tab)。

MySQL 的隔离级别是基于锁实现的吗？

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

SERIALIZABLE 隔离级别是通过锁来实现的，READ-COMMITTED 和 REPEATABLE-READ 隔离级别是基于 MVCC 实现的。不过， SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。

MySQL 锁

锁是一种常见的并发事务的控制方式。

表级锁和行级锁了解吗？有什么区别？

MyISAM 仅仅支持表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性非常差。InnoDB 不光支持表级锁(table-level locking)，还支持行级锁(row-level locking)，默认为行级锁。

行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。

表级锁和行级锁对比：

• 表级锁： MySQL 中锁定粒度最大的一种锁（全局锁除外），是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。
• 行级锁： MySQL 中锁定粒度最小的一种锁，是针对索引字段加的锁 ，只针对当前操作的行记录进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的。

行级锁的使用有什么注意事项？

InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。这个在我们日常工作开发中经常会遇到，一定要多多注意！！！

不过，很多时候即使用了索引也有可能会走全表扫描，这是因为 MySQL 优化器的原因。

InnoDB 有哪几类行锁？

InnoDB 行锁是通过对索引数据页上的记录加锁实现的，MySQL InnoDB 支持三种行锁定方式：

• 记录锁（Record Lock）：也被称为记录锁，属于单个行记录上的锁。
• 间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。
• 临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

一些大厂面试中可能会问到 Next-Key Lock 的加锁范围，这里推荐一篇文章：MySQL next-key lock 加锁范围是什么？ - 程序员小航 - 2021 (opens in a new tab) 。

共享锁和排他锁呢？

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

• 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
• 排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

由于 MVCC 的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁。不过， 你可以通过以下语句显式加共享锁或排他锁。

# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;
# 排他锁
SELECT ... FOR UPDATE;

意向锁有什么作用？

如果需要用到表锁的话，如何判断表中的记录没有行锁呢，一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁的东东来快速判断是否可以对某个表使用表锁。

意向锁是表级锁，共有两种：

• 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

意向锁之间是互相兼容的。

意向锁和共享锁和排它锁互斥（这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥）。

当前读和快照读有什么区别？

快照读（一致性非锁定读）就是单纯的 SELECT 语句，但不包括下面这两类 SELECT 语句：

SELECT ... FOR UPDATE
# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;

快照即记录的历史版本，每行记录可能存在多个历史版本（多版本技术）。

快照读的情况下，如果读取的记录正在执行 UPDATE/DELETE 操作，读取操作不会因此去等待记录上 X 锁的释放，而是会去读取行的一个快照。

只有在事务隔离级别 RC(读取已提交) 和 RR（可重读）下，InnoDB 才会使用一致性非锁定读：

• 在 RC 级别下，对于快照数据，一致性非锁定读总是读取被锁定行的最新一份快照数据。
• 在 RR 级别下，对于快照数据，一致性非锁定读总是读取本事务开始时的行数据版本。

快照读比较适合对于数据一致性要求不是特别高且追求极致性能的业务场景。

当前读 （一致性锁定读）就是给行记录加 X 锁或 S 锁。

当前读的一些常见 SQL 语句类型如下：

# 对读的记录加一个X锁
SELECT...FOR UPDATE
# 对读的记录加一个S锁
SELECT...LOCK IN SHARE MODE
# 对读的记录加一个S锁
SELECT...FOR SHARE
# 对修改的记录加一个X锁
INSERT...
UPDATE...
DELETE...

自增锁有了解吗？

不太重要的一个知识点，简单了解即可。

关系型数据库设计表的时候，通常会有一列作为自增主键。InnoDB 中的自增主键会涉及一种比较特殊的表级锁— 自增锁（AUTO-INC Locks） 。

CREATE TABLE `sequence_id` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `stub` CHAR(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `stub` (`stub`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

更准确点来说，不仅仅是自增主键，AUTO_INCREMENT的列都会涉及到自增锁，毕竟非主键也可以设置自增长。

如果一个事务正在插入数据到有自增列的表时，会先获取自增锁，拿不到就可能会被阻塞住。这里的阻塞行为只是自增锁行为的其中一种，可以理解为自增锁就是一个接口，其具体的实现有多种。具体的配置项为 innodb_autoinc_lock_mode （MySQL 5.1.22 引入），可以选择的值如下：

交错模式下，所有的“INSERT-LIKE”语句（所有的插入语句，包括：INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT、LOAD DATA等）都不使用表级锁，使用的是轻量级互斥锁实现，多条插入语句可以并发执行，速度更快，扩展性也更好。

不过，如果你的 MySQL 数据库有主从同步需求并且 Binlog 存储格式为 Statement 的话，不要将 InnoDB 自增锁模式设置为交叉模式，不然会有数据不一致性问题。这是因为并发情况下插入语句的执行顺序就无法得到保障。

如果 MySQL 采用的格式为 Statement ，那么 MySQL 的主从同步实际上同步的就是一条一条的 SQL 语句。

最后，再推荐一篇文章：为什么 MySQL 的自增主键不单调也不连续 (opens in a new tab) 。

参考资料

• MySQL 事务的两阶段提交 (opens in a new tab)