MySQL 5.7 SYS System schema

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Before explaining the system database, let's look at the evolution of MySQL in data dictionaries:
MySQL4.1 provides a information_schema data dictionary. It is easy to use SQL statements to retrieve the required system metadata from this point.
MYSQL5.5 provides a performance_schema performance dictionary. But the dictionary is more professional, the average person may also look at the dead.
MySQL5.7 provides the SYS system database. SYS database contains a series of stored procedures, custom functions, and views to help us quickly understand the system's meta-data information.


The SYS system database combines INFORMATION_SCHEMA and performance_schema data to make it easier to retrieve metadata. Now, I'll show you how to use it quickly in the next few scenarios.


First,
For example, before you want to know whether a table exists or not, there are two ways to do this:
A, pessimistic method, write SQL from INFORMATION_SCHEMA to take information:

[SQL]View PlainCopy

2. Mysql> SELECT IF (COUNT (*) = 0,' not exists! ',' exists! ') as ' result ' from information_schema.tables WHERE table_schema = ' new_feature ' and table_name = ' T1 ';
4. +-------------+  
6. | Result |
8. +-------------+  
10. |  Not exists! |
12. +-------------+  
14. 1 row in Set (0.00 sec)

B, the optimistic approach, assuming that the table exists, write a stored procedure:
[SQL]View PlainCopy

2. DELIMITER $$
8. Use ' New_feature ' $$
14. DROP PROCEDURE IF EXISTS ' sp_table_exists ' $$
20. CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' sp_table_exists ' (
22. in Db_name VARCHAR (+),
24. in Tb_name VARCHAR (+),
26. out is_exists VARCHAR
28. )
30. BEGIN
32. DECLARE no_such_table CONDITION for 1146;
34. DECLARE EXIT HANDLER for no_such_table
36. BEGIN
38. SET is_exists = ' not exists! ';
40. END;
44. SET @stmt = CONCAT (' Select 1 from ', db_name,'. ', tb_name);
46. PREPARE s1 from @stmt;
48. EXECUTE S1;
50. deallocate PREPARE s1;
52. SET is_exists = ' exists! ';
54. end$$
60. DELIMITER;

Now to call:
[SQL]View PlainCopy

1. Mysql> Call Sp_table_exists (' new_feature ',' t1 ', @result);
2. Query OK, 0 rows Affected (0.00 sec)
5. mysql> Select @result;
6. +-------------+  
7. | @result |
8. +-------------+  
9. |  Not exists! |
10. +-------------+  
11. 1 row in Set (0.00 sec)

Now we call directly with the existing stored procedure in the SYS database,
[SQL]View PlainCopy

2. Mysql> Call Table_exists (' new_feature ',' t1 ', @v_is_exists);
4. Query OK, 0 rows Affected (0.00 sec)
10. Mysql> SELECT IF (@v_is_exists = ',' not exists! ', @v_is_exists) as ' result ';
12. +-------------+  
14. | Result |
16. +-------------+  
18. |  Not exists! |
20. +-------------+  
22. 1 row in Set (0.00 sec)

second, get an index that has not been used.


[SQL]View PlainCopy

2. Mysql> SELECT * from schema_unused_indexes;
4. +---------------+-------------+--------------+  
6. | Object_schema | object_name | index_name |
8. +---------------+-------------+--------------+  
10. | New_feature | T1 | Idx_log_time |
12. | New_feature | T1 | Idx_rank2 |
14. +---------------+-------------+--------------+  
16. 2 rows in Set (0.00 sec)

third, retrieve the table scan information below the specified database and filter out queries that are greater than 10 execution times.
[SQL]View PlainCopy

2. Mysql> SELECT * from statement_analysis WHERE db=' new_feature ' and full_scan = ' * ' and exe C_count > 10\g
4. 1. Row ***************************
6. Query:show STATUS
8. Db:new_feature
10. Full_scan: *
12. Exec_count:26
14. err_count:0
16. warn_count:0
18. total_latency:74.68 ms
20. max_latency:3.86 ms
22. avg_latency:2.87 ms
24. lock_latency:4.50 ms
26. rows_sent:9594
28. rows_sent_avg:369
30. rows_examined:9594
32. rows_examined_avg:369
34. rows_affected:0
36. rows_affected_avg:0
38. tmp_tables:0
40. tmp_disk_tables:0
42. rows_sorted:0
44. sort_merge_passes:0
46. digest:475fa3ad9d4a846cfa96441050fc9787
48. First_seen:2015-11-16 10:51:17
50. Last_seen:2015-11-16 11:28:13
52. 2. Row ***************************
54. Query: SELECT ' state ', ' round ' ( SUM ... uration (summed) in sec ' DESC
56. Db:new_feature
58. Full_scan: *
60. Exec_count:12
62. err_count:0
64. Warn_count:12
66. total_latency:16.43 ms
68. max_latency:2.39 ms
70. avg_latency:1.37 ms
72. lock_latency:3.54 ms
74. rows_sent:140
76. Rows_sent_avg:12
78. rows_examined:852
80. rows_examined_avg:71
82. rows_affected:0
84. rows_affected_avg:0
86. Tmp_tables:24
88. tmp_disk_tables:0
90. rows_sorted:140
92. sort_merge_passes:0
94. digest:538e506ee0075e040b076f810ccb5f5c
96. First_seen:2015-11-16 10:51:17
98. Last_seen:2015-11-16 11:28:13
100. 2 rows in Set (0.01 sec)

Finally, the same continues above, filtering out the query with the temporary table,


[SQL]View PlainCopy

2. Mysql> SELECT * from statement_analysis WHERE db=' new_feature ' and tmp_tables > 0 ORDER by tmp_tables DESC LIMIT 1\g
4. 1. Row ***************************
6. Query: SELECT ' Performance_schema '. ... name '. ' sum_timer_wait ' DESC
8. Db:new_feature
10. Full_scan: *
12. Exec_count:2
14. err_count:0
16. warn_count:0
18. total_latency:87.96 ms
20. max_latency:59.50 ms
22. avg_latency:43.98 ms
24. lock_latency:548.00 US
26. Rows_sent:101
28. Rows_sent_avg:51
30. rows_examined:201
32. Rows_examined_avg:101
34. rows_affected:0
36. rows_affected_avg:0
38. tmp_tables:332
40. Tmp_disk_tables:15
42. rows_sorted:0
44. sort_merge_passes:0
46. digest:ff9bdfb7cf3f44b2da4c52dcde7a7352
48. First_seen:2015-11-16 10:24:42
50. Last_seen:2015-11-16 10:24:42
52. 1 row in Set (0.01 sec)

Can see the above query detailed detailed, no longer do not perform the show status manually to filter.




Five, retrieve the top five executions of the statement,
[SQL]View PlainCopy

2. Mysql> SELECT statement,total from user_summary_by_statement_type WHERE 'user ' =' root '   ORDER by Total DESC LIMIT 5;
4. +-------------------+-------+  
6. | Statement | Total |
8. +-------------------+-------+  
10. | Jump_if_not | 17635 |
12. |  Freturn | 3120 |
14. |   show_create_table | 289 |
16. |   Field List | 202 |
18. |   set_option | 190 |
20. +-------------------+-------+  
22. 5 rows in Set (0.01 sec)

Example I wrote so many, detailed to see the user's manual and find out for yourself. http://blog.csdn.net/yueliangdao0608/article/details/50032851

MySQL 5.7 SYS System schema