MySQL InnoDB 共享表空間和獨立表空間

前言：學習MySQL的時候總是習慣性的和Oracle資料庫進行比較。在學習MySQL InnoDB的儲存結構的時候也免不了跟Oracle進行比較。Oracle的資料儲存有表空間、段、區、塊、資料檔案；MySQL InnoDB的儲存管理也類似，但是MySQL增加了一個共享表空間和獨立表空間的概念；

一、概念

共享表空間： Innodb的所有資料儲存在一個單獨的表空間裡面，而這個表空間可以由很多個檔案組成，一個表可以跨多個檔案存在，所以其大小限制不再是檔案大小的限制，而是其自身的限制。從Innodb的官方文件中可以看到，其表空間的最大限制為64TB，也就是說，Innodb的單表限制基本上也在64TB左右了，當然這個大小是包括這個表的所有索引等其他相關資料。

獨立表空間：

二、檢視資料庫的表空間

mysql> show variables like 'innodb_data%';

 

表空間有四個檔案組成：ibdata1、ibdata2、ibdata3、ibdata4，每個檔案的大小為10M，當每個檔案都滿了的時候，ibdata4會自動擴充套件；

當前的儲存空間滿的時候，可以在其他的磁碟新增資料檔案，語法如下：語法如下所示：

pathtodatafile:sizespecification;pathtodatafile:sizespec;.;pathtodatafile:sizespec[:autoextend[:max:sizespecification]]

如果用 autoextend 選項描述最後一個資料檔案，當 InnoDB 用盡所有表自由空間後將會自動擴充最後一個資料檔案，每次增量為 8 MB。示例：

不管是共享表空間和獨立表空間，都會存在innodb_data_file檔案，因為這些檔案不僅僅要存放資料，而且還要充當著類似於ORACLE的UNDO表空間等一些角色。

三、共享表空間優缺點

既然Innodb有共享表空間和獨立表空間兩種型別，那麼這兩種表空間存在肯定都有時候自己的應用的場景，存在即合理。以下是摘自mysql官方的一些介紹：

3.1 共享表空間的優點

表空間可以分成多個檔案存放到各個磁碟，所以表也就可以分成多個檔案存放在磁碟上，表的大小不受磁碟大小的限制（很多文件描述有點問題）。

資料和檔案放在一起方便管理。

3.2 共享表空間的缺點

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

共享表空間分配後不能回縮：當出現臨時建索引或是建立一個臨時表的操作表空間擴大後，就是刪除相關的表也沒辦法回縮那部分空間了（可以理解為oracle的表空間10G，但是才使用10M，但是作業系統顯示mysql的表空間為10G），進行資料庫的冷備很慢；

四、獨立表空間的優缺點

4.1 獨立表空間的優點

每個表都有自已獨立的表空間，每個表的資料和索引都會存在自已的表空間中，可以實現單表在不同的資料庫中移動。

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

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

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

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

4.2 獨立表空間的缺點

單表增加過大，當單表佔用空間過大時，儲存空間不足，只能從作業系統層面思考解決方法；

五、共享表空間和獨立表空間之間的轉換

5.1 檢視當前資料庫的表空間管理型別

指令碼：show variables like "innodb_file_per_table";

mysql> show variables like "innodb_file_per_table";

 

ON代表獨立表空間管理，OFF代表共享表空間管理；（檢視單表的表空間管理方式，需要檢視每個表是否有單獨的資料檔案）

5.2 修改資料庫的表空間管理方式

修改innodb_file_per_table的引數值即可，但是修改不能影響之前已經使用過的共享表空間和獨立表空間；

innodb_file_per_table=1 為使用獨佔表空間

innodb_file_per_table=0 為使用共享表空間

5.3共享表空間轉化為獨立表空間的方法（引數innodb_file_per_table=1需要設定）
 單個表的轉換操作，指令碼：alter table table_name engine=innodb;

當有大量的表需要操作的時候，先把資料庫匯出，然後刪除資料再進行匯入操作，該操作可以用mysqldump進行操作（ http://www.linuxidc.com/Linux/2014-08/105949.htm ）

總結：經過以上操作便完成資料庫的儲存空間的轉換，瞭解技術是為了更好的利用技術，當資料量很小的時候建議使用共享表空間的管理方式。資料量很大的時候建議使用獨立表空間的管理方式。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15498/viewspace-2040189/，如需轉載，請註明出處，否則將追究法律責任。