**Mysql5.7新特性之----- 浅谈Sys库**

**Mysql5.7新特性之-----

    浅谈Sys库**

随着mysql5.7的逐渐升温，人们对其也越来越感兴趣，我最近又重新学习了一下5.7版本的 SYS库。

**SYS库介绍
什么是sys库？**
MySQL 5.7开始引入了一个新的sys schema，sys是一个MySQL自带的系统库，在安装MySQL 5.7以后的版本，使用mysqld进行初始化时，会自动创建sys库，
sys库里面的表、视图、函数、存储过程可以使我们更方便、快捷的了解到MySQL的一些信息，比如哪些语句使用了临时表、哪个SQL没有使用索引、哪个schema中有冗余索引、查找使用全表扫描的SQL、查找用户占用的IO等.
Sys库的数据来源：
sys库里这些视图中的数据，都是从information_schema里面获得的，目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。
Sys库下有两种表
字母开头： 适合人阅读，显示是格式化的数
x$开头 ： 适合工具采集数据，原始类数据
这些信息都可以通过show tables;查看或者在information_schema中查看：
select table_name,table_type,engine from
information_schema.tables where
table_schema='sys' order by table_name;

每类表大概介绍
sys_开头是库里的配置表：
sys_config用于sys schema库的配置

视图：
host : 以IP分组相关的统计信息
innodb : innodb buffer 相关信息
io : 数据内不同维度展的IO相关的信息
memory : 以IP，连接，用户，分配的类型分组及总的占用显示内存的使用
metrics : DB的内部的统计值
processlist : 线程相关的信息(包含内部线程及用户连接）
ps_ : 没有工具统计的一些变量（没看出来存在的价值）
schema : 表结构相关的信息，例如： 自增，索引， 表里的每个字段类型，等待的锁等等
session : 用户连接相关的信息
statement : 基于语句的统计信息（重点）
statements_ : 出错的语句，进行全表扫描， 运行时间超长，排序相等（重点）
user_ : 和host_开头的相似，只是以用户分组统计
wait : 等待事件，比较专业。
waits : 以IP，用户分组统计出来的一些延迟事件，有一定的参考价值。

**那么sys库到底有哪些功能呢？我们可以具体利用它什么地方呢？
以下是我整理的 部分的功能：**

1. 谁使用了最多的资源？ 基于IP或是用户？
   对于该问题可以从host, user, io三个方面去了解，大概谁的请求最多。对于使用资源问题可以直接从下面四个视图里有一个大概的了解。

Select*from host_summary limit 1G
Select*from io_global_by_file_by_bytes limit 1G
Select*from user_summary limit 1G
Select*from memory_global_total;

注意内存部分，不包括innodbbuffer pool。只是server 层申请的内存

1. 大部分连接来自哪里及发送的SQL情况
   查看当前连接情况：

select host, current_connections,statements from host_summary;
查看当前正在执行的SQL：
select conn_id, user, current_statement, last_statement from session;

1. 机器执行最多的SQL语句是什么样?
   例如查一下系统里执行最多的TOP 10 SQL。

SQL如下：
select * from statement_analysis order byexec_count desc limit 10G;

1. 哪张表的IO最多？哪张表访问次数最多
2. • from io_global_by_file_by_byteslimit 10;(参见上面表格说明)

哪张表访问次数最多，可以参考上面先查询执行最多的语句，然后查找对应的表。
SQL如下：
select * from statement_analysis order byexec_count desc limit 10G;

1. 哪些语句延迟比较严重
   statement_analysis中avg_latency的最高的。(参考上面写法)

SQL语句：
select * from statement_analysis order byavg_latency desc limit 10;

1. 哪些SQL语句使用了磁盘临时表
   利用statement_analysis 中tmp_tables ，tmp_disk_tables 进行计算。(参考上面写法)

参考SQL：
select db, query, tmp_tables,tmp_disk_tables from statement_analysiswhere tmp_tables>0 or tmp_disk_tables >0 order by(tmp_tables+tmp_disk_tables) desc limit 20；

1. 哪张表占用了最多的buffer pool
   例如查询在buffer pool中占用前10的表。

SQL如下：
select * from innodb_buffer_stats_by_tableorder by pages desc limit 10;

1. 每个库占用多少buffer pool
   SQL如下：

select * frominnodb_buffer_stats_by_schema;

1. 每个连接分配多少内存
   利用session表和memory_by_thread_by_current_bytes分配表进行关联查询。

SQL如下：
select b.user, current_count_used,current_allocated, current_avg_alloc, current_max_alloc,total_allocated,current_statement from memory_by_thread_by_current_bytes a,session b where a.thread_id = b.thd_id;

1. MySQL内部现在有多个线程在运行
   MySQL内部的线程类型及数量：

select user, count(*) from processlistgroup by user;

当然要理解上面的问题与方法还需要对一些视图的字段理解其中的意思
例如：

host_summary
字段名 意义
host 从哪个服务器上连过来。如果是NULL，表示内部的进程
Statements 这台服务器共执行了多少语句（从启动开始统计？）
Statement_latency 这台服务器发来等待语句执行的时间
Statement_avg_latency 该服务器等待语句执行的平均时间
Table_scans 该服务器扫描表的次数（非全表）
File_io 该服务器IO事件请求的次数
File_io_latency 该服务器请求等待IO的时间
Current_connections 该服务器当前的连接数
Total_connections 该服务器总连接DB共连接多少次
Unique_user 该服务器上有几个不同用户名的账户连接过来
Current_memory 该服务器上当前连接等占用的内存
Total_memory_allocated 该服务器上的请求总共使用的内存

Io_global_by_file_by_bytes
字段名 意义
File 被操作的文件名
Count_read 总共有多少次读
Total_read 总共读了多少字节
Avg_read 平均每次读多少字节
Count_write 总共多少次写
Total_written 总共写了多少字节
Avg_write 平均每次写的字节大学
Total 读和写总共的IO大学
Write_pct 写占total里的百分比

当然，要全面理解SYS库的所有视图的含义 并不是一个简短的工程，我提供给大家的只是一个理解的思路，与我个人的一些见解，如果有错的地方，希望大家可以指出，互相学习一下。