参考文档：

1. MySQL- 5.7 sys schema笔记
   
2. MySQL 5.7新特性：SYS库详解
   
3. MySQL Performance Schema&sys Schema介绍
   
4. 内存分配统计视图 | 全方位认识 sys 系统库
   
5. MySQL sys Schema
   
6. 初相识 | 全方位认识sys系统库
   
7. MySQL 5.7 Reference Manual / MySQL sys Schema

虽然MySQL5.7中有sys Schema这样子的特性，提供了一系列视图查看MySQL数据库当前的运行状态和性能信息，但是一直以来都使用过和认真的阅读过相关的文档。正好这次部门有一个比较的重要的线下计算系统每天半夜计算的时候总是内存报使用率95%以上，同时伴有数据库被操作系统kill掉的情况发生。在调查原因的过程中，使用了sys schema 的相关功能，特此记录下。

1. sys schema 介绍

从mysql5.5开始mysql提供了performance_schema性能引擎，可以通过查询这个库获取当前MySQL的运行状态。但是这个库比较复杂，而且查询和操作起来也不是方便，因此从5.7开始提供了sys Schema 库方便DBA进行操作。sys schema的基础数据都来自于performance和information_shcema两个库，本身不存储及集采数据。

2. sys schema 功能

在这里来介绍下sys schema都可以提供哪些数据。在sys schema 中提供了大量的对象，这些对象包括视图，表，触发器，函数，存储过程等。通过以下的语句查询overview视图，就可以知道sys库中有多少的对象。 可以看出这个库中就一个表，其他的绝大部分是视图。

        mysql> use sys
Database changed
mysql> select * from schema_object_overview where db=‘sys‘;
+-----+---------------+-------+
| db  | object_type   | count |
+-----+---------------+-------+
| sys | PROCEDURE     |    26 |
| sys | VIEW          |   100 |
| sys | BASE TABLE    |     1 |
| sys | INDEX (BTREE) |     1 |
| sys | TRIGGER       |     2 |
| sys | FUNCTION      |    22 |
+-----+---------------+-------+
6 rows in set (16.84 sec)
    

2.2 sys中的视图

在sys库中占绝大多数的对象是视图。如果通过show tables命令查看库中的对象，会发现这些视图有2种不同的形式。 例如显示当前mysql服务器实际使用了多少内存的视图有如下的形式：

        memory_global_total
x$memory_global_total
    

那么这两种形式的视图有什么不同呢？

1. 以字母开头的视图是经过转换后，便于人类阅读的形式。
   
2. 以 "x" 开头的视图，是没有经过格式化的，方便程序进行处理。
   例如通过以下2个视图的对比，就可以看出区别。

        mysql> select * from memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 183.85 MiB      |
+-----------------+
1 row in set (0.09 sec)

mysql> select * from x$memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
|       192784960 |
+-----------------+
1 row in set (0.00 sec)
    

接下来对sys schema中的其他视图做下简单的介绍，库中视图汇总如下：

接下来将介绍几个使用比较多的视图：

1. host 开头的视图
2. user开头的视图
3. innodb开头的视图
4. io开头的视图
5. memory开头的视图

2.2.1 host 开头的视图

2.2.1.1 host_summary表

host_summary表按host分组统计了语句的执行时间，次数，相关文件的IO，连接数和内存分配等信息。这个视图拥有列如下：

        mysql> select * from host_summary limit 1 \G
*************************** 1. row ***************************
                  host: XXXXX
            statements: 2048
     statement_latency: 4.81 s
 statement_avg_latency: 2.35 ms
           table_scans: 917
              file_ios: 7882
       file_io_latency: 2.51 s
   current_connections: 0
     total_connections: 6
          unique_users: 1
        current_memory: 0 bytes
total_memory_allocated: 0 bytes

    

2.2.1.2 host_summary_by_file_io表

从host角度分组统了I/O的总体情况。这个视图拥有列如下：

        mysql> select * from host_summary_by_file_io limit 1 \G
