mysql的常用系統表和視圖有哪些

這期內容當中小編將會(huì )給大家帶來(lái)有關(guān)的常用系統表和視圖有哪些，文章內容豐富且以專(zhuān)業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

一.information_schema下常用表

1.SCHEMATA

實(shí)例下所有數據庫的信息

*************************** 1. row ***************************
              CATALOG_NAME: def
               SCHEMA_NAME: information_schema
DEFAULT_CHARACTER_SET_NAME: utf8
    DEFAULT_COLLATION_NAME: utf8_general_ci
                  SQL_PATH: NULL

2.TABLES

提供了表和視圖的詳細信息

*************************** 298. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: wwj2
     TABLE_NAME: t1
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 3
 AVG_ROW_LENGTH: 5461
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 4
    CREATE_TIME: 2018-04-17 10:44:04
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_unicode_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:

3.COLUMNS

詳細的表列信息

*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: wwj
              TABLE_NAME: t3
             COLUMN_NAME: idt3
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 10
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: int(11)
              COLUMN_KEY: PRI
                   EXTRA:
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:
   GENERATION_EXPRESSION:

4.STATISTICS

表的索引信息

*************************** 1. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: wwj
   TABLE_NAME: t3
   NON_UNIQUE: 0
 INDEX_SCHEMA: wwj
   INDEX_NAME: PRIMARY
 SEQ_IN_INDEX: 1
  COLUMN_NAME: idt3
    COLLATION: A
  CARDINALITY: 8
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE:
   INDEX_TYPE: BTREE
      COMMENT:
INDEX_COMMENT:
*************************** 2. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: wwj
   TABLE_NAME: t3
   NON_UNIQUE: 1
 INDEX_SCHEMA: wwj
   INDEX_NAME: idx-1
 SEQ_IN_INDEX: 1
  COLUMN_NAME: idt4
    COLLATION: A
  CARDINALITY: 8
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE:
   INDEX_TYPE: BTREE
      COMMENT:
INDEX_COMMENT:

5.USER_PRIVILEGES

用戶(hù)權限信息

