mysql 5.7新数据库sys解析（一）

mysql5.7增加了sys 系统数据库，通过这个库可以快速的了解系统的元数据信息 这个库确实可以方便DBA发现数据库的很多信息，解决性能瓶颈都提供了巨大帮助   这个库在mysql5.7中是默认存在的，在mysql5.6版本以上可以手动导入，数据库包请在github自行查找   这个库包括了哪些内容？ 这个库是通过视图的形式把information_schema 和performance_schema结合起来，查询出更加令人容易理解的数据 存储过程可以可以执行一些性能方面的配置，也可以得到一些性能诊断报告内容 存储函数可以查询一些性能信息   分析每个视图和表之前先说明一下：关于带不带x$，去掉x$同名的视图他们的数据是相同的，区别在于不带x$的单位更加符合直接阅读经过了转换，而带x$是为了某些工具存在而使用的原始单位（多数应该是mysql默认的）   下面就结合mysql官方手册来详细分析sys库
1.表      1.1 sys_config 表         这是在这个系统库上存在的唯一一个表了         先看看表结构

CREATE TABLE `sys_config` (   `variable` varchar(128) NOT NULL,   `value` varchar(128) DEFAULT NULL,   `set_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   `set_by` varchar(128) DEFAULT NULL,   PRIMARY KEY (`variable`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

        variable 配置选项名称

        value     配置选项值         set_time 该行配置修改的时间         set_by     该行配置信息修改者，如果从被安装没有修改过，那么这个数据应该为NULL       表中默认数据为

variable	value	set_time	set_by
diagnostics.allow_i_s_tables	OFF	2015-11-20 16:04:38	root@localhost
diagnostics.include_raw	OFF	2015-11-20 16:04:38	root@localhost
statement_performance_analyzer.limit	100	2015-11-20 16:04:38	root@localhost
statement_performance_analyzer.view		2015-11-20 16:04:38	root@localhost
statement_truncate_len	64	2016-01-22 17:00:16	root@localhost

      以上值的会话变量为@sys.+表中variable字段，譬如：@sys.statement_truncate_len  可以set @sys.statement_truncate_len = 32 临时改变值，在会话中会一直使用这个值，如果想要恢复使用表的默认值，只需要将这个会话值设置为null；set @sys.statement_truncate_len = null;   diagnostics.allow_i_s_tables   diagnostics.include_raw  这两个值默认为OFF ，前者如果开启表示允许diagnostics() 存储过程执行扫描information_schema.tables 表，如果表很多，那么可能会很耗性能，后者开启将会从metrics 视图输出未加工处理的数据 。diagnostics() 具体内容见下面对diagnostics()的解释。   statement_performance_analyzer.limit  视图在没有加limit限制时，返回的最大行数 statement_performance_analyzer.view  （略） 以上参数为mysql5.7.9加入   statement_truncate_len   通过format_statement()函数返回值的最大长度   这个表非默认选项还有一个@sys.debug参数 可以手动加入 INSERT INTO sys_config (variable, value) VALUES("debug", "ON"); UPDATE sys_config SET value = "OFF" WHERE variable = "debug"; SET @sys.debug = NULL; 具体内容请参考官方文档，此处不做介绍   关于这个表有两个触发器 1.1.1 sys_config_insert_set_user触发器 如果加入新行通过insert语句，那么这个触发器会把set_by列设置为当前操作者 1.1.2 sys_config_update_set_user触发器              如果加入新行通过update语句，那么这个触发器会把set_by列设置为当前操作者   2.视图 以下部分只介绍不包含x$的视图内容   2.1 host_summary (主机概要) 有如下列： • host 监听连接过的主机 • statements 当前主机执行的语句总数 • statement_latency 语句等待时间（延迟时间） • statement_avg_latency 执行语句平均延迟时间 • table_scans 表扫描次数 • file_ios io时间总数 • file_io_latency 文件io延迟 • current_connections 当前连接数 • total_connections 总链接数 • unique_users 该主机的唯一用户数 • current_memory 当前账户分配的内存 • total_memory_allocated 该主机分配的内存总数   2.2  The host_summary_by_file_io_type •host 主机 •event_name IO事件名称 •total 该主机发生的事件 •total_latency 该主机发生IO事件总延迟时间 •max_latency 该主机IO事件中最大的延迟时间   2.3 The host_summary_by_file_io •host 主机 •ios IO事件总数 •io_latency IO总的延迟时间   2.4 The host_summary_by_stages • host 主机 • event_name stage event名称 • total stage event发生的总数 • total_latency stage event总的延迟时间 • avg_latency stage event平均延迟时间     2.5 The host_summary_by_statement_latency • host 主机 • total 这个主机的语句总数 • total_latency 这个主机总的延迟时间 • max_latency 主机最大的延迟时间 • lock_latency 等待锁的锁延迟时间 • rows_sent 该主机通过语句返回的总行数 • rows_examined 在存储引擎上通过语句返回的行数 • rows_affected 该主机通过语句影响的总行数 • full_scans 全表扫描的语句总数     2.6  The host_summary_by_statement_type • host 主机 • statement 最后的语句事件名称 • total sql语句总数 • total_latency sql语句总延迟数 • max_latency 最大的sql语句延迟数 • lock_latency 锁延迟总数 • rows_sent 语句返回的行总数 • rows_examined 通过存储引擎的sql语句的读取的总行数 • rows_affected 语句影响的总行数 • full_scans 全表扫描的语句事件总数     2.7 The innodb_buffer_stats_by_schema   这个表是通过数据库统计innodb引擎的innodb缓存 • object_schema 数据库名称 • allocated 分配给当前数据库的总的字节数 • data 分配给当前数据库的数据字节数 • pages 分配给当前数据库的总页数 • pages_hashed 分配给当前数据库的hash页数 • pages_old   分配给当前数据库的旧页数 • rows_cached   当前数据库缓存的行数   2.8 The innodb_buffer_stats_by_table 这个表是通过每个表innodb引擎的innodb缓存
• object_schema 数据库名称 • object_name 表名称 • allocated 分配给表的总字节数 • data 分配该表的数据字节数 • pages 分配给表的页数 • pages_hashed 分配给表的hash页数 • pages_old 分配给表的旧页数 • rows_cached 表的行缓存数   2.9 The innodb_lock_waits 这个表其实从视图的语句来看就是information_schema这个数据库中的innodb_locks、innodb_trx这两个表的整合，能够更清晰的显示当前实例的锁情况 • wait_started 锁等待发生的时间 • wait_age 锁已经等待了多长时间 • wait_age_secs 以秒为单位显示锁已经等待的时间（5.7.9中添加此列） • locked_table 被锁的表 • locked_index 被锁住的索引 • locked_type 锁类型 • waiting_trx_id 正在等待的事务ID • waiting_trx_started 等待事务开始的时间 • waiting_trx_age 已经等待事务多长时间 • waiting_trx_rows_locked 正在等待的事务被锁的行数量 • waiting_trx_rows_modified 正在等待行重定义的数量 • waiting_pid 正在等待事务的线程id • waiting_query 正在等待锁的查询 • waiting_lock_id 正在等待锁的ID • waiting_lock_mode 等待锁的模式 • blocking_trx_id 阻塞等待锁的事务id • blocking_pid 正在锁的线程id • blocking_query 正在锁的查询 •blocking_lock_id 正在阻塞等待锁的锁id. •blocking_lock_mode 阻塞锁模式 • blocking_trx_started 阻塞事务开始的时间 • blocking_trx_age 阻塞的事务已经执行的时间 • blocking_trx_rows_locked 阻塞事务锁住的行的数量 • blocking_trx_rows_modified 阻塞事务重定义行的数量 • sql_kill_blocking_query kill 语句杀死正在运行的阻塞事务 在mysql5.7.9中被加入 • sql_kill_blocking_connection kill 语句杀死会话中正在运行的阻塞事务 在mysql5.7.9中被加入     2.10 The io_by_thread_by_latency 这个过程主要信息是通过IO的消耗展示IO等待的时间 • user 对于当前线程来说，这个值是线程被分配的账户，对于后台线程来讲，就是线程的名称 • total IO事件的总数 • total_latency IO事件的总延迟 • min_latency 单个最小的IO事件延迟 • avg_latency 平均IO延迟 • max_latency 最大IO延迟 • thread_id 线程ID • processlist_id 对于当前线程就是此时的ID，对于后台就是null