sys schema详解

sys schema详解

1、初识sys schema

1.1. sys schema使用基础环境

1.2. sys schema 初体验

1.3. sys schema 进度报告

2、 sys schema 对象使用详解

• sys schema包括表、触发器、视图、存储过程和函数等对象，下文将对这些对象进行详细讲解
• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-schema-views.html

2.1. sys schema目录

2.2. sys schema配置

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-schema-tables.html

2.2.1. sys_config表

2.2.2. sys_config_insert_set_user触发器

2.2.3. sys_config_update_set_user触发器

2.3. sys schema 视图

2.3.1. host_summary,x$ host_summary

2.3.2. host_summary_by_file_io,x$host_summary_by_file_io

2.3.3. host_summary_by_file_io_type,x$host_summary_by_file_io_type

2.3.4. host_summary_by_stages,x$host_summary_by_stages

2.3.5. host_summary_by_statement_latency,x$host_summary_by_statement_latency

2.3.6. host_summary_by_statement_type,x$host_summary_by_statement_type

2.3.7. innodb_buffer_stats_by_schema,x$innodb_buffer_stats_by_schema

2.3.8. innodb_buffer_stats_by_table,x$innodb_buffer_stats_by_table

2.3.9. innodb_lock_waits,x$innodb_lock_waits

2.3.10. io_by_thread_by_latency,x$io_by_thread_by_latency

2.3.11. io_global_by_file_by_bytes,x$io_global_by_file_by_bytes

2.3.12. io_global_by_file_by_latency,x$io_global_by_file_by_latency

2.3.13. io_global_by_wait_by_bytes,x$io_global_by_wait_by_bytes

2.3.14. io_global_by_wait_by_latency,x$io_global_by_wait_by_latency

2.3.15. latest_file_io,x$latest_file_io

2.3.16. memory_by_host_by_current_bytes,x$memory_by_host_by_current_bytes

2.3.17. memory_by_thread_by_current_bytes,x$memory_by_thread_by_current_bytes

2.3.18. memory_by_user_by_current_bytes,x$memory_by_user_by_current_bytes

2.3.19. memory_global_by_current_bytes,x$memory_global_by_current_bytes

2.3.20. memory_global_total,x$memory_global_total

2.3.21. metrics

2.3.22. processlist,x$processlist

2.3.23. ps_check_lost_instrumentation

2.3.24. schema_auto_increment_columns

2.3.25. schema_index_statistics,x$schema_index_statistics

2.3.26. schema_object_overview

2.3.27. schema_redundant_indexes

2.3.28. schema_table_lock_waits,x$schema_table_lock_waits

2.3.29. schema_table_statistics,x$schema_table_statistics

2.3.30. schema_table_statistics_with_buffer,x$schema_table_statistics_with_buffer

2.3.31. schema_tables_with_full_table_scans,x$schema_tables_with_full_table_scans

2.3.32. schema_unused_indexes

2.3.33. session,x$session

2.3.34. session_ssl_status

2.3.35. statement_analysis,x$statement_analysis

2.3.36. statements_with_errors_or_warnings,x$statements_with_errors_or_warnings

2.3.37. statements_with_full_table_scans,x$statements_with_full_table_scans

2.3.38. statements_with_runtimes_in_95th_percentile,x$statements_with_runtimes_in_95th_percentile

2.3.39. statements_with_sorting,x$statements_with_sorting

2.3.40. statements_with_temp_tables,x$statements_with_temp_tables

2.3.41. user_summary,x$user_summary

2.3.42. user_summary_by_file_io,x$user_summary_by_file_io

2.3.43. user_summary_by_file_io_type,x$user_summary_by_file_io_type

2.3.44. user_summary_by_stages,x$user_summary_by_stages

2.3.45. user_summary_by_statement_latency,x$user_summary_by_statement_latency

2.3.46. user_summary_by_statement_type,x$user_summary_by_statement_type

2.3.47. version

2.3.48. wait_classes_global_by_avg_latency,x$wait_classes_global_by_avg_latency

2.3.49. wait_classes_global_by_latency,x$wait_classes_global_by_latency

2.3.50. waits_by_host_by_latency,x$waits_by_host_by_latency

2.3.51. waits_by_user_by_latency,x$waits_by_user_by_latency

2.3.52. waits_global_by_latency,x$waits_global_by_latency

2.3.53. x$ps_digest_95th_percentile_by_avg_us

2.3.54. x$ps_digest_avg_latency_distribution

2.3.55. x$ps_schema_table_statistics_io

2.3.56. x$schema_flattened_keys

2.4. sys schema 存储过程

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-schema-procedures.html

2.4.1. create_synonym_db()

2.4.2. diagnostics()

2.4.3. execute_prepared_stmt()

2.4.4. ps_setup_disable_background_threads()

2.4.5. ps_setup_disable_consumer()

2.4.6. ps_setup_disable_instrument()

2.4.7. ps_setup_disable_thread()

2.4.8. ps_setup_enable_background_threads()

2.4.9. ps_setup_enable_consumer()

2.4.10. ps_setup_enable_instrument()

2.4.11. ps_setup_enable_thread()

2.4.12. ps_setup_reload_saved()

2.4.13. ps_setup_reset_to_default()

2.4.14. ps_setup_save()

2.4.15. ps_setup_show_disabled()

2.4.16. ps_setup_show_disabled_consumers()

2.4.17. ps_setup_show_disabled_instruments()

2.4.18. ps_setup_show_enabled()

2.4.19. ps_setup_show_enabled_consumers()

2.4.20. ps_setup_show_enabled_instruments()

2.4.21. ps_statement_avg_latency_histogram()

2.4.22. ps_trace_statement_digest()

2.4.23. ps_trace_thread()

2.4.24. ps_truncate_all_tables()

2.4.25. statement_performance_analyzer()

2.4.26. table_exists()

2.5. sys schema 函数

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-schema-functions.html

2.5.1. extract_schema_from_file_name()

2.5.2. extract_table_from_file_name()

2.5.3. format_bytes()

2.5.4. format_path()

2.5.5. format_statement()

2.5.6. format_time()

2.5.7. list_add()

2.5.8. list_drop()

2.5.9. ps_is_account_enabled()

2.5.10. ps_is_consumer_enabled()

2.5.11. ps_is_instrument_default_enabled()

2.5.12. ps_is_instrument_default_timed()

2.5.13. ps_is_thread_instrumented()

2.5.14. ps_thread_account()

2.5.15. ps_thread_id()

2.5.16. ps_thread_stack()

2.5.17. ps_thread_trx_info()

2.5.18. quote_identifier()

2.5.19. sys_get_config()

2.5.20. version_major()

2.5.21. version_minor()

2.5.22. version_patch()

3、总结

• 关于sys schema，在数据库中通过show create 语句查看到的是经过格转换的文本，要查看sql语句原始文本，可以通过mysqldump或mysqlpump导出sys 库并查看语句文本，或者到github上下载：https://github.com/mysql/mysql-sys

---

上一篇： 快速入门 | 下一篇： sys schema使用基础环境