mysql sys table

標籤：server   暫存資料表   memory   資料庫連接   主鍵   form   ati   lis   dup   

本文詳細地介紹了MySQL 5.7新引入的sys schema。首先，本文概要地介紹了sys schema的作用和定位；其次，分別介紹了sys schema中的視圖、函數和預存程序；接下來，通過兩個例子來示範sys schema的用法，便於大家理解sys schema帶來的實實在在的好處；最後討論了sys schema還可以增加的內容。1. sys schema的介紹sys schema是MySQL 5.7.7中引入的一個系統庫，包含了一系列視圖、函數和預存程序， 該項目專註於MySQL的易用性，例如，我們可以通過sys schema快速的知道，哪些語句使用了暫存資料表，哪個使用者請求了最多的io，哪個線程佔用了最多的記憶體，哪些索引是無用索引等。引入sys schema以後，MySQL的易用性將會得到極大地提升，MySQL的使用者分析問題和定位問題，將更多的依賴sys schema，減少外部工具的使用。前面說過，sys schema中包含了大量的視圖（只有sys_config是innodb表），那麼，這些視圖的資訊來自哪裡呢？視圖中的資訊均來自performance schema和information schema中的統計資訊。MySQL Server blog中有一個很好的比喻： For Linux users I like to compare performance_schema to /proc, and SYS to vmstat.也就是說，performance schema和information schema中提供了資訊源，但是，沒有很好的將這些資訊組織成有用的資訊，從而沒有很好的發揮它們的作用。而sys schema使用performance schema和information schema中的資訊，通過視圖的方式給出解決實際問題的答案。這就是sys schema的作用和目的，也是為什麼sys schema值得我們花點時間學習的原因。2. sys schema中的視圖、函數和預存程序可以通過以下語句快速查看sys schema包含的視圖、函數和預存程序 show full tables from sys show function status where db = ‘sys‘; show procedure status where db = ‘sys‘接下來將依次給出所有的視圖、函數和預存程序，並進行簡單的分析，希望能夠達到拋磚引玉的效果。2.1 視圖sys schema中的視圖（和一張表）如下，通過名稱就很容易猜到具體是做什麼用的。mysql> select table_name , table_type, engine from information_schema.tables where table_schema = ‘sys‘ order by table_name;+-----------------------------------------------+------------+--------+| table_name | table_type | engine |+-----------------------------------------------+------------+--------+| host_summary | VIEW | NULL || host_summary_by_file_io | VIEW | NULL || host_summary_by_file_io_type | VIEW | NULL || host_summary_by_stages | VIEW | NULL || host_summary_by_statement_latency | VIEW | NULL || host_summary_by_statement_type | VIEW | NULL || innodb_buffer_stats_by_schema | VIEW | NULL || innodb_buffer_stats_by_table | VIEW | NULL || innodb_lock_waits | VIEW | NULL || io_by_thread_by_latency | VIEW | NULL || io_global_by_file_by_bytes | VIEW | NULL || io_global_by_file_by_latency | VIEW | NULL || io_global_by_wait_by_bytes | VIEW | NULL || io_global_by_wait_by_latency | VIEW | NULL || latest_file_io | VIEW | NULL || memory_by_host_by_current_bytes | VIEW | NULL || memory_by_thread_by_current_bytes | VIEW | NULL || memory_by_user_by_current_bytes | VIEW | NULL || memory_global_by_current_bytes | VIEW | NULL || memory_global_total | VIEW | NULL || metrics | VIEW | NULL || processlist | VIEW | NULL || ps_check_lost_instrumentation | VIEW | NULL || schema_auto_increment_columns | VIEW | NULL || schema_index_statistics | VIEW | NULL || schema_object_overview | VIEW | NULL || schema_redundant_indexes | VIEW | NULL || schema_tables_with_full_table_scans | VIEW | NULL || schema_table_lock_waits | VIEW | NULL || schema_table_statistics | VIEW | NULL || schema_table_statistics_with_buffer | VIEW | NULL || schema_unused_indexes | VIEW | NULL || session | VIEW | NULL || session_ssl_status | VIEW | NULL || statements_with_errors_or_warnings | VIEW | NULL || statements_with_full_table_scans | VIEW | NULL || statements_with_runtimes_in_95th_percentile | VIEW | NULL || statements_with_sorting | VIEW | NULL || statements_with_temp_tables | VIEW | NULL || statement_analysis | VIEW | NULL || sys_config | BASE TABLE | InnoDB || user_summary | VIEW | NULL || user_summary_by_file_io | VIEW | NULL || user_summary_by_file_io_type | VIEW | NULL || user_summary_by_stages | VIEW | NULL || user_summary_by_statement_latency | VIEW | NULL || user_summary_by_statement_type | VIEW | NULL || version | VIEW | NULL || waits_by_host_by_latency | VIEW | NULL || waits_by_user_by_latency | VIEW | NULL || waits_global_by_latency | VIEW | NULL || wait_classes_global_by_avg_latency | VIEW | NULL || wait_classes_global_by_latency | VIEW | NULL |這裡還沒有列出全部的視圖，對於每一個視圖，都有兩種形式：mysql> select table_name from information_schema.tables where table_schema = ‘sys‘ and table_name like ‘%memory%‘;+-------------------------------------+| table_name |+-------------------------------------+| memory_by_host_by_current_bytes || memory_by_thread_by_current_bytes || memory_by_user_by_current_bytes || memory_global_by_current_bytes || memory_global_total || x$memory_by_host_by_current_bytes || x$memory_by_thread_by_current_bytes || x$memory_by_user_by_current_bytes || x$memory_global_by_current_bytes || x$memory_global_total |+-------------------------------------+10 rows in set (0.00 sec)前一種是便於人類閱讀的形式，格式化了時間單位和位元組單位，另外一種形式是指以”x$”開頭的視圖名稱，是為了便於工具處理。如下所示：mysql> select * from host_summary_by_file_io;+-------------+------+------------+| host | ios | io_latency |+-------------+------+------------+| background | 2143 | 266.48 ms || 172.17.42.1 | 1748 | 116.52 ms |+-------------+------+------------+2 rows in set (0.00 sec)mysql> select * from x$host_summary_by_file_io;+-------------+------+--------------+| host | ios | io_latency |+-------------+------+--------------+| background | 2148 | 266558291670 || 172.17.42.1 | 1748 | 116518395300 |+-------------+------+--------------+2 rows in set (0.00 sec)我們再看一下host_summary_by_file_io的定義，如下所示：mysql> show create table host_summary_by_file_io\G*************************** 1. row *************************** View: host_summary_by_file_io Create View: CREATE ALGORITHM=TEMPTABLE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `host_summary_by_file_io` AS select if(isnull(`performance_schema`.`events_waits_summary_by_host_by_event_name`.`HOST`),‘background‘,`performance_schema`.`events_waits_summary_by_host_by_event_name`.`HOST`) AS `host`,sum(`performance_schema`.`events_waits_summary_by_host_by_event_name`.`COUNT_STAR`) AS `ios`,`sys`.`format_time`(sum(`performance_schema`.`events_waits_summary_by_host_by_event_name`.`SUM_TIMER_WAIT`)) AS `io_latency` from `performance_schema`.`events_waits_summary_by_host_by_event_name` where (`performance_schema`.`events_waits_summary_by_host_by_event_name`.`EVENT_NAME` like ‘wait/io/file/%‘) group by if(isnull(`performance_schema`.`events_waits_summary_by_host_by_event_name`.`HOST`),‘background‘,`performance_schema`.`events_waits_summary_by_host_by_event_name`.`HOST`) order by sum(`performance_schema`.`events_waits_summary_by_host_by_event_name`.`SUM_TIMER_WAIT`) desccharacter_set_client: utf8collation_connection: utf8_general_ci1 row in set (0.00 sec)可以看到，定義非常複雜，如果不是sys schema提供了這麼好用的視圖，我們自己幾乎無法從這麼多資訊中擷取到有用的資訊。2.2 函數如果你仔細看了上面的定義視圖的SQL語句，你可能注意它使用了一個叫做sys.format_time的函數，這個函數也是隨著sys schema一起發布，用以格式化時間單位。sys schema還包含了其他一些有用的函數，如下所示：mysql> select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE from ROUTINES where ROUTINE_TYPE=‘FUNCTION‘;+----------------+----------------------------------+--------------+| ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE |+----------------+----------------------------------+--------------+| sys | extract_schema_from_file_name | FUNCTION || sys | extract_table_from_file_name | FUNCTION || sys | format_bytes | FUNCTION || sys | format_path | FUNCTION || sys | format_statement | FUNCTION || sys | format_time | FUNCTION || sys | list_add | FUNCTION || sys | list_drop | FUNCTION || sys | ps_is_account_enabled | FUNCTION || sys | ps_is_consumer_enabled | FUNCTION || sys | ps_is_instrument_default_enabled | FUNCTION || sys | ps_is_instrument_default_timed | FUNCTION || sys | ps_is_thread_instrumented | FUNCTION || sys | ps_thread_account | FUNCTION || sys | ps_thread_id | FUNCTION || sys | ps_thread_stack | FUNCTION || sys | ps_thread_trx_info | FUNCTION || sys | sys_get_config | FUNCTION || sys | version_major | FUNCTION || sys | version_minor | FUNCTION || sys | version_patch | FUNCTION |+----------------+----------------------------------+--------------+21 rows in set (0.00 sec)2.3 預存程序下面的是sys schema包含的預存程序。可以看到，大部分預存程序都是PS開頭的，PS是performance schema的簡寫，這些預存程序都是用來控制performance schema統計的行為。例如，ps_truncate_all_tables 的作用就是truncate所有performance schema中summary相關的表。如下所示：mysql> select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE from ROUTINES where ROUTINE_TYPE=‘PROCEDURE‘; +----------------+-------------------------------------+--------------+| ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE |+----------------+-------------------------------------+--------------+| sys | create_synonym_db | PROCEDURE || sys | diagnostics | PROCEDURE || sys | execute_prepared_stmt | PROCEDURE || sys | ps_setup_disable_background_threads | PROCEDURE || sys | ps_setup_disable_consumer | PROCEDURE || sys | ps_setup_disable_instrument | PROCEDURE || sys | ps_setup_disable_thread | PROCEDURE || sys | ps_setup_enable_background_threads | PROCEDURE || sys | ps_setup_enable_consumer | PROCEDURE || sys | ps_setup_enable_instrument | PROCEDURE || sys | ps_setup_enable_thread | PROCEDURE || sys | ps_setup_reload_saved | PROCEDURE || sys | ps_setup_reset_to_default | PROCEDURE || sys | ps_setup_save | PROCEDURE || sys | ps_setup_show_disabled | PROCEDURE || sys | ps_setup_show_disabled_consumers | PROCEDURE || sys | ps_setup_show_disabled_instruments | PROCEDURE || sys | ps_setup_show_enabled | PROCEDURE || sys | ps_setup_show_enabled_consumers | PROCEDURE || sys | ps_setup_show_enabled_instruments | PROCEDURE || sys | ps_statement_avg_latency_histogram | PROCEDURE || sys | ps_trace_statement_digest | PROCEDURE || sys | ps_trace_thread | PROCEDURE || sys | ps_truncate_all_tables | PROCEDURE || sys | statement_performance_analyzer | PROCEDURE || sys | table_exists | PROCEDURE |+----------------+-------------------------------------+--------------+26 rows in set (0.00 sec)3. sys schema樣本這一節簡單的示範sys schema的用法，便於大家理解sys schema帶來的實實在在的好處，然後討論了sys schema還可以增加的內容。3.1 sys schema的用法我們來看幾個與索引相關的例子： 如何查看資料庫中的冗餘索引，在MySQL 5.7之前，需要使用percona的 pt-duplicate-key-checker，現在直接查詢sys.schema_redundant_indexes就可以了: select * from sys.schema_redundant_indexes; 如何擷取未使用的索引 在MySQL 5.7之前，我們也可以通過information schema查看未使用的索引： SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name ; 有了sys schema以後，直接查詢schema_unused_indexes即可: select * from schema_unused_indexes; 如何查看使用全表掃描的SQL語句（MySQL 5.7之前我不知道怎麼看額(⊙o⊙)…） 有了sys schema以後，直接查詢statements_with_full_table_scans即可： select * from statements_with_full_table_scans3.2 sys schema可以引入的視圖我發現，還有一些非常有用的資訊，可以通過information schema 和performance schema擷取到，但是，在sys schema中卻沒有。有鑒於此，個人感覺未來sys schema中的視圖會繼續增加。例如： 查看沒有主鍵的表 SELECT DISTINCT t.table_schema, t.table_name FROM information_schema.tables AS t LEFT JOIN information_schema.columns AS c ON t.table_schema = c.table_schema AND t.table_name = c.table_name AND c.column_key = "PRI" WHERE t.table_schema NOT IN (‘information_schema‘, ‘mysql‘, ‘performance_schema‘) AND c.table_name IS NULL AND t.table_type != ‘VIEW‘; 查看是誰建立的暫存資料表 SELECT user, host, event_name, count_star AS cnt, sum_created_tmp_disk_tables AS tmp_disk_tables, sum_created_tmp_tables AS tmp_tables FROM performance_schema.events_statements_summary_by_account_by_event_name WHERE sum_created_tmp_disk_tables > 0 OR sum_created_tmp_tables > 0 ; 沒有正確關閉資料庫連接的使用者 SELECT ess.user, ess.host , (a.total_connections - a.current_connections) - ess.count_star as not_closed , ((a.total_connections - a.current_connections) - ess.count_star) * 100 / (a.total_connections - a.current_connections) as pct_not_closed FROM performance_schema.events_statements_summary_by_account_by_event_name ess JOIN performance_schema.accounts a on (ess.user = a.user and ess.host = a.host) WHERE ess.event_name = ‘statement/com/quit‘ AND (a.total_connections - a.current_connections) > ess.count_star ;這些都是比較有用的資訊，適合加入到sys schema中。4. 總結本文詳細地介紹了sys schema的作用和組成，並通過幾個例子示範了sys schema的易用性和不足，相信通過這篇文章，大家對sys schema能夠有一個較完整的瞭解，也願意花時間去深入瞭解sys schema中的各個視圖。此外，sys schema可以應用在MySQL 5.6上，具體資訊見這裡。

mysql sys table