MySQL数据目录

Posted on Edited on In , Views: Word count in article: 16k Reading time ≈ 15 mins.

1、MySQL8的主要目录结构

1
2
3
4
5
root@f0796ad75d78:/# find / -name mysql
/etc/mysql
/usr/lib/mysql
/usr/bin/mysql
…………

1.1 数据库文件的存放路径

  • MySQL数据库文件的存放路径：/var/lib/mysql/。

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

  • MySQL把数据都存到哪个路径下呢？其实数据目录对应着一个系统变量datadir，我们在使用客户端与服务器建立连接之后查看这个系统变量的值就可以了：

    1
    2
    3
    4
    5
    6
    7
    mysql> show variables like 'datadir';
    +---------------+-----------------+
    | Variable_name | Value           |
    +---------------+-----------------+
    | datadir       | /var/lib/mysql/ |
    +---------------+-----------------+
    1 row in set (0.00 sec)

1.2 相关命令目录

  • 相关命令目录：/usr/bin（mysqladmin、mysqlbinlog、mysqldump等命令）和/usr/sbin。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    root@f0796ad75d78:/# cd /usr/bin
    root@f0796ad75d78:/usr/bin# find . -name "mysql*"
    ./mysql_secure_installation
    ./mysql_upgrade
    ./mysqldump
    ./mysqldumpslow
    ./mysql
    ./mysqlbinlog
    ./mysql_migrate_keyring
    ./mysql_config_editor
    ./mysqld_safe
    ./mysql_ssl_rsa_setup
    ./mysqlimport
    ./mysqlslap
    ./mysql_tzinfo_to_sql
    ./mysqladmin
    ./mysqlshow
    ./mysqld_multi
    ./mysqlpump
    ./mysqlcheck

    root@f0796ad75d78:/# cd /usr/sbin
    root@f0796ad75d78:/usr/sbin# find . -name "mysql*"
    ./mysqld

1.3 配置文件目录

  • 配置文件目录：/usr/share/mysql-8.0（命令及配置文件），/etc/mysql（如my.cnf）。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    root@f0796ad75d78:/# cd /usr/share/mysql-8.0
    root@f0796ad75d78:/usr/share/mysql-8.0# ls -l
    total 1080
    drwxr-xr-x 2 root root   4096 Oct 12  2021 bulgarian
    drwxr-xr-x 2 root root   4096 Oct 12  2021 charsets
    drwxr-xr-x 2 root root   4096 Oct 12  2021 czech
    drwxr-xr-x 2 root root   4096 Oct 12  2021 danish
    -rw-r--r-- 1 root root  25575 Jul  1  2021 dictionary.txt
    drwxr-xr-x 2 root root   4096 Oct 12  2021 docs
    drwxr-xr-x 2 root root   4096 Oct 12  2021 dutch
    drwxr-xr-x 2 root root   4096 Oct 12  2021 english
    drwxr-xr-x 2 root root   4096 Oct 12  2021 estonian
    drwxr-xr-x 2 root root   4096 Oct 12  2021 french
    drwxr-xr-x 2 root root   4096 Oct 12  2021 german
    drwxr-xr-x 2 root root   4096 Oct 12  2021 greek
    drwxr-xr-x 2 root root   4096 Oct 12  2021 hungarian
    drwxr-xr-x 2 root root   4096 Oct 12  2021 italian
    drwxr-xr-x 2 root root   4096 Oct 12  2021 japanese
    drwxr-xr-x 2 root root   4096 Oct 12  2021 korean
    -rw-r--r-- 1 root root 610894 Jul  1  2021 messages_to_clients.txt
    -rw-r--r-- 1 root root 348904 Jul  1  2021 messages_to_error_log.txt
    -rw-r--r-- 1 root root   1977 Jul  1  2021 mysql-log-rotate
    drwxr-xr-x 2 root root   4096 Oct 12  2021 norwegian
    drwxr-xr-x 2 root root   4096 Oct 12  2021 norwegian-ny
    drwxr-xr-x 2 root root   4096 Oct 12  2021 polish
    drwxr-xr-x 2 root root   4096 Oct 12  2021 portuguese
    drwxr-xr-x 2 root root   4096 Oct 12  2021 romanian
    drwxr-xr-x 2 root root   4096 Oct 12  2021 russian
    drwxr-xr-x 2 root root   4096 Oct 12  2021 serbian
    drwxr-xr-x 2 root root   4096 Oct 12  2021 slovak
    drwxr-xr-x 2 root root   4096 Oct 12  2021 spanish
    drwxr-xr-x 2 root root   4096 Oct 12  2021 swedish
    drwxr-xr-x 2 root root   4096 Oct 12  2021 ukrainian

    root@f0796ad75d78:/# cd /etc/mysql
    root@f0796ad75d78:/etc/mysql# ls -l
    total 12
    drwxr-xr-x 2 root root 4096 Apr  9 13:10 conf.d
    -rw-r--r-- 1 root root  278 Apr  9 13:11 my.cnf
    -rw-r--r-- 1 root root 1448 Jul  1  2021 my.cnf.fallback

