转载

MySQL 5.7 SYS SCHEMA

	 MySQL 5.7 SYS SCHEMA 

官方地址：https://dev.mysql.com/doc/refman/5.7/en/sys-schema.html

1、performance schema：介绍

在MySQL5.7中，performance schema有很大改进，包括引入大量新加入的监控项、降低占用空间和负载，以及通过新的sys schema机制显著提升易用性。在监控方面，performance schema有如下功能：

①：元数据锁：

对于了解会话之间元数据锁的依赖关系至关重要。从MySQL5.7.3开始，就可以通过metadata_locks表来了解元数据锁的相关信息;

--哪些会话拥有哪些元数据锁

--哪些会话正在等待元数据锁

--哪些请求由于死锁被杀掉，或者锁等待超时而被放弃

②：进度跟踪：

跟踪长时间操作的进度(比如alter table)，从MySQL5.7.7开始，performance schema自动提供了语句进度信息。我们可以通过events_stages_current表来查看当前事件的进度信息;

③：事务：

监控服务层和存储引擎层事务的全部方面。从MySQL5.7.3开始，新增了 events_transactions_current表，可以通过setup_consumers、setup_instruments表打开事务监控，通过该表查询到当前事务的状态。如果线上数据库遇到undo log大量增长、数据库性能急剧下降的情况，可以通过该表查询当前是否存在处于未提交状态的事务。如果发现的确有大量事务的state处于active，这时可以确定数据库有大量的事务未提交;

④：内存使用：

提供内存使用信息统计，有利于了解和调整服务器的内存消耗。从MySQL5.7.2开始，performance schema新增内存有关的统计信息，分别从账户、访问主机、线程、用户及事件的角度统计了内存的使用过程;

⑤：存储程序：

存储过程、存储方法、事件调度器和表触发器的检测器。在MySQL5.7中的setup_objects表中，新增了event、function、procedure、trigger的检测器。performance schema用于检测该表中匹配object_schema和object_name的对象;

2、sys schema介绍：

在MySQL5.7中新增的sys schema。是由一系列对象(视图、存储过程、存储方法、表和触发器)组成的schema，它本身不采集和存储什么信息，而是将performance_schema 和 information_schema中的数据以更容易理解的方式总结出来归纳为“视图”。

---sys schema可用于典型的调优和诊断用例，这些对象包括如下三个：

①：将性能模式数据汇总到更容易理解的视图;

②：诸如性能模式配置和生成诊断报告等操作的存储过程

③：用于查询性能模式配置并提供格式化服务的存储函数

---sys schema在查询中的功能，可以查看数据库服务资源的使用情况?哪些主机对数据库服务器的访问量最大?实例上的内存使用情况?

3、sys schema里面的表的分类：

①：主机相关信息：

以host_summary开头的视图，主要汇总了IO延迟的信息，从主机、文件事件类型、语句类型等角度展示文件IO的信息;

②：innodb相关信息：

以innodb开头的视图，汇总了innodb buffer page信息和事务等待innodb锁信息;

③：IO使用情况：

以IO开头的视图，总结了IO使用者的信息，包括等待IO的情况、IO使用量情况，从各个角度分组展示;

④：内存使用情况：

以memory开头的视图，从主机、线程、用户、事件角度展示内存使用情况;

⑤：连接与会话信息：

其中，processlist 和 session相关的视图，总结了会话相关的信息;

⑥：表相关信息：

以schema_table开头的视图，从全表扫描、innodb缓冲池等方面展示了表统计信息;

⑦：索引信息：

其中包含index的视图，统计了索引使用的情况，以及重复索引和未使用的索引情况;

⑧：语句相关信息：

以statement开头的视图，统计的规范化后的语句使用情况，包括错误数、警告数、执行全表扫描的、使用临时表、执行排序等信息;

⑨：用户相关信息：

以user开头的视图，统计了用户使用的文件IO，执行的语句统计信息等;

⑨：等待事件相关信息

：

以wait开头的视图，从主机和事件角度展示等待类事件的延迟情况;

4、sys schema使用列子：

MySQL 5.7 SYS SCHEMA

