溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“怎么理解并掌握mysql中的information_schema”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
| 什么是information_schema
information_schema提供了對數據庫元數據、統計信息、以及有關MySQL Server的信息訪問（例如：數據庫名或表名，字段的數據類型和訪問權限等）。該庫中保存的信息也可以稱為MySQL的數據字典或系統目錄。
在每個MySQL 實例中都有一個獨立的information_schema，用來存儲MySQL實例中所有其他數據庫的基本信息。information_schema數據庫下包含多個只讀表(非持久表)，所以在磁盤中的數據目錄下沒有對應的關聯文件，且不能對這些表設置觸發器。雖然在查詢時可以使用USE語句將默認數據庫設置為information_schema，但該庫下的所有表是只讀的，不能執行INSERT、UPDATE、DELETE等數據變更操作。
針對information_schema下的表的查詢操作可以替代一些show查詢語句（例如：SHOW DATABASES，SHOW TABLES等），與使用show語句相比，通過查詢information_schema下的表獲取數據有以下優勢：
它符合"Codd法則"，所有的訪問都是基于表的訪問完成的。
可以使用SELECT語句的SQL語法，只需要學習你要查詢的一些表名和列名的含義即可
基于SQL語句的查詢，對來自information_schema中的查詢結果可以做過濾、排序、聯結操作，查詢的結果集格式對應用程序來說更友好
這種技術實現與其他數據庫系統中類似的實現更具互操作性。例如：Oracle數據庫的用戶熟悉查詢Oracle數據字典中的表，那么在MySQL中查詢數據字典的表也可以使用同樣的方法來執行查詢獲取想要的數據
訪問information_schema需要的權限
所有用戶都有訪問information_schema下的表權限(但只能看到這些表中用戶具有訪問權限的對象相對應的數據行)，但只能訪問Server層的部分數據字典表，Server層中的部分數據字典表以及InnoDB層的數據字典表需要額外授權才能訪問，如果用戶權限不足，當查詢Server層數據字典表時將不會返回任何數據，或者某個列沒有權限訪問時，該列返回NULL值。當查詢InnoDB數據字典表時將直接拒絕訪問(要訪問這些表需要有process權限，注意不是select權限)
從information_schema中查詢相關數據需要的權限也適用于SHOW語句。無論使用哪種查詢方式，都必須擁有某個對象的權限才能看到相關的數據。
PS：
在MySQL 5.6版本中總共有59張表，其中10張MyISAM引擎臨時表(數據字典表)，49張Memory引擎臨時表(保存統計信息和一些臨時信息)。在MySQL 5.7版本中，該schema下總共有61張表，其中10個InnoDB存儲引擎臨時表(數據字典表)，51個Memory引擎臨時表。在MySQL 8.0中該schema下數據字典表(包含部分原memory引擎臨時表)都遷移到了mysql schema下，且在mysql schema下這些數據字典表被隱藏，無法直接訪問，需要通過information_schema下的同名表進行訪問(統計信息表保留在information_schema下且仍然為Memory引擎)
雖然直接通過查詢information_schema中的表獲取數據有眾多優勢，但是因為SHOW語法已經耳熟能詳且被廣泛使用，所以SHOW語句仍然是一個備選方法，且隨著information_schema的實現，SHOW語句中的功能還有所增強(可以使用like或where子句進行過濾)，例如：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
|
information_schema下的所有表都是使用的Memory和InnoDB存儲引擎，且都是臨時表，不是持久表，在數據庫重啟之后這些數據會丟失，在MySQL 的4個系統庫中，也是唯一一個在文件系統上沒有對應庫表的目錄和文件的系統庫。
下面我們按照這些表的各自用途的相似度，我們把information_schema下的表做了如下歸類，本期我們先大致了解下information_schema系統庫中都有哪些表，這些表大致都有什么用途。
COLUMNS：
提供查詢表中的列(字段)信息
該表為InnoDB 存儲引擎的臨時表
KEY_COLUMN_USAGE：
提供查詢哪些索引列存在約束條件
該表中的信息包含主鍵、唯一索引、外鍵等約束的信息，例如：所在庫表列名，引用的庫表列名等。表中的信息與TABLE_CONSTRAINTS表中記錄的信息有些類似，但TABLE_CONSTRAINTS表中沒有記錄約束引用的庫表列信息。但是卻記錄了TABLE_CONSTRAINTS表中所沒有的約束類型信息
該表為Memory引擎臨時表
REFERENTIAL_CONSTRAINTS：
提供查詢關于外鍵約束的一些信息
該表為Memory引擎臨時表
STATISTICS：
提供查詢關于索引的一些統計信息，一個索引對應一行記錄
該表為Memory引擎臨時表
TABLE_CONSTRAINTS：
提供查詢表相關的約束信息
該表為Memory引擎臨時表
FILES：
提供查詢MySQL的數據表空間文件相關的信息，包含InnoDB存儲引擎和NDB存儲引擎相關的數據文件信息，由于NDB存儲引擎在國內較少使用，我們大多數場景(95%以上場景InnoDB存儲引擎都滿可以使用)都是使用InnoDB存儲引擎
該表為Memory存儲引擎表
ENGINES：
提供查詢MySQL Server支持的引擎相關的信息
該表為Memory引擎臨時表
TABLESPACES：
提供查詢關于活躍表空間的相關信息（主要記錄的是NDB存儲引擎表空間信息）
注意：該表不提供有關InnoDB存儲引擎的表空間的信息。 對于InnoDB表空間元數據信息，請查詢INNODB_SYS_TABLESPACES和INNODB_SYS_DATAFILES表。另外，從MySQL 5.7.8開始，INFORMATION_SCHEMA.FILES表也提供查詢InnoDB表空間的元數據信息
該表為Memory引擎臨時表。
SCHEMATA：
提供查詢MySQL Server中的數據庫列表信息，一個schema就代表一個database
該表為Memory引擎臨時表
VIEWS：
提供查詢數據庫中的視圖相關的信息，查詢該表的帳號需要擁有show view權限
該表為InnoDB引擎臨時表
TRIGGERS：
提供查詢關于某個數據庫下的觸發器相關的信息，要查詢某個表的觸發器，查詢的賬戶必須要有trigger權限
該表為InnoDB引擎臨時表
TABLES：
提供查詢數據庫內的表相關的基本信息
該表為Memory引擎臨時表
ROUTINES：
提供查詢關于存儲過程和存儲函數的信息（不包括用戶自定義函數UDF），該表中的信息與“mysql.proc”中記錄的信息相對應（如果該表中有值的話）
該表為InnoDB引擎臨時表
PARTITIONS：
提供查詢關于分區表的信息
該表為InnoDB引擎臨時表
EVENTS：
提供查詢計劃任務事件相關的信息
該表是InnoDB引擎臨時表
PARAMETERS：
提供有關存儲過程和函數的參數信息，以及有關存儲函數的返回值的信息。 這些參數信息與mysql.proc表中的param_list列記錄的內容類似
該表為InnoDB引擎臨時表
GLOBAL_STATUS、GLOBAL_VARIABLES、SESSION_STATUS、SESSION_VARIABLES：
提供查詢全局、會話級別的的狀態變量與系統變量信息，這些表為Memory引擎臨時表
OPTIMIZER_TRACE：
提供優化程序跟蹤功能產生的信息。
跟蹤功能默認關閉，使用optimizer_trace系統變量啟用跟蹤功能。如果開啟該功能，則每個會話只能跟蹤他自己執行的語句，不能看到其他會話執行的語句，且每個會話只能記錄最后一個跟蹤的SQL語句
該表為InnoDB引擎臨時表
PLUGINS：
提供查詢關于MySQL Server中支持哪些插件的信息
該表為InnoDB引擎臨時表
PROCESSLIST：
提供查詢一些關于線程運行過程中的狀態信息
該表為InnoDB引擎臨時表
PROFILING：
提供查詢關于語句性能分析的信息。其記錄內容對應于SHOW PROFILES和SHOW PROFILE語句產生的信息。該表需要在會話變量 profiling=1時才會記錄語句性能分析信息，否則該表不記錄。
注意：從MySQL 5.7.2開始，此表不再推薦使用，在未來的MySQL版本中刪除。改用Performance Schema;代替
該表為Memory引擎臨時表
CHARACTER_SETS：
提供查詢MySQL Server支持的可用字符集有哪些
該表為Memory引擎臨時表
COLLATIONS：
提供查詢MySQL Server支持的可用校對規則有哪些
該表為Memory引擎臨時表
COLLATION_CHARACTER_SET_APPLICABILITY：
提供查詢MySQL Server中哪種字符集適用于什么校對規則。查詢結果集相當于從SHOW COLLATION獲得的結果集中的前兩個字段值。該表目前并沒有發現有太大作用，為Memory引擎臨時表
COLUMN_PRIVILEGES：
提供查詢關于列(字段)的權限信息，表中的內容來自mysql.column_priv列權限表（需要針對一個表的列單獨授權之后才會有內容）
該表為Memory引擎臨時表
SCHEMA_PRIVILEGES：
提供查詢關于庫級別的權限信息，每種類型的庫級別權限記錄一行信息，該表中的信息來自mysql.db表
該表為Memory引擎臨時表
TABLE_PRIVILEGES：
提供查詢關于表級別權限信息，該表中的內容來自mysql.tables_priv
該表為Memory引擎臨時表
USER_PRIVILEGES：
提供查詢全局權限的信息，該表中的信息來自mysql.user表
該表為Memory引擎臨時表
INNODB_SYS_DATAFILES：
提供查詢InnoDB file-per-table和常規表空間數據文件的路徑信息，等同于InnoDB數據字典中SYS_DATAFILES表中的信息
該表中的信息包含InnoDB所有表空間類型的元數據，包括獨立表空間、常規表空間、系統表空間、臨時表空間和undo表空間（如果開啟了獨立表空間的話）
該表為memory引擎臨時表，查詢該表的用戶需要有process權限。
INNODB_SYS_VIRTUAL：
提供查詢有關InnoDB虛擬生成列和與之關聯的列的元數據信息，等同于InnoDB數據字典內部SYS_VIRTUAL表中的信息。INNODB_SYS_VIRTUAL表中展示的行信息是虛擬生成列相關聯列的每個列的信息。
該表為memory引擎臨時表，查詢該表的用戶需要有process權限
INNODB_SYS_INDEXES：
提供查詢有關InnoDB索引的元數據信息，等同于InnoDB數據字典內部SYS_INDEXES表中的信息
該表為memory引擎臨時表，查詢該表的用戶需要具有process權限
INNODB_SYS_TABLES：
提供查詢有關InnoDB表的元數據，等同于InnoDB數據字典內部SYS_TABLES表的信息。
該表為memory引擎臨時表，查詢該表的用戶需要有process權限
INNODB_SYS_FIELDS：
提供查詢有關InnoDB索引鍵列（字段）的元數據信息，等同于InnoDB數據字典內部SYS_FIELDS表的信息
該表為memory引擎臨時表，查詢該表的用戶需要有process權限
INNODB_SYS_TABLESPACES：
提供查詢有關InnoDB獨立表空間和普通表空間的元數據信息（也包含了全文索引表空間），等同于InnoDB數據字典內部SYS_TABLESPACES表中的信息
該表為memory引擎臨時表，查詢該表的用戶需要有process權限
INNODB_SYS_FOREIGN_COLS：
提供查詢有關InnoDB外鍵列的狀態信息，等同于InnoDB數據字典內部SYS_FOREIGN_COLS表的信息
該表為memory引擎臨時表，查詢該表的用戶需要有process權限
INNODB_SYS_COLUMNS：
提供查詢有關InnoDB表列的元數據信息，等同于InnoDB數據字典內部SYS_COLUMNS表的信息
該表為memory引擎臨時表，查詢該表的用戶需要具有process權限
INNODB_SYS_FOREIGN：
提供查詢有關InnoDB外鍵的元數據信息，等同于InnoDB數據字典內部SYS_FOREIGN表的信息
該表為memory引擎臨時表，查詢該表的用戶需要有process權限
INNODB_SYS_TABLESTATS：
提供查詢有關InnoDB表的較低級別的狀態信息視圖。 MySQL優化器會使用這些統計信息數據來計算并確定在查詢InnoDB表時要使用哪個索引。這些信息保存在內存中的數據結構中，與存儲在磁盤上的數據無對應關系。InnoDB內部也無對應的系統表。
該表為memory引擎臨時表，查詢該表的用戶需要有process權限
INNODB_LOCKS：
提供查詢innodb引擎事務中正在請求的且并未獲得的且同時阻塞了其他事務的鎖信息(即沒有發生不同事務之間的鎖等待的鎖信息，在這里是查看不到的，例如，只有一個事務時，該事務所加的鎖信息無法查看到)。該表中的內容可以用于診斷高并發下的鎖爭用信息。
該表為memory引擎臨時表，訪問該表需要擁有具有process權限
INNODB_TRX：
提供查詢當前在InnoDB引擎中執行的每個事務（不包括只讀事務）的信息，包括事務是否正在等待鎖、事務什么時間點開始、以及事務正在執行的SQL語句文本信息等（如果有SQL的話）。
該表為memory引擎臨時表，查詢該表的用戶需要有process權限
INNODB_BUFFER_PAGE_LRU：
提供查詢緩沖池中的頁面信息，與INNODB_BUFFER_PAGE表不同，INNODB_BUFFER_PAGE_LRU表保存有關innodb buffer pool中的頁如何進入LRU鏈表以及在buffer pool不夠用時確定需要從緩沖池中逐出哪些頁
該表為Memory引擎臨時表
INNODB_LOCK_WAITS：
提供查詢關于每個被阻塞的InnoDB事務的鎖等待記錄，包括發生鎖等帶事務所請求的鎖和阻止該鎖請求被授予的鎖
該表為memory引擎表，訪問該表用戶需要有process權限
INNODB_TEMP_TABLE_INFO：
提供查詢有關在InnoDB實例中當前處于活動狀態的用戶(已建立連接的用戶，斷開的用戶連接對應的臨時表會被自動刪除)創建的InnoDB臨時表的信息。 它不提供查詢優化器使用的內部InnoDB臨時表的信息查詢。INNODB_TEMP_TABLE_INFO表在首次查詢時創建。
該表為memory引擎臨時表，查詢該表的用戶需要有process權限
INNODB_BUFFER_PAGE：
提供查詢關于buffer pool中的頁相關的信息
查詢該表需要用戶具有PROCESS權限，該表為Memory引擎臨時表
INNODB_METRICS：
提供查詢InnoDB更為詳細細致的性能信息，是對InnoDB的PERFORMANCE_SCHEMA的補充。通過對該表的查詢，可用于檢查innodb的整體健康狀況。也可用于診斷性能瓶頸、資源短缺和應用程序的問題等。
該表為memory引擎臨時表，查詢該表的用戶需要有process權限
INNODB_BUFFER_POOL_STATS：
提供查詢一些Innodb buffer pool中的狀態信息，該表中記錄的信息與SHOW ENGINE INNODB STATUS輸出的信息類似相同，另外，innodb buffer pool的一些狀態變量也提供了部分相同的值
查看該表需要有process權限，該表為Memory引擎臨時表
INNODB_FT_CONFIG：
提供查詢有關InnoDB表的FULLTEXT索引和關聯的元數據信息。查詢此表之前，需要先設置innodb_ft_aux_table='db_name/tb_name'，db_name/tb_name為包含全文索引的表名和庫名。
查詢該表的賬戶需要有PROCESS權限，該表為Memory引擎臨時表
INNODB_FT_BEING_DELETED：
該表僅在OPTIMIZE TABLE語句執行維護操作期間作為INNODB_FT_DELETED表的快照數據存放使用。運行OPTIMIZE TABLE語句時，會先清空INNODB_FT_BEING_DELETED表中的數據，保存INNODB_FT_DELETED表中的快照數據到INNODB_FT_BEING_DELETED表，并從INNODB_FT_DELETED表中刪除DOC_ID。由于INNODB_FT_BEING_DELETED表中的內容通常生命周期較短，因此該表中的數據對于監控或者調試來說用處并不大。
表中默認不記錄數據，需要設置系統配置參數innodb_ft_aux_table=string（string表示db_name.tb_name字符串），并創建好全文索引，設置好停用詞等。
查詢該表的賬戶需要有PROCESS權限，該表為Memory引擎臨時表
INNODB_FT_DELETED：
提供查詢從InnoDB表的FULLTEXT索引中刪除的行信息。它的存在是為了避免在InnoDB FULLTEXT索引的DML操作期間進行昂貴的索引重組操作，新刪除的全文索引中單詞的信息將單獨存儲在該表中，在執行文本搜索時從中過濾出搜索結果，該表中的信息僅在執行OPTIMIZE TABLE語句時清空。
該表中的信息默認不記錄，需要使用innodb_ft_aux_table選項(該選項默認值為空串)指定需要記錄哪個innodb引擎表的信息，例如：test/test。
查詢該表的賬戶需要有PROCESS權限，該表為Memory引擎臨時表
INNODB_FT_DEFAULT_STOPWORD：
該表為默認的全文索引停用詞表，提供查詢停用詞列表值。啟用停用詞表需要開啟參數innodb_ft_enable_stopword=ON，該參數默認為ON，啟用停用詞功能之后，如果innodb_ft_user_stopword_table選項（針對指定的innodb引擎表中的全文索引生效）自定義了停用詞庫表名稱值，則停用詞功能使用innodb_ft_user_stopword_table選項指定的停用詞表，如果innodb_ft_user_stopword_table選項未指定，而innodb_ft_server_stopword_table選項（針對所有的innodb引擎表中的全文索引生效）自定義了停用詞庫表名稱值，則同停用詞功能使用innodb_ft_server_stopword_table選項指定的停用詞表，如果innodb_ft_server_stopword_table選項也未指定，則使用默認的停用詞表，即INNODB_FT_DEFAULT_STOPWORD表。
查詢該表需要賬戶有PROCESS權限，該表為Memory引擎臨時表
INNODB_FT_INDEX_TABLE：
提供查詢關于innodb表全文索引中用于反向文本查找的倒排索引的分詞信息。
查詢該表的賬戶需要有PROCESS權限，該表為Memory引擎臨時表
INNODB_FT_INDEX_CACHE：
提供查詢包含FULLTEXT索引的innodb存儲引擎表中新插入行的全文索引標記信息。它存在的目的是為了避免在DML操作期間進行昂貴的索引重組，新插入的全文索引的單詞的信息被單獨存儲在該表中，直到對表執行OPTIMIZE TABLE語句時、或者關閉服務器時、或者當高速緩存中存放的信息大小超過了innodb_ft_cache_size或innodb_ft_total_cache_size系統配置參數指定的大小才會執行清理。默認不記錄數據，需要使用innodb_ft_aux_table系統配置參數指定需要記錄哪個表中的新插入行的全文索引數據。
查詢該表的賬戶需要有PROCESS權限，該表為Memory引擎臨時表
INNODB_CMP和INNODB_CMP_RESET：
這兩個表中的數據包含了與壓縮的InnoDB表頁有關的操作的狀態信息。表中記錄的數據為測量數據庫中的InnoDb表壓縮的有效性提供參考。
查詢表的用戶必須具有PROCESS權限，該表為Memory引擎臨時表
INNODB_CMP_PER_INDEX和INNODB_CMP_PER_INDEX_RESET：
這兩個表中記錄著InnoDB壓縮表數據和索引相關的操作狀態信息，對數據庫、表、索引的每個組合使用不同的統計信息，以便為評估特定表的壓縮性能和實用性提供參考數據。
對于InnoDB壓縮表，會對表中的數據和所有二級索引都進行壓縮。此時表中的數據被視為另一個索引(包含所有數據列的聚集索引）。
注意：由于為每個索引收集單獨的度量值會導致性能大幅度降低，因此默認情況下不收集INNODB_CMP_PER_INDEX和INNODB_CMP_PER_INDEX_RESET表統計信息。如果確有需要，啟用系統配置參數innodb_cmp_per_index_enabled即可（該配置參數為動態變量，默認為OFF）。
查詢該表的賬戶需要有PROCESS權限，該表為Memory引擎臨時表
INNODB_CMPMEM和INNODB_CMPMEM_RESET：
這兩個表中記錄著InnoDB緩沖池中壓縮頁上的狀態信息，為測量數據庫中InnoDB表壓縮的有效性提供參考
查詢該表的賬戶需要有PROCESS權限，該表為Memory引擎臨時表
“怎么理解并掌握mysql中的information_schema”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
