Mysql-数据库优化-连接参数

客户端参数

原文地址

# 连接池配置
# 初始化连接数
spring.datasource.druid.initial-size=1
# 最小空闲连接数，一般设置和initial-size一致
spring.datasource.druid.min-idle=1
# 最大活动连接数，一个数据库能够支撑最大的连接数是多少呢？
spring.datasource.druid.max-active=20
# 从连接池获取连接超时时间
spring.datasource.druid.max-wait=60000
# 配置间隔多久启动一次销毁线程，对连接池内的空闲的connection进行检测，单位是毫秒。
# 1.如果连接空闲并且超过minIdle以外的连接，如果空闲时间超过
minEvictableIdleTimeMillis设置的连接物理关闭。
# 2.在minIdle以内的不处理。
spring.datasource.druid.time-between-eviction-runs-millis=60000
# 配置一个连接在池中连接最小可清理的空闲时间，单位是毫秒
spring.datasource.druid.min-evictable-idle-time-millis=300000
# 打开后，增强timeBetweenEvictionRunsMillis的周期性连接检查，minIdle内的空闲连接
# 设置从连接池获取连接时是否检查连接有效性，true时，每次都检查;false时，不检查
spring.datasource.druid.test-on-borrow=false
# 设置往连接池归还连接时是否检查连接有效性，true时，每次都检查;false时，不检查
spring.datasource.druid.test-on-return=false
# 设置从连接池获取连接时是否检查连接有效性
# 为true时，如果连接空闲时间超过minEvictableIdleTimeMillis进行检查，否则不检查
# 为false时，不检查
spring.datasource.druid.test-while-idle=true
# 检验连接是否有效的查询语句
# 如果数据库Driver支持ping()方法，则优先使用ping()方法进行检查，否则使用
# validationQuery查询进行检查,用于检测sql语句是否可以正常执行
spring.datasource.druid.validation-query=select 1 from dual
# 每次检查强制验证连接有效性
spring.datasource.druid.keep-alive=true

数据库连接池化的思想也就是提前将连接创建好并保存用的时候直接拿来用，同时要进行管理，也就有了上面这些相关的参数，如果不适用池化的思想，数据库会有一个最大的连接数，如果超过这个最大连接数，数据库直接无法处客户端的查询请求。可以通过下面这个命令来查看：

SHOW VARIABLES LIKE 'max_connections';

我们可以看到默认值为151

那我们接下来做个实验：

可以看到一旦达到最大连接数就会报错，异常比例是相当高的，但是使用连接池就不会有这种大量报错的现象。

除了使用连接池，我们对一些参数也可以做一些优化：

spring.datasource.druid.initial-size=1
# 最小空闲连接数，一般设置和initial-size一致
spring.datasource.druid.min-idle=1
# 最大活动连接数
spring.datasource.druid.max-active=20

这个参数是管理连接池数量的，和线程池的思想一样，spring.datasource.druid.min-idle这个相当于核心线程数，无论怎样连接池始终保持这么多的连接数量。spring.datasource.druid.max-active=20这个是最大线程数，如果超过这个线程数会等待前面的处理完才能拿到连接。这个参数的值不是越大越好，真实业务可能需要压测得出一个合理值。

spring.datasource.druid.max-wait=60000

这个参数指定了一个连接等待的最大时间，如果超过这个时间可以直接失败，以免造成阻塞，越来越多的请求阻塞在服务端使得服务端压力更大，吞吐量进一步下降乃至发生宕机。

jdbc:mysql:/ip:3306/hero_all?serverTimezone=UTC&characterEncoding=utf-8&connectionTimeout=3000&socketTimeout=1200

• connectTimeout：表示等待和MySQL数据库建立socket链接的超时时间。如果与服务器(这里指数据库)请求建立连接的时间超过ConnectionTimeOut，就会抛连接超时异常，即服务器连接超时。
• socketTimeout：表示客户端和MySQL数据库建立socket后，读写socket时的等待的超时时间。如果与服务器连接成功，就开始数据传输了。如果服务器处理数据等待用时过长，超过了SocketTimeOut，就会抛出SocketTimeOutExceptin，即服务器响应超时，服务器没有在规定的时间内返回给客户端数据。

总结在客户端连接端，能做的优化也就是合理的配置连接池的参数，及优化等待时间

Msql 参数

下列内容来源于：IT老齐

#max_connections 代表数据库同时允许的最大允许连接数
#连接有两种常见状态:sleep / query 
#sleep 代表连接处于闲置状态
#query 代表连接正处于处理任务的状态
#sleep + query 连接的总量不能超过max_connections的设置值
#否则会出现经典错误:"ERROR 1040：Too many connetcions"
show VARIABLES like 'max_connections';
set global max_connections=300;

show status like 'Threads%';
#Threads_connected 代表当前已经有多少连接(Sleep+Query)
#Threads_created 代表历史总共创建过多少个数据库连接 
#Threads_running 代表有几个连接正处于"工作"状态,也是目前的并发数
#Threads_cached 共缓存过多少连接.如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)。
set global thread_cache_size=80;
#MySQL历史运行过程中最大连接数的数量及时点
show status like 'Max_used_connections%';


#back_log 设置保存多少数据库请求到堆栈(缓冲区)中.
#也就是说，如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。将会报：
unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时.
show VARIABLES LIKE 'back_log'


#wait_timeout和interactive_timeout
#这两个参数都是至超过一段时间后,数据库连接自动关闭(默认28800秒,即8小时)
#interactive_timeout针对交互式连接，wait_timeout针对非交互式连接。
#说得直白一点，通过mysql客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接。 
show VARIABLES LIKE 'wait_timeout';
show VARIABLES LIKE 'interactive_timeout';

#查看当前数据库连接详细状况
show processlist;