MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点2：系统变量的确认

MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点2：系统变量的确认

在MySQL中有很多系统变量，我们可以通过多种方法确认系统变量的设置状况，常用的主要通过如下两种。

• Performance Schema的系统变量表
• SHOW VARIABLES 命令

另外，我们通过 mysqladmin variables 命令也可以确认系统变量的设置状况。

Performance Schema的系统变量表

Performance Schema的系统变量表查看其值。
主要Performance Schema的系统变量表如下：

global_variables：全局系统变量。
session_variables：当前会话的系统变量。
variables_by_thread：每个活动线程的会话变量，可通过线程ID查看相应的会话变量。
persisted_variables: 存储持久化全局系统变量设置的mysqld-auto.cnf 文件的SQL接口。
variables_info：显示每个系统变量的最近设置源及其值范围。

Performance Schema的各系统变量表的定义

各表的定义信息如下：

mysql> use performance_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql> desc global_variables;
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| VARIABLE_NAME  | varchar(64)   | NO   | PRI | NULL    |       |
| VARIABLE_VALUE | varchar(1024) | YES  |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc session_variables;
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| VARIABLE_NAME  | varchar(64)   | NO   | PRI | NULL    |       |
| VARIABLE_VALUE | varchar(1024) | YES  |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> desc variables_by_thread;
+----------------+-----------------+------+-----+---------+-------+
| Field          | Type            | Null | Key | Default | Extra |
+----------------+-----------------+------+-----+---------+-------+
| THREAD_ID      | bigint unsigned | NO   | PRI | NULL    |       |
| VARIABLE_NAME  | varchar(64)     | NO   | PRI | NULL    |       |
| VARIABLE_VALUE | varchar(1024)   | YES  |     | NULL    |       |
+----------------+-----------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc persisted_variables;
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| VARIABLE_NAME  | varchar(64)   | NO   | PRI | NULL    |       |
| VARIABLE_VALUE | varchar(1024) | YES  |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc variables_info;
+-----------------+-----------------------------------------------------------------------------------------------------------+------+-----+----------+-------+
| Field           | Type                                                                                                      | Null | Key | Default  | Extra |
+-----------------+-----------------------------------------------------------------------------------------------------------+------+-----+----------+-------+
| VARIABLE_NAME   | varchar(64)                                                                                               | NO   |     | NULL     |       |
| VARIABLE_SOURCE | enum('COMPILED','GLOBAL','SERVER','EXPLICIT','EXTRA','USER','LOGIN','COMMAND_LINE','PERSISTED','DYNAMIC') | YES  |     | COMPILED |       |
| VARIABLE_PATH   | varchar(1024)                                                                                             | YES  |     | NULL     |       |
| MIN_VALUE       | varchar(64)                                                                                               | YES  |     | NULL     |       |
| MAX_VALUE       | varchar(64)                                                                                               | YES  |     | NULL     |       |
| SET_TIME        | timestamp(6)                                                                                              | YES  |     | NULL     |       |
| SET_USER        | char(32)                                                                                                  | YES  |     | NULL     |       |
| SET_HOST        | char(255)                                                                                                 | YES  |     | NULL     |       |
+-----------------+-----------------------------------------------------------------------------------------------------------+------+-----+----------+-------+
8 rows in set (0.00 sec)

mysql>

我们可以看到global_variables、session_variables、persisted_variables表都包含两列：

VARIABLE_NAME：变量名
VARIABLE_VALUE：变量值。

对于global_variables，表示全局值；
对于session_variables，表示当前会话生效的变量值；
对于persisted_variables，表示持久存储的变量值。

variables_by_thread表的列含义如下：

THREAD_ID：会话级别系统变量对应的线程ID
VARIABLE_NAME：会话级别系统变量名
VARIABLE_VALUE：会话级别系统变量值

variables_info表的列含义如下：

