MySQL系统库之sys

Love The Way You Lie 2023-01-19 04:36 11阅读 0赞

sys这个数据库主要是通过视图的形式把information\_schema和  
performance\_schema结合起来，让程序员可以更方便的了解MySQL服务器的一  
些性能信息。

sys系统库通常都是提供给专业的DBA人员排查一些特定问题使用的，其下  
所涉及的各项查询或多或少都会对性能有一定的影响。

在使用上：  
sys系统库支持MySQL 5.6或更高版本，不支持MySQL5.5.x及以下版本。

# sys库的启用 #

因为sys系统库提供了一些代替直接访问performance\_schema的视图，所  
以必须启用performance\_schema（将performance\_schema系统参数设置为ON），sys系统库的大部分功能才能正常使用。  
同时要完全访问sys系统库，用户必须具有以下数据库的管理员权限。

当然sys系统库本身已经提供了启用所有需要的功能的存储过程，比如：

启用所有的wait instruments：

mysql> call sys.ps_setup_enable_instrument('wait');
    +-------------------------+
    | summary                 |
    +-------------------------+
    | Enabled 315 instruments |
    +-------------------------+
    1 row in set (0.12 sec)
    
    Query OK, 0 rows affected (0.12 sec)

启用所有事件类型的current表：

mysql> call sys.ps_setup_enable_consumer('current');
    +---------------------+
    | summary             |
    +---------------------+
    | Enabled 3 consumers |
    +---------------------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)

> 注意：performance\_schema的默认配置就可以满足sys系统库的大部分数据  
> 收集功能。启用所有需要功能会对性能产生一定的影响，因此最好仅启用所需的  
> 配置。

# sys系统库的使用 #

如果使用了`use`语句切换默认数据库，那么就可以直接使用sys系统库下的  
视图进行查询，就像查询某个库下的表一样操作。也可以使用 b\_name.view\_name、  
db\_name.procedure\_name、db\_name.func\_name等方式，在不指定默认数据库的  
情况下访问sys系统库中的对象（这叫作名称限定对象引用）。

mysql> select * from sys.version;
    +-------------+---------------+
    | sys_version | mysql_version |
    +-------------+---------------+
    | 1.5.2       | 5.7.31-log    |
    +-------------+---------------+
    1 row in set (0.00 sec)
    
    mysql> use sys;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> select * from version;
    +-------------+---------------+
    | sys_version | mysql_version |
    +-------------+---------------+
    | 1.5.2       | 5.7.31-log    |
    +-------------+---------------+
    1 row in set (0.00 sec)

在sys系统库下包含很多视图，它们以各种方式对performance\_schema表  
进行聚合计算展示。这些视图大部分是成对出现的，两个视图名称相同，但有一

个视图是带“x ” 前 缀 的 ， 例 如 ： h o s t s u m m a r y b y f i l e i o 和 x ”前缀的，例如：host\_summary\_by\_file\_io和 x ”前缀的，例如：hostsummarybyfileio和xhost\_summary\_by\_file\_io，代表按照主机进行汇总统计的文件 I/O 性能数据，  
两个视图访问的数据源是相同的，但是在创建视图的语句中，不带“x ” 前 缀 的 视 图 显 示 的 是 相 关 数 值 经 过 单 位 换 算 后 的 数 据 （ 单 位 是 毫 秒 、 秒 、 分 钟 、 小 时 、 天 等 ） ， 带 “ x ”前缀的 视图显示的是相关数值经过单位换算后的数据（单位是毫秒、秒、分钟、小时、 天等），带“x ”前缀的视图显示的是相关数值经过单位换算后的数据（单位是毫秒、秒、分钟、小时、天等），带“x”前缀的视图显示的是原始的数据（单位是皮秒）。

## 查看慢 SQL 语句慢在哪里 ##

如果我们频繁地在慢查询日志中发现某个语句执行缓慢，且在表结构、索引  
结构、统计信息中都无法找出原因时，则可以利用sys系统库中的撒手锏：

sys.session视图结合performance\_schema的等待事件来找出症结所在。那么  
session视图有什么用呢？使用它可以查看当前用户会话的进程列表信息，看看

当前进程到底再干什么，注意，这个视图在 MySQL 5.7.9 中才出现。

首先需要启用与等待事件相关功能：

