MySQL系統庫匯總

簡介

performance_schema

作用

分類

簡單配置與使用

查看最近執行失敗的SQL語句

查看最近的事務執行信息

sys系統庫

作用

使用

查看慢SQL語句慢在哪

information_schema

作用

分類

應用

查看索引列的信息

mysql系統庫

權限系統表

統計信息表

日志記錄表

general_log

slow_log

InnoDB的統計信息

分類

信息參考：MySQL官網

簡介

MySQL有4個系統數據庫，這4個數據庫包含了MySQL服務器運行過程中所需的一些信息以及一些運行狀態信息。

performance_schema：這個數據庫里主要保存MySQL服務器運行過程中的一些狀態信息，算是對MySQL服務器的一個性能監控。其中包括統計最近執行了哪些語句，在執行過程的每個階段都花費了多長時間，內存的使用情況等等信息。
information_schema：這個數據庫保存著MySQL服務器維護的所有其他數據庫的信息，比如有哪些表、哪些視圖、哪些觸發器、哪些列、哪些索引。這些是一些描述性信息，稱之為元數據。
sys：通過視圖的形式把information_schema和performance_schema結合起來，讓程序員可以更方便的了解MySQL服務器的一些性能信息。
mysql：主要存儲了MySQL的用戶賬戶和權限信息，還有一些存儲過程、事件的定義信息，一些運行過程中產生的日志信息，一些幫助信息以及時區信息等。

performance_schema

MySQL的performance_schema 是運行在較低級別的用于監控MySQL Server運行過程中的資源消耗、資源等待等情況的一個功能特性。

運行時較低級別：采集的信息相比底層，比如磁盤文件、表IO、表鎖等。

作用

提供數據庫運行時實施檢查Server內部執行情況的方法。
通過監聽Server的事件來實現監聽其內部執行情況。采集事件可以方便地提供Server中的相關存儲引擎對磁盤文件、表I/O、表鎖等資源的同步調用信息。
當前活躍事件、歷史事件和事件摘要相關表中記錄的信息，能提供某個事件的執行次數、使用時長，進而可用于分析與某個特定線程、特定對象（如mutex或file）相關聯的活動。
performance_schema存儲引擎使用Server源代碼中的“檢測點”來實現事件數據的收集。對于performance_schema實現機制本身的代碼沒有相關的單獨線程來檢測，這與其他功能（如復制或事件計劃程序）不同。
表中數據不會持久化存儲在磁盤中，而是保存在內存中，一旦服務器重啟，這些數據就會丟失

簡單配置與使用

當數據庫初始化完成并啟動時，并非所有的instruments和consumers都啟用了，所以默認不會收集所有的事件。

可能你想檢測的事件并沒有打開，需要進行設置。可以使用如下兩條語句打開對應的instruments和consumers，我們以配置監測等待事件數據為例進行說明。

-- 打開等待事件的采集器配置項開關，需要修改setup_instruments 配置表中對應的采集器配置項。
update setup_instruments set enabled='yes',timed='yes' where name like 'wait%';
-- 打開等待事件的保存表配置項開關，修改setup_consumers 配置表中對應的配置項。
update setup_consumers set enabled='yes' where name like 'wait%';

配置好之后，就可以查看Server當前正在做什么了。

可以通過查詢events_waits_current表來得知，該表中每個線程只包含一行數據，用于顯示每個線程的最新監視事件（正在做的事情）。

_current表中每個線程只保留一條記錄，且一旦線程完成工作，該表中就不會再記錄該線程的事件信息了。history表中記錄每個線程已經執行完成的事件信息，但每個線程的事件信息只記錄10條，再多就會被覆蓋掉。*history_long表中記錄所有線程的事件信息，但總記錄數量是10000行，超過會被覆蓋掉。

summary表提供所有事件的匯總信息。該組中的表以不同的方式匯總事件數據（如：按用戶、按主機、按線程等匯總）。

查看最近執行失敗的SQL語句

使用代碼對數據庫的某些操作（比如：使用Java的ORM框架操作數據庫）報出語法錯誤，但是代碼并沒有記錄SQL語句文本的功能，在MySQL數據庫層能否查看到具體的SQL語句文本，看看是否哪里寫錯了？這個時候，大多數人首先想到的就是去查看錯誤日志。很遺憾，對于SQL語句的語法錯誤，錯誤日志并不會記錄。

實際上，在performance_schema的語句事件記錄表中針對每一條語句的執行狀態都記錄了較為詳細的信息

