科普文：软件架构数据库系列之【MySQL5.7的系统表梳理】

概叙

MySQL5.7的系统中包含了多个重要的系统表，这些表分布在不同的数据库中，提供了关于数据库结构、权限、性能等关键信息的访问。

mysql> \s;
--------------
mysql  Ver 14.14 Distrib 5.7.21, for Win64 (x86_64)

Connection id:          3
Current database:
Current user:           root@
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.7.21-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3307
Uptime:                 19 sec

Threads: 1  Questions: 9  Slow queries: 0  Opens: 99  Flush tables: 1  Open tables: 94  Queries per second avg: 0.473
--------------

ERROR:
No query specified

mysql> show variables like  '%switch%'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set, 1 warning (0.00 sec)

系统库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql>

以下是MySQL 5.7中一些主要的系统表及其所在数据库的概述：

1. ​‌information_schema‌ mysql元数据库：包含了61个表，提供了访问数据库元数据的方式。这些元数据包括数据库名、表名、列的数据类型、访问权限等。information_schema是一个只读数据库，其中的表实际上是视图，因此用户无法直接修改这些数据。通过查询information_schema中的表，可以获得关于MySQL服务器维护的所有其他数据库的详细信息。

2. ​​‌mysql 数据库：包含了31个表，主要记录了用户自定义数据库中表的元数据信息、锁信息、账户信息、权限信息、主从节点信息、慢日志等等。这个数据库对于分析数据库性能、解决故障非常有帮助，因此应该熟练掌握其中表的作用和提供的信息。

3. ​‌performance_schema‌ 性能数据库：具有87个表，主要记录了数据库的性能数据，如等待事件、阶段、仪器等，这些数据对于监控数据库性能、优化查询非常有用。

4. ​​‌sys‌ 性能数据库：虽然只有一个表，但拥有100个视图，提供了关于MySQL服务器状态和性能的详细信息，包括各种统计和监控视图，帮助用户更好地理解和优化数据库性能。Sys库所有的数据源来自：performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。

通过查询这些系统表，可以获得关于数据库结构、性能、安全等方面的关键信息，从而帮助数据库管理员更好地管理和维护数据库系统‌。

系统库：​‌information_schema‌ mysql元数据库

包含了61个表，提供了访问数据库元数据的方式。这些元数据包括数据库名、表名、列的数据类型、访问权限等。

information_schema是一个只读数据库，其中的表实际上是视图，因此用户无法直接修改这些数据。

通过查询information_schema中的表，可以获得关于MySQL服务器维护的所有其他数据库的详细信息。

 information_schema数据库部分表说明

mysql> use information_schema;
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| OPTIMIZER_TRACE                       |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| INNODB_LOCKS                          |
| INNODB_TRX                            |
| INNODB_SYS_DATAFILES                  |
| INNODB_FT_CONFIG                      |
| INNODB_SYS_VIRTUAL                    |
| INNODB_CMP                            |
| INNODB_FT_BEING_DELETED               |
| INNODB_CMP_RESET                      |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMPMEM_RESET                   |
| INNODB_FT_DELETED                     |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_LOCK_WAITS                     |
| INNODB_TEMP_TABLE_INFO                |
| INNODB_SYS_INDEXES                    |
| INNODB_SYS_TABLES                     |
| INNODB_SYS_FIELDS                     |
| INNODB_CMP_PER_INDEX_RESET            |
| INNODB_BUFFER_PAGE                    |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_SYS_TABLESPACES                |
| INNODB_METRICS                        |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_CMPMEM                         |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_SYS_COLUMNS                    |
| INNODB_SYS_FOREIGN                    |
| INNODB_SYS_TABLESTATS                 |
+---------------------------------------+
61 rows in set (0.00 sec)

mysql>

•SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。
•TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。
•COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。
•STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。
•USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。
•SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。
•TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。
•COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。
•CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。
•COLLATIONS表：提供了关于各字符集的对照信息。
•COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。
•TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。
•KEY_COLUMN_USAGE表：描述了具有约束的键列。
•ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。
•VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。
•TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表。

