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

lhrbest發表於2017-09-03
MySql


官網：https://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html


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

image

一、概念

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

獨立表空間：

二、檢視資料庫的表空間

mysql> show variables like 'innodb_data%';

image


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";

image


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://blog.itpub.net/12679300/viewspace-1259451/）

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

Advantages of File-Per-Table Mode

1 當truncate或者drop一個表時可以釋放磁碟空間。如果不是獨立表空間，truncate或drop一個表只是在ibdata檔案內部釋放，實際ibdata檔案並不會縮小，釋放出來的空間也只能讓其他innodb引擎的表使用（有時這會造成磁碟空間容量監控的一個痛點，無法判斷它到底有多滿）

2 獨立表空間下，truncate table操作會更快

3 獨立表空間下，可以自定義表的儲存位置，通過CREATE TABLE ... DATA DIRECTORY =absolute_path_to_directory命令實現（有時將部分熱表放在不同的磁碟可有效地提升IO效能）

4 獨立表空間下，可以回收表空間碎片（比如一個非常大的delete操作之後釋放的空間），alter table xxx engine=innodb

5 可以移動單獨的innodb表，而不是整個database

6 可以copy單獨的innodb表從一個例項到另外一個例項（也就是transportable tablespace特色）

7 獨立表空間模式下，可以使用Barracuda的檔案格式，這個檔案格式有壓縮和動態行模式的特色。這個當表中有blob或者text欄位的話，動態行模式（dynamic row format）可以發揮出更高效的儲存。

8 獨立表空間模式下，可以更好的改善故障恢復，比如更加節約時間或者增加崩潰後正常恢復的機率。

9 單獨備份和恢復某張表的話會更快

10 可以使得從一個備份中單獨分離出表，比如一個lvm的快照備份

11 可以在不訪問MySQL的情況下方便地得知一個表的大小，即在檔案系統的角度上檢視。

12 在大部分的linux檔案系統中，如果innodb_flush_method為O_DIRECT，通常是不允許針對同一個檔案做併發寫操作的。這時如果為獨立表空間模式的話，應該會有較大的效能提升。

13 如果沒有獨立表空間模式，那麼所有的東西都在共享表空間，而共享表空間是有64T的容量上限的（這點比較扯淡）

Potential Disadvantages of File-Per-Table Mode

1 獨立表空間模式下，每個表或許會有很多沒用到的磁碟空間，因為初始表空間大小是固定的吧，有些表根本沒使用都這麼多。如果沒做好管理，可能會造成較大的空間浪費。

2 fsync操作必須執行在每一個單一的檔案上，獨立表空間模式下，多個表的寫操作就無法合併為一個單一的IO，這樣就新增許多額外的fsync操作

3 mysqld必須保證每個表都有一個open file，獨立表空間模式下，這樣就需要很多開啟檔案數，可能會影響效能

4 當drop一個表空間時，buffer pool會被掃描，如果buffer pool有幾十G那麼大，或許要花費幾秒鐘時間。這個掃描操作還會產生一個內部鎖，可能會延遲其他操作，共享表空間模式下不會有這個問題。

5 如果許多表都增長迅速，那麼可能會產生更多的分裂操作（應該指的是表空間大小的擴充），這個操作會損害drop table和table scan的效能。

6 innodb_autoextend_increment引數對獨立表空間無效，這個引數指的是當系統表空間滿了以後，它再次預先申請的磁碟空間大小，單位為M

 

Copying Tablespaces to Another Server(Transportable Tablespaces)

Transportable Tablespaces特性可以處理以下幾個痛點

1 跑報表，單獨拎出需要的幾個表到新的例項上跑報表，可以保證報表產生巨大伺服器開銷不影響到現網服務

2 通過這個特性快速地建立一個新的slave

3 在當前庫發生一個故障後備份一個該表的臨時版本

4 一種比mysqldump更快的moving data的方式

5 可以將部分表移交到一個更合適的硬體環境下，比如小而操作頻繁的放在SSD下，大而操作不頻繁的放在HDD

Tablespace Copying Limitations and Usage Notes(Transportable Tablespaces)

1 必須是在獨立表空間模式下

2 在遷移過程中，涉及到的表只讀不可寫

3 兩邊的page size必須保持一致（innodb_page_size）

