存储引擎

存储引擎负责将mysql的数据存储到物理介质中。执行器会根据优化器提供的执行计划，通过对存储引擎API的调用完成对数据的增删改查操作。

查看mysql的存储引擎

mysql> show engines\G
*************************** 1. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 4. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)

Support，当前MySQL是否支持该引擎，No代表不支持、Yes代表支持、Default代表默认使用该存储引擎
Transaction，是否支持事务
XA，是否支持分布式事务
Savepoints，是否支持事务的savepoint保存点

设置系统默认的存储引擎

查看系统使用的存储引擎：

mysql> show variables like '%storage_engine%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+

-- 或者
mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

使用命令：

SET DEFAULT_STORAGE_ENGINE=MyISAM;

修改配置文件（需要重启服务）：

default-storage-engine=MyISAM

设置表的存储引擎

存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为不同的表设置不同的存储引擎，也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式。

创建表时指定存储引擎

CREATE TABLE 表名 (
    建表语句;
) ENGINE = 存储引擎名称;

如果没有指定ENGINE，则会以系统默认的存储引擎作为此表的存储引擎：

mysql> show create table EMP;
...
| CREATE TABLE `EMP` (
  `EMPNO` int NOT NULL,
  `ENAME` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `JOB` varchar(9) COLLATE utf8_bin DEFAULT NULL,
  `MGR` int DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` double DEFAULT NULL,
  `COMM` double DEFAULT NULL,
  `DEPTNO` int DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin |
...
1 row in set (0.00 sec)

修改表的存储引擎

ALTER TABLE 表名 ENGINE = 存储引擎名称;

各种存储引擎

MyISAM

5.5版本钱默认使用的存储引擎。
不支持事务、不支持行级锁、不支持外键
针对数据统计有额外的常数存储，故 count(*)查询效率很高
访问速度快，主要适合以select和 insert为主的应用
MyISAM只缓存索引，不缓存真实数据

数据文件表示：

表名.frm 用于存储表结构
表名.MYD 用于存储数据 (MyData)
表名.MYI 用于存储索引数据 (MYIndex)

主要应用场景：

制度应用或者以读为主的业务员

InnoDB

5.5版本后默认使用的存储引擎。
支持事务，是默认的事务型引擎，它被设计用来处理大量的短期事务，可以确保事物的完整提交（Commit）和回滚（Rollback）
InnoDB是为处理巨大数据量的最大性能设计
相比较MyISAM，InnoDB写的效率差一些，还会占用更多的磁盘空间以保存数据与索引
InnoDB不仅缓存索引，还会缓存真实数据，对内存的要求较高，且内存大小对性能有决定性影响

数据文件表示：

表名.ifm 存储表结构（在8.0中被合并到.ibd文件中）
表名.ibd存储数据和索引

应用场景：

除非有非常特别的原因需要使用其他的存储引擎，否则应该有限考虑使用InnoDB
如果除了增加查询外，你的表数据还经常更新、删除，那么需要优先使用 InnoDB存储引擎

InnoDB VS MyASIM

CSV

数据文件表示：

表名.CSV：用于存储数据
表名.CSM：用于存储表状态和表中存在的行数

csv文件的示例：

"1","record one" 
"2","record two"

基本上没有什么功能

Memory

采用内存作为存储介质
- 响应速度非常快
- 如果mysqld崩溃会造成数据丢失
- 表大小受限制，防止内存不足，需要通过max_rows和 max_heap_table_size两个属性配置
不能使用可变长度数据类型，比如Blob和Text
索引支持
- Hash索引
- B+Tree索引
- 数据文件与索引文件分开存储

应用场景：

目标数据较小，且访问非常频繁
临时数据，且立即可用，不怕丢失

Blackhole

丢弃写操作，读操作会返回空内容

Federated

Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

Merge

管理多个MyISAM表构成的表集合

NDB

也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，类似于 Oracle 的 RAC 集群。

InnoDB 数据存储结构

tablespace，表空间
segment，段
extent，区
page，页
行，row

表空间（tablespace）

表空间（Tablespace）是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。

在 InnoDB 中存在两种表空间的类型：共享表空间和独立表空间。如果是共享表空间就意味着多张表共用一个表空间。如果是独立表空间，就意味着每张表有一个独立的表空间，也就是数据和索引信息都会保存在自己的表空间中。独立的表空间可以在不同的数据库之间进行迁移。可通过命令：

mysql > show variables like 'innodb_file_per_table';

查看当前系统启用的表空间类型。目前最新版本已经默认启用独立表空间。

InnoDB把数据保存在表空间内，表空间可以看作是InnoDB存储引擎逻辑结构的最高层。本质上是一个由一个或多个磁盘文件组成的虚拟文件系统。InnoDB用表空间并不只是存储表和索引，还保存了回滚段、双写缓冲区等。

共享/系统表空间(system tablespace)

默认情况下，InnoDB会在数据目录下创建一个名为 ibdata1 、大小为 12M 的文件，这个文件就是对应的系统表空间在文件系统上的表示。系统表空间会随着数据量的增大而自扩展。

可以通过配置文件修改系统表空间的存储方式：

[server] 
innodb_data_file_path=data1:512M;data2:512M:autoextend
# 以512M为分割，超过512M会自动分割第二个文件

独立表空间(file-per-table tablespace)

在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是根据不同的数据类型与不同的表，独立创建一个文件，用来存储相关的数据。

比如而是为每一个表建立一个独立表空间 ，也就是说我们创建了多少个表，就有多少个独立表空间。使用独立表空间来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同，只不过添加了一个 .ibd 的扩展名而已，所以完整的文件名称长这样：

表名.ibd

如果使用独立表空间存储数据库test的emp表的话，那么该标在数据库对应的test目录下会生成emp.frm和emp.ibd文件：

emp.frm
emp.ibd

其中emp.ibd文件就是用来存储emp表中的数据和索引。而frm文件则是InnoDB用来存储描述表结构（二进制方式）的文件。

系统表空间 vs 独立表空间

系统表空间会产生IO瓶颈,刷新数据的时候是顺序进行的所以会产生文件的IO瓶颈
独立表空间可以同时向多个文件刷新数据，降低了系统表空间的IO瓶颈

段（segment）

段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段。

段（Segment）由一个或多个区组成，区在文件系统是一个连续分配的空间（在 InnoDB 中是连续的 64 个页），不过在段中不要求区与区之间是相邻的。

常见的段有如下几种：

数据段

B+数的叶子结点

索引段

B+数的非叶子结点

回滚段

即rollback segment，管理undo log segment，记录的是undo日志的数据

区（extent）

在 InnoDB 存储引擎中，在任何情况下每个区大小都为1MB，为了保证页的连续性，InnoDB存储引擎每次从磁盘一次申请4-5个区。因为 InnoDB 中的页大小默认是 16KB， 64*16KB=1MB，即一个区中有64个连续的页。

页（page）

数据库的存储结构的最小单位。

索引结构提供了高效的索引方式，不过索引信息以及数据记录都是保存在文件上的，确切是存储在页(page)结构上的。另一方面，索引是在存储引擎中实现的，mysql服务器上的存储引擎负责对表中数据的读取和写入工作，不同存储引擎中存放的格式一般是不同的。

页之间使用双向链表关联。

磁盘与内存交互的基本单位

InnoDB 将数据划分为若干页，其中InnoDB中页的大小默认为16kb。

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

记录是按照行来存储的，但是数据库的读取并不以行为单位，否则一次读取只能处理一行数据，效率会很低。

为什么需要页？

磁盘的基础知识：

扇区，是指磁盘上划分的区域。
磁道按照512个字节为单位划分为等分，每个等分的弧锻叫做扇区。
磁盘驱动器在向磁盘读取和写入数据时，要以扇区为单位。

buffer io：

因为磁盘的读取数据单位只有512byte很小，所以索引数据时的全量索引会造成更多次数的读取操作，所以读取速度过慢
所以在操作系统读取磁盘时，会增加一个4k大小的buffer io，操作系统每读取一次io，都会至少读取4k的内容
这样就提高了硬盘的读取速度

数据库的data page页，与操作系统的buffer io也是同一个道理，都是为了减轻I/O成本。

查看当前mysql的page的大小

ysql> show variables like '%innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)

在SQLServer中，页的大小为8kb，而在Oracle中，页的概念成为Block块，它支持的块的大小有2kb，4kb，8kb，16kb，32kb，64kb等。

常见的页的类型

数据页（B-tree Node)
undo页（undo Log Page）
系统页（System Page）
事物数据页（Transaction System Page）
插入缓冲位图页（Insert Buffer Bitmap）
插入缓冲空闲列表页（Insert Buffer Free List）
未压缩的二进制大对象页（Uncompressed BLOB Page）
压缩的二进制大对象页（compressed BLOB Page）

页的内部结构

数据页的16kb大小的存储空间被划分为7个部分：

名称	占用大小	说明
File Header	38byte	文件头，描述页的信息
Page Header	56byte	页头，页状态信息
Infimum + Supremum	26byte	最大和最小记录，这是两个虚拟的行记录
User Records	不确定	用户记录，存储行记录的内容
Free Space	不确定	空闲记录，页中还没有被使用的空间
Page Directory	不确定	页目录，存储用户记录的相对位置
File Trailer	8byte	文件尾，校验页是否完整

名称

占用大小

说明

File Header

38byte

文件头，描述页的信息

Page Header

56byte

页头，页状态信息

Infimum + Supremum

26byte

最大和最小记录，这是两个虚拟的行记录