MySQL 连接池打满排查思路与详细解决方案

MySQL 连接池打满是生产环境中常见的严重问题。本文从原理到实践，详细讲解连接池打满的原因、排查方法和解决方案。

MySQL 连接机制

连接架构

应用程序
    │
    │  1. 建立 TCP 连接
    │  2. 发送认证信息
    │  3. 验证通过后创建"连接"
    │
    ▼
MySQL Server
    │
    ├── Connection Manager (连接管理器)
    │   └── 负责接收连接、认证、分配线程
    │
    ├── Thread Cache (线程缓存)
    │   └── 缓存已用完的线程，避免重复创建销毁
    │
    └── Connection Thread (连接线程)
        └── 每个连接对应一个线程

max_connections 参数

sql复制
-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';
-- 默认值：151（MySQL 5.7）
-- 推荐值：500-2000（根据内存和业务调整）

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
-- 查看历史峰值
SHOW STATUS LIKE 'Max_used_connections';
-- 查看缓存线程数
SHOW STATUS LIKE 'Threads_cached';

wait_timeout 和 interactive_timeout

sql复制
-- 非交互式连接超时（应用程序默认用这个）
SHOW VARIABLES LIKE 'wait_timeout';  -- 默认 8 小时

-- 交互式连接超时（mysql 命令行）
SHOW VARIABLES LIKE 'interactive_timeout';  -- 默认 8 小时

-- 建议值：
-- 生产环境：30-300 秒（根据业务特性调整）
SET GLOBAL wait_timeout = 300;
SET GLOBAL interactive_timeout = 300;

连接池原理

连接池工作流程

┌─────────────────────────────────────────────────────────┐
│                    连接池                               │
│  ┌─────────────────────────────────────────────────┐  │
│  │  ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐          │  │
│  │  │ Conn │ │ Conn │ │ Conn │ │ Conn │  ...      │  │
│  │  │  1   │ │  2   │ │  3   │ │  4   │          │  │
│  │  └──────┘ └──────┘ └──────┘ └──────┘          │  │
│  │  minIdle=5    maxPoolSize=20                  │  │
│  └─────────────────────────────────────────────────┘  │
│                                                         │
│  1. 请求到来，从池中获取连接                            │
│  2. 连接空闲时间 > maxIdleTime → 关闭回收              │
│  3. 连接数 < minIdle → 后台创建新连接                  │
│  4. 连接数达到 maxPoolSize → 等待或拒绝               │
└─────────────────────────────────────────────────────────┘

常见连接池配置

yaml复制
# HikariCP（Java）
hikari:
  maximum-pool-size: 20        # 最大连接数
  minimum-idle: 5              # 最小空闲连接
  connection-timeout: 30000    # 获取连接超时(ms)
  idle-timeout: 600000         # 空闲超时(ms)
  max-lifetime: 1800000        # 连接最大生命周期(ms)
  leak-detection-threshold: 60000  # 泄漏检测阈值(ms)

# Druid（Java）
druid:
  maxActive: 20               # 最大连接数
  minIdle: 5                  # 最小空闲连接
  maxWait: 6000              # 获取连接最大等待时间(ms)
  timeBetweenEvictionRunsMillis: 60000  # 清理线程运行间隔
  minEvictableIdleTimeMillis: 300000    # 最小空闲时间

# Node.js mysql2
mysql2:
  connectionLimit: 20         # 最大连接数
  waitForConnections: true    # 连接用完是否等待
  queueLimit: 0               # 等待队列长度(0=无限制)
  connectTimeout: 10000        # 连接超时(ms)

连接池打满的原因

连接池打满 = 连接需求 > 连接释放
           = 获取连接速度 > 释放连接速度

常见原因：
1. 连接泄漏（Connection Leak）
2. 慢查询占用连接
3. 事务未提交/未回滚
4. 短连接频繁创建销毁
5. maxPoolSize 设置过小
6. 高并发峰值超出预期

排查方法

1. 查看当前连接状态

sql复制
-- 查看所有连接
SHOW FULL PROCESSLIST;
-- 或
SELECT * FROM information_schema.PROCESSLIST 
ORDER BY TIME DESC;

-- 查看各状态连接数
SELECT 
    USER,
    COUNT(*) as count,
    STATE
FROM information_schema.PROCESSLIST
GROUP BY USER, STATE;

-- 状态说明：
-- Sleep: 空闲等待
-- Query: 执行查询
-- Locked: 表锁等待
-- Sending data: 发送数据
-- Analyzing: 分析查询
-- Sorting result: 排序
-- Closing tables: 关闭表

2. 统计连接数分布

