1. 全方位认识 sys 系统库 沃趣科技 - 罗小波 杭州沃趣科技股份有限公司 2015.11.03 Hangzhou WOQU Technology Co., Ltd.

2. 目录 01 快速入门 02 配置表 03 组成对象

3. 什么是 sys 系统库 • sys 系统库是什么？ • 由快捷查询视图、辅助存储过程和函数组成的一组 schema 级别的集合 • sys 系统库的数据从哪里来？ • 通 过 快 捷 视 图 查 询 ， 绝 大 部 分 数 据 来 自 performance_schema （如果 如 果 performance_schema 未启用，那么 sys 系统库中的大多数视图将查无数据），少部分数 据来自 information_schema

4. 环境要求 * sys 系统库支持 MySQL 5.6 或更高版本， 5.5.x 及其以下版本不支持 * 因为 sys 系统库提供了一些代替直接访问 performance_schema 的视图，所以必须启用 performance_schema 之后 sys 系统库的大部分功能才能正常使用 * 要完全访问 sys 系统库，用户必须具有以下权限： * 对所有 sys 表和视图具有 SELECT 权限 * 对所有 sys 存储过程和函数具有 EXECUTE 权限 * 对 sys_config 表具有 INSERT 、 UPDATE 权限 * 对某些特定的 sys 系统库存储过程和函数需要额外权限，如， ps_setup_save() 存储过 程，需要临时表相关的权限 * 还有 sys 系统库执行访问的对象相关的权限： * 任何被 sys 系统库访问的 performance_schema 表需要有 SELECT 权限，如果要使用 sys 系统库对 performance_schema 相关表执行更新，则需要 performance_schema 相关 表的 UPDATE 权限 * INFORMATION_SCHEMA.INNODB_BUFFER_PAGE 表的 PROCESS * 如 果 要 充 分 使 用 sys 系 统 库 的 功 能 ， 则 必 须 启 用 某 些 performance_schema 的 instruments 和 consumers

5. 初体验

6. 进度报告 使用 session 视图可以查询到语句执行的进度信息，它是直接调用 processlist 视图并过滤掉 后台线程和 command 为 Daemon 的线程（如果所以两个视图输出结果的字段完全相同），而 processlist 视 图 联 结 查 询 了 threads 、 events_waits_current 、 events_stages_current 、 events_statements_current 、 events_transactions_current 、 sys.x$memory_by_thread_by_current_bytes 、 session_connect_attrs 表， so ，需 要打开相应的 instruments 和 consumers ，否则谁没打开谁对应的信息字段列就为 NULL ， 对于 trx_state 字段为 ACTIVE 的线程， progress 可以输出百分比进度信息 ( 支持进度的事件 才会被统计进来 )

7. 目录 01 快速入门 02 配置表 03 组成对象

8. 配置表 * sys 系统库支持 MySQL 5.6 或更高版本， 5.5.x 及其以下版本不支持 * 为了减少对 sys_config 表直接读取的次数， sys 系统库中的视图、存储过程在需要使用到 这些配置选项时，会优先检查这些配置选项对应的用户自定义配置选项变量 ( 用户自定义配置 选项变量与该表中的配置选项都具有相同的名称，例如：表中的 diagnostics.include_raw 选 项，对应的自定义配置选项变量是 @sys.diagnostics.include_raw)sys.diagnostics.include_raw) 。如果用户定义的配置选 项变量存在于当前会话作用域中并且是非空的，那么 sys 系统库中的函数、存储过程将优先使 用该配置选项变量值。否则，该 sys 系统库函数和存储过程将使用 sys_config 表中的配置选 项值 ( 从表中读取配置选项值之后，会将 sys_config 表中的配置选项时同时更新到用户自定义 配置选项变量中，以便在同一会话后续对该值的引用时使用变量值，而不必再次从 sys_config 表中读取 )

9. 配置表 * sys_config 表中的选项和相应的用户定义的配置选项变量相关描述如下： * diagnostics.allow_i_s_tables ， @sys.diagnostics.include_raw)sys.diagnostics.allow_i_s_tables ：如果此选项为 ON ，则 diagnostics() 存储过程在调用时会扫描 INFORMATION_SCHEMA.TABLES 表找到所有的基表与 STATISTICS 表执行联结查询， 扫描每个表的统计信息。如果基表非常多，该操作可能比较昂贵。默认为 OFF 。此选项在 MySQL 5.7.9 中新增。 * diagnostics.include_raw ， @sys.diagnostics.include_raw)sys.diagnostics.include_raw ：如果此选项为 ON ，则 diagnostics() 存储过程 的输出信息中会包括 metrics 视图中的原始输出信息（如果该存储过程中会调用 metrics 视图）。默认为 OFF 。此选项 在 MySQL 5.7.9 中新增 * ps_thread_trx_info.max_length ， @sys.diagnostics.include_raw)sys.ps_thread_trx_info.max_length ： 由 ps_thread_trx_info() 函 数 生 成的 JSON 输出结果的最大长度。默认值为 65535 字节。此选项在 MySQL 5.7.9 中新增 * statement_performance_analyzer.limit ， @sys.diagnostics.include_raw)sys.statement_performance_analyzer.limit ：不具有内置限制的 视图返回的最大行数。（如果例如， statements_with_runtimes_in_95th_percentile 视图具有内置限制，即只返回平均 执行时间为占总执行时间分布的 95 百分位数的语句）。默认值为 100 。此选项在 MySQL 5.7.9 中新增