4 DISCARD TABLESPACE操作不一致分割槽表，否則報錯ERROR 1031 (HY000): Table storage engine for 'part' doesn'thave this option.

5 DISCARD TABLESPACE時，如果foreign_key_checks為1，那麼不支援存在外來鍵約束的表

6 ALTER TABLE ... IMPORT TABLESPACE操作不會去檢查外來鍵約束

7 ALTER TABLE ... IMPORT TABLESPACE並不需要.cfg的後設資料檔案來匯入一個表空間。但是，這樣的話就沒有後設資料檢查了，同時會給出一個waring

Message: InnoDB: IO Read error: (2, No suchfile or directory) Error opening '.\
test\t.cfg', will attempt to import without schema verification
1 row in set (0.00 sec)

如果確認schema沒有配錯，那麼不用.cfg檔案可能更方便簡潔，並且有利於後設資料無法在.ibd檔案裡收集的故障恢復。

8 主從環境下，master和slave都必須設定為獨立表空間模式

Example Procedure: Copying a Tablespace From One Server ToAnother (Transportable Tablespaces)

1 在源server上，建立一個測試表

mysql> use test;
mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

2 在目標server上，同樣建立出相同表結構的表

mysql> use test;
mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

3 在目標server上，discard掉該表的表空間

mysql> ALTER TABLE t DISCARD TABLESPACE;

4 在源server上，執行FLUSH TABLES ... FOR EXPORT命令來靜默這張表，並建立出.cfg後設資料檔案（.cfg檔案建立在innodb的data目錄）

mysql> use test;
mysql> FLUSH TABLES t FOR EXPORT;

5 複製源server上的.ibd和.cfg檔案到目標server上

shell> scp /path/to/datadir/test/t.{ibd,cfg}destination-server:/path/to/datadir/test

6 在源server上，使用unlock tables命令來釋放flush tables..for export命令上的鎖

mysql> use test;
mysql> UNLOCK TABLES;

7 在目標server上，匯入表空間

mysql> use test;
mysql> ALTER TABLE t IMPORT TABLESPACE;

Tablespace Copying Internals (Transportable Tablespaces)

ALTER TABLE ... DISCARD TABLESPACE做了下面這兩件事

1 給table加X鎖

2 分離出表空間

FLUSH TABLES ... FOR EXPORT做了以下幾件事

1 給table加S鎖

2 停止purge執行緒

3 髒頁重新整理到磁碟

4 表的後設資料寫到.cfg檔案

Unlock tables做了以下幾件事

1 刪除.cfg檔案

2 釋放之前的共享鎖，並重啟purge執行緒

ALTER TABLE ... IMPORT TABLESPACE做了以下幾件事

1 檢查表空間內的每個頁是否有損壞

2 更新每頁上的space ID和LSNs

3 更新開頭頁的有效標誌位和LSN

4 更新btree頁

5 將頁狀態改為“髒頁”，重新整理到磁碟

Storing InnoDB Undo Logs in SeparateTablespaces

  5.6.3以前，innodb undo log存放在系統表空間，5.6.3以後可將undo log從系統表空間中分離出來。這樣帶來的好處是，可將undo放到SSD盤提升效能。這個特性主要由這幾個引數實現

innodb_undo_tablespaces：undo logs建立的表空間檔案數量，預設為0，表示存放在系統表空間，最大值為126

innodb_undo_directory：undo logs存放的路徑，預設為.表示和其他日誌檔案一致

innodb_undo_logs：系統表空間中在一個事務裡最大使用到的回滾段數量，預設為最大值128

分離步驟如下

1 決定undo logs的存放路徑，修改innodb_undo_directory引數

2 決定一個非0的innodb_undo_logs值，可以通過壓測決定到底取啥值，起始值設定小一些

3 決定一個非0的innodb_undo_tablespaces值

4 利用上述配置建立出新的mysql例項

5 壓測IO負載情況

6 壓測期間慢慢增大innodb_undo_logs，找到最合適的值

7 上生產環境下再測試觀察

Changing the Number or Size of InnoDB LogFiles and Resizing the InnoDB Tablespace

Changing the Number or Size of InnoDB Log Files

1 SET GLOBAL innodb_fast_shutdown = 1;

2 正常關閉mysqld程式，確保正常關閉

3 備份舊的log files

