MySQL中的SYS数据库，它是什么以及如何利用它的功能？

MySQL的sys数据库是一个内置的系统库，旨在简化性能监控和诊断工作，它首次在MySQL 5.7版本中引入，通过提供易于阅读的视图、表、存储过程和函数，帮助用户快速了解数据库的运行情况。

sys数据库总览

sys库包含一个表、多个视图（共计100个）、存储过程及函数（共48个），这些组件主要用于从performance_schema采集数据，以降低其复杂度，使DBA能更快捷地了解数据库的性能信息。

视图分类与功能

1. 字母开头的视图（共52个）

这类视图显示格式化的数据，更适合人工阅读。

host_summary：服务器层面的统计，如每个IP的连接消耗资源。

user_summary：用户层级的统计，如每个用户的资源消耗。

innodb：InnoDB引擎相关的统计，如缓冲池使用情况。

io：I/O层统计，如每个数据文件的IO请求。

memory：内存使用情况，如每个主机的当前内存使用。

schema：schema级别的统计信息，如表锁等待。

session：会话级别的统计，如当前连接情况。

statement：语句级别的统计，如错误或警告的SQL语句。

wait：等待事件统计，如按延迟分组的等待事件。

2. x$ 开头的视图（共48个）

这类视图显示原始未处理过的数据，适合工具采集。

x$host_summary：原始的服务器层面统计信息。

x$user_summary：原始的用户层级统计信息。

x$innodb：原始的InnoDB引擎相关统计信息。

x$io：原始的I/O层统计信息。

x$memory：原始的内存使用情况统计信息。

x$schema：原始的schema级别统计信息。

x$session：原始的会话级别统计信息。

x$statement：原始的语句级别统计信息。

x$wait：原始的等待事件统计信息。

常用查询示例

查看每个客户端IP的连接消耗资源：SELECT * FROM host_summary;

查看某个数据文件上的IO请求：SELECT * FROM io_global_by_file_by_bytes;

查看每个用户的资源消耗：SELECT * FROM user_summary;

查看总共分配的内存：SELECT * FROM memory_global_total;

查看当前连接情况：SELECT host, current_connections, statements FROM host_summary;

查看正在执行的SQL：SELECT conn_id, user, current_statement, last_statement FROM session;

查看最热门的SQL：SELECT db, exec_count, query FROM statement_analysis ORDER BY exec_count DESC LIMIT 10;

查看产生最多IO的文件：SELECT * FROM io_global_by_file_by_bytes LIMIT 10;

查看请求最多的表：SELECT * FROM io_global_by_file_by_bytes WHERE file LIKE '%ibd' ORDER BY total DESC LIMIT 10;

查看被访问最多的表：SELECT * FROM statement_analysis ORDER BY avg_latency DESC LIMIT 10;

查看执行了全表扫描或排序操作的SQL：SELECT * FROM statements_with_sorting; SELECT * FROM statements_with_full_table_scans;

查看使用了临时表的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;

查看占用最多buffer pool的表：SELECT * FROM innodb_buffer_stats_by_table ORDER BY allocated DESC LIMIT 10;

查看每个库占用的buffer pool：SELECT * FROM innodb_buffer_stats_by_schema ORDER BY allocated DESC LIMIT 10;

查看每个连接分配的内存：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;

查看自增长字段的最大值和使用到的值：SELECT table_name, column_name, max_value, current_value FROM information_schema.columns WHERE extra = 'auto_increment';

sys_config表简介

在MySQL 5.8中，sys库中的sys_config表用于存储数据库的配置信息，通过修改这个表，可以对系统配置进行持久化管理，即使重启数据库，配置也不会丢失。

variable：配置选项的名称。

value：当前配置选项的值。

set_time：配置项的最后修改时间。

set_by：最后修改配置的用户名。

具体配置项解释

diagnostics.allow_i_s_tables：控制是否扫描INFORMATION_SCHEMA.TABLES表，默认为OFF。

diagnostics.include_raw：决定是否在diagnostics()输出中包含原始数据，默认为OFF。

ps_thread_trx_info.max_length：设置ps_thread_trx_info()函数返回的JSON数据的最大长度，默认为65535字节。

statement_performance_analyzer.limit：限制statement_performance_analyzer()返回的结果的最大行数，默认为100。

statement_performance_analyzer.view：指定statement_performance_analyzer()使用的自定义查询或视图名称，如果为NULL则使用默认视图。

statement_truncate_len：控制format_statement()函数返回的语句文本的最大长度，默认为64字节。

如何修改配置

要修改这些配置，可以通过SET命令来调整自定义变量，然后这些变量会在当前会话中生效。

SET @sys.statement_truncate_len = 32;

这样，format_statement()函数在当前会话中的最大长度将调整为32字节，要恢复默认值，可以将自定义变量设为NULL：

SET @sys.statement_truncate_len = NULL;

配置项的优先级在MySQL 5.8中，sys系统库对配置的读取是有优先级的，每当MySQL的视图、存储过程或函数调用某些配置项时，它们首先会检查是否有相应的会话级自定义配置变量存在，如果存在且非空，系统会优先使用自定义变量的值；否则，将从sys_config表中读取默认配置。