> For the complete documentation index, see [llms.txt](https://bohans.gitbook.io/ji-chu/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://bohans.gitbook.io/ji-chu/mysql/sql-you-hua/performance-schema/shi-yong/jian-cha-bian-liang.md).

# 检查变量

> <mark style="color:blue;">**在5.7版本之前，服务器和状态变量是在information\_schema中配置的**</mark>。
>
> 这种配置是有限制的：<mark style="color:orange;">**只允许跟踪全局和当前会话的变量值**</mark>**。**&#x5176;他会话中关于变量和状态的信息，以及关于用户变量的信息，都是不可访问的。
>
> 出于向后兼容的考虑，MySQL 5.7还是使用 information\_schema 来跟踪变量。
>
> <mark style="color:orange;">**要启用performance\_schema对变量跟踪的支持，需要将配置变量show\_compatibility\_56设置为0。**</mark>这一要求以及information\_schema中的变量表在8.0版中都不再存在。
>
> ```sql
> SELECT @@show_compatibility_56;
>
> +-----------------------+
> |@@show_compatibility_56|
> +-----------------------+
> |0                      |
> +-----------------------+
> ```

## variables

全局变量值被存储在表 <mark style="color:blue;">**global\_variables**</mark> 中。当前会话的会话变量被存储在 <mark style="color:blue;">**session\_variables**</mark> 表中。这两个表中只有两列具有自解释的名称：**VARIABLE\_NAME** 和 **VARIABLE\_VALUE**。

```sql
SHOW COLUMNS FROM performance_schema.global_variables;

+--------------+-------------+----+---+-------+-----+
|Field         |Type         |Null|Key|Default|Extra|
+--------------+-------------+----+---+-------+-----+
|VARIABLE_NAME |varchar(64)  |NO  |   |NULL   |     |
|VARIABLE_VALUE|varchar(1024)|YES |   |NULL   |     |
+--------------+-------------+----+---+-------+-----+
```

```sql
SHOW COLUMNS FROM performance_schema.session_variables;

+--------------+-------------+----+---+-------+-----+
|Field         |Type         |Null|Key|Default|Extra|
+--------------+-------------+----+---+-------+-----+
|VARIABLE_NAME |varchar(64)  |NO  |   |NULL   |     |
|VARIABLE_VALUE|varchar(1024)|YES |   |NULL   |     |
+--------------+-------------+----+---+-------+-----+
```

<mark style="color:blue;">**variables\_by\_thread**</mark> 表中有一个额外的列 **THREAD\_ID**，表示变量所属的线程。这允许你查找将会话变量值设置为不同于默认值的线程。

```sql
SELECT *
FROM variables_by_thread
WHERE variable_name = 'tx_isolation';

+---------+-------------+---------------+
|THREAD_ID|VARIABLE_NAME|VARIABLE_VALUE |
+---------+-------------+---------------+
|52       |tx_isolation |REPEATABLE-READ|
|53       |tx_isolation |REPEATABLE-READ|
+---------+-------------+---------------+
```

用户定义的变量可以通过 **SET @my\_var='foo'** 创建，并在表 <mark style="color:blue;">**user\_variable\_by\_thread**</mark> 中跟踪：

```sql
SET @my_var='foo';
SELECT * FROM user_variables_by_thread;

+---------+--------------------------+--------------+
|THREAD_ID|VARIABLE_NAME             |VARIABLE_VALUE|
+---------+--------------------------+--------------+
|52       |my_var                    |foo           |
|52       |sys.statement_truncate_len|64            |
|52       |rental_id                 |1             |
+---------+--------------------------+--------------+
```

表 <mark style="color:blue;">**variables\_info**</mark> 中不包含任何变量值，但它**有关于服务器变量起源的信息和其他文档**，例如，变量默认的最小值和最大值。

* **SET\_TIME** 列包含最新变量更改的时间戳。
* **SET\_HOST** 和 **SET\_USER** 列标识设置变量的用户账号。

例如，要查找自服务器启动以来动态更改的所有变量，请运行：

```sql
SELECT *
FROM variables_info
WHERE variable_source = 'DYNAMIC';
```

VARIABLE\_SOURCE的值可设置为如下几项：

* COMMAND\_LINE：在命令行中设置的变量。
* COMPILED：编译的默认值。
* PERSISTED：在服务器指定的 mysqld-auto.cnf 选项文件中设置。

> <mark style="color:red;">**MySQL 5.7中不存在**</mark>**&#x20;variables\_info 表！**

## status

全局和当前会话状态值分别被存储在表 <mark style="color:blue;">**global\_status**</mark> 和 <mark style="color:blue;">**session\_status**</mark> 中。它们也只有两列：**VARIABLE\_NAME** 和 **VARIABLE\_VALUE**。

```sql
SHOW COLUMNS FROM performance_schema.global_status;

+--------------+-------------+----+---+-------+-----+
|Field         |Type         |Null|Key|Default|Extra|
+--------------+-------------+----+---+-------+-----+
|VARIABLE_NAME |varchar(64)  |NO  |   |NULL   |     |
|VARIABLE_VALUE|varchar(1024)|YES |   |NULL   |     |
+--------------+-------------+----+---+-------+-----+
```

```sql
SHOW COLUMNS FROM performance_schema.session_status;

+--------------+-------------+----+---+-------+-----+
|Field         |Type         |Null|Key|Default|Extra|
+--------------+-------------+----+---+-------+-----+
|VARIABLE_NAME |varchar(64)  |NO  |   |NULL   |     |
|VARIABLE_VALUE|varchar(1024)|YES |   |NULL   |     |
+--------------+-------------+----+---+-------+-----+
```

<mark style="color:blue;">**status\_by\_thread**</mark> 表中有一个额外的列 **THREAD\_ID**，表示变量所属的线程。

```sql
SELECT *
FROM status_by_thread ;

+---------+-----------------------+--------------+
|THREAD_ID|VARIABLE_NAME          |VARIABLE_VALUE|
+---------+-----------------------+--------------+
|52       |Bytes_received         |116411        |
|52       |Bytes_sent             |1713934       |
|52       |Com_stmt_reprepare     |0             |
|52       |Compression            |OFF           |
|52       |Created_tmp_disk_tables|156           |
|52       |Created_tmp_tables     |935           |
|52       |Handler_commit         |9             |
|52       |Handler_delete         |0             |
|52       |Handler_discover       |0             |
|52       |Handler_external_lock  |327           |
+---------+-----------------------+--------------+
```