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"文件。只是这个方法很笨，对于生产环境，大量的表通过这种方式判别，确实不是一个好方法。

1mysql> show variables like 'innodb_file_per_table';
2
3+-----------------------+-------+
4
5| Variable_name         | Value |
6
7+-----------------------+-------+
8
9| innodb_file_per_table | ON    |
10
11+-----------------------+-------+
12
131 row in set (0.01 sec)
14
15mysql> use MyDB;
16
17Reading table information for completion of table and column names
18
19You can turn off this feature to get a quicker startup with -A
20
21Database changed
22
23mysql> create table Independent_tablespace(name  varchar(64));
24
25Query OK, 0 rows affected (0.03 sec)
26
27mysql> exit
28
29[root@DB-Server ~]# cd /data/mysql/MyDB/
30
31[root@DB-Server MyDB]# ls -lrt Independent_tablespace*
32
33-rw-rw---- 1 mysql mysql  8560 Aug 21 22:05 Independent_tablespace.frm
34
35-rw-rw---- 1 mysql mysql 98304 Aug 21 22:05 Independent_tablespace.ibd
36
37[root@DB-Server MyDB]#

在配置文件my.cnf里面设置innodb_file_per_table=0，重启MySQL服务，创建表common_tablespace，你会在数据目录看到只有common_tablespace.frm文件。

1mysql> show variables like 'innodb_file_per_table';
2
3+-----------------------+-------+
4
5| Variable_name         | Value |
6
7+-----------------------+-------+
8
9| innodb_file_per_table | OFF   |
10
11+-----------------------+-------+
12
131 row in set (0.00 sec)
14
15mysql> use MyDB;
16
17Reading table information for completion of table and column names
18
19You can turn off this feature to get a quicker startup with -A
20
21Database changed
22
23mysql> create table common_tablespace(name varchar(64));
24
25Query OK, 0 rows affected (0.02 sec)
26
27mysql> exit
28
29Bye
30
31[root@DB-Server MyDB]# ls -lrt common_tablespace*
32
33-rw-rw---- 1 mysql mysql 8560 Aug 21 22:08 common_tablespace.frm
34
35[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引擎表是独立表空还是共享表空间

共享表空间：

1SELECT TABLE_SCHEMA
2
3    ,TABLE_NAME
4
5    ,TABLE_TYPE
6
7    ,N'共享表空间' AS TABLE_SPACE
8
9    ,ENGINE
10
11    ,VERSION
12
13    ,TABLE_ROWS
14
15    ,AVG_ROW_LENGTH
16
17    ,CREATE_TIME
18
19    ,UPDATE_TIME
20
21FROM INFORMATION_SCHEMA.TABLES  T
22
23LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
24
25WHERE I.NAME IS NULL  AND T.TABLE_SCHEMA='MyDB' AND T.ENGINE='InnoDB';

不过这个脚本有个小小的bug，对于包含特殊字符的表名，有可能出现错误情况，这个是因为如果表名包含特殊字符，那么文件名或INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES中的NAME做了转义处理，如下所示

独立表空间

1SELECT TABLE_SCHEMA
2
3    ,TABLE_NAME
4
5    ,TABLE_TYPE
6
7    ,N'独立表空间' AS TABLE_SPACE
8
9    ,ENGINE
10
11    ,VERSION
12
13    ,TABLE_ROWS
14
15    ,AVG_ROW_LENGTH
16
17    ,CREATE_TIME
18
19    ,UPDATE_TIME
20
21FROM INFORMATION_SCHEMA.TABLES  T
22
23INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
24
25WHERE 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 分别表示单独表空间和共享表空间。

#单独表空间

1SELECT * FROM  INFORMATION_SCHEMA.INNODB_SYS_TABLES
2
3WHERE SPACE_TYPE='Single';
4
5SELECT TABLE_SCHEMA
6
7    ,TABLE_NAME
8
9    ,TABLE_TYPE
10
11    ,N'独立表空间' AS TABLE_SPACE
12
13    ,ENGINE
14
15    ,VERSION
16
17    ,TABLE_ROWS
18
19    ,AVG_ROW_LENGTH
20
21    ,CREATE_TIME
22
23    ,UPDATE_TIME
24
25FROM INFORMATION_SCHEMA.TABLES  T
26
27INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
28
29WHERE T.TABLE_SCHEMA='YourDatabase'  AND T.ENGINE='InnoDB';

#共享表空间

1SELECT * FROM  INFORMATION_SCHEMA.INNODB_SYS_TABLES
2
3WHERE SPACE_TYPE='System';
4
5SELECT TABLE_SCHEMA
6
7    ,TABLE_NAME
8
9    ,TABLE_TYPE
10
11    ,N'共享表空间' AS TABLE_SPACE
12
13    ,ENGINE
14
15    ,VERSION
16
17    ,TABLE_ROWS
18
19    ,AVG_ROW_LENGTH
20
21    ,CREATE_TIME
22
23    ,UPDATE_TIME
24
25FROM INFORMATION_SCHEMA.TABLES  T
26
27LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
28
29WHERE I.NAME IS NULL  AND T.TABLE_SCHEMA='YourDatabase' AND T.ENGINE='InnoDB'

方法4： INFORMATION_SCHEMA.INNODB_TABLES判别

MySQL 8.0

如果是MySLQ 8.0的话，还多了一种方法， 那就是通过INFORMATION_SCHEMA.INNODB_TABLES来判断，这个新增的系统表可以通过SPACE_TYPE字段来区分共享表空间与独立表空间

SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE SPACE_TYPE=’Single’;

参考资料：

https://dev.mysql.com/doc/refman/8.0/en/innodb-tables-table.html

https://dev.mysql.com/doc/refman/5.6/en/innodb-i\_s-tables.html

https://dev.mysql.com/doc/refman/5.6/en/innodb-sys-tablespaces-table.html#innodb-sys-tablespaces-table-flag-column

https://dev.mysql.com/doc/refman/5.6/en/innodb-sys-tablespaces-table.html