mysql> call sys.ps_setup_enable_instrument('wait');
    +-----------------------+
    | summary               |
    +-----------------------+
    | Enabled 8 instruments |
    +-----------------------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> call sys.ps_setup_enable_consumer('wait');
    +---------------------+
    | summary             |
    +---------------------+
    | Enabled 2 consumers |
    +---------------------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)

然后在一个终端上模拟一个会话耗时的操作：

mysql> select sleep(30);

在另一个终端上查看其他会话阻塞在哪里：

mysql> select * from sys.session where command='query' and conn_id!=connection_id()\G;
    *************************** 1. row ***************************
                    thd_id: 30
                   conn_id: 4
                      user: root@localhost
                        db: sys
                   command: Query
                     state: User sleep
                      time: 3
         current_statement: select sleep(30)
         statement_latency: 2.25 s
                  progress: NULL
              lock_latency: 0 ps
             rows_examined: 0
                 rows_sent: 0
             rows_affected: 0
                tmp_tables: 0
           tmp_disk_tables: 0
                 full_scan: NO
            last_statement: NULL
    last_statement_latency: NULL
            current_memory: 0 bytes
                 last_wait: wait/synch/cond/sql/Item_func_sleep::cond
         last_wait_latency: Still Waiting
                    source: 
               trx_latency: NULL
                 trx_state: NULL
            trx_autocommit: NULL
                       pid: 1451
              program_name: mysql
    1 row in set (0.38 sec)

## 查询表的增、删、改、查数据量和 I/O 耗时统计 ##

mysql> select * from schema_table_statistics_with_buffer\G;
    *************************** 1. row ***************************
                  table_schema: sys
                    table_name: sys_config
                  rows_fetched: 1
                 fetch_latency: 44.57 us
                 rows_inserted: 0
                insert_latency: 0 ps
                  rows_updated: 0
                update_latency: 0 ps
                  rows_deleted: 0
                delete_latency: 0 ps
              io_read_requests: 16
                       io_read: 147.85 KiB
               io_read_latency: 2.35 ms
             io_write_requests: 0
                      io_write: 0 bytes
              io_write_latency: 0 ps
              io_misc_requests: 17
               io_misc_latency: 9.46 ms
       innodb_buffer_allocated: 16.00 KiB
            innodb_buffer_data: 338 bytes
            innodb_buffer_free: 15.67 KiB
           innodb_buffer_pages: 1
    innodb_buffer_pages_hashed: 0
       innodb_buffer_pages_old: 0
     innodb_buffer_rows_cached: 6
    1 row in set (0.23 sec)

## 查看是否有事务锁等待 ##

innodb\_lock\_wait视图通常由于分析事务锁（行锁）等待，注意必须要有事务正在等待该视图才会有值。

演示步骤：

<table> 
 <thead> 
  <tr> 
   <th>步骤</th> 
   <th>会话1</th> 
   <th>会话2</th> 
   <th>会话3</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>begin;</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>select * from dept where id=1 for update;</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td></td> 
   <td>begin;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td></td> 
   <td>select * from dept where id=1 for update;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td></td> 
   <td></td> 
   <td>select * from sys.innodb_lock_waits\G;</td> 
  </tr> 
 </tbody> 
</table>

mysql> select * from sys.innodb_lock_waits\G;
    *************************** 1. row ***************************
                    wait_started: 2021-05-07 09:19:40
                        wait_age: 00:00:05
                   wait_age_secs: 5
                    locked_table: `test`.`dept`
                    locked_index: PRIMARY
                     locked_type: RECORD
                  waiting_trx_id: 8967
             waiting_trx_started: 2021-05-07 09:19:40
                 waiting_trx_age: 00:00:05
         waiting_trx_rows_locked: 1
       waiting_trx_rows_modified: 0
                     waiting_pid: 5
                   waiting_query: select * from dept where id=1 for update
                 waiting_lock_id: 8967:44:3:2
               waiting_lock_mode: X
                 blocking_trx_id: 8966
                    blocking_pid: 6
                  blocking_query: NULL
                blocking_lock_id: 8966:44:3:2
              blocking_lock_mode: X
            blocking_trx_started: 2021-05-07 09:19:21
                blocking_trx_age: 00:00:24
        blocking_trx_rows_locked: 1
      blocking_trx_rows_modified: 0
         sql_kill_blocking_query: KILL QUERY 6
    sql_kill_blocking_connection: KILL 6
    1 row in set, 3 warnings (0.01 sec)

## 查看InnoDB缓冲池中热点库 ##

使用innodb\_buffer\_stats\_by\_schema视图可按照schema分组查询InnoDB缓冲池的统计信息

