MySQL中怎么判斷InnoDB表是獨(dú)立表空間還是共享表空間

MySQL中怎么判斷InnoDB表是獨(dú)立表空間還是共享表空間，相信很多沒有經(jīng)驗的人對此束手無策，為此本文總結(jié)了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個問題。

一、獨(dú)立表空間優(yōu)缺點(diǎn)

1. 優(yōu)點(diǎn)：

(1) 每個表都有自已獨(dú)立的表空間。

(2) 每個表的數(shù)據(jù)和索引都會存在自已的表空間中。

(3) 可以實現(xiàn)單表在不同的數(shù)據(jù)庫中移動。

(4) 空間可以回收(除drop table操作處，表空不能自已回收)

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

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

• 對于使用獨(dú)立表空間的表，不管怎么刪除，表空間的碎片不會太嚴(yán)重的影響性能，而且還有機(jī)會處理。

2. 缺點(diǎn)

單表增加過大，如超過100個G。

二、判別數(shù)據(jù)表是獨(dú)立表空間還是共享表空間

1. 通過ibd文件判別

如果表的存儲引擎是InnoDB，而且表空間(tablespace)是共享表空間的話，那么數(shù)據(jù)庫對應(yīng)目錄下面是沒有"表名.ibd"文件的。獨(dú)立表空間的表的話，則有"表名.ibd"文件。只是這個方法很笨，對于生產(chǎn)環(huán)境，大量的表通過這種方式判別，確實不是一個好方法。

--innodb_file_per_table=1 show variables like 'innodb_file_per_table'; create table Independent_tablespace(name  varchar(64)); ls -lrt independent_tablespace.* --innodb_file_per_table=0 show variables like 'innodb_file_per_table'; create table common_tablespace(name varchar(64)); ls -lrt independent_tablespace.*

2. INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES判別

MySQL 5.6的INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES  關(guān)于這個系統(tǒng)表提供了有關(guān)表格的格式和存儲特性，包括行格式，壓縮頁面大小位級別的信息(如適用)，INNODB的表空間信息。

--共享表空間 SELECT TABLE_SCHEMA     ,TABLE_NAME     ,TABLE_TYPE     ,N'共享表空間' AS TABLE_SPACE     ,ENGINE     ,VERSION     ,TABLE_ROWS     ,AVG_ROW_LENGTH     ,CREATE_TIME     ,UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES  T LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME WHERE I.NAME IS NULL  AND T.TABLE_SCHEMA='HWB' AND T.ENGINE='InnoDB'; --獨(dú)立表空間 SELECT TABLE_SCHEMA     ,TABLE_NAME     ,TABLE_TYPE     ,N'獨(dú)立表空間' AS TABLE_SPACE     ,ENGINE     ,VERSION     ,TABLE_ROWS     ,AVG_ROW_LENGTH     ,CREATE_TIME     ,UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES  T INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME WHERE T.TABLE_SCHEMA='HWB'  AND T.ENGINE='InnoDB';

3. INFORMATION_SCHEMA.INNODB_SYS_TABLES判別(推薦)

MySQL 5.7  的INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES中多了SPACE_TYPE字段，不過其值全部為Single，而INFORMATION_SCHEMA.INNODB_SYS_TABLES中也多了字段SPACE_TYPE，  其值有Single與System 分別表示獨(dú)立表空間和共享表空間。

--獨(dú)立表空間 SELECT * FROM  INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE SPACE_TYPE='Single'; SELECT TABLE_SCHEMA     ,TABLE_NAME     ,TABLE_TYPE     ,N'獨(dú)立表空間' AS TABLE_SPACE     ,ENGINE     ,VERSION     ,TABLE_ROWS     ,AVG_ROW_LENGTH     ,CREATE_TIME     ,UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES  T INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME WHERE T.TABLE_SCHEMA='HWB'  AND T.ENGINE='InnoDB';  --共享表空間 SELECT TABLE_SCHEMA     ,TABLE_NAME     ,TABLE_TYPE     ,N'共享表空間' AS TABLE_SPACE     ,ENGINE     ,VERSION     ,TABLE_ROWS     ,AVG_ROW_LENGTH     ,CREATE_TIME     ,UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES  T LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME WHERE I.NAME IS NULL  AND T.TABLE_SCHEMA='HWB' AND T.ENGINE='InnoDB';

看完上述內(nèi)容，你們掌握MySQL中怎么判斷InnoDB表是獨(dú)立表空間還是共享表空間的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！