MySQL中關於information_schema的詳細介紹

information_schema資料庫是MySQL系統自帶的資料庫，它提供了資料庫元資料的存取方式。感覺information_schema就像是MySQL實例的一個百科全書，記錄了資料庫當中大部分我們需要了結的信息，比如字符集，權限相關，數據庫實體對象信息，外檢約束，分區，壓縮表，表信息，索引信息，參數，優化，鎖和事物等等。透過information_schema我們可以窺透整個MySQL實例的運作情況，可以了結MySQL實例的基本信息，甚至優化調優，維護資料庫等，可以說是真正的一部百科全書啊哈哈。

以下就是根據自己學習的一些小小經驗將這些表進行一個大體的分類，方便大家了結，要是有不足的地方請指出，我會盡快修改。

1：關於字元集和排序規則相關的系統表

#CHARACTER_SETS ：儲存資料庫相關字元集資訊（ memory儲存引擎）

COLLATIONS ：字元集對應的排序規則

##COLLATION_CHARACTER_SET_APPLICABILITY：就是一個字元集和連線校對的一個對應關係而已

下面我們說character sets和collat​​ions的差別：

字元集（character sets）儲存

字串，是指人類語言中最小的表義符號。例如'A'、'B'等；

排序規則（collat​​ions）規則比較字串，collat​​ions是指在同一字元集內字元之間的比較規則

每個字元序唯一對應一種字元集，但一個字元集可以對應多種字元序，其中有一個是預設字元序(Default Collat​​ion)

 MySQL中的字元序名稱遵從命名慣例：以字元序對應的字元集名稱開頭；以_ci(表示大小寫不敏感)、_cs(表示大小寫敏感)或_bin(表示按編碼值比較)結尾。例如：在字元序列「utf8_general_ci」下，字元「a」和「A」是等價的

看看有關於字元集和校對相關的MySQL

變數：

character_set_server：預設的內部操作字元集

character_set_client：客戶端來源資料使用的字元集

character_set_connection：連接層字元集

character_set_results：

查詢結果字元集

character_set_database：目前選取資料庫的預設字元集

character_set_system：系統元資料(欄位名稱等)字元集

#再看一下MySQL中的字元集轉換過程：

（1）. MySQL Server收到請求時將請求資料從character_set_client轉換為character_set_connection;

（2）. 進行內部操作前將請求資料從character_set_connection轉換為內部操作字元集，其確定方法如下：

使用每個資料欄位的CHARACTER SET設定值；

若上述值不存在，則使用對應資料表的DEFAULT CHARACTER SET設定值(MySQL擴展，非SQL標準)；

若上述值不存在，則使用對應資料庫的DEFAULT CHARACTER SET設定值；

若上述值不存在，則使用character_set_server設定值。

（3）. 將操作結果從內部操作字元集轉換為character_set_results。

2：權限相關的一些表格：

SCHEMA_PRIVILEGES：提供了資料庫的相關權限，這個表格是記憶體表是從mysql. db中拉去出來的。

TABLE_PRIVILEGES:提供的是表格權限相關信息，訊息是從 mysql.tables_priv 表中載入的

#COLUMN_PRIVILEGES ：這個表格可以清楚就能看到表格授權的使用者的對象，那張表那個函式庫以及授予的是什麼權限，如果授權的時候加上with grant option的話，我們可以看得到PRIVILEGE_TYPE這個值必須是YES。

USER_PRIVILEGES:提供的是表格權限相關信息，資訊是從 mysql.user 表中載入的

透過表格我們可以很清楚看得到MySQL授權的層次，SCHEMA，TABLE，COLUMN級別，當然這些都是基於使用者來授予的。可以看得到MySQL的授權也是相當的細密的，可以具體到列，這在某一些應用場景下還是很有用的，例如審計等。

3：儲存資料庫系統的實體物件的一些表格：

COLUMNS：儲存表的欄位信息，所有的儲存引擎