*************************** 1. row ***************************
      host: localhost
       ios: 136686048
io_latency: 3.07 d
1 row in set (0.02 sec)

    

2.2.1.3 host_summary_by_file_io_type表

从host角度，统计了每个I/O事件的信息。 这个视图拥有列如下：

        mysql> select * from host_summary_by_file_io_type limit  1 \G
*************************** 1. row ***************************
         host: XXXXXX
   event_name: wait/io/file/sql/FRM
        total: 2868
total_latency: 1.62 s
  max_latency: 71.06 ms
1 row in set (0.08 sec)

    

2.2.1.4 host_summary_by_stages表

从host角度，统计了每个I/O事件各个阶段的信息。这个视图拥有如下的列：

        mysql> select * from host_summary_by_stages limit 1 \G
*************************** 1. row ***************************
         host: background
   event_name: stage/innodb/buffer pool load
        total: 1
total_latency: 4.17 ms
  avg_latency: 4.17 ms
1 row in set (0.00 sec)

    

2.2.1.5 host_summary_by_statement_latency表

按照host角度，统计了host执行语句的总体情况。包括执行的sql语句数，执行时间，锁等待时间，返回的数据量等信息。 这个视图拥有如下的列：

        mysql> select * from host_summary_by_statement_latency limit 1 \G
*************************** 1. row ***************************
         host: localhost
        total: 123861
total_latency: 1.24 d
  max_latency: 1.68 h
 lock_latency: 6.03 s
    rows_sent: 41473119
rows_examined: 13417905908
rows_affected: 12801220
   full_scans: 5059
    

2.2.1.6 host_summary_by_statement_type表

按照host和语句的执行类型，分组统计了包括执行的sql语句数，执行时间，锁等待时间，返回的数据量等信息。这个视图只返回执行时间不为0的统计信息。
这个视图拥有如下的列：

在这里提到的语句执行类型是什么呢？来看下表里的数据就知道了。从下面的结果可以看出执行语句的类型，就是执行的是select，update等等语句。

        mysql> select distinct statement   from host_summary_by_statement_type  ;
+-----------------------+
| statement             |
+-----------------------+
| select                |
| create_table          |
| alter_table           |
| update                |
| insert                |
| insert_select         |
| delete                |
| drop_table            |
| show_databases        |
| show_tables           |
| show_fields           |
| show_variables        |
| show_status           |
| show_engine_status    |
| show_slave_status     |
| show_grants           |
| show_create_table     |
| show_table_status     |
| show_triggers         |
| load                  |
| set_option            |
| lock_tables           |
| unlock_tables         |
| change_db             |
| replace               |
| replace_select        |
| flush                 |
| rollback              |
| commit                |
| begin                 |
| delete_multi          |
| update_multi          |
| show_warnings         |
| show_create_func      |
| show_procedure_status |
| show_function_status  |
| show_create_trigger   |
| error                 |
| stmt                  |
| set                   |
| jump                  |
| jump_if_not           |
| freturn               |
| Quit                  |
| Init DB               |
| set_case_expr         |
| Ping                  |
+-----------------------+
47 rows in set (0.01 sec)
    

        mysql> select *  from host_summary_by_statement_type  limit 1 \G
*************************** 1. row ***************************
         host: XXXX
    statement: select
        total: 191798
total_latency: 57.59 s
  max_latency: 208.20 ms
 lock_latency: 22.66 s
    rows_sent: 412
rows_examined: 12479336
rows_affected: 0
   full_scans: 106
1 row in set (0.01 sec)
    

2.2.2 innodb 开头的视图

innodb开头的视图，主要是显示innodb buffer相关的统计信息。虽然这个库提供了不少innodb buffer的相关信息，但是查询这些表的相关信息会一定程度是上影响数据库的性能。mysql官方文档上有如下的提示:

Warning
Querying views that access the INNODB_BUFFER_PAGE table can affect performance. Do not query these views on a production system unless you are aware of the performance impact and have determined it to be acceptable. To avoid impacting performance on a production system, reproduce the issue you want to investigate and query buffer pool statistics on a test instance.

