全网详细介绍MySQL中的show variables like ‘%xxx%’、show global status like ‘%xxx%’ 和 set global ‘xxx’=value

今天药忘吃喽~ 2023-09-25 23:01 114阅读 0赞

#### 文章目录 ####

*  1. 文章引言
 *  2. show variables介绍
 *  3. show global status介绍
 *  3. 详细介绍
 *   *  3.1 慢查询
     *  3.2 连接数查询
     *  3.3 key\_buffer\_size
     *  3.4 临时表查询
     *  3.5 open table查询
     *  3.6 查看进程使用情况
     *  3.7 查询缓存(query cache)
     *  3.8 排序使用情况
     *  3.9 文件打开数(open\_files)
     *  3.10 查询表锁情况
     *  3.11 表扫描情况
 *  4. set global ‘xxx’=value介绍

## 1. 文章引言 ##

我们在操作数据库时，经常会使用`mysql`定义的一些变量或者格式。

比如，我们有一张`user`表，查询该表的创建时间(`create_time`)的格式化为`yyyy年MM月dd日 HH时mm分ss秒`，但`mysql`查询出来的创建时间为`2023-03-01 19:03:04`，如下代码所示：

mysql> select username as 用户名,create_time as 创建时间 from user where username = '陈希尔';
    +--------+---------------------+
    | 用户名 | 创建时间            |
    +--------+---------------------+
    | 陈希尔 | 2023-03-01 19:03:04 |
    +--------+---------------------+
    1 row in set (0.00 sec)

我们不知道`MySQL`如何定义这种`yyyy年MM月dd日 HH时mm分ss秒`格式，因而，就需要使用`show variables like 'datetime_format'`语句查询，如下代码所示：

mysql> show variables like 'datetime_format';
    +-----------------+-------------------+
    | Variable_name   | Value             |
    +-----------------+-------------------+
    | datetime_format | %Y-%m-%d %H:%i:%s |
    +-----------------+-------------------+
    1 row in set, 1 warning (0.00 sec)

虽然查询出的格式为`%Y-%m-%d %H:%i:%s`的，但我们可替换成`%Y年%m月%d日 %H时%i分%s秒`，继而进行如下`SQL`查询：

select 
    	username as 用户名,
    	DATE_FORMAT(create_time,'%Y年%m月%d日 %H时%i分%s秒') as 创建时间 
    from 
    	`user` 
    where 
    	username = '陈希尔';
    	
    +--------+-----------------------------+
    | 用户名 | 创建时间                      |
    +--------+-----------------------------+
    | 陈希尔 | 2023年03月01日 19时03分04秒   |
    +--------+-----------------------------+
    1 row in set (0.00 sec)

因而，我们需要使用`show variables like xxx`来查询`MySQL`定义的格式，接下来，我便详细分析它。

## 2. show variables介绍 ##

我们可以使用`show variables;`来查询`MySQL`定义的所有变量，但由于查询出`506行`的数据，如下图所示：

![在这里插入图片描述][16cb81bba5b845c5bf57fd55d00f23c7.png]

因而，不能全部列取出来，感兴趣的可以使用`show variables;`查询，如下代码所示：

mysql> show variables;
    +-----------------------------+------------------------+
    | Variable_name               | Value                  |                                                                                                                                                                                                                                                                                                                                                                                          
    +-----------------------------+------------------------+
    | auto_increment_increment    | 1                      |                                                                                                                                                                                                                                                                                                                                            
    | auto_increment_offset       | 1                      |
    | autocommit                  | ON                     |
    | automatic_sp_privileges     | ON                     |
    | avoid_temporal_upgrade      | OFF                    |
    |      ...                   ...                   ... |
    | wait_timeout                | 28800                  |
    | warning_count               | 0                      |
    +-----------------------------+------------------------+
    506 rows in set, 1 warning (0.00 sec)

## 3. show global status介绍 ##

`show global status`查看`MySQL`服务器运行的各种状态值，但由于查询出`353行`的数据，如下图所示：

![在这里插入图片描述][62b14236896d45f0a3e8ad995379d7ac.png]

因而，不能全部列取出来，感兴趣的可以使用`show global status`查询，如下代码所示：

show global status;
    +-----------------------------------+---------+
    | Variable_name                     | Value   |
    +-----------------------------------+---------+
    | Aborted_clients                   | 203     |
    | Aborted_connects                  | 1       |
    | Binlog_cache_disk_use             | 0       |
    | ....                              ...       |
    | Uptime                            | 969795  |
    | Uptime_since_flush_status         | 969795  |
    +-----------------------------------+---------+
    353 rows in set (0.00 sec)

