mysql版本基于5.6.43
information_schema
在MySQL中，把 information_schema 看作是一個(gè)數(shù)據(jù)庫(kù)，確切說是信息數(shù)據(jù)庫(kù)。其中保存著關(guān)于MySQL服務(wù)器所維護(hù)的所有其他數(shù)據(jù)庫(kù)的信息。如數(shù)據(jù)庫(kù)名，數(shù)據(jù)庫(kù)的表，表欄的數(shù)據(jù)類型與訪問權(quán) 限等。在INFORMATION_SCHEMA中，有數(shù)個(gè)只讀表。它們實(shí)際上是視圖，而不是基本表，因此，你將無法看到與之相關(guān)的任何文件。
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| INNODB_LOCKS |
| INNODB_TRX |
| INNODB_SYS_DATAFILES |
| INNODB_LOCK_WAITS |
....
| INNODB_SYS_FOREIGN_COLS |
| INNODB_SYS_TABLES |
| INNODB_BUFFER_POOL_STATS |
| INNODB_FT_CONFIG |
+---------------------------------------+
59 rows in set (0.00 sec)
這里我們重點(diǎn)關(guān)注INNODB_TRX, INNODB_LOCKS, 以及INNODB_LOCK_WAITS三張表，表如其名，這三張表記錄了正在運(yùn)行的事務(wù)（包括事務(wù)占用or釋放鎖的信息）狀態(tài)信息
mysql> desc INNODB_TRX;
+----------------------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id | varchar(18) | NO | | | |
| trx_state | varchar(13) | NO | | | |
| trx_started | datetime | NO | | 0000-00-00 00:00:00 | |
| trx_requested_lock_id | varchar(81) | YES | | NULL | |
| trx_wait_started | datetime | YES | | NULL | |
| trx_weight | bigint(21) unsigned | NO | | 0 | |
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |
| trx_query | varchar(1024) | YES | | NULL | |
| trx_operation_state | varchar(64) | YES | | NULL | |
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |
| trx_tables_locked | bigint(21) unsigned | NO | | 0 | |
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |
| trx_rows_locked | bigint(21) unsigned | NO | | 0 | |
| trx_rows_modified | bigint(21) unsigned | NO | | 0 | |
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |
| trx_isolation_level | varchar(16) | NO | | | |
| trx_unique_checks | int(1) | NO | | 0 | |
| trx_foreign_key_checks | int(1) | NO | | 0 | |
| trx_last_foreign_key_error | varchar(256) | YES | | NULL | |
| trx_adaptive_hash_latched | int(1) | NO | | 0 | |
| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |
| trx_is_read_only | int(1) | NO | | 0 | |
| trx_autocommit_non_locking | int(1) | NO | | 0 | |
+----------------------------+---------------------+------+-----+---------------------+-------+
mysql> desc INNODB_LOCKS;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id | varchar(81) | NO | | | |
| lock_trx_id | varchar(18) | NO | | | |
| lock_mode | varchar(32) | NO | | | |
| lock_type | varchar(32) | NO | | | |
| lock_table | varchar(1024) | NO | | | |
| lock_index | varchar(1024) | YES | | NULL | |
| lock_space | bigint(21) unsigned | YES | | NULL | |
| lock_page | bigint(21) unsigned | YES | | NULL | |
| lock_rec | bigint(21) unsigned | YES | | NULL | |
| lock_data | varchar(8192) | YES | | NULL | |
+-------------+---------------------+------+-----+---------+-------+
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| requesting_trx_id | varchar(18) | NO | | | |
| requested_lock_id | varchar(81) | NO | | | |
| blocking_trx_id | varchar(18) | NO | | | |
| blocking_lock_id | varchar(81) | NO | | | |
+-------------------+-------------+------+-----+---------+-------+
performance_schema
該數(shù)據(jù)庫(kù)主要關(guān)注數(shù)據(jù)庫(kù)運(yùn)行過程中的性能相關(guān)的數(shù)據(jù)，與information_schema不同，information_schema主要關(guān)注server運(yùn)行過程中的元數(shù)據(jù)信息
performance_schema通過監(jiān)視server的事件來實(shí)現(xiàn)監(jiān)視server內(nèi)部運(yùn)行情況， “事件”就是server內(nèi)部活動(dòng)中所做的任何事情以及對(duì)應(yīng)的時(shí)間消耗，利用這些信息來判斷server中的相關(guān)資源消耗在了哪里？一般來說，事件可以是函數(shù)調(diào)用、操作系統(tǒng)的等待、SQL語句執(zhí)行的階段（如sql語句執(zhí)行過程中的parsing 或 sorting階段）或者整個(gè)SQL語句與SQL語句集合。事件的采集可以方便的提供server中的相關(guān)存儲(chǔ)引擎對(duì)磁盤文件、表I/O、表鎖等資源的同步調(diào)用信息。
收集的事件數(shù)據(jù)存儲(chǔ)在performance_schema數(shù)據(jù)庫(kù)的表中。這些表可以使用SELECT語句查詢，也可以使用SQL語句更新performance_schema數(shù)據(jù)庫(kù)中的表記錄（如動(dòng)態(tài)修改performance_schema的setup_*開頭的幾個(gè)配置表，但要注意：配置表的更改會(huì)立即生效，這會(huì)影響數(shù)據(jù)收集）
+----------------------------------------------------+
| Tables_in_performance_schema |
+----------------------------------------------------+
| accounts |
| cond_instances |
| events_stages_current |
| events_stages_history |
| events_stages_history_long |
| events_stages_summary_by_account_by_event_name |
| events_stages_summary_by_host_by_event_name |
| events_stages_summary_by_thread_by_event_name |
| events_stages_summary_by_user_by_event_name |
| events_stages_summary_global_by_event_name |
| events_statements_current |
| events_statements_history |
| events_statements_history_long |
| events_statements_summary_by_account_by_event_name |
| events_statements_summary_by_digest |
| events_statements_summary_by_host_by_event_name |
| events_statements_summary_by_thread_by_event_name |
| events_statements_summary_by_user_by_event_name |
| events_statements_summary_global_by_event_name |
...
| users |
+----------------------------------------------------
語句事件記錄表，這些表記錄了語句事件信息，當(dāng)前語句事件表events_statements_current、歷史語句事件表events_statements_history和長(zhǎng)語句歷史事件表events_statements_history_long、以及聚合后的摘要表summary，其中，summary表還可以根據(jù)帳號(hào)(account)，主機(jī)(host)，程序(program)，線程(thread)，用戶(user)和全局(global)再進(jìn)行細(xì)分)
