mysql初窥sys库

sys库出现在5.6及以上的版本中，由快捷查询试图、辅助存储过程和函数组成的一组schema级别的集合，绝大部分的数据来至于performance_schema(如果未开启performance_schema，则sys系统库中的大多数试图将查无数据)，少数来至于information_schema.sys中的试图大多数都是成对出现的，名称相同，其中一个带x$前缀(供程序访问)，一个不带(人工查询)。

1.表(1个)

sys_config

2.试图(100个)

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 .host_summary_by_file_io_type

•host
	主机
•event_name
	IO事件名称
•total
	该主机发生的事件
•total_latency
	该主机发生IO事件总延迟时间
•max_latency
	该主机IO事件中最大的延迟时间

3.host_summary_by_file_io

•host
	主机
•ios
	IO事件总数
•io_latency
	IO总的延迟时间

4.host_summary_by_stages

• host
	主机
• event_name
	stage even 名称
• total
	stage event发生的总数
• total_latency
	stage event 总的延迟时间
• avg_latency
	stage event 平均延迟时间

5.host_summary_by_statement_latency

• host
	主机
• total
	这个主机的语句总数
• total_latency
	这个主机总的延迟时间
• max_latency
	主机最大的延迟时间
• lock_latency
	等待锁的锁延迟时间
• rows_sent
	该主机通过语句返回的总行数
• rows_examined
	在存储引擎上通过语句返回的行数
• rows_affected
	该主机通过语句影响的总行数
• full_scans
	全表扫描的语句总数

6.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
	全表扫描的语句事件总数

7.innodb_buffer_stats_by_schema

这个表是通过数据库统计innodb引擎的innodb缓存
• object_schema
	数据库名称
• allocated
	分配给当前数据库的总的字节数
• data
	分配给当前数据库的数据字节数
• pages
	分配给当前数据库的总页数
• pages_hashed
	分配给当前数据库的hash页数
• pages_old
	分配给当前数据库的旧页数
• rows_cached
	当前数据库缓存的行数

8.innodb_buffer_stats_by_table

这个表是通过每个表innodb引擎的innodb缓存
• object_schema
	数据库名称
• object_name
	表名称
• allocated
	分配给表的总字节数
• data
	分配该表的数据字节数
• pages
	分配给表的页数
• pages_hashed
	分配给表的hash页数
• pages_old
	分配给表的旧页数
• rows_cached
	表的行缓存数

9.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中被加入

10.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

11.Io_global_by_file_by_bytes

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

12.user_summary

•  User
	客户端连接过来的用户名。如果是NULL，表示内部进程
•  Statements
	该用户执行了多少SQL
•  Statement_latency
	该用户执行SQL的总延迟时间
•  Statement_avg_latency
	该用户执行SQL的平均延迟时间
•  Table_scans
	该用户执行SQL时扫描表的次数
•  File_ios
	该用户请求操作用掉的IO
•  File_io_latency
	该用户请求操作的IO总延迟时间
•  Current_connections
	该用户当前的连接数
•  Total_connections
	该用户总的连接数
•  Unique_hosts
	该用户从几个唯一的机器连接过来
•  Current_memory
	该用户当前占用的内存
•  Total_memory_allocated
	该用户总共申请到的内存（累加值）

13.memory_global_total

•  Total_allocated
	server总共分配出去的内存（应该是server层）

14.memory_by_thread_by_current_bytes

•  Thread_id
	内部线程ID可以和session中的thd_id关联
•  User
	这个线程是哪个用户创建的
•  Current_count_used
	当前使用的内存块还没有释放
•  Current_allocated
	当前分配的内存大小（字节）而且没有被释放出来
•  Current_avg_alloc
	平均分配的blocks
•  Current_max_alloc
	当前线程分配的最多内存
•  Total_allocated
	当前连总共分配的内存大小

15.statement_analysis

•  Query
	归一化的SQL样子
•  Db
	在哪个DB中执行。NULL表示在任何DB
•  Full_scan
	全表扫描的次数
