The SYS table in the mysql5.7 is detailed

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. mysql5.7 adds the SYS system database, which provides a quick overview of the system's meta-data. This library does make it easy for DBAs to discover a lot of information about a database, and it can help to solve performance bottlenecks. This library is the default in mysql5.7 and can be imported manually above the mysql5.6 version, and the database package should be found on GitHub itself. 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 to take information from Information_schema: 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!

B, optimistic method, suppose the table exists, write a stored procedure: (too troublesome) now only need to use: SELECT IF (@v_is_exists = ", ' Not exists! ', @v_is_exists) as ' result ';

Result
-------------
Not exists!

Second, get the unused index SELECT * from schema_unused_indexes;

Third, retrieve the table scan information below the specified database and filter out queries that are greater than 10 execution times.

SELECT * from statement_analysis WHERE db= ' new_feature ' and Full_scan = ' * ' and Exec_count > 10

SELECT * from statement_analysis WHERE db=' new_feature ' and tmp_tables > 0 ORDER by Tmp_tables DESC LIMIT

Five, retrieve the top five execution count statements

SELECT statement,total from User_summary_by_statement_type WHERE ' user ' = ' root ' ORDER by total DESC LIMIT 5;

What is included in this library? This library is a view of the form of Information_schema and Performance_schema together, query a more understandable data stored procedures can perform some performance aspects of configuration, You can also get some performance diagnostic report content store function can query some performance information analyze each view and table before you explain: about band without x$, remove x$ with the same name their data is the same, the difference is that the units without x$ more in line with direct reading has been converted, and with x$ The original unit (most of which should be MySQL default) is used for some tools to exist, and the following is a detailed analysis of the SYS library in conjunction with the official MySQL manual
1. Table 1.1 Sys_config Table This is the only table that exists on this system library. First look at the table structure

CREATE TABLE ' sys_config ' (' variable ' varchar) not NULL, ' value ' varchar (+) DEFAULT NULL, ' set_time ' timestamp N OT null default Current_timestamp on UPDATE current_timestamp, ' set_by ' varchar (+) default NULL, PRIMARY KEY (' Variabl E ')) Engine=innodb DEFAULT Charset=utf8