INNODB_SYS_COLUMNS ：存放的是INNODB的元數據， 他是依賴SYS_COLUMNS這個統計表而存在的。

ENGINES ：引擎類型，是否支援這個引擎，描述，是否支援事物，是否支援分散式事務，是否能夠支援事物的回滾點

EVENTS ：記錄MySQL中的事件，類似定時作業

FILES ：這張表提供了有關在MySQL的表空間中的資料儲存的文件的信息，文件儲存的位置，這個表的資料是從InnoDB in-memory中拉取出來的，所以說這張表本身也是一個記憶體表，每次重啟重新進行拉取。也就是我們下面要說的INNODB_SYS_DATAFILES這張表。還要注意一點的是這張表包含有臨時表的信息，所以說和SYS_DATAFILES 這張表是不能夠對等的，還是要從INNODB_SYS_DATAFILES看。如果undo表空間也配置是InnoDB 的話，那麼也是會被記錄下來的。

PARAMETERS ：參數表儲存了一些預存程序和方法的參數，以及預存程序的回傳值資訊。儲存和方法存放在ROUTINES裡面。

PLUGINS ：基本上是MySQL的插件信息，是否是活動狀態等資訊。其實SHOW PLUGINS本身就是透過這張表來拉取道德資料

ROUTINES：關於儲存過程和方法function的一些信息，不過這個資訊是不包括使用者自訂的，只是系統的一些資訊。

SCHEMATA：這個表提供了實例下有多少個資料庫，而且還有資料庫預設的字元集

TRIGGERS :這個表記錄的就是觸發器的訊息，包括所有的相關的資訊。系統的和自己使用者創建的觸發器。

VIEWS :視圖的訊息，也是系統的和使用者的基本視圖資訊。

這些表格儲存的都是一些資料庫的實體對象，方便我們進行查詢和管理，對於一個DBA來說，這些表能夠大大方便我們的工作，更快更方便的了結和查詢資料庫的相關資訊。

4：約束外鍵等相關的一些表：

REFERENTIAL_CONSTRAINTS：這個表提供的外鍵相關的信息，而且只提供外鍵相關資訊

TABLE_CONSTRAINTS ：這個表提供的是相關的約束資訊

INNODB_SYS_FOREIGN_COLS ：這個表也是儲存的INNODB關於外鍵的元資料資訊和SYS_FOREIGN_COLS 儲存的資訊是一致的

INNODB_SYS_FOREIGN ：儲存的INNODB關於外鍵的元資料資訊和SYS_FOREIGN_COLS 儲存的資訊是一致的，只不過是單獨對於INNODB來說的

KEY_COLUMN_USAGE：資料庫中所有有約束的資料列都會存下來，也會記錄下約束的名字和類別

為什麼要把外鍵和約束單列出來呢，因為感覺這是一塊獨立的東西，雖然我們的生產環境大部分都不會使用外鍵，因為這會降低性能，但是合理的利用約束還是一個不錯的選擇，例如唯一約束。

5：關於管理的一些的一些表：

GLOBAL_STATUS ，GLOBAL_VARIABLES，SESSION_STATUS，SESSION_VARIABLES：這四張表分別記錄了系統的變量，狀態（全局和會話的資訊），作為DBA相信大家也都比較熟悉了，而且這幾張表也是在系統重啟的時候回重新加載的。也就是內存表。

PARTITIONS ：MySQL分區表相關的信息，透過這張表我們可以查詢到分區的相關資訊（資料庫中已分區的表，以及分區表的分區和每個分區的資料資訊），分區相關詳細資訊請參考MySQL分區管理

PROCESSLIST：show processlist其實就是從這個表拉取數據，PROCESSLIST的數據是他的基礎。由於是一個記憶體表，所以我們相當於在記憶體中查詢一樣，這些操作都是很快的。

