Mysql 的主要目录结构
find / -name mysql
数据库文件存放路径
MySQL数据库文件的存放路径：/var/lib/mysql/
show variables like 'datadir'
进入/var/lib/mysql 进行查看, 发现我们所建立的库竟然是一个文件夹
进入dmeo文件夹, 发现了我们所建立的表t1 和 t2
发现使用InnoDB的t1表, 表现形式是idb结尾独立文件, 索引和数据存储一体化的二进制文件
使用MyISAM的t2表, 表象形式是.sdi, .MYD,MYI结尾的三个文件
t2_361.sdi: 表结构元数据定义信息,使用JSON格式保存表的定义。
t2.MYD: 表数据,包含表中的实际行记录数据。
t2.MYI: 表索引,包含表的索引定义和数据。
由于t1.ibd 是二进制命令, 所以无法直接使用cat命令查看, 借助ibd2sdi进行查看
ibd2sdi命令在MySQL 8.0中被引入,用于将InnoDB表空间文件转换为Serialized Dictionary Information(SDI)文件。
ibd2sdi的作用是:
- 输入一个InnoDB表空间文件(.ibd文件)
- 输出转换后的SDI文件
ibd2sdi t1.ibd
["ibd2sdi"
,
{
"type": 1,
"id": 360,
"object":
{
"mysqld_version_id": 80025,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Table",
"dd_object": {
"name": "t1",
"mysql_version_id": 80025,
"created": 20230813140124,
"last_altered": 20230813140124,
"hidden": 1,
"options": "avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;",
"columns": [
{
"name": "id",
"type": 4,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": true,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 1,
"char_length": 11,
"numeric_precision": 10,
"numeric_scale": 0,
"numeric_scale_null": false,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": false,
"srs_id_null": true,
"srs_id": 0,
"default_value": "AAAAAA==",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "table_id=1061;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 2,
"column_type_utf8": "int",
"elements": [],
"collation_id": 33,
"is_explicit_collation": false
},
{
"name": "name",
"type": 16,
"is_nullable": true,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 2,
"char_length": 60,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "table_id=1061;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "varchar(20)",
"elements": [],
"collation_id": 33,
"is_explicit_collation": false
},
{
"name": "DB_TRX_ID",
"type": 10,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 2,
"ordinal_position": 3,
"char_length": 6,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "",
"se_private_data": "table_id=1061;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
},
{
"name": "DB_ROLL_PTR",
"type": 9,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 2,
"ordinal_position": 4,
"char_length": 7,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "",
"se_private_data": "table_id=1061;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
}
],
"schema_ref": "demo",
"se_private_id": 1061,
"engine": "InnoDB",
"last_checked_for_upgrade_version_id": 0,
"comment": "",
"se_private_data": "autoinc=0;version=0;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"row_format": 2,
"partition_type": 0,
"partition_expression": "",
"partition_expression_utf8": "",
"default_partitioning": 0,
"subpartition_type": 0,
"subpartition_expression": "",
"subpartition_expression_utf8": "",
"default_subpartitioning": 0,
"indexes": [
{
"name": "PRIMARY",
"hidden": false,
"is_generated": false,
"ordinal_position": 1,
"comment": "",
"options": "flags=0;",
"se_private_data": "id=150;root=4;space_id=2;table_id=1061;trx_id=2058;",
"type": 1,
"algorithm": 2,
"is_algorithm_explicit": false,
"is_visible": true,
"engine": "InnoDB", # 索引的存储引擎
"engine_attribute": "",
"secondary_engine_attribute": "",
"elements": [
{
"ordinal_position": 1,
"length": 4,
"order": 2,
"hidden": false,
"column_opx": 0
},
{
"ordinal_position": 2,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 2
},
{
"ordinal_position": 3,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 3
},
{
"ordinal_position": 4,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 1
}
],
"tablespace_ref": "demo/t1"
}
],
"foreign_keys": [],
"check_constraints": [],
"partitions": [],
"collation_id": 33
}
}
}
,
{
"type": 2,
"id": 7,
"object":
{
"mysqld_version_id": 80025,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Tablespace",
"dd_object": {
"name": "demo/t1",
"comment": "",
"options": "autoextend_size=0;encryption=N;",
"se_private_data": "flags=16417;id=2;server_version=80025;space_version=1;state=normal;",
"engine": "InnoDB",
"engine_attribute": "",
"files": [
{
"ordinal_position": 1,
"filename": "./demo/t1.ibd",
"se_private_data": "id=2;"
}
]
}
}
}
]
查看t2_361.sdi文件, sdi是一个的一个表描述信息(table definition)文件, 也就是SDI(Serialized Dictionary Information)文件。
内容是JSON格式,包含了表t2的详细定义信息
cat t2_361.sdi
含义解析
{
# mysqld_version_id: 生成这个SDI文件的MySQL服务器版本号
"mysqld_version_id": 80025,
# dd_version: 数据字典版本号
"dd_version": 80023,
# sdi_version: SDI格式版本号
"sdi_version": 80019,
# 对象类型,此处是"Table"
"dd_object_type": "Table",
# dd_object: 表对象具体定义
"dd_object": {
"name": "t2", # 表名
"mysql_version_id": 80025, # 对应的MySQL版本号
"created": 20230813140131, # 表的创建时间(时间戳格式)
"last_altered": 20230813140131, # 表最后一次修改时间(时间戳格式)
"hidden": 1, # 是否是隐藏表
"options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;", # 表选项
# 列定义信息
"columns": [
{
"name": "id", # 类名
"type": 4, # 列数据类型
"is_nullable": false, # 是否允许为空
"is_zerofill": false, # 是否使用0进行填充
"is_unsigned": false, # 是否为无符号类型
"is_auto_increment": true, # 是否自动增长
"is_virtual": false, # 是否为虚拟生成列
"hidden": 1, # 是否是隐藏列
"ordinal_position": 1, # 列的顺序位置
"char_length": 11, # 字符串类型列的最大长度
"numeric_precision": 10, # 数值类型列的精度
"numeric_scale": 0, # 数值类型列的标度
"numeric_scale_null": false, # 数值列标度是否可为空
"datetime_precision": 0, # 数值列标度是否可为空
"datetime_precision_null": 1, # 日期时间精度是否可为空
"has_no_default": false, # 是否没有默认值
"default_value_null": false, # 默认值是否为空
"srs_id_null": true, # 空间引用系统ID是否为空
"srs_id": 0, # 空间引用系统ID
"default_value": "AAAAAA==", # 默认值的utf8编码
"default_value_utf8_null": true, # 默认值的utf8编码是否为空
"default_value_utf8": "", # 默认值的utf8编码
"default_option": "", # 默认值的可选表达式,用于代替默认值本身
"update_option": "", # UPDATE时默认值的表达式
"comment": "", # 列的注释信息
"generation_expression": "", # 生成(虚拟)列的表达式
"generation_expression_utf8": "", # 生成(虚拟)列的表达式
"options": "interval_count=0;", # 特定引擎的列选项
"se_private_data": "", # 存储引擎的私有数据
"engine_attribute": "", # 存储引擎的私有数据
"secondary_engine_attribute": "", # 第二存储引擎(如果是联合表)的属性
"column_key": 2, # 列键类型,比如无键,主键等
"column_type_utf8": "int", # 列类型的UTF8编码
"elements": [], # 数组类型列的元素定义,对于非数组列为空数组
"collation_id": 33, # 列使用的校对规则id
"is_explicit_collation": false # 校对规则是否明确指定
},
{
"name": "name",
"type": 16,
"is_nullable": true,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 2,
"char_length": 60,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "varchar(20)",
"elements": [],
"collation_id": 33,
"is_explicit_collation": false
}
],
# 表信息
"schema_ref": "demo", # 表所属的数据库schema
"se_private_id": 18446744073709551615, # 存储引擎的私有ID
"engine": "MyISAM", # 表的存储引擎
"last_checked_for_upgrade_version_id": 0, # 上次检查升级版本的版本ID
"comment": "", # 表的注释信息
"se_private_data": "", # 存储引擎的私有数据
"engine_attribute": "", # 存储引擎的属性
"secondary_engine_attribute": "", # 第二存储引擎(联合表)的属性
"row_format": 2, # 表的行存储格式
"partition_type": 0, # 分区类型
"partition_expression": "", # 分区表达式
"partition_expression_utf8": "", # 分区表单时的编码
"default_partitioning": 0, # 默认分区数
"subpartition_type": 0, # 子分区类型
"subpartition_expression": "", # 子分区表达式
"subpartition_expression_utf8": "", # 子分区表达式的编码
"default_subpartitioning": 0, # 子分区默认分区数
# 索引定义
"indexes": [
{
"name": "PRIMARY", # 索引名
"hidden": false, # 是否隐藏索引
"is_generated": false, # 是否由列生成
"ordinal_position": 1, # 索引顺序位置
"comment": "", # 索引的注释信息
"options": "flags=0;", # 索引选项
"se_private_data": "", # 存储引擎私有数据
"type": 1, # 存储引擎私有数据
"algorithm": 2, # 索引算法
"is_algorithm_explicit": false, # 算法类型是否Explicit指定
"is_visible": true, # 算法类型是否Explicit指定
"engine": "MyISAM", # 算法类型是否Explicit指定
"engine_attribute": "", # 存储引擎属性
"secondary_engine_attribute": "", # 第二存储引擎属性
# 索引列
"elements": [
{
"ordinal_position": 1, # 索引元素的顺序位置
"length": 4, # 索引长度
"order": 2, # 索引顺序(升序/降序)
"hidden": false, # 索引顺序(升序/降序)
"column_opx": 0 # 索引顺序(升序/降序)
}
]
}
],
"foreign_keys": [], # 外键约束定义
"check_constraints": [], # 表上的CHECK约束定义
"partitions": [], # 分区定义
"collation_id": 33 # 表默认使用的校对规则ID
}
}
配置数据库文件的存储的目录
配置文件目录：/usr/share/mysql-8.0（命令及配置文件），/etc/mysql（如my.cnf）
数据库 和 文件系统的关系
查看默认的数据库
查看默认的数据库
show databases;
可以看到有4个数据库是属于MySQL自带的系统数据库。
-
mysql
- MySQL 系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。
-
information_schema
- MySQL 系统自带的数据库，这个数据库保存着MySQL服务器 维护的所有其他数据库的信息 ，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为 元数据 。在系统数据库 information_schema 中提供了一些以innodb_sys 开头的表，用于表示内部系统表。
-
performance_schema
- MySQL 系统自带的数据库，这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，可以用来 监控 MySQL 服务的各类性能指标 。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息。
-
sys
- MySQL 系统自带的数据库，这个数据库主要是通过 视图 的形式把 information_schema 和performance_schema 结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能。
表在文件系统中的表示
InnoDB存储引擎模式
表结构
为了保存表结构，InnoDB 在 数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件 ，文件名是这样
表名.frm
.frm文件的格式在不同的平台上都是相同的。这个后缀名为.frm是以 二进制格式 存储的，我们直接打开是乱码的
表中数据和索引
系统表空间（system tablespace）
默认情况下，InnoDB会在数据目录下创建一个名为 ibdata1 、大小为 12M 的文件，这个文件就是对应的 系统表空间 在文件系统上的表示。
怎么才12M？注意这个文件是 自扩展文件 ，当不够用的时候它会自己增加文件大小
下述命令可以查看是否使用的系统表, 默认不使用系统表, 而是使用下述将要讲到的独立表
show variables like 'innodb_file_per_table';
独立表空间(file-per-table tablespace)
在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为 每一个表建立一个独立表空间 ，也就是说我们创建了多少个表，就有多少个独立表空间。使用 独立表空间 来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同，只不过添加了一个 .ibd 的扩展名而已，所以完整的文件名称长这样：
表名.idb
例如, 创建了一个dbtest的数据库, 在该数据库创建一个t_emp表(默认就是用Innodb引擎), 新增两条数据
create database dbtest;
use dbtest;
create table t_emp(id int, name varchar(20));
insert into t_emp(id, name) values (1, 'zhang3');
insert into t_emp(id, name) values (2, 'li4');
我们所建立的数据库在Linux的表现形式为文件夹
进入dbtest目录中,查看文件, Innobd引擎中的文件在表在Linux中的表现形式就是一个以表名.ibd结尾的文件
系统表空间和独立表空间的设置
我们可以自己指定使用 系统表空间 还是 独立表空间 来存储数据这个功能由启动参数innodb_file_per_table 控制，比如说我们想刻意将表数据都存储到 系统表空间 时，可以在启动MySQL服务器的时候这样配置：
[server]
# 0：代表使用系统表空间； 1：代表使用独立表空间
innodb_file_per_table=0
配置使用系统的表空间
重新启动MySQL
systemctl restart mysqld
创建了一个dbtest2的数据库, 在该数据库创建一个t_emp表(默认就是用Innodb引擎), 新增两条数据
create database dbtest2;
use dbtest2;
create table t_emp(id int, name varchar(20));
insert into t_emp(id, name) values (1, 'zhang3');
insert into t_emp(id, name) values (2, 'li4');
前往/var/lib/mysql, 发现ibtmp1, 打开发现全部都是乱码(这里就不截图了)
其它类型的表空间
随着MySQL的发展，除了上述两种老牌表空间之外，现在还新提出了一些不同类型的表空间，比如通用
表空间（general tablespace）、临时表空间（temporary tablespace）等。
MyISAM 存储引擎模式
表结构
在存储表结构方面， MyISAM 和 InnoDB 一样，也是在 数据目录 下对应的数据库子目录下创建了一个专门用于描述表结构的文件：
表名.frm
表中的数据 和 索引
在MyISAM中的索引全部都是 二级索引 ，该存储引擎的 数据和索引是分开存放 的。所以在文件系统中也是使用不同的文件来存储数据文件和索引文件，同时表数据都存放在对应的数据库子目录下。假如 test表使用MyISAM存储引擎的话，那么在它所在数据库对应的 atguigu 目录下会为 test 表创建这三个文件：
test.frm 存储表结构
test.MYD 存储数据 (MYData)
test.MYI 存储索引 (MYIndex)
举例：创建一个 MyISAM 表，使用 ENGINE 选项显式指定引擎。因为 InnoDB 是默认引擎。
CREATE TABLE `student_myisam`(
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
`age` int DEFAULT NULL,
`sex` varchar(2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = MYISAM
AUTO_INCREMENT = 0
DEFAULT CHARSET = utf8mb3;
进入/var/lib/mysql查看
数据库文件总结
-
如果表b采用 InnoDB ，data\a中会产生1个或者2个文件：
- b.frm ：描述表结构文件，字段长度等
- 如果采用 系统表空间 模式的，数据信息和索引信息都存储在 ibdata1 中
- 如果采用 独立表空间 存储模式，data\a中还会产生 b.ibd 文件（存储数据信息和索引信息）
- 补充说明
- MySQL5.7 中会在data/a的目录下生成 db.opt 文件用于保存数据库的相关配置。比如：字符集、比较规则。而MySQL8.0不再提供db.opt文件。
- MySQL8.0中不再单独提供b.frm，而是合并在b.ibd文件中。
-
如果表b采用 MyISAM ，data\a中会产生3个文件：
- MySQL5.7 中： b.frm ：描述表结构文件，字段长度等。
- MySQL8.0 中 b.xxx.sdi ：描述表结构文件，字段长度等
- b.MYD (MYData)：数据信息文件，存储数据信息(如果采用独立表存储模式)
- b.MYI (MYIndex)：存放索引信息文件
扫一扫
3085
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