4 刪除舊的log files

5 修改my.cnf配置

6 重啟mysqld，確保啟動後log files的數量和新配置一致

5.6.8以後的版本，不再需要innodb_fast_shutdown，也不用刪除舊的log files。

Increasing the Size of the InnoDB Tablespace

由innodb_data_file_path引數的autoextend引數控制就好，表空間會自動增長。預設每次增長為8M，系統表空間每次增長量可由引數innodb_autoextend_increment控制。如果要兩個系統表空間檔案，一個固定大小，一個自增，可以這麼弄（建議一個就好，不要這麼搞，有點亂，不好管理）

innodb_data_file_path =/ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

Decreasing the Size of the InnoDB Tablespace

獨立表空間回收磁碟空間，只要執行alter table xx engine=innodb就行，系統表空間回收步驟如下

1 使用mysqldump dump出所有的表

2 關閉mysql

3 刪除整個data目錄，包括ibdata檔案和log file

4 啟動mysql

5 匯入dump檔案

Mysql Innodb的兩種表空間方式

要說表空間，Mysql的表空間管理遠遠說不上完善。換句話說，事實上Mysql根本沒有真正意義上的表空間管理。Mysql的Innodb包含兩種表空間檔案模式，預設的共享表空間和每個表分離的獨立表空間。只要在my.cnf裡面增加innodb_file_per_table=1就可以從共享表空間切換到獨立表空間。當然對於已經存在的表，則需要執行alter table MY_TABLE engine=innodb命令遷移資料。

共享表空間方式

由於是預設的方式，就暫且理解為Mysql官方推薦的方式。相對而言所有的資料都在一個（或幾個）檔案中，比較利於管理，而且在操作的時候只需要open這一個（或幾個）檔案即可，相對來說代價很低。

但問題是在資料達到以G為單位來計算的時候優劣逆轉。一個大小驚人的檔案很不利於管理，而且對於一個如此巨大的檔案來說，讀寫它需要耗費的資源一樣巨大。更加令人費解的是，MySQL竟然將索引和資料儲存於同一個檔案中，索引和資料之間尚存在資源爭用，不利於效能的提升。你當然可以通過innodb_data_file_path的配置規劃多個表空間檔案，但MySQL的邏輯是“用滿後增加”，僅僅是一個檔案的拆分而已，不能從根本上分離資料和索引。

之前曾經遭遇到700G以上的表空間檔案，而且更加讓人鬱悶的是對於如此大的檔案還在以每天數G的數量增加。由於無法停機，即便是拷貝一下也要花費差不多一夜，只能眼睜睜看著它繼續增大而毫無保守可行的辦法。

獨立表空間方式

相對而言對立表空間每個表都有獨立的多個資料檔案，而且做到了索引和資料的分離。多個小檔案之間很方便的完成跨資料庫甚至跨硬體的資料拷貝和遷移。相對來說靈活性很好。

這樣做同樣帶來另一個方面的問題。當資料庫中的表數量達到一定級別時，每次操作所涉及的檔案過多，如果按照預設Centos的ulimit -n = 1024的話，僅僅只能保證同時開啟256個表以內，這在習慣上“拆庫拆表”的MySQL資料結構上很難達到要求。尚且這種資料檔案的利用率不算很高，當大量“不高”的檔案集中起來，浪費的空間也很驚人，更何況最後可能出現的狀況不是“一堆K級別的小檔案”而是“一堆G級別的大檔案”，有點適得其反的意思。你自然可以聯想到分割槽表，又是一個“僅僅做檔案拆分而已”，多個分割槽檔案缺一不可。

之前同樣遇到過這個問題，MySQL連線大的狀況下大量的timeout，但主機負載還算可以，查了一圈才知道是open files限制的問題，限制一修改，負載變得驚人，但連線數卻又提升的不多。

總之，兩種方法各有所長，部分互補，但都不是解決問題的終極方案。期待MySQL能夠出現真正意義上表空間的概念，更加自由的規劃資料檔案。

對於innodb的資料儲存檔案，首先要解決兩個概念性的問題: 共享表空間以及獨佔表空間。(innodb引擎與MYISAM引擎的區別很大。特別是它的資料儲存方式等.)

1、共享表空間和獨佔表空間介紹