列名	解释
VARIABLE_NAME	变量名
VARIABLE_SOURCE	配置的来源，也就是由于什么改变了变量
VARIABLE_PATH	如果变量是从选项文件中设置的，则variable_PATH是该文件的路径名。否则，该值为空。
MIN_VALUE	变量允许的最小值。
MAX_VALUE	变量允许的最大值。
SET_TIME	最近设置变量的时间。
SET_USER	最近设置变量的客户端用户的用户名。
SET_HOST	最近设置变量的客户端用户的主机名。

变量查询例

我们可以通过查询这些表来查看变量的各级别设置状态。

--全局变量例
mysql> select * from global_variables where VARIABLE_NAME='sort_buffer_size';
+------------------+----------------+
| VARIABLE_NAME    | VARIABLE_VALUE |
+------------------+----------------+
| sort_buffer_size | 262144         |
+------------------+----------------+
1 row in set (0.01 sec)

--会话级别变量例
mysql> select * from session_variables where VARIABLE_NAME='sort_buffer_size';
+------------------+----------------+
| VARIABLE_NAME    | VARIABLE_VALUE |
+------------------+----------------+
| sort_buffer_size | 262144         |
+------------------+----------------+
1 row in set (0.01 sec)

--各个线程的设置状况
mysql>  select * from variables_by_thread where VARIABLE_NAME='sort_buffer_size';
+-----------+------------------+----------------+
| THREAD_ID | VARIABLE_NAME    | VARIABLE_VALUE |
+-----------+------------------+----------------+
|        70 | sort_buffer_size | 262144         |
|        71 | sort_buffer_size | 262144         |
+-----------+------------------+----------------+
2 rows in set (0.01 sec)

SHOW VARIABLES 查看变量

除了系统变量表，还可以通过SHOW VARIABLES 查看变量的设置情况。

SHOW VARIABLES语法：

SHOW [GLOBAL | SESSION] VARIABLES
    [LIKE 'pattern' | WHERE expr]

SHOW VARIABLES可以通过可选的GLOBAL或SESSION变量查看不同级别的变量设置情况。

- GLOBAL：显示全局系统变量值。如果变量没有全局值，则不会显示任何值。
- SESSION：显示当前连接有效的系统变量值。如果变量没有会话值，则显示全局值。LOCAL是SESSION的同义词。
- 如果没有指定GLOBAL或者SESSION，则默认值为SESSION。

变量查询例

mysql> set session sort_buffer_size = 300000;
Query OK, 0 rows affected (0.00 sec)
--全局变量
mysql> SHOW GLOBAL VARIABLES LIKE 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)

--会话级别变量
mysql> SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 300000 |
+------------------+--------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 300000 |
+------------------+--------+
1 row in set (0.00 sec)

mysql>

SELECT @@<变量名>

也可以通过SELECT @@<变量名>来查看变量

@@global.<变量名>  :用于标记全局变量；
@@session<变量名> :用于标记会话变量；
@@<变量名>       :首先标记会话变量，如果会话变量不存在，则标记全局变量。

mysql> SELECT @@sort_buffer_size ;
+--------------------+
| @@sort_buffer_size |
+--------------------+
|             300000 |
+--------------------+
1 row in set (0.00 sec)
mysql> select @@global.sort_buffer_size;
+---------------------------+
| @@global.sort_buffer_size |
+---------------------------+
|                    262144 |
+---------------------------+
1 row in set (0.00 sec)

mysql>  select @@session.sort_buffer_size;
+----------------------------+
| @@session.sort_buffer_size |
+----------------------------+
|                     262144 |
+----------------------------+
1 row in set (0.00 sec)

例题

Choose the best answer. You want to check the values of the sort_buffer_size session variables of all existing connections.

Which performance_schema table can you query?

A) user_variables_by_thread
B) global_variables
C) variables_by_thread
D) session_variables

例题解析

本题的关键点在于要查看所有连接的会话( all existing connections)的sort_buffer_size 设置情况，所以需要通过variables_by_thread来确认。

参考答案： C

参考

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-system-variable-tables.html
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-variables-info-table.html
https://dev.mysql.com/doc/refman/8.0/en/show-variables.html
https://dev.mysql.com/doc/refman/8.0/en/mysqladmin.html