　　　　　　　　COLUMNS：存储表的字段信息，所有的存储引擎

　　　　　　　　INNODB_SYS_COLUMNS ：存放的是INNODB的元数据， 他是依赖于SYS_COLUMNS这个统计表而存在的。

　　　　　　　　ENGINES ：引擎类型，是否支持这个引擎，描述，是否支持事物，是否支持分布式事务，是否能够支持事物的回滚点

　　　　　　　　EVENTS ：记录MySQL中的事件，类似于定时作业

　　　　　　　　FILES ：这张表提供了有关在MySQL的表空间中的数据存储的文件的信息，文件存储的位置，这个表的数据是从InnoDB in-memory中拉取出来的，所以说这张表本身也是一个内存表，每次重启重新进行拉取。也就是我们下面要说的INNODB_SYS_DATAFILES这张表。还要注意一点的是这张表包含有临时表的信息，所以说和SYS_DATAFILES 这张表是不能够对等的，还是要从INNODB_SYS_DATAFILES看。如果undo表空间也配置是InnoDB 的话，那么也是会被记录下来的。

　　　　　　　　PARAMETERS ：参数表存储了一些存储过程和方法的参数，以及存储过程的返回值信息。存储和方法在ROUTINES里面存储。

　　　　　　　　PLUGINS ：基本上是MySQL的插件信息，是否是活动状态等信息。其实SHOW PLUGINS本身就是通过这张表来拉取的数据

　　　　　　　　ROUTINES：关于存储过程和方法function的一些信息，不过这个信息是不包括用户自定义的，只是系统的一些信息。

　　　　　　　　SCHEMATA：这个表提供了实例下有多少个数据库，而且还有数据库默认的字符集

　　　　　　　　TRIGGERS :这个表记录的就是触发器的信息，包括所有的相关的信息。系统的和自己用户创建的触发器。

　　　　　　　　VIEWS :视图的信息，也是系统的和用户的基本视图信息。

　　　　　　　　TABLES　　这张表毫无疑问了，就是记录的数据库中表的信息，其中包括系统数据库和用户创建的数据库。show table status like 'test1'\G的来源就是这个表；

　　　　　　　　TABLESPACES 却是标注的活跃表空间。 这个表是不提供关于innodb的表空间信息的，对于我们来说并没有太大作用，因为我们生产库是强制INNODB的；

　　　　　　　　INNODB_SYS_TABLES 这张表依赖的是SYS_TABLES数据字典中拉取出来的。此表提供了有关表格的格式和存储特性，包括行格式，压缩页面大小位级别的信息（如适用）提供的是关于INNODB的表空间信息，其实和SYS_TABLESPACES 中的INNODB信息是一致的。
　　　　　　　　STATISTICS：这个表提供的是关于表的索引信息，所有索引的相关信息。
　　　　　　　　INNODB_SYS_INDEXES：提供相关INNODB表的索引的相关信息，和SYS_INDEXES 这个表存储的信息基本是一样的，只不过后者提供的是所有存储引擎的索引信息，后者只提供INNODB表的索引信息。
　　　　　　　　INNODB_SYS_TABLESTATS：这个表就比较重要了，记录的是MySQL的INNODB表信息以及MySQL优化器会预估SQL选择合适的索引信息，其实就是MySQL数据库的统计信息
这个表的记录是记录在内存当中的，是一个内存表，每次重启后就会重新记录，所以只能记录从上次重启后的数据库统计信息。有了这个表，我们对于索引的维护就更加方便了，我们可以查询索引的使用次数，方便清理删除不常用的索引，提高表的更新插入等效率，节省磁盘空间。
　　　　　　　　INNODB_SYS_FIELDS ：这个表记录的是INNODB的表索引字段信息，以及字段的排名
　　　　　　　　INNODB_FT_CONFIG :这张表存的是全文索引的信息
　　　　　　　　INNODB_FT_DEFAULT_STOPWORD：这个表存放的是stopword 的信息,是和全文索引匹配起来使用的，和innodb的 INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD 是相同的，这个STOPWORD必须是在创建索引之前创建，而且必须指定字段为varchar。stopword 也就是我们所说的停止词，全文检索时，停止词列表将会被读取和检索，在不同的字符集和排序方式下，会造成命中失败或者找不到此数据，这取决于停止词的不同的排序方式。我们可以使用这个功能筛选不必要字段。
　　　　　　　　INNODB_FT_INDEX_TABLE：这个表存储的是关于INNODB表有全文索引的索引使用信息的，同样这个表也是要设置innodb_ft_aux_table以后才能够使用的，一般情况下是空的
　　　　　　　　INNODB_FT_INDEX_CACHE ：这张表存放的是插入前的记录信息，也是为了避免DML时候昂贵的索引重组


