一、information_schema簡介

在MySQL中，把 information_schema 看作是一個數據庫，確切說是信息數據庫。其中保存著關于MySQL服務器所維護的所有其他數據庫的信息。如數據庫名，數據庫的表，表欄的數據類型與訪問權限等。在INFORMATION_SCHEMA中，有數個只讀表。它們實際上是視圖，而不是基本表，因此，你將無法看到與之相關的任何文件。

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| OPTIMIZER_TRACE                       |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| INNODB_LOCKS                          |
| INNODB_TRX                            |
| INNODB_SYS_DATAFILES                  |
| INNODB_FT_CONFIG                      |
| INNODB_SYS_VIRTUAL                    |
| INNODB_CMP                            |
| INNODB_FT_BEING_DELETED               |
| INNODB_CMP_RESET                      |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMPMEM_RESET                   |
| INNODB_FT_DELETED                     |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_LOCK_WAITS                     |
| INNODB_TEMP_TABLE_INFO                |
| INNODB_SYS_INDEXES                    |
| INNODB_SYS_TABLES                     |
| INNODB_SYS_FIELDS                     |
| INNODB_CMP_PER_INDEX_RESET            |
| INNODB_BUFFER_PAGE                    |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_SYS_TABLESPACES                |
| INNODB_METRICS                        |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_CMPMEM                         |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_SYS_COLUMNS                    |
| INNODB_SYS_FOREIGN                    |
| INNODB_SYS_TABLESTATS                 |
+---------------------------------------+
61 rows in set (0.00 sec)

二、information_schema數據庫表說明:

SCHEMATA表：提供了當前mysql實例中所有數據庫的信息。是show databases的結果取之此表。
TABLES表：提供了關于數據庫中的表的信息（包括視圖）。詳細表述了某個表屬于哪個schema，表類型，表引擎，創建時間等信息。是show tables from schemaname的結果取之此表。
COLUMNS表：提供了表中的列信息。詳細表述了某張表的所有列以及每個列的信息。是show columns from schemaname.tablename的結果取之此表。
STATISTICS表：提供了關于表索引的信息。是show index from schemaname.tablename的結果取之此表。
USER_PRIVILEGES（用戶權限）表：給出了關于全程權限的信息。該信息源自mysql.user授權表。是非標準表。
SCHEMA_PRIVILEGES（方案權限）表：給出了關于方案（數據庫）權限的信息。該信息來自mysql.db授權表。是非標準表。
TABLE_PRIVILEGES（表權限）表：給出了關于表權限的信息。該信息源自mysql.tables_priv授權表。是非標準表。
COLUMN_PRIVILEGES（列權限）表：給出了關于列權限的信息。該信息源自mysql.columns_priv授權表。是非標準表。
CHARACTER_SETS（字符集）表：提供了mysql實例可用字符集的信息。是SHOW CHARACTER SET結果集取之此表。
COLLATIONS表：提供了關于各字符集的對照信息。
COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校對的字符集。這些列等效于SHOW COLLATION的前兩個顯示字段。
TABLE_CONSTRAINTS表：描述了存在約束的表。以及表的約束類型。
KEY_COLUMN_USAGE表：描述了具有約束的鍵列。
ROUTINES表：提供了關于存儲子程序（存儲程序和函數）的信息。此時，ROUTINES表不包含自定義函數（UDF）。名為“mysql.proc name”的列指明了對應于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。
VIEWS表：給出了關于數據庫中的視圖的信息。需要有show views權限，否則無法查看視圖信息。
TRIGGERS表：提供了關于觸發程序的信息。必須有super權限才能查看該表

三、按類別劃分

1：關于字符集和排序規則相關的系統表

CHARACTER_SETS ：存儲數據庫相關字符集信息（memory存儲引擎）

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

COLLATION_CHARACTER_SET_APPLICABILITY：就是一個字符集和連線校對的一個對應關系而已

下面我們說一下character sets和collations的區別：

字符集（character sets）存儲字符串，是指人類語言中最小的表義符號。例如’A'、’B'等；

排序規則（collations）規則比較字符串，collations是指在同一字符集內字符之間的比較規則

每個字符序唯一對應一種字符集，但一個字符集可以對應多種字符序，其中有一個是默認字符序(Default Collation)

?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時候昂貴的索引重組