MySQL 高级 - 第二章 | 数据库目录结构与文件系统

墨蓝 2024-05-06 23:21 62阅读 0赞

#### 目录 ####

*  前言
 *  一、数据库主要目录结构
 *   *  1.1 数据目录路径
     *  1.2 相关命令目录
     *  1.3 配置文件路径
 *  二、数据库和文件系统的关系
 *   *  2.1 默认数据库
     *  2.2 数据库在文件系统中的表示
     *  2.3 数据表在文件系统中的表示
     *   *  2.3.1 InnoDB 存储引擎模式
         *  2.3.2 MyISAM 存储引擎模式
     *  2.4 视图在文件系统中的表示
     *  2.5 其他的文件

--------------------

上篇：[第一章、字符集、大小写规范与sql\_mode的合理设置][sql_mode]  
下篇：[第三章、用户、权限与角色管理][Link 1]

## 前言 ##

以下内容以在 `linux` 上安装的 `mysql-8.0` 为例，讲述 `mysql` 的目录结构

本文内容主要源于：[bilibili-尚硅谷-MySQL高级篇][bilibili-_-MySQL]

--------------------

## 一、数据库主要目录结构 ##

### 1.1 数据目录路径 ###

`MySQL` 服务器程序在启动时会到文件系统的某个目录下加载一些文件，之后在运行过程中产生的数据也会存储到这个目录下的某些文件中，这个目录称为 `数据目录`。

在 `MySQL` 中 `数据目录` 有一个对应的系统变量 `datadir`，可以通过以下名称查询 `数据目录` 的路径：

show variables like 'datadir';

![在这里插入图片描述][cf93aa48a982488fbfac1d0c044be071.png]

可以进入到 `/var/lib/mysql` 目录下，并通过 `ll` 指令对该目录进行查看：

# 进入 /var/lib/mysql 目录
    cd /var/lib/mysql
    # 查看目录下的文件
    ll

![在这里插入图片描述][8ade5dd2f80d4a7d8c95d26490724372.png]

--------------------

### 1.2 相关命令目录 ###

在 `Linux` 上 `MySQL` 的相关指令主要存放在 `/usr/bin` 和 `/usr/sbin` 中

在 `/usr/bin` 下查看 `mysql` 的相关指令：

# 进入 /usr/bin 目录
    cd /usr/bin
    # 查看目录下的文件
    ls
    # 或者 查看目录下与 mysql 相关的文件
    find . -name "mysql*"

![在这里插入图片描述][559d606a35924f5d9e78a91051836534.png]

在 `/usr/sbin` 下查看 `mysql` 的相关指令：

# 进入 /usr/sbin 目录
    cd /usr/sbin
    # 查看目录下的文件
    ls
    # 或者 查看目录下与 mysql 相关的文件
    find . -name "mysql*"

![在这里插入图片描述][b71e397aade244de8209767cc7acb29f.png]

--------------------

### 1.3 配置文件路径 ###

在 `Linux` 上 `MySQL` 的配置文件主要存放在 `/usr/share/mysql-8.0`

在 `/usr/share/mysql-8.0` 下查看 `mysql` 的配置文件：

# 进入 /usr/share/mysql-8.0 目录
    cd /usr/share/mysql-8.0
    # 查看目录下的文件
    ls

![在这里插入图片描述][943fbd18a1224ecaa38f369151e9b5ad.png]

以及 `/etc` 下的 `my.cnf`

# 进入 /etc 目录
    cd /etc
    # 查看当前目录下名为 my.cnf 的文件
    find . -name my.cnf

![在这里插入图片描述][b01a4b2bf2134654a53560268e186e44.png]

--------------------

## 二、数据库和文件系统的关系 ##

像 `InnoDB`、`MyISAMM` 这样的存储引擎都是把表存储在磁盘上的，操作系统用来管理磁盘的结构被称为 `文件系统`，所以用专业一点的话来表述就是：像 `InnoDB`、`MyISAMM` 这样的存储引擎都是把 `表存储在文件系统上` 的。在读取数据的时候，这些存储引擎会从文件系统中把数据读出来返回；在写入数据的时候，这些存储引擎会把这些数据又写回文件系统。