共享表空間以及獨佔表空間都是針對資料的儲存方式而言的。

共享表空間:  每一個資料庫的所有的表資料，索引檔案全部放在一個檔案中，預設這個共享表空間的檔案路徑在data目錄下。 預設的檔名為:ibdata1  初始化為10M。

獨佔表空間:  每一個表都將會生成以獨立的檔案方式來進行儲存，每一個表都有一個.frm表描述檔案，還有一個.ibd檔案(這個檔案包括了單獨一個表的資料內容以及索引內容)。


2、共享表空間和獨佔表空間的區別

共享表空間：
優點：
1）可以放表空間分成多個檔案存放到各個磁碟上（表空間檔案大小不受表大小的限制，如一個表可以分佈在不同的檔案上）。

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

2）表資料和表描述放在一起方便管理。 

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


獨立表空間（在配置檔案（my.cnf）中設定innodb_file_per_table=1）：

優點：
1）每個表都有自已獨立的表空間。
2）每個表的資料和索引都會存在自已的表空間中。
3）可以實現單表在不同的資料庫中移動。
4）空間可以回收。

對於使用獨立表空間的表，不管怎麼刪除，表空間的碎片不會太嚴重的影響效能，而且還有機會處理（表空不能自已回收）,處理方式如下：
Drop table操作自動回收表空間

如果對於統計分析或是日誌表，刪除大量資料後可以通過:alter table TableName engine=innodb;回縮不用的空間
對於使innodb-plugin的Innodb使用turncate table也會使空間收縮
5）使用獨佔表空間的效率以及效能會更高一點。

缺點：
1）單表增加過大，如超過100個G：

當使用獨享表空間來存放Innodb的表的時候，每個表的資料以一個單獨的檔案來存放，這個時候的單表限制，又變成檔案系統的大小限制了。

3、共享表空間以及獨佔表空間之間的轉化

修改獨佔空表空間配置，以下幾個引數必須在一起加入

innodb_data_home_dir = "/usr/local/MySQL/var/"  資料庫檔案所存放的目錄

innodb_log_group_home_dir = "/usr/local/mysql/var" 日誌存放目錄

innodb_data_file_path=ibdata1:10M:autoextend  設定配置一個可擴充套件大小的尺寸為10MB的單獨檔案（共享資料檔案），名為ibdata1。沒有給出檔案的位置，所以預設的是在MySQL的資料目錄內（如 /db/mysql/ibdata1）。

innodb_file_per_table=1  是否使用共享以及獨佔表空間（1 為使用獨佔表空間，0 為使用共享表空間）



innodb_file_per_table 通過這個引數來實現的轉化，如果為OFF說明所使用的是共享表空間【預設情況下，所使用的表空間為共享表空間】

innodb_file_per_table值來進行修改即可，但是對於之前使用過的共享表空間則不會影響，除非手動的去進行修改


注意：

InnoDB不建立目錄，所以在啟動伺服器之前請確認”所配置的路徑目錄”的確存在。

做資料的移植以及備份時，要注意資料檔案的完整性.

  • innodb_file_per_table

    Command-Line Format --innodb-file-per-table
    System Variable Name innodb_file_per_table
    Variable Scope Global
    Dynamic Variable Yes
    Permitted Values (<= 5.6.5) Type boolean
    Default OFF
    Permitted Values (>= 5.6.6) Type boolean
    Default ON

    When innodb_file_per_table is enabled (the default), InnoDB stores the data and indexes for each newly created table in a separate .ibd file instead of the system tablespace. The storage for these tables is reclaimed when the tables are dropped or truncated. This setting enables InnoDBfeatures such as table compression. See Section 14.7.4, “InnoDB File-Per-Table Tablespaces” for more information.

    Enabling innodb_file_per_table also means that an ALTER TABLE operation moves an InnoDB table from the system tablespace to an individual .ibd file in cases where ALTER TABLE rebuilds the table (ALGORITHM=COPY).

    When innodb_file_per_table is disabled, InnoDB stores the data for tables and indexes in the ibdata files that make up the system tablespace. This setting reduces the performance overhead of file system operations for operations such as DROP TABLE or TRUNCATE TABLE. It is most appropriate for a server environment where entire storage devices are devoted to MySQL data. Because the system tablespace never shrinks, and is shared across all databases in an instance, avoid loading huge amounts of temporary data on a space-constrained system when innodb_file_per_table is disabled. Set up a separate instance in such cases, so that you can drop the entire instance to reclaim the space.

    innodb_file_per_table is enabled by default as of MySQL 5.6.6. Consider disabling it if backward compatibility with MySQL 5.5 or 5.1 is a concern. This will prevent ALTER TABLE from moving InnoDB tables from the system tablespace to individual .ibd files.

    innodb_file_per_table is dynamic and can be set ON or OFF using SET GLOBAL. You can also set this option in the MySQL configuration file (my.cnf or my.ini) but this requires shutting down and restarting the server.

    Dynamically changing the value requires the SUPER privilege and immediately affects the operation of all connections. 