在说明系统数据库之前，先来看下MySQL在数据字典方面的演变历史：
MySQL4.1 提供了information_schema 数据字典。从此可以很简单的用SQL语句来检索需要的系统元数据了。
MySQL5.5 提供了performance_schema 性能字典。但是这个字典比较专业，一般人可能也就看看就不了了之了。
MySQL5.7 提供了 sys系统数据库。 sys数据库里面包含了一系列的存储过程、自定义函数以及视图来帮助我们快速的了解系统的元数据信息。

sys系统数据库结合了information_schema和performance_schema的相关数据，让我们更加容易的检索元数据。现在呢，我就示范下几种场景下如何快速的使用。

第一，
比如之前想要知道某个表是否存在与否，可以用以下两种方法：
A，悲观的方法，写SQL从information_schema中拿信息：

		[sql] view plain copy
		
		mysql> SELECT IF(COUNT(*) = 0,'Not exists!','Exists!') AS 'result' FROM information_schema.tables WHERE table_schema = 'new_feature' AND table_name = 't1';   
	
		+-------------+   
	
		| result      |   
	
		+-------------+   
	
		| Not exists! |   
	
		+-------------+   
	
		1 row in set (0.00 sec)

B，乐观的方法，假设表存在，写一个存储过程：

			[sql] view plain copy
			
			DELIMITER $$   
		
			USE `new_feature`$$   
		
			DROP PROCEDURE IF EXISTS `sp_table_exists`$$   
		
			CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_table_exists`(   
		
			    IN db_name VARCHAR(64),   
		
			    IN tb_name VARCHAR(64),   
		
			    OUT is_exists VARCHAR(60)   
		
			    )   
		
			BEGIN   
		
			      DECLARE no_such_table CONDITION FOR 1146;   
		
			      DECLARE EXIT HANDLER FOR no_such_table   
		
			      BEGIN   
		
			        SET is_exists = 'Not exists!';   
		
			      END;   
		
			      SET @stmt = CONCAT('select 1 from ',db_name,'.',tb_name);   
		
			      PREPARE s1 FROM @stmt;   
		
			      EXECUTE s1;   
		
			      DEALLOCATE PREPARE s1;   
		
			      SET is_exists = 'Exists!';   
		
			    END$$   
		
			DELIMITER ;

现在来调用：

		[sql] view plain copy
		
		mysql> call sp_table_exists('new_feature','t1',@result);   
	
		Query OK, 0 rows affected (0.00 sec)   
	
		mysql> select @result;   
	
		+-------------+   
	
		| @result     |   
	
		+-------------+   
	
		| Not exists! |   
	
		+-------------+   
	
		1 row in set (0.00 sec)

现在我们直接用sys数据库里面现有的存储过程来进行调用，

			[sql] view plain copy
			
			mysql> CALL table_exists('new_feature','t1',@v_is_exists);   
		
			Query OK, 0 rows affected (0.00 sec)   
		
			mysql> SELECT IF(@v_is_exists = '','Not exists!',@v_is_exists) AS 'result';   
		
			+-------------+   
		
			| result      |   
		
			+-------------+   
		
			| Not exists! |   
		
			+-------------+   
		
			1 row in set (0.00 sec)

第二，获取没有使用过的索引。

		[sql] view plain copy
		
		mysql> SELECT * FROM schema_unused_indexes;   
	
		+---------------+-------------+--------------+   
	
		| object_schema | object_name | index_name   |   
	
		+---------------+-------------+--------------+   
	
		| new_feature   | t1          | idx_log_time |   
	
		| new_feature   | t1          | idx_rank2    |   
	
		+---------------+-------------+--------------+   
	
		2 rows in set (0.00 sec)

第三，检索指定数据库下面的表扫描信息，过滤出执行次数大于10的查询，

			[sql] view plain copy
			
			mysql> SELECT * FROM statement_analysis WHERE db='new_feature' AND full_scan = '*'  AND exec_count > 10/G   
		
			*************************** 1. row ***************************   
		
			            query: SHOW STATUS    
		
			               db: new_feature   
		
			        full_scan: *   
		
			       exec_count: 26   
		
			        err_count: 0   
		
			       warn_count: 0   
		
			    total_latency: 74.68 ms   
		
			      max_latency: 3.86 ms   
		
			      avg_latency: 2.87 ms   
		
			     lock_latency: 4.50 ms   
		
			        rows_sent: 9594   
		
			    rows_sent_avg: 369   
		
			    rows_examined: 9594   
		
			rows_examined_avg: 369   
		
			    rows_affected: 0   
		
			rows_affected_avg: 0   
		
			       tmp_tables: 0   
		
			  tmp_disk_tables: 0   
		
			      rows_sorted: 0   
		
			sort_merge_passes: 0   
		
			           digest: 475fa3ad9d4a846cfa96441050fc9787   
		
			       first_seen: 2015-11-16 10:51:17   
		
			        last_seen: 2015-11-16 11:28:13   
		
			*************************** 2. row ***************************   
		
			            query: SELECT `state` , `round` ( SUM ... uration (summed) in sec` DESC    
		
			               db: new_feature   
		
			        full_scan: *   
		
			       exec_count: 12   
		
			        err_count: 0   
		
			       warn_count: 12   
		
			    total_latency: 16.43 ms   
		
			      max_latency: 2.39 ms   
		
			      avg_latency: 1.37 ms   
		
			     lock_latency: 3.54 ms   
		
			        rows_sent: 140   
		
			    rows_sent_avg: 12   
		
			    rows_examined: 852   
		
			rows_examined_avg: 71   
		
			    rows_affected: 0   
		
			rows_affected_avg: 0   
		
			       tmp_tables: 24   
		
			  tmp_disk_tables: 0   
		
			      rows_sorted: 140   
		
			sort_merge_passes: 0   
		
			           digest: 538e506ee0075e040b076f810ccb5f5c   
		
			       first_seen: 2015-11-16 10:51:17   
		
			        last_seen: 2015-11-16 11:28:13   
		
			2 rows in set (0.01 sec)