--------------------

### 2.1 默认数据库 ###

在初始化完成之后，会自动创建 `4` 个默认的数据库

查看所拥有的数据库：

show databases;

示例：

各默认数据库的说明：

*  `mysql`
    
     *  `MySQL` 系统自带的核心数据库，它存储了 `MySQL` 的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。
 *  `information_schema`
    
     *  `MySQL` 系统自带的数据库，这个数据库保存着 `MySQL` 服务器 `维护的所有其他数据库的信息`，比如有表、视图、 触发器、列、索引等。这些信息并不是真实的用户数据，而是一些描述信息，有时候也成为 `元数据`。
 *  `performance_schema`
    
     *  `MySQL` 系统自带的数据库，这个数据库主要保存 `MySQL` 服务器运行过程中的一些状态信息，可以用来 `监控 MySQL 服务的各类性能指标`。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长事件，内存的使用情况等信息。
 *  `sys`
    
     *  `MySQL` 系统自带的数据库，这个数据库主要通过 `视图` 的形式把 `information_schema` 和 `performance_schema` 结合起来，帮助系统管理员和开发人员监控 `MySQL` 的技术性能。

--------------------

### 2.2 数据库在文件系统中的表示 ###

当使用 `CREATE DATABASE database_name` 命令创建数据库的时候，在 `数据目录` 下也会自动创建以 `database_name` 为名的文件夹

例如：当前 `MySQL` 中所存在的库

![在这里插入图片描述][e6bfd925ed6b49c9a00595209d87dad4.png]

数据目录 `/var/lib/mysql` 所存在的文件

![在这里插入图片描述][72ea9c0a04c5491097c812499984383e.png]

创建一个名为 `mike_test` 的数据库：

CREATE DATABASE mike_test;

![在这里插入图片描述][d33f30376d8341aa816bdb4218242212.png]

在数据目录 `/var/lib/mysql` 下就会有一个名为 `mike_test` 的文件夹被创建出来

![在这里插入图片描述][2c72d5b17c1a45649b678b8db1d4fd4d.png]

如果数据库是 `mysql-5.7` 及之前的版本，在所创建库同名的文件夹 `mike_test` 下，还会创建一个 `db.opt` 的文件，该文件主要是用于记录当前数据库的字符集与比较规则：  
示例：

[root@iZwz99hndp44mfccvavnzmZ mysql]# cd mike_test
    [root@iZwz99hndp44mfccvavnzmZ mike_test]# ls
    db.opt

而 `mysql-8.0` 创建库的时候并不会生产 `db.opt` 文件

--------------------

### 2.3 数据表在文件系统中的表示 ###

表中的数据都是以 `记录的形式` 插入到表中的，每个表的信息可以分为两种：

*  ① 表结构的定义
 *  ② 表中的数据

`表结构` 就是该表的名称，表里边有多少列，每个列的数据类型，约束条件和索引，使用的字符集和比较规则等各种信息，这些信息都体现在建表语句中。

--------------------

#### 2.3.1 InnoDB 存储引擎模式 ####

之前我创建了一个 `mike_test` 的数据库，接下来在该数据库中创建一张 `test_table_01` 的表，用的是 `InnoDB` 的存储引擎

CREATE TABLE `test_table_01`
    (
        `id`   int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
        `name` varchar(64) DEFAULT NULL COMMENT '名称',
        PRIMARY KEY (`id`)
    ) ENGINE = InnoDB
      AUTO_INCREMENT = 1
      DEFAULT CHARSET = utf8mb4 COMMENT ='测试表01';

![在这里插入图片描述][b2e0f651a31d4fe5a89d1122b6d11af4.png]

这里主要分为两种情况去考虑，一个是 `mysql-5.7`，另一个是 `mysql-8.0`

**情况一：mysql 版本为 5.7**

