MySQL 的物理存储文件 mysql数据库物理存储结构

大家好，我是anyux。本文介绍MySQL存储引擎的物理结构与表空间。

InnoDB物理结构

在5.6版本中，ibdata1包含存储系统元信息，undo表空间数据和临时表空间

在5.7版本中，文件结构与作用如下

表空间

在5.7版本中下面两个参数长的很像，名字叫起来也相似，但完全不一样，需要重点牢记

ibdata1：整个库的统计信息和undo信息

ibd:数据行和索引信息

关于ibdata1是如何出现的，又是如何没落了。

虽然ibdata1和ibd是两个文件，但是在MySQL系统中将它们两个称为表空间(Tablespace)

现在我们使用磁盘，都是将一块300G的磁盘，格式化并且挂载到/data目录下，给MySQL服务使用。半年后，磁盘几乎被占满。此时又添加一块新的600G磁盘，格式化并且挂载到/data目录下，给MySQL服务使用。又半年后，又需要重新挂载磁盘，并且每次都需要停止数据库服务，那么有没有可能不重启也能实现数据在磁盘间的转移呢？

此时就使用到了表空间，表空间这个名词是来自于Oracle数据库。Oracle就是将数据库中的数据存储到表空间(Tablespace)，而在表空间内存，会分配数据存储到sdb1,sdc1等磁盘分区位置上，也就是说磁盘空间的增加对于数据库服务而言是透明的，是不可感知的

而对于MySQL到了5.5这个版本，已经被Oracle收购了。就将表空间的概念引入到MySQL中，MySQL也具备了表空间的管理模式。不足的是MySQL访问数据是基于文件系统去访问，表空间被迫改成了共享表空间，分别存储到各个磁盘下。

最初始时只要是个innodb引擎的表，都需要将数据行，索引，元信息和undo信息都存入到共享表空间，共享表空间再以不可见的形式存入到各大磁盘下，全都存入了ibdata1中。这样做的好处是可以实现类似Oracle的表空间管理模式，做一些扩容。问题是所有数据全部写入到几个有限的文件中，一旦数据多了，处理效率就会出现严重下降

所以到了5.6版本就改成了独立表空间模式，独立表空间解决了共享表空间数据"扎堆"的问题,解决方式是独立存储每一张表，每张表都自己的ibd,frm,见下图

需要知道现在共离表空间还在，只不在存储每张表的数据了，而是存储库的元信息和undo信息。而独立表空间存储数据行和索引信息

对于mariaDB和PerconaDB和5.5版本，需要更新，否则一旦数据量增长到一定量级，会导致性能下降

对于共享表空间(ibdata1~N)，自5.5版本以来，其负责的职责就被不同的程度的剥离

5.6版本以后，共享表空间得以保留，只用来存储：数字字典信息，undo信息，临时表

5.7版本以后，临时表被剥离

8.0版本以后，undo信息被剥离

查看共享表空间路径

select @@innodb_data_file_path;

下图表示，第一次启动时创建共享表空间ibdata1,初始大小为12M,空间不够时，会自动增长64M的空间

使用du -sh 命令查看文件大小

du -sh /data/mysql/data/ibdata1;

查看共享表空间默认每次自动增长值

show variables like 'innodb_data_file_path';show variables like '%extend%'

共享表空间参数的配置需要要创建数据库服务时配置，否则无法修改

独立表空间

从MySQL5.6版本开始，默认表空间已经变成了独立表空间，而不再是共享表空间

独立表空间主要用于存储用户数据

每一个表都有一个ibd文件，用于存储数据及索引信息

基本表结构元数据存储在frm文件

mysql数据表是由元数据，数据，索引构成

mysql数据库是由ibdata1~N和frm(元数据)，ibd文件(段区页)

一张InnoDB表由frm,ibd,ibdata1构成

查看独立表空间变量

select @@innodb_file_per_table;

如果将innodb_file_per_table设置为0，那么以后创建的数据表，就没有了独立表空间了

修改独立表空间变量

set global innodb_file_per_table=0;

不建议修改此变量，原因是改为0后，所以表将存储到共享表空间中，数据量增长后，容易导致数据库性能瓶颈

MySQL的存储引擎日志

Redo Log: ib_logfile0 ib_logfile1 重做日志

Undo Log: ibdata1 ibdata2(存储在共享表空间中)，回滚日志

临时表:ibtmp1,在做join union操作产生临时数据，用完就自动清理

表空间迁移

建立与原表一样的空表discard空表的ibd文件复制原表的ibd文件，修改权限，import原表的ibd文件查询验证alter table city dicard tablespace;alter table city import tablespace;