MySQL InnoDB 共享表空間與獨立表空間深度解析

MySQL InnoDB 共享表空間與獨立表空間深度解析

InnoDB 數據存儲有兩種模式：共享表空間（System Tablespace）與獨立表空間（File-Per-Table），由參數 innodb_file_per_table 控制。本文對比優劣、遷移方法與運維建議。

1. 概念區別

2. 優劣勢對比

共享表空間

• 優點：可跨磁盤分佈多文件，單表不受單文件大小限制；管理文件少。
• 缺點：大量刪除產生碎片難回收（ibdata 不會縮小），統計/日志類高寫入刪除場景不適用；統計信息混雜影響優化器。

獨立表空間（推薦，默認 ON）

• 優點：DROP TABLE 瞬間回收空間；TRUNCATE / ALTER TABLE ... ENGINE=InnoDB 可收縮碎片；可跨實例傳輸 .ibd（配合 DISCARD/IMPORT TABLESPACE）；碎片局限單表不影響全局。
• 缺點：單表超大（>100GB）時文件系統操作受限；大量小表產生海量文件影響 inode/元數據性能。

3. 切換與遷移

開啟獨立表空間（動態生效，僅影響新建表）：

SET GLOBAL innodb_file_per_table = ON;

持久化寫入 my.cnf：

[mysqld]
innodb_file_per_table = 1
innodb_data_home_dir = /data/mysql
innodb_log_group_home_dir = /data/mysql/logs
innodb_data_file_path = ibdata1:10M:autoextend

存量表遷移（需重建表）：

ALTER TABLE t ENGINE=InnoDB;  -- 會生成 t.ibd，舊數據仍留在 ibdata 中
-- 或導出導入：mysqldump -> DROP -> CREATE -> IMPORT

4. 運維建議

• 生產環境強烈建議開啟獨立表空間。
• 定期 ANALYZE TABLE 更新統計信息。
• 大表碎片整理：OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB（Online DDL）。
• 備份策略：物理備份 xtrabackup 支持兩種模式；邏輯備份無差異。