MySQL InnoDB File-Per-Table表空間

InnoDB File-Per-Table表空間
過去，所有InnoDB表和索引都儲存在系統表空間中。這種整體的方法針對的是完全專用於資料庫處理的機器，透過精心規劃的資料增長，分配給MySQL的任何磁碟儲存永遠不會被用於其他用途。InnoDB的file-per-table表空間特性提供了一個更靈活的替代方案，每個InnoDB表及其索引都儲存在一個單獨的.ibd資料檔案中。每個這樣的.ibd資料檔案代表一個單獨的表空間。這個特性是由innodb_file_per_table配置選項控制的，在MySQL 5.6.6及更高版本中預設啟用。

file-per-table表空間的優點
.當truncate或drop儲存在file-per-table表空間中的表時，可以回收磁碟空間。truncate或drop儲存在共享系統表空間中的表會在系統表空間資料檔案(ibdata檔案)內部建立空閒空間，這些空間只能用於新的InnoDB資料。

.在儲存在file-per-table表空間檔案中的表上執行TRUNCATE TABLE操作會更快。

.您可以將特定的表儲存在單獨的儲存裝置上，以實現I/O最佳化、空間管理或備份目的。在以前的版本中，您必須將整個資料庫目錄移動到其他驅動器，並在MySQL資料目錄中建立符號連結。在MySQL 5.6.6及更高版本中，你可以使用
create table… data directory=absolute_path_to_directory。

.你可以執行OPTIMIZE TABLE來壓縮或重新建立一個file-per-table表空間。當你執行一個OPTIMIZE TABLE時，InnoDB會建立一個新的.ibd檔案，該檔案帶有一個臨時名稱，只使用儲存實際資料所需的空間。當最佳化完成後，InnoDB會刪除舊的.ibd檔案，並用新檔案替換它。如果以前的.ibd檔案顯著增長，但實際資料只佔其大小的一部分，那麼執行OPTIMIZE TABLE可以回收未使用的空間。

.你可以移動單個InnoDB表，而不是整個資料庫

.你可以將InnoDB表從一個MySQL例項複製到另一個例項(稱為可遷移表空間特性)。

.在file-per-table表空間中建立的表使用Barracuda檔案格式。Barracuda檔案格式支援壓縮和動態行格式等特性

.可以使用動態行格式為具有大型BLOB或TEXT列的表啟用更高效的儲存。

.file-per-table表空間可以在發生損壞、伺服器無法重啟或備份和二進位制日誌不可用時提高成功恢復的機會並節省時間。

.在複製或備份表時，file-per-table表空間可以方便地報告每個表的狀態。

.可以在檔案系統級別監控表大小，而不需要訪問MySQL。

.當innodb_flush_method設定為O_DIRECT時，普通的Linux檔案系統不允許併發寫入單個檔案。因此，使用file-per-table表空間和innodb_flush_method可能會提高效能。

.系統表空間儲存資料字典和undo日誌，受InnoDB表空間大小限制。使用file-per_table表空間，每個表都有自己的表空間，這為增長提供了空間。

file-per-table表空間的潛在缺點
.使用file-per-table表空間，每個表可能有未使用的空間，這些空間只能由同一表的行使用。如果管理不當，可能會造成空間的浪費。

.fsync操作必須在每個開啟的表上執行，而不是單個檔案上。因為每個檔案都有一個單獨的fsync操作，所以對多個表的寫操作不能合併成一個單獨的I/O操作。這可能需要InnoDB執行更多的fsync操作。

.mysqld必須為每個表保留一個開啟的檔案控制程式碼，如果在file-per-table表空間中有很多表，這可能會影響效能。

.使用了更多的檔案描述符

.innodb_file_per_table在MySQL 5.6.6及更高版本中是預設啟用的。如果向後相容MySQL 5.5或5.1是一個問題，你可以考慮禁用它。禁用innodb_file_per_table功能可以防止在ALTER TABLE重新建立InnoDB表(ALGORITHM=COPY)時，阻止alter table將InnoDB表從系統表空間移動到單獨的.ibd檔案中。

例如，當重構InnoDB表的聚集索引時，表會使用innodb_file_per_table的當前設定重新建立。此行為在新增或刪除InnoDB二級索引時不適用。當不重建表而建立二級索引時，無論當前的innodb_file_per_table設定是什麼，索引都被儲存在與表資料相同的檔案中。此行為也不適用於使用CREATE TABLE…TABLESPACE或ALTER TABLE …TABLESPACE語法新增到系統表空間中的表。這些表不受innodb_file_per_table設定的影響。

.如果許多表都在增長，可能會出現更多的碎片，這可能會影響DROP TABLE和表掃描效能。但是，在管理碎片時，將檔案放在它們自己的表空間中可以提高效能。

.在刪除file-per-table表空間時會掃描緩衝池，對於大小為幾十gb的緩衝池來說，這可能需要幾秒鐘的時間。掃描是用一個寬的內部鎖執行的，這可能會延遲其他操作。系統表空間中的表不受影響。

.innodb_autoextend_increment變數定義了自動擴充套件的共享表空間檔案滿時的擴充套件大小(以MB為單位)，但不適用於file-per-table表空間檔案，不管是否設定了innodb_autoextend_increment，這些檔案都是自動擴充套件的。最初的擴充套件是少量的，之後擴充套件以4MB的增量出現。

啟用與禁用file-per-table表空間
innodb_file_per_table選項預設是啟用的。

為了在啟動時設定innodb_file_per_table選項，可以在啟動服務時使用--innodb_file_per_table命令列選項或者在my.cnf檔案中[mysqld]部分增加以下一行內容:

[mysqld]
innodb_file_per_table=1

你也可以在伺服器執行時動態設定innodb_file_per_table:

mysql> SET GLOBAL innodb_file_per_table=1;
Query OK, 0 rows affected (0.00 sec)

啟用innodb_file_per_table時，可以將InnoDB表儲存在tbl_name.ibd檔案。不像MyISAM儲存引擎，它有單獨的tbl_name.MYD和tbl_name.MYI檔案用於索引和資料，InnoDB將資料和索引一起儲存在一個.ibd檔案中。仍然像往常一樣建立tbl_name.frm檔案。

如果在啟動選項中禁用innodb_file_per_table並重啟伺服器或者使用set global命令來禁用它，除非你顯式的使用create table ... tablespace選項將表存放在file-per-table表空間或通用表空間否則innodb將在系統表空間建立新表。

你總是可以讀取和寫入任何InnoDB表，不管file-per-table設定情況。

如果要將表從系統表空間移動到自己的表空間，需要修改innodb_file_per_table的設定，然後重新建立表:

mysql> SET GLOBAL innodb_file_per_table=1;
mysql> ALTER TABLE table_name ENGINE=InnoDB;

使用CREATE TABLE…TABLESPACE或ALTER TABLE…TABLESPACE語法新增表到系統表空間不受innodb_file_per_table設定的影響。要將這些表從系統表空間移動到file-per-table表空間，必須使用ALTER TABLE…TABLESPACE語法。

InnoDB總是需要系統表空間，因為它把它的內部資料字典和undo日誌放在那裡。.ibd檔案不夠InnoDB操作。

當一個表從系統表空間移到它自己的.ibd檔案時，組成系統表空間的資料檔案保持相同的大小。InnoDB表以前佔用的空間可以被新的InnoDB資料重用，但是不會被作業系統回收使用。當將較大的InnoDB表移出系統表空間(磁碟空間有限)時，你可能更喜歡啟用innodb_file_per_table並使用mysqldump命令重新建立整個例項。如上所述，使用CREATE TABLE…TABLESPACE或者ALTER TABLE…表空間語法不受innodb_file_per_table設定的影響。這些桌子必須單獨移動。

在資料目錄外建立逐file-per-table表空間
要在MySQL資料目錄之外的特定位置建立一個新的InnoDB file-per-table表空間，使用有data directory = absolute_path_to_directory子句的CREATE TABLE語句的來實現。

提前計劃位置，因為您不能在ALTER TABLE語句中使用DATA DIRECTORY子句。您指定的目錄可以位於具有特定效能或容量特徵的另一個儲存裝置上，例如快速SSD或大容量HDD。

在目標目錄中，MySQL建立一個與資料庫名稱對應的子目錄，在該子目錄中為新表建立一個.ibd檔案。在MySQL DATADIR目錄下的資料庫目錄中，MySQL建立了一個table_name.Isl檔案包含表路徑名。.isl檔案被MySQL視為一個符號連結。(InnoDB表不支援使用實際的符號連結。)

下面的例子演示瞭如何在MySQL資料目錄外建立一個file-per-table表空間。它顯示了在指定目錄中建立的.ibd，以及在MySQL資料目錄下的資料庫目錄中建立的.isl。

mysql> use mysql
Database changed
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)
mysql> create table t_cs(c1 int primary key) data directory='/data';
Query OK, 0 rows affected (0.22 sec)
[root@localhost mysql]# pwd
/data/mysql
[root@localhost mysql]# ls -lrt
總用量 96
-rw-r-----. 1 mysql mysql 98304 3月   8 16:09 t_cs.ibd
[root@localhost mysql]# ls -lrt t_cs*
-rw-r-----. 1 mysql mysql 8556 3月   8 16:09 t_cs.frm
-rw-r-----. 1 mysql mysql   20 3月   8 16:09 t_cs.isl

你也可以使用CREATE TABLE…TABLESPACE與DATA DIRECTORY子句結合，在MySQL資料目錄之外建立一個file-per-table表空間。為此，你必須指定innodb_file_per_table作為表空間名。

mysql> create table t_cs_3(c1 int primary key) tablespace=innodb_file_per_table data directory='/data';
Query OK, 0 rows affected (0.28 sec)
[root@localhost mysql]# ls -lrt t_cs_2*
-rw-r-----. 1 mysql mysql 98304 3月   8 16:14 t_cs_2.ibd
[root@localhost mysql]# ls -lrt t_cs_2*
-rw-r-----. 1 mysql mysql 8556 3月   8 16:14 t_cs_2.frm
-rw-r-----. 1 mysql mysql   22 3月   8 16:14 t_cs_2.isl

使用這個方法時，你不需要啟用innodb_file_per_table。

使用說明:
.MySQL最初儲存的.ibd檔案是開啟的，防止您解除安裝裝置，但如果伺服器繁忙，可能最終會關閉表。小心不要在MySQL執行時意外地解除安裝外部裝置，或者在裝置斷開連線時啟動MySQL。當關聯的.ibd檔案丟失時，試圖訪問表會導致嚴重錯誤，需要重新啟動伺服器。

如果.ibd檔案仍然不在預期的路徑上，伺服器重啟可能會失敗。在本例中，手動刪除資料庫目錄中的table_name.isl檔案重新啟動後，執行DROPTABLE命令刪除.frm檔案，並從資料字典中刪除該表的資訊。

.在將表存放在NFS掛載的捲上之前，請檢視在使用NFS和MySQL中列出的潛在問題。

.如果您使用LVM快照、檔案複製或其他基於檔案的機制來備份.ibd檔案，請始終使用FLUSH TABLES…FOR EXPORT語句，以確保在備份發生之前將快取在記憶體中的所有更改重新整理到磁碟。

.DATA DIRECTORY子句是一種支援替代符號連結的方法，符號連結一直存在問題，從來沒有被支援用於單獨的InnoDB表。