mysql 系统库
一、information_schema
1）MySQL information_schema 详解
information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。什么是元数据呢？元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。
在MySQL中，把 information_schema 看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。在INFORMATION_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。
2）information_schema数据库表粗略分类:
1：关于字符集和排序规则相关的系统表
CHARACTER_SETS ：存储数据库相关字符集信息（memory存储引擎）
COLLATIONS ：字符集对应的排序规则
COLLATION_CHARACTER_SET_APPLICABILITY：就是一个字符集和连线校对的一个对应关系而已
下面我们说一下character sets和collations的区别：
字符集（character sets）存储字符串，是指人类语言中最小的表义符号。例如’A’、’B’等；
排序规则（collations）规则比较字符串，collations是指在同一字符集内字符之间的比较规则
每个字符序唯一对应一种字符集，但一个字符集可以对应多种字符序，其中有一个是默认字符序(Default Collation)
MySQL中的字符序名称遵从命名惯例：以字符序对应的字符集名称开头；以_ci(表示大小写不敏感)、_cs(表示大小写敏感)或_bin(表示按编码值比较)结尾。例如：在字符序“utf8_general_ci”下，字符“a”和“A”是等价的
看一下有关于字符集和校对相关的MySQL变量：
character_set_server：默认的内部操作字符集
character_set_client：客户端来源数据使用的字符集
character_set_connection：连接层字符集
character_set_results：查询结果字符集
character_set_database：当前选中数据库的默认字符集
character_set_system：系统元数据(字段名等)字符集
再看一下MySQL中的字符集转换过程：
（1）. MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection；
（2）. 进行内部操作前将请求数据从character_set_connection转换为内部操作字符集，其确定方法如下：
使用每个数据字段的CHARACTER SET设定值；
若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展，非SQL标准)；
若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET设定值；
若上述值不存在，则使用character_set_server设定值。
（3）. 将操作结果从内部操作字符集转换为character_set_results。
2：权限相关的一些表：
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的授权也是相当的细密的，可以具体到列，这在某一些应用场景下还是很有用的，比如审计等。
3：存储数据库系统的实体对象的一些表：
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 :视图的信息，也是系统的和用户的基本视图信息。
这些表存储的都是一些数据库的实体对象，方便我们进行查询和管理，对于一个DBA来说，这些表能够大大方便我们的工作，更快更方便的了结和查询数据库的相关信息。
4：约束外键等相关的一些表：
REFERENTIAL_CONSTRAINTS：这个表提供的外键相关的信息，而且只提供外键相关信息
TABLE_CONSTRAINTS ：这个表提供的是 相关的约束信息
INNODB_SYS_FOREIGN_COLS ：这个表也是存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的
INNODB_SYS_FOREIGN ：存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的，只不过是单独对于INNODB来说的
KEY_COLUMN_USAGE：数据库中所有有约束的列都会存下下来，也会记录下约束的名字和类别
为什么要把外键和约束单列出来呢，因为感觉这是一块独立的东西，虽然我们的生产环境大部分都不会使用外键，因为这会降低性能，但是合理的利用约束还是一个不错的选择，比如唯一约束。
5：关于管理的一些表：
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_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信息表的时候的相关信息，详细请见推荐笔记。
为什么把这些表列为管理相关的表呢，因为我感觉像连接，分区，压缩表，innodb buffer pool等表，我们通过这些表都能很清晰的看到自己数据库的相关功能的状态，特别是我们通过一些变量更容易窥透MySQL的运行状态，方便我们进行管理。
6：关于表信息和索引信息的一些表
TABLES，TABLESPACES，INNODB_SYS_TABLES ，INNODB_SYS_TABLESPACES ：
TABLES这张表毫无疑问了，就是记录的数据库中表的信息，其中包括系统数据库和用户创建的数据库。show table status like ‘test1’\G的来源就是这个表；
字段 名称 说明
table_schema 数据库名称
table_name 数据表名称
table_type 数据表类型
engine 引擎
table_rows 数据表记录总数 一般小于真实记录数
AVG_ROW_LENGTH 表中行的平均行（字节）
INDEX_LENGTH 索引的占用空间大小（字节）
create_time 建表时间
update_time 修改表时间
table_comment 备注
常见用法
1.查看数据库下每张表的磁盘空间占用
SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024/1024," MB") AS size_MB
FROM information_schema.tables WHERE TABLE_SCHEMA='test' order by size_MB DESC ;
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时候昂贵的索引重组
7：关于MySQL优化相关的一些表
OPTIMIZER_TRACE ：提供的是优化跟踪功能产生的信息.关于这个我也谢了做了一个小测试，[MySQL追踪优化器小试]
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 的时候才会使用。详细信息详见我的OPTIMIZE TABLE 小解
8：关于MySQL事物和锁的相关的一些表
INNODB_LOCKS:现在获取的锁，但是不含没有获取的锁，而且只是针对INNODB的。
INNODB_LOCK_WAITS：系统锁等待相关信息，包含了阻塞的一行或者多行的记录，而且还有锁请求和被阻塞改请求的锁信息等。
INNODB_TRX：包含了所有正在执行的的事物相关信息（INNODB），而且包含了事物是否被阻塞或者请求锁。
我们通过这些表就能够很方便的查询出来未结束的事物和被阻塞的进程，这是不是更方便了，详细可见information_schema系列八(事物，锁)
原文链接：https://blog.csdn.net/qq_46138160/article/details/120717638
二、sys库
1）.sys库介绍
Mysql5.7版本自带4个数据库，information_schema、mysql、performance_schema、sys。Sys库所有的数据源来自：performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。sys_开头是库里的配置表，sys_config用于sys schema库的配置
Sys库下有两种表：
- 字母开头： 适合人阅读，显示是格式化的数
- x$开头 ： 适合工具采集数据，原始类数据
sys系统库支持MySQL 5.6或更高版本，要完全访问sys系统库，用户必须具有以下权限：
对所有sys表和视图具有SELECT权限
对所有sys存储过程和函数具有EXECUTE权限
对sys_config表具有INSERT、UPDATE权限
对某些特定的sys系统库存储过程和函数需要额外权限，如，ps_setup_save()存储过程，需要临时表相关的权限
2）.表清单
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) ————————————————
3）表介绍
1、视图表分类介绍
host : 以IP分组相关的统计信息
innodb : innodb buffer 相关信息
io : 数据内不同维度展的IO相关的信息
memory : 以IP，连接，用户，分配的类型分组及总的占用显示内存的使用
metrics : DB的内部的统计值
processlist : 线程相关的信息(包含内部线程及用户连接）
ps_ : 没有工具统计的一些变量（没看出来存在的价值）
schema : 表结构相关的信息，例如： 自增，索引， 表里的每个字段类型，等待的锁等等
session : 用户连接相关的信息
statement : 基于语句的统计信息（重店）
statements_ : 出错的语句，进行全表扫描， 运行时间超长，排序相等（重点）
user_ : 和host_开头的相似，只是以用户分组统计
wait : 等待事件，比较专业，难看懂。
waits : 以IP，用户分组统计出来的一些延迟事件，有一定的参考价值。
2、表格字段说明
host_summary
字段名 意义
host 从哪个服务器上连过来。如果是NULL，表示内部的进程
Statements 这台服务器共执行了多少语句
Statement_latency 这台服务器发来等待语句执行的时间
Statement_avg_latency 该服务器等待语句执行的平均时间
Table_scans 该服务器扫描表的次数（非全表）
File_io 该服务器IO事件请求的次数
File_io_latency 该服务器请求等待IO的时间
Current_connections 该服务器当前的连接数
Total_connections 该服务器总连接DB共连接多少次
Unique_user 该服务器上有几个不同用户名的账户连接过来
Current_memory 该服务器上当前连接等占用的内存
Total_memory_allocated 该服务器上的请求总共使用的内存
Io_global_by_file_by_bytes
字段名 意义
File 被操作的文件名
Count_read 总共有多少次读
Total_read 总共读了多少字节
Avg_read 平均每次读多少字节
Count_write 总共多少次写
Total_written 总共写了多少字节
Avg_write 平均每次写的字节大学
Total 读和写总共的IO大学
Write_pct 写占total里的百分比
User_summary
字段名 意义
User 客户端连接过来的用户名。如果是NULL，表示内部进程
Statements 该用户执行了多少SQL
Statement_latency 该用户执行SQL的总延迟时间
Statement_avg_latency 该用户执行SQL的平均延迟时间
Table_scans 该用户执行SQL时扫描表的次数
File_ios 该用户请求操作用掉的IO
File_io_latency 该用户请求操作的IO总延迟时间
Current_connections 该用户当前的连接数
Total_connections 该用户总的连接数
Unique_hosts 该用户从几个唯一的机器连接过来
Current_memory 该用户当前占用的内存
Total_memory_allocated 该用户总共申请到的内存（累加值）
Memory_global_total
Total_allocated server总共分配出去的内存（应该是server层）
Memory_by_thread_by_current_bytes
字段名 意义
Thread_id 内部线程ID可以和session中的thd_id关联
User 这个线程是哪个用户创建的
Current_count_used 当前使用的内存块还没有释放
Current_allocated 当前分配的内存大小（字节）而且没有被释放出来
Current_avg_alloc 平均分配的blocks
Current_max_alloc 当前线程分配的最多内存
Total_allocated 当前连总共分配的内存大小
Statement_analysis
字段名 意义
Query 归一化的SQL样子
Db 在哪个DB中执行。NULL表示在任何DB
Full_scan 全表扫描的次数
Exec_count 该SQL执行的总次数
Err_count 发生错误的次数
Warn_count 发生警告的次数
Total_latency 总共发生延迟的实际
Max_latency 最大延迟时间
Avg_latency 平均延迟时间
Lock_latency 因锁等待占用的总时间
Rows_sent 执行该SQL返回的总行数
Rows_sent_avg 执行该SQL平均返回的行数
Tmp_tables 该SQL形成内存临时表的总次数
Tmp_disk_tables 该SQL形成文件临时表的总次数
Rows_sorted 该SQL总共排序的行数
Sort_merge_passes 用于排序中合并的总次数
Digest 该语句的hash值
First_screen 该SQL最早出现的时间
Last_screen 该SQL最近出现的时间
Session和processlist视图基本一样，只是把后台线程过滤掉。
Innodb_buffer_stats_by_schema
字段名 意义
Object_schema 库名
Allocated 基于库分配的buffer pool大小
Data 基于schema实际缓存的数据大小
Pages 当前schema缓存的page数
Pages_hashed Buffer pool中进行hash 索引的page
Pages_old Buffer pool中的旧页，可能被置换出去
Rows_cached Buffer pool中以行为单位的缓存
Innodb_buffer_stats_by_table和innodb_buffer_stats_by_schema基本一致。只是比上面多了个object_name指定表名。
更多sys库下的表说明参考：
(12条消息) MySQL系统SYS数据库——各类统计视图整理_ZWZhangYu的博客-CSDN博客_统计视图sql
4）使用示例
1、查询资源使用情况
mysql> Select * from host_summary limit 1\G *************************** 1. row *************************** host: 192.168.0.124 statements: 4 statement_latency: 234.17 us statement_avg_latency: 58.54 us table_scans: 0 file_ios: 0 file_io_latency: 0 ps current_connections: 0 total_connections: 2 unique_users: 1 current_memory: 0 bytes total_memory_allocated: 0 bytes 1 row in set (0.03 sec) mysql> Select * from memory_global_total; ±----------------+ | total_allocated | ±----------------+ | 143.38 MiB | ±----------------+ 1 row in set (0.01 sec) 说明：内存部分，不包括innodbbuffer pool，只是server 层申请的内存 mysql> Select * from io_global_by_file_by_bytes limit 1\G *************************** 1. row *************************** file: @@datadir/ibdata1 count_read: 235 total_read: 5.69 MiB avg_read: 24.78 KiB count_write: 2129 total_written: 63.44 MiB avg_write: 30.51 KiB total: 69.12 MiB write_pct: 91.77 1 row in set (0.01 sec) mysql> Select * from user_summary limit 1\G *************************** 1. row *************************** user: root statements: 375314 statement_latency: 4.32 h statement_avg_latency: 41.40 ms table_scans: 157158 file_ios: 142646 file_io_latency: 2.97 m current_connections: 1 total_connections: 8043 unique_hosts: 2 current_memory: 0 bytes total_memory_allocated: 0 bytes 1 row in set (0.01 sec)
2、查询连接及发送的SQL情况
mysql> select host, current_connections,statements from host_summary; ±--------------±--------------------±-----------+ | host | current_connections | statements | ±--------------±--------------------±-----------+ | 192.168.0.124 | 0 | 4 | | localhost | 1 | 187728 | ±--------------±--------------------±-----------+ 2 rows in set (0.01 sec) mysql> select conn_id, user, current_statement, last_statement from session; ±--------±---------------±------------------------------------------------------------------±---------------+ | conn_id | user | current_statement | last_statement | ±--------±---------------±------------------------------------------------------------------±---------------+ | 10052 | root@localhost | select conn_id, user, current_ … t, last_statement from session | NULL | ±--------±---------------±------------------------------------------------------------------±---------------+ 1 row in set (0.08 sec) ————————————————
3、查询系统里执行最多的TOP 10 SQL
mysql> select * from statement_analysis order by exec_count desc limit 10\G; *************************** 1. row *************************** query: INSERT INTO t1 VALUES (…) db: mysqlslap full_scan: exec_count: 98041 err_count: 0 warn_count: 0 total_latency: 47.17 m max_latency: 1.98 s avg_latency: 28.87 ms lock_latency: 7.68 s rows_sent: 0 rows_sent_avg: 0 rows_examined: 0 rows_examined_avg: 0 rows_affected: 98041 rows_affected_avg: 1 tmp_tables: 0 tmp_disk_tables: 0 rows_sorted: 0 sort_merge_passes: 0 digest: 946acd9cdfe59ae300b995cf0698c7ce first_seen: 2021-02-10 11:30:55 last_seen: 2021-02-10 11:54:28 *************************** 2. row *************************** query: SELECT intcol1 , charcol1 FROM t1 db: mysqlslap full_scan: * … ————————————————
4、查询IO最高的表
mysql> select * from io_global_by_file_by_bytes limit 10; ±------------------------------------------±-----------±-----------±----------±------------±--------------±-----------±-----------±----------+ | file | count_read | total_read | avg_read | count_write | total_written | avg_write | total | write_pct | ±------------------------------------------±-----------±-----------±----------±------------±--------------±-----------±-----------±----------+ | @@datadir/ibdata1 | 235 | 5.69 MiB | 24.78 KiB | 2129 | 63.44 MiB | 30.51 KiB | 69.12 MiB | 91.77 | | @@datadir/ib_logfile1 | 2 | 64.50 KiB | 32.25 KiB | 9972 | 25.25 MiB | 2.59 KiB | 25.31 MiB | 99.75 | | @@datadir/ib_logfile0 | 5 | 4.00 KiB | 819 bytes | 5901 | 15.30 MiB | 2.66 KiB | 15.31 MiB | 99.97 | | @@datadir/ibtmp1 | 0 | 0 bytes | 0 bytes | 72 | 12.94 MiB | 184.00 KiB | 12.94 MiB | 100.00 | | @@datadir/test2/fi_finance_detail_old.ibd | 545 | 8.52 MiB | 16.00 KiB | 0 | 0 bytes | 0 bytes | 8.52 MiB | 0.00 | | @@datadir/test2/fi_finance_detail.ibd | 454 | 7.09 MiB | 16.00 KiB | 0 | 0 bytes | 0 bytes | 7.09 MiB | 0.00 | | @@datadir/test2/macro_data.ibd | 259 | 4.05 MiB | 16.00 KiB | 0 | 0 bytes | 0 bytes | 4.05 MiB | 0.00 | | @@datadir/test2/stock_daily.ibd | 191 | 2.98 MiB | 16.00 KiB | 0 | 0 bytes | 0 bytes | 2.98 MiB | 0.00 | | @@datadir/test2/macro_data_copy1.ibd | 190 | 2.97 MiB | 16.00 KiB | 0 | 0 bytes | 0 bytes | 2.97 MiB | 0.00 | | @@datadir/mysql/innodb_index_stats.ibd | 6 | 96.00 KiB | 16.00 KiB | 36 | 576.00 KiB | 16.00 KiB | 672.00 KiB | 85.71 | ±------------------------------------------±-----------±-----------±----------±------------±--------------±-----------±-----------±----------+
5、查询延迟比较严重语句
mysql> select * from statement_analysis order by avg_latency desc limit 2; ±------------------------------------------------------------------±-----±----------±-----------±----------±-----------±--------------±------------±------------±-------------±----------±--------------±--------------±------------------±--------------±------------------±-----------±----------------±------------±------------------±---------------------------------±--------------------±--------------------+ | query | db | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | lock_latency | rows_sent | rows_sent_avg | rows_examined | rows_examined_avg | rows_affected | rows_affected_avg | tmp_tables | tmp_disk_tables | rows_sorted | sort_merge_passes | digest | first_seen | last_seen | ±------------------------------------------------------------------±-----±----------±-----------±----------±-----------±--------------±------------±------------±-------------±----------±--------------±--------------±------------------±--------------±------------------±-----------±----------------±------------±------------------±---------------------------------±--------------------±--------------------+ | GRANT ALL PRIVILEGES ON `test1` . * TO ? @? | NULL | | 1 | 0 | 0 | 923.08 us | 923.08 us | 923.08 us | 461.00 us | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | b59b9848088068e714ba6de1bd813e9d | 2021-02-09 16:47:08 | 2021-02-09 16:47:08 | | SELECT `t` . `THREAD_ID` AS `t … _NUMBER_OF_BYTES_USED` ) DESC | sys | * | 3 | 0 | 0 | 274.54 ms | 116.47 ms | 91.51 ms | 6.10 ms | 20 | 7 | 30939 | 10313 | 0 | 0 | 15 | 7 | 200 | 0 | b3ea91361b876a2dba55fdce3df2ee23 | 2021-02-13 07:59:57 | 2021-02-13 08:10:46 | ±------------------------------------------------------------------±-----±----------±-----------±----------±-----------±--------------±------------±------------±-------------±----------±--------------±--------------±------------------±--------------±------------------±-----------±----------------±------------±------------------±---------------------------------±--------------------±--------------------+ 2 rows in set (0.00 sec)
6、查询使用了磁盘临时表的SQL语句
mysql> 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; ±-------------------±------------------------------------------------------------------±-----------±----------------+ | db | query | tmp_tables | tmp_disk_tables | ±-------------------±------------------------------------------------------------------±-----------±----------------+ | sys | SELECT `t` . `THREAD_ID` AS `t … _NUMBER_OF_BYTES_USED` ) DESC | 15 | 7 | | sys | SELECT IF ( `isnull` ( `perfor … _host_by_event_name` GROUP BY | 16 | 2 | | sys | SELECT IF ( `isnull` ( `perfor … _user_by_event_name` GROUP BY | 9 | 1 | | sys | SELECT IF ( ( `locate` ( ? , ` … . `COMPRESSED_SIZE` ) ) DESC | 4 | 3 | | sys | SELECT IF ( ( `locate` ( ? , ` … . `COMPRESSED_SIZE` ) ) DESC | 4 | 3 | | sys | SHOW TABLES | 5 | 0 | | NULL | SHOW SCHEMAS | 4 | 0 | | NULL | SHOW VARIABLES LIKE ? | 4 | 0 | | sys | SHOW TABLES LIKE ? | 4 | 0 | | sys | SHOW SCHEMAS | 3 | 0 | | test2 | SHOW TABLES | 2 | 0 | | mysql | SHOW SCHEMAS | 2 | 0 | | test1 | SHOW TABLES | 2 | 0 | | performance_schema | SHOW TABLES | 2 | 0 | | sys | SELECT `sys` . `format_bytes` … summary_global_by_event_name` | 1 | 1 | | test2 | SHOW SCHEMAS | 1 | 0 | | mysql | SHOW TABLES | 1 | 0 | | test1 | SHOW SCHEMAS | 1 | 0 | | sys | SHOW VARIABLES LIKE ? | 1 | 0 | | performance_schema | SHOW SCHEMAS | 1 | 0 | ±-------------------±------------------------------------------------------------------±-----------±----------------+ 20 rows in set (0.00 sec)
7、查询占用了最多的buffer pool的表
mysql> select * from innodb_buffer_stats_by_table order by pages desc limit 10; ±--------------±----------------------±-----------±-----------±------±-------------±----------±------------+ | object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached | ±--------------±----------------------±-----------±-----------±------±-------------±----------±------------+ | test2 | fi_finance_detail_old | 8.48 MiB | 7.73 MiB | 543 | 0 | 201 | 23448 | | test2 | fi_finance_detail | 7.06 MiB | 6.46 MiB | 452 | 0 | 75 | 17179 | | test2 | macro_data | 4.02 MiB | 3.68 MiB | 257 | 0 | 225 | 45098 | | test2 | stock_daily | 2.95 MiB | 2.68 MiB | 189 | 0 | 189 | 49946 | | test2 | macro_data_copy1 | 2.94 MiB | 2.69 MiB | 188 | 0 | 188 | 34261 | | InnoDB System | SYS_TABLES | 1.45 MiB | 1.27 MiB | 93 | 0 | 1 | 4234 | | test1 | tb_9002 | 416.00 KiB | 316.07 KiB | 26 | 0 | 26 | 1352 | | test2 | macro_policy | 288.00 KiB | 224.03 KiB | 18 | 0 | 18 | 155 | | mysql | help_topic | 176.00 KiB | 126.44 KiB | 11 | 0 | 11 | 239 | | test1 | tb_9001 | 144.00 KiB | 96.00 KiB | 9 | 0 | 9 | 295 | ±--------------±----------------------±-----------±-----------±------±-------------±----------±------------+ 10 rows in set (0.03 sec)
8、查询每个库占用多少buffer pool
mysql> select * from innodb_buffer_stats_by_schema; ±--------------±-----------±-----------±------±-------------±----------±------------+ | object_schema | allocated | data | pages | pages_hashed | pages_old | rows_cached | ±--------------±-----------±-----------±------±-------------±----------±------------+ | test2 | 26.42 MiB | 23.85 MiB | 1691 | 0 | 928 | 86357 | | test1 | 672.00 KiB | 464.52 KiB | 42 | 0 | 42 | 840 | | mysql | 656.00 KiB | 245.75 KiB | 41 | 0 | 36 | 1267 | | InnoDB System | 304.00 KiB | 98.92 KiB | 19 | 0 | 7 | 235 | | sys | 16.00 KiB | 338 bytes | 1 | 0 | 1 | 6 | ±--------------±-----------±-----------±------±-------------±----------±------------+ 5 rows in set (0.07 sec)
9、查询每个连接分配多少内存
mysql> 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; ±---------------±-------------------±------------------±------------------±------------------±----------------±------------------------------------------------------------------+ | user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated | current_statement | ±---------------±-------------------±------------------±------------------±------------------±----------------±------------------------------------------------------------------+ | root@localhost | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | select b.user, current_count_u … b where a.thread_id = b.thd_id | ±---------------±-------------------±------------------±------------------±------------------±----------------±------------------------------------------------------------------+ 1 row in set (0.12 sec)
10、查询MySQL内部现在有多个线程在运行
mysql> select user, count() from processlist group by user; ±--------------------------------±---------+ | user | count() | ±--------------------------------±---------+ | innodb/buf_dump_thread | 1 | | innodb/dict_stats_thread | 1 | | innodb/io_ibuf_thread | 1 | | innodb/io_log_thread | 1 | | innodb/io_read_thread | 4 | | innodb/io_write_thread | 4 | | innodb/page_cleaner_thread | 1 | | innodb/srv_error_monitor_thread | 1 | | innodb/srv_lock_timeout_thread | 1 | | innodb/srv_master_thread | 1 | | innodb/srv_monitor_thread | 1 | | innodb/srv_purge_thread | 1 | | innodb/srv_worker_thread | 3 | | root@localhost | 1 | | sql/compress_gtid_table | 1 | | sql/main | 1 | | sql/signal_handler | 1 | | sql/thread_timer_notifier | 1 | ±--------------------------------±---------+ 18 rows in set (0.08 sec)
原文链接：https://blog.csdn.net/carefree2005/article/details/113798841
三、performance_schema库
1).performance_schema的介绍
MySQL的performance schema 用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况。
特点如下：
1、提供了一种在数据库运行时实时检查server的内部执行情况的方法。performance_schema 数据库中的表使用performance_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关的数据，与information_schema不同，information_schema主要关注server运行过程中的元数据信息
2、performance_schema通过监视server的事件来实现监视server内部运行情况， “事件”就是server内部活动中所做的任何事情以及对应的时间消耗，利用这些信息来判断server中的相关资源消耗在了哪里？一般来说，事件可以是函数调用、操作系统的等待、SQL语句执行的阶段（如sql语句执行过程中的parsing 或 sorting阶段）或者整个SQL语句与SQL语句集合。事件的采集可以方便的提供server中的相关存储引擎对磁盘文件、表I/O、表锁等资源的同步调用信息。
3、performance_schema中的事件与写入二进制日志中的事件（描述数据修改的events）、事件计划调度程序（这是一种存储程序）的事件不同。performance_schema中的事件记录的是server执行某些活动对某些资源的消耗、耗时、这些活动执行的次数等情况。
4、performance_schema中的事件只记录在本地server的performance_schema中，其下的这些表中数据发生变化时不会被写入binlog中，也不会通过复制机制被复制到其他server中。
5、 当前活跃事件、历史事件和事件摘要相关的表中记录的信息。能提供某个事件的执行次数、使用时长。进而可用于分析某个特定线程、特定对象（如mutex或file）相关联的活动。
6、PERFORMANCE_SCHEMA存储引擎使用server源代码中的“检测点”来实现事件数据的收集。对于performance_schema实现机制本身的代码没有相关的单独线程来检测，这与其他功能（如复制或事件计划程序）不同
7、收集的事件数据存储在performance_schema数据库的表中。这些表可以使用SELECT语句查询，也可以使用SQL语句更新performance_schema数据库中的表记录（如动态修改performance_schema的setup_*开头的几个配置表，但要注意：配置表的更改会立即生效，这会影响数据收集）
8、performance_schema的表中的数据不会持久化存储在磁盘中，而是保存在内存中，一旦服务器重启，这些数据会丢失（包括配置表在内的整个performance_schema下的所有数据）
9、MySQL支持的所有平台中事件监控功能都可用，但不同平台中用于统计事件时间开销的计时器类型可能会有所差异
1、performance schema入门
在mysql的5.7版本中，性能模式是默认开启的，如果想要显式的关闭的话需要修改配置文件，不能直接进行修改，会报错Variable ‘performance_schema’ is a read only variable。
--查看performance_schema的属性 mysql> SHOW VARIABLES LIKE 'performance_schema'; --在配置文件中修改performance_schema的属性值，on表示开启，off表示关闭 [mysqld] performance_schema=ON --切换数据库 use performance_schema; --查看当前数据库下的所有表,会看到有很多表存储着相关的信息 show tables; --可以通过show create table tablename来查看创建表的时候的表结构 mysql> show create table setup_consumers;
必须要理解的两个基本概念：
instruments: 生产者，用于采集mysql中各种各样的操作产生的事件信息，对应配置表中的配置项我们可以称为监控采集配置项。
consumers:消费者，对应的消费者表用于存储来自instruments采集的数据，对应配置表中的配置项我们可以称为消费存储配置项。
2.表清单
accounts
cond_instances
events_stages_current
events_stages_history
events_stages_history_long
events_stages_summary_by_account_by_event_name
events_stages_summary_by_host_by_event_name
events_stages_summary_by_thread_by_event_name
events_stages_summary_by_user_by_event_name
events_stages_summary_global_by_event_name
events_statements_current
events_statements_history
events_statements_history_long
events_statements_summary_by_account_by_event_name
events_statements_summary_by_digest
events_statements_summary_by_host_by_event_name
events_statements_summary_by_program
events_statements_summary_by_thread_by_event_name
events_statements_summary_by_user_by_event_name
events_statements_summary_global_by_event_name
events_transactions_current
events_transactions_history
events_transactions_history_long
events_transactions_summary_by_account_by_event_name
events_transactions_summary_by_host_by_event_name
events_transactions_summary_by_thread_by_event_name
events_transactions_summary_by_user_by_event_name
events_transactions_summary_global_by_event_name
events_waits_current
events_waits_history
events_waits_history_long
events_waits_summary_by_account_by_event_name
events_waits_summary_by_host_by_event_name
events_waits_summary_by_instance
events_waits_summary_by_thread_by_event_name
events_waits_summary_by_user_by_event_name
events_waits_summary_global_by_event_name
file_instances
file_summary_by_event_name
file_summary_by_instance
global_status
global_variables
host_cache
hosts
memory_summary_by_account_by_event_name
memory_summary_by_host_by_event_name
memory_summary_by_thread_by_event_name
memory_summary_by_user_by_event_name
memory_summary_global_by_event_name
metadata_locks
mutex_instances
objects_summary_global_by_type
performance_timers
prepared_statements_instances
replication_applier_configuration
replication_applier_status
replication_applier_status_by_coordinator
replication_applier_status_by_worker
replication_connection_configuration
replication_connection_status
replication_group_member_stats
replication_group_members
rwlock_instances
session_account_connect_attrs
session_connect_attrs
session_status
session_variables
setup_actors
setup_consumers
setup_instruments
setup_objects
setup_timers
socket_instances
socket_summary_by_event_name
socket_summary_by_instance
status_by_account
status_by_host
status_by_thread
status_by_user
table_handles
table_io_waits_summary_by_index_usage
table_io_waits_summary_by_table
table_lock_waits_summary_by_table
threads
user_variables_by_thread
users
variables_by_thread
3.performance_schema表的分类
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%';
4.performance_schema的简单配置与使用
数据库刚刚初始化并启动时，并非所有instruments(事件采集项，在采集项的配置表中每一项都有一个开关字段，或为YES，或为NO)和consumers(与采集项类似，也有一个对应的事件类型保存表配置项，为YES就表示对应的表保存性能数据，为NO就表示对应的表不保存性能数据)都启用了，所以默认不会收集所有的事件，可能你需要检测的事件并没有打开，需要进行设置，可以使用如下两个语句打开对应的instruments和consumers（行计数可能会因MySQL版本而异)。
--打开等待事件的采集器配置项开关，需要修改setup_instruments配置表中对应的采集器配置项 UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES'where name like 'wait%'; --打开等待事件的保存表配置开关，修改setup_consumers配置表中对应的配置项 UPDATE setup_consumers SET ENABLED = 'YES'where name like '%wait%'; --当配置完成之后可以查看当前server正在做什么，可以通过查询events_waits_current表来得知，该表中每个线程只包含一行数据，用于显示每个线程的最新监视事件 select * from events_waits_current\G *************************** 1. row *************************** THREAD_ID: 11 EVENT_ID: 570 END_EVENT_ID: 570 EVENT_NAME: wait/synch/mutex/innodb/buf_dblwr_mutex SOURCE: TIMER_START: 4508505105239280 TIMER_END: 4508505105270160 TIMER_WAIT: 30880 SPINS: NULL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL INDEX_NAME: NULL OBJECT_TYPE: NULL OBJECT_INSTANCE_BEGIN: 67918392 NESTING_EVENT_ID: NULL NESTING_EVENT_TYPE: NULL OPERATION: lock NUMBER_OF_BYTES: NULL FLAGS: NULL /*该信息表示线程id为11的线程正在等待buf_dblwr_mutex锁，等待事件为30880 属性说明： id:事件来自哪个线程，事件编号是多少 event_name:表示检测到的具体的内容 source:表示这个检测代码在哪个源文件中以及行号 timer_start:表示该事件的开始时间 timer_end:表示该事件的结束时间 timer_wait:表示该事件总的花费时间 注意：_current表中每个线程只保留一条记录，一旦线程完成工作，该表中不会再记录该线程的事件信息 */ /* _history表中记录每个线程应该执行完成的事件信息，但每个线程的事件信息只会记录10条，再多就会被覆盖，*_history_long表中记录所有线程的事件信息，但总记录数量是10000，超过就会被覆盖掉 */ select thread_id,event_id,event_name,timer_wait from events_waits_history order by thread_id limit 21; /* summary表提供所有事件的汇总信息，该组中的表以不同的方式汇总事件数据（如：按用户，按主机，按线程等等）。例如：要查看哪些instruments占用最多的时间，可以通过对events_waits_summary_global_by_event_name表的COUNT_STAR或SUM_TIMER_WAIT列进行查询（这两列是对事件的记录数执行COUNT（*）、事件记录的TIMER_WAIT列执行SUM（TIMER_WAIT）统计而来） */ SELECT EVENT_NAME,COUNT_STAR FROM events_waits_summary_global_by_event_name ORDER BY COUNT_STAR DESC LIMIT 10; /* instance表记录了哪些类型的对象会被检测。这些对象在被server使用时，在该表中将会产生一条事件记录，例如，file_instances表列出了文件I/O操作及其关联文件名 */ select * from file_instances limit 20;
5、常用配置项的参数说明
1.启动选项 performance_schema_consumer_events_statements_current=TRUE 是否在mysql server启动时就开启events_statements_current表的记录功能(该表记录当前的语句事件信息)，启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新setup_consumers配置表中的events_statements_current配置项，默认值为TRUE performance_schema_consumer_events_statements_history=TRUE 与performance_schema_consumer_events_statements_current选项类似，但该选项是用于配置是否记录语句事件短历史信息，默认为TRUE performance_schema_consumer_events_stages_history_long=FALSE 与performance_schema_consumer_events_statements_current选项类似，但该选项是用于配置是否记录语句事件长历史信息，默认为FALSE 除了statement(语句)事件之外，还支持：wait(等待)事件、state(阶段)事件、transaction(事务)事件，他们与statement事件一样都有三个启动项分别进行配置，但这些等待事件默认未启用，如果需要在MySQL Server启动时一同启动，则通常需要写进my.cnf配置文件中 performance_schema_consumer_global_instrumentation=TRUE 是否在MySQL Server启动时就开启全局表（如：mutex_instances、rwlock_instances、cond_instances、file_instances、users、hostsaccounts、socket_summary_by_event_name、file_summary_by_instance等大部分的全局对象计数统计和事件汇总统计信息表 ）的记录功能，启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新全局配置项 默认值为TRUE performance_schema_consumer_statements_digest=TRUE 是否在MySQL Server启动时就开启events_statements_summary_by_digest 表的记录功能，启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新digest配置项 默认值为TRUE performance_schema_consumer_thread_instrumentation=TRUE 是否在MySQL Server启动时就开启 events_xxx_summary_by_yyy_by_event_name表的记录功能，启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新线程配置项 默认值为TRUE performance_schema_instrument[=name] 是否在MySQL Server启动时就启用某些采集器，由于instruments配置项多达数千个，所以该配置项支持key-value模式，还支持%号进行通配等，如下: # [=name]可以指定为具体的Instruments名称（但是这样如果有多个需要指定的时候，就需要使用该选项多次），也可以使用通配符，可以指定instruments相同的前缀+通配符，也可以使用%代表所有的instruments ## 指定开启单个instruments --performance-schema-instrument= 'instrument_name=value' ## 使用通配符指定开启多个instruments --performance-schema-instrument= 'wait/synch/cond/%=COUNTED' ## 开关所有的instruments --performance-schema-instrument= '%=ON' --performance-schema-instrument= '%=OFF' 注意，这些启动选项要生效的前提是，需要设置performance_schema=ON。另外，这些启动选项虽然无法使用show variables语句查看，但我们可以通过setup_instruments和setup_consumers表查询这些选项指定的值。 2、系统变量 show variables like '%performance_schema%'; --重要的属性解释 performance_schema=ON /* 控制performance_schema功能的开关，要使用MySQL的performance_schema，需要在mysqld启动时启用，以启用事件收集功能 该参数在5.7.x之前支持performance_schema的版本中默认关闭，5.7.x版本开始默认开启 注意：如果mysqld在初始化performance_schema时发现无法分配任何相关的内部缓冲区，则performance_schema将自动禁用，并将performance_schema设置为OFF */ performance_schema_digests_size=10000 /* 控制events_statements_summary_by_digest表中的最大行数。如果产生的语句摘要信息超过此最大值，便无法继续存入该表，此时performance_schema会增加状态变量 */ performance_schema_events_statements_history_long_size=10000 /* 控制events_statements_history_long表中的最大行数，该参数控制所有会话在events_statements_history_long表中能够存放的总事件记录数，超过这个限制之后，最早的记录将被覆盖 全局变量，只读变量，整型值，5.6.3版本引入 * 5.6.x版本中，5.6.5及其之前的版本默认为10000，5.6.6及其之后的版本默认值为-1，通常情况下，自动计算的值都是10000 * 5.7.x版本中，默认值为-1，通常情况下，自动计算的值都是10000 */ performance_schema_events_statements_history_size=10 /* 控制events_statements_history表中单个线程（会话）的最大行数，该参数控制单个会话在events_statements_history表中能够存放的事件记录数，超过这个限制之后，单个会话最早的记录将被覆盖 全局变量，只读变量，整型值，5.6.3版本引入 * 5.6.x版本中，5.6.5及其之前的版本默认为10，5.6.6及其之后的版本默认值为-1，通常情况下，自动计算的值都是10 * 5.7.x版本中，默认值为-1，通常情况下，自动计算的值都是10 除了statement(语句)事件之外，wait(等待)事件、state(阶段)事件、transaction(事务)事件，他们与statement事件一样都有三个参数分别进行存储限制配置，有兴趣的同学自行研究，这里不再赘述 */ performance_schema_max_digest_length=1024 /* 用于控制标准化形式的SQL语句文本在存入performance_schema时的限制长度，该变量与max_digest_length变量相关(max_digest_length变量含义请自行查阅相关资料) 全局变量，只读变量，默认值1024字节，整型值，取值范围0~1048576 */ performance_schema_max_sql_text_length=1024 /* 控制存入events_statements_current，events_statements_history和events_statements_history_long语句事件表中的SQL_TEXT列的最大SQL长度字节数。 超出系统变量performance_schema_max_sql_text_length的部分将被丢弃，不会记录，一般情况下不需要调整该参数，除非被截断的部分与其他SQL比起来有很大差异 全局变量，只读变量，整型值，默认值为1024字节，取值范围为0~1048576，5.7.6版本引入 降低系统变量performance_schema_max_sql_text_length值可以减少内存使用，但如果汇总的SQL中，被截断部分有较大差异，会导致没有办法再对这些有较大差异的SQL进行区分。 增加该系统变量值会增加内存使用，但对于汇总SQL来讲可以更精准地区分不同的部分。 */
6、重要配置表的相关说明
/* performance_timers表中记录了server中有哪些可用的事件计时器 字段解释： timer_name:表示可用计时器名称，CYCLE是基于CPU周期计数器的定时器 timer_frequency:表示每秒钟对应的计时器单位的数量,CYCLE计时器的换算值与CPU的频率相关、 timer_resolution:计时器精度值，表示在每个计时器被调用时额外增加的值 timer_overhead:表示在使用定时器获取事件时开销的最小周期值 */ select * from performance_timers; /* setup_timers表中记录当前使用的事件计时器信息 字段解释： name:计时器类型，对应某个事件类别 timer_name:计时器类型名称 */ select * from setup_timers; /* setup_consumers表中列出了consumers可配置列表项 字段解释： NAME：consumers配置名称 ENABLED：consumers是否启用，有效值为YES或NO，此列可以使用UPDATE语句修改。 */ select * from setup_consumers; /* setup_instruments 表列出了instruments 列表配置项，即代表了哪些事件支持被收集： 字段解释： NAME：instruments名称，instruments名称可能具有多个部分并形成层次结构 ENABLED：instrumetns是否启用，有效值为YES或NO，此列可以使用UPDATE语句修改。如果设置为NO，则这个instruments不会被执行，不会产生任何的事件信息 TIMED：instruments是否收集时间信息，有效值为YES或NO，此列可以使用UPDATE语句修改，如果设置为NO，则这个instruments不会收集时间信息 */ SELECT * FROM setup_instruments; /* setup_actors表的初始内容是匹配任何用户和主机，因此对于所有前台线程，默认情况下启用监视和历史事件收集功能 字段解释： HOST：与grant语句类似的主机名，一个具体的字符串名字，或使用“％”表示“任何主机” USER：一个具体的字符串名称，或使用“％”表示“任何用户” ROLE：当前未使用，MySQL 8.0中才启用角色功能 ENABLED：是否启用与HOST，USER，ROLE匹配的前台线程的监控功能，有效值为：YES或NO HISTORY：是否启用与HOST， USER，ROLE匹配的前台线程的历史事件记录功能，有效值为：YES或NO */ SELECT * FROM setup_actors; /* setup_objects表控制performance_schema是否监视特定对象。默认情况下，此表的最大行数为100行。 字段解释： OBJECT_TYPE：instruments类型，有效值为：“EVENT”（事件调度器事件）、“FUNCTION”（存储函数）、“PROCEDURE”（存储过程）、“TABLE”（基表）、“TRIGGER”（触发器），TABLE对象类型的配置会影响表I/O事件（wait/io/table/sql/handler instrument）和表锁事件（wait/lock/table/sql/handler instrument）的收集 OBJECT_SCHEMA：某个监视类型对象涵盖的数据库名称，一个字符串名称，或“％”(表示“任何数据库”) OBJECT_NAME：某个监视类型对象涵盖的表名，一个字符串名称，或“％”(表示“任何数据库内的对象”) ENABLED：是否开启对某个类型对象的监视功能，有效值为：YES或NO。此列可以修改 TIMED：是否开启对某个类型对象的时间收集功能，有效值为：YES或NO，此列可以修改 */ SELECT * FROM setup_objects; /* threads表对于每个server线程生成一行包含线程相关的信息， 字段解释： THREAD_ID：线程的唯一标识符（ID） NAME：与server中的线程检测代码相关联的名称(注意，这里不是instruments名称) TYPE：线程类型，有效值为：FOREGROUND、BACKGROUND。分别表示前台线程和后台线程 PROCESSLIST_ID：对应INFORMATION_SCHEMA.PROCESSLIST表中的ID列。 PROCESSLIST_USER：与前台线程相关联的用户名，对于后台线程为NULL。 PROCESSLIST_HOST：与前台线程关联的客户端的主机名，对于后台线程为NULL。 PROCESSLIST_DB：线程的默认数据库，如果没有，则为NULL。 PROCESSLIST_COMMAND：对于前台线程，该值代表着当前客户端正在执行的command类型，如果是sleep则表示当前会话处于空闲状态 PROCESSLIST_TIME：当前线程已处于当前线程状态的持续时间（秒） PROCESSLIST_STATE：表示线程正在做什么事情。 PROCESSLIST_INFO：线程正在执行的语句，如果没有执行任何语句，则为NULL。 PARENT_THREAD_ID：如果这个线程是一个子线程（由另一个线程生成），那么该字段显示其父线程ID ROLE：暂未使用 INSTRUMENTED：线程执行的事件是否被检测。有效值：YES、NO HISTORY：是否记录线程的历史事件。有效值：YES、NO * THREAD_OS_ID：由操作系统层定义的线程或任务标识符（ID）： */ select * from threads
注意：在performance_schema库中还包含了很多其他的库和表，能对数据库的性能做完整的监控，大家需要参考官网详细了解
7、performance_schema实践操作
基本了解了表的相关信息之后，可以通过这些表进行实际的查询操作来进行实际的分析。
--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;
原文链接：https://blog.csdn.net/m0_46690280/article/details/114414257
