极乐门资源网

MySQL判别InnoDB表是独立表空间还是共享表空间的方法详解
数据库 2024/12/23 佚名
2 0
极乐门资源网 Design By www.ioogu.com

前言

InnoDB采用按表空间（tablespace)的方式进行存储数据, 默认配置情况下会有一个初始大小为10MB， 名字为ibdata1的文件， 该文件就是默认的表空间文件（tablespce file），用户可以通过参数innodb_data_file_path对其进行设置，可以有多个数据文件，如果没有设置innodb_file_per_table的话， 那些Innodb存储类型的表的数据都放在这个共享表空间中，而系统变量innodb_file_per_table=1的话，那么InnoDB存储引擎类型的表就会产生一个独立表空间，独立表空间的命名规则为：表名.idb. 这些单独的表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息，其它信息还是存放在共享表空间中，那么如何判别数据库中哪些表是独立表空间，哪些表是共享表空间呢？

InnoDB逻辑存储结构

MySQL判别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';

MySQL判别InnoDB表是独立表空间还是共享表空间的方法详解

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

MySQL判别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
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';

MySQL判别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

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。

标签：
mysqlinnodb分表,mysqlinnodb表锁,mysql数据库教程
极乐门资源网 Design By www.ioogu.com
极乐门资源网 免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
极乐门资源网 Design By www.ioogu.com

评论“MySQL判别InnoDB表是独立表空间还是共享表空间的方法详解”

暂无MySQL判别InnoDB表是独立表空间还是共享表空间的方法详解的评论...
www.ioogu.com 极乐门资源网
139,976影音资源
144,792福利资源
21,817软件资源
631,128技术资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
极乐门资源网 Design By www.ioogu.com