欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL如何判别InnoDB表是独立表空间还是共享表空间

程序员文章站 2022-06-16 13:21:08
InnoDB采用按表空间(tablespace)的方式进行存储数据, 默认配置情况下会有一个初始大小为10MB, 名字为ibdata1的文件, 该文件就是默认的表空间文件(tablespce file),用户可以通过参数innodb_data_file_path对其进行设置,可以有多个数据文件,如果... ......

 

 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 分别表示单独表空间和共享表空间。

 

 

#单独表空间

 

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