> 储备知识：
> 
>  *  InnoDB 其实是使用页为基本单位来管理存储空间的，默认的页大小为 16KB
>  *  对于 InnoDB 存储引擎来说，每个索引都对应着一棵 B+ 树，该 B+ 树的每个节点都是一个数据页，数据页之间不必要是物理连续的，因为数据页之间有双向链表来维护着这些页的顺序
>  *  InnoDB 的聚簇索引的叶子节点存储了完整的用户记录，也就是所谓的索引即数据，数据即索引
> 
> --------------------
> 
> 为了更好的管理这些页，`InnoDB` 提出了一个 `表空间` 或者 `文件空间`（英文名：`table space` 或者 `file space`）的概念，这个表空间是一个抽象的概念，它可以对应文件系统上一个或者多个真实文件（不同表空间对应的文件数量可能不同）。每一个 `表空间` 可以被划分为很多个 `页`，表数据就存放在某个表空间下的某些页里，这里的表空间有不同的类型
> 
>  *  ① 系统表空间（system table space）  
>     默认情况下，`InnoDB` 会在数据目录下创建一个名为 `ibdata1`、大小为 `12MB` 的文件，这个文件就是对应的系统表空间在文件系统上的表示，该文件是自扩展文件，当不够用的时候它会自己增加文件大小。当然，如果你想让系统表空间对应系统上多个实际文件，或者不想用 `ibdata1`。可以修改一下 `my.cnf` 配置文件，使 `MySQL` 启动时配置对应的文件路径以及它们的大小，比如：  
>       
>     \[server\]  
>     innodb\_data\_file\_path=data1:512M;data2:512M;autoextend  
>       
>     这样在 `MySQL` 启动之后就会创建这两个 `512MB` 大小的文件作为系统表空间，其中的 `autoextend` 表明这两个文件如果不够用会自动扩展 `data2` 文件的大小，需要注意的一点是，在 `一个 MySQL 服务器中，系统表空间只有一种`，从 `mysql-5.5.7` 到 `mysql-5.6.6` 之间的各个版本中，表中的数据都会被默认存储到这个系统表空间
>  *  ② 独立表空间（file-per-table table space）  
>     在 `mysql-5.6.6` 以及之后的版本中，`InnoDB` 并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间，也就是说创建了多少个表，就有多少个独立表空间，使用独立表空间来存储数据表的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同，只不过添加了一个 `.ibd`

如果使用的是 `5.7` 版本的 `mysql` 创建表，在该数控库同名的文件夹 `mike_test` 下，会生成 `.frm` 和 `.ibd` 两个文件（`db.opt` 文件在创建数据库时就会生成）

示例：

[root@iZwz99hndp44mfccvavnzmZ mysql]# cd mike_test
    [root@iZwz99hndp44mfccvavnzmZ mike_test]# ls
    db.opt
    test_table_01.frm
    test_table_01.ibd

`表名.frm` 是 `描述表结构的文件`，`.frm` 文件的格式在不同的平台上都是相同的，以 `二进制` 存储

`表名.ibd` 是一个 `独立表空间`，用于存放表的数据和索引

可以自己指定使用 `系统表空间` 还是 `独立表空间` 来存储数据，这个功能由启动参数 `innodb_file_per_table` 控制

![在这里插入图片描述][3c549d95f2d24b1d99abc8bab06bb67e.png]

如果需要将数据存储到 `系统表空间` 时，可以在启动 `MySQL` 服务器的时候配置：

[server]
    #0:代表使用系统表空间，1:代表使用独立表空间
    innodb_file_per_table=0

`innodb_file_per_table` 参数的修改只对新建的表起作用，对于以及分配了表空间的数据表则不起作用，如果我们想把已经存在系统表空间中的表转移到独立表空间，可以使用下边的语法：

ALTER TABLE 表名 TABLESPACE [=] innodb_file_per_table;

**情况二：mysql 版本为 8.0**

如果使用的是 `8.0` 版本的 `mysql` 创建表，在该数控库同名的文件夹 `mike_test` 下，只会生成 `.ibd` 一个文件

示例：