File-Per-Table的優點：
1.當表刪除或者truncate，空間可以被OS回收。
2.Truncate table在單個.ibd檔案上執行更快。
3.可以為每個表指定一個特定儲存。優化IO，空間管理。CREATE TABLE ... DATA DIRECTORY =absolute_path_to_directory 。
4.執行OPTIMEIZE TABLE，壓縮或者重建建立表空間。執行OPTIMIZE TABLE InnoDB會建立一個新的ibd檔案。當完成時，老的表空間會被新的代替。
5.可以移動單個表，不需要移動整個資料庫
6.可以把表複製到另外一個例項
7.innodb_file_per_table啟動後才能使用Barracuda檔案格式。
8.可以更有效的儲存帶BLOB，TEXT使用動態行模式的表。
9.使用innodb_file_per_table可以提高recovery的成功率，減少損壞錯誤發生恢復的時間。
10.可以快速的備份，恢復單個表。
11.innodb_file_per_table 可以從備份中去除一個表
12.innodb_file_per_table在備份和複製表時，容易觀察每個表的狀態。
13.可以通過檔案系統直接觀察表的大小。
14.當innodb_flish_method設定為O_DIRECT，通常linux檔案系統不允許併發的寫入同一個檔案。使用innodb_file_per_table就會有效能提升。
15.不啟用innodb_file_per_table，資料都會放在系統表空間中，最大64TB，如果使用innodb_file_per_table每個表可以64TB。

File-Per-Table的缺點：
1.表空間中的空間只能被這個表使用
2.fsync操作必須在每個表上都執行一遍
3.mysqld必須保持一個開啟的檔案控制程式碼，表太多會影響效能。
4.會消耗很多檔案描述
5.innodb_file_per_tablezhiyou 5.6.6或更高版本才能用，有向下相容問題。
6.如果很多表都增長，會出現檔案碎片問題。導致drop表和表掃描效能下降。
7.當drop表的時候會掃描buffer pool，如果太大會比較耗時。
8.innodb_autoextend_increment指定當檔案滿了之後增長的空間。

PS：innodb這種引擎,與MYISAM引擎的區別很大.特別是它的資料儲存格式等.對於innodb的資料結構,首先要解決兩個概念性的問題: 共享表空間以及獨佔表空間.

1、什麼是共享表空間和獨佔表空間

共享表空間以及獨佔表空間都是針對innodb表的資料儲存而言的,ibdata1為innodb引擎的儲存資料與索引的資料檔案,ib_logfile0與ib_logfile1為innodb引擎使用的日誌檔案
共享表空間: mysql伺服器中所有資料庫的innodb表(資料,索引)全部放在一個檔案中,預設這個共享表空間的檔案路徑在data目錄下. 預設的檔名為:ibdata1 初始化為10M.
獨佔表空間: 每一個表都將會生成以獨立的檔案方式來進行儲存,每一個表都有一個.frm表描述檔案,還有一個.ibd檔案. 其中這個檔案包括了單獨一個表的資料內容以及索引內容,預設情況下它的儲存位置也是在表的位置之中.

 

