MySQL八股整理.md

MySQL八股整理 > 【计算机基础】数据库MySQL面试八股总结_哔哩哔哩_bilibili MySQL > 上图来自于视频：【计算机基础】数据库MySQL面试八股总结_哔哩哔哩_bilibili ## MySQL 组成结构 MySQL 主要由两部分组成：server 层、存储引擎层 `` 客户端 │ ▼ ┌──────────────────────────────┐ │ Server 层 │ │ │ │ 连接管理 │ │ SQL 解析器 │ │ SQL 优化器 │ │ SQL 执行器 │ │ 权限校验等 │ └──────────────┬───────────────┘ │ ▼ ┌──────────────────────────────┐ │ 存储引擎层 │ │ │ │ InnoDB │ │ MyISAM │ │ Memory │ │ ... │ └──────────────┬───────────────┘ │ ▼ 磁盘数据 ` ## 从后端一段SQL代码到MySQL的执行流程 > 客户端发送SQL查询语句到MySQL服务器，MySQL处理请求，并与客户端建立连接，
获取权限，后续MySQL的解析器对SQL语句进行解析，检查是否符合SQL语法规则，
通过后优化器尝试找出最高效的方式来执行查询，执行器调用存储引擎的API进行数据的读写，
并返回给客户端完成本次查询 第一步，客户端发送 SQL 查询语句到 MySQL 服务器。 第二步，MySQL 服务器的连接器开始处理这个请求，跟客户端建立连接、获取权限、管理连接。
第三步，解析器开始对 SQL 语句进行解析，检查语句是否符合 SQL 语法规则，确保引用的数据库、表和列都存在，并处理 SQL 语句中的名称解析和权限验证。 第四步，优化器负责确定 SQL 语句的执行计划，这包括选择使用哪些索引，以及决定表之间的连接顺序等。
优化器会尝试找出最高效的方式来执行查询。
第五步，执行器会调用存储引擎的 API 来进行数据的读写。 第六步，MySQL 的存储引擎是插件式的，不同的存储引擎在细节上面有很大不同。
例如，InnoDB 是支持事务的，而 MyISAM 是不支持的。之后，会将执行结果返回给客户端 第七步，客户端接收到查询结果，完成这次查询请求。
## 常用命令 创建、删除，CRUD ## MySQL支持哪些存储引擎？各个引擎之间有什么区别？
image-20261005175212478 常见的存储引擎主要有三个：memory、innodb、myisam，主要区别是：
- memory：基于内存 - innodb：支持事务、行锁、外键，其中与myisam的区别是，innodb存储引擎存放数据只有一个idb文件，
该文件包含了索引与数据，而myisam则分为两个：索引文件与数据文件，查询时需先查询索引，
然后根据索引再去找到数据 - myisam：不支持事务、行锁、外键 | 文件 | 含义 | 存什么 | | ------ | ------- | ------------ | | .MYD | MYData | 实际的行数据 | | .MYI | MYIndex | 索引数据 | ## MySQL 日志文件有哪些？ > Error Log 看 MySQL 自己出了什么问题；Binlog 看数据发生了什么变化；General Log 看客户端执行了什么 SQL；Slow Log 看哪些 SQL 执行得太慢 | 日志类型 | 英文名称 | 主要记录内容 | 默认状态 | 主要用途 | 补充说明 | | ---------------- | ------------------- | ------------------------------------------------------------ | ------------------ | --------------------------------------- | ------------------------------------------------------------ | | 错误日志 | Error Log | 记录 MySQL 在启动、运行、停止过程中产生的错误、警告以及部分重要运行信息 | 默认开启 | 故障排查、定位 MySQL 启动失败或运行异常 | 例如数据库启动失败、存储引擎异常、权限问题等。
错误日志本身并不是用于恢复业务数据的日志 | | 二进制日志 | Binary Log / Binlog | 记录数据库中会导致数据或结构发生变化的事件，例如 CREATE、INSERT、UPDATE、DELETE 等；通常不记录普通 SELECT 查询 | MySQL 8.0 默认开启 | 数据恢复、主从复制、数据同步 | Binlog 主要有 STATEMENT、ROW、MIXED 三种格式。STATEMENT 记录执行的 SQL；ROW 记录行数据发生的变化；MIXED 根据情况在两者之间选择 | | 通用查询日志 | General Query Log | 记录客户端连接以及 MySQL 接收到的各种 SQL 请求，包括 SELECT、INSERT、UPDATE、DELETE 等 | 默认关闭 | 排查 SQL 请求、分析客户端执行了哪些操作 | 记录范围非常广，写入频繁，性能开销较大，通常只在排查问题时临时开启 | | 慢查询日志 | Slow Query Log | 记录执行时间超过指定阈值的 SQL | 默认关闭 | 定位慢 SQL、进行数据库性能优化 | 通过 long_query_time 设置慢查询时间阈值，例如设置为 2，表示执行时间超过 2 秒的 SQL 可被记录 | ## innodb 存储引擎中 redolog 与 undolog 的区别 - undolog：记录旧数据，即更新前的数据，用于回滚和MVCC多版本并发控制 - redolog：记录修改，用于崩溃后恢复，由于innodb对于数据更改，
缓冲区与实际存储数据的磁盘处理顺序不同，首先将位于内存的缓冲区数据更新，
并写入redolog，若数据写入实际存储的磁盘时发生崩溃，此时重启后可以通过redolog重新恢复已提交的数据 ## redo log 与 binlog 有什么区别 最核心的区别是： ` redo log ↓ InnoDB 层 ↓ 保证存储引擎自身崩溃恢复 binlog ↓ MySQL Server 层 ↓ 记录数据库发生的逻辑变化 ` redolog是MySQL存储引擎层innodb的日志记录文件，而binlog是MySQL server层的日志记录文件，redolog与innodb绑定，记录物理级别的修改操作，
如：具体的索引、页、以及数据等内容，而binlog主要是逻辑层面的修改操作，
有三种记录方式：statement、row、mixed（两者混合），其中statement记录SQL语句，
row记录更改前后的数据 ## MySQL 两阶段提交 背景：一条SQL语句进行更新操作设计了两阶段提交 1. 为什么需要两阶段提交？ 2. 两阶段提交的流程是什么？ 两阶段提交是为了保证 redolog 和 binlog 的一致性，由于 redolog 是 innodb 存储引擎的日志记录，而 binlog 是MySQL server层的日志记录，主要用于主从复制和数据恢复。
如果两者独立提交，宕机时可能出现 redolog 有记录但 binlog 没有，或者 binlog 有记录但 redolog 没有，从而导致主库恢复结果与 binlog 不一致，因此 MySQL 将 redolog 的提交分为 prepare 与 commit 两个阶段，中间写入 binlog，以保证两套日志对同一个事务的提交状态保持一致 两阶段提交的流程是， innodb 先写 redolog，并将其标记为 prepare 状态，然后server 层写人binlog，binlog 写入成功后，再将 redolog 标记为 commit，事务完成；若后续发生故障可能进行回滚或者判断事务已经成功 如果在 prepare 后，binlog 前宕机，此时binlog没有成功写入，事务会回滚；
如果 binlog 已经写入成功，但 redolog 仍处于 prepare 状态，重启后会根据 binlog 判断事务已经成功，并完成提交 > 其中真正的“两阶段”主要指 redo log 的： > > ` > prepare > ↓ > commit > ` > > binlog 写入发生在这两个阶段之间 ## MySQL 索引 > 【编程不良人】这一次彻底搞定MySQL索引、
从此不在恐惧面试_哔哩哔哩_bilibili 索引分类： ` 按功能分类 ├── 主键索引 ├── 唯一索引 ├── 普通索引 └── 全文索引 按包含字段数量分类 ├── 单列索引 └── 复合索引（联合索引） ` ==不同索引加锁情况不同== | 索引类型 | 说明 | 特点 / 注意事项 | 示例 | | ---------------------- | ------------------------------------------------------------ | ------------------------------------------------------------ | -------------------------------------- | | 主键索引 PRIMARY KEY | 创建表时设置主键，MySQL 会自动为主键创建索引 | 数据必须唯一且不能为 NULL；一个表只能有一个主键 | PRIMARY KEY (id) | | 唯一索引 UNIQUE | 用于保证索引列的值具有唯一性 | 不允许出现重复的非 NULL 值；MySQL 中通常允许多个 NULL | UNIQUE INDEX idx_email (email) | | 普通索引 INDEX | 最基本的索引类型，不要求数据唯一 | 主要用于提高查询效率，可以出现重复值和 NULL | INDEX idx_name (name) | | 单列索引 | 只包含一个字段的索引 | 描述的是索引包含的字段数量，可以是普通索引、唯一索引等 | INDEX idx_name (name) | | 复合索引 / 联合索引 | 一个索引同时包含多个字段 | 查询时通常需要遵循最左前缀原则 | INDEX idx_name_age (name, age) | | 全文索引 FULLTEXT | 用于文本内容的全文搜索，通过分词 + 倒排索引提高文本检索效率 | 适合 CHAR、VARCHAR、TEXT 等文本字段；InnoDB 从 MySQL 5.6 开始支持全文索引 | FULLTEXT INDEX idx_content (content) | 全文索引：类似于 elasticsearch ，但分词与高亮方面不如 ES，在分词方面 MySQL 内置的 ngram 全文解析器，并不是按照语义划分，而是滑动窗口，生硬的将文本按字符数进行拆分，
如：默认的 ngram_token_size 为 2，后续分词时只会按照 2 个字符进行切分，而 ES 可通过扩展使用分词器，中文分词器有 IK 分词器，该分词器是按照语义进行分词的，下面是 ngram 与 IK 分词器的对比： 文本：“中华人民共和国” - ngram 分词结果：中华、华人、人民、民共、共和、和国 - IK 分词结果：中华、人民、共和国 在高亮方面，由于对文本进行了分词，若后端进行高亮需知道分词的结果，然后才能进行高亮，
而 ES 则支持高亮，无需后端处理，但MySQL只能返回全文搜索的结果与评分，高亮需后端进行处理 ### MySQL 底层 B+树 MySQL 的数据最终存储在磁盘中，如果每次查询都直接扫描磁盘中的所有数据，查询效率会非常低 以 InnoDB 为例，它主要使用 B+ 树作为索引的数据结构，通过 B+ 树减少磁盘 I/O 次数，提高数据查询效率 需要注意： - InnoDB 的索引通常采用 B+ 树 - 主键索引又称聚簇索引，叶子节点存储完整的行数据 - 非叶子节点主要存储 索引键 + 子页指针 - InnoDB 默认一个数据页大小为 16KB - B+树顶层存放于内存中，无需经过磁盘IIO，进一步减少了磁盘IO访问次数 ##### B+ 树如何存储数据 假设表中存在一个主键 id： `sql CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(20), age INT ); ` 随着数据不断插入，InnoDB 会按照主键顺序组织聚簇索引 例如： `text 1 2 3 4 5 6 7 8 ... ` 但 InnoDB 并不是直接将每条数据作为一个独立的 B+ 树节点，而是将多条记录存放在一个 数据页 Page 中 一个数据页默认大小为： `text 16KB ` 因此整体结构可以简化理解为： `text 非叶子节点 [100] [200] [300] ↓ ↓ ↓ Page1 Page2 Page3 ↓ ↓ ↓ 行数据 行数据 行数据 ` B+ 树的所有完整行数据都存储在叶子节点的数据页中，而非叶子节点只保存索引键以及指向下一层页面的指针 image-20261007161342209 同时，叶子节点之间通过双向链表连接： `text Page1 ⇄ Page2 ⇄ Page3 ⇄ Page4 ` 这样不仅可以快速进行单条数据查询，也非常适合范围查询 例如： `sql SELECT FROM user WHERE id BETWEEN 100 AND 200; ` MySQL 可以先通过 B+ 树找到 id = 100 所在的数据页，然后沿着叶子节点的链表继续向后扫描，而不需要重新从根节点查找每一条数据 #### 两层 B+ 树可以存放多少数据 下面可以使用一个简化模型进行面试估算 假设： `text INT：4 字节 非叶子节点中的索引键：4 字节 子页指针：为了方便估算，假设约 8 字节 ` 表结构： `sql CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(20), age INT ); ` 为了方便计算，假设一条记录大约占用： `text id = 4 字节 name ≈ 20 字节 age = 4 字节 其他记录开销 ≈ 8 字节 合计约 36 字节 ` 这里的 36 字节 只是为了方便理解而进行的粗略估算 实际上 InnoDB 一条记录还包含： - Record Header - NULL 标记 - 变长字段长度信息 - MVCC 隐藏字段 - 页内部管理信息 并且 VARCHAR(20) 的 20 指的是字符数，而不是固定占用 20 字节，实际大小还与字符集以及真实字符串长度有关 因此实际一页能存放多少行数据，需要根据具体表结构判断 ###### 第一层：根节点 如果非叶子节点的一条目录记录粗略认为包含： `text 主键：4 字节 页指针：8 字节 ` 那么一条目录记录约： `text 4 + 8 = 12 字节 ` 一个页大小为 16KB： `text 16 × 1024 = 16384 字节 ` 因此一个非叶子页大约可以保存： `text 16384 ÷ 12 ≈ 1365 ` 也就是说，根节点最多可以粗略指向约： `text 1365 个叶子数据页 ` ###### 第二层：叶子节点 假设每条完整数据约占： `text 36 字节 ` 那么一个 16KB 数据页大约可以存储： `text 16384 ÷ 36 ≈ 455 条数据 ` 第一层最多指向约 1365 个叶子页，因此两层 B+ 树理论上可以粗略存储： `text 1365 × 455 ≈ 621075 ` 即： `text 约 62 万条数据 ` 可以简化理解为： `text Root │ 最多约 1365 个指针 │ ┌─────────┼─────────┐ Page1 Page2 Page... 455条 455条 455条 总数据量： 1365 × 455 ≈ 62 万 ` #### 三层 B+ 树可以存放多少数据 如果 B+ 树增加到三层： `text 第一层：根节点 第二层：非叶子索引页 第三层：叶子数据页 ` 结构可以简化为： `text Root │ 1365 个指针 │ ┌────────┴────────┐ Index Page Index Page │ │ 1365 个指针 1365 个指针 │ │ Data Page Data Page ` 第一层最多指向： `text 1365 个第二层索引页 ` 每个第二层索引页又可以指向： `text 1365 个叶子数据页 ` 因此叶子数据页总数约为： `text 1365 × 1365 ` 每个叶子数据页约保存： `text 455 条数据 ` 最终数据量约为： `text 1365 × 1365 × 455 = 847767375 ` 即大约： `text 8.48 亿条数据 ` 因此，在这个极度简化的估算模型中： | B+ 树高度 | 粗略可存储数据量 | | --------- | ---------------: | | 2 层 | 约 62 万条 | | 3 层 | 约 8.48 亿条 | ##### 为什么 B+ 树只有三层也能存储大量数据 核心原因是 B+ 树的扇出非常大，一个 16KB 的非叶子节点并不存储完整行数据，而主要保存： `text 索引键 + 子页指针 ` 因此一个索引页可以指向大量下一层页面，例如按照前面的简化模型： `text 一个非叶子节点 ≈ 可以指向 1365 个子节点 ` B+ 树每增加一层，理论容量都会成百上千倍增长，所以实际数据库中，即使存在几百万、
几千万甚至更多的数据，B+ 树通常仍然只有较少的层数 查询一条数据时，其过程可以简化理解为： `text 根节点 ↓ 中间索引节点 ↓ 叶子数据页 ↓ 找到目标记录 ` 因此 B+ 树能够使用很少的磁盘 I/O 完成大量数据中的定位 #### 面试回答 InnoDB 的索引主要采用 B+ 树结构，一个数据页默认大小为 16KB 对于主键索引，也就是聚簇索引，完整的行数据存储在 B+ 树的叶子节点中，非叶子节点主要保存索引和指向下一层数据页的指针 因为非叶子节点不需要保存完整行数据，所以一个 16KB 的页面可以保存大量索引项 假设一个非叶子页大约可以指向 1000 多个子页，一个叶子页可以保存几百条数据，那么三层 B+ 树理论上就可以管理上亿条数据 同时叶子节点之间通过双向链表连接，因此 B+ 树不仅适合等值查询，也非常适合范围查询 ### 聚簇索引与非聚簇索引 在 InnoDB 中，索引主要可以分为 聚簇索引（Clustered Index） 和 辅助索引（Secondary Index）。 #### 聚簇索引 聚簇索引的特点是：索引和完整行数据存储在一起，如：主键索引，其 B+ 树的叶子节点存储完整的行数据。 例如： `sql CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(20), age INT ); ` 主键索引可以简化理解为： `text 主键索引 B+ 树 目录 / \ / \ 数据页 数据页 id=1 id=10 name=Tom name=Jack age=20 age=25 ` 叶子节点中不仅存在主键 id，还保存这一行的完整数据： `text id + name + age + ... ` 因此： `sql SELECT FROM user WHERE id = 10; ` 通过主键索引找到对应的叶子节点后，就可以直接取得完整的数据。
> InnoDB 一张表只能有一个聚簇索引，因为完整的行数据只能按照一种 B+ 树结构进行组织。 如果表中没有显式定义主键，InnoDB 会按照一定规则选择其他字段作为聚簇索引；
如果仍然没有合适的字段，则会生成隐藏的行 ID 来组织数据。 #### 非聚簇索引 InnoDB 中通常称为 辅助索引或二级索引（Secondary Index）。 例如为 name 创建索引： `sql CREATE INDEX idx_name ON user(name); ` 此时会额外创建一棵以 name 为索引键的 B+ 树。 但辅助索引的叶子节点不会再次保存完整行数据，而是主要保存： `text 辅助索引列 + 主键值 ` 例如： `text name id Alice 5 Jack 10 Tom 1 ` 因此可以简化理解为： `text 辅助索引 B+ 树 name ↓ 找到 Jack ↓ 得到 id=10 ` 然后再通过： `text id = 10 ` 去主键索引 B+ 树中查找完整行数据： `text 辅助索引 B+ 树 ↓ 得到主键 id ↓ 主键索引 B+ 树 ↓ 获得完整行数据 ` 这种根据辅助索引取得主键值，再根据主键值查询聚簇索引的过程称为： `text 回表 ` 例如： `sql SELECT FROM user WHERE name = 'Jack'; ` 如果 name 上存在辅助索引，查询过程可以简化为： `text idx_name B+ 树 ↓ 找到 name = Jack ↓ 取得主键 id = 10 ↓ 主键索引 B+ 树 ↓ 找到 id = 10 ↓ 获得完整行数据 ` 也就是说，通常需要经过两棵 B+ 树。 不过，并不是所有辅助索引查询都一定需要回表。
例如： `sql SELECT id, name FROM user WHERE name = 'Jack'; ` 假设存在： `sql INDEX idx_name(name) ` 辅助索引叶子节点本身已经保存： `text name + id ` 查询需要的字段都可以直接从索引中取得，因此无需再次查询主键索引。
这种情况称为： `text 覆盖索引 ` ### 聚簇索引与辅助索引对比 | 对比项 | 聚簇索引 | 辅助索引 | | -------------------- | ---------------- | -------------------- | | 典型代表 | 主键索引 | 普通索引、唯一索引等 | | 是否构建 B+ 树 | 是 | 是 | | 叶子节点存储内容 | 完整行数据 | 索引列 + 主键值 | | 一张表数量 | 一个 | 可以有多个 | | 查询完整数据 | 找到叶子节点即可 | 可能需要回表 | | 是否可能使用覆盖索引 | — | 可以 | 可以简单记忆： `text 主键索引： 主键 ↓ 完整数据 ` 辅助索引： `text 索引字段 ↓ 主键 ↓ 完整数据 ` ### 为什么辅助索引不直接存储数据的磁盘位置 一个很自然的问题是： > 既然辅助索引最终要找到完整数据，为什么不直接在辅助索引中保存数据所在的磁盘地址，
而是保存主键值？ 核心原因是：InnoDB 的数据本身就是按照聚簇索引组织的，数据页的位置并不是永久不变的。
例如随着数据不断进行： `text INSERT DELETE UPDATE ` 可能发生： `text 页分裂 页合并 页面重新组织 ` 原来位于某个数据页中的记录，之后可能移动到其他数据页。
假设辅助索引保存物理地址： `text name = Jack ↓ Page 100 ` 如果之后因为页分裂，Jack 所在的数据发生移动： `text Page 100 ↓ Page 205 ` 那么所有引用该数据物理位置的辅助索引都需要同步更新。
如果一张表存在多个辅助索引： `text idx_name idx_age idx_email idx_phone ... ` 数据位置变化可能导致多个索引都需要修改，维护成本较高。
而 InnoDB 选择保存主键： `text name = Jack ↓ id = 10 ` 即使数据所在的数据页发生变化： `text Page 100 ↓ Page 205 ` 只要主键仍然是： `text id = 10 ` 辅助索引就不需要因为数据页位置变化而修改。
查询时只需要： `text 辅助索引 ↓ 主键 id ↓ 聚簇索引 ↓ 当前真实数据位置 ` 因此，本质上是使用稳定的逻辑标识——主键，而不是依赖容易发生变化的物理地址。
不过需要注意： > 如果主键本身发生修改，那么相关辅助索引中的主键值仍然需要更新。
因此实际设计表结构时，也通常建议主键： `text 短 稳定 尽量不修改 ` ### 索引失效的常见情况 所谓“索引失效”，通常是指查询条件无法有效利用已经建立的索引进行快速定位，
MySQL 优化器可能因此选择全表扫描。 常见情况主要有以下几类。 #### LIKE 以通配符开头 假设： `sql CREATE INDEX idx_name ON user(name); ` 下面的查询通常可以使用索引： `sql SELECT FROM user WHERE name LIKE 'Tom%'; ` 因为 B+ 树中的数据按照 name 排序，可以快速找到： `text Tom... ` 所在的位置。 但是： `sql SELECT FROM user WHERE name LIKE '%Tom'; ` 或者： `sql SELECT FROM user WHERE name LIKE '_Tom'; ` 由于第一个字符无法确定，无法直接确定 B+ 树从哪里开始查找，因此通常无法利用普通 B+ 树索引快速定位。 可以简单理解为： `text LIKE 'Tom%' → 知道从 Tom 开始找 LIKE '%Tom' → 不知道从哪里开始找 ` #### 复合索引不满足最左前缀原则 假设存在复合索引： `sql CREATE INDEX idx_name_age ON user(name, age); ` B+ 树首先按照： `text name ` 排序，在 name 相同的情况下，再按照： `text age ` 排序。 因此： `sql WHERE name = 'Tom' ` 可以有效使用索引。 `sql WHERE name = 'Tom' AND age = 20 ` 也可以有效使用索引。 但是： `sql WHERE age = 20 ` 无法直接按照这棵复合索引的排序方式快速定位。
可以理解为： `text (name, age) 先按照 name 排序 ↓ name 相同 ↓ 再按照 age 排序 ` 因此如果跳过 name，直接查询 age，通常无法有效利用这个复合索引。
这就是： `text 最左前缀原则 ` #### OR 中存在无法使用索引的条件 例如： `sql CREATE INDEX idx_name ON user(name); ` 执行： `sql SELECT FROM user WHERE name = 'Tom' OR age = 20; ` 其中： `text name → 有索引 age → 没有索引 ` 为了找到： `text age = 20 ` 的数据，MySQL 可能仍然需要扫描大量数据。 在某些情况下，优化器就可能认为： `text 直接全表扫描 ` 比： `text 索引查询 + 额外扫描 ` 成本更低，从而放弃使用索引。 因此更准确的说法是： > OR 本身不会必然导致索引失效，但如果 OR 两侧存在无法有效使用索引的条件，MySQL 可能选择全表扫描。 ### 其他常见的索引失效或无法充分利用索引的情况 除了上面三种情况，面试中还经常会问到以下几类。
#### 对索引列进行函数运算 例如： `sql CREATE INDEX idx_create_time ON user(create_time); ` 执行： `sql WHERE YEAR(create_time) = 2026 ` 由于对索引列进行了函数计算，普通索引通常无法直接按照原始 create_time 的排序关系进行快速定位。 更适合写成： `sql WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01' ` #### 对索引列进行计算 例如： `sql WHERE age + 1 = 21; ` 相比： `sql WHERE age = 20; ` 前者通常更难直接利用 age 索引进行定位。 #### 出现隐式类型转换 例如： `sql phone VARCHAR(20) ` 并且： `sql CREATE INDEX idx_phone ON user(phone); ` 正确写法： `sql WHERE phone = '13800138000'; ` 如果写成： `sql WHERE phone = 13800138000; ` 可能发生隐式类型转换，从而影响索引的使用。
### 面试回答 InnoDB 中主要有聚簇索引和非聚簇索引 聚簇索引，存放索引与数据，如：主键索引；
而非聚簇索引，则主要存放非聚簇索引列和对应的主键值。
因此通过非聚簇索引查询完整数据时，通常先查询非聚簇索引的 B+ 树得到主键，再根据主键查询聚簇索引。
如果查询需要的字段都已经包含在辅助索引中，则可以通过覆盖索引避免回表。
辅助索引之所以存储主键，而不是直接保存数据的物理地址，是因为 InnoDB 中数据页可能由于插入、删除、页分裂等操作发生位置变化，而主键作为逻辑标识更加稳定，
可以降低多个辅助索引的维护成本。 常见的索引无法有效使用的情况包括：LIKE 以通配符开头、复合索引不满足最左前缀原则、OR 中存在无法使用索引的条件 ## 事务 > MYSQL事务基本概念1_哔哩哔哩_bilibili > > 面试官：
说说MVCC？ 事务隔离级别实现原理是什么？_哔哩哔哩_bilibili 事务（Transaction）可以理解为：将一组数据库操作看作一个整体，
这些操作要么全部成功，要么全部失败 例如转账： `sql START TRANSACTION; UPDATE account SET balance = balance - 100 WHERE id = 1; UPDATE account SET balance = balance + 100 WHERE id = 2; COMMIT; ` 一次转账包含两个操作： `text 账户 A -100 账户 B +100 ` 这两个操作必须同时成功。 如果第二条 SQL 执行失败，就应该回滚第一条 SQL： `sql ROLLBACK; ` 否则可能出现： `text A 已经扣钱 B 却没有收到钱 ` 因此事务的核心目标就是保证一组相关操作的正确性。
#### 事务的四大特性 事务具有四个基本特性，简称 ACID。 | 特性 | 英文 | 含义 | | ------ | ----------- | ------------------------------------------------------------ | | 原子性 | Atomicity | 一个事务中的操作要么全部成功，要么全部失败 | | 一致性 | Consistency | 事务执行前后，数据库都应该处于合法、一致的状态 | | 隔离性 | Isolation | 多个事务并发执行时，相互之间尽量不产生不正确的影响 | | 持久性 | Durability | 事务提交后，数据修改应该被永久保存，即使数据库发生故障也能够恢复 | ### 隔离级别 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | | ---------------- | ---- | ---------- | ----------------------------- | | Read Uncommitted | 可能 | 可能 | 可能 | | Read Committed | 避免 | 可能 | 可能 | | Repeatable Read | 避免 | 避免 | InnoDB 通过 MVCC + 锁机制处理 | | Serializable | 避免 | 避免 | 避免 | MySQL InnoDB 默认： ` Repeatable Read ` 为什么需要事务隔离级别？核心原因是： `text 并发 ` 如果数据库中只有一个事务执行，那么通常不会发生事务之间的数据干扰，但实际系统中可能同时存在：
`text 事务 A 事务 B 事务 C ... ` 多个事务同时读取、修改同一批数据，如果完全不进行隔离，就可能出现： `text 脏读 不可重复读 幻读 ` 因此 SQL 标准定义了不同的事务隔离级别，从低到高分别为： `text 读未提交 ↓ 读已提交 ↓ 可重复读 ↓ 串行化 ` 隔离级别越高： `text 数据隔离能力越强 ` 但通常也意味着： `text 并发性能可能越低 ` ### 读未提交 Read Uncommitted 读未提交是隔离程度最低的隔离级别。 假设： `MySQL 初始： id = 1 balance = 100 ` 事务 A： `MySQL START TRANSACTION; UPDATE account SET balance = 50 WHERE id = 1; ` 但是此时： ` 事务 A 还没有 COMMIT ` 如果事务 B 可以直接读取： `MySQL SELECT balance FROM account WHERE id = 1; ` 得到： ` 50 ` 随后事务 A： `MySQL ROLLBACK; ` 数据重新恢复： `MySQL balance = 100 ` 那么事务 B 曾经读取到的： ` 50 ` 实际上是一个从未真正提交的数据。 这种现象称为： ` 脏读 Dirty Read ` 即： > 一个事务读取到了另一个事务尚未提交的数据 ### 读已提交 Read Committed 读已提交解决了脏读问题。 一个事务只能读取其他事务： ` 已经 COMMIT ` 的数据。 例如初始数据： `，MySQL balance = 100 ` 事务 B： `MySQL START TRANSACTION; SELECT balance FROM account WHERE id = 1; ` 得到： ` 100 ` 此时事务 A： `MySQL START TRANSACTION; UPDATE account SET balance = 200 WHERE id = 1; COMMIT; ` 事务 B 再次执行： `MySQL SELECT balance FROM account WHERE id = 1; ` 得到： ` 200 ` 对于事务 B： ` 第一次读取 → 100 第二次读取 → 200 ` 同一个事务中，对同一条数据进行两次相同查询，却得到了不同结果。
这种现象称为： ` 不可重复读 Non-Repeatable Read ` 即： > 同一个事务中，多次读取同一条记录，由于其他事务进行了修改并提交，导致前后读取结果不一致 ### 可重复读 Repeatable Read InnoDB 默认使用： ` Repeatable Read ` 也就是可重复读。 它解决了： ` 脏读 不可重复读 ` 例如： ` balance = 100 ` 事务 B： `MySQL START TRANSACTION; SELECT balance FROM account WHERE id = 1; ` 得到： ` 100 ` 此时事务 A： `MySQL UPDATE account SET balance = 200 WHERE id = 1; COMMIT; ` 事务 B 再次进行相同的普通查询： `MySQL SELECT balance FROM account WHERE id = 1; ` 仍然可以看到： ` 100 ` 也就是说： ` 事务 B 第一次读取 → 100 事务 A 修改并提交 → 200 事务 B 第二次读取 → 仍然是 100 ` 从事务 B 的视角来看，在自己的事务执行期间，看到的数据版本保持相对稳定。
这就是： ` 可重复读 ` ### 幻读 Phantom Read 幻读主要关注的不是： ` 某一行数据发生变化 ` 而是： ` 满足某个查询条件的记录数量发生变化 ` 例如事务 A： `MySQL START TRANSACTION; SELECT FROM user WHERE age > 20; ` 第一次得到： ` 10 条数据 ` 此时事务 B 插入： `MySQL INSERT INTO user(id, name, age) VALUES (100, 'Tom', 25); COMMIT; ` 如果事务 A 再次执行： `MySQL SELECT FROM user WHERE age > 20; ` 突然看到： ` 11 条数据 ` 就好像凭空多出了一条数据，因此称为： ` 幻读 ` ### 串行化 Serializable Serializable 是隔离程度最高的事务隔离级别。 它会对读取采取更加严格的加锁策略，使存在冲突的事务： ` 等待 ` 从效果上来看，很多存在冲突的操作会趋近于： ` 串行执行 ` 因此可以避免： ` 脏读 不可重复读 幻读 ` 但代价也很明显： ` 并发能力下降 锁竞争增加 事务阻塞增加 ` 因此普通业务系统中通常不会默认使用 Serializable。 需要注意，Serializable 并不是说： ` 整个数据库同一时刻只能运行一个事务 ` 而是数据库通过更加严格的锁控制，使存在数据冲突的事务按照安全顺序执行。
### MVCC 多版本并发控制 image-20261008192937239 MVCC 全称： ` Multi-Version Concurrency Control ` 即： ` 多版本并发控制 ` 其核心思想是： > 当一条数据被修改后，不需要立即让所有事务都只能看到最新版本，而是保留数据的历史版本，
让不同事务根据自己的可见性规则读取对应版本的数据 这样可以减少： ` 读操作与写操作之间的锁竞争 ` 提高数据库的并发性能。 ### MVCC 的核心组成 理解 MVCC 可以抓住三个核心结构： ` 隐藏字段 + Undo Log 版本链 + Read View ` ### 隐藏字段 InnoDB 的记录中存在一些内部维护的隐藏字段，其中与 MVCC 密切相关的主要有： ` DB_TRX_ID DB_ROLL_PTR ` 其中： ` DB_TRX_ID ` 记录最近一次修改当前数据的事务 ID。 而： ` DB_ROLL_PTR ` 指向 Undo Log 中上一版本的数据。 因此一条数据经过多次修改后，可以形成类似： ` 当前版本 ↓ 历史版本 1 ↓ 历史版本 2 ↓ 历史版本 3 ` 这样的版本链。 ### Undo Log 版本链 假设最开始： ` name = Tom ` 事务 10 修改为： ` name = Jack ` 随后事务 20 修改为： ` name = Bob ` 从逻辑上可以理解为： ` 当前记录： name = Bob trx_id = 20 │ │ roll_ptr ↓ Undo Log： name = Jack trx_id = 10 │ ↓ name = Tom trx_id = ... ` 因此数据库中存在： ` Bob Jack Tom ` 多个历史版本。 需要注意： > 并不是所有版本都完整地直接存放在数据页中，当前版本位于数据页中，而旧版本主要通过 Undo Log 串联形成版本链 ### Read View 有了多个数据版本，还存在一个关键问题： ` 当前事务到底应该读取哪个版本？ ` 这就是： ` Read View ` 负责解决的问题。 Read View 可以理解为： > 某个事务进行快照读时，用来判断哪些事务产生的数据版本对自己可见的一套规则 因此 Read View 并不是： ` 直接复制一份完整数据库数据 ` 而更接近： ` 保存事务状态信息 + 根据事务 ID 判断数据版本是否可见 ` 查询数据时： ` 读取当前版本 ↓ 根据 Read View 判断是否可见 ↓ 可见 → 返回 ↓ 不可见 ↓ 沿 Undo Log 版本链向前寻找 ↓ 直到找到可见版本 ` #### Read Committed 与 Repeatable Read 的区别 MVCC 正是实现： ` Read Committed Repeatable Read ` 的重要机制。 两者最大的区别之一就是： ` Read View 的创建时机不同 ` ##### Read Committed 在 Read Committed 中： ` 每一次快照读 ` 通常都会重新创建 Read View。 例如： ` 事务 B 第一次查询 ↓ 创建 Read View 1 ↓ 读取 name = Tom 事务 A 修改 Tom → Jack ↓ COMMIT 事务 B 第二次查询 ↓ 重新创建 Read View 2 ↓ Jack 已经提交 ↓ 读取 name = Jack ` 所以： ` 第一次 → Tom 第二次 → Jack ` 这就可能产生： ` 不可重复读 ` ##### Repeatable Read 在 Repeatable Read 中： ` 第一次快照读 ` 会创建 Read View。 后续快照读通常继续： ` 复用这个 Read View ` 例如： ` 事务 B 第一次查询 ↓ 创建 Read View ↓ 读取 Tom 事务 A ↓ 修改 Tom → Jack ↓ COMMIT 事务 B 第二次查询 ↓ 继续使用原来的 Read View ↓ Jack 对当前 Read View 不可见 ↓ 沿 Undo Log 查找旧版本 ↓ 找到 Tom ` 因此： ` 第一次 → Tom 第二次 → Tom ` 实现： ` 可重复读 ` 需要特别注意： > Repeatable Read 的 Read View 通常不是在 START TRANSACTION 的瞬间创建，而是在第一次普通快照读时创建 例如： ` START TRANSACTION; -- 此时通常还没有建立快照读使用的 Read View SELECT FROM user; -- 第一次快照读时创建 Read View ` ### MVCC 为什么并发性能更高 如果不使用 MVCC，为了保证事务读取的数据不发生变化，一种简单方式就是： ` 读数据 ↓ 加锁 ↓ 其他事务不能修改 ↓ 事务结束 ↓ 释放锁 ` 这样容易出现： ` 事务 A 读数据 ↓ 事务 B 等待 事务 C 也等待 ` 并发能力受到影响。 而 MVCC 的思路是： ` 事务 A → 读取旧版本 事务 B → 修改并提交新版本 ` 两者在很多情况下可以同时进行。 例如： ` 数据库当前： Tom ` 事务 A 开始读取： ` Tom ` 事务 B： ` Tom → Jack COMMIT ` 事务 A 后续仍然可以： ` 通过 Undo Log ↓ 读取 Tom ` 而新的事务可以读取： ` Jack ` 因此不同事务可以： ` 读取不同的数据版本 ` 而不是所有读操作都必须通过加锁互相等待。
这也是 MVCC 的核心优势： ` 读写并发 ↓ 减少锁竞争 ↓ 提高数据库并发性能 ` 不过需要注意： > MVCC 主要作用于快照读，并不意味着 MySQL 的事务完全不需要锁 对于： ` SELECT ... FOR UPDATE; UPDATE ... DELETE ... ` 等当前读和写操作，InnoDB 仍然需要使用行锁、间隙锁、Next-Key Lock 等锁机制。 ### 面试回答 事务是一组要么全部成功、要么全部失败的数据库操作，具有 ACID 四个特性，分别是原子性、一致性、隔离性和持久性。
由于数据库中多个事务会并发执行，所以存在脏读、不可重复读和幻读等问题，因此数据库提供了 Read Uncommitted、Read Committed、Repeatable Read 和 Serializable 四种隔离级别，InnoDB 默认使用 Repeatable Read。 InnoDB 中 Read Committed 和 Repeatable Read 主要通过 MVCC 实现。MVCC 的核心由 Undo Log 版本链、记录中的事务信息以及 Read View 组成。数据发生修改后，旧版本通过 Undo Log 保存并形成版本链，事务进行快照读时通过 Read View 判断哪个版本对当前事务可见。 Read Committed 通常每次快照读都会创建新的 Read View，因此可能读取到其他事务最新提交的数据，产生不可重复读；
Repeatable Read 通常在第一次快照读时创建 Read View，后续继续复用，因此可以保证同一个事务中的多次快照读看到相对一致的数据。
MVCC 允许不同事务读取同一条数据的不同版本，减少了读写之间的锁竞争，因此相比所有读取都依赖加锁，
能够提供更高的并发性能。 不过 MVCC 主要解决快照读的问题，对于 UPDATE、DELETE、SELECT FOR UPDATE 等当前读操作，InnoDB 仍然需要结合行锁、间隙锁和 Next-Key Lock 等机制保证事务隔离。 ## MySQL 锁 MySQL 中的锁可以从锁粒度、读写类型、加锁思想等不同角度进行分类，而 InnoDB 在此基础上还实现了记录锁、间隙锁、临键锁等更细粒度的锁机制 #### 按锁粒度分类 | 锁类型 | 锁定范围 | 典型场景 | 特点 | | ------ | -------------- | --------------------- | -------------------------- | | 全局锁 | 整个数据库实例 | 全库备份 | 锁粒度最大，对业务影响较大 | | 表锁 | 整张表 | MyISAM、部分 DDL 操作 | 开销较小，但并发能力较弱 | | 行锁 | 索引记录 | InnoDB 并发读写 | 粒度小，并发能力较强 | 全局锁可以通过： `sql FLUSH TABLES WITH READ LOCK; ` 将整个实例设置为只读状态，对于全部使用 InnoDB 的数据库进行逻辑备份时，一般更推荐： `bash mysqldump --single-transaction ` 它利用 InnoDB 的 MVCC 获取一致性快照，通常无需长时间阻塞正常写操作 #### 按读写类型分类 | 锁类型 | 英文 | 作用 | 常见语法 | | ------ | ---------------------- | ------------------------------------------------------------ | ------------ | | 共享锁 | Shared Lock，S Lock | 多个事务可以同时持有共享锁，但会阻止其他事务获得冲突的排他锁 | FOR SHARE | | 排他锁 | Exclusive Lock，X Lock | 当前事务独占该记录，阻止其他事务对其进行冲突的加锁读或修改 | FOR UPDATE | 共享锁： `sql SELECT FROM user WHERE id = 1 FOR SHARE; ` 排他锁： `sql SELECT FROM user WHERE id = 1 FOR UPDATE; ` 需要注意： > 排他锁并不意味着其他事务完全无法读取该数据 其他事务的普通 SELECT 通常属于 MVCC 快照读，仍然可以读取符合其 Read View 的版本，排他锁主要会阻塞其他事务的： `sql UPDATE DELETE SELECT ... FOR UPDATE ` 等存在锁冲突的操作 因此： `sql SELECT ... FOR UPDATE; ` 可以理解为： > 当前虽然执行的是读取，但以“准备修改”的方式对数据加排他锁 #### 按加锁思想分类 | 类型 | 核心思想 | 特点 | | ------ | -------------------------------------- | -------------------------------- | | 悲观锁 | 假设并发冲突很可能发生，操作前先加锁 | 数据一致性强，但可能降低并发 | | 乐观锁 | 假设冲突较少，更新时再检测是否发生冲突 | 并发能力较好，通常通过版本号实现 | 例如乐观锁可以设计： `sql UPDATE product SET stock = stock - 1, version = version + 1 WHERE id = 1 AND version = 5; ` 若 version 已经发生变化，则更新失败，由业务决定是否重试 ## InnoDB 常见锁 | 锁类型 | 锁定对象 | 主要作用 | | ------ | ---------------------- | ------------------------------------ | | 意向锁 | 表 | 快速判断表中是否存在与表锁冲突的行锁 | | 记录锁 | 已存在的索引记录 | 防止已有记录被并发修改 | | 间隙锁 | 两条索引记录之间的间隙 | 防止其他事务向间隙插入新记录 | | 临键锁 | 记录 + 前方间隙 | 同时锁住已有记录和插入空间 | ### 意向锁 假设事务 A 已经为某一行加了排他锁： `sql SELECT FROM user WHERE id = 10 FOR UPDATE; ` 此时事务 B 想给整个表加一个与之冲突的表锁，需要判断： > 当前表中是否存在已经被锁住的记录？ 如果没有意向锁，就可能需要遍历检查是否存在行锁，因此 InnoDB 在事务获得行级共享锁或排他锁时，会同时在表级别设置对应的意向锁 例如： `text 行级共享锁 ↓ 表级意向共享锁 IS 行级排他锁 ↓ 表级意向排他锁 IX ` 这样其他事务申请表锁时，可以先检查表上的意向锁，而不需要逐条检查所有记录，
效率提升 ### 记录锁、间隙锁与临键锁 假设索引数据为： `text 10 20 30 40 ` | 锁类型 | 示例 | 锁定内容 | 是否阻止区间插入 | | ------ | --------- | -------------------------- | ---------------- | | 记录锁 | [30] | 已存在的 30 | 否 | | 间隙锁 | (20,30) | 20 与 30 之间的空隙 | 是 | | 临键锁 | (20,30] | 间隙 (20,30) + 记录 30 | 是 | 临键锁 Next-Key Lock 本质上可以理解为： `text Next-Key Lock = Gap Lock + Record Lock ` 通常表现为左开右闭区间： `text (20,30] ` 其中： `text (20,30) → Gap Lock 30 → Record Lock ` ## innodb为什么需要间隙锁、临界锁 背景：通过主键进行精确查询时，此时innodb会自动添加记录锁（行锁），
如果是范围查询直接为该范围内的所有数据添加记录锁不就行了吗？
为什么还细分为间隙锁、临界锁？ 因为，记录锁只能为已经存在的数据加锁，而间隙锁、临界锁可以为区间进行加锁，
举例：user 表存在以下数据 | id | age | | ---- | ---- | | 1 | 10 | | 2 | 20 | | 3 | 30 | 其中，age 添加了索引，此时事务A进行范围查询： `sql SELECT FROM user WHERE age BETWEEN 20 AND 30 FOR UPDATE; ` FOR UPDATE 是排它锁（写锁），会进行加锁，若只为范围内的所有数据添加记录锁，那么加锁的数据只有两个：
` age = 20 age = 30 ` 此时会产生问题，当事务 B 执行： `MySQL INSERT INTO user(age) VALUES (25); ` 25 这条记录原本不存在，所以事务 A 根本没有任何“记录”可以锁住它，于是事务 B 可以插入： ` 10 20 25 30 ` 此时事务 A 再执行： `SQL SELECT FROM user WHERE age BETWEEN 20 AND 30 FOR UPDATE; ` 结果就从： ` 20 30 ` 变成： ` 20 25 30 ` 这就是典型的幻读，因此： > 记录锁无法锁住“尚不存在的数据” 这就是间隙锁存在的根本原因 ### 记录锁解决什么问题 记录锁 Record Lock 锁的是： > 索引中已经存在的某条记录 例如： `SQL SELECT FROM user WHERE id = 10 FOR UPDATE; ` 如果： `SQL id = 10 ` 确实存在，那么 InnoDB 可以直接锁住这条索引记录： ` ... 9 | [10] | 11 ... ↑ 记录锁 ` 其他事务不能： `SQL UPDATE user SET ... WHERE id = 10; DELETE FROM user WHERE id = 10; ` 但它并不会阻止： `SQL INSERT id = 9 INSERT id = 11 ` 如果查询本身只是唯一键精确匹配，这通常也没关系，因为： `SQL WHERE id = 10 ` 无论旁边插入多少数据，都不会影响查询结果，所以这种情况一般只需要记录锁 ### 间隙锁解决什么问题 间隙锁 Gap Lock 锁的是： > 两条索引记录之间不存在数据的区域 例如索引： ` 10 20 30 40 ` 其中： ` (20, 30) ` 就是一个 gap，如果事务 A 锁住： ` (20, 30) ` 那么其他事务就不能插入： ` 21 25 29 ` 间隙锁真正解决的是： > 防止其他事务制造“新的索引记录” 所以它主要服务于范围查询 ### 为什么还需要 Next-Key Lock Next-Key Lock，也常翻译为临键锁，它实际上不是第三种完全独立的东西，而是： ` Next-Key Lock = Record Lock + Gap Lock ` 比如索引： ` 10 20 30 40 ` 一个 Next-Key Lock 通常可以理解成： ` (20, 30] ` 即： ` (20,30) → Gap Lock 30 → Record Lock ` 合起来就是： ` (20,30] ` 所以： > Next-Key Lock 本质上就是“既锁住已有记录（30），又锁住它前面的空隙（20-30）” ### 为什么不直接设置范围锁 这里正是 InnoDB 设计比较巧妙的地方，可能会想： > 那为什么不直接设计一个 20 <= age <= 30 的范围锁？ 理论上当然可以，但 InnoDB 的数据本身就是存储在 B+Tree 索引 中的，所以它更自然的做法是： ` 索引记录 + 索引之间的 gap ` 例如： ` 10 20 30 40 ↑ ↑ record record └──────┘ gap ` 于是所有范围都可以由： ` Record Lock Gap Lock ` 组合出来，这与 B+Tree 的存储结构天然契合 ### 完整举例 当前索引： ` 10 20 30 40 ` 执行： `SQL SELECT FROM user WHERE age >= 20 AND age <= 30 FOR UPDATE; ` 为了防止其他事务： ` UPDATE age = 20 UPDATE age = 30 ` 需要记录锁锁住20与30，同时为了防止： ` INSERT age = 21 INSERT age = 25 INSERT age = 29 ` 需要间隙锁锁住区间，因此可以抽象理解为： ` Record Gap Record ↓ ↓ ↓ 10 [20] ─────────────── [30] 40 ` 或者使用 Next-Key Lock 表示： ` (10,20] (20,30] ` 具体实际加锁区间还会受到查询条件、索引扫描范围等影响，不一定严格就是上面这个简化图，
但理解原理时可以这样看 ## MySQL 解决幻读 InnoDB 默认事务隔离级别为 可重复读（Repeatable Read，RR），在 RR 隔离级别下，InnoDB 主要通过两套机制处理幻读： | 读取方式 | 常见操作 | 实现机制 | 是否加锁 | | -------- | ------------------------------------------- | ---------------- | ---------- | | 快照读 | 普通 SELECT | MVCC + Read View | 通常不加锁 | | 当前读 | SELECT ... FOR UPDATE、UPDATE、DELETE | 当前版本 + 锁 | 加锁 | 可以概括为： ` 快照读 ↓ MVCC + Read View ↓ 控制当前事务能够看到哪些版本 当前读 ↓ 记录锁 / 间隙锁 / 临键锁 ↓ 控制其他事务能否修改记录或插入新记录 ` 因此： > InnoDB 在 RR 隔离级别下通过 MVCC 和锁机制共同解决幻读问题 ### MVCC 与 Read View MVCC 的核心基础包括： ` undo log 版本链 + Read View 版本可见性规则 ` 其中： - undo log 保存数据修改前的旧版本，并形成版本链 - Read View 用于判断版本链中的哪些版本对当前事务可见 例如一条记录可能存在： ` 最新版本 V3 ↓ 旧版本 V2 ↓ 旧版本 V1 ` 事务读取数据时，并不是直接读取某份提前复制好的“数据快照”，而是： ` 读取当前记录 ↓ 根据 Read View 判断当前版本是否可见 ↓ 如果不可见，则沿 undo log 版本链继续查找 ↓ 找到符合可见性规则的版本 ↓ 读取该版本对应的数据 ` 因此更准确地说： ` Read View ≠ 数据快照 Read View = 版本可见性规则 ` 可以把 MVCC 理解为： > 数据可以产生多个版本，Read View 决定当前事务应该读取哪个版本 ### READ COMMITTED 与 REPEATABLE READ 两种隔离级别的重要区别之一，是 Read View 的使用方式不同： | 隔离级别 | Read View | 效果 | | --------------- | ------------------------------------------------------ | -------------------------------- | | READ COMMITTED | 每次一致性快照读通常重新创建 | 可以看到其他事务最新已提交版本 | | REPEATABLE READ | 一个事务中的一致性快照读通常复用第一次建立的 Read View | 多次读取遵循相同的版本可见性规则 | 需要注意： > RR 并不是“相同 SQL 才复用 Read View” 而是： > 同一个事务中的一致性快照读通常复用同一个 Read View 例如： `SQL SELECT FROM user; SELECT FROM product; SELECT FROM orders; ` 虽然 SQL 不同，但都可以使用同一套版本可见性规则 ### MVCC 如何避免快照读幻读 假设 age 已建立索引，初始数据： ` 20 30 ` 事务 A： `SQL BEGIN; SELECT FROM user WHERE age BETWEEN 20 AND 30; ` 查询结果： ` 20 30 ` 第一次一致性快照读建立 Read View，此时事务 B： `SQL INSERT INTO user(age) VALUES (25); COMMIT; ` 数据库中已经存在新的记录版本： ` 20 25 30 ` 事务 A 再次执行： `SQL SELECT FROM user WHERE age BETWEEN 20 AND 30; ` 仍然可能得到： ` 20 30 ` 原因是事务 A 仍然按照之前的 Read View 判断版本是否可见，事务 B 后插入的： ` 25 ` 属于新的版本，对事务 A 当前使用的 Read View 不可见，因此事务 A 不会读取这个版本，所以 MVCC 的核心不是： ` 保存第一次查询结果 ` 而是： ` 保存版本可见性规则 ↓ 后续读取时继续按照该规则选择数据版本 ` ### 为什么 MVCC 无法单独解决当前读幻读 假设事务 A 通过 Read View 能看到的版本仍然对应： ` 20 30 ` 但是事务 B 已经提交： ` 25 ` 数据库当前真实存在的最新记录为： ` 20 25 30 ` 如果事务 A 接下来执行： `SQL UPDATE user SET some_column = 'x' WHERE age BETWEEN 20 AND 30; ` 不能因为： ` Read View 看不到 25 ` 就认为： ` UPDATE 也不会处理 25 ` 因为 UPDATE、DELETE、SELECT ... FOR UPDATE 等操作属于： `SQL 当前读 ` 当前读需要读取并操作当前最新版本的数据，而不是继续按照普通快照读的方式只读取旧版本，
因此： ` 普通 SELECT ↓ 可能只看到： 20 30 UPDATE ↓ 可能处理： 20 25 30 ` 所以： > Read View 只能决定当前事务读取哪个版本，不能阻止其他事务真正创建新的记录或修改现有记录 这就是 MVCC 无法单独解决当前读场景幻读的原因 ### FOR UPDATE 的作用 普通查询： `SQL SELECT FROM user WHERE age BETWEEN 20 AND 30; ` 属于快照读，通常通过 MVCC 读取符合 Read View 的版本，不主动加行级锁 而： `SQL SELECT FROM user WHERE age BETWEEN 20 AND 30 FOR UPDATE; ` 属于： ` 当前读 + 排他性质的加锁读 ` 也就是说： > FOR UPDATE 虽然执行的是 SELECT，但会按照“准备修改这些数据”的方式读取最新版本，并申请排他性质的锁 ### FOR UPDATE 会加什么锁 FOR UPDATE 并不代表固定添加某一种锁，实际锁类型由： ` 隔离级别 查询条件 使用的索引 是否为唯一索引 记录是否存在 索引扫描范围 ` 共同决定，常见情况： | 查询情况 | 可能使用的锁 | | -------------------------- | -------------------------- | | 唯一索引精确匹配，记录存在 | 记录锁 | | 普通索引范围查询 | 临键锁、间隙锁 | | 查询不存在的索引值 | 可能锁对应间隙 | | RR 下范围扫描 | 通常需要锁住相关记录和间隙 | 因此： ` FOR UPDATE ≠ 固定的“记录锁 + 间隙锁” ` 更准确的是： ` FOR UPDATE ↓ 触发当前读 ↓ 申请排他性质的锁 ↓ InnoDB 根据索引扫描情况选择具体锁类型 ` ### 记录锁、间隙锁与临键锁 假设索引： ` 10 20 30 40 ` | 锁类型 | 示例 | 锁定对象 | 主要作用 | | ------ | --------- | ------------------ | -------------------------- | | 记录锁 | [30] | 已存在的索引记录 | 防止已有记录被冲突修改 | | 间隙锁 | (20,30) | 两条记录之间的间隙 | 防止插入新的索引记录 | | 临键锁 | (20,30] | 间隙 + 右侧记录 | 同时保护已有记录和插入空间 | 临键锁： ` Next-Key Lock = Gap Lock + Record Lock ` 例如： ` (20,30] ` 可以拆分为： ` (20,30) → Gap Lock 30 → Record Lock ` ### 当前读为什么需要间隙锁 假设当前索引： ` 10 20 30 40 ` 事务 A： `SQL BEGIN; SELECT FROM user WHERE age BETWEEN 20 AND 30 FOR UPDATE; ` 如果仅仅给： ` 20 30 ` 添加记录锁，那么事务 B 仍然可以尝试： `SQL INSERT INTO user(age) VALUES (25); ` 原因是： ` 25 ` 原本并不存在，因此没有任何已有记录可以被记录锁锁住，如果事务 B 成功插入，数据库就会变成： ` 10 20 25 30 40 ` 事务 A 再次执行当前读： `SQL SELECT FROM user WHERE age BETWEEN 20 AND 30 FOR UPDATE; ` 结果就可能从： ` 20 30 ` 变成： ` 20 25 30 ` 产生幻读，因此： > 记录锁只能保护已有记录，不能保护尚不存在的数据 ### 间隙锁如何解决问题 InnoDB 不需要尝试锁住未来所有可能出现的： ` 21 22 23 24 25 ... 29 ` 而只需要锁住： ` (20,30) ` 这个索引间隙，可以理解为： ` 20 ----------- 30 Gap ` 当这个间隙被锁住以后，其他事务就不能向其中插入新的索引记录，例如： `SQL INSERT INTO user(age) VALUES (25); ` 会因为需要进入被锁定的间隙而等待，因此间隙锁解决的是： > 防止当前读范围内出现新的记录 ### 临键锁如何解决当前读幻读 范围查询往往既需要保护已有记录，也需要保护记录之间的空隙，因此 InnoDB 可以使用临键锁： ` Next-Key Lock = Record Lock + Gap Lock ` 例如： ` (20,30] ` 表示： ` (20,30) ↓ 禁止插入新记录 30 ↓ 保护已有记录 ` 因此临键锁可以同时实现： ` 已有记录不能被冲突修改 + 相关间隙不能插入新记录 ` 从而避免当前读产生幻读 ### 完整示例 当前索引： ` 10 20 30 40 ` 事务 A： `SQL BEGIN; SELECT FROM user WHERE age BETWEEN 20 AND 30 FOR UPDATE; ` 这是一个当前读，在 RR 隔离级别下，InnoDB 会根据实际索引扫描范围对相关记录和间隙进行加锁，此时事务 B： `SQL INSERT INTO user(age) VALUES (25); ` 由于 25 需要插入事务 A 已经保护的索引间隙，因此会被阻塞，事务 A 再执行： `SQL SELECT FROM user WHERE age BETWEEN 20 AND 30 FOR UPDATE; ` 仍然只能看到： ` 20 30 ` 事务 A： ` COMMIT; ` 之后事务 B 才能够继续插入，因此： ` 事务 A 当前读 ↓ 锁住相关记录和间隙 事务 B INSERT 25 ↓ 被阻塞 事务 A 再次当前读 ↓ 不会出现 25 ` 当前读幻读因此被避免 ## MVCC 与锁的关系 可以最终归纳为： | 机制 | 作用 | 核心思想 | | ---------------- | ------------------ | ------------------------------ | | MVCC + Read View | 保证快照读的一致性 | 根据版本可见性规则选择数据版本 | | 记录锁 | 保护已有记录 | 防止已有记录被冲突修改 | | 间隙锁 | 保护记录之间的空间 | 防止插入新记录 | | 临键锁 | 记录锁 + 间隙锁 | 同时保护已有记录和插入空间 | 整个逻辑可以表示为： ` 普通 SELECT ↓ 快照读 ↓ MVCC ↓ Read View 判断版本是否可见 ↓ 沿版本链找到可见版本 ↓ 读取该版本的数据 SELECT ... FOR UPDATE UPDATE DELETE ↓ 当前读 ↓ 读取最新可操作版本 ↓ 根据索引扫描范围加锁 ↓ Record Lock / Gap Lock / Next-Key Lock ↓ 限制其他事务修改或插入 ` 最终可以记住两句话： > Read View 决定“当前事务应该读取哪个版本” > 锁机制决定“其他事务现在能不能修改或插入数据” 因此，InnoDB 在 RR 隔离级别下，是通过 MVCC + Read View 处理快照读，通过记录锁、间隙锁、临键锁等机制处理当前读，两者共同避免幻读 ## 当前读与快照读 | 类型 | 常见 SQL | 读取方式 | | ------ | ------------------------------------------------------------ | ------------------------------------------------------------ | | 快照读 | 普通 SELECT | 根据 Read View 判断版本可见性，必要时沿 undo log 版本链找旧版本 | | 当前读 | SELECT ... FOR UPDATE、SELECT ... FOR SHARE、UPDATE、DELETE | 读取当前最新可操作版本，并进行加锁 | 版本链只是记录了数据的可见与不可见，不同版本之间数据的可见性不同，但数据的存在与否于版本链无关 ## SQL 优化 思想：SQL优化基本是建立在索引的基础上，通过底层的B+树进行索引的使用，
提高查询效率 | 优化方式 | 核心思路 | | ----------------------- | -------------------------------------- | | ORDER BY 利用索引 | 减少额外排序 filesort | | GROUP BY 利用索引 | 减少排序、临时表等操作 | | MIN / MAX 利用索引 | 直接定位索引边界值 | | 使用覆盖索引 | 减少回表 | | 小表驱动大表：JOIN 优化 | 使用join时主表为小表，通过小表驱动大表 | | SQL 优化分析 | 通过慢查询日志和 EXPLAIN 定位问题 | ### SQL 优化分析 SQL 出现性能问题时，可以按照以下流程排查： ` 开启慢查询日志 ↓ 找到执行时间较长的 SQL ↓ 使用 EXPLAIN 查看执行计划 ↓ 分析索引使用情况、扫描行数、排序、临时表等 ↓ 调整索引或 SQL ` 例如： `SQL EXPLAIN SELECT id, name FROM user WHERE age = 20; ` 重点可以关注： - type：访问类型 - possible_keys：可能使用的索引 - key：实际使用的索引 - rows：预计扫描的数据量 - Extra：是否出现 Using filesort、Using temporary 等信息 需要注意： > EXPLAIN 主要用于分析执行计划，并不能直接表示 SQL 的真实执行耗时 如果需要查看实际执行过程和耗时，可以进一步使用 EXPLAIN ANALYZE ### ORDER BY 优化 ORDER BY 需要对查询结果进行排序，例如： `SQL SELECT FROM user ORDER BY age; ` 如果 age 没有合适的索引，MySQL 可能需要额外执行排序操作： ` 查询数据 ↓ 额外排序 ↓ 返回结果 ` 如果建立： `SQL CREATE INDEX idx_age ON user(age); ` B+Tree 本身按照 age 有序排列，因此 MySQL 在满足条件时可以直接按照索引顺序读取数据： ` 索引： 10 20 30 40 ` 从而避免额外排序，因此： ` 没有合适索引 ↓ 查数据 + 排序 可以利用索引顺序 ↓ 按索引读取数据 ↓ 减少排序开销 ` 不过并不是： > 只要 ORDER BY 字段创建索引就一定能够利用索引排序 还需要考虑查询条件、联合索引顺序、排序方向等因素，例如联合索引： ` INDEX idx_name_age(name, age) ` 对于： `SQL SELECT FROM user WHERE name = 'Tom' ORDER BY age; ` 由于： ` name 已经固定为 Tom ↓ 在 name = Tom 的范围内 ↓ age 按索引顺序排列 ` 因此通常能够很好地利用联合索引 ### GROUP BY 优化 GROUP BY 用于根据某个字段进行分组： `SQL SELECT age, COUNT() FROM user GROUP BY age; ` 如果 age 没有索引，MySQL 可能需要对大量数据进行排序或使用临时结构完成分组 如果： `SQL CREATE INDEX idx_age ON user(age); ` 索引本身已经按照： ` 18 18 18 20 20 25 25 ` 这样的顺序组织，相同 age 的记录在索引中通常是连续的，因此 MySQL 可以更高效地进行分组，可以理解为： ` 无索引： 20 18 25 18 20 25 18 ↓ 需要先组织数据 18 18 18 20 20 25 25 ` 而利用索引： ` 18 18 18 20 20 25 25 ↑ 索引天然有序 ` 因此可以减少分组过程中的额外工作 ### MIN / MAX 优化 例如： `sql SELECT MAX(age) FROM user; ` 如果没有 age 索引，理论上可能需要检查大量数据才能找到最大值 如果： `sql CREATE INDEX idx_age ON user(age); ` 由于索引本身有序： ` 10 20 30 40 50 ` 最大值就在索引的一端，因此 MySQL 可以快速找到： ` MAX(age) → 50 ` 同理： `SQL SELECT MIN(age) FROM user; ` 可以从索引另一端快速定位最小值，因此 MIN / MAX 非常适合利用索引的有序性 ### 覆盖索引：尽量避免回表 InnoDB 的辅助索引叶子节点通常保存： ` 辅助索引字段 + 主键值 ` 例如： `SQL INDEX idx_age(age) ` 可以抽象理解为： ` age id 18 → 1 20 → 5 25 → 8 ` 执行： `SQL SELECT id, age FROM user WHERE age = 20; ` 辅助索引中已经存在： ` age id ` 因此可以直接返回结果，不需要再根据主键访问聚簇索引，这就是： > 覆盖索引 但如果： `SQL SELECT id, age, name FROM user WHERE age = 20; ` 而 name 不在辅助索引中，则需要： ` idx_age ↓ 找到 id ↓ 根据 id 查询聚簇索引 ↓ 获取 name ` 这个过程就是： > 回表 因此在业务允许的情况下，可以尽量让查询字段被索引覆盖，从而减少回表次数，
例如： `SQL INDEX idx_age_name(age, name) ` 对于： ` SELECT age, name FROM user WHERE age = 20; ` 就可能形成覆盖索引 ### 小表驱动大表：JOIN 优化 多表连接时，需要尽量减少连接过程中参与计算的数据量，例如： `SQL SELECT FROM small_table s JOIN large_table l ON s.user_id = l.user_id; ` 传统上经常使用： > 小表驱动大表 其核心思想并不是简单地规定“小表必须写在前面”，而是： > 尽量让外层处理的数据量更少，并让被匹配的一侧具有合适索引 例如： ` 小表：100 行 大表：100 万行 ` 如果能够先处理小表中的少量记录，再利用大表的索引查找： ` 小表 100 行 ↓ 每一行通过索引查大表 ↓ 快速定位对应记录 ` 通常比大量扫描更加高效，不过需要注意： > MySQL 优化器会根据统计信息自动选择 JOIN 顺序，并不完全取决于 SQL 中表的书写顺序 因此 JOIN 优化更应该关注： ` 参与连接的数据量 + JOIN 字段是否有索引 + WHERE 条件能否提前过滤数据 + 实际执行计划 ` 而不能机械地认为： ` FROM 小表 JOIN 大表 ` 就一定更快 ## 模拟面试题 ### 为什么联合索引 (name, age) 必须优先知道 name，才能高效地利用 age？请从 B+ 树中数据的排序方式解释，而不是只回答“因为最左前缀原则” > 因为B+树对于联合索引存放的数据进行排序时，首先对name进行排序，当name相同时，
再对age进行排序，此时age并不是全局有序的，因此若缺失name只对age进行索引时，
索引失效，可能会进行全表扫描 你这段回答里的核心逻辑是完整的： ` 联合索引 (name, age) 先按 name 排序 ↓ name 相同时 ↓ 再按 age 排序 ` 所以 age 只是局部有序，不是全局有序。缺少 name 这个前提时，就不能直接根据 age = 20 快速定位到一个连续的索引范围 有一个表达细节建议修正： > “只对 age 进行索引时，索引失效” 更准确应说： > “只根据 age 查询时，这个 (name, age) 联合索引通常无法被用于高效定位” 因为 age` 本身并没有单独创建索引，所以不是“age 索引失效”，而是联合索引无法充分利用