MySQL运维16-数据文件及表空间

1、数据文件分类

1.1、db.opt文件

db.opt：数据库的结构定义和设置（即库的元数据）

1.2、.frm文件

.frm文件是数据表的结构定义（即表的元数据），InnoDB和MyISAM都会使用frm文件来存储表结构，包括表的字段名称、数据类型、索引信息和其他属性。当我们创建一个MySQL表时，MySQL会自动创建一个与表名相同的.frm文件，用于存储该表的元数据。

1.3、.MYD文件

MyISAM表的表数据。

1.4、.MYI文件

MyISAM表的索引数据。

1.5、ibdata*问你件

InnoDB的系统表空间文件。

1.6、.idb文件

• 如果InnoDB表选择使用独立表空间或者通用表空间，就会用.idb文件存储表的数据和索引信息。
• .ibd文件包含了InnoDB表的行数据、索引数据、Undo日志、MVCC信息以及其他元数据信息。
• 每个.ibd文件都对应着一个InnoDB表空间，它可以独立于其他表空间进行备份、恢复和移动。
• .ibd文件的大小和表的大小息息相关，当表的数据量增加时，对应的.ibd文件大小也会增加。但表的大小和.ibd文件的大小也不是一一对应的，因为InnoDB存储引擎使用了一些高级数据结构和压缩技术来优化数据存储和查询性能。

1.7、.trg文件

触发器文件

2、系统表空间及ibdata文件

2.1、概述

1. 如果innodb_file_per_table等于0，那么InnoDB数据则会统一存放在一个系统表空间里。默认innodb_file_per_table为0，即InnoDB默认使用系统表空间的方式，所有的数据都会存储在类似ibdata*这样的文件里。如果将innodb_file_per_table设置为1，那么InnoDB数据表可以各自存储为一个文件，称为独立表空间。
2. 对于系统表空间，InnoDB将在数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。对于一般的生产负荷来说，这种配置太小了，可能会导致性能问题，所以需要手动设置大小。建议日志文件应大于256MB，数据文件初始分配1GB到5GB，并设置为自动扩展，设置示例如下：

innodb_data_file_path = ibdata1:1000M:autoextend
innodb_log_file_size = 256M

2.2、系统表空间数据文件的增加

2.2.1、数据文件增加的应用场景

1. 当前数据文件过大，比如上百GB，已不利于管理，这时你可能想要另外增加一个数据文件；
2. 磁盘已满，想把数据添加到另一个硬盘上，那么这时可以手动添加一个数据文件；

2.2.2、数据文件增加的步骤

1. 计算出最后一个数据文件文件的大小（针对按MB计算的大小取整，即字节数除以10242，再四舍五入）；
2. 修改配置文件，把innodb_data_file_path参数指定的最后一个文件大小设置为上面计算出来的值，并在其后继续追加新的数据文件；
3. 重启MySQL实例；

注意：

1. 当要添加一个新文件名到innodb_data_file_path参数指定的文件名列表时，请确认该文件在当前文件系统内并不存在。因为当重启实例时，InnoDB会创建并初始化这个文件。
2. 如果最后一个数据文件是用关键字autoextend定义的，那么在编辑my.cnf文件时必须考虑最后一个数据文件已经增长到多大了。你需要获取这个数据文件的大小，四舍五入，使其最接近1024*1024bytes的乘积（即1MB），然后在innodb_data_file_path中明确指定大致的尺寸。然后添加另一个数据文件。
3. 只有innodb_data_file_path里的最后一个数据文件才可以被指定为自动扩展。

2.2.3、数据文件增加的实例

1. 关闭数据库实例；
2. 找到最后一个数据文件，查看其文件大小。如下是Linux操作系统ll命令的输出。

-rw-rw---  1 mysql mysql 10829692928 Mar 10 10:27 ibdata4

3. 计算最后一个数据文件的大小：10829692928/1024/1024=10328 MB（四舍五入）
4. 原配置文件如下：

innodb_data_file_path = ibdata1:4000M;ibdata2:4000M;ibdata3:4000M;ibdata4:4000M:autoextend

5. 对配置文件做如下修改，增加一个数据文件ibdata5，初始值为8000MB，可自动扩展。

innodb_data_file_path = ibdata1:4000M;ibdata2:4000M;ibdata3:4000M;ibdata4:10328M;ibdata5:8000M:autoextend

6. 重启数据库实例，MySQL Server会自动创建ibdata5，并将最新数据写入该文件。

3、独立表空间

3.1、作用

• 对独立表空间，InnoDB会把每个新创建的表存储到数据库目录下的文件tbl_name.ibd中，类似于MyISAM存储引擎所做的，但MyISAM是把表分成数据文件tbl_name.MYD和索引文件tbl_name.MYI。对于InnoDB，数据和索引则会被一起存放到.ibd文件中。不过tbl_name.frm文件照旧会被创建，用于存放表结构。
• 系统表空间的使用很简单、维护方便，但缺点由于所有表在一起，所以其中大表的空间难以被回收，I/O操作可能因为并发导致资源等待等问题，所以推荐使用独立表空间

3.2、设置

• 可以向my.cnf的[mysqld]节中添加下面的语句来允许使用独立表空间，重启MySQL实例（MySQL Server）即可生效。

[mysqld]
innodb_file_per_table

• 如果从my.cnf文件里删除了innodb_file_per_table行，并重启了实例，那么之后新表创建时，InnoDB将会在系统表空间文件里创建新表，但并不影响已有表的使用，也就是说，innodb_file_per_table只会影响新表的创建。

3.3、使用独立表空间后系统表空间的意义

即使使用了独立表空间，也仍然有一部分共享数据需要存放在系统表空间内，所以idata*文件仍然存在。主要仍会保存如下数据：

• InnoDB数据字典：该字典包含了关于所有InnoDB表和索引的元数据信息，例如表结构、列定义、索引信息等。
• MVCC（多版本并发控制）数据：InnoDB使用MVCC来实现事务的隔离级别。系统表空间中保存了历史版本数据的一些元数据信息，例如事务ID、回滚指针等。
• 描述符信息：系统表空间中保存了InnoDB表空间的描述符信息，例如表空间ID、大小、文件名等。
• UNDO日志。