2、数据库和文件系统的关系

  • 像InnoDB、MyISAN这样的存储引擎都是把表存储在磁盘上的，操作系统用来管理磁盘的结构被称为文件系统，所以用专业一点的话来表述就是：像lnnoDB、MylSAM这样的存储引擎都是把表存储在文件系统上的。当我们想读取数据的时候，这些存储引擎会从文件系统中把数据读出来返回给我们，当我们想写入数据的时候，这些存储引擎会把这些数据又写回文件系统。本章学习一下InnoDB和MyISAM这两个存储引擎的数据如何在文件系统中存储。

2.1 查看默认数据库

  • 查看一下在我的计算机上当前有哪些数据库：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    # 可以看到有4个数据库是属于MySQL自带的系统数据库。
    mysql> SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    13 rows in set (0.00 sec)

    • mysql：

      • MySQL系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定 义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。

    • information_schema：

      • MySQL系统自带的数据库，这个数据库保存着MySQL服务器维护的所有其他数据库的信息 ，比如有 哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。在系统数据库information_schema中提供了一些以 innodb_sys 开头的表，用于表示内部系统表。

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22
        23
        24
        25
        26
        27
        28
        29
        30
        31
        32
        33
        34
        35
        36
        37
        38
        39
        40
        41
        42
        43
        mysql> USE information_schema;
        Reading table information for completion of table and column names
        You can turn off this feature to get a quicker startup with -A

        Database changed

        mysql> SHOW TABLES LIKE 'INNODB%';
        +----------------------------------------+
        | Tables_in_information_schema (INNODB%) |
        +----------------------------------------+
        | INNODB_BUFFER_PAGE                     |
        | INNODB_BUFFER_PAGE_LRU                 |
        | INNODB_BUFFER_POOL_STATS               |
        | INNODB_CACHED_INDEXES                  |
        | INNODB_CMP                             |
        | INNODB_CMPMEM                          |
        | INNODB_CMPMEM_RESET                    |
        | INNODB_CMP_PER_INDEX                   |
        | INNODB_CMP_PER_INDEX_RESET             |
        | INNODB_CMP_RESET                       |
        | INNODB_COLUMNS                         |
        | INNODB_DATAFILES                       |
        | INNODB_FIELDS                          |
        | INNODB_FOREIGN                         |
        | INNODB_FOREIGN_COLS                    |
        | INNODB_FT_BEING_DELETED                |
        | INNODB_FT_CONFIG                       |
        | INNODB_FT_DEFAULT_STOPWORD             |
        | INNODB_FT_DELETED                      |
        | INNODB_FT_INDEX_CACHE                  |
        | INNODB_FT_INDEX_TABLE                  |
        | INNODB_INDEXES                         |
        | INNODB_METRICS                         |
        | INNODB_SESSION_TEMP_TABLESPACES        |
        | INNODB_TABLES                          |
        | INNODB_TABLESPACES                     |
        | INNODB_TABLESPACES_BRIEF               |
        | INNODB_TABLESTATS                      |
        | INNODB_TEMP_TABLE_INFO                 |
        | INNODB_TRX                             |
        | INNODB_VIRTUAL                         |
        +----------------------------------------+
        31 rows in set (0.01 sec)

    • performance_schema：

      • MySQL系统自带的数据库，这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，可以 用来监控MySQL服务的各类性能指标。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息。

    • sys：

      • MySQL系统自带的数据库，这个数据库主要是通过视图的形式把information_schema和performance_schema结合起来，帮助系统管理员和开发人员监控MySQL的技术性能。

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

  • 使用CREATE DATABASE 数据库名语句创建一个数据库的时候，在文件系统上实际发生了什么呢？其实很简单，每个数据库都对应数据目录下的一个子目录，或者说对应一个文件夹，每当新建一个数据库时，MySQL会帮我们做这两件事：

    • 在数据目录下创建一个和数据库名同名的子目录。
    • 在与该数据库名同名的子目录下创建一个名为db.opt的文件（仅限MySQL5.7及之前版本），这个文件中包含了该数据库的各种属性，比如该数据库的字符集和比较规则。

  • 看一下我的计算机上的数据目录下的内容：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    # mysql5.7
    root@415439d38b3c:/# cd /var/lib/mysql
    root@415439d38b3c:/var/lib/mysql# ls -l
    total 188480
    -rw-r----- 1 mysql mysql       56 Apr  8 02:46 auto.cnf
    -rw------- 1 mysql mysql     1676 Apr  8 02:46 ca-key.pem
    -rw-r--r-- 1 mysql mysql     1112 Apr  8 02:46 ca.pem
    -rw-r--r-- 1 mysql mysql     1112 Apr  8 02:46 client-cert.pem
    -rw------- 1 mysql mysql     1680 Apr  8 02:46 client-key.pem
    -rw-r----- 1 mysql mysql     1359 Apr  8 02:46 ib_buffer_pool
    -rw-r----- 1 mysql mysql 50331648 Apr  8 02:52 ib_logfile0
    -rw-r----- 1 mysql mysql 50331648 Apr  8 02:46 ib_logfile1
    -rw-r----- 1 mysql mysql 79691776 Apr  8 02:52 ibdata1 # 系统表空间
    -rw-r----- 1 mysql mysql 12582912 Apr  8 02:46 ibtmp1
    drwxr-x--- 2 mysql mysql     4096 Apr  8 02:46 mysql
    drwxr-x--- 2 mysql mysql     4096 Apr  8 02:46 performance_schema
    -rw------- 1 mysql mysql     1680 Apr  8 02:46 private_key.pem
    -rw-r--r-- 1 mysql mysql      452 Apr  8 02:46 public_key.pem
    -rw-r--r-- 1 mysql mysql     1112 Apr  8 02:46 server-cert.pem
    -rw------- 1 mysql mysql     1676 Apr  8 02:46 server-key.pem
    drwxr-x--- 2 mysql mysql    12288 Apr  8 02:46 sys
    drwxr-x--- 2 mysql mysql     4096 Apr  8 02:52 test

    • 这个数据目录下的文件和子目录比较多，除了information_schema这个系统数据库外，其他的数据库在 数据目录下都有对应的子目录。

    • 以我的test数据库为例，在MySQL5.7中打开：

      1
      2
      3
      4
      5
      root@415439d38b3c:/var/lib/mysql/test# ls -l
      total 112
      -rw-r----- 1 mysql mysql    65 Apr  8 02:47 db.opt
      -rw-r----- 1 mysql mysql  8556 Apr 16 04:12 mytable.frm # 存储表结构
      -rw-r----- 1 mysql mysql 98304 Apr 16 04:12 mytable.ibd # 存储数据

    • 在MySQL8.0中打开：

      1
      2
      3
      root@f0796ad75d78:/var/lib/mysql/test# ls -l
      total 112
      -rw-r----- 1 mysql mysql 114688 Apr 16 04:17 mytable.ibd # 存放表结构以及表数据

