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等信息，其它信息还是存放在共享表空间中，那么如何判别数据库中哪些表是独立表空间，哪些表是共享表空间呢？
innodb逻辑存储结构
方法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 分别表示单独表空间和共享表空间。
#单独表空间
select * from information_schema.innodb_sys_tables where space_type='single'; 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='yourdatabase' and t.engine='innodb';
#共享表空间
select * from information_schema.innodb_sys_tables where space_type='system'; 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='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
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。