## 3. 详细介绍 ##

### 3.1 慢查询 ###

mysql> show variables like '%slow%';
    +---------------------------+--------------------------+
    | Variable_name             | Value                    |
    +---------------------------+--------------------------+
    | log_slow_admin_statements | OFF                      |
    | log_slow_slave_statements | OFF                      |
    | slow_launch_time          | 2                        |
    | slow_query_log            | OFF                      |
    | slow_query_log_file       | DESKTOP-UVTEHFR-slow.log |
    +---------------------------+--------------------------+
    5 rows in set, 1 warning (0.00 sec)
    
    mysql> show global status like '%slow%';
    +---------------------+-------+
    | Variable_name       | Value |
    +---------------------+-------+
    | Slow_launch_threads | 0     |
    | Slow_queries        | 120     |
    +---------------------+-------+
    2 rows in set (0.00 sec)

配置中关闭了记录慢查询，但最好是打开，方便优化。

过2秒即为慢查询，一共有`120`条慢查询。

### 3.2 连接数查询 ###

mysql> show variables like 'max_connections';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | max_connections | 1000  |
    +-----------------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> show global status like 'max_used_connections';
    +----------------------+-------+
    | Variable_name        | Value |
    +----------------------+-------+
    | Max_used_connections | 750    |
    +----------------------+-------+
    1 row in set (0.00 sec)

设置的最大连接数是1000，而响应的连接数是750。

`max_used_connections / max_connections * 100% = 75% （理想值 ≈ 85%）`

### 3.3 key\_buffer\_size ###

mysql> show variables like 'key_buffer_size';
    +-----------------+---------+
    | Variable_name   | Value   |
    +-----------------+---------+
    | key_buffer_size | 8388608 |
    +-----------------+---------+
    1 row in set, 1 warning (0.00 sec)

`key_buffer_size`是对`MyISAM`表性能影响最大的一个参数，不过数据库中多为`Innodb`。

mysql> show global status like 'key_read%';
    +-------------------+-------+
    | Variable_name     | Value |
    +-------------------+-------+
    | Key_read_requests | 270   |
    | Key_reads         | 5     |
    +-------------------+-------+
    2 rows in set (0.00 sec)

一共有`270`个索引读取请求，有`5`个请求在内存中没有找到直接从硬盘读取索引。

计算索引未命中缓存的概率：

`key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% =1.85%.`

需要适当加大`key_buffer_size`，如下代码所示：

mysql> set global key_buffer_size= 83886080;
    Query OK, 0 rows affected (0.00 sec)

`Key_blocks_unused`表示未使用的缓存簇(`blocks`)数，`Key_blocks_used`表示曾经用到的最大的`blocks`数。

mysql> show global status like 'key_blocks_u%';
    +-------------------+-------+
    | Variable_name     | Value |
    +-------------------+-------+
    | Key_blocks_unused | 6693  |
    | Key_blocks_used   | 500  |
    +-------------------+-------+
    2 rows in set (0.00 sec)

`Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 7.5% （理想值 ≈ 80%）`

### 3.4 临时表查询 ###

mysql> show global status like 'created_tmp%';
    +-------------------------+-------+
    | Variable_name           | Value |
    +-------------------------+-------+
    | Created_tmp_disk_tables | 623   |
    | Created_tmp_files       | 21    |
    | Created_tmp_tables      | 1423  |
    +-------------------------+-------+
    3 rows in set (0.00 sec)

每次创建临时表，`Created_tmp_tables`增加。

如果是在磁盘上创建临时表，`Created_tmp_disk_tables`也增加。

`Created_tmp_files`表示`MySQL`服务创建的临时文件文件数：

`Created_tmp_disk_tables / Created_tmp_tables * 100% ＝ 43.8% （理想值<= 25%）。`

mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
    +---------------------+----------+
    | Variable_name       | Value    |
    +---------------------+----------+
    | max_heap_table_size | 16777216 |
    | tmp_table_size      | 68157440 |
    +---------------------+----------+
    2 rows in set, 1 warning (0.00 sec)

需要增加`tmp_table_size`，如下代码所示：

mysql> set global tmp_table_size = 681574400;
    Query OK, 0 rows affected (0.00 sec)

### 3.5 open table查询 ###

mysql> show global status like 'open%tables%';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | Open_tables   | 529   |
    | Opened_tables | 1447  |
    +---------------+-------+
    2 rows in set (0.00 sec)

