sys schema详解 - xiaoboluo768/mysql-system-schema GitHub Wiki

sys schema详解

1、初识sys schema

1.1. sys schema使用基础环境

1.2. sys schema 初体验

1.3. sys schema 进度报告

2、 sys schema 对象使用详解

2.1. sys schema目录

2.2. sys schema配置

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 存储过程

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 函数

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使用基础环境