例如：events_statements表和events_statements_summary_by_digest表（events_statements表記錄了語句所有的執行錯誤信息，而events_statements_summary_by_digest表只記錄了語句在執行過程中發生錯誤的語句記錄統計信息，不記錄具體的錯誤類型，例如：不記錄語法錯誤類的信息）下面看看如何使用這兩個表查詢語句發生錯誤的語句信息。

-- 查詢event_statements_history 錯誤號為1064的記錄
select * from events_statements_history where mysql_errno=1064\G-- 如果不知道錯誤號是多少，可以查詢發生錯誤次數不為0的語句記錄，
-- 在里邊找到SQL_TEXT和MESSAGE_TEXT字段（提示信息為語法錯誤的就是它）。

查看最近的事務執行信息

我們可以通過慢查詢日志查詢到一條語句的執行總時長，但是如果數據庫中存在著一些大事務在執行過程中回滾了，或者在執行過程中異常中止，這個時候慢查詢日志就愛莫能助了;這時我們可以借助performance_schema的events_transactions_*表來查看與事務相關的記錄，在這些表中詳細記錄了是否有事務被回滾、活躍（長時間未提交的事務也屬于活躍事務）或已提交等信息。

-- 首先需要進行配置啟用，事務事件默認并未啟用
update setup_instruments set enabled='yes',timed='yes' where name like 'transaction%';
update setup_consumers set enabled='yes' where name like '%transaction%';

當然performance_schema的用途不止上面這些，它還能提供比如查看SQL語句執行階段和進度信息、MySQL集群下復制功能查看復制報錯詳情等等。

sys系統庫

sys系統庫支持MySQL 5.6或更高版本，不支持MySQL 5.5.x及以下版本。

sys系統庫通常都是提供給專業的DBA人員排查一些特定問題使用的，其下所涉及的各項查詢或多或少都會對性能有一定的影響。因為sys系統庫提供了一些代替直接訪問performance_schema的視圖，所以必須啟用performance_schema（將performance_schema系統參數設置為ON），sys系統庫的大部分功能才能正常使用。同時要完全訪問sys系統庫，用戶必須具有以下數據庫的管理員權限。

-- 啟用所有的wait instruments：
CALL sys.ps_setup_enable_instrument('wait');
-- 啟用所有事件類型的current表：
CALL sys.ps_setup_enable_consumer('current');

作用

在sys系統庫下包含很多視圖，它們以各種方式對performance_schema表進行聚合計算展示。

這些視圖大部分是成對出現的，兩個視圖名稱相同，但有一個視圖是帶 x host_summary_by_file_io和 x$host_summary_by_file_io 帶x前綴的視圖顯示的是原始的數據（單位是皮秒）

使用

查看慢SQL語句慢在哪

如果我們頻繁地在慢查詢日志中發現某個語句執行緩慢，且在表結構、索引結構、統計信息中都無法找出原因時，則可以利用sys系統庫中的撒手锏：sys.session視圖結合performance_schema的等待事件來找出癥結所在。

那么session視圖有什么用呢？使用它可以查看當前用戶會話的進程列表信息，看看當前進程到底再干什么

這個視圖在MySQL 5.7.9中才出現。

select * from session where command='query' and conn_id !=connection_id()\G

查詢表的增、刪、改、查數據量和I/O耗時統計

select * from schema_table_statistics_with_buffer\G

除此之外，通過sys還可以查詢查看InnoDB緩沖池中的熱點數據、查看是否有事務鎖等待、查看未使用的，冗余索引、查看哪些語句使用了全表掃描等等。

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等）。

PS：根據MySQL版本的不同，表的個數和存放是有所不同的。

在MySQL 8.0版本中，該schema下的數據字典表（包含部分原Memory引擎臨時表）都遷移到了mysql schema下，且在mysql schema下這些數據字典表被隱藏，無法直接訪問，需要通過information_schema下的同名表進行訪問。

information_schema下的所有表使用的都是Memory和InnoDB存儲引擎，且都是臨時表，不是持久表，在數據庫重啟之后這些數據會丟失。在MySQL 的4個系統庫中，information_schema也是唯一一個在文件系統上沒有對應庫表的目錄和文件的系統庫。

分類

