MySQL5.7SYS系統SCHEMA
在說明系統數據庫之前,先來看下MySQL在數據字典方面的演變歷史:
MySQL4.1 提供了information_schema 數據字典。從此可以很簡單的用SQL語句來檢索需要的系統元數據了。
MySQL5.5 提供了performance_schema 性能字典。 但是這個字典比較專業,一般人可能也就看看就不了了之了。
MySQL5.7 提供了 sys系統數據庫。 sys數據庫裡面包含了一系列的存儲過程、自定義函數以及視圖來幫助我們快速的了解系統的元數據信息。
sys系統數據庫結合了information_schema和performance_schema的相關數據,讓我們更加容易的檢索元數據。 現在呢,我就示范下幾種場景下如何快速的使用。
第一,
比如之前想要知道某個表是否存在與否,可以用以下兩種方法:
A, 悲觀的方法,寫SQL從information_schema中拿信息:
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,樂觀的方法,假設表存在,寫一個存儲過程:
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 ;
現在來調用:
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數據庫裡面現有的存儲過程來進行調用,
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)
第二,獲取沒有使用過的索引。
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的查詢,
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)
第四, 同樣繼續上面的,過濾出有臨時表的查詢,
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 手工去過濾了。
第五, 檢索執行次數排名前五的語句,
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)
示例我就寫這麼多了,詳細的去看使用手冊並且自己摸索去吧。