mysql数据库中的表空间与数据文件管理

mysql表空间分system/file-per-table/general三类：system即ibdata1，存系统数据；file-per-table每表独立.ibd；general需手动创建共享表空间。

MySQL 表空间类型怎么区分：system / file-per-table / general

MySQL 的表空间不是抽象概念，而是对应磁盘上真实的数据文件。关键区别在 innodb_file_per_table 配置和 CREATE TABLE 时是否显式指定 TABLESPACE。

默认开启 innodb_file_per_table=ON（MySQL 5.6.6+），此时每张 InnoDB 表生成独立的 .ibd 文件；若关闭，则所有表数据都写入共享表空间 ibdata1 —— 这会导致 DROP TABLE 后空间无法回收，且无法用 OPTIMIZE TABLE 收缩单表。

• SYSTEM 表空间：固定为 ibdata1（及可能的 ibdata2 等），存储数据字典、undo log、系统表等，不可删除
• FILE_PER_TABLE 表空间：每个表一个 .ibd，路径在 datadir/数据库名/表名.ibd，支持 TRUNCATE 和 OPTIMIZE 回收空间
• GENERAL 表空间：由 CREATE TABLESPACE ... ADD DATAFILE 创建，可跨库共享，但需手动管理路径与权限，且不支持临时表

如何查某张表用的是哪种表空间？

直接查 INFORMATION_SCHEMA.INNODB_SYS_TABLES 最可靠，SPACE 字段值为 0 表示 system 表空间，非 0 则是 file-per-table 或 general 表空间。

SELECT NAME, SPACE, FILE_FORMAT, ROW_FORMAT  FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES  WHERE NAME = 'testdb/users';

再结合 INFORMATION_SCHEMA.FILES 查物理路径：

SELECT TABLESPACE_NAME, FILE_NAME, TOTAL_EXTENTS  FROM INFORMATION_SCHEMA.FILES  WHERE TABLESPACE_NAME = 'testdb/users' OR TABLESPACE_NAME = 'innodb_system';

注意：FILES 视图只显示已打开的数据文件，冷备后未重启 MySQL 可能看不到新 .ibd。

迁移表到 GENERAL 表空间要注意什么？

不是所有场景都适合用 GENERAL 表空间。它主要解决多表共用一个大文件、统一管理 I/O 调度的需求，但会引入额外约束：

• 目标表空间必须已存在，且 ADD DATAFILE 指定的路径对 mysqld 进程可读写
• 迁移前表不能有全文索引、虚拟列、外键引用（除非所有关联表也在同一表空间）
• 执行 ALTER TABLE t1 TABLESPACE = my_ts 会重建整张表，期间锁表（online DDL 在 8.0.12+ 支持部分场景无锁，但表空间迁移仍需 copy algorithm）
• 迁移后 .ibd 文件被移走，原路径只剩一个空壳，误删会导致表不可访问

ibdata1 越来越大，能 shrink 吗？

不能在线 shrink。因为 ibdata1 是 SYSTEM 表空间，包含 undo logs、数据字典等核心结构，MySQL 不提供收缩接口。常见错误操作是直接清空或删除 ibdata1，这会导致实例启动失败。

真正可行的路径只有两个：

• 如果启用了 innodb_undo_tablespaces 且 undo 存在独立表空间中，可通过 SET GLOBAL innodb_undo_log_truncate=ON 配合 innodb_max_undo_log_size 自动截断旧 undo
• 彻底重建：导出所有数据（mysqldump --all-databases），停库，删除 ibdata1 + ib_logfile*，重启初始化新实例，再导入 —— 这是唯一能让 ibdata1 回到初始大小的方式

所以一开始就把 innodb_file_per_table=ON 设为强制策略，比后期补救重要得多。