+-----------------------------+---------------+-------------------------+--------------+
| GRANTEE                     | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
+-----------------------------+---------------+-------------------------+--------------+
| 'root'@'localhost'          | def           | SELECT                  | YES          |
| 'root'@'localhost'          | def           | INSERT                  | YES          |
| 'root'@'localhost'          | def           | UPDATE                  | YES          |
| 'root'@'localhost'          | def           | DELETE                  | YES          |
| 'root'@'localhost'          | def           | CREATE                  | YES          |
| 'root'@'localhost'          | def           | DROP                    | YES          |
| 'root'@'localhost'          | def           | RELOAD                  | YES          |
| 'root'@'localhost'          | def           | SHUTDOWN                | YES          |
| 'root'@'localhost'          | def           | PROCESS                 | YES          |
| 'root'@'localhost'          | def           | FILE                    | YES          |
| 'root'@'localhost'          | def           | REFERENCES              | YES          |
| 'root'@'localhost'          | def           | INDEX                   | YES          |
| 'root'@'localhost'          | def           | ALTER                   | YES          |
| 'root'@'localhost'          | def           | SHOW DATABASES          | YES          |
| 'root'@'localhost'          | def           | SUPER                   | YES          |
| 'root'@'localhost'          | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'localhost'          | def           | LOCK TABLES             | YES          |
| 'root'@'localhost'          | def           | EXECUTE                 | YES          |
| 'root'@'localhost'          | def           | REPLICATION SLAVE       | YES          |
| 'root'@'localhost'          | def           | REPLICATION CLIENT      | YES          |
| 'root'@'localhost'          | def           | CREATE VIEW             | YES          |
| 'root'@'localhost'          | def           | SHOW VIEW               | YES          |
| 'root'@'localhost'          | def           | CREATE ROUTINE          | YES          |
| 'root'@'localhost'          | def           | ALTER ROUTINE           | YES          |
| 'root'@'localhost'          | def           | CREATE USER             | YES          |
| 'root'@'localhost'          | def           | EVENT                   | YES          |
| 'root'@'localhost'          | def           | TRIGGER                 | YES          |
| 'root'@'localhost'          | def           | CREATE TABLESPACE       | YES          |
| 'mysql.session'@'localhost' | def           | SUPER                   | NO           |
| 'mysql.sys'@'localhost'     | def           | USAGE                   | NO           |
| 'REPL_USER'@'109.115.12.41' | def           | REPLICATION SLAVE       | NO           |
| 'REPL_USER'@'109.115.12.42' | def           | REPLICATION SLAVE       | NO           |
| 'REPL_USER'@'109.115.12.%'  | def           | REPLICATION SLAVE       | NO           |
| 'REPL_USER'@'109.115.12.%'  | def           | REPLICATION CLIENT      | NO           |
| 'root'@'%'                  | def           | SELECT                  | NO           |
| 'root'@'%'                  | def           | INSERT                  | NO           |
| 'root'@'%'                  | def           | UPDATE                  | NO           |
| 'root'@'%'                  | def           | DELETE                  | NO           |
| 'root'@'%'                  | def           | CREATE                  | NO           |
| 'root'@'%'                  | def           | DROP                    | NO           |
| 'root'@'%'                  | def           | RELOAD                  | NO           |
| 'root'@'%'                  | def           | SHUTDOWN                | NO           |
| 'root'@'%'                  | def           | PROCESS                 | NO           |
| 'root'@'%'                  | def           | FILE                    | NO           |
| 'root'@'%'                  | def           | REFERENCES              | NO           |
| 'root'@'%'                  | def           | INDEX                   | NO           |
| 'root'@'%'                  | def           | ALTER                   | NO           |
| 'root'@'%'                  | def           | SHOW DATABASES          | NO           |
| 'root'@'%'                  | def           | SUPER                   | NO           |
| 'root'@'%'                  | def           | CREATE TEMPORARY TABLES | NO           |
| 'root'@'%'                  | def           | LOCK TABLES             | NO           |
| 'root'@'%'                  | def           | EXECUTE                 | NO           |
| 'root'@'%'                  | def           | REPLICATION SLAVE       | NO           |
| 'root'@'%'                  | def           | REPLICATION CLIENT      | NO           |
| 'root'@'%'                  | def           | CREATE VIEW             | NO           |
| 'root'@'%'                  | def           | SHOW VIEW               | NO           |
| 'root'@'%'                  | def           | CREATE ROUTINE          | NO           |
| 'root'@'%'                  | def           | ALTER ROUTINE           | NO           |
| 'root'@'%'                  | def           | CREATE USER             | NO           |
| 'root'@'%'                  | def           | EVENT                   | NO           |
| 'root'@'%'                  | def           | TRIGGER                 | NO           |
| 'root'@'%'                  | def           | CREATE TABLESPACE       | NO           |
+-----------------------------+---------------+-------------------------+--------------+

6.SCHEMA_PRIVILEGES

數據庫權限信息

+-----------------------------+---------------+--------------------+----------------+--------------+
| GRANTEE                     | TABLE_CATALOG | TABLE_SCHEMA       | PRIVILEGE_TYPE | IS_GRANTABLE |
+-----------------------------+---------------+--------------------+----------------+--------------+
| 'mysql.sys'@'localhost'     | def           | sys                | TRIGGER        | NO           |
| 'mysql.session'@'localhost' | def           | performance_schema | SELECT         | NO           |
+-----------------------------+---------------+--------------------+----------------+--------------+

7.TABLE_PRIVILEGES

表權限信息

*************************** 3. row ***************************
       GRANTEE: 'wwj'@'%'
 TABLE_CATALOG: def
  TABLE_SCHEMA: wwj
    TABLE_NAME: t3
PRIVILEGE_TYPE: SELECT
  IS_GRANTABLE: NO

8.COLUMN_PRIVILEGES

關(guān)于列權限信息

9.CHARACTER_SETS

mysql可用字符集

10.TABLE_CONSTRAINTS

表的約束信息

11.KEY_COLUMN_USAGE

描述了具有約束的列的信息

*************************** 79. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: wwj2
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: wwj2
                   TABLE_NAME: t1
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL

12.ROUTINES

記錄了存儲過(guò)程和函數的信息

13.VIEWS

記錄了數據庫中的視圖信息，需要有show views權限

