2、MySQL 原理一

1、SELECT 语句的执行

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

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

image

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 利用了回滚段实现了多版本查询数据

image

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，这条记录上一个版本的指针

image

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;

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

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

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

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

image

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

3、索引

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

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

MySQL 索引失效

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

4、事务

4.1、Read View

Read View 有四个重要的字段

image

  • 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 万的记录
image

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

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

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

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

  • 如果记录的 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 如下
image

在「可重复读」隔离级别下，事务 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）
image

然后事务 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 万的这条记录

image

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

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

事务 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，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作

发生幻读现象的场景

image

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 锁
    这个锁包含记录锁和间隙锁，这时候如果其它事务更新 / 插入了加了锁的记录，都会被阻塞住
    这样就不会发生不可重复读和幻读了
posted @ 2023-09-20 15:46  lidongdongdong~  阅读(25)  评论(0)  编辑  收藏  举报