Shulou(Shulou.com)06/01 Report--

MySQL 5.7 introduced a new sys schema, sys is a MySQL built-in system library, after installing MySQL 5.7 version, use mysqld initialization, will automatically create sys library,

The tables, views, functions, and stored procedures in sys library can make it easier and faster to understand some information about MySQL, such as which statements use temporary tables, which SQL does not use indexes, and which SQL does not use indexes.

There are redundant indexes in the schema, SQL with full table scan, IO occupied by users, etc. The data in these views in the.sys library is obtained from the information_schema.

As of now (MySQL 5.7.12), sys has 1 table and 52 views (we can find the information mentioned above from these views, and we expect that in future versions, the number of views in sys library will be

This information can be viewed via show tables; or in information_schema:

select table_name,table_type,engine from

information_schema.tables where

table_schema='sys' order by table_name;

When you use the above method to look at it, you will find a very interesting phenomenon, for example, let's look at part of the output of show tables;:

Above, we can see that these views have two representations, one is host_summary, the other is x$host_summary, the information in these two views is the same,

Only the form is different, the former is easy for us to read, formatted time and byte units, the latter refers to the view name starting with "x$", is to facilitate some tools data processing.

We look at the creation statements of the above two views through the show create table command, and we can find that their creation is the same, except that when defining the io_latency column,

host_summary_by_file_io uses a function sys.format_time to format the time format:

`format_time`(sum(`performance_schema`.` events_

waits_summary_by_host_by_event_name`.` SUM_TI

MER_WAIT`)) AS `io_latency`

Functions like sys.format_time, which are also created with the sys library, can be viewed with the following command:

select

routine_schema,routine_name,routine_type

from information_schema.routines where

routine_type='FUNCTION';

We can also use show function status where db='sys'\G to see how these functions are created, what they do, how they are used, etc.

We can get help from the official manual on what information these views carry, which is worth spending some time studying, for example, we can find it in schema_redundant_indexes

View redundant indexes in the database in statements_with_full_table_scans

SQL：http://dev.mysql.com/doc/refman/5.7/en/sys-schema-views.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.