MySQL存储引擎

存储引擎的基本概念

为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL server的功能，把真实存取数据的功能划分为存储引擎的功能。所以在MySQL server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。
MySQL中提到了存储引擎的概念。简而言之，存储引擎就是指表的类型。其实存储引擎以前叫做表处理器，后来改名为存储引擎，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。

存储引擎的一些操作

  1. show engines; #查看MySQL都提供什么引擎
  3. #查看默认的存储引擎
  4. show variables like '%storage_engine%';
  5. #或
  6. SELECT @@default_storage_engine;
  8. #修改默认的存储引擎
  9. #如果在创建表的语句中没有显式指定表的存储引擎的话，那就会默认使用 InnoDB 作为表的存储引擎。
  10. #如果我们想改变表的默认存储引擎的话，可以这样写启动服务器的命令行：
  11. SET DEFAULT_STORAGE_ENGINE=MyISAM;
  13. #或者修改 my.cnf文件
  14. default-storage-engine=MyISAM
  15. # 重启服务
  16. systemctl restart mysqld.service
  19. #创建表时指定存储引擎
  20. #如果没有指定，那就会使用默认的存储引擎InnoDB
  21. CREATE TABLE 表名(
  22.  建表语句;
  23. ) ENGINE = 存储引擎名称;
  25. #修改表的引擎
  26. ALTER TABLE 表名 ENGINE = 存储引擎名称;
  28. #查看表的引擎(查看表结构)
  29. SHOW CREATE TABLE 表名

引擎介绍

参考博客https://achang.blog.csdn.net/article/details/122581631

MyISAM和InnoDB的区别

首先，对于InnoDB存储引擎，它提供了良好的事务管理、崩溃修复能力和并发控制。因为InnoDB存储引擎支持事务，所以对于要求事务完整性的场合需要选择InnoDB，比如数据操作除了插入和查询以外还包含有很多更新、删除操作，像财务系统等对数据准确性要求较高的系统。缺点是其读写效率稍差，占用的数据空间相对比较大。
其次，对于MyISAM存储引擎，如果是小型应用，系统以读操作和插入操作为主，只有很少的更新、删除操作，并且对事务的要求没有那么高，则可以选择这个存储引擎。MyISAM存储引擎的优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。
image.png
注：引擎也是可以自己研发的，比如阿里巴巴就不使用InnoDB，而是使用自己研发的引擎 Percona。
image.png

InnoDB行格式

基本简介

我们平时的数据以行为单位来向表中插入数据，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。InnoDB存储引擎设计了4种不同类型的行格式，分别是Compact、Redundant、Dynamic和Compressed行格式。默认是 Dynamic。

  1. CREATE TABLE 表名(
  2.   ...
  3. )ROW_FORMAT = 行格式名称
  5. ALTER TABLE 表名 ROW_FORMAT = 行格式名称

image.png

变长字段长度列表

把所有拥有可变长度字段的当前字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。注意：这里面存储的变长长度和字段顺序是反过来的。比如两个varchar字段在表结构的顺序是a(10)，b(15)，是真实长度。那么在变长字段长度列表中存储的长度顺序就是15，10，是反过来的。image.png
image.png

NULL值列表

image.png
为什么定义NULL值列表：
如果没有标注出来NULL值的位置，就有可能在查询数据的时候出现混乱。如果使用一个特定的符号放到相应的数据位表示空置的话，虽然能达到效果，但是这样很浪费空间，所以直接就在行数据得头部开辟出一块空间专门用来记录该行数据哪些是非空数据，哪些是空数据，格式如下:
1. 二进制位的值为1时，代表该列的值为NULL。
2. 二进制位的值为0时，代表该列的值不为NULL。
image.png 注意记录顺序也是反着来的，跟变长字段长度列表相同。

记录头信息

image.png
image.png
image.png

delete_mask

image.png

record_type

image.png

heap_no

image.png

记录的真实信息

image.png

行溢出与页扩展

image.png
image.png

Compressed和Dynamic

行格式都基本差不多，只不过在处理行溢出的方法上有区别。
image.png
image.png
image.png

Redundant行格式

image.png
image.png

InnoDB数据存储结构：页详解

基本介绍

lnnoDB将数据划分为若干个页，InnoDB中页的大小默认为16KB。以页作为磁盘和内存之间交互的基本单位，也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。
在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页(Page) ，数据库I/О操作的最小单位是页。一个页中可以存储多个行记录。

  1. show variables like '%innodb_page_size%'  #查看页的大小

页结构概述

页a、页b、页c…页n这些页可以不在物理结构上相连，只要通过双向链表相关联即可(但是为了方便查找，一般是一起放在区中，参考区的概念)。每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。
页如果按类型划分的话，常见的有数据页（保存B+树节点)、系统页、Undo页和事务数据页等。数据页是我们最常使用的页。
image.png
image.png

文件头(File Header)

image.png
校验和：
image.png
image.png

文件尾(File Trailer)

image.png

空闲空间(Free Space)

image.png
我们自己存储的记录会按照指定的行格式存储到User Records部分。但是在一开始生成页的时候，其实并没有User Records这个部分，每当我们插入一条记录，都会从FreeSpace部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到UserRecords部分，当FreeSpace部分的空间全部被UserRecords部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了。

用户记录(User Records)

