MySQL-存储引擎

零、本文纲要

• 一、MySQL存储引擎
• 二、InnoDB存储引擎
  1、InnoDB逻辑存储结构
  2、InnoDB架构
  3、InnoDB内存结构
  4、InnoDB磁盘结构

tips：Ctrl + F快速定位所需内容阅读吧。

一、MySQL存储引擎

1、查看当前版本支持的存储引擎

 show engines;

8.0.26版本，输出结果如下：

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

可以看到当前数据库默认使用的是InnoDB存储引擎，当然也可以建表的时候指定想要使用的存储引擎。

2、常见的存储引擎及特点

• ① InnoDB：支持事务、支持行锁/表锁、支持外键；
• ② MyISAM：不支持事务、支持表锁、支持全文索引(InnoDB5.6版本之后)；
• ③ Memory：不支持事务、支持表锁、支持Hash索引。

3、存储引擎的选择

• ① InnoDB

如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择；

• ② MyISAM

如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的；

• ③ MEMORY

将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。（大小受限、无法保障数据安全）

二、InnoDB存储引擎

InnoDB逻辑存储结构.png

1、InnoDB逻辑存储结构

• ① 表空间（Tablespace）

表空间是InnoDB存储引擎逻辑结构的最高层， 如果用户启用了参数 innodb_file_per_table(在8.0版本中默认开启) ，则每张表都会有一个表空间（xxx.ibd），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。

show variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

查看.ibd文件存储位置的命令：show global variables like '%datadir%';，位置为/var/lib/mysql/，如下：

 show global variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

test数据库的ibd文件.png

• ② 段（Segment）

段，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），段用来管理多个Extent（区）。
关于段举例：InnoDB是索引组织表，数据段就是B+树的叶子节点， 索引段即为B+树的非叶子节点。

• ③ 区（Extent）

区，表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。

• ④ 页（Page）

页，是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。

• ⑤ 行（Row）

行，InnoDB 存储引擎数据是按行进行存放的。

在行中，默认有两个隐藏字段：

• Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。
• Roll_pointer：每次对某条引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

2、InnoDB架构

官方文档查看位置：InnoDB Architecture。

InnoDB架构.png

3、InnoDB内存结构

内存结构包含：Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。

• ① Buffer Pool（缓冲池）

Ⅰ 缓冲池官方文档：Buffer Pool。

在专用服务器上，通常将多达80％的物理内存分配给缓冲池 。

 show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

在InnoDB的缓冲池中不仅缓存了索引页和数据页，还包含了undo页、插入缓存、自适应哈希索引以及InnoDB的锁信息等等。

缓冲池 Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：

• free page：空闲page，未被使用。
• clean page：被使用page，数据没有被修改过。
• dirty page：脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。

• ② Change Buffer（更改缓冲区）

Ⅰ 更改缓冲区官方文档：Change Buffer。

更改缓冲区是一种特殊的数据结构，当二级索引页不在缓冲池中时，它将缓存这些页的更改。由 INSERT、UPDATE DELETE （DML） 操作导致的缓冲更改先保存在更改缓冲区，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。

Change Buffer.png

与聚集索引不同，二级索引通常是非唯一的，并且插入到二级索引中按相对随机的顺序发生。同样，删除和更新可能会影响不在索引树中相邻的二级索引页。这些大量随机访问的 I/O会大大影响效率，在更改缓冲区中对这些更改做合并，以减少IO操作。

• ③ Adaptive Hash Index（自适应哈希索引）

Ⅰ 自适应哈希索引官方文档：Adaptive Hash Index。

hash索引在进行等值匹配时，一般性能是要高于B+树的。InnoDB存储引擎会监控对表上各索引页的查询情况，根据实际情况判断是否创建自适应哈希索引。自适应哈希索引，无需人工干预，是系统根据情况自动完成。

show variables like 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+

• ④ Log Buffer（日志缓冲区）

Ⅰ 日志缓冲区官方文档：Log Buffer。

日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log、undo log），默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。

查看缓冲区大小：

show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+

刷盘时机参数：

show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+

刷盘时机参数说明：

• 0: 每秒将日志写入并刷新到磁盘一次；
• 1: 日志在每次事务提交时写入并刷新到磁盘，默认值；
• 2: 日志在每次事务提交后写入，并每秒刷新到磁盘一次。

4、InnoDB磁盘结构

• ① The System Tablespace（系统表空间）

Ⅰ 系统表空间官方文档：The System Tablespace。

系统表空间是更改缓冲区的存储区域。如果表是在系统表空间中创建的，而不是在按表的文件或常规表空间中创建的，则它还可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数据字典、undolog等)。

show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+

系统表空间，默认的文件名： ibdata1。

• ② File-Per-Table Tablespaces（每个表的文件空间）

Ⅰ 每个表的文件空间官方文档：File-Per-Table Tablespaces。

每个表的文件表空间包含单个表的数据和索引，并存储在文件系统上的单个数据文件中(.ibd文件)。

show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

• ③ General Tablespaces（常规表空间）

Ⅰ 常规表空间官方文档：General Tablespaces。

语法：

CREATE TABLESPACE tablespace_name
    [ADD DATAFILE 'file_name']
    [FILE_BLOCK_SIZE = value]
        [ENGINE [=] engine_name]

在数据目录中创建常规表空间：

CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;

创建表：

CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;

• ④ Undo Tablespaces（撤消表空间）

Ⅰ 撤消表空间官方文档：Undo Tablespaces。

撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储undo log日志。

• ⑤ Temporary Tablespaces（临时表空间）

Ⅰ 临时表空间官方文档：Temporary Tablespaces。

InnoDB 使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。

• ⑥ Doublewrite Buffer Files（双写缓冲区）

Ⅰ 双写缓冲区官方文档：Doublewrite Buffer。

双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。

#ib_16384_0.dblwr
#ib_16384_1.dblwr

• ⑦ Redo Log（重做日志）

Ⅰ 重做日志官方文档：Redo Log。

重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘时，发生错误时，进行数据恢复使用。

#ib_logfile0
#ib_logfile1

三、结尾

以上即为MySQL-存储引擎的部分内容，感谢阅读。