sql复制
-- 按用户统计连接数
SELECT 
    USER,
    COUNT(*) as connection_count,
    HOST
FROM information_schema.PROCESSLIST
GROUP BY USER, HOST
ORDER BY COUNT(*) DESC;

-- 按数据库统计
SELECT 
    DB,
    COUNT(*) as connection_count
FROM information_schema.PROCESSLIST
WHERE DB IS NOT NULL
GROUP BY DB
ORDER BY COUNT(*) DESC;

-- 按命令类型统计
SELECT 
    COMMAND,
    COUNT(*) as count
FROM information_schema.PROCESSLIST
GROUP BY COMMAND;

3. 找出慢查询占用的连接

sql复制
-- 查看长时间运行的查询
SELECT 
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    LEFT(INFO, 100) as query
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
  AND TIME > 10  -- 运行超过 10 秒
ORDER BY TIME DESC;

-- 查看有哪些锁等待
SELECT 
    r.trx_id,
    r.trx_mysql_thread_id,
    r.trx_state,
    r.trx_started,
    r.trx_rows_locked,
    r.trx_query,
    l.lock_id,
    l.lock_mode
FROM information_schema.INNODB_TRX r
JOIN information_schema.INNODB_LOCKS l ON r.trx_id = l.lock_trx_id
WHERE r.trx_state = 'LOCK WAIT';

4. 监控连接创建释放

sql复制
-- 查看连接相关状态变量
SHOW GLOBAL STATUS LIKE 'Connections';        -- 总连接数
SHOW GLOBAL STATUS LIKE 'Aborted_connects'; -- 失败连接数
SHOW GLOBAL STATUS LIKE 'Aborted_clients';  -- 客户端异常断开

-- 查看是否接近上限
SHOW STATUS LIKE 'Max_used_connections';    -- 历史峰值
SHOW VARIABLES LIKE 'max_connections';       -- 最大值

-- 计算连接使用率
-- 使用率 = Max_used_connections / max_connections * 100%
-- 超过 80% 就要警惕

5. 开启慢查询日志

sql复制
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 查看最近 5 条慢查询
SHOW FULL PROCESSLIST 
WHERE Command != 'Sleep' 
  AND Time > 1 
LIMIT 5;

连接泄漏（Connection Leak）

什么是连接泄漏

go复制
// 连接泄漏：获取连接后没有释放
// 每次请求都泄漏一个连接，直到连接池耗尽

// Java 示例（伪代码）
func getUserById(id int) {
    conn := dbPool.GetConnection()  // 获取连接
    defer conn.Close()               // 如果忘记写这行，就泄漏了
    
    result := conn.Query("SELECT * FROM users WHERE id = ?", id)
    return result
}

// 正确写法
func getUserById(id int) {
    conn := dbPool.GetConnection()
    defer conn.Close()  // 无论成功失败都会释放
    
    result := conn.Query("SELECT * FROM users WHERE id = ?", id)
    return result
}

Python 连接泄漏示例

python复制
# ❌ 错误写法：异常时连接未释放
def get_user(user_id):
    conn = db_pool.get_connection()
    try:
        result = conn.execute("SELECT * FROM users WHERE id = ?", user_id)
        return result
    except Exception as e:
        return None
    # 如果异常，conn 永远不会被释放！

# ✅ 正确写法：使用 context manager
def get_user(user_id):
    with db_pool.connection() as conn:  # 自动释放
        try:
            result = conn.execute("SELECT * FROM users WHERE id = ?", user_id)
            return result
        except Exception as e:
            return None

Java 连接泄漏排查

java复制
// HikariCP 连接泄漏检测
// 配置 leak-detection-threshold
// 当连接借用时间超过阈值，打印警告日志

HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setLeakDetectionThreshold(60000);  // 60 秒

// 警告日志示例：
// Connection leak detection triggered for connection #1 on thread "http-nio-8080-exec-1"
// , stack trace follows
//    at com.mypackage.UserService.getUserById(UserService.java:45)
    
// 排查步骤：
// 1. 找到泄漏的代码位置
// 2. 检查是否有 try-catch 导致 close() 未执行
// 3. 检查是否有 return 语句在 close() 之前

检测连接泄漏的代码审查清单

go复制
// 检查清单：
// 1. defer conn.Close() 是否在每个函数中都写了？
// 2. 是否有早期 return 导致 defer 未执行？
// 3. 错误处理路径是否也释放了连接？

