初识Sys · 轻松掌握MySQL系统库配置表

MySQL sys 系统库作为 MySQL 的管理工具集，从 MySQL 5.7 开始被引入，它帮助数据库管理员简化了性能监控和诊断工作。随着 MySQL 的发展，sys 系统库的功能也逐渐完善，比如在 MySQL 5.8 中，sys_config 表管理变得更加便捷，配置也变得更灵活。接下来，我将对 sys_config 表及相关的功能做一个详细说明，并通过举例使内容更加易于理解。

sys_config 表简介

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

sys_config 表提供了一个集中管理 MySQL 系统配置选项的方式，允许用户根据需要进行调整和优化。通过理解这些配置项及其含义，用户可以更好地管理和调优 MySQL 实例的性能和行为。

SELECT * FROM sys.sys_config;

可以通过查询 sys_config 表来查看当前的配置选项及其状态。

mysql> SELECT * FROM sys.sys_config;
+--------------------------------------+-------+---------------------+--------+
| variable                             | value | set_time            | set_by |
+--------------------------------------+-------+---------------------+--------+
| diagnostics.allow_i_s_tables         | OFF   | 2024-05-23 16:33:49 | NULL   |
| diagnostics.include_raw              | OFF   | 2024-05-23 16:33:49 | NULL   |
| ps_thread_trx_info.max_length        | 65535 | 2024-05-23 16:33:49 | NULL   |
| statement_performance_analyzer.limit | 100   | 2024-05-23 16:33:49 | NULL   |
| statement_performance_analyzer.view  | NULL  | 2024-05-23 16:33:49 | NULL   |
| statement_truncate_len               | 64    | 2024-05-23 16:33:49 | NULL   |
+--------------------------------------+-------+---------------------+--------+
6 rows in set

表字段说明

• variable：配置选项的名称。例如，diagnostics.allow_i_s_tables 表示是否允许扫描 INFORMATION_SCHEMA.TABLES。
• value：当前配置选项的值。例如，ps_thread_trx_info.max_length 为 65535 字节。
• set_time：配置项的最后修改时间。例如，所有配置选项的修改时间为 2024-05-23 16:33:49。
• set_by：最后修改配置的用户名。如果该字段为 NULL，表示该配置项自创建以来没有被修改过。

具体配置项解释

• diagnostics.allow_i_s_tables：控制 diagnostics() 存储过程是否扫描 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 表中读取默认配置。

例如，statement_truncate_len 配置项控制 SQL 语句在调用 format_statement() 函数时的最大长度，默认情况下，语句长度被限制为 64 个字符。

如果想在当前会话中将这个长度修改为 32 个字符，可以使用如下命令：

SET @sys.statement_truncate_len = 32;

此时，任何调用 format_statement() 的函数都会使用这个会话变量的值（32 个字符）。如果希望恢复默认配置（64 个字符），可以将这个变量设为 NULL 或者重启会话：

SET @sys.statement_truncate_len = NULL;

注意：如果当前会话中存在自定义变量值（如上例中的 32），那么即使在 sys_config 表中修改了 statement_truncate_len，对当前会话来说，新的值也不会生效。

自定义配置变量与 sys_config 表的联动

可以通过自定义配置变量临时修改某些配置项，而无需直接修改 sys_config 表。例如，当执行 SQL 语句性能分析时，可以临时调整某些设置，而这些设置仅对当前会话生效，不会影响其他会话。

假设要暂时将 statement_performance_analyzer.limit 从默认的 100 修改为 50，可以执行以下操作：

SET @sys.statement_performance_analyzer.limit = 50;

执行此操作后，任何基于 statement_performance_analyzer() 的查询都将返回最多 50 行。如果希望恢复默认的 100 行限制，只需将该变量设为 NULL：

SET @sys.statement_performance_analyzer.limit = NULL;

触发器的作用

在 MySQL 5.8 中，sys_config 表的 insert 和 update 操作会触发两个触发器：

• sys_config_insert_set_user：当插入新配置项时，该触发器会自动将 set_by 字段设置为当前用户。
• sys_config_update_set_user：当更新现有配置项时，该触发器也会将 set_by 字段设置为当前用户。

注意事项：

• mysql.sys 用户的权限：为了使触发器正常运行，MySQL 系统中必须存在 mysql.sys 用户，并且该用户需要具备对 sys.sys_config 表的 INSERT 和 UPDATE 权限。
• 如果不小心删除了 mysql.sys 用户，可能会在执行 sys_config 表的修改时遇到权限错误。解决方法是通过重新创建该用户或调整用户权限表来恢复正常功能。

总结

通过 sys_config 表，MySQL 5.8 提供了灵活的配置持久化和会话级自定义变量功能。可以根据具体需求，调整系统的行为或性能监控设置，而这些配置既可以临时应用于当前会话，也可以通过修改 sys_config 表来永久生效。

使用这些配置项和触发器时，务必注意 mysql.sys 用户的权限管理，以及会话变量与 sys_config 表配置的优先级关系，以避免潜在的配置冲突问题。