MySQL判別InnoDB表是獨立表空間還是共享表空間的方法詳解
前言
InnoDB采用按表空間（tablespace)的方式進(jìn)行存儲數(shù)據(jù), 默認(rèn)配置情況下會有一個初始大小為10MB， 名字為ibdata1的文件， 該文件就是默認(rèn)的表空間文件（tablespce file），用戶可以通過參數(shù)innodb_data_file_path對其進(jìn)行設(shè)置，可以有多個數(shù)據(jù)文件，如果沒有設(shè)置innodb_file_per_table的話， 那些Innodb存儲類型的表的數(shù)據(jù)都放在這個共享表空間中，而系統(tǒng)變量innodb_file_per_table=1的話，那么InnoDB存儲引擎類型的表就會產(chǎn)生一個獨立表空間，獨立表空間的命名規(guī)則為：表名.idb. 這些單獨的表空間文件僅存儲該表的數(shù)據(jù)、索引和插入緩沖BITMAP等信息，其它信息還是存放在共享表空間中，那么如何判別數(shù)據(jù)庫中哪些表是獨立表空間，哪些表是共享表空間呢？
InnoDB邏輯存儲結(jié)構(gòu)
方法1：通過ibd文件判別
如果表的存儲引擎是InnoDB，而且表空間（tablespace）是共享表空間的話，那么數(shù)據(jù)庫對應(yīng)目錄下面是沒有"表名.ibd"文件的。獨立表空間的表的話，則有"表名.ibd"文件。只是這個方法很笨，對于生產(chǎn)環(huán)境，大量的表通過這種方式判別，確實不是一個好方法。
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，你會在數(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)于這個系統(tǒng)表提供了有關(guān)表格的格式和存儲特性，包括行格式，壓縮頁面大小位級別的信息（如適用），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數(shù)據(jù)庫下面的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，對于包含特殊字符的表名，有可能出現(xiàn)錯誤情況，這個是因為如果表名包含特殊字符，那么文件名或INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES中的NAME做了轉(zhuǎn)義處理，如下所示
獨立表空間
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來判斷，這個新增的系統(tǒng)表可以通過SPACE_TYPE字段來區(qū)分共享表空間與獨立表空間
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í)價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。
相關(guān)文章
這篇文章主要介紹了阿里云云服務(wù)器mysql密碼找回的方法，非常不錯，具有一定的參考借鑒價值,需要的朋友可以參考下2018-07-07
小白安裝登錄mysql-8.0.19-winx64的教程圖解(新手必看)
這篇文章主要介紹了安裝登錄mysql-8.0.19-winx64的教程圖解,非常適合新手學(xué)習(xí)參考，本文通過圖文并茂的形式給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2020-03-03
最新Navicat?15?for?MySQL破解+教程?正確破解步驟
Navicat?for?MySQL是一個針對MySQL數(shù)據(jù)庫而開發(fā)的第三方mysql管理工具，該軟件可以用于?MySQL?數(shù)據(jù)庫服務(wù)器版本?3.21?或以上的和?MariaDB?5.1?或以上，這篇文章主要介紹了最新Navicat?15?for?MySQL破解+教程?正確破解步驟,需要的朋友可以參考下2023-04-04
mvcc多版本并發(fā)控制是一種數(shù)據(jù)庫的并發(fā)控制機(jī)制,本文主要介紹了MySQL多版本并發(fā)控制mvcc原理淺析,具有一定的參考價值,感興趣的可以了解一下2024-07-07
這篇文章主要介紹了MySQL修改密碼不成功(無效)的解決方案，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-12-12
這篇文章主要介紹了mysql刪除表數(shù)據(jù)如何恢復(fù)，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-11-11
最近在將數(shù)據(jù)從Oracle遷移到MySQL的過程中，遇到一些問題，其中就包括關(guān)鍵字。下面這篇文章主要給大家介紹了MySQL 5.7中的關(guān)鍵字與保留字的相關(guān)資料，文中介紹的非常詳細(xì)，需要的朋友可以參考學(xué)習(xí)，下面來一起看看吧。2017-03-03
本文主要搜集總結(jié)了一些和mysql主鍵相關(guān)的sql語句，包括增加主鍵或者更改表的列為主鍵之類的sql語句，希望對大家能有所幫助2014-08-08
用管理員身份打開cmd試圖啟動MySQL時出現(xiàn)服務(wù)無法啟動并提示服務(wù)沒有報錯任何錯誤,所以本文小編給大家介紹了一個親測有效的解決辦法,需要的朋友可以參考下2023-12-12![Mysql啟動與數(shù)據(jù)庫的創(chuàng)建方法[圖文]](http://img.jbzj.com/images/xgimg/bcimg9.png)
Mysql啟動與數(shù)據(jù)庫的創(chuàng)建方法[圖文]
Mysql啟動與數(shù)據(jù)庫的創(chuàng)建方法，mysql數(shù)據(jù)庫入門2012-03-03