MySQL5.7SYS system SCHEMA

MySQL5.7SYS system SCHEMA
Before explaining the system database, let's take a look at the evolution history of MySQL in terms of data dictionary:
MySQL4.1 provides the information_schema data dictionary. You can use SQL statements to retrieve the required system metadata.
MySQL5.5 provides the performance_schema performance dictionary. However, this dictionary is professional, and the average person may not be able to look at it.
MySQL5.7 provides the sys system database. The sys database contains a series of stored procedures, user-defined functions, and views to help us quickly understand the metadata of the system.

The sys System database integrates the information_schema and performance_schema data, making it easier to retrieve metadata. Now, I will demonstrate how to use it quickly in several scenarios.

First,
For example, if you want to know whether a table exists or not, you can use either of the following methods:
A. pessimistic method: Write SQL statements to get information from 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. Optimistic method. If the table exists, write a stored procedure:

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 ;

Call now:

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 can directly call the existing stored procedures in the sys database,

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, retrieve unused indexes.

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 under the specified database and filter out the queries with execution times greater than 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: 9594rows_examined_avg: 369    rows_affected: 0rows_affected_avg: 0       tmp_tables: 0  tmp_disk_tables: 0      rows_sorted: 0sort_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: 852rows_examined_avg: 71    rows_affected: 0rows_affected_avg: 0       tmp_tables: 24  tmp_disk_tables: 0      rows_sorted: 140sort_merge_passes: 0           digest: 538e506ee0075e040b076f810ccb5f5c       first_seen: 2015-11-16 10:51:17        last_seen: 2015-11-16 11:28:132 rows in set (0.01 sec)

Fourth, filter out queries with temporary tables,

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: 201rows_examined_avg: 101    rows_affected: 0rows_affected_avg: 0       tmp_tables: 332  tmp_disk_tables: 15      rows_sorted: 0sort_merge_passes: 0           digest: ff9bdfb7cf3f44b2da4c52dcde7a7352       first_seen: 2015-11-16 10:24:42        last_seen: 2015-11-16 10:24:421 row in set (0.01 sec)

You can see the details of the above query, and you no longer need to execute show status to manually filter out.

Fifth, search for the top five statements with execution times,

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)

I have written so many examples. I will go to the user manual in detail and explore it myself.