•  Exec_count
	该SQL执行的总次数
•  Err_count
	发生错误的次数
•  Warn_count
	发生警告的次数
•  Total_latency
	总共发生延迟的实际
•  Max_latency
	最大延迟时间
•  Avg_latency
	平均延迟时间
•  Lock_latency
	因锁等待占用的总时间
•  Rows_sent
	执行该SQL返回的总行数
•  Rows_sent_avg
	执行该SQL平均返回的行数
•  Tmp_tables
	该SQL形成内存临时表的总次数
•  Tmp_disk_tables
	该SQL形成文件临时表的总次数
•  Rows_sorted
	该SQL总共排序的行数
•  Sort_merge_passes
	用于排序中合并的总次数
•  Digest
	该语句的hash值
•  First_screen
	该SQL最早出现的时间
•  Last_screen
	该SQL最近出现的时间

16.processlist && session

session和processlist视图基本一样，只是把后台线程过滤掉。
•  Thd_id
	内部线程ID
•  Conn_id
	连接的ID，对应show processlist中的ID列
•  User
	该线程创建的用户名
•  Db
	连接的DB，如果NULL表示后台线程
•  Command
	Client发起命令的类型提示
•  State
	命令的状态
•  Time
	基于上面的state停留的实际
•  Current_statement
	该线程执行的语句
•  Statement_latency
	语句运行总共占用时间
•  Progress
	该语句运行完成的百分比
•  Lock_latency
	该语句用于锁等待的时间
•  Rows_examined
	该语句扫描的次数
•  Rows_send
	该语句返回的行数
•  Rows_affected
	该语句影响到的行数（写入语句）
•  Tmp_tables
	形成内存临时表的次数
•  Tmp_disk_tables
	形成磁盘临时表的次数
•  Full_scan
	全表扫描的次数
•  Trx_state
	当前事务的状态
•  Pid
	对应到系统里的pid
•  Program_name
	连接进来的标识名

18.innodb_buffer_stats_by_schema && innodb_buffer_stats_by_table

innodb_buffer_stats_by_table和innodb_buffer_stats_by_schema基本一致。只是比上面多了个object_name指定表名。

•  Object_schema
	库名
•  Allocated
	基于库分配的buffer pool大小
•  Data
	基于schema实际缓存的数据大小
•  Pages
	当前schema缓存的page数
•  Pages_hashed
	Buffer  pool中进行hash 索引的page
•  Pages_old
	Buffer pool中的旧页，可能被置换出去
•  Rows_cached
	Buffer pool中以行为单位的缓存

触发器(2个)

sys_config_insert_set_user && sys_config_update_set_user
对sys_config表的insert和update操作会触发sys_config_insert_set_user和sys_config_update_set_user触发器，而该触发器在5.7.x版本中新增了一个用户mysql.sys.

存储过程(26个)

用于便捷地修改与查询performance_schema系统库下的事件配置表。

函数(22个)

用于单位转换、确定某个事件配置项是否启用等。

日常用法

1.查询当前正在执行的语句进度信息
	select * from session where conn_id!=connection_id() and trx_state='ACTIVE';

2.查询慢SQL慢在什么地方
	select * from session where conn_id!=connection_id() and command='query';

3.查看InnoDB Buffer pool中的热点数据
	select * from innodb_buffer_stats_by_schema;
	select * from innodb_buffer_stats_by_table;

4.查看冗余索引
	select * from schema_redundant_indexes;

5.查看未使用的索引
	select * from schema_unused_indexes;
	
6.查看全表扫描
	select * from statements_with_full_table_scans;

7.查看文件排序
	select * from statements_with_sorting;

8.查看使用临时表
	select * from statements_with_temp_tables;

9.查看配置表
	select * from sys_config;

参考资料
https://dev.mysql.com/doc/refman/5.7/en/sys-schema-prerequisites.html
https://www.cnblogs.com/jiangwenju/p/5157333.html
https://blog.csdn.net/yanzongshuai/article/details/73441158