　　　　　　　　GLOBAL_STATUS ，GLOBAL_VARIABLES，SESSION_STATUS，SESSION_VARIABLES：这四张表分别记录了系统的变量，状态（全局和会话的信息），作为DBA相信大家也都比较熟悉了，而且这几张表也是在系统重启的时候回重新加载的。也就是内存表。

　　　　　　　　PARTITIONS ：MySQL分区表相关的信息，通过这张表我们可以查询到分区的相关信息（数据库中已分区的表，以及分区表的分区和每个分区的数据信息）

　　　　　　　　PROCESSLIST：show processlist其实就是从这个表拉取数据，PROCESSLIST的数据是他的基础。由于是一个内存表，所以我们相当于在内存中查询一样，这些操作都是很快的。

　　　　　　　　INNODB_CMP_PER_INDEX，INNODB_CMP_PER_INDEX_RESET：这两个表存储的是关于压缩INNODB信息表的时候的相关信息,有关整个表和索引信息都有.我们知道对于一个INNODB压缩表来说,不管是数据还是二级索引都是会被压缩的,因为数据本身也可以看作是一个聚集索引。
　　　　　　　　INNODB_CMPMEM ，INNODB_CMPMEM_RESET：这两个表是存放关于MySQL INNODB的压缩页的buffer pool信息，但是要注意一点的就是,用这两个表来收集所有信息的表的时候,是会对性能造成严重的影响的,所以说默认是关闭状态的。如果要打开这个功能的话我们要设置innodb_cmp_per_index_enabled参数为ON状态。
　　　　　　　　INNODB_BUFFER_POOL_STATS ：表提供有关INNODB 的buffer pool相关信息，和show engine innodb status提供的信息是相同的。也是show engine innodb status的信息来源。
　　　　　　　　INNODB_BUFFER_PAGE_LRU，INNODB_BUFFER_PAGE :维护了INNODB LRU LIST的相关信息，详细请见小编笔记innodb buffer pool小解
　　　　　　　　INNODB_BUFFER_PAGE ：这个表就比较屌了，存的是buffer里面缓冲的页数据。查询这个表会对性能产生很严重的影响，千万不要再我们自己的生产库上面执行这个语句，除非你能接受服务短暂的停顿
　　　　　　　　INNODB_SYS_DATAFILES ：这张表就是记录的表的文件存储的位置和表空间的一个对应关系(INNODB)
　　　　　　　　INNODB_TEMP_TABLE_INFO ：这个表会记录所有的INNODB的所有用户使用到的信息，但是只能记录在内存中和没有持久化的信息。
　　　　　　　　INNODB_METRICS ：提供INNODB的各种的性能指数，是对INFORMATION_SCHEMA的补充，收集的是MySQL的系统统计信息。这些统计信息都是可以手动配置打开还是关闭的。有以下参数都是可以控制的：innodb_monitor_enable, innodb_monitor_disable, innodb_monitor_reset, innodb_monitor_reset_all。
　　　　　　　　INNODB_SYS_VIRTUAL :表存储的是INNODB表的虚拟列的信息，当然这个还是比较简单的，在MySQL 5.7中，支持两种Generated Column，即Virtual Generated Column和Stored Generated Column，前者只将Generated Column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将Generated Column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与实际存储一列数据相比并没有优势，因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column。
　　　　　　　　INNODB_CMP，INNODB_CMP_RESET：存储的是关于压缩INNODB信息表的时候的相关信息，详细请见推荐笔记。

 information_schema数据库部相关的命令--show

