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
- 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, the optimistic approach, assuming that the table exists, write a stored procedure:
[SQL]View PlainCopy
- DELIMITER $$
- Use ' New_feature ' $$
- DROP PROCEDURE IF EXISTS ' sp_table_exists ' $$
- CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' sp_table_exists ' (
- in Db_name VARCHAR (+),
- in Tb_name VARCHAR (+),
- out is_exists VARCHAR
- )
- 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;
Now to call:
[SQL]View PlainCopy
- 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)
Now we call directly with the existing stored procedure in the SYS database,
[SQL]View PlainCopy
- 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)
second, get an index that has not been used.
[SQL]View PlainCopy
- 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)
third, retrieve the table scan information below the specified database and filter out queries that are greater than 10 execution times.
[SQL]View PlainCopy
- Mysql> SELECT * from statement_analysis WHERE db=' new_feature ' and full_scan = ' * ' and exe C_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)
Finally, the same continues above, filtering out the query with the temporary table,
[SQL]View PlainCopy
- 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)
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
- 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)
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