Server層統計信息字典表
- Columns 查詢表中列信息
- key_columns_usage 查詢哪些索引列存在約束條件該表中的信息包含主鍵、唯一索引、外鍵等約束信息
- Referential_constraints 提供查詢關于外鍵約束的一些信息。
- STATISTICS 提供查詢關于索引的一些統計信息，一個索引對應一行記錄。
- TABLE_CONSTRAINTS 提供查詢與表相關的約束信息
- FILES 提供查詢與MySQL的數據表空間文件相關的信息。
- ENGINES 提供查詢MySQL Server支持的引擎相關信息。
- TABLESPACES 提供查詢關于活躍表空間的相關信息
- SCHEMATA 提供查詢MySQL Server中的數據庫列表信息，一個schema就代表一個數據庫。
Server層表級別對象字典表
- Views 提供查詢數據庫中的視圖相關信息。
- Triggers 提供查詢關于某個數據庫下的觸發器相關信息。
- Tables 提供查詢與數據庫內的表相關的基本信息。
- Routines 提供查詢關于存儲過程和存儲函數的信息
- Partitions 提供查詢關于分區表的信息。
- Events 提供查詢與計劃任務事件相關的信息。
- Parameters 提供有關存儲過程和函數的參數信息，以及有關存儲函數的返回值信息。
Server層混雜信息字典表
- GLOBAL_STATUS、GLOBAL_VARIABLES、SESSION_STATUS、SESSION_VARIABLES
- OPTIMIZER_TRACE
- PLUGINS
- ·············太多了
InnoDB層的鎖、事務、統計信息字典表

??提供查詢有關InnoDB外鍵列的狀態信息，等同于InnoDB數據字典內部SYS_FOREIGN_COLS表的信息。
- INNODB_SYS_DATAFILES 提供查詢InnoDB所有表空間類型文件的元數據（內部使用的表空間ID和表空間文件的路徑信息），包括獨立表空間、常規表空間、系統表空間、臨時表空間和undo空間（如果開啟了獨立undo空間的話）。
- INNODB_SYS_VIRTUAL 提供查詢有關InnoDB虛擬生成列和與之關聯的列的元數據信息，等同于InnoDB數據字典內部SYS_VIRTUAL表的信息。該表中展示的行信息是與虛擬生成列相關聯列的每個列的信息。
- INNODB_SYS_INDEXES 提供查詢有關InnoDB索引的元數據信息，等同于InnoDB數據字典內部SYS_INDEXES表中的信息。
- INNODB_SYS_TABLES 提供查詢有關InnoDB表的元數據信息，等同于InnoDB數據字典內部SYS_TABLES表的信息。
- INNODB_SYS_FIELDS 提供查詢有關InnoDB索引鍵列（字段）的元數據信息，等同于InnoDB數據字典內部SYS_FIELDS表的信息
- INNODB_SYS_TABLESPACES 提供查詢有關InnoDB獨立表空間和普通表空間的元數據信息（也包含了全文索引表空間），等同于InnoDB數據字典內部SYS_TABLESPACES表的信息。
- INNODB_SYS_FOREIGN_COLS
- INNODB_SYS_COLUMNS 提供查詢有關InnoDB外鍵的元數據信息，等同于InnoDB數據字典內部SYS_FOREIGN表的信息。
- INNODB_SYS_FOREIGN 提供查詢有關InnoDB外鍵的元數據信息，等同于InnoDB數據字典內部SYS_FOREIGN表的信息。
- INNODB_SYS_TABLESTATS 提供查詢有關InnoDB表的較低級別的狀態信息視圖。 MySQL優化器會使用這些統計信息數據來計算并確定在查詢InnoDB表時要使用哪個索引。這些信息保存在內存中的數據結構中，與存儲在磁盤上的數據無對應關系。在InnoDB內部也無對應的系統表。
InnoDB層的鎖、事務、統計信息字典表
InnoDB層的全文索引字典表
InnoDB層的壓縮相關字典表

應用

查看索引列的信息

INNODB_SYS_FIELDS表提供查詢有關InnoDB索引列（字段）的元數據信息，等同于InnoDB數據字典中SYS_FIELDS表的信息。

INNODB_SYS_INDEXES表提供查詢有關InnoDB索引的元數據信息，等同于InnoDB數據字典內部SYS_INDEXES表中的信息。

INNODB_SYS_TABLES表提供查詢有關InnoDB表的元數據信息，等同于InnoDB數據字典中SYS_TABLES表的信息。

mysql系統庫

權限系統表

