一个技术博客

(一) Mysql5.7 SYS Schema

本文由 Tim 于 2016-04-07 15:21:19 发表

MySQL4.1 提供了information_schema 数据字典。从此可以很简单的用SQL语句来检索需要的系统元数据了。
MySQL5.5 提供了performance_schema 性能字典。 但是这个字典比较专业,一般人可能也就看看就不了了之了。

sys schema是MySQL 5.7.7中引入的一个系统库,包含了一系列的视图、存储过程、自定义函数,以及来帮助我们快速的了解系统的元数据信息, 用于提升MySQL的易用性,例如,我们可以通过sys schema快速的知道,哪些语句使用了临时表,哪个用户请求了最多的io,哪个线程占用了最多的内存,哪些索引是无用索引等。

sys schema结合了information_schema和performance_schema的相关数据,让我们更加容易的检索元数据。

官网详细说明

可以通过以下语句快速查看sys schema包含的视图、函数和存储过程

 

关于带不带x$,去掉x$同名的视图他们的数据是相同的,区别在于不带x$的单位更加符合直接阅读经过了转换,而带x$是为了某些工具存在而使用的原始单位(多数应该是mysql默认的)

 

现在查看版本还可以这样

 

以前查询表是否存在需要查询information_schema.tables 或者写存储过程,现在可以用sys schema内置的存储过程table_exists()

 

如果存在会列出表的基本信息,不存在为空,需要没有该表的时候打印不存在可以这样

查看数据库中的冗余索引

select * from sys.schema_redundant_indexes;
获取未使用的索引
在MySQL 5.7之前,我们也可以通过performance_schema查看未使用的索引:

现在直接查询schema_unused_indexes即可:

查看使用全表扫描的SQL语句

可以看到引入sys schema以后,MySQL的易用性得到极大地提升,MySQL的用户分析问题和定位问题,将更多的依赖sys schema,减少外部工具的使用。
可以好好将sys schema利用起来

下面是通过information schema 和performance schema获取到常用的信息。

查看没有主键的表

查看是谁创建的临时表

没有正确关闭数据库连接的用户

 

sys schema包含的视图、函数和存储过程官方说明

The following tables list sys schema objects and provide a short description of each one.

Table 23.1 sys Schema Tables and Triggers

Table or Trigger Name Description
sys_config sys schema configuration options
sys_config_insert_set_user sys_config insert trigger
sys_config_update_set_user sys_config update trigger

Table 23.2 sys Schema Views

View Name Description
host_summary, x$host_summary Statement activity, file I/O, and connections, grouped by host
host_summary_by_file_io, x$host_summary_by_file_io File I/O, grouped by host
host_summary_by_file_io_type, x$host_summary_by_file_io_type File I/O, grouped by host and event type
host_summary_by_stages, x$host_summary_by_stages Statement stages, grouped by host
host_summary_by_statement_latency, x$host_summary_by_statement_latency Statement statistics, grouped by host
host_summary_by_statement_type, x$host_summary_by_statement_type Statements executed, grouped by host and statement
innodb_buffer_stats_by_schema, x$innodb_buffer_stats_by_schema InnoDB buffer information, grouped by schema
innodb_buffer_stats_by_table, x$innodb_buffer_stats_by_table InnoDB buffer information, grouped by schema and table
innodb_lock_waits, x$innodb_lock_waits InnoDB lock information
io_by_thread_by_latency, x$io_by_thread_by_latency I/O consumers, grouped by thread
io_global_by_file_by_bytes, x$io_global_by_file_by_bytes Global I/O consumers, grouped by file and bytes
io_global_by_file_by_latency, x$io_global_by_file_by_latency Global I/O consumers, grouped by file and latency
io_global_by_wait_by_bytes, x$io_global_by_wait_by_bytes Global I/O consumers, grouped by bytes
io_global_by_wait_by_latency, x$io_global_by_wait_by_latency Global I/O consumers, grouped by latency
latest_file_io, x$latest_file_io Most recent I/O, grouped by file and thread
memory_by_host_by_current_bytes, x$memory_by_host_by_current_bytes Memory use, grouped by host
memory_by_thread_by_current_bytes, x$memory_by_thread_by_current_bytes Memory use, grouped by thread
memory_by_user_by_current_bytes, x$memory_by_user_by_current_bytes Memory use, grouped by user
memory_global_by_current_bytes, x$memory_global_by_current_bytes Memory use, grouped by allocation type
memory_global_total, x$memory_global_total Total memory use
metrics Server metrics
processlist, x$processlist Processlist information
ps_check_lost_instrumentation Variables that have lost instruments
schema_auto_increment_columns AUTO_INCREMENT column information
schema_index_statistics, x$schema_index_statistics Index statistics
schema_object_overview Types of objects within each schema
schema_redundant_indexes Duplicate or redundant indexes
schema_table_lock_waits, x$schema_table_lock_waits Sessions waiting for metadata locks
schema_table_statistics, x$schema_table_statistics Table statistics
schema_table_statistics_with_buffer, x$schema_table_statistics_with_buffer Table statistics, including InnoDB buffer pool statistics
schema_tables_with_full_table_scans, x$schema_tables_with_full_table_scans Tables being accessed with full scans
schema_unused_indexes Indexes not in active use
session, x$session Processlist information for user sessions
session_ssl_status Connection SSL information
statement_analysis, x$statement_analysis Statement aggregate statistics
statements_with_errors_or_warnings, x$statements_with_errors_or_warnings Statements that have produced errors or warnings
statements_with_full_table_scans, x$statements_with_full_table_scans Statements that have done full table scans
statements_with_runtimes_in_95th_percentile, x$statements_with_runtimes_in_95th_percentile Statements with highest average runtime
statements_with_sorting, x$statements_with_sorting Statements that performed sorts
statements_with_temp_tables, x$statements_with_temp_tables Statements that used temporary tables
user_summary, x$user_summary User statement and connection activity
user_summary_by_file_io, x$user_summary_by_file_io File I/O, grouped by user
user_summary_by_file_io_type, x$user_summary_by_file_io_type File I/O, grouped by user and event
user_summary_by_stages, x$user_summary_by_stages Stage events, grouped by user
user_summary_by_statement_latency, x$user_summary_by_statement_latency Statement statistics, grouped by user
user_summary_by_statement_type, x$user_summary_by_statement_type Statements executed, grouped by user and statement
version Current sys schema and MySQL server versions
wait_classes_global_by_avg_latency, x$wait_classes_global_by_avg_latency Wait class average latency, grouped by event class
wait_classes_global_by_latency, x$wait_classes_global_by_latency Wait class total latency, grouped by event class
waits_by_host_by_latency, x$waits_by_host_by_latency Wait events, grouped by host and event
waits_by_user_by_latency, x$waits_by_user_by_latency Wait events, grouped by user and event
waits_global_by_latency, x$waits_global_by_latency Wait events, grouped by event
x$ps_digest_95th_percentile_by_avg_us Helper view for 95th-percentile views
x$ps_digest_avg_latency_distribution Helper view for 95th-percentile views
x$ps_schema_table_statistics_io Helper view for table-statistics views
x$schema_flattened_keys Helper view for schema_redundant_indexes
 
