本文共 12620 字，大约阅读时间需要 42 分钟。
官方地址：
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使用列子：
MySQL4.1 提供了information_schema 数据字典。从此可以很简单的用SQL语句来检索需要的系统元数据了。
MySQL5.5 提供了performance_schema 性能字典。 但是这个字典比较专业，一般人可能也就看看就不了了之了。
MySQL5.7 提供了 sys系统数据库。 sys数据库里面包含了一系列的存储过程、自定义函数以及视图来帮助我们快速的了解系统的元数据信息。
sys系统数据库结合了information_schema和performance_schema的相关数据，让我们更加容易的检索元数据。 现在呢，我就示范下几种场景下如何快速的使用。
第一，比如之前想要知道某个表是否存在与否，可以用以下两种方法：
A， 悲观的方法，写SQL从information_schema中拿信息：
- 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，乐观的方法，假设表存在，写一个存储过程：
- 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 ;
现在来调用：
- 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数据库里面现有的存储过程来进行调用，
- 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)
第二，获取没有使用过的索引。
- 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的查询，
- 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)
第四， 同样继续上面的，过滤出有临时表的查询，
- 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 手工去过滤了。
第五， 检索执行次数排名前五的语句，
- 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)
示例我就写这么多了，详细的去看使用手册并且自己摸索去吧。
1.表
| 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 配置选项名称
| variable | value | set_time | set_by |
| diagnostics.allow_i_s_tables | OFF | 2015-11-20 16:04:38 | |
| diagnostics.include_raw | OFF | 2015-11-20 16:04:38 | |
| statement_performance_analyzer.limit | 100 | 2015-11-20 16:04:38 | |
| statement_performance_analyzer.view | | 2015-11-20 16:04:38 | |
| statement_truncate_len | 64 | 2016-01-22 17:00:16 | |
About Me
.............................................................................................................................................
● 本文整理自网络
● 本文在itpub（）、博客园（）和个人微信公众号（）上有同步更新
● 本文itpub地址：
● 本文博客园地址：
● 本文pdf版、个人简介及小麦苗云盘地址：
● 数据库笔试面试题库及解答：
● DBA宝典今日头条号地址：
.............................................................................................................................................
● QQ群号：230161599（满）、618766405
● 微信群：可加我微信，我拉大家进群，非诚勿扰
● 联系我请加QQ好友（646634621），注明添加缘由
● 于 2017-08-01 09:00 ~ 2017-08-31 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
.............................................................................................................................................
● 小麦苗的微店：
● 小麦苗出版的数据库类丛书：
.............................................................................................................................................
使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（）及QQ群（DBA宝典），学习最实用的数据库技术。
小麦苗的微信公众号 小麦苗的DBA宝典QQ群1 小麦苗的DBA宝典QQ群2 小麦苗的微店
.............................................................................................................................................