2.2.2.1 innodb_buffer_stats_by_schema表

按数据分组统计了innodb buffer的相关信息。这个视图拥有如下的列：

        mysql> select * from innodb_buffer_stats_by_schema limit 1 \G
*************************** 1. row ***************************
object_schema: XXXXX
    allocated: 38.59 GiB
         data: 35.58 GiB
        pages: 2528767
 pages_hashed: 163354
    pages_old: 782043
  rows_cached: 73175818
1 row in set (2 min 25.12 sec)
    

2.2.2.2 innodb_buffer_stats_by_table表

从表的角度提供innodb buffer 的统计信息。这个视图拥有如下的列：

        mysql> select * from innodb_buffer_stats_by_table limit 1 \G
*************************** 1. row ***************************
object_schema: XXX
  object_name: XXX
    allocated: 23.08 GiB
         data: 15.88 GiB
        pages: 1512638
 pages_hashed: 745355
    pages_old: 213638
  rows_cached: 207340166
1 row in set (3 min 52.70 sec)
    

2.2.2.3 innodb_lock_waits表

这个视图汇总了innodb事物正在等待的锁的情况。这个视图拥有如下的列：

我们来看看当有个阻塞的事物的时候，这个表中数据的情况：
1.首先建立一个表 ,同时有数据如下：

        mysql> show create table test_lock  \G
*************************** 1. row ***************************
       Table: test_lock
Create Table: CREATE TABLE `test_lock` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from test_lock;
+-----+
| id  |
+-----+
|   3 |
|  10 |
| 100 |
+-----+
3 rows in set (0.00 sec)
    

2.事务1

        mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_lock set id =2  where id =10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
    

3.事物2

        mysql> begin ;
mysql> select * from test_lock where id = 10 for update ;
    

4.show processlist

        *************************** 8. row ***************************
     Id: 1690543
   User: xxxx
   Host: localhost
     db: test
Command: Query
   Time: 2
  State: statistics
   Info: select * from test_lock where id = 10 for update
    

5.查看表可以有如下的数据：

        mysql> select * from innodb_lock_waits \G
*************************** 1. row ***************************
                wait_started: 2018-12-31 01:26:54
                    wait_age: 00:00:20
               wait_age_secs: 20
                locked_table: `test`.`test_lock`
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 294360748
         waiting_trx_started: 2018-12-30 23:18:16
             waiting_trx_age: 02:08:58
     waiting_trx_rows_locked: 5
   waiting_trx_rows_modified: 0
                 waiting_pid: 1690543
               waiting_query: select * from test_lock where id = 10 for update
             waiting_lock_id: 294360748:30180851:3:2
           waiting_lock_mode: X
             blocking_trx_id: 294360747
                blocking_pid: 1690288
              blocking_query: NULL
            blocking_lock_id: 294360747:30180851:3:2
          blocking_lock_mode: X
        blocking_trx_started: 2018-12-30 23:17:59
            blocking_trx_age: 02:09:15
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 2
     sql_kill_blocking_query: KILL QUERY 1690288
sql_kill_blocking_connection: KILL 1690288
1 row in set, 3 warnings (0.04 sec)
    

可以看出 waiting开头的部分就是事务2，正在等待锁的事务。而blocking开头的就是正在阻塞其他进程的事务。

2.2.3 io 开头的视图

以I/O层面为视角，统计I/O相关的信息。

2.2.3.1 io_by_thread_by_latency 表

这个视图从线程的角度，统计了I/O的延迟情况（I/O时间消耗）这个视图拥有以下的列：

        mysql> select * from io_by_thread_by_latency limit 1 \G
*************************** 1. row ***************************
          user: XXXXX
         total: 3982138
 total_latency: 46.85 m
   min_latency: 384.48 ns
   avg_latency: 8.60 ms
   max_latency: 50.59 s
     thread_id: 30
processlist_id: NULL
1 row in set (0.04 sec)
    

2.2.3.2 io_global_by_file_by_bytes 表

