知数堂-罗小波-全方位认识 sys 系统库

2020-02-23 324浏览

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.