第四，同样继续上面的，过滤出有临时表的查询，

		[sql] view plain copy
		
		mysql> SELECT * FROM statement_analysis WHERE db='new_feature' AND tmp_tables > 0 ORDER BY tmp_tables DESC LIMIT 1/G   
	
		*************************** 1. row ***************************   
	
		            query: SELECT `performance_schema` .  ... name` . `SUM_TIMER_WAIT` DESC    
	
		               db: new_feature   
	
		        full_scan: *   
	
		       exec_count: 2   
	
		        err_count: 0   
	
		       warn_count: 0   
	
		    total_latency: 87.96 ms   
	
		      max_latency: 59.50 ms   
	
		      avg_latency: 43.98 ms   
	
		     lock_latency: 548.00 us   
	
		        rows_sent: 101   
	
		    rows_sent_avg: 51   
	
		    rows_examined: 201   
	
		rows_examined_avg: 101   
	
		    rows_affected: 0   
	
		rows_affected_avg: 0   
	
		       tmp_tables: 332   
	
		  tmp_disk_tables: 15   
	
		      rows_sorted: 0   
	
		sort_merge_passes: 0   
	
		           digest: ff9bdfb7cf3f44b2da4c52dcde7a7352   
	
		       first_seen: 2015-11-16 10:24:42   
	
		        last_seen: 2015-11-16 10:24:42   
	
		1 row in set (0.01 sec)

可以看到上面查询详细的详细，再也不用执行show status 手工去过滤了。

第五，检索执行次数排名前五的语句，

			[sql] view plain copy
			
			mysql> SELECT statement,total FROM user_summary_by_statement_type WHERE `user`='root' ORDER BY total DESC LIMIT 5;   
		
			+-------------------+-------+   
		
			| statement         | total |   
		
			+-------------------+-------+   
		
			| jump_if_not       | 17635 |   
		
			| freturn           |  3120 |   
		
			| show_create_table |   289 |   
		
			| Field List        |   202 |   
		
			| set_option        |   190 |   
		
			+-------------------+-------+   
		
			5 rows in set (0.01 sec)

示例我就写这么多了，详细的去看使用手册并且自己摸索去吧。

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

About Me

.............................................................................................................................................

● 本文整理自网络

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号：230161599（满）、618766405

● 微信群：可加我微信，我拉大家进群，非诚勿扰

● 联系我请加QQ好友（646634621），注明添加缘由

● 于 2017-08-01 09:00 ~ 2017-08-31 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

.............................................................................................................................................

● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。

小麦苗的微信公众号小麦苗的DBA宝典QQ群1 小麦苗的DBA宝典QQ群2 小麦苗的微店

.............................................................................................................................................