INNODB_CMP_PER_INDEX，INNODB_CMP_PER_INDEX_RESET：這兩個表儲存的是關於壓縮INNODB資訊表的時候的相關資訊,有關整個表和索引資訊都有.我們知道對於一個INNODB壓縮表來說,不管是資料還是二級索引都是會被壓縮的,因為資料本身也可以看作是一個聚集索引。關於壓縮表在information_schema系列十一有些許簡單的介紹。

INNODB_CMPMEM ，INNODB_CMPMEM_RESET：這兩個表是存放關於MySQL INNODB的壓縮頁的buffer pool信息，但是要注意一點的就是,用這兩個表來收集所有信息的表的時候,是會對性能造成嚴重的影響的,所以說默認是關閉狀態的。如果要開啟這個功能的話我們要設定innodb_cmp_per_index_enabled參數為ON狀態。

INNODB_BUFFER_POOL_STATS ：表提供有關INNODB 的buffer pool相關信息，和show engine innodb status提供的信息是相同的。也是show engine innodb status的資訊來源。

INNODB_BUFFER_PAGE_LRU，INNODB_BUFFER_PAGE :維護了INNODB LRU LIST的相關信息，詳細請見小編筆記innodb buffer pool小解

INNODB_BUFFER_PAGE ：這個表就比較屌了，存的是buffer裡面緩衝的頁數據。查詢這個表會對效能產生很嚴重的影響，千萬不要再我們自己的生產庫上面執行這個語句，除非你能接受服務短暫的停頓，詳細請見小編筆記innodb buffer pool小解

#INNODB_SYS_DATAFILES ：這張表就是記錄的表的檔案儲存的位置和表空間的一個對應關係(INNODB)

INNODB_TEMP_TABLE_INFO ：這個表惠記錄所有的INNODB的所有使用者所使用到的信息，但是只能記錄在記憶體中和沒有持久化的信息。

INNODB_METRICS ：提供INNODB的各種的效能指數，是對INFORMATION_SCHEMA的補充，收集的是MySQL的系統統計資料。這些統計資料都是可以手動設定開啟還是關閉的。有以下的參數都是可以控制：innodb_monitor_enable, innodb_monitor_disable, innodb_monitor_reset, innodb_monitor_reset_all。

INNODB_SYS_VIRTUAL :表儲存的是INNODB表的虛擬列的信息，當然這個還是比較簡單的，在MySQL 5.7中，支援兩種Generated Column，即Virtual Generated Column和Stored Generated Column，前者只將Generated Column保存在資料字典中（表格的元資料），並不會將這一列資料持久化到磁碟上；後者會將Generated Column持久化到磁碟上，而不是每次讀取取的時候計算所得。很明顯，後者存放了可以透過已有數據計算而得的數據，需要更多的磁碟空間，與實際儲存一列數據相比並沒有優勢，因此，MySQL 5.7中，不指定Generated Column的類型，默認是Virtual Column。

INNODB_CMP，INNODB_CMP_RESET：儲存的是關於壓縮INNODB資訊表的時候的相關信息，詳細請見推薦筆記。

為什麼把這些表列為管理相關的表呢，因為我感覺像連接，分區，壓縮表，innodb buffer pool等表，我們透過這些表都能很清晰的看到自己資料庫的相關功能的狀態，特別是我們透過一些變數更容易窺透MySQL的運作狀態，方便我們進行管理。相關筆記有推薦innodb buffer pool小解，MySQL分割區管理，information_schema系列十一。都是小編自己的筆記。

6：關於表格資訊和索引資訊的一些表格

TABLES，TABLESPACES，INNODB_SYS_TABLES ，INNODB_SYS_TABLESPACES ：

TABLES這張表毫無疑問了，就是記錄的資料庫中表格的信息，其中包括系統資料庫和使用者創建的資料庫。 show table status like 'test1'\G的來源就是這個表；

TABLESPACES 卻是標註的活躍表空間。 這個表是不提供關於innodb的表空間資訊的，對我們來說並沒有太大作用，因為我們生產庫是強制INNODB的；

