概述
今天主要介紹一下mysql的表空間，不過MySQL沒有真正意義上的表空間管理。
MySQL的Innodb包含兩種表空間文件模式，默認的共享表空間和每個表分離的獨立表空間。
一般來說，當數據量很小的時候建議使用共享表空間的管理方式。數據量很大的時候建議使用獨立表空間的管理方式。
概述
今天主要介紹一下mysql的表空間，不過MySQL沒有真正意義上的表空間管理。
MySQL的Innodb包含兩種表空間文件模式，默認的共享表空間和每個表分離的獨立表空間。
一般來說，當數據量很小的時候建議使用共享表空間的管理方式。數據量很大的時候建議使用獨立表空間的管理方式。
01
查看錶空間模式
mysql> show variables like '%innodb_file_per_table%' ;
ON表示當前是獨立表空間，若為OFF，則表示為共享表空間。
同樣：1表示ON、0表示OFF
概述
今天主要介紹一下mysql的表空間，不過MySQL沒有真正意義上的表空間管理。
MySQL的Innodb包含兩種表空間文件模式，默認的共享表空間和每個表分離的獨立表空間。
一般來說，當數據量很小的時候建議使用共享表空間的管理方式。數據量很大的時候建議使用獨立表空間的管理方式。
01
查看錶空間模式
mysql> show variables like '%innodb_file_per_table%' ;
ON表示當前是獨立表空間，若為OFF，則表示為共享表空間。
同樣：1表示ON、0表示OFF
02
查看錶空間信息
select * from information_schema.global_variables where variable_name in ("datadir","innodb_data_file_path","innodb_data_home_dir","innodb_file_per_table","innodb_open_files");
- datadir表示數據文件的基本路徑。
- innodb_data_file_path表示共享文件的名稱。
- innodb_file_per_table表示表空間的類型。
- innodb_open_files最多可打開的文件個數。
- innodb_data_file_path文件格式：
- file_name:file_size:auto_extend:max_size
- file_name表示數據文件名稱、file_size表示數據文件大小、auto_extend表示自動擴展、max_size表示最大大小。
- innodb_data_file_path=ibdata1:10M:autoextend:max:500M
- ibdata1初始大小為10M、自動擴展且最大為500M。
多個標示符之間使用分號隔開，另外，只有最後一個文件能夠使用autoextend屬性。
03
共享表空間
Innodb的所有數據保存在一個單獨的表空間裡面，而這個表空間可以由很多個文件組成，一個表可以跨多個文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。從Innodb的官方文檔中可以看到，其表空間的最大限制為64TB，也就是說，Innodb的單表限制基本上也在64TB左右了，當然這個大小是包括這個表的所有索引等其他相關數據。
1、共享表空間的優點：
表空間可以分成多個文件存放到各個磁盤，所以表也就可以分成多個文件存放在磁盤上，表的大小不受磁盤大小的限制。
數據和文件放在一起方便管理。
2、共享表空間的缺點：
1）所有的數據和索引存放到一個文件，雖然可以把一個大文件分成多個小文件，但是多個表及索引在表空間中混合存儲，當數據量非常大的時候，表做了大量刪除操作後表空間中將會有大量的空隙，特別是對於統計分析，對於經常刪除操作的這類應用最不適合用共享表空間。
2）共享表空間分配後不能回縮：當出現臨時建索引或是創建一個臨時表的操作表空間擴大後，就是刪除相關的表也沒辦法回縮那部分空間了，進行數據庫的冷備很慢；
3、設置數據文件：
有兩種方式設置數據文件，一種是所有的數據文件均在一個目錄下，稱為同目錄數據文件。另一種是所有的數據文件在不同的目錄下，稱為異目錄數據文件。
1）設置同目錄數據文件：
[mysqld]
innodb_file_per_table=0
innodb_data_home_dir=/msdata/datas
innodb_data_file_path=ibdata1:50M;ibdata2:20M;ibdata3:50M:autoextend
通過innodb_data_home_dir設置一個共同的數據文件目錄。
2）設置異目錄數據文件：
[mysqld]
innodb_file_per_table=0
innodb_data_home_dir=
innodb_data_file_path=/msdata/datas/ibdata1:76M;/msdata/datas/ibdata2:10M;/msdata/datas/ibdata3:10M;/msdata/datas01/ibdata4:10M:autoextend
注意：“innodb_data_home_dir=”參數必須存在。同時，autoextend屬性只能用於最後一個文件。
04
獨立表空間
獨立表空間是每個表都有獨立的多個數據文件，而且做到了索引和數據的分離。
概述
今天主要介紹一下mysql的表空間，不過MySQL沒有真正意義上的表空間管理。
MySQL的Innodb包含兩種表空間文件模式，默認的共享表空間和每個表分離的獨立表空間。
一般來說，當數據量很小的時候建議使用共享表空間的管理方式。數據量很大的時候建議使用獨立表空間的管理方式。
01
查看錶空間模式
mysql> show variables like '%innodb_file_per_table%' ;
ON表示當前是獨立表空間，若為OFF，則表示為共享表空間。
同樣：1表示ON、0表示OFF
02
查看錶空間信息
select * from information_schema.global_variables where variable_name in ("datadir","innodb_data_file_path","innodb_data_home_dir","innodb_file_per_table","innodb_open_files");
- datadir表示數據文件的基本路徑。
- innodb_data_file_path表示共享文件的名稱。
- innodb_file_per_table表示表空間的類型。
- innodb_open_files最多可打開的文件個數。
- innodb_data_file_path文件格式：
- file_name:file_size:auto_extend:max_size
- file_name表示數據文件名稱、file_size表示數據文件大小、auto_extend表示自動擴展、max_size表示最大大小。
- innodb_data_file_path=ibdata1:10M:autoextend:max:500M
- ibdata1初始大小為10M、自動擴展且最大為500M。
多個標示符之間使用分號隔開，另外，只有最後一個文件能夠使用autoextend屬性。
03
共享表空間
Innodb的所有數據保存在一個單獨的表空間裡面，而這個表空間可以由很多個文件組成，一個表可以跨多個文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。從Innodb的官方文檔中可以看到，其表空間的最大限制為64TB，也就是說，Innodb的單表限制基本上也在64TB左右了，當然這個大小是包括這個表的所有索引等其他相關數據。
1、共享表空間的優點：
表空間可以分成多個文件存放到各個磁盤，所以表也就可以分成多個文件存放在磁盤上，表的大小不受磁盤大小的限制。
數據和文件放在一起方便管理。
2、共享表空間的缺點：
1）所有的數據和索引存放到一個文件，雖然可以把一個大文件分成多個小文件，但是多個表及索引在表空間中混合存儲，當數據量非常大的時候，表做了大量刪除操作後表空間中將會有大量的空隙，特別是對於統計分析，對於經常刪除操作的這類應用最不適合用共享表空間。
2）共享表空間分配後不能回縮：當出現臨時建索引或是創建一個臨時表的操作表空間擴大後，就是刪除相關的表也沒辦法回縮那部分空間了，進行數據庫的冷備很慢；
3、設置數據文件：
有兩種方式設置數據文件，一種是所有的數據文件均在一個目錄下，稱為同目錄數據文件。另一種是所有的數據文件在不同的目錄下，稱為異目錄數據文件。
1）設置同目錄數據文件：
[mysqld]
innodb_file_per_table=0
innodb_data_home_dir=/msdata/datas
innodb_data_file_path=ibdata1:50M;ibdata2:20M;ibdata3:50M:autoextend
通過innodb_data_home_dir設置一個共同的數據文件目錄。
2）設置異目錄數據文件：
[mysqld]
innodb_file_per_table=0
innodb_data_home_dir=
innodb_data_file_path=/msdata/datas/ibdata1:76M;/msdata/datas/ibdata2:10M;/msdata/datas/ibdata3:10M;/msdata/datas01/ibdata4:10M:autoextend
注意：“innodb_data_home_dir=”參數必須存在。同時，autoextend屬性只能用於最後一個文件。
04
獨立表空間
獨立表空間是每個表都有獨立的多個數據文件，而且做到了索引和數據的分離。
參數
1、獨立表空間的優點：
每個表都有自已獨立的表空間，每個表的數據和索引都會存在自已的表空間中，可以實現單表在不同的數據庫中移動。
空間可以回收（除drop table操作處，表空不能自已回收），Drop table操作自動回收表空間，如果對於統計分析或是日值表，刪除大量數據後可以通過:alter table TableName engine=innodb;回縮不用的空間。
對於使用獨立表空間的表，不管怎麼刪除，表空間的碎片不會太嚴重的影響性能，而且還有機會處理。
2、獨立表空間的缺點：
1）當數據庫中的表數量達到一定級別時，每次操作所涉及的文件過多，如果按照默認Centos的ulimit -n = 1024的話，僅僅只能保證同時打開256個表以內，這在習慣上“拆庫拆表”的MySQL數據結構上很難達到要求。
2）這種數據文件的利用率不算很高，當大量“不高”的文件集中起來，浪費的空間也很驚人，更何況最後可能出現的狀況不是“一堆K級別的小文件”而是“一堆G級別的大文件”，有點適得其反的意思。
3）獨立表空間數據存儲規則：
表結構定義文件：$datadir/$dbname/$tabname.frm
數據文件：$datadir/$dbname/$tabname.ibd
附：獨立表空間空間爆滿的問題
當獨立表空間的所處空間爆滿的時候，只能採用數據遷移的方式，將表空間移走。具體辦法是，將獨立表空間更改為共享表空間。
05
釋放表空間
方法一：optimize table table_name
這是我們經常見到的方法，這裡就不在過多介紹。
方法二：alter table table_name engine=engine_name
該方法通常用於切換表的引擎，例如MyISAM轉為InnoDB，但是同樣適用於釋放表空間，只不過切換後的引擎和原來的engine相同罷了。
注意：
- 1.這兩種方式都適用於我們常用的表，myisam和innodb。
- 2.優化表的時候會鎖表，數據表越大，耗時越長，因此不要在網站忙時進行表優化。
後面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注一下~
概述
今天主要介紹一下mysql的表空間，不過MySQL沒有真正意義上的表空間管理。
MySQL的Innodb包含兩種表空間文件模式，默認的共享表空間和每個表分離的獨立表空間。
一般來說，當數據量很小的時候建議使用共享表空間的管理方式。數據量很大的時候建議使用獨立表空間的管理方式。
01
查看錶空間模式
mysql> show variables like '%innodb_file_per_table%' ;
ON表示當前是獨立表空間，若為OFF，則表示為共享表空間。
同樣：1表示ON、0表示OFF
02
查看錶空間信息
select * from information_schema.global_variables where variable_name in ("datadir","innodb_data_file_path","innodb_data_home_dir","innodb_file_per_table","innodb_open_files");
- datadir表示數據文件的基本路徑。
- innodb_data_file_path表示共享文件的名稱。
- innodb_file_per_table表示表空間的類型。
- innodb_open_files最多可打開的文件個數。
- innodb_data_file_path文件格式：
- file_name:file_size:auto_extend:max_size
- file_name表示數據文件名稱、file_size表示數據文件大小、auto_extend表示自動擴展、max_size表示最大大小。
- innodb_data_file_path=ibdata1:10M:autoextend:max:500M
- ibdata1初始大小為10M、自動擴展且最大為500M。
多個標示符之間使用分號隔開，另外，只有最後一個文件能夠使用autoextend屬性。
03
共享表空間
Innodb的所有數據保存在一個單獨的表空間裡面，而這個表空間可以由很多個文件組成，一個表可以跨多個文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。從Innodb的官方文檔中可以看到，其表空間的最大限制為64TB，也就是說，Innodb的單表限制基本上也在64TB左右了，當然這個大小是包括這個表的所有索引等其他相關數據。
1、共享表空間的優點：
表空間可以分成多個文件存放到各個磁盤，所以表也就可以分成多個文件存放在磁盤上，表的大小不受磁盤大小的限制。
數據和文件放在一起方便管理。
2、共享表空間的缺點：
1）所有的數據和索引存放到一個文件，雖然可以把一個大文件分成多個小文件，但是多個表及索引在表空間中混合存儲，當數據量非常大的時候，表做了大量刪除操作後表空間中將會有大量的空隙，特別是對於統計分析，對於經常刪除操作的這類應用最不適合用共享表空間。
2）共享表空間分配後不能回縮：當出現臨時建索引或是創建一個臨時表的操作表空間擴大後，就是刪除相關的表也沒辦法回縮那部分空間了，進行數據庫的冷備很慢；
3、設置數據文件：
有兩種方式設置數據文件，一種是所有的數據文件均在一個目錄下，稱為同目錄數據文件。另一種是所有的數據文件在不同的目錄下，稱為異目錄數據文件。
1）設置同目錄數據文件：
[mysqld]
innodb_file_per_table=0
innodb_data_home_dir=/msdata/datas
innodb_data_file_path=ibdata1:50M;ibdata2:20M;ibdata3:50M:autoextend
通過innodb_data_home_dir設置一個共同的數據文件目錄。
2）設置異目錄數據文件：
[mysqld]
innodb_file_per_table=0
innodb_data_home_dir=
innodb_data_file_path=/msdata/datas/ibdata1:76M;/msdata/datas/ibdata2:10M;/msdata/datas/ibdata3:10M;/msdata/datas01/ibdata4:10M:autoextend
注意：“innodb_data_home_dir=”參數必須存在。同時，autoextend屬性只能用於最後一個文件。
04
獨立表空間
獨立表空間是每個表都有獨立的多個數據文件，而且做到了索引和數據的分離。
參數
1、獨立表空間的優點：
每個表都有自已獨立的表空間，每個表的數據和索引都會存在自已的表空間中，可以實現單表在不同的數據庫中移動。
空間可以回收（除drop table操作處，表空不能自已回收），Drop table操作自動回收表空間，如果對於統計分析或是日值表，刪除大量數據後可以通過:alter table TableName engine=innodb;回縮不用的空間。
對於使用獨立表空間的表，不管怎麼刪除，表空間的碎片不會太嚴重的影響性能，而且還有機會處理。
2、獨立表空間的缺點：
1）當數據庫中的表數量達到一定級別時，每次操作所涉及的文件過多，如果按照默認Centos的ulimit -n = 1024的話，僅僅只能保證同時打開256個表以內，這在習慣上“拆庫拆表”的MySQL數據結構上很難達到要求。
2）這種數據文件的利用率不算很高，當大量“不高”的文件集中起來，浪費的空間也很驚人，更何況最後可能出現的狀況不是“一堆K級別的小文件”而是“一堆G級別的大文件”，有點適得其反的意思。
3）獨立表空間數據存儲規則：
表結構定義文件：$datadir/$dbname/$tabname.frm
數據文件：$datadir/$dbname/$tabname.ibd
附：獨立表空間空間爆滿的問題
當獨立表空間的所處空間爆滿的時候，只能採用數據遷移的方式，將表空間移走。具體辦法是，將獨立表空間更改為共享表空間。
05
釋放表空間
方法一：optimize table table_name
這是我們經常見到的方法，這裡就不在過多介紹。
方法二：alter table table_name engine=engine_name
該方法通常用於切換表的引擎，例如MyISAM轉為InnoDB，但是同樣適用於釋放表空間，只不過切換後的引擎和原來的engine相同罷了。
注意：
- 1.這兩種方式都適用於我們常用的表，myisam和innodb。
- 2.優化表的時候會鎖表，數據表越大，耗時越長，因此不要在網站忙時進行表優化。
後面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注一下~