本文档风哥主要介绍MySQL连接池优化的实战技巧，包括连接池配置、监控、应用场景等内容，风哥教程参考MySQL官方文档，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。更多视频教程www.fgedu.net.cn
Part01-基础概念与理论知识
1.1 连接池概述
连接池是管理数据库连接的重要组件：
1. 连接池定义
连接池特点：
– 预先创建数据库连接
– 连接复用机制
– 统一连接管理
– 减少连接开销
连接池优势：
– 减少连接创建开销
– 提高响应速度
– 控制连接数量
– 资源复用
连接池用途：
– 高并发应用
– 频繁数据库访问
– 连接资源管理
– 性能优化
2. MySQL连接管理
查看当前连接：
mysql> SHOW PROCESSLIST;
输出示例：
+—-+——+———–+——+———+——+———-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+——+———+——+———-+——————+
| 1 | root | localhost | test | Sleep | 10 | | NULL |
| 2 | app | localhost | prod | Query | 0 | starting | SHOW PROCESSLIST |
+—-+——+———–+——+———+——+———-+——————+
查看连接统计：
mysql> SHOW STATUS LIKE ‘Threads%’;
输出示例：
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 10 |
| Threads_connected | 50 |
| Threads_created | 100 |
| Threads_running | 5 |
+——————-+——-+
3. 连接参数配置
查看连接参数：
mysql> SHOW VARIABLES LIKE ‘%connect%’;
输出示例：
+————————–+——-+
| Variable_name | Value |
+————————–+——-+
| max_connect_errors | 100 |
| max_connections | 151 |
| max_user_connections | 0 |
+————————–+——-+
设置最大连接数：
mysql> SET GLOBAL max_connections = 500;
输出示例：
Query OK, 0 rows affected (0.00 sec)
查看线程缓存：
mysql> SHOW VARIABLES LIKE ‘thread_cache_size’;
输出示例：
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| thread_cache_size | 100 |
+——————-+——-+
4. 连接生命周期
连接创建过程：
1. 客户端发起连接请求
2. MySQL创建新线程
3. 进行权限验证
4. 建立连接会话
5. 执行SQL操作
6. 连接关闭或返回池
查看连接创建统计：
mysql> SELECT
VARIABLE_NAME,
VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
‘Connections’,
‘Threads_created’,
‘Threads_cached’
);
输出示例：
+—————–+—————+
| VARIABLE_NAME | VARIABLE_VALUE|
+—————–+—————+
| Connections | 10000 |
| Threads_created | 500 |
| Threads_cached | 100 |
+—————–+—————+
5. 连接池工作原理
连接池工作流程：
+——————-+——————+——————+
| 阶段 | 无连接池 | 有连接池 |
+——————-+——————+——————+
| 获取连接 | 每次创建新连接 | 从池中获取 |
| 执行查询 | 直接执行 | 直接执行 |
| 释放连接 | 关闭连接 | 返回连接池 |
| 性能 | 较低 | 较高 |
+——————-+——————+——————+
连接池核心参数：
– 最小连接数（minPoolSize）
– 最大连接数（maxPoolSize）
– 连接超时（connectionTimeout）
– 空闲超时（idleTimeout）
– 最大生命周期（maxLifetime）
1.2 连接池类型
MySQL支持多种连接池实现：
1. MySQL内置线程缓存
查看线程缓存配置：
mysql> SHOW VARIABLES LIKE ‘thread_cache_size’;
输出示例：
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| thread_cache_size | 100 |
+——————-+——-+
设置线程缓存：
mysql> SET GLOBAL thread_cache_size = 200;
输出示例：
Query OK, 0 rows affected (0.00 sec)
查看缓存效果：
mysql> SHOW STATUS LIKE ‘Threads%’;
输出示例：
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 150 |
| Threads_connected | 50 |
| Threads_created | 200 |
+——————-+——-+
计算线程缓存命中率：
mysql> SELECT
ROUND((1 – (
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Threads_created’) /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Connections’)
)) * 100, 2) AS thread_cache_hit_ratio;
输出示例：
+————————+
| thread_cache_hit_ratio |
+————————+
| 98.00 |
+————————+
2. HikariCP连接池（Java）
HikariCP配置示例：
# application.properties
spring.datasource.hikari.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.hikari.jdbc-url=jdbc:mysql://localhost:3306/production_db
spring.datasource.hikari.username=app_user
spring.datasource.hikari.password=password
# 连接池配置
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.maximum-pool-size=50
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.pool-name=AppHikariPool
启动应用：
java -jar app.jar
输出示例：
HikariPool-1 – Starting…
HikariPool-1 – Added connection
HikariPool-1 – Start completed.
3. Druid连接池（Java）
Druid配置示例：
# application.properties
spring.datasource.druid.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.url=jdbc:mysql://localhost:3306/production_db
spring.datasource.druid.username=app_user
spring.datasource.druid.password=password
# 连接池配置
spring.datasource.druid.initial-size=10
spring.datasource.druid.min-idle=10
spring.datasource.druid.max-active=50
spring.datasource.druid.max-wait=60000
spring.datasource.druid.time-between-eviction-runs-millis=60000
spring.datasource.druid.min-evictable-idle-time-millis=300000
# 监控配置
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
访问监控页面：
http://localhost:8080/druid/
输出示例：
Druid Stat View
4. c3p0连接池（Java）
c3p0配置示例：
# c3p0.properties
c3p0.driverClass=com.mysql.cj.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql://localhost:3306/production_db
c3p0.user=app_user
c3p0.password=password
# 连接池配置
c3p0.minPoolSize=10
c3p0.maxPoolSize=50
c3p0.acquireIncrement=5
c3p0.maxIdleTime=300
c3p0.idleConnectionTestPeriod=60
c3p0.acquireTimeout=30
5. Python连接池
SQLAlchemy连接池配置：
# Python代码
from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool
engine = create_engine(
‘mysql+pymysql://app_user:password@localhost/production_db’,
poolclass=QueuePool,
pool_size=10,
max_overflow=20,
pool_timeout=30,
pool_recycle=3600,
pool_pre_ping=True
)
使用连接：
# Python代码
with engine.connect() as conn:
result = conn.execute(‘SELECT * FROM users’)
for row in result:
print(row)
输出示例：
(1, ‘user1’, ‘user1@example.com’)
(2, ‘user2’, ‘user2@example.com’)
DBUtils连接池配置：
# Python代码
import pymysql
from dbutils.pooled_db import PooledDB
pool = PooledDB(
creator=pymysql,
maxconnections=50,
mincached=10,
maxcached=20,
host=’localhost’,
user=’app_user’,
password=’password’,
database=’production_db’
)
conn = pool.connection()
cursor = conn.cursor()
cursor.execute(‘SELECT * FROM users’)
print(cursor.fetchall())
conn.close()
1.3 连接池特性
连接池具有独特的特性需要了解：
1. 连接复用
特点：连接可被多次复用
无连接池模式：
— 每次请求创建新连接
mysql> SHOW STATUS LIKE ‘Connections’;
— 值持续增长
有连接池模式：
— 连接复用，创建次数减少
mysql> SHOW STATUS LIKE ‘Connections’;
— 值增长缓慢
查看连接复用效果：
mysql> SELECT
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Connections’) AS total_connections,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Threads_created’) AS threads_created,
ROUND((SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Connections’) /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Threads_created’), 2) AS reuse_ratio;
输出示例：
+——————+—————–+————-+
| total_connections| threads_created | reuse_ratio |
+——————+—————–+————-+
| 10000 | 500 | 20.00 |
+——————+—————–+————-+
2. 连接预热
特点：预先创建连接减少延迟
HikariCP预热配置：
spring.datasource.hikari.minimum-idle=10
Druid预热配置：
spring.datasource.druid.initial-size=10
验证预热效果：
mysql> SELECT COUNT(*) FROM information_schema.PROCESSLIST
WHERE USER = ‘app_user’;
输出示例：
+———-+
| COUNT(*) |
+———-+
| 10 |
+———-+
3. 连接健康检查
特点：自动检测和移除无效连接
HikariCP健康检查：
spring.datasource.hikari.connection-test-query=SELECT 1
spring.datasource.hikari.validation-timeout=3000
Druid健康检查：
spring.datasource.druid.validation-query=SELECT 1
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.test-on-borrow=true
测试健康检查：
mysql> KILL CONNECTION 123;
输出示例：
Query OK, 0 rows affected (0.00 sec)
— 连接池会自动检测并重建连接
4. 连接超时控制
特点：控制连接获取和空闲超时
获取连接超时：
spring.datasource.hikari.connection-timeout=30000
空闲连接超时：
spring.datasource.hikari.idle-timeout=300000
连接最大生命周期：
spring.datasource.hikari.max-lifetime=1800000
查看超时设置：
mysql> SHOW VARIABLES LIKE ‘%timeout%’;
输出示例：
+—————————–+———-+
| Variable_name | Value |
+—————————–+———-+
| connect_timeout | 10 |
| wait_timeout | 28800 |
| interactive_timeout | 28800 |
+—————————–+———-+
5. 连接泄漏检测
特点：检测未正确关闭的连接
HikariCP泄漏检测：
spring.datasource.hikari.leak-detection-threshold=60000
Druid泄漏检测：
spring.datasource.druid.remove-abandoned=true
spring.datasource.druid.remove-abandoned-timeout=300
spring.datasource.druid.log-abandoned=true
查看泄漏连接：
mysql> SELECT * FROM information_schema.PROCESSLIST
WHERE TIME > 300 AND COMMAND = ‘Sleep’;
输出示例：
+—-+——+———–+——+———+——+———-+——+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+——+———+——+———-+——+
| 45 | app | localhost | prod | Sleep | 500 | | NULL |
+—-+——+———–+——+———+——+———-+——+
Part02-生产环境规划与建议
2.1 连接池设计原则
合理的连接池设计是性能优化的基础：
1. 连接数规划
计算公式：
连接数 = (核心数 * 2) + 有效磁盘数
示例计算：
– 8核CPU + 1块SSD = 17个连接
– 16核CPU + 2块SSD = 34个连接
MySQL最大连接数设置：
mysql> SET GLOBAL max_connections = 500;
输出示例：
Query OK, 0 rows affected (0.00 sec)
连接池最大连接数：
maxPoolSize = max_connections / 应用实例数
示例：
– MySQL max_connections = 500
– 应用实例数 = 5
– 每个实例 maxPoolSize = 100
2. 连接池参数设计
核心参数设计：
+——————-+——————+——————+
| 参数 | 推荐值 | 说明 |
+——————-+——————+——————+
| minimumIdle | 10-20 | 最小空闲连接 |
| maximumPoolSize | 根据业务计算 | 最大连接数 |
| connectionTimeout | 30000 | 获取连接超时 |
| idleTimeout | 300000 | 空闲超时 |
| maxLifetime | 1800000 | 最大生命周期 |
+——————-+——————+——————+
HikariCP推荐配置：
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.maximum-pool-size=50
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1800000
3. 连接验证设计
连接验证配置：
spring.datasource.hikari.connection-test-query=SELECT 1
spring.datasource.hikari.validation-timeout=3000
MySQL端超时配置：
mysql> SET GLOBAL wait_timeout = 28800;
mysql> SET GLOBAL interactive_timeout = 28800;
输出示例：
Query OK, 0 rows affected (0.00 sec)
验证连接有效性：
mysql> SELECT 1;
输出示例：
+—+
| 1 |
+—+
| 1 |
+—+
4. 连接预热设计
预热配置：
spring.datasource.hikari.minimum-idle=20
应用启动后验证：
mysql> SELECT COUNT(*) FROM information_schema.PROCESSLIST
WHERE USER = ‘app_user’;
输出示例：
+———-+
| COUNT(*) |
+———-+
| 20 |
+———-+
5. 监控设计
Druid监控配置：
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.web-stat-filter.enabled=true
HikariCP监控配置：
management.endpoint.health.show-details=always
management.endpoints.web.exposure.include=health,metrics
访问监控端点：
curl http://localhost:8080/actuator/health
输出示例：
{
“status”: “UP”,
“components”: {
“db”: {
“status”: “UP”,
“details”: {
“database”: “MySQL”,
“validationQuery”: “isValid()”
}
}
}
}
2.2 连接池优化策略
制定合理的连接池优化策略：
1. 连接数优化
分析当前连接使用：
mysql> SELECT
COUNT(*) AS total_connections,
SUM(CASE WHEN COMMAND = ‘Sleep’ THEN 1 ELSE 0 END) AS sleep_connections,
SUM(CASE WHEN COMMAND = ‘Query’ THEN 1 ELSE 0 END) AS active_connections
FROM information_schema.PROCESSLIST;
输出示例：
+——————+——————+——————-+
| total_connections| sleep_connections| active_connections|
+——————+——————+——————-+
| 100 | 80 | 20 |
+——————+——————+——————-+
优化建议：
– 如果sleep_connections过高，减少minimumIdle
– 如果active_connections经常达到上限，增加maximumPoolSize
2. 连接超时优化
查看当前超时设置：
mysql> SHOW VARIABLES LIKE ‘%timeout%’;
输出示例：
+—————————–+———-+
| Variable_name | Value |
+—————————–+———-+
| connect_timeout | 10 |
| wait_timeout | 28800 |
| interactive_timeout | 28800 |
+—————————–+———-+
优化MySQL超时：
mysql> SET GLOBAL wait_timeout = 3600;
mysql> SET GLOBAL interactive_timeout = 3600;
输出示例：
Query OK, 0 rows affected (0.00 sec)
优化连接池超时：
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
3. 连接泄漏优化
开启泄漏检测：
spring.datasource.hikari.leak-detection-threshold=60000
查看长时间空闲连接：
mysql> SELECT * FROM information_schema.PROCESSLIST
WHERE TIME > 60 AND COMMAND = ‘Sleep’;
输出示例：
+—-+——+———–+——+———+——+———-+——+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+——+———+——+———-+——+
| 45 | app | localhost | prod | Sleep | 120 | | NULL |
+—-+——+———–+——+———+——+———-+——+
清理长时间空闲连接：
mysql> KILL CONNECTION 45;
输出示例：
Query OK, 0 rows affected (0.00 sec)
4. 连接验证优化
优化验证查询：
— 使用简单的验证查询
spring.datasource.hikari.connection-test-query=SELECT 1
— 或使用JDBC4的isValid方法
spring.datasource.hikari.connection-test-query=
验证连接有效性：
mysql> SELECT 1;
输出示例：
+—+
| 1 |
+—+
| 1 |
+—+
5. 连接池监控优化
创建监控视图：
mysql> CREATE VIEW v_connection_stats AS
SELECT
USER,
HOST,
DB,
COUNT(*) AS connection_count,
SUM(CASE WHEN COMMAND = ‘Sleep’ THEN 1 ELSE 0 END) AS sleep_count,
SUM(CASE WHEN COMMAND = ‘Query’ THEN 1 ELSE 0 END) AS query_count,
AVG(TIME) AS avg_time
FROM information_schema.PROCESSLIST
GROUP BY USER, HOST, DB;
查询监控数据：
mysql> SELECT * FROM v_connection_stats;
输出示例：
+——+———–+——+—————–+————-+————+———-+
| USER | HOST | DB | connection_count| sleep_count | query_count| avg_time |
+——+———–+——+—————–+————-+————+———-+
| app | localhost | prod | 50 | 40 | 10 | 10.00 |
+——+———–+——+—————–+————-+————+———-+
2.3 连接池监控
建立完善的连接池监控体系：
1. MySQL连接监控
查看当前连接：
mysql> SHOW PROCESSLIST;
输出示例：
+—-+——+———–+——+———+——+———-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+——+———+——+———-+——————+
| 1 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
| 2 | app | localhost | prod | Sleep | 10 | | NULL |
+—-+——+———–+——+———+——+———-+——————+
查看连接统计：
mysql> SHOW STATUS LIKE ‘Threads%’;
输出示例：
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 10 |
| Threads_connected | 50 |
| Threads_created | 100 |
| Threads_running | 5 |
+——————-+——-+
2. 连接池状态监控
HikariCP JMX监控：
# 开启JMX
spring.datasource.hikari.register-mbeans=true
# 使用jconsole查看
jconsole
输出示例：
MBean: com.zaxxer.hikari:type=Pool (HikariPool-1)
– TotalConnections: 50
– ActiveConnections: 10
– IdleConnections: 40
– ThreadsAwaitingConnection: 0
Druid监控页面：
http://localhost:8080/druid/
输出示例：
Druid Stat View
– Active Count: 10
– Pooling Count: 40
– Wait Thread Count: 0
3. 连接性能监控
监控连接获取时间：
mysql> SELECT
EVENT_NAME,
COUNT_STAR,
SUM_TIMER_WAIT / 1000000000 AS total_seconds,
AVG_TIMER_WAIT / 1000000 AS avg_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE ‘%connection%’
ORDER BY COUNT_STAR DESC;
输出示例：
+—————————+————+—————+——–+
| EVENT_NAME | COUNT_STAR | total_seconds | avg_ms |
+—————————+————+—————+——–+
| wait/io/socket/connection | 10000 | 10.00 | 1.00 |
+—————————+————+—————+——–+
4. 连接错误监控
监控连接错误：
mysql> SHOW STATUS LIKE ‘%connect%’;
输出示例：
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| Aborted_connects | 5 |
| Connection_errors… | 0 |
+———————-+——-+
查看错误日志：
cat /var/log/mysql/error.log | grep -i connection
输出示例：
2026-04-01T10:00:00.000000Z 5 [Note] Access denied for user ‘app’@’localhost’
5. 告警配置
创建告警存储过程：
mysql> DELIMITER //
mysql> CREATE PROCEDURE check_connection_health()
BEGIN
DECLARE active_conn INT;
DECLARE max_conn INT;
DECLARE usage_ratio DECIMAL(5,2);
SELECT VARIABLE_VALUE INTO active_conn
FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Threads_connected’;
SELECT VARIABLE_VALUE INTO max_conn
FROM performance_schema.global_variables
WHERE VARIABLE_NAME = ‘max_connections’;
SET usage_ratio = (active_conn / max_conn) * 100;
IF usage_ratio > 80 THEN
SELECT CONCAT(‘Warning: Connection usage is ‘, usage_ratio, ‘%’) AS alert;
ELSE
SELECT CONCAT(‘OK: Connection usage is ‘, usage_ratio, ‘%’) AS status;
END IF;
END //
mysql> DELIMITER ;
执行检查：
mysql> CALL check_connection_health();
输出示例：
+——————————————+
| status |
+——————————————+
| OK: Connection usage is 30.00% |
+——————————————+
Part03-生产环境项目实施方案
3.1 连接池实施规范
制定连接池实施规范确保一致性：
1. 连接池配置规范
HikariCP标准配置：
# application-prod.properties
spring.datasource.hikari.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.hikari.jdbc-url=jdbc:mysql://localhost:3306/production_db?useSSL=false&serverTimezone=UTC
spring.datasource.hikari.username=app_user
spring.datasource.hikari.password=${DB_PASSWORD}
# 连接池核心配置
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.maximum-pool-size=50
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1800000
# 连接验证配置
spring.datasource.hikari.connection-test-query=SELECT 1
spring.datasource.hikari.validation-timeout=3000
# 泄漏检测
spring.datasource.hikari.leak-detection-threshold=60000
# 监控配置
spring.datasource.hikari.register-mbeans=true
spring.datasource.hikari.pool-name=AppPool
Druid标准配置：
# application-prod.properties
spring.datasource.druid.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.url=jdbc:mysql://localhost:3306/production_db
spring.datasource.druid.username=app_user
spring.datasource.druid.password=${DB_PASSWORD}
# 连接池核心配置
spring.datasource.druid.initial-size=10
spring.datasource.druid.min-idle=10
spring.datasource.druid.max-active=50
spring.datasource.druid.max-wait=60000
# 连接验证配置
spring.datasource.druid.validation-query=SELECT 1
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
# 监控配置
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.web-stat-filter.enabled=true
2. MySQL端配置规范
配置文件设置：
vim /etc/my.cnf
[mysqld]
max_connections = 500
thread_cache_size = 100
wait_timeout = 28800
interactive_timeout = 28800
max_connect_errors = 1000
重启MySQL：
systemctl restart mysqld
输出示例：
MySQL service restarted.
验证配置：
mysql> SELECT
@@max_connections,
@@thread_cache_size,
@@wait_timeout;
输出示例：
+——————-+———————+—————+
| @@max_connections | @@thread_cache_size | @@wait_timeout|
+——————-+———————+—————+
| 500 | 100 | 28800 |
+——————-+———————+—————+
3. 用户权限规范
创建应用用户：
mysql> CREATE USER ‘app_user’@’%’ IDENTIFIED BY ‘secure_password’;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON production_db.* TO ‘app_user’@’%’;
mysql> FLUSH PRIVILEGES;
输出示例：
Query OK, 0 rows affected (0.01 sec)
限制用户连接数：
mysql> ALTER USER ‘app_user’@’%’ WITH MAX_USER_CONNECTIONS 100;
输出示例：
Query OK, 0 rows affected (0.01 sec)
4. 连接池命名规范
命名约定：
+——————-+————————+
| 应用 | 连接池名称 |
+——————-+————————+
| 主应用 | MainAppPool |
| 报表服务 | ReportPool |
| 批处理服务 | BatchPool |
+——————-+————————+
配置连接池名称：
spring.datasource.hikari.pool-name=MainAppPool
5. 文档规范
创建连接池文档表：
mysql> CREATE TABLE connection_pool_config (
id INT AUTO_INCREMENT PRIMARY KEY,
app_name VARCHAR(100),
pool_name VARCHAR(100),
max_pool_size INT,
min_idle INT,
mysql_host VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
输出示例：
Query OK, 0 rows affected (0.02 sec)
记录配置：
mysql> INSERT INTO connection_pool_config
(app_name, pool_name, max_pool_size, min_idle, mysql_host)
VALUES (‘MainApp’, ‘MainAppPool’, 50, 10, ‘localhost’);
输出示例：
Query OK, 1 row affected (0.01 sec)
3.2 连接池应用场景
连接池在不同场景下的应用：
1. 高并发Web应用
配置：
spring.datasource.hikari.maximum-pool-size=100
spring.datasource.hikari.minimum-idle=20
spring.datasource.hikari.connection-timeout=10000
MySQL配置：
mysql> SET GLOBAL max_connections = 1000;
输出示例：
Query OK, 0 rows affected (0.00 sec)
监控连接使用：
mysql> SELECT COUNT(*) FROM information_schema.PROCESSLIST;
输出示例：
+———-+
| COUNT(*) |
+———-+
| 150 |
+———-+
2. 批处理应用
配置：
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.max-lifetime=3600000
批处理代码示例：
// Java代码
@Transactional
public void batchInsert(List dataList) {
jdbcTemplate.batchUpdate(
“INSERT INTO table (col1, col2) VALUES (?, ?)”,
dataList, 1000,
(ps, data) -> {
ps.setString(1, data.getCol1());
ps.setString(2, data.getCol2());
}
);
}
3. 微服务架构
每个服务独立连接池：
# 服务A
spring.datasource.hikari.pool-name=ServiceAPool
spring.datasource.hikari.maximum-pool-size=30
# 服务B
spring.datasource.hikari.pool-name=ServiceBPool
spring.datasource.hikari.maximum-pool-size=20
查看各服务连接：
mysql> SELECT USER, COUNT(*) FROM information_schema.PROCESSLIST
GROUP BY USER;
输出示例：
+——+———-+
| USER | COUNT(*) |
+——+———-+
| svc_a| 25 |
| svc_b| 15 |
+——+———-+
4. 读写分离场景
主库连接池：
spring.datasource.master.hikari.pool-name=MasterPool
spring.datasource.master.hikari.maximum-pool-size=30
从库连接池：
spring.datasource.slave.hikari.pool-name=SlavePool
spring.datasource.slave.hikari.maximum-pool-size=50
验证连接分布：
mysql> SELECT HOST, COUNT(*) FROM information_schema.PROCESSLIST
WHERE USER = ‘app_user’
GROUP BY HOST;
输出示例：
+———–+———-+
| HOST | COUNT(*) |
+———–+———-+
| master:3306| 25 |
| slave:3306 | 40 |
+———–+———-+
5. 多租户场景
动态连接池配置：
// Java代码
public DataSource createTenantDataSource(String tenantId) {
HikariConfig config = new HikariConfig();
config.setPoolName(“Tenant_” + tenantId + “_Pool”);
config.setJdbcUrl(“jdbc:mysql://localhost:3306/tenant_” + tenantId);
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
return new HikariDataSource(config);
}
查看租户连接：
mysql> SELECT DB, COUNT(*) FROM information_schema.PROCESSLIST
GROUP BY DB;
输出示例：
+———-+———-+
| DB | COUNT(*) |
+———-+———-+
| tenant_1 | 15 |
| tenant_2 | 10 |
+———-+———-+
3.3 连接池维护
连接池维护是长期运营的重要工作：
1. 连接池监控
查看连接池状态：
mysql> SELECT
USER,
HOST,
DB,
COMMAND,
TIME,
STATE
FROM information_schema.PROCESSLIST
WHERE USER = ‘app_user’
ORDER BY TIME DESC;
输出示例：
+——+———–+——+———+——+———-+
| USER | HOST | DB | COMMAND | TIME | STATE |
+——+———–+——+———+——+———-+
| app | localhost | prod | Sleep | 100 | |
| app | localhost | prod | Query | 0 | starting |
+——+———–+——+———+——+———-+
2. 连接清理
清理长时间空闲连接：
mysql> SELECT CONCAT(‘KILL ‘, id, ‘;’)
FROM information_schema.PROCESSLIST
WHERE TIME > 300 AND COMMAND = ‘Sleep’;
输出示例：
+———————-+
| CONCAT(‘KILL ‘, id, ‘;’) |
+———————-+
| KILL 45; |
| KILL 46; |
+———————-+
执行清理：
mysql> KILL CONNECTION 45;
输出示例：
Query OK, 0 rows affected (0.00 sec)
3. 连接池重启
应用重启：
systemctl restart app-service
输出示例：
Application service restarted.
验证连接重建：
mysql> SELECT COUNT(*) FROM information_schema.PROCESSLIST
WHERE USER = ‘app_user’;
输出示例：
+———-+
| COUNT(*) |
+———-+
| 10 |
+———-+
4. 配置调整
动态调整MySQL连接数：
mysql> SET GLOBAL max_connections = 600;
输出示例：
Query OK, 0 rows affected (0.00 sec)
应用配置调整后重启：
systemctl restart app-service
输出示例：
Application service restarted.
5. 问题排查
排查连接超时：
mysql> SHOW VARIABLES LIKE ‘%timeout%’;
输出示例：
+—————————–+———-+
| Variable_name | Value |
+—————————–+———-+
| wait_timeout | 28800 |
| interactive_timeout | 28800 |
+—————————–+———-+
排查连接错误：
mysql> SHOW STATUS LIKE ‘%connect%’;
输出示例：
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| Aborted_connects | 5 |
| Connections | 10000 |
+———————-+——-+
Part04-生产案例与实战讲解
4.1 Java连接池案例
以下是Java连接池配置的实战案例：
# 案例：Spring Boot + HikariCP
# 问题描述：
# 高并发场景下连接池配置优化
# 步骤1：添加依赖
# pom.xml
# 步骤2：配置连接池
# application.yml
spring:
datasource:
hikari:
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/production_db
username: app_user
password: ${DB_PASSWORD}
minimum-idle: 20
maximum-pool-size: 100
connection-timeout: 30000
idle-timeout: 300000
max-lifetime: 1800000
connection-test-query: SELECT 1
leak-detection-threshold: 60000
pool-name: AppPool
# 步骤3：启动应用
mvn spring-boot:run
# 输出示例：
# HikariPool-1 – Starting…
# HikariPool-1 – Added connection
# HikariPool-1 – Start completed.
# 步骤4：验证连接池
mysql> SELECT COUNT(*) FROM information_schema.PROCESSLIST
WHERE USER = ‘app_user’;
# 输出示例：
# +———-+
# | COUNT(*) |
# +———-+
# | 20 |
# +———-+
# 步骤5：压测验证
ab -n 10000 -c 100 http://localhost:8080/api/test
# 输出示例：
# Requests per second: 5000 [#/sec]
# 性能对比：
# +——————+————+————+
# | 配置 | 无连接池 | 有连接池 |
# +——————+————+————+
# | QPS | 500 | 5000 |
# | 响应时间 | 200ms | 20ms |
# +——————+————+————+
4.2 Python连接池案例
以下是Python连接池配置的实战案例：
# 案例：SQLAlchemy连接池
# 问题描述：
# Python应用连接池配置优化
# 步骤1：安装依赖
pip install sqlalchemy pymysql
# 输出示例：
# Successfully installed sqlalchemy pymysql
# 步骤2：配置连接池
# Python代码
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine(
‘mysql+pymysql://app_user:password@localhost/production_db’,
pool_size=20,
max_overflow=10,
pool_timeout=30,
pool_recycle=3600,
pool_pre_ping=True,
echo=True
)
Session = sessionmaker(bind=engine)
# 步骤3：使用连接池
# Python代码
def get_users():
session = Session()
try:
result = session.execute(‘SELECT * FROM users’)
return result.fetchall()
finally:
session.close()
# 步骤4：验证连接
users = get_users()
print(users)
# 输出示例：
# [(1, ‘user1’), (2, ‘user2’)]
# 步骤5：监控连接
mysql> SELECT COUNT(*) FROM information_schema.PROCESSLIST
WHERE USER = ‘app_user’;
# 输出示例：
# +———-+
# | COUNT(*) |
# +———-+
# | 20 |
# +———-+
4.3 性能优化案例
以下是连接池性能优化的实战案例：
# 案例：连接池性能调优
# 问题描述：
# 连接池配置不当导致性能问题
# 步骤1：分析当前状态
mysql> SELECT
COUNT(*) AS total,
SUM(CASE WHEN COMMAND = ‘Sleep’ THEN 1 ELSE 0 END) AS sleep,
SUM(CASE WHEN COMMAND = ‘Query’ THEN 1 ELSE 0 END) AS active
FROM information_schema.PROCESSLIST;
# 输出示例：
# +——-+——-+——–+
# | total | sleep | active |
# +——-+——-+——–+
# | 200 | 180 | 20 |
# +——-+——-+——–+
# 步骤2：识别问题
# – Sleep连接过多
# – 连接池配置过大
# 步骤3：优化配置
# application.yml
spring:
datasource:
hikari:
minimum-idle: 10
maximum-pool-size: 50
idle-timeout: 60000
max-lifetime: 1800000
# 步骤4：重启应用
systemctl restart app-service
# 输出示例：
# Application service restarted.
# 步骤5：验证优化效果
mysql> SELECT
COUNT(*) AS total,
SUM(CASE WHEN COMMAND = ‘Sleep’ THEN 1 ELSE 0 END) AS sleep,
SUM(CASE WHEN COMMAND = ‘Query’ THEN 1 ELSE 0 END) AS active
FROM information_schema.PROCESSLIST;
# 输出示例：
# +——-+——-+——–+
# | total | sleep | active |
# +——-+——-+——–+
# | 60 | 40 | 20 |
# +——-+——-+——–+
# 性能对比：
# +——————+————+————+
# | 指标 | 优化前 | 优化后 |
# +——————+————+————+
# | 总连接数 | 200 | 60 |
# | 空闲连接数 | 180 | 40 |
# | 连接利用率 | 10% | 33% |
# +——————+————+————+
Part05-风哥经验总结与分享
5.1 连接池最佳实践
以下是MySQL连接池的最佳实践：
1. 配置原则
– 根据业务需求设置连接数
– minimumIdle不宜过大
– maximumPoolSize需要合理计算
– 设置合适的超时时间
2. 监控原则
– 监控连接使用率
– 监控连接等待时间
– 监控连接泄漏
– 定期检查连接状态
3. 维护原则
– 定期清理空闲连接
– 监控连接错误
– 及时调整配置
– 做好文档记录
4. 安全原则
– 使用专用数据库用户
– 限制用户连接数
– 定期更换密码
– 监控异常连接
5. 性能原则
– 预热连接池
– 使用连接验证
– 避免连接泄漏
– 合理设置超时
5.2 连接池限制
以下是MySQL连接池的主要限制：
1. 连接数限制
– 受MySQL max_connections限制
– 受系统资源限制
– 受网络连接限制
2. 内存限制
– 每个连接占用内存
– 大量连接消耗内存
– 需要合理规划
3. 性能限制
– 连接创建开销
– 连接验证开销
– 锁竞争影响
4. 功能限制
– 不支持跨事务连接共享
– 连接状态需要重置
– 部分设置无法动态修改
5. 兼容性限制
– 不同连接池实现差异
– 版本兼容性问题
– 驱动兼容性问题
5.3 连接池检查清单
以下是MySQL连接池的检查清单：
1. 设计阶段检查
[ ] 是否评估了连接需求
[ ] 是否选择了合适的连接池
[ ] 是否设置了合理的参数
[ ] 是否规划了监控方案
2. 开发阶段检查
[ ] 是否正确使用连接池
[ ] 是否处理了连接异常
[ ] 是否实现了连接回收
[ ] 是否测试了性能
3. 运维阶段检查
[ ] 是否监控了连接使用
[ ] 是否设置了告警
[ ] 是否定期清理连接
[ ] 是否优化了配置
4. 安全阶段检查
[ ] 是否使用了专用用户
[ ] 是否限制了连接数
[ ] 是否监控了异常连接
[ ] 是否定期审计权限
风哥提示：连接池是提升数据库访问性能的重要组件，合理配置连接池可以显著提高应用性能。建议根据实际业务需求设置连接池大小，minimumIdle设置为10-20，maximumPoolSize根据公式计算。生产环境建议开启连接泄漏检测和健康检查。监控连接使用率，当使用率超过80%时需要考虑扩容。对于生产环境的连接池变更，务必在测试环境验证后再执行。更多视频教程请访问www.fgedu.net.cn
注意：本文档内容基于MySQL 8.4官方文档编写，适合DBA人员在学习和测试中使用。在生产环境中应用时，请务必进行充分的测试和验证。连接池配置需要根据实际业务需求和硬件资源进行调整。文档中的命令和配置可能因MySQL版本和连接池实现不同而有所差异，请根据实际情况进行调整。
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
