前言
InnoDB采用按表空間(tablespace)的方式進(jìn)行存儲(chǔ)數(shù)據(jù), 默認(rèn)配置情況下會(huì)有一個(gè)初始大小為10MB, 名字為ibdata1的文件, 該文件就是默認(rèn)的表空間文件(tablespce file),用戶可以通過參數(shù)innodb_data_file_path對其進(jìn)行設(shè)置,可以有多個(gè)數(shù)據(jù)文件,如果沒有設(shè)置innodb_file_per_table的話, 那些Innodb存儲(chǔ)類型的表的數(shù)據(jù)都放在這個(gè)共享表空間中,而系統(tǒng)變量innodb_file_per_table=1的話,那么InnoDB存儲(chǔ)引擎類型的表就會(huì)產(chǎn)生一個(gè)獨(dú)立表空間,獨(dú)立表空間的命名規(guī)則為:表名.idb. 這些單獨(dú)的表空間文件僅存儲(chǔ)該表的數(shù)據(jù)、索引和插入緩沖BITMAP等信息,其它信息還是存放在共享表空間中,那么如何判別數(shù)據(jù)庫中哪些表是獨(dú)立表空間,哪些表是共享表空間呢?
InnoDB邏輯存儲(chǔ)結(jié)構(gòu)
方法1:通過ibd文件判別
如果表的存儲(chǔ)引擎是InnoDB,而且表空間(tablespace)是共享表空間的話,那么數(shù)據(jù)庫對應(yīng)目錄下面是沒有"表名.ibd"文件的。獨(dú)立表空間的表的話,則有"表名.ibd"文件。只是這個(gè)方法很笨,對于生產(chǎn)環(huán)境,大量的表通過這種方式判別,確實(shí)不是一個(gè)好方法。
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里面設(shè)置innodb_file_per_table=0,重啟MySQL服務(wù),創(chuàng)建表common_tablespace,你會(huì)在數(shù)據(jù)目錄看到只有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 關(guān)于這個(gè)系統(tǒng)表提供了有關(guān)表格的格式和存儲(chǔ)特性,包括行格式,壓縮頁面大小位級別的信息(如適用),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.
花了點(diǎn)時(shí)間了解了一下INFORMATION_SCHEMA數(shù)據(jù)庫下面的INNODB_SYS_TABLESPACES這個(gè)表,遂寫了一個(gè)SQL來判斷那些InnoDB引擎表是獨(dú)立表空還是共享表空間
共享表空間:
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';
不過這個(gè)腳本有個(gè)小小的bug,對于包含特殊字符的表名,有可能出現(xiàn)錯(cuò)誤情況,這個(gè)是因?yàn)槿绻砻厥庾址?,那么文件名或INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES中的NAME做了轉(zhuǎn)義處理,如下所示
獨(dú)立表空間
SELECT TABLE_SCHEMA
,TABLE_NAME
,TABLE_TYPE
,N'獨(dú)立表空間' 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 分別表示單獨(dú)表空間和共享表空間。
#單獨(dú)表空間
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE SPACE_TYPE='Single';
SELECT TABLE_SCHEMA
,TABLE_NAME
,TABLE_TYPE
,N'獨(dú)立表空間' 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來判斷,這個(gè)新增的系統(tǒng)表可以通過SPACE_TYPE字段來區(qū)分共享表空間與獨(dú)立表空間
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
總結(jié)
以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,如果有疑問大家可以留言交流,謝謝大家對腳本之家的支持。
您可能感興趣的文章:- mysql Innodb表空間卸載、遷移、裝載的使用方法
- MySQL InnoDB表空間加密示例詳解