【重学MySQL】九十七、MySQL目录结构与文件系统解析

一、MySQL 目录结构

1.1 核心目录与文件

• 数据目录（datadir）

  • 默认路径：
    • Linux：/var/lib/mysql
    • Windows：C:\ProgramData\MySQL\MySQL Server X.X\Data
  • 查询命令：

    SHOW VARIABLES LIKE 'datadir';
    

  • 子目录与文件：
    • 数据库子目录：每个数据库对应一个子目录，名称与数据库同名，如mysql、information_schema。
    • 系统文件：
      • .frm：表结构定义文件（所有存储引擎通用）。
      • .ibd：InnoDB独立表空间文件（仅当innodb_file_per_table=ON时生成）。
      • .MYD、.MYI：MyISAM表的数据和索引文件。

• 其他关键文件：

  • 日志文件：
    • 错误日志：hostname.err（路径通过SHOW VARIABLES LIKE 'log_error';查询）。
    • 慢查询日志：记录执行时间超过long_query_time的查询。
    • 二进制日志：记录数据变更操作，用于复制和恢复。
  • 配置文件：
    • my.cnf（Linux）或my.ini（Windows），包含全局配置如datadir、innodb_file_per_table。
  • 临时文件：
    • 排序、连接等操作产生的临时文件，通常位于/tmp（Linux）或系统临时目录（Windows）。

1.2 存储引擎与文件关联

2.3 查看默认数据库

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

  SHOW  DATABASES ;

可以看到有4个数据库是属于MySQL自带的系统数据库。

• mysql
  MySQL 系统自带的核心数据库，它存储了MySQL的用户账户和权限信息， 一些存储过程、事件的定义信息， 一些运行过程中产生的日志信息， 一些帮助信息以及时区信息等。
• information_schema
  MySQL 系统自带的数据库，这个数据库保存着MySQL服务器 维护的所有其他数据库的信息 ，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为 元数据 。在系统数据库 information_schema 中提供了一些以innodb_sys 开头的表，用于表示内部系统表。

mysql>  USE  information_schema ; Database  changed

mysql>  SHOW  TABLES  LIKE   'innodb_sys% ' ;

|  Tables_in_information_schema  (innodb_sys%)   |

+--------------------------------------------+
|  INNODB_SYS_DATAFILES                                                    |
|  INNODB_SYS_VIRTUAL                                                        |
|  INNODB_SYS_INDEXES                                                         |
|  INNODB_SYS_TABLES                                                            |
|  INNODB_SYS_FIELDS                                                          |
|  INNODB_SYS_TABLESPACES                                                 |
|  INNODB_SYS_FOREIGN_COLS                                               |
|  INNODB_SYS_COLUMNS                                                        |
|  INNODB_SYS_FOREIGN                                                          |
|  INNODB_SYS_TABLESTATS                                                   |

10  rows  in  set  (0 .00  sec)

• performance_schema
  MySQL 系统自带的数据库，这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，可以用来 监控 MySQL 服务的各类性能指标 。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息。
• sys
  MySQL 系统自带的数据库，这个数据库主要是通过 视图 的形式把 information_schema 和 performance_schema 结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能。

二、表在文件系统中的表示

2.1 MyISAM 引擎

• 文件组成：
  • .frm：存储表结构定义（列名、数据类型、索引等元数据）。
  • .MYD（MyData）：存储实际数据，采用固定或动态长度格式。
  • .MYI（MyIndex）：存储B+树结构的索引（主键索引和辅助索引）。
• 示例：
  • 表user（MyISAM引擎）对应文件：user.frm、user.MYD、user.MYI。

2.2 InnoDB 引擎

• 文件组成：
  • .frm：存储表结构定义（与MyISAM共享）。
  • .ibd（独立表空间文件）：存储数据和索引（聚簇索引结构），仅在启用innodb_file_per_table=ON时生成。
  • 系统表空间：若未启用独立表空间，所有表共享ibdata1文件，包含元数据、Undo日志等。
• 示例：
  • 表order（InnoDB引擎，启用独立表空间）对应文件：order.frm、order.ibd。

2.3 InnoDB 文件格式与行格式

• 文件格式：
  • Antelope：旧格式，支持COMPACT和REDUNDANT行格式（MySQL 5.7及之前默认）。
  • Barracuda：新格式，支持DYNAMIC和COMPRESSED行格式（MySQL 8.0默认）。
• 行格式：
  • REDUNDANT：兼容旧版本，变长字段前768字节存储在索引页，剩余在溢出页。
  • COMPACT：空间效率高（比REDUNDANT节省约20%），但增加CPU开销。
  • DYNAMIC：变长字段完全存储在溢出页，支持大索引前缀（最大3072字节）。
  • COMPRESSED：在DYNAMIC基础上增加数据压缩，适用于存储大量文本或BLOB。

三、表空间配置与管理

3.1 独立表空间 vs 系统表空间

3.2 表空间扩展与调整

• 扩展系统表空间：

  ALTER TABLESPACE my_tablespace ADD DATAFILE 'my_tablespace2.ibd' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 5G;
  

• 独立表空间自动扩展：

  CREATE TABLESPACE my_tablespace ADD DATAFILE 'my_tablespace.ibd' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 5G;
  

四、常见问题与解决方案

4.1 数据目录修改

• 步骤：
  1. 停止MySQL服务：sudo systemctl stop mysql（Linux）或通过服务管理器（Windows）。
  2. 备份数据：复制原数据目录至新路径。
  3. 修改配置文件：在my.cnf中更新datadir路径。
  4. 移动文件：将备份的数据文件移动至新目录。
  5. 重启服务：sudo systemctl start mysql。
  6. 验证路径：SHOW VARIABLES LIKE 'datadir';。

4.2 表空间不足

• 解决方案：
  • 扩展系统表空间：通过ALTER TABLESPACE添加新数据文件或调整现有文件大小。
  • 清理空间：删除无用表或数据，优化表结构（如重建索引）。

4.3 文件权限问题

• 解决命令：

  sudo chown -R mysql:mysql /new/data/path  # 修改目录所有权
  sudo chmod -R 755 /new/data/path          # 修改目录权限
  

五、版本差异与兼容性

5.1 MySQL 5.7 vs 8.0

5.2 升级注意事项

• 数据迁移：使用mysqldump导出数据，再导入新版本，避免因文件格式变化导致错误。
• 配置兼容性：检查旧版本配置（如innodb_file_format）是否与新版本兼容。

六、总结

• 目录结构：以datadir为核心，存储数据库、表及日志文件，不同存储引擎文件格式差异显著。
• 表表示：MyISAM采用三文件结构，InnoDB依赖.frm和.ibd（或系统表空间），通过合理配置表空间和行格式可优化性能。
• 管理建议：生产环境优先启用独立表空间，定期备份数据，关注版本升级后的配置变化。

通过深入理解MySQL的目录结构与文件表示，可有效提升数据库管理效率，确保数据安全与性能优化。