INNODB_SYS_TABLES 這張表依賴的是SYS_TABLES資料字典中拉取出來的。此表提供了有關表格的格式和存儲特性，包括行格式，壓縮頁面大小位級別的信息（如適用）

提供的是關於INNODB的表空間信息，其實和SYS_TABLESPACES 中的INNODB資訊是一致的。

STATISTICS：這個表提供的是關於表的索引信息，所有索引的相關信息。

INNODB_SYS_INDEXES：提供相關INNODB表的索引的相關信息，和SYS_INDEXES 這個表存儲的信息基本上是一樣的，只不過後者提供的是所有存儲引擎的索引信息，後者只提供INNODB表的索引資訊。

INNODB_SYS_TABLESTATS：

這個表就比較重要了，記錄的是MySQL的INNODB表資訊以及MySQL優化器會預估SQL選擇合適的索引信息，其實就是MySQL資料庫的統計資料

這個表的記錄是記錄在記憶體當中的，是記憶體表，每次重啟後就會重新記錄，所以只能記錄從上次重啟後的資料庫統計資訊。有了這個表，我們對於索引的維護就更加方便了，我們可以查詢索引的使用次數，方便清理刪除不常用的索引，提高表的更新插入等效率，節省磁碟空間。

INNODB_SYS_FIELDS ：這個表記錄的是INNODB的表索引字段信息，以及字段的排名

INNODB_FT_CONFIG :這張表存的是全文索引的資訊

INNODB_FT_DEFAULT_STOPWORD：這個表格存放的是stopword 的資訊,是和全文索引相符起來使用的，和innodb的INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD 是相同的，這個STOPWORD必須是在建立索引之前創建，而且必須指定欄位為varchar。 stopword 也就是我們所說的停止詞，全文檢索時，停止詞列表將會被讀取和檢索，在不同的字符集和排序方式下，會造成命中失敗或找不到此數據，這取決於停止詞的不同的排序方式。我們可以使用這個功能篩選不必要欄位。

INNODB_FT_INDEX_TABLE：這個表儲存的是關於INNODB表有全文索引的索引使用資訊的，同樣這個表也是要設定innodb_ft_aux_table以後才能夠使用的，一般情況下是空的

INNODB_FT_INDEX_CACHE ：這張表存放的是插入前的記錄信息，也是為了避免DML時候昂貴的索引重組

#7：關於MySQL優化相關的一些表格

OPTIMIZER_TRACE ：提供的是優化追蹤功能產生的資訊.關於這個我也謝了做了一個小測試，MySQL追蹤優化器小試

PROFILING：SHOW PROFILE可以深入的查看伺服器執行語句的工作情況。以及也能幫助你理解執行語句消耗時間的情況。一些限制是它沒有實現的功能，不能查看和剖析其他連接的語句，以及剖析時所造成的消耗。

SHOW PROFILES顯示最近發給伺服器的多個語句，條數根據會話變數profiling_history_size定義，預設是15，最大值為100。設為0等價於關閉分析功能。詳細資訊請見MySQL profile

INNODB_FT_BEING_DELETED,INNODB_FT_DELETED: INNODB_FT_BEING_DELETED 這張表是INNODB_FT_DELETED的一個快照,只在應用程式資料的時候才會使用。詳細資訊詳見我的OPTIMIZE TABLE 小解

8：關於MySQL事物和鎖的相關的一些表

##INNODB_LOCKS:現在獲取的鎖，但是不含沒有取得的鎖，而且只是針對INNODB的。

INNODB_LOCK_WAITS：系統鎖等待相關信息，包含了阻塞的一行或多行的記錄，而且還有鎖請求和被阻塞改請求的鎖信息等。

INNODB_TRX：包含了所有正在執行的事物相關資訊（INNODB），而且包含了事物是否被阻塞或要求鎖定。

我們透過這些表就能夠很方便的查詢出來未結束的事物和被阻塞的進程，這是不是更方便了，詳細可見information_schema系列八(事物，鎖)

 

#

以上是MySQL中關於information_schema的詳細介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章！