*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: sys
          TABLE_NAME: host_summary
     VIEW_DEFINITION: select if(isnull(`performance_schema`.`accounts`.`HOST`),'background',`performance_schema`.`accounts`.`HOST`) AS `host`,sum(`stmt`.`total`) AS `statements`,`sys`.`format_time`(sum(`stmt`.`total_latency`)) AS `statement_latency`,`sys`.`format_time`(ifnull((sum(`stmt`.`total_latency`) / nullif(sum(`stmt`.`total`),0)),0)) AS `statement_avg_latency`,sum(`stmt`.`full_scans`) AS `table_scans`,sum(`io`.`ios`) AS `file_ios`,`sys`.`format_time`(sum(`io`.`io_latency`)) AS `file_io_latency`,sum(`performance_schema`.`accounts`.`CURRENT_CONNECTIONS`) AS `current_connections`,sum(`performance_schema`.`accounts`.`TOTAL_CONNECTIONS`) AS `total_connections`,count(distinct `performance_schema`.`accounts`.`USER`) AS `unique_users`,`sys`.`format_bytes`(sum(`mem`.`current_allocated`)) AS `current_memory`,`sys`.`format_bytes`(sum(`mem`.`total_allocated`)) AS `total_memory_allocated` from (((`performance_schema`.`accounts` join `sys`.`x$host_summary_by_statement_latency` `stmt` on((`performance_schema`.`accounts`.`HOST` = `stmt`.`host`))) join `sys`.`x$host_summary_by_file_io` `io` on((`performance_schema`.`accounts`.`HOST` = `io`.`host`))) join `sys`.`x$memory_by_host_by_current_bytes` `mem` on((`performance_schema`.`accounts`.`HOST` = `mem`.`host`))) group by if(isnull(`performance_schema`.`accounts`.`HOST`),'background',`performance_schema`.`accounts`.`HOST`)
        CHECK_OPTION: NONE
        IS_UPDATABLE:
             DEFINER: mysql.sys@localhost
       SECURITY_TYPE: INVOKER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci

14.TRIGGERS

提供了數據庫中觸發(fā)器的信息

*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: sys
              TRIGGER_NAME: sys_config_insert_set_user
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: sys
        EVENT_OBJECT_TABLE: sys_config
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER(); END IF; END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: 2018-04-06 10:15:16.30
                  SQL_MODE:
                   DEFINER: mysql.sys@localhost
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: utf8_general_ci

二.performance_schema下常用表

參考:http://www.cnblogs.com/cchust/p/5022148.html

PERFORMANCE_SCHEMA這個(gè)功能默認是關(guān)閉的。需要設置參數： performance_schema 才可以啟動(dòng)該功能，這個(gè)參數是靜態(tài)參數，只能寫(xiě)在my.cnf 中 不能動(dòng)態(tài)修改。

1.配置表

(1).set_actors

用于配置user維度的監控，默認情況下監控所有用戶(hù)線(xiàn)程

+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | YES     | YES     |
+------+------+------+---------+---------+

(2).setup_consumers

表用于配置事件的消費者類(lèi)型，即收集的事件最終會(huì )寫(xiě)入到哪些統計表中。

+----------------------------------+---------+
| NAME                             | ENABLED |
+----------------------------------+---------+
| events_stages_current            | NO      |
| events_stages_history            | NO      |
| events_stages_history_long       | NO      |
| events_statements_current        | YES     |
| events_statements_history        | YES     |
| events_statements_history_long   | NO      |
| events_transactions_current      | NO      |
| events_transactions_history      | NO      |
| events_transactions_history_long | NO      |
| events_waits_current             | NO      |
| events_waits_history             | NO      |
| events_waits_history_long        | NO      |
| global_instrumentation           | YES     |
| thread_instrumentation           | YES     |
| statements_digest                | YES     |
+----------------------------------+---------+

(3).setup_instruments
表用于配置一條條具體的instrument，主要包含4大類(lèi)：idle,stage/xxx,statement/xxx,wait/xxx.

-idle:表示socket空閑的時(shí)間

-stage:表示每個(gè)語(yǔ)句每個(gè)執行階段的統計

-statement:統計語(yǔ)句維度的信息

-wait:統計各種等待事件

(4).setup_objects

用于配置監控對象，默認情況下所有，mysql,information_schema,performance_schema下的對象不監控，其他DB的對象全部監控