desc 表名;       // 表信息
show columns from 表名;       // 表字段
describe 表名;       // 表信息
show create table 表名;        // 表创建语句
show create database 数据库名;        // 显示数据库 信息
show table status from 数据库名;        // 数据库状态
show tables或show tables from database_name;       // 显示当前数据库中所有表的名称
show databases;       // 显示mysql中所有数据库的名称
show processlist;       // 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。
show table status;       // 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间
show columns from table_name from database_name;        // 显示表中列名称
show columns from database_name.table_name;        // 显示表中列名称
show grants for user_name@localhost;        // 显示一个用户的权限，显示结果类似于grant 命令
show index from table_name;        // 显示表的索引 show status;解释：显示一些系统特定资源的信息，例如，正在运行的线程数量
show variables;        // 显示系统变量的名称和值 show privileges;解释：显示服务器所支持的不同权限
show create database database_name ;       // 显示create database 语句是否能够创建指定的数据库
show create table table_name;       // 显示create database 语句是否能够创建指定的数据库
show engies;        // 显示安装以后可用的存储引擎和默认引擎。
show innodb status ;        // 显示innoDB存储引擎的状态
show logs;        // 显示BDB存储引擎的日志
show warnings;       //显示最后一个执行的语句所产生的错误、警告和通知
show errors;       // 只显示最后一个执行语句所产生的错误

系统库：​​‌mysql 数据库

包含了31个表，主要记录了用户自定义数据库中表的元数据信息、锁信息、账户信息、权限信息、主从节点信息、慢日志等等。这个数据库对于分析数据库性能、解决故障非常有帮助，因此应该熟练掌握其中表的作用和提供的信息。

存储权限的表有：

SCHEMA_PRIVILEGES：提供了数据库的相关权限，这个表是内存表是从mysql.db中拉去出来的。

TABLE_PRIVILEGES:提供的是表权限相关信息，信息是从 mysql.tables_priv 表中加载的

COLUMN_PRIVILEGES ：这个表可以清楚就能看到表授权的用户的对象，那张表那个库以及授予的是什么权限，如果授权的时候加上with grant option的话，我们可以看得到PRIVILEGE_TYPE这个值必须是YES。

USER_PRIVILEGES:提供的是表权限相关信息，信息是从 mysql.user 表中加载的

通过表我们可以很清晰看得到MySQL授权的层次，SCHEMA，TABLE，COLUMN级别，当然这些都是基于用户来授予的。可以看得到MySQL的授权也是相当的细密的，可以具体到列，这在某一些应用场景下还是很有用的，比如审计等。

　　　　　　　　REFERENTIAL_CONSTRAINTS：这个表提供的外键相关的信息，而且只提供外键相关信息

　　　　　　　　TABLE_CONSTRAINTS ：这个表提供的是 相关的约束信息

　　　　　　　　INNODB_SYS_FOREIGN_COLS ：这个表也是存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的

　　　　　　　　INNODB_SYS_FOREIGN ：存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的，只不过是单独对于INNODB来说的

　　　　　　　　KEY_COLUMN_USAGE：数据库中所有有约束的列都会存下下来，也会记录下约束的名字和类别

1、user表: 用户列、权限列、安全列、资源控制列
2、db表 : 用户列、权限列
3、host表
4、table_priv表
5、columns_priv表
6、proc_priv表

mysql> use mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

mysql>

创建账户/授权