`Open_tables`表示打开表的数量。

`Opened_tables`表示打开过的表数量。

如果`Opened_tables`数量过大，说明配置中`table_open_cache`(`MySQL`在`5.1.3`版本之前叫做`table_cache`)值可能太小，我们查询一下服务器`table_open_cache`值，如下代码所示：

mysql> show variables like 'table_open_cache';
    +------------------+-------+
    | Variable_name    | Value |
    +------------------+-------+
    | table_open_cache | 2000  |
    +------------------+-------+
    1 row in set, 1 warning (0.00 sec)

`Open_tables / Opened_tables * 100% =36.6% 理想值 （>= 85%）。`  
`Open_tables / table_open_cache * 100% = 26.4% 理想值 (<= 95%)。`

### 3.6 查看进程使用情况 ###

mysql> show global status like 'Thread%';
    +-------------------+-------+
    | Variable_name     | Value |
    +-------------------+-------+
    | Threads_cached    | 12    |
    | Threads_connected | 9     |
    | Threads_created   | 275   |
    | Threads_running   | 1     |
    +-------------------+-------+
    4 rows in set (0.00 sec)

如果我们在`MySQL`服务器配置文件中设置了`thread_cache_size`，当客户端断开之后，服务器处理此客户的线程将会缓存起来，以响应下一个客户而不是销毁（前提是缓存数未达上限）。

`Threads_created`表示创建过的线程数。

如果发现`Threads_created`值过大的话，表明`MySQL`服务器一直在创建线程，这也是比较耗资源。

可以适当增加配置文件中`thread_cache_size`值，查询服务器`thread_cache_size`配置：

mysql> show variables like 'thread_cache_size';
    +-------------------+-------+
    | Variable_name     | Value |
    +-------------------+-------+
    | thread_cache_size | 60    |
    +-------------------+-------+
    1 row in set, 1 warning (0.00 sec)

设置`thread_cache_size`的代码，如下所示：

mysql> set global thread_cache_size = 100;
    Query OK, 0 rows affected (0.00 sec)

### 3.7 查询缓存(query cache) ###

mysql>  show global status like 'qcache%';
    +-------------------------+-------+
    | Variable_name           | Value |
    +-------------------------+-------+
    | Qcache_free_blocks      | 0     |
    | Qcache_free_memory      | 0     |
    | Qcache_hits             | 0     |
    | Qcache_inserts          | 0     |
    | Qcache_lowmem_prunes    | 0     |
    | Qcache_not_cached       | 0     |
    | Qcache_queries_in_cache | 0     |
    | Qcache_total_blocks     | 0     |
    +-------------------------+-------+
    8 rows in set (0.00 sec)

1.  `Qcache_free_blocks`：缓存中相邻内存块的个数。

数目大说明可能有碎片。

`FLUSH QUERY CACHE`会对缓存中的碎片进行整理，从而得到一个空闲块。

1.  `Qcache_free_memory`：缓存中的空闲内存。
2.  `Qcache_hits`：每次查询在缓存中命中时就增大。
3.  `Qcache_inserts`：每次插入一个查询时就增大，命中次数除以插入次数就是不中比率。
4.  `Qcache_lowmem_prunes`：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。

这个数字最好长时间来看。

如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。

上面的`free_blocks`和`free_memory`可以告诉您属于哪种情况。

1.  `Qcache_not_cached`：不适合进行缓存的查询的数量，通常是由于这些查询不是`SELECT`语句或者用了`now()`之类的函数。
2.  `Qcache_queries_in_cache`：当前缓存的查询（和响应）的数量。
3.  `Qcache_total_blocks`：缓存中块的数量。

我们再查询一下服务器关于`query_cache`的配置：

mysql> show variables like 'query_cache%';
    +------------------------------+---------+
    | Variable_name                | Value   |
    +------------------------------+---------+
    | query_cache_limit            | 1048576 |
    | query_cache_min_res_unit     | 4096    |
    | query_cache_size             | 0       |
    | query_cache_type             | OFF     |
    | query_cache_wlock_invalidate | OFF     |
    +------------------------------+---------+
    5 rows in set, 1 warning (0.00 sec)

各字段的解释：

1.  `query_cache_limit`：超过此大小的查询将不缓存。
2.  `query_cache_min_res_unit`：缓存块的最小大小。
3.  `query_cache_size`：查询缓存大小。
4.  `query_cache_type`：缓存类型，决定缓存什么样的查询，示例中表示不缓存 `select sql_no_cache`查询。
5.  `query_cache_wlock_invalidate`：当有其他客户端正在对`MyISAM`表进行写操作时，如果查询在`query cache`中，是否返回`cache`结果还是等写操作完成再读表获取结果。