// ❌ 问题代码
func getUser(id int) (*User, error) {
    conn := db.Get()
    
    if conn.Ping() != nil {
        return nil, errors.New("ping failed")
        // conn 未释放！defer 在 return 之后才执行
    }
    
    user := &User{}
    conn.QueryRow("SELECT * FROM users WHERE id = ?", id, user)
    
    conn.Close()  // 太晚，可能前面就泄漏了
    return user, nil
}

// ✅ 正确代码
func getUser(id int) (*User, error) {
    conn := db.Get()
    defer conn.Close()  // defer 会保证释放
    
    if conn.Ping() != nil {
        return nil, errors.New("ping failed")
        // defer 会执行
    }
    
    user := &User{}
    err := conn.QueryRow("SELECT * FROM users WHERE id = ?", id, user)
    if err != nil {
        return nil, err
        // defer 会执行
    }
    
    return user, nil
    // defer 执行
}

事务未提交/未回滚

常见问题

go复制
// 问题：忘记提交或回滚
func transferMoney(from, to, amount int) error {
    conn := db.Get()
    defer conn.Close()
    
    conn.Begin()  // 开始事务
    
    conn.Execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, from)
    conn.Execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, to)
    
    // ❌ 忘记 conn.Commit()
    // 如果函数异常退出，会自动回滚（defer）
    // 但正常执行时会一直持有锁
    
    return nil
}

Go 正确事务写法

