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

前言：學(xué)習(xí)mysql的時候總是習(xí)慣性的和oracle數(shù)據(jù)庫進行比較。在學(xué)習(xí)mysql InnoDB的存儲結(jié)構(gòu)的時候也免不了跟oracle進行比較。Oracle的數(shù)據(jù)存儲有表空間、段、區(qū)、塊、數(shù)據(jù)文件；mysql InnoDB的存儲管理也類似，但是mysql增加了一個共享表空間和獨立表空間的概念；

一、概念

共享表空間： Innodb的所有數(shù)據(jù)保存在一個單獨的表空間里面，而這個表空間可以由很多個文件組成，一個表可以跨多個文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。從Innodb的官方文檔中可以看到，其表空間的最大限制為64TB，也就是說，Innodb的單表限制基本上也在64TB左右了，當(dāng)然這個大小是包括這個表的所有索引等其他相關(guān)數(shù)據(jù)。

獨立表空間：

二、查看數(shù)據(jù)庫的表空間

l 表空間有四個文件組成：ibdata1、ibdata2、ibdata3、ibdata4，每個文件的大小為10M，當(dāng)每個文件都滿了的時候，ibdata4會自動擴展；

l 當(dāng)前的存儲空間滿的時候，可以在其他的磁盤添加數(shù)據(jù)文件，語法如下：語法如下所示：

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

如果用 autoextend 選項描述最后一個數(shù)據(jù)文件，當(dāng) InnoDB 用盡所有表自由空間后將會自動擴充最后一個數(shù)據(jù)文件，每次增量為 8 MB。示例：

不管是共享表空間和獨立表空間，都會存在innodb_data_file文件，因為這些文件不僅僅要存放數(shù)據(jù)，而且還要充當(dāng)著類似于ORACLE的UNDO表空間等一些角色。

三、共享表空間優(yōu)缺點

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

3.1 共享表空間的優(yōu)點

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

數(shù)據(jù)和文件放在一起方便管理。

3.2 共享表空間的缺點

所有的數(shù)據(jù)和索引存放到一個文件，雖然可以把一個大文件分成多個小文件，但是多個表及索引在表空間中混合存儲，當(dāng)數(shù)據(jù)量非常大的時候，表做了大量刪除操作后表空間中將會有大量的空隙，特別是對于統(tǒng)計分析，對于經(jīng)常刪除操作的這類應(yīng)用最不適合用共享表空間。

共享表空間分配后不能回縮：當(dāng)出現(xiàn)臨時建索引或是創(chuàng)建一個臨時表的操作表空間擴大后，就是刪除相關(guān)的表也沒辦法回縮那部分空間了（可以理解為oracle的表空間10G，但是才使用10M，但是操作系統(tǒng)顯示mysql的表空間為10G），進行數(shù)據(jù)庫的冷備很慢；

四、獨立表空間的優(yōu)缺點

4.1 獨立表空間的優(yōu)點

每個表都有自已獨立的表空間，每個表的數(shù)據(jù)和索引都會存在自已的表空間中，可以實現(xiàn)單表在不同的數(shù)據(jù)庫中移動。

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

Drop table操作自動回收表空間，如果對于統(tǒng)計分析或是日值表，刪除大量數(shù)據(jù)后可以通過:alter table TableName engine=innodb;回縮不用的空間。

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

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

4.2 獨立表空間的缺點

單表增加過大，當(dāng)單表占用空間過大時，存儲空間不足，只能從操作系統(tǒng)層面思考解決方法；

五、共享表空間和獨立表空間之間的轉(zhuǎn)換

5.1 查看當(dāng)前數(shù)據(jù)庫的表空間管理類型

腳本：show variables like "innodb_file_per_table";

ON代表獨立表空間管理，OFF代表共享表空間管理；（查看單表的表空間管理方式，需要查看每個表是否有單獨的數(shù)據(jù)文件）

5.2 修改數(shù)據(jù)庫的表空間管理方式

修改innodb_file_per_table的參數(shù)值即可，但是修改不能影響之前已經(jīng)使用過的共享表空間和獨立表空間；

innodb_file_per_table=1 為使用獨占表空間

innodb_file_per_table=0 為使用共享表空間

5.3共享表空間轉(zhuǎn)化為獨立表空間的方法（參數(shù)innodb_file_per_table=1需要設(shè)置）
單個表的轉(zhuǎn)換操作，腳本：alter table table_name engine=innodb;

當(dāng)有大量的表需要操作的時候，先把數(shù)據(jù)庫導(dǎo)出，然后刪除數(shù)據(jù)再進行導(dǎo)入操作，該操作可以用mysqldump進行操作（http://blog.itpub.net/12679300/viewspace-1259451/）

總結(jié)：經(jīng)過以上操作便完成數(shù)據(jù)庫的存儲空間的轉(zhuǎn)換，了解技術(shù)是為了更好的利用技術(shù)，當(dāng)數(shù)據(jù)量很小的時候建議使用共享表空間的管理方式。數(shù)據(jù)量很大的時候建議使用獨立表空間的管理方式。

******************************************************************************************************

本文作者：JOHN QQ：1916066696 （請備注數(shù)據(jù)庫）

ORACLE技術(shù)博客：
ORACLE 獵人筆記 http://blog.itpub.net/12679300/

********************************************************************************************************************