UserRecords中的这些记录按照指定的行格式一条一条摆在UserRecords中，相互之间形成单链表。具体的用户记录格式，可以参考Compact行格式。

最大和最小记录(Infimum + Supremum)

由5字节大小的记录头信息和8字节大小的一个固定的部分组成的。
image.png 这两条记录不是我们自己定义的记录，所以它们并不存放在页的UserRecords部分，被单独放在一个称为 Infimum + Supremum的部分。
image.png

页目录(Page Directory)

image.png
image.png
image.png
image.png
image.png
在一个数据页中查找指定主键值的记录的过程分为两步：

  1. 通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录（通过上一个槽最大记录的next指针得来）。
  2. 通过遍历next_record属性得到指定的记录。

    页面头部(Page Header)

    PAGE_DIRECTION

    image.png

    PAGE_N_DIRECTION

    image.png

    页的上层结构

    image.png
    image.png

区、段、碎片区

为什么要有区

B+树的每一层中的页都会形成一个双向链表，如果是以页为单位来分配存储空间的话，双向链表相邻的两个页之间的物理位置可能离得非常远。我们介绍B+树索引的适用场景的时候特别提到范围查询只需要定位到最左边的记录和最右边的记录，然后沿着双向链表一直扫描就可以了，而如果链表中相邻的两个页物理位置离得非常远，就是所谓的随机I/O。再一次强调，磁盘的速度和内存的速度差了好几个数量级，随机I/0是非常慢的，所以我们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用所谓的顺序I/O。
引入区的概念，一个区就是在物理位置上连续的64个页。因为InnoDB中的页大小默认是16KB，所以一个区的大小是64*16KB=1MB。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区为单位分配，甚至在表中的数据特别多的时候，可以一次性分配多个连续的区。虽然可能造成一点点空间的浪费(数据不足以填充满整个区)，但是从性能角度看，可以消除很多的随机I/O。

为什么要有段

对于范围查询，其实是对B+树叶子节点中的记录进行顺序扫描，而如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请到的区中的话，进行范围扫描的效果就大打折扣了。所以InnoDB对B+树的叶子节点和非叶子节点进行了区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独有的区。存放叶子节点的区的集合就算是一个段 ( segment )，存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段。
除了索引的叶子节点段和非叶子节点段之外，InnoDB中还有为存储一些特殊的数据而定义的段，比如回滚段。所以，常见的段有数据段、索引段、回滚段。数据段即为B+树的叶子节点，索引段即为B+树的非叶子节点。
段其实不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念，由一些完整的区和若干个零散的页面(碎片区)组成。

为什么要有碎片区

默认情况下，一个使用InnoDB存储引擎的表只有一个聚簇索引，一个索引会生成2个段，而段是以区为单位申请存储空间的，一个区默认占用1M (64*16Kb=1024Kb）存储空间，所以默认情况下一个只存了几条记录的小表也需要2M的存储空间么？以后每次添加一个索引都要多申请2M的存储空间么？这对于存储记录比较少的表简直是天大的浪费。这个问题的症结在于到现在为止我们介绍的区都是非常纯粹的，也就是一个区被整个分配给某一个段，或者说区中的所有页面都是为了存储同一个段的数据而存在的，即使段的数据填不满区中所有的页面，那余下的页面也不能挪作他用。
为了考虑这种情况，InnoDB提出了一个碎片(fragment)区的概念。在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页用于段A，有些页用于段B，有些页甚至哪个段都不属于。碎片区直属于表空间，并不属于任何一个段。所以此后为某个段分配存储空间的策略是这样的：在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。当某个段已经占用了32个碎片区页面之后，就会申请以完整的区为单位来分配存储空间。因此段更精确的应该是某些零散的页面以及一些完整的区的集合。

区的分类

image.png

数据页加载的三种方式

如果缓冲池中没有需要的页数据，那么缓冲池有以下三种读取数据的方式：
image.png

  1. 随机读取

如果数据没有在内存中，就需要在磁盘上对该页进行查找，整体时间预估在10ms 左右，这10ms 中有6ms是磁盘的实际繁忙时间(包括了寻道和半圈旋转时间），有3ms是对可能发生的排队时间的估计值，另外还有1ms的传输时间，将页从磁盘服务器缓冲区传输到数据库缓冲区中。这10ms看起来很快，但实际上对于数据库来说消耗的时间已经非常长了，因为这只是一个页的读取时间。
image.png

  1. 顺序读取

顺序读取其实是一种批量读取的方式，因为我们请求的数据在磁盘上往往都是相邻存储的，顺序读取可以帮我们批量读取页面，这样的话，一次性加载到缓冲池中就不需要再对其他页面单独进行磁盘I/O操作了。如果一个磁盘的吞吐量是40MB/S，那么对于一个16KB大小的页来说，一次可以顺序读取2560 (40MB/16KB)个页，相当于一个页的读取时间为0.4ms。采用批量读取的方式，即使是从磁盘上进行读取，效率也比从内存中只单独读取一个页的效率要高。

表空间

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。表空间是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。
表空间数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间(System tablespace)、独立表空间(File-per-table tablespace)、撤销表空间(Undo Tablespace)和临时表空间(Temporary Tablespace)等。

独立表空间

image.png

系统表空间

在表内记录着一些元数据，这些表被称为数据字典。
image.png