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表。

MySQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。