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

2024-07-24 12:34:36

字體：大中小

來源：轉載

供稿：網友

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

　　一、概念

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

　　獨立表空間：

　　二、查看數據庫的表空間

　　mysql> show variables like 'innodb_data%';

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

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

　　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的參數值即可，但是修改不能影響之前已經使用過的共享表空間和獨立表空間；

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

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

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

（編輯：武林網）

上一篇：mysql5.1關于自動重連的一些改變

下一篇：MySQL5.7新特征之錯誤日志打印到操作系統日志文件