mysql5.7 将创建账户和授权写在一起
grant all privileges on *.* to  tst@% identified by '123456';


mysql8.0之后需要拆开操作
create user 'tst'@'%' identified by '123456';
grant all privileges on *.* to 'tst'@'%' with grant option;
ALTER USER 'tst'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER USER 'tst'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; 

修改密码

方法1： 用SET PASSWORD命令
mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
mysql> FLUSH PRIVILEGES;

方法2：用mysqladmin
mysqladmin -u root password "123456"
#如果root已经设置过密码，采用如下方法
mysqladmin -u root password oldpass "123456"

方法3： 用UPDATE直接编辑mysql.user表
mysql -u root
mysql> use mysql;
mysql> UPDATE user SET Password = PASSWORD('123456') WHERE user = 'root';
mysql> FLUSH PRIVILEGES;


方法4： 忘记root密码的时候，可以skip-grant-tables

mysqld_safe --skip-grant-tables&
或者修改my.ini参数文件
[mysqld]
skip-grant-tables

mysql -u root mysql
mysql> UPDATE user SET password=PASSWORD("") WHERE user='root';
mysql> FLUSH PRIVILEGES;
 
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> SET PASSWORD = PASSWORD('123456');

系统库：​‌performance_schema‌ 性能数据库

具有87个表，主要记录了数据库的性能数据，如等待事件、阶段、仪器等，这些数据对于监控数据库性能、优化查询非常有用。

Mysql优化相关的一些表：

OPTIMIZER_TRACE ：提供的是优化跟踪功能产生的信息.关于这个我也谢了做了一个小测试

PROFILING：SHOW PROFILE可以深入的查看服务器执行语句的工作情况。以及也能帮助你理解执行语句消耗时间的情况。一些限制是它没有实现的功能，不能查看和剖析其他连接的语句，以及剖析时所引起的消耗。SHOW PROFILES显示最近发给服务器的多条语句，条数根据会话变量profiling_history_size定义，默认是15，最大值为100。设为0等价于关闭分析功能。详细信息请见MySQL profile

INNODB_FT_BEING_DELETED,INNODB_FT_DELETED: INNODB_FT_BEING_DELETED 这张表是INNODB_FT_DELETED的一个快照,只在OPTIMIZE TABLE 的时候才会使用。

Mysql事务和锁的一些表：

INNODB_LOCKS:现在获取的锁，但是不含没有获取的锁，而且只是针对INNODB的。

INNODB_LOCK_WAITS：系统锁等待相关信息，包含了阻塞的一行或者多行的记录，而且还有锁请求和被阻塞改请求的锁信息等。

INNODB_TRX：包含了所有正在执行的的事物相关信息（INNODB），而且包含了事物是否被阻塞或者请求锁。

1.开启mysql性能模式

　　　查看　　mysql> SHOW VARIABLES LIKE 'performance_schema';

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set, 1 warning (0.00 sec)

　　　修改　　在配置文件中，添加内容：

　　　　　　　　　　　　[mysqld]

　　　　　　　　　　　　performance_schema=ON

2.performance_schema库中表的分类

--语句事件记录表，这些表记录了语句事件信息，当前语句事件表events_statements_current、历史语句事件表events_statements_history和长语句历史事件表events_statements_history_long、以及聚合后的摘要表summary，其中，summary表还可以根据帐号(account)，主机(host)，程序(program)，线程(thread)，用户(user)和全局(global)再进行细分)
show tables like '%statement%';
 
--等待事件记录表，与语句事件类型的相关记录表类似：
show tables like '%wait%';
 
--阶段事件记录表，记录语句执行的阶段事件的表
show tables like '%stage%';
 
--事务事件记录表，记录事务相关的事件的表
show tables like '%transaction%';
 
--监控文件系统层调用的表
show tables like '%file%';
 
--监视内存使用的表
show tables like '%memory%';
 
--动态对performance_schema进行配置的配置表
show tables like '%setup%';

3.实际分析sql　

