服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|

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

2019-06-26 11:33潇湘隐者 Mysql

这篇文章主要给大家介绍了关于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逻辑存储结构

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

总结

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

延伸 · 阅读

精彩推荐
  • Mysql详解Mysql数据库date, datetime类型设置0000-00-00默认值(default)报错问题

    详解Mysql数据库date, datetime类型设置0000-00-00默认值(default)报错问

    这篇文章主要介绍了详解Mysql数据库date, datetime类型设置0000-00-00默认值(default)报错问题,小编觉得挺不错的,现在分享给大家,也给大家做个参考。一起跟随小编过来看看吧...

    chentging2632019-06-10
  • Mysqlgetdata table表格数据join mysql方法

    getdata table表格数据join mysql方法

    今天小编就为大家分享一篇关于getdata table表格数据join mysql方法,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧...

    我是高手高手高高手1852019-06-04
  • Mysqlmysql中的一些稍微复杂用法实例代码

    mysql中的一些稍微复杂用法实例代码

    这篇文章主要给大家介绍了关于mysql中的一些稍微复杂用法的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用mysql具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...

    gaoziyuecj3002019-06-18
  • MysqlMySQL中可为空的字段设置为NULL还是NOT NULL

    MySQL中可为空的字段设置为NULL还是NOT NULL

    今天小编就为大家分享一篇关于MySQL中可为空的字段设置为NULL还是NOT NULL,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧...

    CODETC2302019-06-01
  • MysqlMySQL高效模糊搜索之内置函数locate instr position find_in_set使用详解

    MySQL高效模糊搜索之内置函数locate instr position find_in_set使用详解

    在MySQL中一般进行模糊搜索都是使用LIKE配合通配符进行查询的,在性能上一定的影响,下面给大家分享MYSQL自带的内置模糊搜索函数,除最后一个外其它三个性能上要比Like快些...

    wdc3252019-06-22
  • MysqlMySQL组合索引与最左匹配原则详解

    MySQL组合索引与最左匹配原则详解

    这篇文章主要给大家介绍了关于MySQL组合索引与最左匹配原则的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Mysql具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧...

    Wolf、Heart1262019-06-01
  • MysqlMySQL常用SQL语句总结包含复杂SQL查询

    MySQL常用SQL语句总结包含复杂SQL查询

    今天小编就为大家分享一篇关于MySQL常用SQL语句总结包含复杂SQL查询,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧...

    琦彦2942019-06-04
  • Mysqlnavicat连接mysql出现2059错误的解决方法

    navicat连接mysql出现2059错误的解决方法

    这篇文章主要为大家详细介绍了navicat连接mysql出现2059错误的解决方法,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...

    wgx_19971292019-06-16