+-------------+--------------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME | ENABLED | TIMED |
+-------------+--------------------+-------------+---------+-------+
| EVENT       | mysql              | %           | NO      | NO    |
| EVENT       | performance_schema | %           | NO      | NO    |
| EVENT       | information_schema | %           | NO      | NO    |
| EVENT       | %                  | %           | YES     | YES   |
| FUNCTION    | mysql              | %           | NO      | NO    |
| FUNCTION    | performance_schema | %           | NO      | NO    |
| FUNCTION    | information_schema | %           | NO      | NO    |
| FUNCTION    | %                  | %           | YES     | YES   |
| PROCEDURE   | mysql              | %           | NO      | NO    |
| PROCEDURE   | performance_schema | %           | NO      | NO    |
| PROCEDURE   | information_schema | %           | NO      | NO    |
| PROCEDURE   | %                  | %           | YES     | YES   |
| TABLE       | mysql              | %           | NO      | NO    |
| TABLE       | performance_schema | %           | NO      | NO    |
| TABLE       | information_schema | %           | NO      | NO    |
| TABLE       | %                  | %           | YES     | YES   |
| TRIGGER     | mysql              | %           | NO      | NO    |
| TRIGGER     | performance_schema | %           | NO      | NO    |
| TRIGGER     | information_schema | %           | NO      | NO    |
| TRIGGER     | %                  | %           | YES     | YES   |
+-------------+--------------------+-------------+---------+-------+

(5).setup_timers

配置每種類(lèi)型統計的時(shí)間單位

+-------------+-------------+
| NAME        | TIMER_NAME  |
+-------------+-------------+
| idle        | MICROSECOND |
| wait        | CYCLE       |
| stage       | NANOSECOND  |
| statement   | NANOSECOND  |
| transaction | NANOSECOND  |
+-------------+-------------+

2.instance表
(1).file_instances
表中記錄了系統中打開(kāi)了文件的對象,open_count顯示當前文件打開(kāi)的數目
+-------------------------------------------+--------------------------------------+------------+
| FILE_NAME                                 | EVENT_NAME                           | OPEN_COUNT |
+-------------------------------------------+--------------------------------------+------------+
| /usr/local/mysql/share/english/errmsg.sys | wait/io/file/sql/ERRMSG              |          0 |
| /usr/local/mysql/share/charsets/Index.xml | wait/io/file/mysys/charset           |          0 |
| /mysql/mysql3306/mysql3306/ibdata1        | wait/io/file/innodb/innodb_data_file |          3 |
+-------------------------------------------+--------------------------------------+------------+

(2).mutex_instances
表中記錄了系統中使用互斥量對象的所有記錄,LOCKED_BY_THREAD_ID顯示哪個(gè)線(xiàn)程正持有mutex，若沒(méi)有線(xiàn)程持有，則為NULL。
+----------------------------------------+-----------------------+---------------------+
| NAME                                   | OBJECT_INSTANCE_BEGIN | LOCKED_BY_THREAD_ID |
+----------------------------------------+-----------------------+---------------------+
| wait/synch/mutex/mysys/THR_LOCK_heap   |              32492032 |                NULL |
| wait/synch/mutex/mysys/THR_LOCK_net    |              32491776 |                NULL |
| wait/synch/mutex/mysys/THR_LOCK_myisam |              32491712 |                NULL |
+----------------------------------------+-----------------------+---------------------+

(3).rwlock_instances
記錄了系統中使用讀寫(xiě)鎖對象的所有記錄
WRITE_LOCKED_BY_THREAD_ID為正在持有該對象的thread_id，若沒(méi)有線(xiàn)程持有，則為NULL
READ_LOCKED_BY_COUNT為記錄了同時(shí)有多少個(gè)讀者持有讀鎖
+-------------------------------------------------------+-----------------------+---------------------------+----------------------+
| NAME                                                  | OBJECT_INSTANCE_BEGIN | WRITE_LOCKED_BY_THREAD_ID | READ_LOCKED_BY_COUNT |
+-------------------------------------------------------+-----------------------+---------------------------+----------------------+
| wait/synch/rwlock/session/LOCK_srv_session_collection |              31736184 |                      NULL |                    0 |
+-------------------------------------------------------+-----------------------+---------------------------+----------------------+