`query_cache_min_res_unit`的配置是一柄`双刃剑`，默认是`4KB`，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。

`查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%.`

如果查询缓存碎片率超过`20%`，可以用`FLUSH QUERY CACHE`整理缓存碎片，或者减小`query_cache_min_res_unit`，如果你的查询都是小数据量的话。

`查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%.`

查询缓存利用率在`25%`以下的话说明`query_cache_size`设置的过大，可适当减小。

查询缓存利用率在`80％`以上，而且`Qcache_lowmem_prunes > 50`的话，说明`query_cache_size`可能有点小，要不就是碎片太多。

`查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%.`

示例服务器：

1.  查询缓存碎片率 ＝ 20.46％
2.  查询缓存利用率 ＝ 62.26％
3.  查询缓存命中率 ＝ 1.94％

说明命中率很差，可能写操作比较频繁吧，而且可能有些碎片。

### 3.8 排序使用情况 ###

mysql> show global status like 'sort%';
    +-------------------+-------+
    | Variable_name     | Value |
    +-------------------+-------+
    | Sort_merge_passes | 8     |
    | Sort_range        | 247   |
    | Sort_rows         | 7021  |
    | Sort_scan         | 313   |
    +-------------------+-------+
    4 rows in set (0.00 sec)

`Sort_merge_passes`包括两步。

`MySQL`首先会尝试在内存中做排序，使用的内存大小由系统变量 `Sort_buffer_size`决定。

如果它的大小不够把所有的记录都读到内存中，`MySQL`就会把每次在内存中排序的结果存到临时文件中，等`MySQL`找到所有记录之后，再把临时文件中的记录做一次排序。

这再次排序就会增加`Sort_merge_passes`。

实际上，`MySQL`会用另一个临时文件来存再次排序的结果，所以通常会看到 `Sort_merge_passes`增加的数值是建临时文件数的两倍。

因为用到了临时文件，所以速度可能会比较慢，增加`Sort_buffer_size`会减少 `Sort_merge_passes`和创建临时文件的次数。

但盲目的增加`Sort_buffer_size`并不一定能提高速度。

另外，增加`read_rnd_buffer_size`(3.2.3是`record_rnd_buffer_size`)的值对排序的操作也有一点的好处。

### 3.9 文件打开数(open\_files) ###

mysql> show global status like 'open_files';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | Open_files    | 47    |
    +---------------+-------+
    1 row in set (0.00 sec)
    
    mysql> show variables like 'open_files_limit';
    +------------------+-------+
    | Variable_name    | Value |
    +------------------+-------+
    | open_files_limit | 6209  |
    +------------------+-------+
    1 row in set, 1 warning (0.00 sec)

比较合适的设置：`Open_files / open_files_limit * 100% <= 75％.`

### 3.10 查询表锁情况 ###

mysql> show global status like 'table_locks%';
    +-----------------------+-------+
    | Variable_name         | Value |
    +-----------------------+-------+
    | Table_locks_immediate | 353   |
    | Table_locks_waited    | 0     |
    +-----------------------+-------+
    2 rows in set (0.00 sec)

`Table_locks_immediate`表示立即释放表锁数。

`Table_locks_waited`表示需要等待的表锁数。

如果`Table_locks_immediate / Table_locks_waited > 5000`，最好采用`InnoDB`引擎，因为`InnoDB`是行锁而`MyISAM`是表锁，对于高并发写入的应用`InnoDB`效果会好些。

### 3.11 表扫描情况 ###

mysql> show global status like 'handler_read%';
    +-----------------------+--------+
    | Variable_name         | Value  |
    +-----------------------+--------+
    | Handler_read_first    | 2470   |
    | Handler_read_key      | 16165  |
    | Handler_read_last     | 3      |
    | Handler_read_next     | 514168 |
    | Handler_read_prev     | 12     |
    | Handler_read_rnd      | 6302   |
    | Handler_read_rnd_next | 942267 |
    +-----------------------+--------+
    7 rows in set (0.00 sec)

调出服务器完成的查询请求次数：

mysql> show global status like 'com_select';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | Com_select    | 5556  |
    +---------------+-------+
    1 row in set (0.00 sec)

计算表扫描率：

`表扫描率 ＝ Handler_read_rnd_next / Com_select`

如果表扫描率超`4000`，说明进行了太多表扫描，很有可能索引没有建好。