10. 配置表 *statement_performance_analyzer.view ， @sys.diagnostics.include_raw)sys.statement_performance_analyzer.view ： 给 statement_performance_analyzer() 存 储 过 程 当 作 入 参 使 用 的 自 定 义 查 询 或 视 图 名 称 （如果 statement_performance_analyzer() 存储过程由 diagnostics() 存储过程内部调用）。如果该选项值包含空 格，则将其值解释为查询语句。否则解释为视图名称，且这个视图必须提前创建好的用于查询 performance_schema.events_statements_summary_by_digest 表 的 视 图 。 如 果 statement_performance_analyzer.limit 配置选项值大于 0 ，则 statement_performance_analyzer.view 配置选 项 指 定 的 查 询 语 句 或 视 图 中 不 能 有 任 何 LIMIT 子 句 ( 因 为 statement_performance_analyzer.limit 选 项 在 statement_performance_analyzer() 存储过程存储过程中是作为一个条件判断值决定是否要添加一个 LIMIT 子 句，如果你再自行添加一个 LIMIT 会导致语法错误 ) 。 statement_performance_analyzer.view 配置选项默认值 为 NULL 。此选项在 MySQL 5.7.9 中新增 * statement_truncate_len ， @sys.diagnostics.include_raw)sys.statement_truncate_len ：控制 format_statement() 函数返回的语句文本的 最大长度。超过该长度的语句文本会被截断，只保留该配置选项定义的长度文本。默认值为 64 字节 * 其他选项可以被添加到 sys_config 表中。例如：如果存在 debug 配置选项且不为 null 值，则 diagnostics() 和 execute_prepared_stmt() 存储过程调用时会执行检查并做相应的判断，但默认情况下，此选项在 sys_config 表 中 不 存 在 ， 因 为 debug 输 出 通 常 只 能 临 时 启 用 ， 通 过 会 话 级 别 设 置 自 定 义 配 置 选 项 变 量 实 现 ， 如 ： set @sys.diagnostics.include_raw)sys.debug='ON'; * 注意：如果用户在会话中设置了自定义配置选项变量值，然后再更新了 sys_config 表中相同名称的配置选项， 则对于当前会话， sys_config 表中的配置选项值不生效（如果除非设置自定义配置选项变量值为 NULL ），只对于新 的会话且不存在自定义配置选项变量或者自定义配置选项值为 NULL 生效

11. 配置表 • PS ： * 对 sys_config 表 的 insert 和 update 操 作 会 触 发 sys_config_insert_set_user 和 sys_config_update_set_user 触发器，而该触发器在 5.7.x 版本中新增了一个用户 mysql.sys ，且这俩触发器定 义时指定了 DEFINER=`mysql.sys`@sys.diagnostics.include_raw)`localhost` （如果表示该触发器只能用 mysql.sys 用户调用） * mysql.sys 用户初始化默认对表 sys.sys_config 表只有 select 权限，无法调用 sys_config_insert_set_user 和 sys_config_update_set_user 触发 器完成更新 set_by 字段为当前操作用户名，要 实现 这个功能， 针 对 sys.sys_config 表还需要添加 insert 和 update 权限给 mysql.sys 用户

12. 目录 01 快速入门 02 配置表 03 组成对象

13. 组成对象预览 杭州沃趣科技股份有限公司 2015.11.03 Hangzhou WOQU Technology Co., Ltd.

14. 组成对象 1 个 innodb 存储引擎配置表 2 个配置表触发器 sys_config sys 系统库 组成对象 100 个查询视图 1 、对 MySQL 性能、统计提供便捷查询 ，主要数据来自 performance_schema ，少部分数据来 自 information_schema 2 、它们大多数是成对出现，名称相同， 其中一个带 x$ 前缀，其中一个不带 x$ 前 缀（如果带前缀的主要提供给程序访问，不带 前缀的主要提供可读格式给人工查询使 用） sys_config_insert_set_us er sys_config_update_set_u ser 26 个存储过程和 22 个函数 1 、存储过程主要是用于便捷地修改与查 询 performance_schema 系统库下的事 件配置表 2 、函数主要是用于单位转换、确定某个 事件配置项是否启用等

15. sys 系统库组成对象

16. 组成对象列表 杭州沃趣科技股份有限公司 2015.11.03 Hangzhou WOQU Technology Co., Ltd.

17. 视图列表

18. 存储过程列表

19. 函数列表

20. sys 系 统 库 应 用 场 景 荟 萃 杭州沃趣科技股份有限公司 2015.11.03 Hangzhou WOQU Technology Co., Ltd.

21. 查看慢 SQL 慢在哪里

22. 查看是否有事务锁和表锁

23. 查看 InnoDB buffer pool 中的热点数据

24. 查看冗余索引和未使用索引

25. 查看使用了全表扫描、文件排序、临时表的语句

26. sys 系统库应用案例 杭州沃趣科技股份有限公司 2015.11.03 Hangzhou WOQU Technology Co., Ltd.

27. 找出主库诡异切换原因 • 由于篇幅较多，不便添加到 PPT 中，请大家移驾如下链接： • http://5d096a11.wiz03.com/share/s/1t2mEh0a- kl_2c2NZ33kSiac21mPNC14r4zO25ofjz3om8JQ

28. 提问 & 答疑 DBGeeK 社群微信公众号 杭州沃趣科技股份有限公司 微信二维码 DBGeeK 社群 QQ 号 :516293316 Hangzhou WOQU Technology Co., Ltd. 28 Hangzhou WOQU Technology Co., Ltd.