MySQL如何判別InnoDB表是獨立表空間還是共用表空間

 InnoDB採用按表空間（tablespace)的方式進行存儲數據, 預設配置情況下會有一個初始大小為10MB， 名字為ibdata1的文件， 該文件就是預設的表空間文件（tablespce file），用戶可以通過參數innodb_data_file_path對其進行設置，可以有多個數據文件，如果沒有設置innodb_file_per_table的話， 那些Innodb存儲類型的表的數據都放在這個共用表空間中，而系統變數innodb_file_per_table=1的話，那麼InnoDB存儲引擎類型的表就會產生一個獨立表空間，獨立表空間的命名規則為：表名.idb. 這些單獨的表空間文件僅存儲該表的數據、索引和插入緩衝BITMAP等信息，其它信息還是存放在共用表空間中，那麼如何判別資料庫中哪些表是獨立表空間，哪些表是共用表空間呢？

方法1：通過ibd文件判別

如果表的存儲引擎是InnoDB，而且表空間（tablespace）是共用表空間的話，那麼資料庫對應目錄下麵是沒有"表名.ibd"文件的。獨立表空間的表的話，則有"表名.ibd"文件。只是這個方法很笨，對於生產環境，大量的表通過這種方式判別，確實不是一個好方法。

mysql> show variables like 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | ON    |

+-----------------------+-------+

1 row in set (0.01 sec)

mysql> use MyDB;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table Independent_tablespace(name  varchar(64));

Query OK, 0 rows affected (0.03 sec)

mysql> exit

[root@DB-Server ~]# cd /data/mysql/MyDB/

[root@DB-Server MyDB]# ls -lrt Independent_tablespace*

-rw-rw---- 1 mysql mysql  8560 Aug 21 22:05 Independent_tablespace.frm

-rw-rw---- 1 mysql mysql 98304 Aug 21 22:05 Independent_tablespace.ibd

[root@DB-Server MyDB]# 

在配置文件my.cnf裡面設置innodb_file_per_table=0，重啟MySQL服務，創建表common_tablespace，你會在數據目錄看到只有common_tablespace.frm文件。

mysql> show variables like 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | OFF   |

+-----------------------+-------+

1 row in set (0.00 sec)

mysql> use MyDB;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table common_tablespace(name varchar(64));

Query OK, 0 rows affected (0.02 sec)

mysql> exit

Bye

[root@DB-Server MyDB]# ls -lrt common_tablespace*

-rw-rw---- 1 mysql mysql 8560 Aug 21 22:08 common_tablespace.frm

[root@DB-Server MyDB]# 

方法2：使用INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES來判別。

MySQL 5.6

MySQL 5.6 INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES 關於這個系統表提供了有關表格的格式和存儲特性，包括行格式，壓縮頁面大小位級別的信息（如適用），INNODB的表空間信息。

The INNODB_SYS_TABLESPACES table provides metadata about InnoDB tablespaces, equivalent to the information in the SYS_TABLESPACES table in the InnoDB data dictionary.

   花了點時間瞭解了一下INFORMATION_SCHEMA資料庫下麵的INNODB_SYS_TABLESPACES這個表，遂寫了一個SQL來判斷那些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='MyDB' AND T.ENGINE='InnoDB';

不過這個腳本有個小小的bug，對於包含特殊字元的表名，有可能出現錯誤情況，這個是因為如果表名包含特殊字元，那麼文件名或INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES中的NAME做了轉義處理，如下所示

獨立表空間

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

INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME

WHERE T.TABLE_SCHEMA='MyDB'  AND T.ENGINE='InnoDB';

方法3 ：INFORMATION_SCHEMA.INNODB_SYS_TABLES判別

MySQL 5.7

     如果是MySQL 5.7的話， 比MySQL 5.6有多了一種方法，MySQL 5.7 的INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES中多了SPACE_TYPE欄位，不過其值全部為Single，而INFORMATION_SCHEMA.INNODB_SYS_TABLES中也多了欄位SPACE_TYPE， 其值有Single與System 分別表示單獨表空間和共用表空間。