--1、哪类的SQL执行最多？
SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--2、哪类SQL的平均响应时间最多？
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--3、哪类SQL排序记录数最多？
SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--4、哪类SQL扫描记录数最多？
SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--5、哪类SQL使用临时表最多？
SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--6、哪类SQL返回结果集最多？
SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--7、哪个表物理IO最多？
SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
--8、哪个表逻辑IO最多？
SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC
--9、哪个索引访问最多？
SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC
--10、哪个索引从来没有用过？
SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
--11、哪个等待事件消耗时间最多？
SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC
--12-1、剖析某条SQL的执行情况，包括statement信息，stege信息，wait信息
SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';
--12-2、查看每个阶段的时间消耗
SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;
--12-3、查看每个阶段的锁等待情况
SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_longWHERE nesting_event_id = 1553;

系统库：​​‌sys‌ 性能数据库

虽然只有一个表，但拥有100个视图，提供了关于MySQL服务器状态和性能的详细信息，包括各种统计和监控视图，帮助用户更好地理解和优化数据库性能。Sys库所有的数据源来自：performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。

mysql> use sys
Database changed
mysql> show tables;
+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| host_summary                                  |
| host_summary_by_file_io                       |
| host_summary_by_file_io_type                  |
| host_summary_by_stages                        |
| host_summary_by_statement_latency             |
| host_summary_by_statement_type                |
| innodb_buffer_stats_by_schema                 |
| innodb_buffer_stats_by_table                  |
| innodb_lock_waits                             |
| io_by_thread_by_latency                       |
| io_global_by_file_by_bytes                    |
| io_global_by_file_by_latency                  |
| io_global_by_wait_by_bytes                    |
| io_global_by_wait_by_latency                  |
| latest_file_io                                |
| memory_by_host_by_current_bytes               |
| memory_by_thread_by_current_bytes             |
| memory_by_user_by_current_bytes               |
| memory_global_by_current_bytes                |
| memory_global_total                           |
| metrics                                       |
| processlist                                   |
| ps_check_lost_instrumentation                 |
| schema_auto_increment_columns                 |
| schema_index_statistics                       |
| schema_object_overview                        |
| schema_redundant_indexes                      |
| schema_table_lock_waits                       |
| schema_table_statistics                       |
| schema_table_statistics_with_buffer           |
| schema_tables_with_full_table_scans           |
| schema_unused_indexes                         |
| session                                       |
| session_ssl_status                            |
| statement_analysis                            |
| statements_with_errors_or_warnings            |
| statements_with_full_table_scans              |
| statements_with_runtimes_in_95th_percentile   |
| statements_with_sorting                       |
| statements_with_temp_tables                   |
| sys_config                                    |
| user_summary                                  |
| user_summary_by_file_io                       |
| user_summary_by_file_io_type                  |
| user_summary_by_stages                        |
| user_summary_by_statement_latency             |
| user_summary_by_statement_type                |
| version                                       |
| wait_classes_global_by_avg_latency            |
| wait_classes_global_by_latency                |
| waits_by_host_by_latency                      |
| waits_by_user_by_latency                      |
| waits_global_by_latency                       |
| x$host_summary                                |
| x$host_summary_by_file_io                     |
| x$host_summary_by_file_io_type                |
| x$host_summary_by_stages                      |
| x$host_summary_by_statement_latency           |
| x$host_summary_by_statement_type              |
| x$innodb_buffer_stats_by_schema               |
| x$innodb_buffer_stats_by_table                |
| x$innodb_lock_waits                           |
| x$io_by_thread_by_latency                     |
| x$io_global_by_file_by_bytes                  |
| x$io_global_by_file_by_latency                |
| x$io_global_by_wait_by_bytes                  |
| x$io_global_by_wait_by_latency                |
| x$latest_file_io                              |
| x$memory_by_host_by_current_bytes             |
| x$memory_by_thread_by_current_bytes           |
| x$memory_by_user_by_current_bytes             |
| x$memory_global_by_current_bytes              |
| x$memory_global_total                         |
| x$processlist                                 |
| x$ps_digest_95th_percentile_by_avg_us         |
| x$ps_digest_avg_latency_distribution          |
| x$ps_schema_table_statistics_io               |
| x$schema_flattened_keys                       |
| x$schema_index_statistics                     |
| x$schema_table_lock_waits                     |
| x$schema_table_statistics                     |
| x$schema_table_statistics_with_buffer         |
| x$schema_tables_with_full_table_scans         |
| x$session                                     |
| x$statement_analysis                          |
| x$statements_with_errors_or_warnings          |
| x$statements_with_full_table_scans            |
| x$statements_with_runtimes_in_95th_percentile |
| x$statements_with_sorting                     |
| x$statements_with_temp_tables                 |
| x$user_summary                                |
| x$user_summary_by_file_io                     |
| x$user_summary_by_file_io_type                |
| x$user_summary_by_stages                      |
| x$user_summary_by_statement_latency           |
| x$user_summary_by_statement_type              |
| x$wait_classes_global_by_avg_latency          |
| x$wait_classes_global_by_latency              |
| x$waits_by_host_by_latency                    |
| x$waits_by_user_by_latency                    |
| x$waits_global_by_latency                     |
+-----------------------------------------------+
101 rows in set (0.01 sec)