(4).socket_instances
表中記錄了thread_id,socket_id,ip和port，其它表可以通過(guò)thread_id與socket_instance進(jìn)行關(guān)聯(lián)，獲取IP-PORT信息，能夠與應用對接起來(lái)。
event_name主要包含3類(lèi)：
wait/io/socket/sql/server_unix_socket，服務(wù)端unix監聽(tīng)socket
wait/io/socket/sql/server_tcpip_socket，服務(wù)端tcp監聽(tīng)socket
wait/io/socket/sql/client_connection，客戶(hù)端socket
+----------------------------------------+-----------------------+-----------+-----------+----+------+--------+
| EVENT_NAME                             | OBJECT_INSTANCE_BEGIN | THREAD_ID | SOCKET_ID | IP | PORT | STATE  |
+----------------------------------------+-----------------------+-----------+-----------+----+------+--------+
| wait/io/socket/sql/server_tcpip_socket |              83218752 |         1 |        22 | :: | 3306 | ACTIVE |
| wait/io/socket/sql/server_unix_socket  |              83219072 |         1 |        24 |    |    0 | ACTIVE |
| wait/io/socket/sql/client_connection   |              83221952 |        63 |        27 |    |    0 | IDLE   |
| wait/io/socket/sql/client_connection   |              83222272 |        64 |        29 |    |    0 | IDLE   |
| wait/io/socket/sql/client_connection   |              83222592 |        65 |        30 |    |    0 | ACTIVE |
+----------------------------------------+-----------------------+-----------+-----------+----+------+--------+

3.Wait Event表
events_waits_current         #記錄了當前線(xiàn)程等待的事件
events_waits_history         #記錄了每個(gè)線(xiàn)程最近等待的10個(gè)事件
events_waits_history_long    #記錄了最近所有線(xiàn)程產(chǎn)生的10000個(gè)事件
history表中的事件都是完成了的，沒(méi)有結束的事件不會(huì )加入到history表中.
THREAD_ID:線(xiàn)程ID
EVENT_ID：當前線(xiàn)程的事件ID，和THREAD_ID組成一個(gè)Primary Key。
END_EVENT_ID:當事件開(kāi)始時(shí)，這一列被設置為NULL。當事件結束時(shí)，再更新為當前的事件ID。
SOURCE：該事件產(chǎn)生時(shí)的源碼文件
TIMER_START, TIMER_END, TIMER_WAIT：事件開(kāi)始/結束和等待的時(shí)間，單位為皮秒（picoseconds）
OBJECT_SCHEMA, OBJECT_NAME, OBJECT_TYPE視情況而定
對于同步對象(cond, mutex, rwlock),這個(gè)3個(gè)值均為NULL
對于文件IO對象，OBJECT_SCHEMA為NULL，OBJECT_NAME為文件名，OBJECT_TYPE為FILE
對于SOCKET對象，OBJECT_NAME為該socket的IP:SOCK值
對于表I/O對象，OBJECT_SCHEMA是表的SCHEMA名，OBJECT_NAME是表名，OBJECT_TYPE為T(mén)ABLE或者TEMPORARY TABLE
NESTING_EVENT_ID：該事件對應的父事件ID
NESTING_EVENT_TYPE：父事件類(lèi)型(STATEMENT, STAGE, WAIT)
OPERATION:操作類(lèi)型（lock, read, write）

4.stage event表
events_waits_current      
events_stages_history
events_stages_history_long
表中記錄了當前線(xiàn)程所處的執行階段，由于可以知道每個(gè)階段的執行時(shí)間，因此通過(guò)stage表可以得到SQL在每個(gè)階段消耗的時(shí)間。
THREAD_ID：線(xiàn)程ID
EVENT_ID：事件ID
END_EVENT_ID：剛結束的事件ID
SOURCE：源碼位置
TIMER_START, TIMER_END, TIMER_WAIT：事件開(kāi)始/結束和等待的時(shí)間，單位為皮秒（picoseconds）
NESTING_EVENT_ID：該事件對應的父事件ID
NESTING_EVENT_TYPE：父事件類(lèi)型(STATEMENT, STAGE, WAIT)

