MySQL Server參數優化 - innodb_file_per_table（獨立表空間）

1 簡介

    Innodb存儲引擎可将所有資料存放于ibdata*的共享表空間，也可将每張表存放于獨立的.ibd檔案的獨立表空間。

    共享表空間以及獨立表空間都是針對資料的存儲方式而言的。

    共享表空間:  某一個資料庫的所有的表資料，索引檔案全部放在一個檔案中，預設這個共享表空間的檔案路徑在data目錄下。 預設的檔案名為:ibdata1  初始化為10M。

    獨立表空間:  每一個表都将會生成以獨立的檔案方式來進行存儲，每一個表都有一個.frm表描述檔案，還有一個.ibd檔案。 其中這個檔案包括了單獨一個表的資料内容以及索引内容，預設情況下它的存儲位置也是在表的位置之中。

2 二者優缺點

    2.1 共享表空間：

    優點：

    可以将表空間分成多個檔案存放到各個磁盤上（表空間檔案大小不受表大小的限制，如一個表可以分布在不同的檔案上）。資料和檔案放在一起友善管理。

    缺點：

    所有的資料和索引存放到一個檔案中，雖然可以把一個大檔案分成多個小檔案，但是多個表及索引在表空間中混合存儲，這樣對于一個表做了大量删除操作後表空間中将會有大量的空隙，特别是對于統計分析，日值系統這類應用最不适合用共享表空間。

    2.2 獨立表空間：

    在配置檔案（my.cnf）中設定： innodb_file_per_table

    1.每個表都有自已獨立的表空間。

    2.每個表的資料和索引都會存在自已的表空間中。

    3.可以實作單表在不同的資料庫中移動。

    4.空間可以回收（除drop table操作處，表空不能自已回收）

        a.Drop table操作自動回收表空間，如果對于統計分析或是日值表，删除大量資料後可以通過:alter table TableName engine=innodb;回縮不用的空間。

        b.對于使innodb-plugin的Innodb使用turncate table也會使空間收縮。

        c.對于使用獨立表空間的表，不管怎麼删除，表空間的碎片不會太嚴重的影響性能，而且還有機會處理。

    單表增加過大，如超過100個G。

    相比較之下，使用獨占表空間的效率以及性能會更高一點。

3 獨立表空間參數innodb_file_per_table詳解

    3.1參數清單：

Command-Line Format --innodb_file_per_table

System Variable Name innodb_file_per_table

Variable Scope Global

Dynamic Variable Yes

Permitted Values (<= 5.6.5) Type boolean

Default OFF

Permitted Values (>= 5.6.6) Type boolean

Default ON

    3.2 參數功能

    該參數在mysql5.6.6及其後續版本預設開啟，開啟該參數的時候，Innodb将每個新建立的表的資料及索引存儲在一個獨立的.ibd檔案裡，而不是系統的表空間。當這些innodb表被删除或清空的時候，存儲空間會被回收。

    開啟innodb_file_per_table，在ALTER TABLE操作重建表的情況下，會将innodb表從系統共享表空間移動到獨立的.ibd檔案。

    不開啟innodb_file_per_table，innodb會将所有表及索引的資料存儲在構成系統表空間的ibdata檔案。這樣會降低檔案系統操作的性能開銷，例如DROP TABLE 或 TRUNCATE TABLE。它最适合于将整合磁盤都用于存儲mysql資料的情況，因為系統表空間不會收縮，所有的資料庫都在一個空間執行個體裡面。當innodb_file_per_table=OFF的時候，應避免在空間受限的系統表空間裡導入大量臨時資料。

    innodb_file_per_table 可通過SET GLOBAL動态的修改為ON或OFF，也可以在my.cnf中做永久性修改，在my.cnf中修改後生效的話需要重新開機mysqld服務。

    注：動态修改後僅對後續操作生效，如原來為共享表空間，動态修改為獨立表空間後僅建立的表為獨立表空間。

4 innodb共享表空間轉化為獨立表空間

    檢視目前表空間情況:

    表示目前是共享表空間。

    想要将共享表空間轉化為獨立表空間有兩種方法：

    1.先邏輯備份，然後修改配置檔案my.cnf中的參數innodb_file_per_table參數為1，重新開機服務後将邏輯備份導入即可。

    2.修改配置檔案my.cnf中的參數innodb_file_per_table參數為1，重新開機服務後将需要修改的所有innodb表都執行一遍：alter table table_name engine=innodb;

    使用第二種方式修改後，原來庫中的表中的資料會繼續存放于ibdata1中，建立的表才會使用獨立表空間