2、MySQL 原理一

1、SELECT 语句的执行

先来一个上帝视角图，下面就是 MySQL 执行一条 SQL 查询语句的流程，也从图中可以看到 MySQL 内部架构里的各个功能模块（联查皆有之）

• 连接器：建立连接，管理连接、校验用户身份
• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行（MySQL 8.0 已删除该模块）
• 解析 SQL：通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型
• 执行 SQL：执行 SQL 共有三个阶段
  • 预处理阶段：检查表或字段是否存在、将 select * 中的 * 符号扩展为表上的所有列
  • 优化阶段：基于查询成本的考虑，选择查询成本最小的执行计划
  • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端

MySQL 的架构共分为两层：Server 层和存储引擎层

• Server 层负责建立连接、分析和执行 SQL
  MySQL 大多数的核心功能模块都在这实现，主要包括：连接器，查询缓存、解析器、预处理器、优化器、执行器等
  所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等）都在 Server 层实现
• 存储引擎层负责数据的存储和提取
  支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层
  现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始，InnoDB 成为了 MySQL 的默认存储引擎
  我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同
  比如 InnoDB 支持索引类型是 B+ 树 ，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引

2、MySQL 一行记录是怎么存储的

从数据页的角度看 B+ 树
MySQL 一行记录是怎么存储的

• 默认每个页的大小为 16KB，页是 InnoDB 存储引擎磁盘管理的最小单元
• 在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配
  每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I / O 了
• 表空间是由各个段（segment）组成的，段是由多个区（extent）组成的，段一般分为数据段、索引段和回滚段等
  索引段：存放 B + 树的非叶子节点的区的集合
  数据段：存放 B + 树的叶子节点的区的集合
  回滚段：存放的是回滚数据的区的集合，之前讲事务隔离的时候就介绍到了 MVCC 利用了回滚段实现了多版本查询数据

InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic 和 Compressed 行格式
Compact 是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的行记录
Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，从 MySQL 5.7 版本之后，默认使用 Dynamic 行格式

Compact 行格式：一条完整的记录分为「记录的额外信息」和「记录的真实数据」两个部分

• 记录的额外信息
  变长字段长度列表：这些变长字段的真实数据占用字节数，会按照列的顺序逆序存放
  NULL 值列表：占用整数个字节，如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列（1 为 NULL）
  记录头信息：delete_mask 标识此条数据是否被删除、next_record 下一条记录的位置、record_type 当前记录的类型（0 普通|1 B+ Tree 非叶子节点|2 最小记录|3 最大记录）
• 记录的真实数据
  row_id：6 bytes，如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段
  trx_id：6 bytes，表示这个数据是由哪个事务生成的
  roll_pointer：7 bytes，这条记录上一个版本的指针

变长字段长度列表

这些 "变长字段的真实数据占用的字节数" 会按照列的顺序 "逆序存放"
记录头信息中的 next_record 为下一条记录的位置：记录与记录之间是通过链表组织的，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置
这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便
逆序存放：这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，提高 CPU Cache 的命中率
同样的道理， NULL 值列表的信息也需要逆序存放

varchar(n) 中 n 最大取值为多少

MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节
65535 = 2（变长字段长度列表）+ 1（NULL 值列表）+ 65532（数据）