2.3 表在文件系统中的表示

  • 我们的数据其实都是以记录的形式插入到表中的，每个表的信息其实可以分为两种：
    • 表结构的定义。
    • 表中的数据。
  • 表结构就是该表的名称，表里边有多少列，每个列的数据类型，约束条件和索引，使用的字符集和比较规则等各种信息，这些信息都体现在了我们的建表语句中了。

2.3.1 InnoDB存储引擎模式

表结构

  • 为了保存表结构，InnoDB在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件，文件名是这样：

    1
    表名.frm

  • 比方说我们在test数据库下创建一个名为mytable的表：

    1
    2
    3
    4
    5
    6
    7
    mysql> USE test;
    Database changed

    mysql> CREATE TABLE mytable (
    -> id INT
    -> );
    Query OK, 0 rows affected (0.03 sec)

    那在数据库test对应的子目录下就会创建一个名为mytable.frm的用于描述表结构的文件。.frm文件的格式在不同的平台上都是相同的。这个后缀名为.frm是以二进制格式存储的，我们直接打开是乱码的。

表中数据和索引

  • 储备知识：（索引章节会讲到）

    • InnoDB其实是使用页为基本单位来管理存储空间的，默认的页大小为16KB。
    • 对于lnnoDB存储引擎来说，每个索引都对应着一棵B+树，该B+树的每个节点都是一个数据页，数据页之间不必要是物理连续的，因为数据页之间有双问链表来维护着这些页的顺序。
    • InnoDB的聚接索引的叶子节点存储了完整的用户记录，也就是所谓的索引即数据，数据即索引。

  • 为了更好的管理这些页，InnoDB提出了一个表空间或者文件空间（英文名：table space或者file space）
    的概念，这个表空间是一个抽象的概念，它可以对应文件系统上一个或多个真实文件（不同表空间对应的文件数量可能不同）。每一个表空间可以被划分为很多个页，我们的表数据就存放在某个表空间下的某些页里。这里表空间有几种不同的类型：

    • ①系统表空间（system tablespace）：

      • 默认情况下，InnoDB会在数据目录下创建一个名为ibdata1、大小为12M的文件，这个文件就是对应 的系统表空间在文件系统上的表示。怎么才12M？注意这个文件是自扩展文件，当不够用的时候它会自己增加文件大小。

      • 当然，如果你想让系统表空间对应文件系统上多个实际文件，或者仅仅觉得原来的ibdata1这个文件名难听，那可以在MySQL启动时配置对应的文件路径以及它们的大小，比如我们这样修改一下my.cnf 配置文件：

        1
        2
        [server]
        innodb_data_file_path=data1:512M;data2:512M:autoextend

        这样在MySQL启动之后就会创建这两个512M大小的文件作为系统衣空间，其中的autoextend表明这两个文件如果不够用会自动扩展data2文件的大小。

      • 需要注意的一点是，在一个MySQL服务器中，系统衣空间只有一份。从MySQL5.5.7至到MySQL5.6.6之间的各个版本中，我们表中的数据都会被默认存储到这个系统表空间。

    • ②独立表空间（file-per-table tablespace）：

      • 在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为每 一个表建立一个独立表空间 ，也就是说我们创建了多少个表，就有多少个独立表空间。使用独立表空间来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同，只不过添加了一个.ibd的扩展名而已，所以完整的文件名称长这样：

        1
        表名.ibd

        比如我们使用了独立表空间去存储test数据库下的mytable表的话，那么在该表所在数据库对应的test目录下会为mytable表创建这两个文件：

        1
        2
        mytable.frm
        mytable.ibd

        其中test.ibd文件就用来存储mytable表中的数据和索引。

    • ③系统表空间与独立表空间的设置。

      • 我们可以自己指定使用系统表空间还是独立表空间来存储数据，这个功能由启动参数innodb_file_per_table控制，比如说我们想刻意将表数据都存储到系统表空间时，可以在启动 MySQL服务器的时候这样配置：

        1
        2
        [server]
        innodb_file_per_table=0 # 0：代表使用系统表空间； 1：代表使用独立表空间

        默认情况： 

        1
        2
        3
        4
        5
        6
        7
        8
        # mysql5.7与mysql8.0
        mysql> show variables like 'innodb_file_per_table';
        +-----------------------+-------+
        | Variable_name         | Value |
        +-----------------------+-------+
        | innodb_file_per_table | ON    |
        +-----------------------+-------+
        1 row in set (0.00 sec)

        说明：innodb-file-per-table参数的修改只对新建的表起作用，对于已经分配了表空间的表并不起作用。如果我们想把已经存在系统表空间中的表转移到独立表空间，可以使用下边的语法：

        1
        ALTER TABLE 表名 TABLESPACE [=] innodb_file_per_table;

        或者把已经存在独立表空间的表转移到系统表空间，可以使用下边的语法：

        1
        ALTER TABLE 表名 TABLESPACE [=] innodb_system;

        其中中括号扩起来的=可有可无，比如：我们想把mytable表从独立表空间移动到系统表空间，可以这么写：

        1
        ALTER TABLE mytable TABLESPACE innodb_system;

    • ④其他类型的表空间。

      • 随着MySQL的发展，除了上述两种老牌表空间之外，现在还新提出了一些不同类型的表空间，比如通用表空间（general tablespace）、临时表空间（temporary tablespace）等。

  • .frm在MySQL8中不存在了。那去哪里了呢？

    • 这就需要解析ibd文件。Oracle官方将frm文件的信息及更多信息移动到叫做序列化字典信息（Serialized Dictionary Information，SDl），SDl被写在ibd文件内部。MySQL8.0属于Oracle旗下，同理。

    • 为了从IBD文件中提取SDl信息，Oracle提供了一个应用程序ibd2sdi。这个工具不需要下载，MySQL8自带的有，只要你配好环境变量就能到处用。

      • ①查看表结构，到存储ibd文件的目录下，执行下面的命令：

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22
        23
        24
        25
        26
        27
        28
        29
        30
        31
        32
        33
        34
        35
        36
        37
        38
        39
        40
        41
        42
        43
        44
        45
        46
        47
        48
        49
        50
        51
        52
        53
        54
        55
        56
        57
        58
        59
        60
        61
        62
        63
        64
        65
        66
        67
        68
        69
        70
        71
        72
        73
        74
        75
        76
        77
        78
        79
        80
        81
        82
        83
        84
        85
        86
        87
        88
        89
        90
        91
        92
        93
        94
        95
        96
        97
        98
        99
        100
        101
        102
        103
        104
        105
        106
        107
        108
        109
        110
        111
        112
        113
        114
        115
        116
        117
        118
        119
        120
        121
        122
        123
        124
        125
        126
        127
        128
        129
        130
        131
        132
        133
        134
        135
        136
        137
        138
        139
        140
        root@f0796ad75d78:/# cd /var/lib/mysql/test
        root@f0796ad75d78:/var/lib/mysql/test# ibd2sdi --dump-file=mytable.txt mytable.ibd
        root@f0796ad75d78:/var/lib/mysql/test# ls
        mytable.ibd  mytable.txt
        root@f0796ad75d78:/var/lib/mysql/test# cat mytable.txt 
        ["ibd2sdi"
        ,
        {
                "type": 1,
                "id": 478,
                "object":
                        {
            "mysqld_version_id": 80026,
            "dd_version": 80023,
            "sdi_version": 80019,
            "dd_object_type": "Table",
            "dd_object": {
                "name": "mytable", # 表名
                "mysql_version_id": 80026,
                "created": 20220416041724,
                "last_altered": 20220416041724,
                "hidden": 1,
                "options": "avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;",
                "columns": [
                    {
                        "name": "id",
                        "type": 4,
                        "is_nullable": true,
                        "is_zerofill": false,
                        "is_unsigned": false,
                        "is_auto_increment": false,
                        "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": 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=1169;",
                        "engine_attribute": "",
                        "secondary_engine_attribute": "",
                        "column_key": 1,
                        "column_type_utf8": "int",
                        "elements": [],
                        "collation_id": 33,
                        "is_explicit_collation": false
                    },
                   ……………………
                ],
                "schema_ref": "test",
                "se_private_id": 1169,
                "engine": "InnoDB",
                "last_checked_for_upgrade_version_id": 0,
                "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": true,
                        "is_generated": false,
                        "ordinal_position": 1,
                        "comment": "",
                        "options": "",
                        "se_private_data": "id=319;root=4;space_id=102;table_id=1169;trx_id=4043;",
                        "type": 2,
                        "algorithm": 2,
                        "is_algorithm_explicit": false,
                        "is_visible": true,
                        "engine": "InnoDB",
                        "engine_attribute": "",
                        "secondary_engine_attribute": "",
                        "elements": [
                            {
                                "ordinal_position": 1,
                                "length": 4294967295,
                                "order": 2,
                                "hidden": true,
                                "column_opx": 1
                            },
                            {
                                "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": 0
                            }
                        ],
                        "tablespace_ref": "test/mytable"
                    }
                ],
                "foreign_keys": [],
                "check_constraints": [],
                "partitions": [],
                "collation_id": 33
            }
        }
        }
        ……………………
        }
        ]

        通过上面的测试结果可以发现，MySQL8把之前版本的frm文件合并到ibd文件中了。