mysql> select * from sys.innodb_buffer_stats_by_schema;
    +---------------+------------+-----------+-------+--------------+-----------+-------------+
    | object_schema | allocated  | data      | pages | pages_hashed | pages_old | rows_cached |
    +---------------+------------+-----------+-------+--------------+-----------+-------------+
    | InnoDB System | 1.55 MiB   | 1.22 MiB  |    99 |            0 |         0 |        1229 |
    | mysql         | 240.00 KiB | 1.46 KiB  |    15 |            0 |         0 |          21 |
    | sys           | 16.00 KiB  | 338 bytes |     1 |            0 |         0 |           6 |
    | test          | 16.00 KiB  | 27 bytes  |     1 |            0 |         0 |           1 |
    +---------------+------------+-----------+-------+--------------+-----------+-------------+
    4 rows in set (0.13 sec)

视图字段含义如下：

*  object\_schema：对象所在schema，如果该表属于Innodb存储引擎，则该字段显示为InnoDB System，如果是其他引擎，则该字段显示为每个schema name（db名）。
 *  allocated：当前已分配给schema的总内存字节数
 *  data：当前已分配给schema的数据部分使用的内存字节总数
 *  pages：当前已分配给schema内存总页数
 *  pages\_hashed：当前已分配给schema的自适应hash索引页总数
 *  pages\_old：当前已分配给schema的旧页总数（位于LRU列表中的旧块子列表中的页数）
 *  rows\_cached：buffer pool中为schema缓冲的总数据行数

## 查看冗余索引 ##

使用MySQL5.7.9新增的sys.schema\_redundant\_indexes视图，其数据来源为sys.x$schema\_flattened\_keys。

mysql> select * from sys.schema_redundant_indexes;

## 查看未使用的索引 ##

schema\_unused\_indexes视图可以查看未用过的索引，其数据来源为performance\_schema.table\_io\_waits\_summary\_by\_index\_usage。该视图在数据库运行足够长时间后数据才有参考意义。

mysql> select * from sys.schema_unused_indexes;

## 查看磁盘文件产生的磁盘流量与读写比例 ##

io\_global\_by\_file\_by\_bytes视图可以按照文件路径+名称分组（磁盘文件名）查看全局IO字节数、读写文件IO事件数量统计信息，默认按总IO读写字节数进行降序排列。

各个字段含义如下：

*  file：被操作的文件名
 *  count\_read：总共有多少次读
 *  total\_read：总共读了多少字节
 *  avg\_read：平均每次读多少字节
 *  count\_write：总共多少次写
 *  total\_written：总共写了多少字节
 *  avg\_write：平均每次写的字节
 *  total：读和写总共的IO
 *  write\_pct：写占IO里的占比

## 查看哪些语句使用了全表扫描 ##

statements\_with\_full\_table\_scans可查看全表扫描或者没有使用到最优索引的语句（经过标准化转化的语句文本），默认按照平均扫描次数百分比和语句总延迟时间（执行时间）降序排序。数据来源：performance\_schema.events\_statements\_summary\_by\_digest。

mysql> select * from sys.statements_with_full_table_scans limit 1\G;
    *************************** 1. row ***************************
                       query: SELECT `information_schema` .  ... . `STATISTICS` . `INDEX_NAME` 
                          db: test
                  exec_count: 1
               total_latency: 8.56 ms
         no_index_used_count: 1
    no_good_index_used_count: 0
           no_index_used_pct: 100
                   rows_sent: 0
               rows_examined: 6
               rows_sent_avg: 0
           rows_examined_avg: 6
                  first_seen: 2021-05-07 09:27:27
                   last_seen: 2021-05-07 09:27:27
                      digest: ae5d4708e5973332bb9b3a04eb4ce19d
    1 row in set (0.01 sec)

视图各字段含义如下：

*  query：经过标准化转换的语句字符串
 *  db：语句对应的默认数据库，如果没有默认数据库，该字段为NULL
 *  exec\_count：语句执行的总次数
 *  total\_latency：语句执行的总延迟时间（执行时间）
 *  no\_index\_used\_count：语句执行没有使用索引扫描表（而是使用全表扫描）的总次数
 *  no\_good\_index\_used\_count：语句执行没有使用到更好的索引扫描表的总次数
 *  no\_index\_used\_pct：语句执行没有使用索引扫描表(而是使用全表扫描)的次数与语句执行总次数的百分比
 *  rows\_sent：语句执行从表返回给客户端的总数据行数
 *  rows\_examined：语句执行从存储引擎检查的总数据行数
 *  rows\_sent\_avg：每个语句执行从表中返回客户端的平均数据行数
 *  rows\_examined\_avg：每个语句执行从存储引擎读取的平均数据行数
 *  first\_seen：该语句第一次出现的时间
 *  last\_seen：该语句最近一次出现的时间
 *  digest：语句摘要计算的md5 hash值

