面试题：MySQL中常见配置变量的作用域有哪些及如何区分

Question

请详细阐述MySQL配置变量常见的作用域，比如全局作用域、会话作用域等，并说明如何从语法和实际应用场景中区分不同作用域的变量。

Answer 1

MySQL配置变量常见作用域

1. 全局作用域（Global Scope）：
   • 作用：对整个MySQL服务器实例生效。影响所有当前和未来的客户端连接。例如，max_connections变量设置MySQL服务器允许的最大并发连接数，这个设置对整个服务器实例起作用，所有客户端连接都受此限制。
   • 语法：
     • 查看全局变量：使用SHOW GLOBAL VARIABLES;语句。例如，SHOW GLOBAL VARIABLES LIKE'max_connections';
     • 设置全局变量：使用SET GLOBAL variable_name = value;语句。例如，SET GLOBAL max_connections = 200;注意，这种设置在服务器重启后可能失效，若要永久生效，需修改配置文件（如my.cnf或my.ini）。
   • 实际应用场景：当需要对整个服务器的行为进行调整，如调整缓存大小（innodb_buffer_pool_size）、设置日志相关参数（log_bin等）时，使用全局变量。这些设置会影响所有连接到服务器的客户端。
2. 会话作用域（Session Scope）：
   • 作用：仅对当前客户端会话有效。每个客户端连接到MySQL服务器时，会有自己的一组会话变量，这些变量基于全局变量的初始值进行设置，但可以在会话内独立修改，不会影响其他会话。例如，sql_mode变量可以在会话内设置不同的SQL模式，只影响当前会话的SQL语句执行方式。
   • 语法：
     • 查看会话变量：使用SHOW SESSION VARIABLES;或SHOW VARIABLES;（默认显示会话变量）。例如，SHOW SESSION VARIABLES LIKE'sql_mode';
     • 设置会话变量：使用SET SESSION variable_name = value;或直接使用SET variable_name = value;（默认设置会话变量）。例如，SET SESSION sql_mode = 'STRICT_ALL_TABLES';
   • 实际应用场景：当不同的客户端需要不同的行为设置时，使用会话变量。比如，某些特定的查询需要宽松的SQL模式来处理数据，而其他查询需要严格模式，就可以在不同的会话中设置不同的sql_mode变量值。
3. 静态变量（Static Variables）：
   • 作用：这些变量的值在MySQL服务器启动时确定，在服务器运行期间不能动态修改。例如，innodb_version显示InnoDB存储引擎的版本号，这个值在服务器启动后就固定了。
   • 语法：只能通过查看，使用SHOW VARIABLES LIKE 'innodb_version';等类似语句查看，无法使用SET语句修改。
   • 实际应用场景：主要用于获取服务器或存储引擎的一些固定属性信息，如版本号、编译信息等，用于诊断、记录或了解服务器的特性。

区分不同作用域变量

1. 语法区分：
   • 查看：查看全局变量使用SHOW GLOBAL VARIABLES;，查看会话变量使用SHOW SESSION VARIABLES;或SHOW VARIABLES;。
   • 设置：设置全局变量使用SET GLOBAL variable_name = value;，设置会话变量使用SET SESSION variable_name = value;或SET variable_name = value;。对于静态变量，没有设置语法，只能查看。
2. 实际应用场景区分：
   • 全局影响：如果设置影响整个服务器实例，对所有客户端连接都生效，那通常是全局变量，如调整服务器资源限制、整体性能相关的设置。
   • 单个会话影响：如果设置只希望影响当前客户端连接，而不影响其他连接，那使用会话变量，如调整当前会话的查询执行模式等。
   • 固定属性：如果是服务器或存储引擎的一些固定属性信息，不可动态修改的，那就是静态变量，用于查看相关的版本、编译等信息。