MySQL InnoDB 共用資料表空間和獨立資料表空間

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進行操作（  ）

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

MySQL InnoDB儲存引擎鎖機制實驗

InnoDB儲存引擎的啟動、關閉與恢複

MySQL InnoDB獨立資料表空間的配置

MySQL Server 層和 InnoDB 引擎層 體繫結構圖

InnoDB 死結案例解析

MySQL Innodb獨立資料表空間的配置

本文永久更新連結地址：