MySQL判别InnoDB表是独立表空间还是共享表空间的方法详解
作者:潇湘隐者 发布时间:2024-01-18 14:23:34
前言
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
来源:https://www.cnblogs.com/kerrycode/p/9515200.html
![](https://www.aspxhome.com/images/zang.png)
![](https://www.aspxhome.com/images/jiucuo.png)
猜你喜欢
- 前两天在帮朋友整理他的主页空间时候,发现的一点关于MySQL可能大家都会忽略的问题:我们知道,在安装完MySQL后,它会自动创建一个root
- 最近遇到一个现象,将做好的软件放在更高分辨率的电脑上运行,会导致字体显示不完全,出现被控件遮挡的情况。具体原因可以上网查询,在这里将记录下解
- 在数据存储过多时,我们会选择清除,不过有时候也需要找回一些我们之前删掉的数据。有的小伙伴可能会使用不同的方法分别完成,那么今天小编带来的_d
- 句柄(handle)是C++程序设计中经常提及的一个术语。它并不是一种具体的、固定不变的数据类型或实体,而是代表了程序设计中的一个广义的概念
- 最近想做实时目标检测,需要用到python开启摄像头,我手上只有两个uvc免驱的摄像头,性能一般。利用python开启摄像头费了一番功夫,主
- 要求利用python实现禁用键盘鼠标思路经过查阅资料目前最好的办法是采用ctypes中的dll文件进行编写from ctypes impor
- tensorlfow网络模型可视化。baidu了一些方法,现在介绍下我的流程和遇到的问题:配置window7tensorlfow1.5ana
- json.dumps将一个Python数据结构转换为JSONimport jsondata = { 'na
- 前言随着深度学习的不断发展,从开山之作Alexnet到VGG,网络结构不断优化,但是在VGG网络研究过程中,人们发现随着网络深度的不断提高,
- 1、使用+加号+加号是将两个list列表相加,返回一个新的列表对象,会消耗额外的内存。#!/usr/bin/env python# -*-
- Kafka 样式的 soap 端点Christopher Dix 所开发的“Kafka — XSL SOAP 工具箱”(请参阅 参考资料)是
- 1. apply与transform首先讲一下apply() 与transform()的相同点与不同点相同点:都能针对dataframe完成
- 穿过云朵升一级是要花6个金币的,有的时候金币真的很重要前言嗨喽,大家好呀!这里是魔王~一天晚上,天空中掉下一颗神奇的豌豆种子,正好落在了梦之
- 一. torch.cat()函数解析1. 函数说明1.1 官网:torch.cat(),函数定义及参数说明如下图所示:1.2 函数功能函数将
- 大家好,我是煎蛋哥!全国有很多彩民,其中购买最多的彩种分别是体彩大乐透和福彩双色球;虽然中大奖的概率极低,但是彩民纷至沓来,一方面抱着一份中
- 通过HTTP_USER_AGENT判断用户是从手机上访问,还是电脑IE上访问。 asp代码片段:主要使用了正则匹配手机环境,大家可以补充手机
- 1.背景在python运行一些,计算复杂度比较高的函数时,服务器端单核CPU的情况比较耗时,因此需要多CPU使用多进程加快速度2.函数要求笔
- 前言Beautiful Soup是python的一个HTML或XML的解析库,我们可以用它来方便的从网页中提取数据,它拥有强大的API和多样
- QSlider 是一个具有可来回拉动手柄的控件。有时使用滑块比输入数字或使用旋转框更方便。在我们的例子中,我们将创建一个滑块和一个标签。标签
- 曾经为看别人写的杂乱代码而头痛吗?曾经为看BWindow代码而烦恼吗?曾经为减小JS体积和JS的可读性之间的矛盾而左右徘徊吗?最好的办法是有