mysql>

常用性能查询sql

Sys库下有两种表

　　　　　　字母开头：　　适合人阅读，显示是格式化的数据

　　　　　　x$开头：　　适合工具采集数据，原始类数据

　　　　1.按 host 分组统计视图

　　　　　　　　host_summary_by_file_io
　　　　　　　　host_summary
　　　　　　　　host_summary_by_statement_latency
　　　　2.按 user 分组统计视图
　　　　　　　　user_summary
　　　　　　　　user_summary_by_file_io
　　　　　　　　user_summary_by_statement_latency
　　　　　　　　user_summary_by_statement_type
　　　　3.语句效率统计视图
　　　　　　　　schema_tables_with_full_table_scans
　　　　　　　　statement_analysis
　　　　　　　　statements_with_errors_or_warnings
　　　　　　　　statements_with_full_table_scans
　　　　4.统计信息查询视图
　　　　　　　　schema_auto_increment_columns
　　　　　　　　schema_index_statistics
　　　　　　　　schema_object_overview
　　　　　　　　schema_redundant_indexes
　　　　　　　　schema_table_statistics
　　　　　　　　schema_table_statistics_with_buffer
　　　　　　　　schema_unused_indexes
　　　　5.会话和锁信息查询视图
　　　　　　　　innodb_lock_waits
　　　　　　　　processlist
　　　　　　　　session
　　　　　　　　schema_table_lock_waits
　　　　6.查询系统里执行最多的TOP 10 SQL　　　　　　　　

　　　　　　　　select * from statement_analysis order by exec_count desc limit 10\G;

　　　　7.查询IO最高的表

　　　　　　　　select * from io_global_by_file_by_bytes limit 10;

　　　　8.查询延迟比较严重语句

　　　　　　　　select * from statement_analysis order by avg_latency desc limit 2;

　　　　9.查询使用磁盘临时表的SQL语句　　　　　　　　

　　　　　　　　select db, query, tmp_tables,tmp_disk_tables from statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc limit 20;

　　　　10.查询占用了最多的buffer pool的表

　　　　　　　　select * from innodb_buffer_stats_by_table order by pages desc limit 10;

　　　　11.查询每个库占用多少buffer pool　　　　　　　

　　　　　　　　select * from innodb_buffer_stats_by_schema;　　　　　　　　　　　　　　　　

　　　　12.查询每个连接分配多少内存

　　　　　　　　select b.user, current_count_used,current_allocated, current_avg_alloc, current_max_alloc,total_allocated,current_statement from memory_by_thread_by_current_bytes a,session b where a.thread_id = b.thd_id;