2、兩者之間的優缺點

  1. 共享表空間：

    優點：
    InnoDB在共享表空間模式下,是支援多檔案的,用innodb_data_file_path選項可以配置：
    innodb_data_file_path = /disk1/ibdata1:2G;/disk2/ibdata2:2G:autoextend
    這樣配置就把資料檔案分散在了disk1和disk2兩個路徑下,第一個檔案固定2G大小,第二個檔案初始化2G,可以自增長.（資料檔案大小不受表大小的限制,如一個表可以分佈在不同的資料檔案上）.資料和檔案放在一起方便管理.
    缺點：
    1.所有的資料和索引存放到一個檔案中意味著將有一個很常大的檔案,雖然可以把一個大檔案分成多個小檔案,但是伺服器上的所有庫中的innodb表及索引在表空間中混合儲存,這樣對於一個表做了大量刪除操作後表空間中將會有大量的空隙,特別是對於統計分析,日值系統這類應用最不適合用共享表空間.
    2.共享表空間不會收縮! ibdata1 只會一個勁的增長.對於一臺伺服器上面n個庫而且存在刪除庫的的情況就鬱悶了！

  2. 獨立表空間：

    在配置檔案（my.cnf）中設定： innodb_file_per_table

    優點：
    1． 每個表都有自已獨立的表空間.
    2． 每個表的資料和索引都會存在自已的表空間中.
    3． 可以實現單表在不同的資料庫中移動.
    4． 空間可以回收（除drop table操作處,表空不能自已回收）
    a) Drop table操作自動回收表空間,如果對於統計分析或是日值表,刪除大量資料後可以通過:alter table TableName engine=innodb;回縮不用的空間.
    b) 對於使innodb-plugin的Innodb使用turncate table也會使空間收縮.
    c) 對於使用獨立表空間的表,不管怎麼刪除,表空間的碎片不會太嚴重的影響效能,而且還有機會處理.
    缺點：
    單表增加過大,如超過100個G.
    對於單表增長過大的問題,如果使用共享表空間可以把檔案分開,但有同樣有一個問題,如果訪問的範圍過大同樣會訪問多個檔案,一樣會比較慢.對於獨立 表空間也有一個解決辦法是：使用分割槽表,也可以把那個大的表空間移動到別的空間上然後做一個連線.其實從效能上出發,當一個表超過100個G有可能響應也 是較慢了,對於獨立表空間還容易發現問題早做處理.

     相比較之下,使用獨佔表空間的效率以及效能會更高一點,推薦使用獨立表空間的原因：

  1. 從效能上對比共享表空間和獨立表空間：

    共享表空間在Insert操作上少有優勢.其它都沒獨立表空間表現好.這裡也有一個TIPS當啟用獨立表空間時,請合理調整一下：innodb_open_files .

  2. 從Linux系統處理上出發：

    檔案系統fsync一大片更新資料,對系統io衝擊較大.若分隔成多個小資料fsync,能夠減少對讀的影響. 同時從mysql程式碼,發現mysql保證兩次fsync之間至少有20ms的sleep,這樣的話,若將一次fsync變成多次小資料操作,應該能夠減 少慢查詢的比例.所以對於大量更新操作的系統不太適合用共享表空間

Innodb表空間

Innodb有兩種管理表空間的方法

　　獨立表空間：每一張表都會生成獨立的檔案來進行儲存，每一張表都有一個.frm表描述檔案，和一個.ibd檔案。其中ibd檔案包括了單獨一個表的資料內容和索引內容。

　　共享表空間：某一個資料庫的所有表的資料和索引檔案都放在一個檔案下，預設的檔案是.ibdata1檔案，初始值是10M，預設是存放在資料檔案的根目錄下（mysql/var）。

在效能和運維上獨立表空間比共享的表空間有很多優勢

 共享表空間

　　優點：

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

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

 　　缺點：

　　　　多個表及索引在表空間中混合儲存，這樣對於一個表做了大量刪除操作後表空間中將會有大量的空隙，特別是對於統計分析，日值系統這類應用最不適合用共享表空間。

　　　　共享表空間分配後不能回縮

　　　　進行資料庫的冷備很慢，mysqldump是一個好的處理方式

獨立空間

　　優點：　　

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

　　缺點：

　　　　單表增加過大，響應也是較慢，可以使用分割槽表

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

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

1.檢視當前表空間情況:

1
show variables like '%per_table';

ON代表獨立表空間管理，OFF代表共享表空間管理；

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

　　修改配置檔案 ，在/etc/my.cnf檔案中設定

　　　　innodb_file_per_table=1 為使用獨佔表空間

　　　　innodb_file_per_table=0 為使用共享表空間

　　重啟mysql

3.共享表空間轉化為獨立表空間的方法

