mysql系列之InnoDB存储引擎的磁盘结构

前言

之前已经介绍了InnoDB整体的结构，并详细介绍了InnoDB内存结构的各个组成部分及其作用，今天继续分析InnoDB存储引擎的磁盘结构。

---

一、InnoDB的磁盘结构

InnoDB的磁盘结构主要由以下几部分组成：

1、表

2、索引

3、表空间

4、Doublewrite Buffer

5、Redo Log

6、Undo Logs

二、表空间

InnoDB采用将存储的数据按表空间（tablesspace）进行存放的设计。简单来说，表空间就是mysql中各类数据存放的目录，默认情况下表空间的目录就是数据目录。

InnoDB下有以下5种表空间：

1、系统表空间 The System Tablespace

系统表空间是存放change buffer的区域。
Change Buffer是缓存那些不在buffer pool里的辅助索引的变化的特殊数据结构 。
在磁盘上，Change Buffer是system tablespace（系统表空间）的一部分，当数据库宕机时，索引的变更会被缓冲到磁盘的Change Buffer区域。

如果表是在系统表空间中创建的，而不是单表单文件表空间或常规表空间，则系统表空间中还会保存表和索引的数据。

在早期的MySQL版本中，系统表空间包含InnoDB数据字典。 在MySQL 8.0中，InnoDB将元数据存储在MySQL数据字典中。
在早期的MySQL版本中，系统表空间还包含doublewrite缓冲存储区。 从MySQL 8.0.20开始，该区域驻留在单独的doublewrite文件中。

系统表空间可以有一个或多个数据文件，默认情况下，有一个单独的数据文件被创建在数据目录下，名称为：iddata1

说明：
MySQL8开始删除了原来的frm文件，并采用 Serialized Dictionary Information (SDI), SDI是MySQL8.0重新设计数据词典后引入的新产物,并开始已经统一使用InnoDB存储引擎来存储表的元数据信息。SDI信息源记录保存在ibd文件中。

2、单表单文件表空间

InnoDB默认情况下就启用了单表单文件表空间，每个表的数据和索引都会采用单独的文件进行保存。
是否启动 file-per-table表空间是由innodb_file_per_table属性来控制的。

示例：
配置文件设置：

[mysqld]
innodb_file_per_table=ON

命令行设置：

mysql> SET GLOBAL innodb_file_per_table=ON;

创建表test：

mysql> USE test;
mysql> CREATE TABLE t1 (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100)
) ENGINE = InnoDB;

shell> cd /path/to/mysql/data/test
shell> ls
t1.ibd

数据存储文件.idb文件会根据表明来命名（table_name.ibd）

单表单文件表空间的好处是：
1、在单表单文件的表空间下，删除或者清空表后，存储空间会立刻返回给操作系统。而在共享表空间下，表空间数据文件的大小不会缩小。

2、对共享表空间下的表进行修改操作时，会增加表空间的磁盘空间。该空间不会像单表单文件表空间那样释放回操作系统。

3、清空表（TRUNCATE TABLE）的操作性能更好。

4、File-per-table表空间数据文件可以创建在不同的存储设备，对于I/O优化,空间管理,或备份操作都更加方便。

5、可以通过复制File-per-table表空间的对应表的数据文件到其他mysql数据库实例的表空间下，实现表的导入迁移。

6、File-per-table表空间中创建的表支持与DYNAMIC和压缩行格式

7、File-per-table表空间可以节省故障恢复时间，提高数据损坏恢复的成功几率。

8、更快速的备份机制，不用中断其他表的正在使用的InnoDB表。

9、能够通过监视文件的大小来监视表的大小。

10、常见的LInux文件系统不允许并发写入单个文件（例如共享表空间）。File-per-table表空间提高了并发读写入性能。

11、共享表空间中的表的大小受到64TB表空间大小限制的限制。 相比之下，每个
每表文件表空间的大小限制为64TB，这为单个表的增长提供了足够的空间
在尺寸方面。

