MySQL information_schema 系統(tǒng)庫介紹

前言：

當(dāng)我們安裝好 MySQL 數(shù)據(jù)庫后，會發(fā)現(xiàn)數(shù)據(jù)庫實例自帶有 information_schema 系統(tǒng)庫，你是否有去關(guān)注過這個系統(tǒng)庫呢？是否有查詢過此庫中的表數(shù)據(jù)呢？又是否清楚此庫存在的具體作用呢？帶著這些疑問，我們一起來看本篇文章。

  1. information_schema 簡介

information_schema 顧名思義就是一個信息庫，是用來存儲數(shù)據(jù)庫的元數(shù)據(jù)（比如數(shù)據(jù)庫，表的名稱，列的數(shù)據(jù)類型或者訪問權(quán)限等），在每個 MySQL 實例中，information_schema 保存了它維護(hù)的所有數(shù)據(jù)庫的信息，這個庫中包含了很多只讀的表（它們實際上可看作為視圖，因此并沒有與之關(guān)聯(lián)的文件，你也無法為它們創(chuàng)建觸發(fā)器）。

我們來具體看下 information_schema 下的表，不同版本的數(shù)據(jù)庫稍有區(qū)別，以 5.7.23 版本為例，打開 information_schema 庫，我們發(fā)現(xiàn)共有 61 個表。

undefined

可以很明顯看出，information_schema 下的表大部分是 MEMORY 存儲引擎，有個別是 InnoDB 存儲引擎，再仔細(xì)看這些表的創(chuàng)建語句，發(fā)現(xiàn)這些表都是臨時表。下面展示部分表的作用：

• CHARACTER_SETS：可用的字符集信息表。

• COLLATIONS：字符集排序規(guī)則信息表。

• COLUMNS：每個表中的列的信息。

• ENGINES：存儲引擎的信息，可以用于檢查引擎是否支持。

• FILES：表空間數(shù)據(jù)存儲文件的信息。

• GLOBAL_STATUS：全局狀態(tài)變量值。

• GLOBAL_VARIABLES：全局系統(tǒng)變量值。

• INNODB_BUFFER_PAGE：InnoDB 緩沖池中頁的信息。

• INNODB_BUFFER_POOL_STATS：InnoDB 緩沖池統(tǒng)計信息。

• INNODB_LOCK_WAITS：InnoDB 事務(wù)鎖等待信息

• INNODB_LOCKS：包含了事務(wù)請求但是未獲得的鎖或者阻塞其它事務(wù)的鎖的信息。

• INNODB_TRX：所有當(dāng)前正在執(zhí)行的事務(wù)的信息。

• PARTITIONS：記錄表分區(qū)信息。

• PLUGINS：服務(wù)器安裝的插件信息。

• PROCESSLIST：記錄正在運行的線程的各種信息。

• ROUTINES：存儲過程及函數(shù)信息。

• SCHEMATA：數(shù)據(jù)庫的信息。

• STATISTICS：表索引信息。

• TABLES：表的信息。

• TRIGGERS：觸發(fā)器信息。

• VIEWS：數(shù)據(jù)庫視圖信息。

  2. information_schema 相關(guān)查詢

其實，在使用數(shù)據(jù)庫的過程中，你經(jīng)常與 information_schema 打交道，當(dāng)我們想查詢 MySQL 中各種對象的信息時，基本上都是從 information_schema 庫中查詢得到的。一些常見的 show 語句背后的邏輯也是查詢 information_schema 庫，例如：show tables 其實查的就是 information_schema.TABLES 表；show databases、show processlist 等語句查詢的都是 information_schema 庫中的相關(guān)表。

我們想了解數(shù)據(jù)庫中的各種信息時，都可以查詢 information_schema 庫，下面分享幾條筆者積累的相關(guān)查詢語句，來看下吧。

undefined

# 查看某個庫中的表信息
SELECT
 table_name,
 table_type,
  ENGINE 
FROM
 information_schema.TABLES 
WHERE
 table_schema = 'db5' 
ORDER BY
 table_name

# 查看整個實例占用空間
SELECT
 concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,
 concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB 
FROM
 information_schema.TABLES;

# 查看各個庫占用空間
SELECT
 TABLE_SCHEMA,
 concat( TRUNCATE ( sum( data_length )/ 1024 / 1024, 2 ), ' MB' ) AS data_size,
 concat( TRUNCATE ( sum( index_length )/ 1024 / 1024, 2 ), 'MB' ) AS index_size 
FROM
 information_schema.TABLES 
GROUP BY
 TABLE_SCHEMA 
ORDER BY
 data_length DESC;
  
# 查看某個表占用空間
SELECT
 concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,
 concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB 
FROM
 information_schema.TABLES 
WHERE
 table_schema = 'test' 
 AND table_name = 'test_tb'
  
# 查看所有線程信息
SELECT
 * 
FROM
 information_schema.PROCESSLIST
  
# 查看非睡眠線程信息
SELECT
 * 
FROM
 information_schema.PROCESSLIST 
WHERE
 command != 'sleep'
  
# 查看某個用戶發(fā)起的線程信息
SELECT
 * 
FROM
 information_schema.PROCESSLIST 
WHERE
 USER = 'testuser'
 
# 查看某個字符集支持的所有排序規(guī)則
SELECT
 COLLATION_NAME,
 CHARACTER_SET_NAME,
 IS_DEFAULT
FROM
 `information_schema`.`COLLATIONS` 
WHERE
 `CHARACTER_SET_NAME` = 'utf8' 
  
# 查看某個表的分區(qū)信息（如果有）
SELECT
 TABLE_SCHEMA,
 TABLE_NAME,
 PARTITION_NAME 
FROM
 `information_schema`.`PARTITIONS` 
WHERE
 `TABLE_SCHEMA` = 'test' 
 AND `TABLE_NAME` = 'tbname'
  
# 查看某個表的索引信息
SELECT
 * 
FROM
 `information_schema`.`STATISTICS` 
WHERE
 `TABLE_SCHEMA` = 'test' 
 AND `TABLE_NAME` = 'tbname' 
  
# 查看 innodb 事務(wù)相關(guān)信息
SELECT
 * 
FROM
 information_schema.INNODB_TRX

總結(jié)：

本文主要介紹 information_schema 系統(tǒng)庫相關(guān)內(nèi)容，我們想知道的數(shù)據(jù)庫元數(shù)據(jù)信息基本上都可以查到，熟悉 information_schema 庫有助于我們對數(shù)據(jù)庫更加了解，有興趣的同學(xué)可以看下 information_schema 下的表，一定會有收獲的。