　　（1）需要設定innodb_file_per_table=1

　　（2）單個表 

　　　　　　alter table table_name engine=innodb;

　　　　很多表

　　　　　　先把資料庫匯出，然後刪除資料再進行匯入操作，該操作可以用mysqldump進行操作

總結：

　　經過以上操作便完成資料庫的儲存空間的轉換

　　當資料量很小的時候建議使用共享表空間的管理方式。資料量很大的時候建議使用獨立表空間的管理方式 

 

innodb有兩種表空間，共享表空間和獨立表空間，表空間相關的兩個引數有：

MySQL> show variables like 'innodb_data%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir  |                        |
+-----------------------+------------------------+

 

innodb_data_home_dir指明資料檔案的父目錄，預設的是伺服器的資料目錄。

innodb_data_file_path指明表空間組成的資料檔案。

要重新配置表空間，需要先停止mysql伺服器，刪除變空間和日誌檔案，在使用新的配置重新啟動伺服器。

 

檢視innodb表空間剩餘大小

mysql> show table status like 'user'\G
*************************** 1. row ***************************
           Name: user
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 5
 Avg_row_length: 3276
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 33
    Create_time: 2015-02-10 17:48:21
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment: 使用者表
1 row in set (0.01 sec)

我這個在comment中沒有顯示，不知道是為什麼？？

在表空間滿的時候，可以新增資料檔案，在配置檔案中新增

innodb_data_file_path=ibdata1:79M;ibdata2:12M:autoextend，新增這個後，無法啟動mysql了，這個啟動的條件是什麼？

 

設定獨立表空間

使用--innodb-file-per-table選項啟動mysql，每個表有個。frm和.ibd檔案，對於之後建立的表起作用。

 

增加mysql InnoDB的表空間

2013年9月12日 默北發表評論閱讀評論 

上節說到了如何新增和調整innodb log檔案，然而調整表空間沒這麼簡單，需要將資料匯出來然後再匯入。對於共享的表空間，ibdata會變的非常大，即使刪除大量的資料，ibdata空間回收效果不明顯。

innodb_data_file_path
該變數設定innoDB資料檔案的位置和大小。指定的檔案必須大於10M，如果不受系統檔案限制，可以設定大於4G。該變數是MySQL伺服器容量規劃和效能擴充套件能力的核心要素。通常設定是建立一個資料目錄內容的基線大小，在10M到128M之間，第二個檔案設定為10M並自動擴充套件。如innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend。

innodb_file_per_table
有三種分配和管理表空間：
1. 原始分割槽的表空間也就是raw
2. 共享檔案表空間
3. 每個表單獨的表空間

使用innodb_file_per_table的好處有：
1. 如果使用軟連結將大表分配到不同的分割槽上，易於管理資料檔案
2. 易於監控解決IO資源使用的問題
3. 易於修復和恢復損壞的資料
3.1 相互獨立的，不會影響其他innodb表
3.2 匯出匯入只針對單個表，而不是整個共享表空間
4. 解決單個檔案大小的限制
5. 對於大量的delete操作，更易於回收磁碟空間
6. 碎片較少，易於整理optimize table
7. 易於安全審計
8. 易於備份

如果在innodb表已建立後設定innodb_file_per_table，那麼資料將不會遷移到單獨的表空間上，而是續集使用之前的共享表空間。
只有新建立的表才會分離到自己的表空間檔案。

innodb_file_per_table是靜態變數，更改後要重啟mysql服務才能生效。




增加表空間
當沒有使用innodb_file_per_table也沒有啟用自動擴充套件，那麼隨著資料的增長，表空間將滿了。在這情況下，需要新增額外的表空間來擴充套件容量。方法如下：
1. 停止mysql服務
2. 備份配置檔案，便於出現問題好回退
3. 編輯innodb_data_file_path值
根據你的環境更改ibdata1:$size;ibdataN:$size;…ibdataN:$size; 當前定義的表空間或預設表空間是不能改變的，否則啟動失敗，但是，可以額外的新增表空間，ibdataN序列根據當前的數量遞增，$size自定義。
4. 啟動mysql服務
6. 觀察mysql錯誤日誌是否有錯

如需轉載請註明出處：http://www.ttlsa.com/html/2961.html




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

相關文章