MySQL中相关数据文件说明

原创

俗可耐

修改于 2020-06-19 13:31:07

1.5K0

修改于 2020-06-19 13:31:07

文章被收录于专栏：俗人笔记

概览

.frm文件
.ibd 文件
.MYD 文件
.MYI文件
db.opt
ib_buffer_pool
ib_logfileN
ibtmp1

.frm 文件

frm文件是磁盘上用来保存表结构的描述文件。

存储引擎无关

无论何种存储引擎（InnoDB、MyISAM、MEMORY...），只要创建了表结构就会在磁盘上创建一个frm文件，文件名为：表名.frm

-- 创建表
CREATE TABLE t_user(id INT(11) NOT NULL PRIMARY KEY, name VARCHAR(20)) ENGINE=InnoDB;

存储位置

frm文件保存在MySQL实例的data文件夹下的库名文件夹下（datadir/database_name）

可以通过下面的命令分别查看datadir和database_name的值：

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

mysql> select DATABASE();
+------------+
| DATABASE() |
+------------+
| test       |
+------------+

所以test.t_user表的frm文件路径为：/var/lib/mysql/test/t_user.frm

视图的frm文件

CREATE VIEW也会创建一个.frm文件，但是视图的frm文件和表的frm文件没有什么相似之处，视图的frm文件是纯文本的。文件位置和表结构的frm文件位置相同。

mysql> CREATE VIEW test.t_user_view AS SELECT * FROM t_user;

$ cat t_user_view.frm 
TYPE=VIEW
query=select `test`.`t_user`.`id` AS `id`,`test`.`t_user`.`name` AS `name` from `test`.`t_user`
md5=8d045c77bce380c0cb3ee18cdf1d4b87
updatable=1
algorithm=0
definer_user=root
definer_host=localhost
suid=2
with_check_option=0
timestamp=2020-06-19 05:21:00
create-version=1
source=SELECT * FROM t_user
client_cs_name=utf8
connection_cl_name=utf8_general_ci
view_body_utf8=select `test`.`t_user`.`id` AS `id`,`test`.`t_user`.`name` AS `name` from `test`.`t_user`

.ibd 文件

.ibd文件是InnoDB表的数据文件，最初InnoDB表的数据是保存在系统表空间中的，后来支持了每个表一个单独的文件存储，这个可以通过参数innodb_file_per_table来控制，MySQL5.6.6及以上版本默认是开启的。

优点

truncate或drop表的时候，独立表空间会回收磁盘空间，而如果表数据保存在系统表空间则没办法回收，只能被新的InnoDB文件使用。

表数据保存在系统表空间时，`ALTER TABLE`的复制表操作，会需要额外的磁盘空间，而且操作完之后额外的空间不会被释放。

TRUNCATE TABLE命令执行更快
可以指定每个表的存储位置

CREATE TABLE ... DATA DIRECTORY = absolute_path_to_directory

可以使用命令OPTIMIZE TABLE来回收表空间，减小ibd文件的大小
可以移动单独的InnoDB表，而不是整个库
可以拷贝单独的InnoDB表到另一个MySQL实例
支持compressed和dynamic行格式的特性

可能的缺点

同一个表的空间只能被同一个表的数据使用，可能会造成空间的浪费
要对每一个打开的表执行fsync命令
mysqld要维护每一个表文件的句柄
使用更多的文件描述符

如何将一个表从系统表空间切换为独立表空间

第一步：表创建的时候由于当时的设置innodb_file_per_table=off，导致创建在系统表空间

    mysql> SET GLOBAL innodb_file_per_table=1;
    mysql> ALTER TABLE table_name ENGINE=InnoDB;

第二步：如果是在CREATE TABLE或ALTER TABLE时指定TABLESPACE的方式导致创建在系统表空间，则变量innodb_file_per_table对此表无效，只能通过ALTER TABLE ... TABLESPACE的方式进行修改。

.MYD

MyISAM数据文件，文件名称为表名.MYD，文件位置在MySQL实例的data文件夹下的库名文件夹下。

.MYI

MyISAM索引文件，文件名称为表名.MYI，文件位置在MySQL实例的data文件夹下的库名文件夹下。

db.opt

文本文件，保存数据库的默认字符集编码以及排序规则

文件位置同.frm文件为：datadir/database_name

示例文件如下：

default-character-set=latin1
default-collation=latin1_swedish_ci

ib_buffer_pool

用于保存和恢复Buffer Pool 的状态，可以设置buffer pool的内容在服务器关闭的时候持久化到磁盘文件中，在启动时恢复到buffer pool中，文件的路径（默认在datadir中）和名字可以通过变量innodb_buffer_pool_filename来设置

mysql> show variables like 'innodb_buffer_pool_filename';
+-----------------------------+----------------+
| Variable_name               | Value          |
+-----------------------------+----------------+
| innodb_buffer_pool_filename | ib_buffer_pool |
+-----------------------------+----------------+

其余相关变量：

mysql> show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 268435456      |
+-------------------------------------+----------------+

ib_logfileN

redo log文件，用于异常崩溃恢复。默认是两个文件，文件名分别为ib_logfile0和ib_logfile1。redo log文件的总大小（innodb_log_file_size * innodb_log_files_in_group）一般建议配置为可以处理一个小时写操作的量，数值越大则通过checkpoint刷新的次数越少，就越能降低磁盘IO。

mysql> show variables like 'innodb_log%';
+-----------------------------+-----------+
| Variable_name               | Value     |
+-----------------------------+-----------+
| innodb_log_buffer_size      | 8388608   |
| innodb_log_checksums        | ON        |
| innodb_log_compressed_pages | ON        |
| innodb_log_file_size        | 268435456 |
| innodb_log_files_in_group   | 2         |
| innodb_log_group_home_dir   | .\        |
| innodb_log_write_ahead_size | 8192      |
+-----------------------------+-----------+

ibdata1

InnoDB系统表空间文件

InnoDB系统表空间包含了InnoDB的数据目录（metadata），doublewrite buffer，change buffer 以及 undo logs。还包含了指定创建在系统表空间的表数据和索引。

系统表空间文件默认存储的datadir目录下，默认文件名为ibdata1,可以通过参数innodb_data_file_path来设置，示例如下：

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

-- max和autoextend只能用在最后一个文件上
file_name:file_size:[:autoextend[:max:max_file_size]]

ibtmp1

临时表空间

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

数据库

sql

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

数据库

sql

登录后参与评论

0 条评论

热度