这个视图从每个文件的角度，统计了I/O的情况（主要显示文件写入和读取的byte数量）。这个视图拥有以下的列：

        mysql> select * from io_global_by_file_by_bytes limit 1 \G
*************************** 1. row ***************************
         file: XXXXX
   count_read: 124097831
   total_read: 1.85 TiB
     avg_read: 16.00 KiB
  count_write: 154762
total_written: 2.36 GiB
    avg_write: 16.00 KiB
        total: 1.85 TiB
    write_pct: 0.12
1 row in set (0.03 sec)
    

2.2.3.3 io_global_by_file_by_latency表(misc是啥)

这个视图从每个文件的角度，统计了I/O的情况（主要显示I/O的时间延迟）。这个视图拥有以下的列：

        mysql> select * from  io_global_by_file_by_latency limit 1 \G
*************************** 1. row ***************************
         file: XXXXX.ibd
        total: 58795939
total_latency: 7.52 h
   count_read: 56231839
 read_latency: 7.47 h
  count_write: 2372895
write_latency: 1.51 m
   count_misc: 191205
 misc_latency: 1.18 m
1 row in set (0.02 sec)
    

2.2.3.4 io_global_by_wait_by_bytes表

这个视图从I/O事件的角度（显示的时候会有wait/io/file/ 这样子的前缀），统计了I/O的情况（主要显示I/O的时间延迟，和写入和读取的byte数）。这个视图拥有如下的列：

        mysql> select * from io_global_by_wait_by_bytes limit 1  \G
*************************** 1. row ***************************
     event_name: innodb/innodb_data_file
          total: 762718061
  total_latency: 2.47 d
    min_latency: 0 ps
    avg_latency: 279.74 us
    max_latency: 16.23 s
     count_read: 743120377
     total_read: 11.07 TiB
       avg_read: 16.00 KiB
    count_write: 17834643
  total_written: 484.40 GiB
    avg_written: 28.48 KiB
total_requested: 11.55 TiB
1 row in set (0.03 sec)
    

2.2.4 memory开头的视图

以内存层面为视角，统计分析内存使用情况相关信息。

2.2.4.1 memory_by_host_by_current_bytes表

从host层面，统计当前内存使用的情况，这个视图拥有以下的几个列：

        mysql>  select * from memory_by_host_by_current_bytes order by current_allocated desc \G
*************************** 1. row ***************************
              host: background
current_count_used: 0
 current_allocated: 0 bytes
 current_avg_alloc: 0 bytes
 current_max_alloc: 0 bytes
   total_allocated: 0 bytes

    

其他几个相关的表，结构和作用都差不多，不过从其他的层面进行统计，这里就不多做介绍了：

1. memory_by_thread_by_current_bytes
2. memory_by_user_by_current_bytes
3. memory_global_by_current_bytes
4. memory_global_total

2.2.5 user开头的表

user开头的表，主要从user的角度统计相关的信息。

2.2.5.1 user_summary表

这个表在用户的维度统计了语句的执行情况，I/O和连接数等情况。这个表拥有如下的列：

        mysql> select * from user_summary limit 1 \G
*************************** 1. row ***************************
                  user: XXXX
            statements: 520988370
     statement_latency: 8.04 w
 statement_avg_latency: 9.34 ms
           table_scans: 3729186
              file_ios: 175723683186
       file_io_latency: 2.23 w
   current_connections: 27
     total_connections: 1063563
          unique_hosts: 6
        current_memory: 0 bytes
total_memory_allocated: 0 bytes
1 row in set (0.04 sec)
    

2.2.5.2 user_summary_by_file_io表

这个表按用户汇总了I/O的相关信息。这个表拥有如下的列：

        mysql> select * from user_summary_by_file_io;
+------------+-------------+------------+
| user       | ios         | io_latency |
+------------+-------------+------------+
| XXXX1      | 45364630590 | 3.92 d     |
| XXXX5      |    35400554 | 2.60 h     |
| XXXX4      |  5316048288 | 39.01 m    |
| XXXX2      |      114389 | 457.59 ms  |
| XXXX3      |         288 | 1.02 ms    |
+------------+-------------+------------+
5 rows in set (0.03 sec)
    

