MySQL 中的系统库之sys 系统库

---

MySQL学习系列

---

系统库简介

以下系统库的介绍， 主要是扩展大家的知识面， 因为系统库中的数据很重要，没有对 MySQL 有全面通盘的了解， 不宜去操作其下的数据， 而且对系统库的管理维护是是 DBA 的职责。

MySQL 有几个系统数据库， 这几个数据库包含了 MySQL 服务器运行过程中所需的一些信息以及一些运行状态信息， 我们现在稍微了解一下。

• performance_schema

这个数据库里主要保存 MySQL 服务器运行过程中的一些状态信息， 算是对MySQL 服务器的一个性能监控。 包括统计最近执行了哪些语句， 在执行过程的每个阶段都花费了多长时间， 内存的使用情况等等信息。

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

• information_schema

这个数据库保存着 MySQL 服务器维护的所有其他数据库的信息， 比如有哪些表、 哪些视图、 哪些触发器、 哪些列、 哪些索引。 这些信息并不是真实的用户数据， 而是一些描述性信息， 有时候也称之为元数据。

• mysql

这个数据库核心， 它存储了 MySQL 的用户账户和权限信息， 一些存储过程、事件的定义信息， 一些运行过程中产生的日志信息， 一些帮助信息以及时区信息等。

sys 系统库

1. sys 使用须知

sys 系统库通常都是提供给专业的 DBA 人员排查一些特定问题使用的， 其下所涉及的各项查询或多或少都会对性能有一定的影响。 所以这个库的配置、 触发器等等我们都不做学习。同时在使用上：sys 系统库支持 MySQL 5.6 或更高版本， 不支持 MySQL 5.5.x 及以下版本。因为 sys 系统库提供了一些代替直接访问 performance_schema 的视图， 所以必须启用 performance_schema（将 performance_schema 系统参数设置为 ON） ，sys 系统库的大部分功能才能正常使用。同时要完全访问 sys 系统库， 用户必须具有以下数据库的管理员权限。如果要充分使用 sys 系统库的功能， 则必须启用某些 performance_schema的功能。 当然 sys 系统库本身已经提供了启用所有需要的功能的存储过程，

比如：启用所有的 wait instruments：

CALL sys.ps_setup_enable_instrument('wait');

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

CALL sys.ps_setup_enable_consumer('current');

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

2. sys 系统库使用

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

在 sys 系统库下包含很多视图， 它们以各种方式对 performance_schema 表进行聚合计算展示。 这些视图大部分是成对出现的， 两个视图名称相同， 但有一个视图是带“x$”前缀的， 例如： host_summary_by_file_io 和x$host_summary_by_file_io， 代表按照主机进行汇总统计的文件 I/O 性能数据，两个视图访问的数据源是相同的， 但是在创建视图的语句中， 不带“x$”前缀的视图显示的是相关数值经过单位换算后的数据（单位是毫秒、 秒、 分钟、 小时、天等） ， 带“x$”前缀的视图显示的是原始的数据（单位是皮秒） 。

查看慢 SQL 语句慢在哪里

如果我们频繁地在慢查询日志中发现某个语句执行缓慢， 且在表结构、 索引结构、 统计信息中都无法找出原因时， 则可以利用 sys 系统库中的杀手锏：sys.session 视图结合 performance_schema 的等待事件来找出症结所在。 那么session 视图有什么用呢？ 使用它可以查看当前用户会话的进程列表信息， 看看当前进程到底再干什么， 注意， 这个视图在 MySQL 5.7.9 中才出现。

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

call sys.ps_setup_enable_instrument('wait');
call sys.ps_setup_enable_consumer('wait');

另一个会话中模拟

select sleep(30);

SELECT * FROM session WHERE command = 'query' AND conn_id != CONNECTION_ID()\G

mysql> SELECT * FROM session WHERE command = 'query' AND conn_id != CONNECTION_ID()\G
*************************** 1. row ***************************
                thd_id: 43
               conn_id: 18
                  user: root@localhost
                    db: performance_schema
               command: Query
                 state: User sleep
                  time: 3
     current_statement: select sleep(30)
     statement_latency: 2.72 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: 303
          program_name: mysql
1 row in set (0.05 sec)

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

select * from schema_table_statistics_with_buffer\G

除此之外， 通过 sys 还可以查询查看 InnoDB 缓冲池中的热点数据、 查看是否有事务锁等待、 查看未使用的， 冗余索引、 查看哪些语句使用了全表扫描等等。