增加`read_buffer_size`值会有一些好处，但最好不要超过`8MB`。

## 4. set global ‘xxx’=value介绍 ##

`set global ‘xxx’=value`设置变量的值。

比如，我想开启慢查询记录(`slow_query_log`)，我可以这样做：

1.  首先查找`slow_query_log`是否开启，如下代码所示：

mysql> show variables like 'slow_query_log';
    +----------------+-------+
    | Variable_name  | Value |
    +----------------+-------+
    | slow_query_log | OFF   |
    +----------------+-------+
    1 row in set, 1 warning (0.00 sec)

`OFF`表示关闭，但实际工作中，最好是打开，方便优化。

1.  接着修改`slow_query_log`的值为`ON`

mysql> set global slow_query_log = ON;
    Query OK, 0 rows affected (0.01 sec)

再次查找`slow_query_log`，发现已然修改为`ON`，如下代码所示：

mysql> show variables like 'slow_query_log';
    +----------------+-------+
    | Variable_name  | Value |
    +----------------+-------+
    | slow_query_log | ON    |
    +----------------+-------+
    1 row in set, 1 warning (0.00 sec)

如果我们不加`global`，修改则会报错：

mysql> set  slow_query_log = OFF;
    ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL

[16cb81bba5b845c5bf57fd55d00f23c7.png]: https://img-blog.csdnimg.cn/16cb81bba5b845c5bf57fd55d00f23c7.png
[62b14236896d45f0a3e8ad995379d7ac.png]: https://img-blog.csdnimg.cn/62b14236896d45f0a3e8ad995379d7ac.png

发表评论取消回复

表情：

评论列表（有 0 条评论，114人围观）

还没有评论，来说两句吧...

相关阅读

相关 python global和globals()函数

.命名空间及作用域的基本概念 python命名空间和作用域及local,global,locals(),globals()区别目录： ...

Love The Way You Lie/ 2024年04月19日 15:48/ 0 赞/ 66 阅读

相关全网详细介绍MySQL中的show variables like ‘%xxx%’、show global status like ‘%xxx%’ 和 set global ‘xxx’=value

文章目录 1. 文章引言 2. show variables介绍 3. show global status介绍 3. 详细介绍 3.

今天药忘吃喽~/ 2023年09月25日 23:01/ 0 赞/ 115 阅读

相关全网详述Mysql查看连接总数、活跃数、最大并发数，以及show status和show global status区别，show status like ‘%变量名%‘；

文章目录 1. 文章引言 2. MySQL连接数 2.1 连接总数 2.2 活跃数 2.3 最大并发数 3. 重

缺乏、安全感/ 2023年09月25日 22:53/ 0 赞/ 108 阅读

相关 mysql 输出表的详细信息详解 show table status like ‘表名‘

在mysql中查看表的详细信息, 可以使用 use 数据库; show table status like '表名称'; 在dos窗口中使用

古城微笑少年丶/ 2022年11月13日 09:26/ 0 赞/ 199 阅读

相关 php中global与$_GLOBALS[]的区别

很多人都认为global和$GLOBALS[]只是写法上面的差别，其实不然。根据官方的解释是 1.$GLOBALS['var']是

叁歲伎倆/ 2022年09月26日 03:27/ 0 赞/ 156 阅读

相关 php中global和$GLOBALS 的区别

<?php $str = 'hello baby!'; function say() { global $str;

川长思鸟来/ 2022年08月26日 03:49/ 0 赞/ 213 阅读

相关 PHP中global关键字和$GLOBALS用法

`<?php $a = 1; / global scope / function Test() { echo $a ; / reference to loc

淡淡的烟草味﹌/ 2022年05月29日 03:09/ 0 赞/ 244 阅读

相关 Mysql show global status 各项指标介绍

Aborted\_clients 指出由于某种原因客户程序不能正常关闭连接而导致失败的连接的数量。如果客户不在退出之前调整mysql\_close()函数，

悠悠/ 2022年05月22日 06:36/ 0 赞/ 244 阅读

相关 show master status, show slave status中的Executed_Gtid_Set

slave 如果server是slave节点，在server上执行show master staus与show slave status显示的Executed\_Gtid

我会带着你远行/ 2022年04月10日 06:16/ 0 赞/ 190 阅读

相关 SHOW STATUS LIKE Last_query_cost

MySQL中可以通过show status like 'last\_query\_cost' 来查看查上一个查询的代价，而且它是io\_cost和cpu\_cost的开

Bertha 。/ 2021年09月24日 11:02/ 0 赞/ 249 阅读