5.Statement Event
events_statements_current
events_statements_history
events_statements_history_long
Statments表只記錄最頂層的請求，SQL語(yǔ)句或是COMMAND，每條語(yǔ)句一行，對于嵌套的子查詢(xún)或者存儲過(guò)程不會(huì )單獨列出。event_name形式為statement/sql/*，或statement/com/*
*************************** 8. row ***************************
              THREAD_ID: 67
               EVENT_ID: 96
           END_EVENT_ID: 96
             EVENT_NAME: statement/sql/select
                 SOURCE: socket_connection.cc:101
            TIMER_START: 365640494222296000
              TIMER_END: 365640496863080000
             TIMER_WAIT: 2640784000
              LOCK_TIME: 195000000
               SQL_TEXT: select * from events_statements_history
                 DIGEST: 12bf4ba549a7a86ad0d382bf2cd93f91
            DIGEST_TEXT: SELECT * FROM `events_statements_history`
         CURRENT_SCHEMA: performance_schema
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 12
          ROWS_EXAMINED: 12
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 1
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 1
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
    NESTING_EVENT_LEVEL: 0

SQL_TEXT：記錄SQL語(yǔ)句
DIGEST:對SQL_TEXT做MD5產(chǎn)生的32位字符串。如果為consumer表中沒(méi)有打開(kāi)statement_digest選項，則為NULL。
DIGEST_TEXT：將語(yǔ)句中值部分用問(wèn)號代替，用于SQL語(yǔ)句歸類(lèi)。如果為consumer表中沒(méi)有打開(kāi)statement_digest選項，則為NULL。
CURRENT_SCHEMA：默認的數據庫名
OBJECT_SCHEMA,OBJECT_NAME,OBJECT_TYPE：保留字段，全部為NULL
ROWS_AFFECTED：影響的數目
ROWS_SENT:返回的記錄數
ROWS_EXAMINED:讀取的記錄數目
CREATED_TMP_DISK_TABLES:創(chuàng )建物理臨時(shí)表數目
CREATED_TMP_TABLES:創(chuàng )建臨時(shí)表數目
SELECT_FULL_JOIN:join時(shí)，第一個(gè)表為全表掃描的數目
SELECT_FULL_RANGE_JOIN:join時(shí)，引用表采用range方式掃描的數目
SELECT_RANGE:join時(shí)，第一個(gè)表采用range方式掃描的數目
SELECT_SCAN:join時(shí)，第一個(gè)表位全表掃描的數目
SORT_ROWS:排序的記錄數目
NESTING_EVENT_ID,NESTING_EVENT_TYPE，保留字段，為NULL。

6.connection表
users，hosts和account表
accounts包含hosts和users的信息。
USER:用戶(hù)名
HOST:用戶(hù)的IP

7.Summary
Summary表聚集了各個(gè)維度的統計信息包括表維度，索引維度，會(huì )話(huà)維度，語(yǔ)句維度和鎖維度的統計信息。
(1).wait-summary表
events_waits_summary_global_by_event_name
場(chǎng)景：按等待事件類(lèi)型聚合，每個(gè)事件一條記錄。
events_waits_summary_by_instance
場(chǎng)景：按等待事件對象聚合，同一種等待事件，可能有多個(gè)實(shí)例，每個(gè)實(shí)例有不同的內存地址，因此
event_name+object_instance_begin唯一確定一條記錄。
events_waits_summary_by_thread_by_event_name
場(chǎng)景：按每個(gè)線(xiàn)程和事件來(lái)統計,thread_id+event_name唯一確定一條記錄。
COUNT_STAR：事件計數
SUM_TIMER_WAIT：總的等待時(shí)間
MIN_TIMER_WAIT：最小等待時(shí)間
MAX_TIMER_WAIT：最大等待時(shí)間
AVG_TIMER_WAIT：平均等待時(shí)間

(2).stage-summary表
events_stages_summary_by_thread_by_event_name
events_stages_summary_global_by_event_name
與前面類(lèi)似

(3).statements-summary表
events_statements_summary_by_thread_by_event_name表和events_statements_summary_global_by_event_name表與前面類(lèi)似。對于events_statements_summary_by_digest表，
FIRST_SEEN_TIMESTAMP：第一個(gè)語(yǔ)句執行的時(shí)間
LAST_SEEN_TIMESTAMP：最后一個(gè)語(yǔ)句執行的時(shí)間
場(chǎng)景：用于統計某一段時(shí)間內top SQL

(4).file I/O summary表
file_summary_by_event_name [按事件類(lèi)型統計]
file_summary_by_instance [按具體文件統計]
場(chǎng)景：物理IO維度
FILE_NAME:具體文件名,比如：/u01/my3306/data/tcbuyer_0168/tc_biz_order_2695.ibd
EVENT_NAME:事件名，比如：wait/io/file/innodb/innodb_data_file
COUNT_STAR，SUM_TIMER_WAIT,MIN_TIMER_WAIT,AVG_TIMER_WAIT,MAX_TIMER_WAIT
統計IO操作
COUNT_READ,SUM_TIMER_READ,MIN_TIMER_READ,AVG_TIMER_READ,MAX_TIMER_READ, SUM_NUMBER_OF_BYTES_READ
統計讀
COUNT_WRITE,SUM_TIMER_WRITE,MIN_TIMER_WRITE,AVG_TIMER_WRITE,MAX_TIMER_WRITE, SUM_NUMBER_OF_BYTES_WRITE
統計寫(xiě)
COUNT_MISC,SUM_TIMER_MISC,MIN_TIMER_MISC,AVG_TIMER_MISC,MAX_TIMER_MISC
統計其他IO事件，比如create，delete，open，close等

(5).Table I/O and Lock Wait Summaries-表
table_io_waits_summary_by_table
根據wait/io/table/sql/handler，聚合每個(gè)表的I/O操作，[邏輯IO]
COUNT_STAR,SUM_TIMER_WAIT,MIN_TIMER_WAIT,AVG_TIMER_WAIT,MAX_TIMER_WAIT
統計IO操作
COUNT_READ,SUM_TIMER_WAIT,MIN_TIMER_WAIT,AVG_TIMER_WAIT,MAX_TIMER_WAIT
統計讀
COUNT_WRITE,SUM_TIMER_WRITE,MIN_TIMER_WRITE,AVG_TIMER_WRITE, MAX_TIMER_WRITE
統計寫(xiě)
COUNT_FETCH,SUM_TIMER_FETCH,MIN_TIMER_FETCH,AVG_TIMER_FETCH, MAX_TIMER_FETCH
與讀相同
COUNT_INSERT,SUM_TIMER_INSERT,MIN_TIMER_INSERT,AVG_TIMER_INSERT,MAX_TIMER_INSERT
INSERT統計，相應的還有DELETE和UPDATE統計。

(6).table_io_waits_summary_by_index_usage
與table_io_waits_summary_by_table類(lèi)似，按索引維度統計

(7).table_lock_waits_summary_by_table
聚合了表鎖等待事件，包括internal lock 和 external lock。
internal lock通過(guò)SQL層函數thr_lock調用，OPERATION值為：
read normal
read with shared locks
read high priority
read no insert
write allow write
write concurrent insert
write delayed
write low priority
write normal

external lock則通過(guò)接口函數handler::external_lock調用存儲引擎層，
OPERATION列的值為：
read external
write external

(8).Connection Summaries表
events_waits_summary_by_account_by_event_name
events_waits_summary_by_user_by_event_name
events_waits_summary_by_host_by_event_name
events_stages_summary_by_account_by_event_name
events_stages_summary_by_user_by_event_name
events_stages_summary_by_host_by_event_name
events_statements_summary_by_account_by_event_name
events_statements_summary_by_user_by_event_name
events_statements_summary_by_host_by_event_name

(9).socket-summaries表
socket_summary_by_instance
socket_summary_by_event_name

其它表
performance_timers: 系統支持的統計時(shí)間單位
threads: 監視服務(wù)端的當前運行的線(xiàn)程

http://www.cnblogs.com/cchust/p/5061131.html

三.mysql數據庫常用表
1、user表: 用戶(hù)列、權限列、安全列、資源控制列
2、db表 : 用戶(hù)列、權限列
3、table_priv表
4、columns_priv表
5、proc_priv表

四.sys數據庫常用表
1.host視圖
(1).host_summary;
統計了host的SQL執行信息，文件io信息和連接信息
? host : 監聽(tīng)連接過(guò)的主機
? statements : 當前主機執行的語(yǔ)句總數
? statement_latency : 語(yǔ)句等待時(shí)間（延遲時(shí)間）
? statement_avg_latency : 執行語(yǔ)句平均延遲時(shí)間
? table_scans : 表掃描次數
? file_ios : io時(shí)間總數
? file_io_latency : 文件io延遲
? current_connections : 當前連接數
? total_connections : 總鏈接數
? unique_users : 該主機的唯一用戶(hù)數
? current_memory : 當前賬戶(hù)分配的內存
? total_memory_allocated : 該主機分配的內存總數
(2).host_summary_by_file_io
統計了host的文件io信息
?host : 主機
?iOS : IO事件總數
?io_latency : IO總的延遲時(shí)間
(3).host_summary_by_file_io_type
按照host和event進(jìn)行分組，統計文件的io信息
?host : 主機
?event_name : IO事件名稱(chēng)
?total : 該主機發(fā)生的事件
?total_latency : 該主機發(fā)生IO事件總延遲時(shí)間
?max_latency : 該主機IO事件中最大的延遲時(shí)間
(4).host_summary_by_stages
總計語(yǔ)句stage，由host分組
? host ：主機
? event_name ：名稱(chēng)
? total ：發(fā)生的總數
? total_latency ： 總的延遲時(shí)間
? avg_latency ：平均延遲時(shí)間
(5).host_summary_by_statement_latency
統計了host，執行SQL語(yǔ)句的信息
? host ： 主機
? total ： 這個(gè)主機的語(yǔ)句總數
? total_latency ： 這個(gè)主機總的延遲時(shí)間
? max_latency ： 主機最大的延遲時(shí)間
? lock_latency ： 等待鎖的鎖延遲時(shí)間
? rows_sent ： 該主機通過(guò)語(yǔ)句返回的總行數
? rows_examined ： 在存儲引擎上通過(guò)語(yǔ)句返回的行數
? rows_affected ： 該主機通過(guò)語(yǔ)句影響的總行數
? full_scans ： 全表掃描的語(yǔ)句總數
(6).host_summary_by_statement_type
按照host和sql類(lèi)型進(jìn)行分組，統計SQL語(yǔ)句執行信息
? host： 主機
? statement： 最后的語(yǔ)句事件名稱(chēng)
? total： sql語(yǔ)句總數
? total_latency： sql語(yǔ)句總延遲數
? max_latency： 最大的sql語(yǔ)句延遲數
? lock_latency： 鎖延遲總數
? rows_sent： 語(yǔ)句返回的行總數
? rows_examined： 通過(guò)存儲引擎的sql語(yǔ)句的讀取的總行數
? rows_affected： 語(yǔ)句影響的總行數
? full_scans ： 全表掃描的語(yǔ)句事件總數

2.innodb視圖
(1).innodb_buffer_stats_by_schema
通過(guò)數據庫統計innodb引擎的innodb緩存
? object_schema： 數據庫名稱(chēng)
? allocated： 分配給當前數據庫的總的字節數
? data： 分配給當前數據庫的數據字節數
? pages： 分配給當前數據庫的總頁(yè)數
? pages_hashed： 分配給當前數據庫的hash頁(yè)數
? pages_old： 分配給當前數據庫的舊頁(yè)數
? rows_cached： 當前數據庫緩存的行數
 
(2).innodb_buffer_stats_by_table
每個(gè)表innodb引擎的innodb緩存
? object_schema： 數據庫名稱(chēng)
? object_name： 表名稱(chēng)
? allocated： 分配給表的總字節數
? data： 分配該表的數據字節數
? pages： 分配給表的頁(yè)數
? pages_hashed： 分配給表的hash頁(yè)數
? pages_old ： 分配給表的舊頁(yè)數
? rows_cached ： 表的行緩存數

(3).innodb_lock_waits
總計了innodb鎖等待
? wait_started ： 鎖等待發(fā)生的時(shí)間
? wait_age ： 鎖已經(jīng)等待了多長(cháng)時(shí)間
? wait_age_secs ： 以秒為單位顯示鎖已經(jīng)等待的時(shí)間（5.7.9中添加此列）
? locked_table ： 被鎖的表
? locked_index ： 被鎖住的索引
? locked_type ： 鎖類(lèi)型
? waiting_trx_id ： 正在等待的事務(wù)ID
? waiting_trx_started ： 等待事務(wù)開(kāi)始的時(shí)間
? waiting_trx_age ： 已經(jīng)等待事務(wù)多長(cháng)時(shí)間