CREATE TABLE test (
`name` VARCHAR(65532) DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

上面这个例子是针对字符集为 ascii 情况，如果采用的是 UTF-8，varchar(n) 最多能存储的数据计算方式就不一样了
在 UTF-8 字符集下，一个字符最多需要三个字节，varchar(n) 的 n 最大取值就是 65532/3 = 21844
上面所说的只是针对于一个字段的计算方式
如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL 值列表所占用的字节数 <= 65535

行溢出后，MySQL 是怎么处理的

MySQL 中磁盘和内存交互的基本单位是页，一个页的大小一般是 16KB，也就是 16384字节
一个 varchar(n) 类型的列最多可以存储 65532 字节，一些大对象如 TEXT、BLOB 可能存储更多的数据
一个页可能就存不了一条记录，这个时候就会发生行溢出，多的数据就会存到另外的「溢出页」中

在一般情况下，InnoDB 的数据都是存放在「数据页」中，但是当发生行溢出时，溢出的数据会存放到「溢出页」中

• 在「记录的真实数据」只会保存该行的一部分数据
• 把剩余的数据放在「溢出页」中，然后「记录的真实数据」用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页

上面这个是 Compact 行格式在发生行溢出后的处理，Compressed 和 Dynamic 这两个行格式和 Compact 非常类似，主要的区别在于处理行溢出数据时有些区别
这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该行的一部分数据，只存储 20 个字节的指针来指向溢出页，实际的数据都存储在溢出页中

3、索引

组合索引
索引常见面试题
MySQL 联合索引夜市

一些名词：主键索引 B+ 树、联合索引 B+ 树、索引覆盖、回表、联合索引最左匹配原则、索引下推、= 排序 范围

MySQL 索引失效

• 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效
• 当我们在查询条件中对索引列使用函数，就会导致索引失效
• 当我们在查询条件中对索引列进行表达式计算，也是无法走索引的
• MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较
  如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换
  由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效
• 联合索引要能正确使用，需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效
• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效

4、事务

4.1、Read View

Read View 有四个重要的字段

• creator_trx_id：创建该 Read View 的事务的事务 id
• m_ids：在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，注意是一个列表（活跃事务：启动了但还没提交的事务）
• min_trx_id：在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是 m_ids 的最小值
• max_trx_id：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1

隐藏列

假设在账户余额表插入一条小林余额为 100 万的记录

对于使用 InnoDB 存储引擎的数据库表，它的聚簇索引记录中都包含下面两个隐藏列

• trx_id：当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里
• roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中
  这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录

在创建 Read View 后，我们可以将记录中的 trx_id 划分这三种情况

一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况

• 如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值
  表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见
• 如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值
  表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见
• 如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中
  • 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见
  • 如果记录的 trx_id 不在 m_ids 列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见

这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）

4.2、可重复读

可重复读隔离级别：在事务期间读到的记录都是事务启动前的记录
可重复读隔离级别：启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View

假设事务 A（事务 id 为 51）启动后，紧接着事务 B（事务 id 为 52）也启动了，那这两个事务创建的 Read View 如下

在「可重复读」隔离级别下，事务 A 和事务 B 按顺序执行了以下操作

• 事务 B 读取小林的账户余额记录，读到余额是 100 万
• 事务 A 将小林的账户余额记录修改成 200 万，并没有提交事务
• 事务 B 读取小林的账户余额记录，读到余额还是 100 万
• 事务 A 提交事务
• 事务 B 读取小林的账户余额记录，读到余额依然还是 100 万

接下来跟大家具体分析下

事务 B 第一次读小林的账户余额记录，在找到记录后，它会先看这条记录的 trx_id，此时发现 trx_id 为 50，比事务 B 的 Read View 中的 min_trx_id 值（51）还小
这意味着修改这条记录的事务早就在事务 B 启动前提交过了，所以该版本的记录对事务 B 可见的，也就是事务 B 可以获取到这条记录

事务 A 通过 update 语句将这条记录修改了（还未提交事务），将小林的余额改成 200 万，这时 MySQL 会记录相应的 undo log，并以链表的方式串联起来，形成版本链
你可以在下图的「记录的字段」看到，由于事务 A 修改了该记录，以前的记录就变成旧版本记录了
于是最新记录和旧版本记录通过链表的方式串起来，而且最新记录的 trx_id 是事务 A 的事务 id（trx_id = 51）

然后事务 B 第二次去读取该记录，发现这条记录的 trx_id 值为 51
在事务 B 的 Read View 的 min_trx_id 和 max_trx_id 之间，则需要判断 trx_id 值是否在 m_ids 范围内
判断的结果是在的，那么说明这条记录是被还未提交的事务修改的，这时事务 B 并不会读取这个版本的记录
而是沿着 undo log 链条往下找旧版本的记录，直到找到 trx_id 「小于」事务 B 的 Read View 中的 min_trx_id 值的第一条记录
所以事务 B 能读取到的是 trx_id 为 50 的记录，也就是小林余额是 100 万的这条记录

当事物 A 提交事务后，由于隔离级别是「可重复读」，所以事务 B 再次读取记录时，还是基于启动事务时创建的 Read View 来判断当前版本的记录是否可见
所以即使事物 A 将小林余额修改为 200 万并提交了事务，事务 B 第三次读取记录时，读到的记录还是小林余额为 100 万的这条记录

4.3、读已提交

读提交隔离级别是在每次读取数据时，都会生成一个新的 Read View
事务期间多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间 "另外一个事务" 修改了该记录并提交了事务

假设事务 A（事务 id 为 51）启动后，紧接着事务 B（事务 id 为 52）也启动了，接着按顺序执行了以下操作

• 事务 B 读取数据（创建 Read View），小林的账户余额为 100 万
• 事务 A 修改数据（还没提交事务），将小林的账户余额从 100 万修改成了 200 万
• 事务 B 读取数据（创建 Read View），小林的账户余额为 100 万
• 事务 A 提交事务
• 事务 B 读取数据（创建 Read View），小林的账户余额为 200 万

前两次事务 B 读取数据时创建的 Read View 如下图

我们来分析下为什么事务 B 第二次读数据时，读不到事务 A （还未提交事务）修改的数据

事务 B 在找到小林这条记录时，会看这条记录的 trx_id 是 51
在事务 B 的 Read View 的 min_trx_id 和 max_trx_id 之间，接下来需要判断 trx_id 值是否在 m_ids 范围内
判断的结果是在的，那么说明这条记录是被还未提交的事务修改的，这时事务 B 并不会读取这个版本的记录
而是沿着 undo log 链条往下找旧版本的记录，直到找到 trx_id 「小于」事务 B 的 Read View 中的 min_trx_id 值的第一条记录
所以事务 B 能读取到的是 trx_id 为 50 的记录，也就是小林余额是 100 万的这条记录

为什么事务 A 提交后，事务 B 就可以读到事务 A 修改的数据

在事务 A 提交后，由于隔离级别是「读已提交」，所以事务 B 在每次读数据的时候，会重新创建 Read View，此时事务 B 第三次读取数据时创建的 Read View 如下

事务 B 在找到小林这条记录时，会发现这条记录的 trx_id 是 51，比事务 B 的 Read View 中的 min_trx_id 值（52）还小
这意味着修改这条记录的事务早就在创建 Read View 前提交过了，所以该版本的记录对事务 B 是可见的

4.4、幻读

对于幻读现象，不建议将隔离级别升级为串行化，因为这会导致数据库并发时性能很差
MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了），解决的方案有两种

• 针对快照读（普通 select 语句）：通过 MVCC 方式解决了幻读
  因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的
• 针对当前读（select ... for update 等语句）：通过 next-key lock（记录锁 + 间隙锁）方式解决了幻读
  因为当执行 select ... for update 语句的时候，会加上 next-key lock
  如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入
• MySQL 里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作

发生幻读现象的场景

T1 时刻：事务 A 先执行「快照读语句」select * from t_test where id > 100 得到了 3 条记录
T2 时刻：事务 B 往插入一个 id= 200 的记录并提交
T3 时刻：事务 A 再执行「当前读语句」select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select ... for update 这类「当前读」的语句
因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录

4.5、更多

可重复读如何解决不可重复读和幻读

MySQL 提供了两种查询方式

• 快照读：就是普通 select 语句
• 当前读：比如 select ... for update 语句

不同查询方式，解决问题的方式是不一样的

• 针对快照读的话：是通过 MVCC 机制来解决的
  在可重复读隔离级别下，第一次 select 查询的时候会生成 Read View，在第二次执行 select 查询的时候，会复用这个 Read View
  这样前后两次查询的记录都是一样的，不会读到其它事务更新 / 插入的操作，就不会发生不可重复读和幻读了
• 针对当前读的话：靠 "行级锁中的记录锁" 来解决不可重复读，靠 "行级锁中的间隙锁" 来解决幻读
  在可重复读隔离级别下，第一次 select ... for update 语句查询的时候，会对记录加 next-key 锁
  这个锁包含记录锁和间隙锁，这时候如果其它事务更新 / 插入了加了锁的记录，都会被阻塞住
  这样就不会发生不可重复读和幻读了