介紹一些MySQL information_schema庫相關知識

下面一起來了解下MySQL information_schema庫相關知識，相信大家看完肯定會受益匪淺，文字在精不在多，希望MySQL information_schema庫相關知識這篇短內容是你想要的。

mysql> show create table test.test\G

1 row in *************************** 1. row ***************************

       Table: test

Create Table: CREATE TABLE `test` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `a` int(11) unsigned DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `a` (`a`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8set (0.00 sec)

parameters

information_schema

一、關于字符集和排序規則相關的系統表

CHARACTER_SETS                         -- 字符集

COLLATIONS                             -- 字符集校驗集

COLLATION_CHARACTER_SET_APPLICABILITY  -- 字符集和字符校驗集的關系 這些列等效于SHOW COLLATION的前兩個顯示字段。

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

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

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

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

看一下有關于字符集和校對相關的MySQL變量：

character_set_client：客戶端來源數據使用的字符集

character_set_connection：連接層字符集

character_set_database：當前選中數據庫的默認字符集

character_set_system：系統元數據(字段名等)字符集

character_set_server：默認的內部操作字符集

character_set_results：查詢結果字符集

再看一下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。

二、權限相關的表

SCHEMA_PRIVILEGES    -- 提供了數據庫的相關權限，信息來自mysql.db

TABLE_PRIVILEGES     -- 提供的是表權限相關信息，信息來自mysql.tables_priv 表中加載的

COLUMN_PRIVILEGES    -- 給出了關于列權限的信息。該信息源自mysql.columns_priv授權表

USER_PRIVILEGES      -- 提供的是表權限相關信息，信息來自mysql.user 表

三、存儲數據庫系統的實體對象的一些表

COLUMNS                                -- select * from COLUMNS where TABLE_NAME='test' \G  等價于 desc test.test  或者 show columns from test.test; 

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

ENGINES                                -- 支持的engine和默認的engine 

EVENTS                                 -- 等價于 show events 或者mysql.event，備份是要加上--event，主庫設置event_scheduler=ON，從庫event_scheduler=OFF

FILES                                  -- 表空間文件，MySQL的表空間中的數據存儲的文件的信息，文件存儲的位置，

PARAMETERS                             -- 參數表存儲了一些存儲過程和方法的參數，以及存儲過程的返回值信息。存儲和方法在ROUTINES里面存儲。

PLUGINS                                -- 基本上是MySQL的插件信息，是否是活動狀態等信息。SHOW PLUGINS的信息來自此

ROUTINES                               --關于存儲過程和方法function的一些信息以及幫助文檔，不過這個信息是不包括用戶自定義的，只是系統的一些信息。mysql.proc name

SCHEMATA                               --這個表提供了實例下有多少個數據庫，而且還有數據庫默認的字符集

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

VIEWS                                  -- 視圖的信息，也是系統的和用戶的基本視圖信息。

四、約束外鍵等相關的一些表

REFERENTIAL_CONSTRAINTS              --提供的外鍵相關的信息，而且只提供外鍵相關信息

TABLE_CONSTRAINTS                    --提供的是相關的約束信息,比較全面

INNODB_SYS_FOREIGN_COLS              --存儲的INNODB關于外鍵的元數據信息和SYS_FOREIGN_COLS 存儲的信息是一致的

INNODB_SYS_FOREIGN                   --存儲的INNODB關于外鍵的元數據信息和SYS_FOREIGN_COLS 存儲的信息是一致的，只不過是單獨對于INNODB來說的

KEY_COLUMN_USAGE                     --描述了具有約束的鍵列。

五、關于管理的一些的一些表：

GLOBAL_STATUS                          -- 不可查，see the documentation for 'show_compatibility_56'  show global status 

GLOBAL_VARIABLES                       -- 不可查，see the documentation for 'show_compatibility_56'  show global variables 

SESSION_STATUS                         -- 不可查，see the documentation for 'show_compatibility_56'  show  status 

SESSION_VARIABLES                      -- 不可查，see the documentation for 'show_compatibility_56'  show  variables 

PARTITIONS                             -- MySQL分區表相關的信息

PROCESSLIST                            --SHOW PROCESSLIST 數據來自此表

INNODB_CMP_PER_INDEX                   -- 存儲關于壓縮INNODB表的相關信息

INNODB_CMP_PER_INDEX_RESET             -- 存儲關于壓縮INNODB表的相關信息

INNODB_CMPMEM                          -- 存放關于MySQL INNODB的壓縮頁的buffer pool信息，和show variables like 'innodb_cmp_per_index_enabled';有關，打開影響性能

INNODB_CMPMEM_RESET                    -- 存放關于MySQL INNODB的壓縮頁的buffer pool信息，和show variables like 'innodb_cmp_per_index_enabled';有關，打開影響性能

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

INNODB_BUFFER_PAGE_LRU                 -- 維護了INNODB LRU LIST的相關信息

INNODB_BUFFER_PAGE                     --存放的是buffer里面緩沖的頁數據，查詢這個表會對性能產生很嚴重的影響

INNODB_SYS_DATAFILES                   --這張表就是記錄的表的文件存儲的位置和表空間的一個對應關系

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表的虛擬列的信息，

INNODB_CMP    --存儲的是關于壓縮INNODB信息表的時候的相關信息

INNODB_CMP_RESET  --存儲的是關于壓縮INNODB信息表的時候的相關信息

六、關于表信息和索引信息的一些表

TABLES   -- 記錄的數據庫中表的信息，其中包括系統數據庫和用戶創建的數據庫。show table status like 'test1'\G的來源就是這個表

TABLESPACES   -- 這個表是不提供關于innodb的表空間信息的，對于我們來說并沒有太大作用，因為我們生產庫是強制INNODB的；

INNODB_SYS_TABLES  -- 此表提供了有關表格的格式和存儲特性，包括行格式，壓縮頁面大小位級別的信息

INNODB_SYS_TABLESPACES   -- 提供的是關于INNODB的表空間信息，其實和SYS_TABLESPACES 中的INNODB信息是一致的。

STATISTICS          -- 提供關于表的索引信息，所有索引的相關信息。

INNODB_SYS_INDEXES --提供相關INNODB表的索引的相關信息，和 SYS_INDEXES 這個表存儲的信息基本是一樣的，

INNODB_SYS_TABLESTATS -- 重要，記錄的是MySQL的INNODB表信息以及MySQL優化器會預估SQL選擇合適的索引信息，其實就是MySQL數據庫的統計信息

INNODB_SYS_FIELDS   --存放INNODB的表索引字段信息，以及字段的排名

INNODB_FT_CONFIG  --存放的是全文索引的信息

INNODB_FT_DEFAULT_STOPWORD  -- 存放的是stopword 的信息,是和全文索引匹配起來使用的

INNODB_FT_INDEX_TABLE    --存儲的是關于INNODB表有全文索引的索引使用信息的，同樣這個表也是要設置innodb_ft_aux_table以后才能夠使用的，一般情況下是空的

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

七、關于MySQL優化相關的一些表

OPTIMIZER_TRACE                        -- 查看執行計劃的選擇，set optimizer_trace="enabled=on";SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000; desc select * ...; select OPTIMIZER_TRACE\G

PROFILING                       --SHOW PROFILES,不能查看和剖析其他連接的語句，以及剖析時所引起的消耗。profiling_history_size 設為0等價于關閉分析功能

INNODB_FT_BEING_DELETED         --INNODB_FT_BEING_DELETED 這張表是INNODB_FT_DELETED的一個快照

INNODB_FT_DELETED               -- OPTIMIZE TABLE 的時候才會使用

八、關于MySQL事物和鎖的相關的一些表

INNODB_LOCKS                          INNODB_LOCKS表主要包含了InnoDB事務鎖的具體情況，包括事務正在申請加的鎖和事務加上的鎖。

INNODB_TRX                            INNODB_TRX表主要是包含了正在InnoDB引擎中執行的所有事務的信息，包括waiting for a lock和running的事務

INNODB_LOCK_WAITS                     INNODB_LOCK_WAITS表包含了blocked的事務的鎖等待的狀態

看完MySQL information_schema庫相關知識這篇文章后，很多讀者朋友肯定會想要了解更多的相關內容，如需獲取更多的行業信息，可以關注我們的行業資訊欄目。