[root@iZwz99hndp44mfccvavnzmZ mysql]# cd mike_test
    [root@iZwz99hndp44mfccvavnzmZ mike_test]# ls
    test_table_01.ibd

对比 `mysql-5.7` 所创建的表可以发现少了用于记录当前数据库的字符集与比较规则 `db.opt` 和描述表结构的文件 `表名.frm`，只有一个独立表空间 `表名.ibd`，

`Oracle` 官方将 `frm` 文件的信息移动到序列化字典信息 （Serialized Dictionary information, SDI），`SDI` 就是表和表空间对象的序列化元数据，在 `mysql-8.0` 时 `SDI` 被写在 `.ibd` 文件内部

也就是说在 `mysql-8.0` 的时候，就把表的字符集、比较规则、表结构信息都存到了 `.ibd` 文件中

`Oracle` 提供了一个应用程序 `ibd2sdi`可以从 `.ibd` 文件中提取 `SDI` 信息

查看表结构：

ibd2sdi --dump-file=table_name.txt table_name.ibd

![在这里插入图片描述][615222378c1f432eb031d9229811a433.png]

打开该文件就能查看到表结构的详细信息

![在这里插入图片描述][161a8bd4c50444f1874976af74ed6045.png]

--------------------

#### 2.3.2 MyISAM 存储引擎模式 ####

之前在 `mike_test` 的数据库下创建一张 `test_table_01` 的表，用的是 `InnoDB` 的存储引擎，现在创建一张 `test_table_02` 的表，用的是 `MyIASM` 的存储引擎

CREATE TABLE `test_table_02`
    (
        `id`   int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
        `name` varchar(64) DEFAULT NULL COMMENT '名称',
        PRIMARY KEY (`id`)
    ) ENGINE = MYISAM
      AUTO_INCREMENT = 1
      DEFAULT CHARSET = utf8mb4 COMMENT ='测试表02';

![在这里插入图片描述][75c259d78bdb45c88b0c063f2692a1ef.png]

这里也分为两种情况去考虑，一个是 `mysql-5.7`，另一个是 `mysql-8.0`

**情况一：mysql 版本为 5.7**

如果使用的是 `5.7` 版本的 `mysql` 创建表，在该数控库同名的文件夹 `mike_test` 下，会生成 `.frm` 以及 `.MYD`、`.MYI` 三个文件

示例：

[root@iZwz99hndp44mfccvavnzmZ mysql]# cd mike_test
    [root@iZwz99hndp44mfccvavnzmZ mike_test]# ls
    db.opt
    test_table_01.frm
    test_table_01.ibd
    test_table_02.frm
    test_table_02.MYD
    test_table_02.MYI

`表名.frm` 是 `描述表结构的文件`

`表名.MYD` 是 `存储表数据的文件`，采用独立表存储模式

`表名.MYI` 是 `存储表索引的文件`，为该表创建的索引都会存放在该文件之中

在存储表结构上，`MyISAM` 和 `InnoDB` 一样，也是在 `数据目录` 下对应的数据库子目录下创建了一个专门用于描述表结构的 `.frm` 文件

由于 `MyISAM` 的索引都是 `二级索引`，该存储引擎的 `数据` 和 `索引` 是分开的，所以在文件系统中也是使用不同的文件来存储数据和索引

**情况二：mysql 版本为 8.0**

如果使用的是 `5.7` 版本的 `mysql` 创建表，在该数控库同名的文件夹 `mike_test` 下，会生成 `.sdi` 以及 `.MYD`、`.MYI` 三个文件

示例：

[root@iZwz99hndp44mfccvavnzmZ mysql]# cd mike_test
    [root@iZwz99hndp44mfccvavnzmZ mike_test]# ls
    test_table_01.ibd
    test_table_02_422.sdi
    test_table_02.MYD
    test_table_02.MYI

对比 `mysql-5.7` 所创建的表可以发现 `.frm` 被 `.sdi` 取代了，`.MYD` 和 `.MYI` 都存在