## 查看哪些语句使用了文件排序 ##

statements\_with\_sorting视图可查看执行了文件排序的语句，默认情况下按照语句总延迟时间（执行时间）降序排序，数据来源：performance\_schema.events\_statements\_summary\_by\_digest。

mysql> select * from sys.statements_with_sorting limit 1\G;
    *************************** 1. row ***************************
                query: SELECT `sys` . `format_path` ( ...  ) ) , ? ) ) AS `avg_write` , 
                   db: test
           exec_count: 2
        total_latency: 2.12 s
    sort_merge_passes: 0
      avg_sort_merges: 0
    sorts_using_scans: 2
     sort_using_range: 0
          rows_sorted: 269
      avg_rows_sorted: 135
           first_seen: 2021-05-07 09:29:28
            last_seen: 2021-05-07 09:30:19
               digest: 8a0cfcb07bad7d1e9ef40b9d99ff2c5f
    1 row in set (0.00 sec)

视图字段含义如下：

*  query：经过标准化转换的语句字符串
 *  db：语句对应的默认数据库，如果没有默认数据库，该字段为NULL
 *  exec\_count：语句执行的总次数
 *  total\_latency：语句执行的总延迟时间（执行时间）
 *  sort\_merge\_passes：语句执行发生的语句排序合并的总次数
 *  avg\_sort\_merges：语句的平均排序合并次数 SUM\_SORT\_MERGE\_PASSES/COUNT\_STAR
 *  sorts\_using\_scans：语句排序执行全表扫描的总次数
 *  sort\_using\_range：语句排序执行范围扫描的总次数
 *  rows\_sorted：语句执行发生排序的总数据行数
 *  avg\_rows\_sorted：语句的平均排序数据行数 SUM\_SORT\_ROWS/COUNT\_STAR
 *  first\_seen：该语句第一次出现的时间
 *  last\_seen：该语句最近一次出现的时间
 *  digest：语句摘要计算的md5 hash值

## 查看哪些语句使用了临时表 ##

tatements\_with\_temp\_tables 查看使用了临时表的语句，默认情况下按照磁盘临时表数量和内存临时表数量进行降序排序。数据来源：performance\_schema.events\_statements\_summary\_by\_digest。

mysql> select * from sys.statements_with_temp_tables limit 1\G;
    *************************** 1. row ***************************
                       query: SELECT `t` . `OBJECT_SCHEMA` A ... SCHEMA` , `t` . `OBJECT_NAME` 
                          db: test
                  exec_count: 1
               total_latency: 11.48 ms
           memory_tmp_tables: 63
             disk_tmp_tables: 10
    avg_tmp_tables_per_query: 63
      tmp_tables_to_disk_pct: 16
                  first_seen: 2021-05-07 09:28:36
                   last_seen: 2021-05-07 09:28:36
                      digest: e1f092285d953ee6a7a6cc32bac9dc42
    1 row in set (0.00 sec)

视图字段含义如下：

*  query：经过标准化转换的语句字符串
 *  db：语句对应的默认数据库，如果没有默认数据库，该字段为NULL
 *  exec\_count：语句执行的总次数
 *  total\_latency：语句执行的总延迟时间（执行时间）
 *  memory\_tmp\_tables：语句执行时创建内部内存临时表的总数量
 *  disk\_tmp\_tables：语句执行时创建的内部磁盘临时表的总数量
 *  avg\_tmp\_tables\_per\_query：语句使用内存临时表的平均数量 SUM\_CREATED\_TMP\_TABLES/COUNT\_STAR
 *  tmp\_tables\_to\_disk\_pct：内存临时表的总数量与磁盘临时表的总数量百分比，表示磁盘临时表的转换率 SUM\_CREATED\_TMP\_DISK\_TABLES/SUM\_CREATED\_TMP\_TABLES
 *  first\_seen：该语句第一次出现的时间
 *  last\_seen：该语句最近一次出现的时间
 *  digest：语句摘要计算的md5 hash值