MySQL 中的共享表空間與獨立表空間如何選擇

導讀	對於 InnoDB 儲存引擎來說，它可以將每張表存放於獨立的表空間，即 tablename.ibd 檔案;也可以將資料存放於 ibdata 的共享表空間，一般命名是 ibdataX，後面的 X 是一個具體的數字。

我們今天就開始學習 MySQL 中另外一個非常重要的儲存引擎 InnoDB 了。

要說 InnoDB，我們就不得不先來說說另外一個比較重要的話題表空間和共享表空間，這也是我們本文的主題。

對於 InnoDB 儲存引擎來說，它可以將每張表存放於獨立的表空間，即 tablename.ibd 檔案;也可以將資料存放於 ibdata 的共享表空間，一般命名是 ibdataX，後面的 X 是一個具體的數字。

我們先來體驗看看這兩種有什麼區別。

首先我們執行如下程式碼可以檢視是否開啟了獨立表空間：

show variables like 'innodb_file_per_table';

可以看到，預設情況下，獨立表空間是開啟的。

這個時候我們來建立一個使用了 InnoDB 引擎的表，如下(預設儲存引擎就是 InnoDB)：

create table book(id int,name varchar(255));

建立完成後，我們來到存放資料檔案的目錄下，可以看到如下檔案：

可以看到，當前表被存放在獨立的 book.ibd 檔案中。

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

接下來我們執行如下程式碼，修改 innodb_file_per_table 的值為 OFF，即關閉獨立表空間，如下：

關閉之後，再來建立新表：

create table book2(id int,name varchar(255));

新表建立成功之後，此時我們再去表目錄下檢視檔案，發現並沒有一個名為 book2.ibd 的檔案，說明此時的表已經存放於共享表空間了。

那麼共享表空間的 ibdata1 檔案又在哪裡呢?其實就在 MySQL 存放資料庫的目錄下：

我們可以透過如下 檢視 ibdata1 的預設大小：

show variables like 'innodb_data_file_path';

可以看到，預設大小是 12M。

需要注意的是，innodb_file_per_table 要在建立表之前修改，建立表之後再去修改，是不會影響已有的表結構的。

要搞明白要用哪一種表空間，我們得先明白兩種不同表空間各自的特點。

2.1 獨立表空間

優勢

1. 每張表都有自己獨立的表空間。
2. 每張表的資料和索引都會儲存在自己的表空間中。
3. 可以實現單表在不同的資料庫中移動(因為每張表都有獨立的資料表檔案)。
4. 空間可以回收(透過 optimize table  實現)。
5. 無論怎麼刪除，表空間的碎片不會太嚴重影響系統效能。

缺點

1. 單表增加過大。

2.2 共享表空間

優勢

1. 可以將表空間分成多個檔案存放到各個磁碟上(表空間檔案大小不受表大小的限制，如一個表可以分佈在不同的檔案上)。
2. 資料和檔案放在一起方便管理。

缺點

1. 所有的資料和索引存放到一個檔案中，這將意味著有一個很大的檔案存在(雖然可以把一個大檔案分成多個小檔案)，但是多個表及索引在表空間中混合儲存，這樣當一個表做了大量刪除操作後表空間中將會有大量的空隙，特別是對於統計分析、日誌系統這類應用而言，最不適合用共享表空間(例如，當系統空間不夠用的時候，我們希望透過刪除一些無效資料來騰出來一些表空間，這個時候我們會發現，如果使用了共享表空間，即使無效資料刪除了，表空間還是還是不會縮小)。
2. 共享表空間管理會出現表空間分配後不能回縮的問題，當臨時建立索引或者臨時表導致表空間擴大後，就是刪除相關的表也沒辦法回縮那部分空間了。
3. 對於第二點存在的問題，一般使用 mysqldump 匯出資料，然後刪除共享表空間資料檔案後，再重新匯入。
4. 由於一個檔案中儲存了多個表資料，所以併發操作時可能會產生 IO 瓶頸，所以需要頻繁寫入的場景並不適合共享表空間。

經過以上的分析，相信小夥伴們已經明白了，在實際專案中，還是首選獨立表空間比較好，事實上，從 MySQL5.6 開始，獨立表空間就已經成為預設選項了。

最後還有一個問題，如果一開始建立的是共享表空間，那麼還能遷移到獨立表空間嗎?當然可以!參考如下四個步驟：

1. 使用 mysqldump 匯出所有資料庫表資料。
2. 停止 MySQL 服務，修改 innodb_file_per_table 引數，並刪除 InnoDB 相關檔案(如果是主從結構，則可以從 Slave 上入手完成這些操作)。
3. 重啟 MySQL 服務，重建 InnoDB 共享表空間(此時裡邊就沒有資料了)。
4. 重新匯入資料。

好啦，今天就和小夥伴們聊一聊共享表空間和獨立表空間，InnoDB 的其他玩法我們後面再繼續介紹～

原文來自：