Variable configuration option name

        value     configuration option values         Set_time The line configuration modification time       &N Bsp Set_by     The line configuration information modifier, if the installation has not been modified, then this data should be null     table default data is       The session variable for the above value is the Variable field in the @sys.+ table, for example: @sys .statement_truncate_len  can set  @sys. Statement_truncate_len = 32 Temporary change value , this value is always used in the session, and if you want to restore the default value of using the table, you only need to set the session value to null;set  @sys. Statement_truncate_len = Null; diagnostics.allow _i_s_tables  diagnostics.include_raw  these two values are off by default, the former if Open means allow diagnostics () stored procedure to perform the scan Information_ The Schema.tables table, if there are many tables, can be very costly, and the latter will output unprocessed data from the metrics view. Diagnostics () The specific contents of the next face diagnostics () explanation.  statement_performance_analyzer.limit  View the maximum number of rows returned when there is no limit restriction Statement_performance_analyzer.view   (slightly) the above parameter is mysql5.7.9 join   statement_truncate_len   The maximum length of the return value via the format_statement () function   This table is not the default option and there is a @sys.debug parameter that can be manually added to insert into sys_config  (variable, value)  values (' Debug ' ,  ' on ');update sys_config set value =  ' OFF '  WHERE variable =  ' Debug '; set  @sys .debug = null; For details, please refer to the official documentation, no introduction here   about this table there are two triggers 1.1.1 sys_config_insert_set_ User trigger If you add a new row through the INSERT statement, then this trigger will set the Set_by column as the current operator 1.1.2 sys_config_update_set_user trigger              If you add a new row through the UPDATE statement, the trigger will set the Set_by column to the current operator  2. View the following sections only describe the content of the view that does not contain x$  2.1  Host_summary (host profile) resembles the following:  host listens for connected hosts  statements Total number of statements executed by the current host  statement_latency statement wait time (latency) ·  statement_avg_latency execution Statement Average delay time  table_scans table scan count  file_iosio time total  file_io_latency file io Latency ·  current_connections Current number of connections  total_connections total number of links  unique_users the number of unique users for the host  current_ Memory current Account allocated  total_memory_allocated total memory allocated by the host  2.2  the host_summary_by_file_io_type Host host Event_nameio event name Total The event that this host occurs total_latency the host IO event total latency time max_latency The maximum delay time in the host iOS event  2.3 the  host_summary_by_file_io HostTotal host Iosio events Io_latencyio Total delay time  2.4 the host_summary_by_stages  host host · event_ Namestage event name  totalstage event The total number of occurrences  total_latencystage event total delay Time · avg_ Latencystage event average delay time   2.5 the host_summary_by_statement_latency  host host ·  total The total number of statements for this host  total_latency the total delay time of this host  max_latency the maximum latency of the host  lock_latency the lock delay time to wait for the lock ·  rows_sent the total number of rows returned by the host through the statement  rows_examined the number of lines that are returned by the statement on the storage engine  rows_affected The number of total rows affected by the host through the statement  full_ Scans total number of statements scanned   2.6  the host_summary_by_statement_type  host host   Statement last statement event name  totalsql Total number of statements  total_latencysql statement Total delay  max_latency maximum number of SQL statement delays  lock_ Total number of latency lock latencies The total number of rows returned by the  rows_sent statement  rows_examined The row count that is affected by the SQL statement that stores the engine's read rows  rows_affected statement   Full_scans full table Scan statement events Total   2.7 the innodb_buffer_stats_by_schema   This table is the InnoDB cache of the InnoDB engine that is counted through the database  object_schema the database name  allocatedThe total number of bytes allotted to the current database  data bytes allocated to the current database  pages The total number of pages allocated to the current database  pages_hashed the hash pages assigned to the current database  pages_ old  the number of old pages assigned to the current database  rows_cached  the current database cache rows  2.8 the innodb_buffer_stats_by_ Table is the INNODB cache for each table InnoDB engine
 object_schema database name  object_name table name  allocated The total number of bytes allocated to the table  data the number of data bytes allocated for the table   Pages assigned to a table  pages_hashed the number of hash pages assigned to the table  pages_old the  rows_cached table's Row cache for the old pages assigned to the table  2.9 the  innodb_lock_waits This table in fact from the view of the statement is INFORMATION_SCHEMA this database innodb_locks, Innodb_trx the integration of the two tables, can more clearly show the current instance of the lock situation  wait_started Lock Wait time  wait_age How long the lock has been waiting  wait_age_secs displays the time in seconds that the lock has been waiting (add this column in 5.7.9)   Locked_table locked table  locked_index locked index  locked_type lock type  waiting_trx_id waiting transaction ID  waiting_trx _started the number of rows waiting for the transaction to start,  waiting_trx_age has been waiting for the transaction for how long  waiting_trx_rows_locked is waiting for the transaction to be locked  waiting_trx _rows_modified is waiting for the number of row redefinition  waiting_pid the thread that is waiting for the transaction ID  waiting_query the query that is waiting for the lock  waiting_lock_ The ID is waiting for the lock's ID  waiting_lock_mode mode  blocking_trx_id blocking the thread that waits for the lock ID  blocking_pid the threads that are being locked ID   Blocking_query locked query blocking_lock_id blocking lock ID. Blocking_lock_mode blocking lock mode  blocking_trx_started blocking transaction start time ·  blocking_trx_age the time that the blocked transaction has been executed  blocking_trx_rows_locked the number of rows locked by the blocking transaction  blocking_trx_rows_modified The number of rows that are blocking the transaction redefinition  sql_kill_blocking_ Querykill statement kills a running blocking transaction in mysql5.7.9 is added  sql_kill_blocking_connectionkill statement kills a running blocking transaction in a session is added in mysql5.7.9   2.10 the io_by_thread_by_latency This process the main message is to show the IO waiting time through the consumption of IO  user for the current thread. This value is the account that the thread is assigned to, and for the background thread, the total number of the thread's name  totalio event  total_latencyio The total delay of the event  min_latency a single minimum IO event delay ·  avg_latency Average IO latency  max_latency max io latency  thread_id thread ID  processlist_id for the current thread is the ID at this time, Is null for the background

The SYS table in mysql5.7 (GO)