Table 23.3 sys Schema Stored Procedures
Procedure Name Description
create_synonym_db() Create synonym for schema
diagnostics() Collect system diagnostic information
execute_prepared_stmt() Execute prepared statement
ps_setup_disable_background_threads() Disable background thread instrumentation
ps_setup_disable_consumer() Disable consumers
ps_setup_disable_instrument() Disable instruments
ps_setup_disable_thread() Disable instrumentation for thread
ps_setup_enable_background_threads() Enable background thread instrumentation
ps_setup_enable_consumer() Enable consumers
ps_setup_enable_instrument() Enable instruments
ps_setup_enable_thread() Enable instrumentation for thread
ps_setup_reload_saved() Reload saved Performance Schema configuration
ps_setup_reset_to_default() Reset saved Performance Schema configuration
ps_setup_save() Save Performance Schema configuration
ps_setup_show_disabled() Display disabled Performance Schema configuration
ps_setup_show_disabled_consumers() Display disabled Performance Schema consumers
ps_setup_show_disabled_instruments() Display disabled Performance Schema instruments
ps_setup_show_enabled() Display enabled Performance Schema configuration
ps_setup_show_enabled_consumers() Display enabled Performance Schema consumers
ps_setup_show_enabled_instruments() Display enabled Performance Schema instruments
ps_statement_avg_latency_histogram() Display statement latency histogram
ps_trace_statement_digest() Trace Performance Schema instrumentation for digest
ps_trace_thread() Dump Performance Schema data for thread
ps_truncate_all_tables() Truncate Performance Schema summary tables
statement_performance_analyzer() Report of statements running on server
table_exists() Whether a table exists
 

Table 23.4 sys Schema Stored Functions

Function Name Description
extract_schema_from_file_name() Extract schema name from file path name
extract_table_from_file_name() Extract table name from file path name
format_bytes() Convert byte value to value with units
format_path() Replace data and temp-file directories in path name with symbolic values
format_statement() Truncate long statement to fixed length
format_time() Convert picoseconds value to value with units
list_add() Add item to list
list_drop() Remove item from list
ps_is_account_enabled() Check whether account instrumentation is enabled
ps_is_consumer_enabled() Check whether consumer is enabled
ps_is_instrument_default_enabled() Check whether instrument is enabled
ps_is_instrument_default_timed() Check whether instrument is timed
ps_is_thread_instrumented() Check whether thread is instrumented
ps_thread_account() Return account for thread ID
ps_thread_id() Return thread ID for connection ID
ps_thread_stack() Return event information for thread ID
ps_thread_trx_info() Return transaction information for thread ID
sys_get_config() Return sys schema configuration option
version_major() MySQL server major version number
version_minor() MySQL server minor version number
version_patch() MySQL server patch release version number

 

欢迎转载,转载请注明出处!Tim » (一) Mysql5.7 SYS Schema

点赞 (1)or拍砖 (0)
分享到:更多 ()