標籤：
著作權聲明：本文為博主原創文章，未經博主允許不得轉載。
在說明系統資料庫之前，先來看下MySQL在資料字典方面的演變曆史：
MySQL4.1 提供了information_schema 資料字典。從此可以很簡單的用SQL語句來檢索需要的系統中繼資料了。
MySQL5.5 提供了performance_schema 效能字典。 但是這個字典比較專業，一般人可能也就看看就不了了之了。
MySQL5.7 提供了 sys系統資料庫。 sys資料庫裡麵包含了一系列的預存程序、自訂函數以及視圖來協助我們快速的瞭解系統的中繼資料資訊。
sys系統資料庫結合了information_schema和performance_schema的相關資料，讓我們更加容易的檢索中繼資料。 現在呢，我就示範下幾種情境下如何快速的使用。
第一，
比如之前想要知道某個表是否存在與否，可以用以下兩種方法：
A， 悲觀的方法，寫SQL從information_schema中拿資訊：
[sql] view plain copy
- mysql> SELECT IF(COUNT(*) = 0,‘Not exists!‘,‘Exists!‘) AS ‘result‘ FROM information_schema.tables WHERE table_schema = ‘new_feature‘ AND table_name = ‘t1‘;
- +-------------+
- | result |
- +-------------+
- | Not exists! |
- +-------------+
- 1 row in set (0.00 sec)
B，樂觀的方法，假設表存在，寫一個預存程序：
[sql] view plain copy
- DELIMITER $$
-
-
- USE `new_feature`$$
-
-
- DROP PROCEDURE IF EXISTS `sp_table_exists`$$
-
-
- CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_table_exists`(
- IN db_name VARCHAR(64),
- IN tb_name VARCHAR(64),
- OUT is_exists VARCHAR(60)
- )
- BEGIN
- DECLARE no_such_table CONDITION FOR 1146;
- DECLARE EXIT HANDLER FOR no_such_table
- BEGIN
- SET is_exists = ‘Not exists!‘;
- END;
-
- SET @stmt = CONCAT(‘select 1 from ‘,db_name,‘.‘,tb_name);
- PREPARE s1 FROM @stmt;
- EXECUTE s1;
- DEALLOCATE PREPARE s1;
- SET is_exists = ‘Exists!‘;
- END$$
-
-
- DELIMITER ;
現在來調用：
[sql] view plain copy
- mysql> call sp_table_exists(‘new_feature‘,‘t1‘,@result);
- Query OK, 0 rows affected (0.00 sec)
-
-
- mysql> select @result;
- +-------------+
- | @result |
- +-------------+
- | Not exists! |
- +-------------+
- 1 row in set (0.00 sec)
現在我們直接用sys資料庫裡面現有的預存程序來進行調用，
[sql] view plain copy
- mysql> CALL table_exists(‘new_feature‘,‘t1‘,@v_is_exists);
- Query OK, 0 rows affected (0.00 sec)
-
-
- mysql> SELECT IF(@v_is_exists = ‘‘,‘Not exists!‘,@v_is_exists) AS ‘result‘;
- +-------------+
- | result |
- +-------------+
- | Not exists! |
- +-------------+
- 1 row in set (0.00 sec)
第二，擷取沒有使用過的索引。
[sql] view plain copy
- mysql> SELECT * FROM schema_unused_indexes;
- +---------------+-------------+--------------+
- | object_schema | object_name | index_name |
- +---------------+-------------+--------------+
- | new_feature | t1 | idx_log_time |
- | new_feature | t1 | idx_rank2 |
- +---------------+-------------+--------------+
- 2 rows in set (0.00 sec)
第三， 檢索指定資料庫下面的表掃描資訊，過濾出執行次數大於10的查詢，
[sql] view plain copy
- mysql> SELECT * FROM statement_analysis WHERE db=‘new_feature‘ AND full_scan = ‘*‘ AND exec_count > 10\G
- *************************** 1. row ***************************
- query: SHOW STATUS
- db: new_feature
- full_scan: *
- exec_count: 26
- err_count: 0
- warn_count: 0
- total_latency: 74.68 ms
- max_latency: 3.86 ms
- avg_latency: 2.87 ms
- lock_latency: 4.50 ms
- rows_sent: 9594
- rows_sent_avg: 369
- rows_examined: 9594
- rows_examined_avg: 369
- rows_affected: 0
- rows_affected_avg: 0
- tmp_tables: 0
- tmp_disk_tables: 0
- rows_sorted: 0
- sort_merge_passes: 0
- digest: 475fa3ad9d4a846cfa96441050fc9787
- first_seen: 2015-11-16 10:51:17
- last_seen: 2015-11-16 11:28:13
- *************************** 2. row ***************************
- query: SELECT `state` , `round` ( SUM ... uration (summed) in sec` DESC
- db: new_feature
- full_scan: *
- exec_count: 12
- err_count: 0
- warn_count: 12
- total_latency: 16.43 ms
- max_latency: 2.39 ms
- avg_latency: 1.37 ms
- lock_latency: 3.54 ms
- rows_sent: 140
- rows_sent_avg: 12
- rows_examined: 852
- rows_examined_avg: 71
- rows_affected: 0
- rows_affected_avg: 0
- tmp_tables: 24
- tmp_disk_tables: 0
- rows_sorted: 140
- sort_merge_passes: 0
- digest: 538e506ee0075e040b076f810ccb5f5c
- first_seen: 2015-11-16 10:51:17
- last_seen: 2015-11-16 11:28:13
- 2 rows in set (0.01 sec)
第四， 同樣繼續上面的，過濾出有暫存資料表的查詢，
[sql] view plain copy
- mysql> SELECT * FROM statement_analysis WHERE db=‘new_feature‘ AND tmp_tables > 0 ORDER BY tmp_tables DESC LIMIT 1\G
- *************************** 1. row ***************************
- query: SELECT `performance_schema` . ... name` . `SUM_TIMER_WAIT` DESC
- db: new_feature
- full_scan: *
- exec_count: 2
- err_count: 0
- warn_count: 0
- total_latency: 87.96 ms
- max_latency: 59.50 ms
- avg_latency: 43.98 ms
- lock_latency: 548.00 us
- rows_sent: 101
- rows_sent_avg: 51
- rows_examined: 201
- rows_examined_avg: 101
- rows_affected: 0
- rows_affected_avg: 0
- tmp_tables: 332
- tmp_disk_tables: 15
- rows_sorted: 0
- sort_merge_passes: 0
- digest: ff9bdfb7cf3f44b2da4c52dcde7a7352
- first_seen: 2015-11-16 10:24:42
- last_seen: 2015-11-16 10:24:42
- 1 row in set (0.01 sec)
可以看到上面查詢詳細的詳細，再也不用執行show status 手工去過濾了。
第五， 檢索執行次數排名前五的語句，
[sql] view plain copy
- mysql> SELECT statement,total FROM user_summary_by_statement_type WHERE `user`=‘root‘ ORDER BY total DESC LIMIT 5;
- +-------------------+-------+
- | statement | total |
- +-------------------+-------+
- | jump_if_not | 17635 |
- | freturn | 3120 |
- | show_create_table | 289 |
- | Field List | 202 |
- | set_option | 190 |
- +-------------------+-------+
- 5 rows in set (0.01 sec)
樣本我就寫這麼多了，詳細的去看使用手冊並且自己摸索去吧。 http://blog.csdn.net/yueliangdao0608/article/details/50032851
MySQL 5.7 SYS系統SCHEMA