2.2.5.3 user_summary_by_file_io_type表

这个表按用户分别统计了不同类型的I/O的情况。这个表拥有如下的列：

        mysql> select * from user_summary_by_file_io_type  limit 10;
+------------+--------------------------------------+----------+-----------+-------------+
| user       | event_name                           | total    | latency   | max_latency |
+------------+--------------------------------------+----------+-----------+-------------+
| background | wait/io/file/innodb/innodb_data_file | 35139637 | 1.37 h    | 14.10 s     |
| background | wait/io/file/innodb/innodb_log_file  |   311112 | 1.25 h    | 50.59 s     |
| background | wait/io/file/sql/FRM                 |   129640 | 121.38 ms | 1.74 ms     |
| background | wait/io/file/sql/ERRMSG              |        5 | 1.24 ms   | 1.20 ms     |
| background | wait/io/file/sql/file_parser         |      350 | 760.17 us | 13.46 us    |
| background | wait/io/file/sql/binlog              |       20 | 459.69 us | 200.42 us   |
| background | wait/io/file/myisam/kfile            |       33 | 109.25 us | 12.38 us    |
| background | wait/io/file/sql/casetest            |       10 | 93.15 us  | 44.42 us    |
| background | wait/io/file/myisam/dfile            |       29 | 92.66 us  | 14.30 us    |
| background | wait/io/file/sql/binlog_index        |       20 | 84.62 us  | 39.29 us    |
+------------+--------------------------------------+----------+-----------+-------------+
10 rows in set (0.01 sec)
    

2.2.5.4 user_summary_by_stages表

这个表按用户分别统计阶段事件的信息。这个表拥有如下的列：

        mysql> select  * from user_summary_by_stages;
+------------+-------------------------------+-------+---------------+-------------+
| user       | event_name                    | total | total_latency | avg_latency |
+------------+-------------------------------+-------+---------------+-------------+
| background | stage/innodb/buffer pool load |     1 | 18.49 s       | 18.49 s     |
| XXXXXX     | stage/sql/copy to tmp table   |  3579 | 5.96 s        | 1.66 ms     |
+------------+-------------------------------+-------+---------------+-------------+
2 rows in set (0.18 sec)
    

2.2.5.5 user_summary_by_statement_latency表

这个表按用户统计了语句的执行情况。这个表拥有如下的列：

        mysql> select * from user_summary_by_statement_latency \G
*************************** 1. row ***************************
         user: XXXX
        total: 125928907
total_latency: 2.04 w
  max_latency: 3.57 h
 lock_latency: 16.55 m
    rows_sent: 512627430
rows_examined: 193893079249
rows_affected: 589428520
   full_scans: 981959
    

2.2.5.6 user_summary_by_statement_type表

按用户和语句事件类型（事件类型名称为语句事件的event_name截取最后一部分字符串，也是语句command类型字符串类似）分组的语句统计信息。这个表拥有如下的列：

        mysql> select * from user_summary_by_statement_type limit 10 \G
*************************** 1. row ***************************
         user: XXX
    statement: show_variables
        total: 5269
total_latency: 9.42 s
  max_latency: 13.69 ms
 lock_latency: 380.99 ms
    rows_sent: 2592348
rows_examined: 5184696
rows_affected: 0
   full_scans: 5269
*************************** 2. row ***************************
         user: XXX
    statement: show_status
        total: 5269
total_latency: 8.06 s
  max_latency: 24.84 ms
 lock_latency: 654.23 ms
    rows_sent: 1859957
rows_examined: 3719914
rows_affected: 0
   full_scans: 5269
    

3. 结束语

到这里，简单的介绍了下sys Schema 库相关的表。那么这些表能查询到些什么数据呢？在一篇文章中在做下介绍吧~

原文：https://www.cnblogs.com/bush2582/p/10201968.html