坏处：
1、可能存在空间浪费

2、fsync操作在每个表的多个数据文件上执行，而不是在单个共享上执行
表空间数据文件。 由于fsync操作是针对每个文件的，因此无法对多个表进行写操作
组合在一起，可能会导致fsync操作总数增加。

3、mysqld进程必须为每个表文件表空间保留一个打开的文件句柄，这可能会对性能有一定影响。

4、会产生更多的磁盘碎片，可能会影响drop table和表扫描的性能。

3、常规表空间

常规表空间是使用CREATE TABLESPACE语法创建的共享InnoDB表空间。

常规表空间和系统表空间类似，也是共享的表空间，一个文件能够存储多个表数据。
相比File-per-table表空间，常规表空间由于多表共享表空间，消耗的内存会更少一点，具有潜在的内存优势。（占用的磁盘空间会更小）

常规表空间提供了类似File-per-table表空间的多个数据文件和的存储管理功能。比如可以创建多个常规表空间，然后将表分散的创建在多个常规表空间中。

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;
mysql> DROP TABLE t1;
mysql> DROP TABLESPACE ts1;

4、Undo表空间

回滚表空间，用来保存回滚日志，即undo logs。
回滚表空间（undo tablespaces）的默认路径是mysql的数据存储路径，会在undo tablespaces下生成undo_001和undo002共2个文件。
可通过配置可以通过 innodb_undo_directory属性主动设定回滚表空间的位置。
在MySQL8.0.14后，可以通过 CREATE UNDO TABLESPACE主动创建回滚表空间。

CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'file_name.ibu';

回滚表空间的文件必须以".ibu"作为扩展后缀名。

删除回滚表空间：

ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;
DROP UNDO TABLESPACE tablespace_name;

4、临时表空间（Temporary Tablespaces）

InnoDB使用会话临时表空间和全局临时表空间。

4.1、会话临时表空间 Session Temporary Tablespaces
会话临时表空间存储用户创建的临时表和内部临时表的相关信息。
当InnoDB配置为磁盘内部的存储引擎时，由优化程序创建
临时表。比如大表的join查询时，可能就会自动创建临时表来辅助查询。

在 MySQL 8.0.13后，新增加了INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES表用来保存相关会话临时表的信息。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES;
+----+------------+----------------------------+-------+----------+-----------+
| ID | SPACE | PATH | SIZE | STATE | PURPOSE |
+----+------------+----------------------------+-------+----------+-----------+
| 8 | 4294566162 | ./#innodb_temp/temp_10.ibt | 81920 | ACTIVE | INTRINSIC |
| 8 | 4294566161 | ./#innodb_temp/temp_9.ibt | 98304 | ACTIVE | USER |
| 0 | 4294566153 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294566154 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294566155 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294566156 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294566157 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294566158 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294566159 | ./#innodb_temp/temp_7.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294566160 | ./#innodb_temp/temp_8.ibt | 81920 | INACTIVE | NONE |
+----+------------+----------------------------+-------+----------+-----------+

4.2 全局临时表空间 Global Temporary Tablespace
全局临时表空间（ibtmp1）存储对用户创建的临时表的修改操作的回滚段信息。
可以通过innodb_temp_data_file_path属性指定临时表空间的位置。

mysql> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend |
+------------------------------+
To check the size of global temporary tablespace

在my.conf中配置临时表空间：

