MySQL共用資料表空間概念

標籤：div   檔案的   儲存空間   限制   動態   mysql5.6   適合   共用   修改   

1.資料表空間概念

Innodb儲存引擎可將所有資料存放於ibdata*的共用資料表空間，也可將每張表存放於獨立的.ibd檔案的獨立資料表空間.
共用資料表空間以及獨立資料表空間都是針對資料的儲存方式而言的。

• 共用資料表空間

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

• 獨立資料表空間

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

2. 二者優缺點

• 共用資料表空間

優點：可以將資料表空間分成多個檔案存放到各個磁碟上（資料表空間檔案大小不受表大小的限制，如一個表可以分布在不同的檔案上）。資料和檔案放在一起方便管理。

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

• 獨立資料表空間

優點：
1.每個表都有自已獨立的資料表空間。
2.每個表的資料和索引都會存在自已的資料表空間中。
3.可以實現單表在不同的資料庫中移動。
4.空間可以回收（除drop table操作處，表空不能自已回收）
a.Drop table操作自動回收資料表空間，如果對於統計分析或是日值表，刪除大量資料後可以通過: alter table TableName engine=innodb;回縮不用的空間。
b.對於使innodb-plugin的Innodb使用turncate table也會使空間收縮。
c.對於使用獨立資料表空間的表，不管怎麼刪除，資料表空間的片段不會太嚴重的影響效能，而且還有機會處理。
缺點：
1.單表增加過大，如超過100個G。
2.相比較之下，使用獨佔資料表空間的效率以及效能會更高一點。 3. 獨立資料表空間參數innodb_file_per_table詳解該參數在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服務。

mysql> set global innodb_file_per_table =ON;  mysql> show variables like ‘%per_table%‘;  +-----------------------+-------+  | Variable_name         | Value |  +-----------------------+-------+  | innodb_file_per_table | ON    |  +-----------------------+-------+

 Note：動態修改後僅對後續操作生效，如原來為共用資料表空間，動態修改為獨立資料表空間後僅建立的表為獨立資料表空間。

4. innodb共用資料表空間轉化為獨立資料表空間

查看當前資料表空間情況:

mysql> show variables like ‘%per_table‘;  +-----------------------+-------+  | Variable_name         | Value |  +-----------------------+-------+  | innodb_file_per_table | OFF   |  +-----------------------+-------+

 innodb_file_per_table參數值為OFF表示當前是共用資料表空間。
將共用資料表空間轉化為獨立資料表空間有如下兩種方法：

1.先邏輯備份，然後修改設定檔my.cnf中的參數 innodb_file_per_table = 1，重啟服務後將邏輯備份匯入即可。
2.修改設定檔my.cnf中的參數 innodb_file_per_table = 1，重啟服務後將需要修改的所有innodb表都執行一遍： alter table table_name engine=innodb;
Note：使用第二種方式修改之後，原來庫中的表中的資料會繼續存放於ibdata1中，建立的表才會使用獨立資料表空間。

MySQL共用資料表空間概念