🎸 MySQL存储引擎

1. 存储引擎

MySQL 存储引擎种类

查看 当前 MySQL 版本

查看 有哪些存储引擎

常见存储引擎分析 [结合官方解释]

存储引擎	InnoDB	MYISAM	MEMORY
存储位置	磁盘文件	磁盘文件	内存
支持事务	😃	🥶	🥶
支持崩溃异常后的安全恢复	😃	🥶	🥶
锁级别	行级	表级	-
支持锁级别外键	😃	🥶	🥶
索引数据结构	B+ & Hash	B+	B+ & Hash
B+ 树区别	叶子节点存放实际值	叶子节点存放数据磁盘地址，根据磁盘地址去索引文件中取出实际值	-
索引文件后缀	.myi	.myd	-
是否为默认存储引擎	😃	🥶	🥶

MYISAM 所有索引存储格式：

可以看出，所有的索引，数据项均为 物理地址，再通过物理地址找到实际各项数据值

---

2. InnoDB 架构

InnoDB 架构

1) Adaptive Hash Index [自适应 Hash 索引]

• 频繁被访问的数据， MySQL 会自动评估使用自适应索引是否值得，

• 如果观察到建立哈希索引可以提升速度，则建立

2) Buffer Pool [缓存池]

• Buffer Pool 是一个以页为基本单位的链表
• 作用
  • 起到缓存作用，提高查询速度，减少磁盘访问次数
• 存放哪些数据
  • 基于 LRU 淘汰算法管理数据
  • 将缓冲池分为两部分：
    • 老年代
      • 进入 Buffer Pool 的数据，首先进入老年代
    • 新生代
      • 老年代数据被访问，才会升入新生代 --> 防止了预读失效[OS 内核会把相邻的页也加载进来，这些页如果未被使用]的页被访问
      • 老年代停留时间超过配置阈值，才可以升入新生代 --> 解决缓冲池污染[批量扫描大量数据，将 Buffer Pool 中所有页均被替换出去，导致热数据被换出，性能下降]

3) Change Buffer

• 作用对象：
  • 二级索引[ = 辅助索引 = 除了主键索引以外的所有索引]
  • 主键索引需要保证唯一性，每次 DML 操作均需要加载并判断是否违反唯一性，例如更新为某个新值时，需要判断该值是否已经存在
• 作用：
  • 当需要更新一个数据页时，如果数据页在内存中就直接更新
  • 若数据页还不在内存中，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，不需要特意从磁盘中读入这个数据页
  • 在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中之前缓存的更新操作
• 发生时刻：
  • DML ： 插入 | 更新 | 删除
• merge：
  • 将 change buffer 中的操作应用到原数据页，得到最新结果
  • 发生时刻：
    • 访问到相关数据页时会触发 merge
    • 系统后台线程定期 merge
    • 数据库正常关闭的过程中，也会执行 merge 操作
• 优点：
  • 将更新操作先记录在 change buffer，减少读磁盘的次数，提高语句的执行速度
  • 数据读入内存是需要占用 buffer pool 空间，此操作能够避免占用内存，提高内存利用率
• 适用场景：
  • 对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer的使用效果最好
  • 这种业务模型常见的就是账单类、日志类的系统
  • 若一个操作写入之后马上会做查询，此时写入 change buffer，然后立即读数据页，立即触发 merge，此时读磁盘的次数并不会减少，而且还增加了 buffer pool 的维护代价

唯一索引 VS 普通索引 在 Change Buffer 中的角色

• 唯一索引：
  • 所有的更新操作都要先判断这个操作是否违反唯一性约束
  • 要判断表中是否存在这个数据，而这必须要将数据页读入内存才能判断
  • 那么此时都已经读入到内存了，直接更新内存会更快，就没必要使用 change buffer了
  • 唯一索引的更新就不能使用 change buffer，也只有普通索引可以使用

查看 Change Buffer 占比

4) Log Buffer

• 记录要被刷到磁盘里的日志文件数据

5) Operating System Cache

• 操作系统的缓存

6) 表空间

• The System Tablespace

• File-Per-Table Tablespace

• General Tablespace

• Undo Tablespace

• Temporary Tablespace

7) Doublewrite Buffer

• 保证数据页的可靠性
• MySQL 在刷数据到磁盘之前，要先把数据写到 Doublewrite Buffer，
• 写完后，再开始写磁盘
• Doublewrite Buffer 可以理解为是一个备份（recovery），
• 万一真的发生 crash，就可以利用 Doublewrite Buffer 来修复磁盘里的数据。

---

3. InnoDB 逻辑结构

1) 表空间/Tablespace

1. 表空间是InnoDB存储引擎中逻辑结构的最高层，所有数据逻辑上都存储在表空间中
2. 类型：

• The System Tablespace
  • DoubleWrite Buffer
  • Change Buffer
  • InnoDB 相关所有对象的元数据等
• File-Per-Table Tablespace
  • 每张表对应一个独立的表空间，每个表都是一个 .ibd 文件
  • 5.6.6之后此配置默认开启
• General Tablespace
  • 通过create tablespace 表空间名 手动创建的表空间
• Temporary Tablespace
  • 存储临时表以及临时表变化对应的回滚段

2) 区/簇/Extent

1. 一个区/簇是物理上连续分配的一段空间，Extent 上有多个连续的页，以存储同一逻辑单元的数据，例：索引段， 数据段
2. 一个 Extent 默认 1MB，含有 64 页 16KB 的页

• 

3) 段/Segment

1. 逻辑上有关联的 区/簇 归属为一个段
2. InnoDB 中把这些记录具有相关性区的存储空间状态的管理信息称为段实体，段实体所管理的区的总和称为段

4) 页/Page

1. InnoDB 中 IO 操作的最小单位
2. 组成部分：
   • 页头
     • 指明当前页号、类型和所属表空间
   • 页身
     • 存储数据的主要部分
     • 2 部分：
       • 表空间首页头信息区
         • 表、段、区簇相关信息
       • 业务数据区

• 页尾
  • 主要用于数据的校验

3. 表空间中的 0 号文件的 0 号 page 页中存储表空间信息以及当前表空间所拥有的段链表的指针

---

4. InnoDB 行格式

默认行格式为 dynamic 格式，是单向链表结构

自增 ID 用完后？

• 若设置了主键，则会报错主键冲突
• 若未设置主键，采用默认自增主键 row_id，则新数据会覆盖老数据