go复制
// ✅ 推荐写法：使用 Tx 上下文
func transferMoney(from, to, amount int) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer func() {
        if err != nil {
            tx.Rollback()
        } else {
            tx.Commit()
        }
    }()
    
    _, err = tx.Exec("UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, from)
    if err != nil {
        return err  // defer 会回滚
    }
    
    _, err = tx.Exec("UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, to)
    if err != nil {
        return err  // defer 会回滚
    }
    
    return nil  // defer 会提交
}

// ✅ 更简洁：使用 commit callback
func transferMoney(from, to, amount int) error {
    return db.Transaction(func(tx *sql.Tx) error {
        _, err := tx.Exec("UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, from)
        if err != nil {
            return err
        }
        
        _, err = tx.Exec("UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, to)
        return err
    })
}

解决方案

1. 紧急处理

bash复制
# 1. 找出长时间运行的查询并 kill
mysql> SHOW FULL PROCESSLIST;
mysql> KILL <thread_id>;  -- 杀死占用连接的线程

# 2. 如果是应用层连接泄漏，重启应用
systemctl restart your-app

# 3. 临时增加 max_connections（重启后恢复）
mysql> SET GLOBAL max_connections = 500;

2. 优化慢查询

sql复制
-- 找出最慢的查询
SELECT 
    COUNT(*) as executions,
    SUM(duration) as total_duration,
    AVG(duration) as avg_duration,
    LEFT(digest_text, 100) as query
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text != ''
GROUP BY digest_text
ORDER BY SUM(duration) DESC
LIMIT 10;

3. 合理设置连接池参数

yaml复制
# 推荐配置（根据服务器配置调整）
hikari:
  maximum-pool-size: 20        # CPU 核心数 * 2
  minimum-idle: 5             # 正常负载下保持的空闲连接
  connection-timeout: 10000   # 10 秒足够
  idle-timeout: 300000        # 5 分钟
  max-lifetime: 1800000       # 30 分钟
  
# 估算公式：
# maxPoolSize = (核心数 * 2) + 磁盘数
# 例如：4 核 + 1 块磁盘 = 9，建议设为 10-20

4. 监控告警

go复制
// 应用层监控
metrics := prometheus.NewGaugeVec(prometheus.GaugeOpts{
    Name: "db_pool_connections_active",
    Help: "Active connections in pool",
}, []string{"pool_name"})

metrics := prometheus.NewGaugeVec(prometheus.GaugeOpts{
    Name: "db_pool_connections_waiting",
    Help: "Connections waiting to acquire",
}, []string{"pool_name"})

// 告警规则（Prometheus）
# 连接使用率 > 80% 持续 5 分钟
- alert: DatabaseConnectionPoolHighUsage
  expr: db_pool_connections_active / db_pool_connections_max > 0.8
  for: 5m
  labels:
    severity: warning
  annotations:
    summary: "数据库连接池使用率过高"

# 连接等待数量 > 10
- alert: DatabaseConnectionPoolWaiters
  expr: db_pool_connections_waiting > 10
  for: 1m
  labels:
    severity: critical

5. 连接池打满时的熔断策略

go复制
// 连接池满时的降级处理
func GetUserWithFallback(id int) (*User, error) {
    user, err := getUserFromDB(id)
    if err != nil {
        // 连接池可能满了，降级到缓存
        return getUserFromCache(id)
    }
    return user, nil
}

// 或者使用 circuit breaker
breaker := gobreaker.NewCircuitBreaker(circuitbreaker.Config{
    Name:        "db-pool",
    MaxRequests: 3,           // 熔断打开后，3 个请求后尝试半开
    Interval:    10 * time.Second,
    Timeout:     30 * time.Second,
})

result, err := breaker.Execute(func() (interface{}, error) {
    return getUserFromDB(id)
})

6. 长连接 vs 短连接

go复制
// 短连接：每次请求都创建新连接（性能差）
func handler(w http.ResponseWriter, r *http.Request) {
    conn, _ := sql.Open("mysql", "user:pass@tcp(host)/db")
    // 每次请求都创建 TCP 连接
    conn.Query("SELECT ...")
    conn.Close()  // 关闭连接
}

// 长连接：复用连接池中的连接（推荐）
var db *sql.DB

func init() {
    db, _ = sql.Open("mysql", "user:pass@tcp(host)/db?parseTime=true")
    db.SetMaxOpenConns(20)
    db.SetMaxIdleConns(10)
    db.SetConnMaxLifetime(time.Hour)
}

func handler(w http.ResponseWriter, r *http.Request) {
    // 复用 db 管理的连接池
    db.Query("SELECT ...")
}

预防措施

1. 连接治理规范

markdown复制
## 连接使用规范

1. **获取连接后必须释放**
   - defer conn.Close()
   
2. **禁止在连接上执行耗时操作**
   - 禁止 SELECT * 查大表
   - 禁止无 LIMIT 的查询
   
3. **禁止长事务**
   - 事务内禁止网络 IO
   - 超时自动回滚
   
4. **禁止自动提交关闭**
   - 保持 autocommit=1（除非需要事务）
   
5. **禁止连接池过大**
   - maxPoolSize ≤ CPU 核心数 * 2

2. SQL 审核

sql复制
-- 上线前检查：
-- 1. 所有查询必须有 LIMIT
SELECT * FROM orders WHERE user_id = ? LIMIT 1000;

-- 2. 禁止全表更新/删除
-- ❌ UPDATE orders SET status = 1;
-- ✅ UPDATE orders SET status = 1 WHERE created_at > '2024-01-01' LIMIT 10000;

-- 3. 检查执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = ?;

3. 定期巡检

sql复制
-- 每日巡检
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME IN (
    'Threads_connected',
    'Max_used_connections',
    'Aborted_connects',
    'Slow_queries',
    'Innodb_row_lock_waits'
);

常见面试问题

Q1: max_connections 和连接池大小的关系？

sql复制
-- MySQL max_connections: MySQL 服务端允许的最大连接数
-- 应用连接池 maxPoolSize: 应用端持有的最大连接数

-- 建议：
-- MySQL max_connections > 应用连接池 maxPoolSize * 应用实例数 * 1.5
-- 例如：3 个应用实例，每个 20 连接，max_connections 应该 > 90

Q2: 连接池大小如何计算？

markdown复制
# 经验公式
maxPoolSize = (CPU 核心数 * 2) + 磁盘数

# 考虑因素：
1. CPU 密集型任务：CPU 核心数 + 少量连接
2. IO 密集型任务：可以更多连接（等待 IO 时不占 CPU）
3. 混合型：取中间值

# 实际调整：
1. 基准测试找最优值
2. 监控 Active 和 Waiting 比例
3. Waiting 高 → 增加连接
4. Active 高但 Waiting 低 → 连接够用

Q3: 如何处理连接 TIME_WAIT？

bash复制
# Linux 层面优化
# /etc/sysctl.conf
net.ipv4.tcp_tw_reuse = 1           # 允许重用 TIME_WAIT 连接
net.ipv4.tcp_fin_timeout = 30       # 减少 FIN_WAIT_2 超时

# MySQL 层面优化
# 减少 wait_timeout，让空闲连接更快释放
SET GLOBAL wait_timeout = 300;      # 5 分钟

# 应用层面
# 使用连接池复用连接，避免频繁创建销毁

Q4: 连接池和线程池的区别？

markdown复制
# 连接池：管理数据库连接
# - 获取连接有开销（TCP 连接、认证）
# - 复用连接减少开销
# - 连接是有状态的（事务、临时表等）

# 线程池：管理工作线程
# - 线程创建有开销
# - 复用线程减少开销
# - 线程通常无状态

# 配合使用：
# 请求 → 线程池取线程 → 从连接池取连接 → 执行 → 归还

总结

预防胜于治疗：做好监控、规范开发、合理配置，比出问题后再救火重要得多。