而 `SDI` 就是表和表空间对象的序列化元数据，相对于 `InnoDB` 将 `SDI` 写入 `.ibd` 中，`MyISAM` 则是写在了 `.sdi` 文件中

--------------------

### 2.4 视图在文件系统中的表示 ###

`MySQL` 中 `视图` 其实是 `虚拟的表`，也就是某个查询语句的一个别名而已，所以在存储视图的时候是不需要存储真实的数据的，只要把它的结构存储起来就行了，和表一样，描述视图结构的文件也会被存储到所属数据库对应的子目录下

比如我创建一个视图：

# 创建一个视图
    CREATE VIEW view_test_table_01 AS
    SELECT id, name
    FROM test_table_01;

![在这里插入图片描述][8e5595794858491994ac26f08689ca52.png]

如果 `mysql` 用的是 `5.7` 版本，在数据库同名的文件夹下就会存储一个 `视图名.frm` 的文件

示例：

如果 `mysql` 用的是 `8.0` 版本，创建视图并不会在数据库同名的文件夹下创建文件

--------------------

### 2.5 其他的文件 ###

除了上述用于用户存储的数据文件外，`数据目录` 下还包括为了更好运行程序的一些额外文件，主要包括以下几类：

*  服务器进程文件
    
     *  每一个运行的 `MySQL` 服务程序都相当于一个进程，`MySQL` 服务器会把自己的进程 `ID` 写入到一个文件中
 *  服务器日志文件
    
     *  在服务器运行过程中，会产生各种各样的日志，比如：常规的查询日志、错误日志、二进制日志、`redo` 日志等
 *  默认/自动生成的 SSL 和 RSA 证书和密钥文件
    
     *  主要是为了客户端和服务端安全通信而创建的一些文件

--------------------

上篇：[第一章、字符集、大小写规范与sql\_mode的合理设置][sql_mode]  
下篇：[第三章、用户、权限与角色管理][Link 1]

[sql_mode]: https://blog.csdn.net/xhmico/article/details/138074211
[Link 1]: https://blog.csdn.net/xhmico/article/details/138164322
[bilibili-_-MySQL]: https://www.bilibili.com/video/BV1iq4y1u7vj?p=104
[cf93aa48a982488fbfac1d0c044be071.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/091cbb3daa7c4bf78250923679e7ee13.png
[8ade5dd2f80d4a7d8c95d26490724372.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/ddf26647cab74967aae401eec1f2491a.png
[559d606a35924f5d9e78a91051836534.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/cf347b689d7c4fc8a6991baa99fa3d49.png
[b71e397aade244de8209767cc7acb29f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/c475dfe743ae4e26bda009165f8e40eb.png
[943fbd18a1224ecaa38f369151e9b5ad.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/3f73ba08dcd24902a9286370e2a5e3f8.png
[b01a4b2bf2134654a53560268e186e44.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/0b30994197784b8aa4aa7218d0ce095b.png
[e6bfd925ed6b49c9a00595209d87dad4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/49c7aeda087a496e8d2efdc0520fc6cb.png
[72ea9c0a04c5491097c812499984383e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/589ceca05b9f4ec194e206d960dc5f01.png
[d33f30376d8341aa816bdb4218242212.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/1813272f030045fdb27843b699bb114d.png
[2c72d5b17c1a45649b678b8db1d4fd4d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/517c494ebba9424f94e5f681302f053a.png
[b2e0f651a31d4fe5a89d1122b6d11af4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/ed3c012683c64f6d9ed432c185d64f70.png
[3c549d95f2d24b1d99abc8bab06bb67e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/829defe90c134a9583c0d180e28adca2.png
[615222378c1f432eb031d9229811a433.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/71a6be4a1972411e96a4f69fd1c918e9.png
[161a8bd4c50444f1874976af74ed6045.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/856d49bb55de45e48a623b5488deddfa.png
[75c259d78bdb45c88b0c063f2692a1ef.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/e1afa4f742ac4a75bf97311978684f45.png
[8e5595794858491994ac26f08689ca52.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/36545581119a486290a5967bb3b623c8.png