mysql优化（连接数、最大并发数）

淡淡的烟草味﹌ 2023-10-10 12:49 76阅读 0赞

**目录**

1、max\_connections最大连接数

1.1、查询数据库当前设置的最大连接数

1.2、调整max\_connections参数的值

1.2.1、方式一：实时（临时）修改此参数的值

1.2.2、方式二：在配置文件my.ini中设置max\_connections的值

2、max\_used\_connections 查看最大连接数

3、max\_user\_connections 每个MySQL用户的最大连接数

3.1、查看全局每个用户最大连接数max\_user\_connections

3.2、设置全局每个用户最大连接数max\_user\_connections

3.3、 针对某个用户进行最大连接数限制

4、mysql服务器的线程数

4.1、查看mysql的线程情况

4.2、thread\_cache\_size缓存线程 优化

4,2.1、查询thread\_cache\_size

4.2.2、设置线程缓存数量 thread\_cache\_size

--------------------

-- 查看最大连接数
    show variables like '%max_connection%'; 
    
    -- 重新设置最大连接数
    set global max_connections=700;   
    
    -- 查看数据库状态
    -- threads_created：表示创建过的线程数，threads_created过大，
    -- 表明mysql服务器一直在创建线程，这也是比较耗资源，说明服务器不健康。
    show status like  'Threads%';
    
    -- 查询服务器 thread_cache_size 配置
    show variables like 'thread_cache_size';
    -- 设置 线程缓存数量
    set global thread_cache_size=100;

## 1、max\_connections最大连接数 ##

### 1.1、查询数据库当前设置的最大连接数 ###

show variables like '%max_connections%';

![20210510135917423.png][]

> **MySQL无论如何都会保留一个用于管理员（SUPER）登陆的连接**，用于管理员连接数据库进行维护操作，即使当前连接数已经达到了max\_connections。因此MySQL的实际最大可连接数为max\_connections+1；  
> 这个参数实际起作用的最大值（实际最大可连接数）为16384，即该**参数最大值不能超过16384**，即使超过也以16384为准；
> 
> **增加max\_connections参数的值，不会占用太多系统资源**。系统资源（CPU、内存）的占用**主要取决于查询的密度、效率等**；
> 
> 该参数设置过小的最明显特征是出现**”Too many connections”**错误；
> 
> **MySQL比较理想的最大连接数计算方式为**：**max\_used\_connections / max\_connections\*100% ≈ 85%**
> 
> 最大连接数占上限连接数的85%左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。

### 1.2、调整max\_connections参数的值 ###

#### 1.2.1、方式一：实时（临时）修改此参数的值 ####

使用root用户登录。

set global max_connections=1000;

**修改完成后实时生效，无需重启MySQL。**

总体来说，**该参数在服务器资源够用的情况下应该尽量设置大**，以满足多个客户端同时连接的需求。否则将会出现类似”Too many connections”的错误。

#### 1.2.2、方式二：在配置文件my.ini中设置max\_connections的值 ####

找到max\_connections一行，修改为（如果没有，则自己添加）1000.

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ4MjgxNg_size_16_color_FFFFFF_t_70][]

## 2、max\_used\_connections 查看最大连接数 ##

show global status like 'Max_used_connections';

![20210510143130132.png][]  
这两个状态说的是MYSQL SERVER自上次启动起来 **最大连接数量**和**发生的时间**。

> Max\_used\_connections 服务器启动后**已经同时使用的连接的最大数量**。

## 3、max\_user\_connections 每个MySQL用户的最大连接数 ##

### 3.1、查看全局每个用户最大连接数max\_user\_connections ###

-- 查看max_user_connections 默认值（每个用户最大连接数）
    show variables like 'max_user_connections';
    -- 或者
    select @@max_user_connections;

![20210510144203894.png][]

> 默认情况值为 0。代表：不限制用户资源的。

### 3.2、设置全局每个用户最大连接数max\_user\_connections ###

-- 设置max_user_connections
    set global max_user_connections=1; 
    -- 或
    set  @@global.max_user_connections=1;

> 该设置为 实时（临时）修改，在服务器重启后失效，想一直保存的话就放在配置文件里面。

设置后，用该用户重新开一个连接。会出现以下错误提示：意思是这个用户已经达到最大的连接数，不能再连接拥有资源！

![20210510145801848.png][]

### 3.3、 针对某个用户进行最大连接数限制 ###

max\_user\_connections 该参数只能对整体限制资源，不能对某个用户进行限制，如果需要对某个用户限制的话，你可以在权限表里面看到

> **max\_questions**: **0**  
> **max\_updates**: **0**  
> **max\_connections: 0  
> max\_user\_connections**: **0**  
> 这几个参数可以对某个用户进行限制。

## 4、**mysql服务器的线程数** ##

### 4.1、查看mysql的线程情况 ###

show global status like 'Thread%';

![20210510154102272.png][]

参数说明：

> **treads\_cached** ：表示是缓冲池中的线程个数。
> 
> **Threads\_connected** ：表示当前连接的线程数。跟show processlist结果相同。
> 
> **Threads\_created** ：表示创建过的线程数，如果发现Threads\_created值过大的话，表明MySQL服务器一直在创建线程，这是比较耗资源，可以适当增加配置文件中thread\_cache\_size值。
> 
> **Threads\_running** ：这个数值指的是激活的连接数，这个数值一般远低于connected数值。准确的来说Threads\_running当前并发数。

### 4.2、**thread\_cache\_size缓存线程 优化** ###

**thread\_cache\_size**：当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户，而不是销毁(前提是缓存数未达上限)。

即可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善系统性能。

#### 4,2.1、**查询thread\_cache\_size** ####

-- 查询服务器 thread_cache_size 配置
    show variables like 'thread_cache_size';

#### 4.2.2、设置线程缓存数量 **thread\_cache\_size** ####

如果是短连接，适当设置大一点，因为短连接往往需要不停创建，不停销毁，如果大一点，连接线程都处于取用状态，不需要重新创建和销毁，所以对性能肯定是比较大的提升。

对于长连接，不能保证连接的稳定性，所以设置这参数还是有一定必要，可能连接池的问题，会导致连接数据库的不稳定性，也会出现频繁的创建和销毁，但这个情况比较少，如果是长连接，可以设置成小一点，一般在50-100左右。

物理内存设置规则：通过比较Connections 和 Threads\_created 状态的变量，可以看到这个变量的作用。

**方式1：实时（临时）设置，重启后失效。**

-- 设置 线程缓存数量
    set global thread_cache_size=100;

**方式2：配置文件修改。**

> **thread\_concurrency = 100**

[20210510135917423.png]: https://img-blog.csdnimg.cn/20210510135917423.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ4MjgxNg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2021051014184419.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ4MjgxNg==,size_16,color_FFFFFF,t_70
[20210510143130132.png]: https://img-blog.csdnimg.cn/20210510143130132.png
[20210510144203894.png]: https://img-blog.csdnimg.cn/20210510144203894.png
[20210510145801848.png]: https://img-blog.csdnimg.cn/20210510145801848.png
[20210510154102272.png]: https://img-blog.csdnimg.cn/20210510154102272.png