user：包含用戶賬戶、全局權限和其他非權限列表（安全配置字段和資源控制字段）。
db：數據庫級別的權限表。該表中記錄的權限信息代表用戶是否可以使用這些權限來訪問被授予訪問的數據庫下的所有對象（表或存儲程序）。
tables_priv：表級別的權限表。
columns_priv：字段級別的權限表。
procs_priv：存儲過程和函數權限表。
proxies_priv：代理用戶權限表。

統計信息表

持久化統計功能是通過將內存中的統計數據存儲到磁盤中，使其在數據庫重啟時可以快速重新讀入這些統計信息而不用重新執行統計，從而使得查詢優化器可以利用這些持久化的統計信息準確地選擇執行計劃（如果沒有這些持久化的統計信息，那么數據庫重啟之后內存中的統計信息將會丟失，下一次訪問到某庫某表時，需要重新計算統計信息，并且重新計算可能會因為估算值的差異導致查詢計劃發生變更，從而導致查詢性能發生變化）。

-- 當innodb_stats_persistent = ON時全局的開啟統計信息的持久化功能，默認是開啟的
show variables like 'innodb_stats_persistent';

innodb_table_stats 提供查詢與表數據相關的統計信息。
innodb_index_stats 提供查詢與索引相關的統計信息。

日志記錄表

MySQL的日志系統包含：普通查詢日志、慢查詢日志、錯誤日志（記錄服務器啟動時、運行中、停止時的錯誤信息）、二進制日志（記錄服務器運行過程中數據變更的邏輯日志）、中繼日志（記錄從庫I/O線程從主庫獲取的主庫數據變更日志）、DDL日志（記錄DDL語句執行時的元數據變更信息。在MySQL 5.7中只支持寫入文件中，在MySQL 8.0中支持寫入innodb_ddl_log表中。在MySQL5.7中，只有普通查詢日志、慢查詢日志支持寫入表中（也支持寫入文件中）,可以通過log_output=TABLE設置保存到mysql.general_log表和mysql.slow_log表中，其他日志類型在MySQL 5.7中只支持寫入文件中。

general_log

提供查詢普通SQL語句的執行記錄信息，用于查看客戶端到底在服務器上執行了什么SQL語句。

set global log_output='TABLE'; -- 'TABLE,FILE'表示同時輸出到表和文件
set global general_log=on;
show variables like 'general_log';

slow_log

slow_log表提供查詢執行時間超過long_query_time設置值的SQL語句、未使用索引的語句（需要開啟參數log_queries_not_using_indexes=ON）或者管理語句（需要開啟參數log_slow_admin_statements=ON）。

set global log_queries_not_using_indexes=on;
set global log_slow_admin_statements=on;
show variables like 'log_queries_not_using_indexes';
show variables like 'log_slow_admin_statements';-- 打開慢查詢日志
set GLOBAL slow_query_log=1;-- show VARIABLES like '%long_query_time%';  默認十秒

InnoDB的統計信息

分類

永久性的統計數據，這種統計數據存儲在磁盤上，也就是服務器重啟之后這些統計數據還在。

非永久性的統計數據，這種統計數據存儲在內存中，當服務器關閉時這些這些統計數據就都被清除掉了，等到服務器重啟之后，在某些適當的場景下才會重新收集這些統計數據。

MySQL給我們提供了系統變量innodb_stats_persistent來控制到底采用哪種方式去存儲統計數據。在MySQL 5.6.6之前，innodb_stats_persistent的值默認是OFF，也就是說InnoDB的統計數據默認是存儲到內存的，之后的版本中innodb_stats_persistent的值默認是ON，也就是統計數據默認被存儲到磁盤中。

InnoDB默認是以表為單位來收集和存儲統計數據的，我們也可以單獨為某個表設置是否自動重新計算統計數的屬性，設置方式就是在創建或修改表的時候通過指定STATS_AUTO_RECALC屬性來指明該表的統計數據存儲方式：

CREATE TABLE 表名 (...)

Engine=InnoDB, STATS_AUTO_RECALC = (1|0);

ALTER TABLE 表名

Engine=InnoDB, STATS_AUTO_RECALC = (1|0);

當STATS_AUTO_RECALC=1時，表明我們想讓該表自動重新計算統計數據，當STATS_AUTO_RECALC=0時，表明不想讓該表自動重新計算統計數據。如果我們在創建表時未指定STATS_AUTO_RECALC屬性，那默認采用系統變量innodb_stats_auto_recalc的值作為該屬性的值。