2.3.2 MyISAM存储引擎模式

表结构

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

    1
    表名.frm

表中数据和索引

  • 在MyISAM中的索引全部都是二级索引 ，该存储引擎的数据和索引是分开存放的。所以在文件系统中也是使用不同的文件来存储数据文件和索引文件，同时表数据都存放在对应的数据库子目录下。假如mytable表使用MyISAM存储引擎的话，那么在它所在数据库对应的test目录下会为mytable表创建这三个文件：

    1
    2
    3
    mytable.frm 存储表结构
    mytable.MYD 存储数据 (MYData)
    mytable.MYI 存储索引 (MYIndex)
    • 其中mytable.MYD代表表的数据文件，也就是我们插入的用户记录。采用独立表存储模式，每个表对应一个MYD文件；mytable.MYI代表表的索引文件，我们为该表创建的索引都会放到这个文件中。

  • 举例：创建一个MyISAM表，使用ENGINE选项显式指定引擎。因为InnoDB是默认引擎。

    1
    2
    3
    4
    5
    6
    7
    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;

    • MySQL8版本中：

      1
      2
      3
      4
      5
      6
      7
      8
      root@f0796ad75d78:/# cd /var/lib/mysql/test
      root@f0796ad75d78:/var/lib/mysql/test# ls -l
      total 136
      -rw-r----- 1 mysql mysql 114688 Apr 16 04:17 mytable.ibd
      -rw------- 1 root  root    9398 Apr 16 06:53 mytable.txt
      -rw-r----- 1 mysql mysql      0 Apr 16 07:03 student_myisam.MYD # 存储数据
      -rw-r----- 1 mysql mysql   1024 Apr 16 07:03 student_myisam.MYI # 存储索引
      -rw-r----- 1 mysql mysql   4327 Apr 16 07:03 student_myisam_479.sdi # 存储元数据
      • 对于lnnoDB表，SDl与lnnoDB用户表空间中的数据一起存储。对于MyISAM和其他存储引擎，它被写入数据目录中的sdi文件。
      • 在MySQL8.0中，MyISAM存储引擎不提供分区支持。在以前版本的MySQL中创建的分区MyISAM表不能在MySQL8.0中使用。

    • MySQL5版本中：

      1
      2
      3
      4
      5
      6
      7
      8
      9
      root@f0796ad75d78:/# cd /var/lib/mysql/test
      root@415439d38b3c:/var/lib/mysql/test# ls -l
      total 128
      -rw-r----- 1 mysql mysql    65 Apr  8 02:47 db.opt
      -rw-r----- 1 mysql mysql  8556 Apr 16 04:12 mytable.frm
      -rw-r----- 1 mysql mysql 98304 Apr 16 04:12 mytable.ibd
      -rw-r----- 1 mysql mysql     0 Apr 16 07:06 student_myisam.MYD # 存储数据
      -rw-r----- 1 mysql mysql  1024 Apr 16 07:06 student_myisam.MYI # 存储索引
      -rw-r----- 1 mysql mysql  8642 Apr 16 07:06 student_myisam.frm # 存储表结构

      可以发现，在之前的数据库版本中，MylSAM引擎已存在frm文件，但是在MySQL8之后也和InnoDB引一样去掉了，放入了sdi文件中。

2.4 小结

  • 举例：数据库a，表b。

    • 如果表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)：存放索引信息文件。

2.5 视图在文件系统中的表示

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

2.6 其他的文件

  • 除了我们上边说的这些用户自己存储的数据以外，数据日录下还包括为了更好运行程序的一些额外文件，主要包括这几种类型的文件：
    • 服务器进程文件。
      • 我们知道每运行一个MySQL服务器程序，都意味着启动一个进程。MySQL服务器会把自己的进程ID写入到一个文件中。
    • 服务器日志文件。
      • 在服务器运行过程中，会产生各种各样的日志，比如常规的查询日志、错误日志、二进制日志、redo日志等。这些日志各有各的用途，后面讲解。
    • 默认/自动生成的SSL和RSA证书和密钥文件。
      • 主要是为了客户端和服务器安全通信而创建的一些文件。