[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500

通过INFORMATION_SCHEMA.FILES表查看元数据文件信息。

mysql> SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE
 AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES
 WHERE TABLESPACE_NAME = 'innodb_temporary'\G
*************************** 1. row ***************************
 FILE_NAME: ./ibtmp1
TABLESPACE_NAME: innodb_temporary
 ENGINE: InnoDB
 INITIAL_SIZE: 12582912
 TotalSizeBytes: 12582912
 DATA_FREE: 6291456
 MAXIMUM_SIZE: NULL

三、双写缓冲 Doublewrite Buffer

doublewrite缓冲区是一个存储区域，InnoDB在将页面写入InnoDB数据文件中的适当位置之前，会在其中写入从缓冲池中刷新的页面。
如果在页面写入过程中发生操作系统，存储子系统或mysqld进程崩溃，则InnoDB可以在崩溃恢复期间从doublewrite缓冲区中找到该页面的良好副本。

在MySQL 8.0.20之前，doublewrite缓冲区存储区位于InnoDB系统表空间中。 从MySQL 8.0.20开始，doublewrite缓冲区存储区位于doublewrite文件中。

从MySQL 8.0.20开始，默认会创建2个Doublewrite Buffer文件。

#ib_16384_0.dblwr
#ib_16384_1.dblwr

InnoDB存储引擎数据双写的流程说明：

正是由于Doublewrite Buffer机制，极大的保障了Innodb引擎的数据安全性。尽管出现了宕机坏页的情况，也可以从Doublewrite Buffer读取正常页来恢复。

为什么有了redo，还要Doublewrite Buffer机制?数据库双写的好处是什么？
Doublewrite Buffer机制主要是更大的保障了数据页的可靠性。**主要是解决部分写失效的问题。**比如16KB的页，只写了前面4KB，之后就发生宕机了，这种情况被称为部分写失效。
针对部分写失效的问题，redo重做日志也不能解决这个问题。

四、重做日志 Redo Log

重做日志是基于磁盘的数据结构，主要作用是在崩溃恢复期间用于纠正不完整事务写入的数据。 在正常操作期间，重做日志对更改表数据的请求进行编码记录，这些请求是由SQL语句或低级API调用引起的。 在初始化期间以及接受连接之前，会自动重播未完成意外关闭之前未完成更新数据文件的修改。
默认情况下，redo log会自动生成2个文件：

ib_logfile0
ib_logfile1

WAL（Write-Ahead Logging）机制
WAL 的全称是 Write-Ahead Logging，中文称预写式日志，是一种数据安全写入机制。就是先写日志，然后在写入磁盘，这样保证数据的安全性。Mysql中的Redo Log就是采用WAL机制。(这里的写日志由于是顺序写，所以不会成为性能瓶颈。)

WAL作用
　　Mysql中如果为了保证数据的持久性，在每提交一个事务就将日志刷新到磁盘上，这样效率就太低了，严重影响性能，所以就有了Write-Ahead 。
　　Write-Ahead工作机制：
　　　　先在内存中提交事务，然后写日志(在InnoDB中就是Redo Log，日志是为了防止宕机导致内存数据丢失)，然后再后台任务中把内存中的数据异步刷到磁盘。

五、回滚日志 Undo Log

回滚日志主要是为了支持事务回滚功能。

默认会生成2个回滚日志，保存在undo tablespaces，默认情况下就在数据目录下：

undo_001
undo_002

一个事务最多可以分配四个撤消日志，以下每种操作类型都可以分配一个：
1、对用户自定义表执行插入操作
2、对用户自定义表执行删除和更新操作
3、对用户自定义的临时表执行插入操作
4、对用户自定义的临时表执行删除和更新操作

补充说明一:全文索引

全文索引：

mysql> CREATE TABLE opening_lines (
 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
 opening_line TEXT(500),
2830
Indexes
 author VARCHAR(200),
 title VARCHAR(200),
 FULLTEXT idx (opening_line)
 ) ENGINE=InnoDB;
mysql> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_TABLES
 WHERE name LIKE 'test/%';
+----------+----------------------------------------------------+-------+
| table_id | name | space |
+----------+----------------------------------------------------+-------+
| 333 | test/fts_0000000000000147_00000000000001c9_index_1 | 289 |
| 334 | test/fts_0000000000000147_00000000000001c9_index_2 | 290 |
| 335 | test/fts_0000000000000147_00000000000001c9_index_3 | 291 |
| 336 | test/fts_0000000000000147_00000000000001c9_index_4 | 292 |
| 337 | test/fts_0000000000000147_00000000000001c9_index_5 | 293 |
| 338 | test/fts_0000000000000147_00000000000001c9_index_6 | 294 |
| 330 | test/fts_0000000000000147_being_deleted | 286 |
| 331 | test/fts_0000000000000147_being_deleted_cache | 287 |
| 332 | test/fts_0000000000000147_config | 288 |
| 328 | test/fts_0000000000000147_deleted | 284 |
| 329 | test/fts_0000000000000147_deleted_cache | 285 |
| 327 | test/opening_lines | 283 |
+----------+----------------------------------------------------+-------+

补充说明二：二进制日志(binlog)

binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。

binlog不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL执行过的所有语句。

二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

MySQL中的二进制日志主要有两个功能：数据恢复和数据复制。

数据恢复–MySQL本身具备数据备份和恢复功能。例如我们可以每天午夜12:00进行数据备份。但是，此类备份功能并不是对数据库的实时备份，如果数据库在下午17:00出现故障无法恢复，那么从前一天午夜12:00到当天下午17:00的数据库内容将丢失。通过二进制日志可以解决这个问题。可以通过前一天午夜12:00的数据库备份文件恢复数据库，然后使用二进制日志恢复从前一天午夜12:00到当天下午17:00的数据库内容。

数据复制–MySQL支持主从服务器间的数据复制功能，并通过该功能实现数据库的冗余机制以保证数据库的可用性和提高数据库的性能。MySQL正是通过主服务器的二进制日志来实现数据的传递。主服务器上的二进制日志内容会被发送到各个从服务器，并在每个从服务器上执行，从而保证了主从服务器之间数据的一致性。

在默认配置下，MySQL不记录二进制日志。可以通过设置参数–log-bin=[base_name]启用二进制日志功能

补充说明三：数据和回滚日志的逻辑存储结构

Page是Innodb存储的最基本结构，也是Innodb磁盘管理的最小单位，与数据库相关的所有内容都存储在Page结构里。**Page分为几种类型：数据页（B-Tree Node），Undo页（Undo Log Page），系统页（System Page），事务数据页（Transaction System Page）等；**每个数据页的大小为16kb，每个Page使用一个32位（一位表示的就是0或1）的int值来表示，正好对应Innodb最大64TB的存储容量(16kb * 2^32=64tib)

六、查看mysql数据目录下的文件

默认配置下，mysql8.0.11数据目录下相关数据文件说明：

七、通过sql语句查看表空间相关信息

-- 查看数据库版本
select VERSION();
 
-- 查看表空间数据文件信息
SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary';

-- 查看所有表空间信息
SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES;

-- 表空间的名称和路径
SELECT * FROM  INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF;

-- 查看所有Innodb引擎表
SELECT * from INFORMATION_SCHEMA.INNODB_TABLES;

-- 查看所有Innodb引擎下的临时表信息
SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO;

总结

1、InnoDB采用表空间（tablesspace）的形式管理数据存放。
2、InnoDB有5类表空间：系统表空间（System Tablespace），单表单文件表空间（File-Per-Table Tablespaces）、常规表空间（General Tablespaces）、回滚表空间（Undo Tablespaces）、临时表空间（Temporary Tablespaces）
3、InnoDB通过重做日志（redo log）和双写缓冲 （Doublewrite Buffer）保证了数据的安全性，在事务中断、数据库宕机、数据页出现部分写失效的问题情况下能正常恢复。
4、通过回滚日志（Undo log），可以支持事务回滚和多版本控制机制（MVCC）。
5、了解日志预习机制WAL（Write-Ahead Logging），顺序写，fsync，双写缓冲（Doublewrite Buffer）等数据库优化机制