连接池管理

MySQL 连接池与连接管理完全指南

高效的连接管理对可扩展性至关重要。本指南涵盖连接池架构、配置和性能优化。

1. 连接池架构

为什么需要连接池:

无连接池（反模式）:
新连接 (50-100ms 握手) + 查询(1ms) = ~101ms/次查询

有连接池（最佳实践）:
复用连接 (1ms) + 查询 (1ms) + 归还(0.1ms) = ~2ms/次查询

性能提升: 约 50 倍
连接数减少: 5000 → 50 个TCP连接

连接生命周期：

1. 池初始化
   └─ 预创建 N 个连接（min_connections）

2. 使用中
   └─ 应用查询运行，保持连接活跃

3. 空闲
   └─ 查询完成后归还池中
   └─ 等待下次请求（空闲超时通常5-30分钟）

4. 驱逐
   └─ 空闲时间 > 超时：关闭连接
   └─ 达到最大连接数：对新请求排队

1.1 关键配置参数

min_connections（核心池大小）：
├─ 默认：5-10
├─ 启动时预创建
└─ 经验法则 = 应用线程数 / 2
   示例：100个应用线程 → 50个min_connections

max_connections（最大池大小）：
├─ 默认：20-50
├─ 同时连接的硬限制
└─ 计算：线程数 × 每线程最大并发查询数
   示例：100个应用线程 → 200个max_connections

connection_timeout（连接超时）：
├─ 等待可用连接的时间（毫秒）
├─ 默认：30000（30秒）
└─ 超过则抛出 ConnectionTimeoutException

idle_timeout（空闲超时）：
├─ 空闲连接驱逐时间（分钟）
├─ 默认：10分钟
└─ 推荐：15分钟

max_lifetime（连接最大年龄）：
├─ 最大连接存活时间（分钟）
├─ 防止陈旧连接
└─ 推荐：30分钟

推荐配置（100个并发应用服务，1个MySQL实例）：

min_connections: 10
max_connections: 100
connection_timeout: 30000
idle_timeout: 15（分钟）
max_lifetime: 30（分钟）

HikariCP 配置（Java）：
dataSource.setMinimumIdle(10);
dataSource.setMaximumPoolSize(100);
dataSource.setConnectionTimeout(30000);
dataSource.setIdleTimeout(900000);   // 15分钟
dataSource.setMaxLifetime(1800000);  // 30分钟

ProxySQL 配置（基于代理）：
[mysql_variables]
interfaces="127.0.0.1:6033"
max_connections=2000
default_max_connections=100

[[mysql_servers]]
hostgroup_id=0
hostname="192.168.1.10"
port=3306
max_connections=100

2. 代理连接池

2.1 ProxySQL 与 MyCat 对比

PROXYSQL（大多数场景推荐）

架构：
应用程序 → ProxySQL (端口6033) → MySQL

特性：
✅ 连接池（每线程）
✅ 查询缓存
✅ 读写分离
✅ 分片支持
✅ 连接多路复用
✅ 动态配置（无需重启）
✅ 查询重写规则
✅ 管理界面

示例：
// 应用程序连接到 ProxySQL
mysql -h 127.0.0.1 -P 6033 -u app_user

// ProxySQL 多路复用到后端 MySQL
// 100个应用连接 → 10个MySQL连接

查询路由：
SELECT * FROM users      → 读副本（replica）
INSERT INTO orders ...   → 主库（master）

ProxySQL 配置：

INSERT INTO mysql_users (username, password, active, default_hostgroup)
VALUES ('app_user', 'password_hash', 1, 0);

INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections)
VALUES (0, 'master.example.com', 3306, 100),
       (1, 'replica1.example.com', 3306, 100),
       (1, 'replica2.example.com', 3306, 100);

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup)
VALUES (1, 1, '^SELECT .*FOR UPDATE', 0),  -- 主库
       (2, 1, '^SELECT', 1);               -- 副本

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

MYCAT（适合以分片为主的工作负载）

特性：
✅ Sharding（水平分区）
✅ 连接池
✅ 读写分离
❌ 比 ProxySQL 慢
❌ 配置更复杂
✅ 基于Java（需要JVM）

对比表：

功能              | ProxySQL | MyCat
──────────────────┼──────────┼──────
连接池            | ✅       | ✅
读写分离          | ✅       | ✅
Sharding         | 有限     | ✅✅
性能              | 快       | 中等
复杂度            | 低       | 高

推荐：
├─ < 100GB数据：应用层连接池（HikariCP）
├─ 100GB - 1TB：添加ProxySQL
└─ > 1TB分片：添加MyCat用于分片路由

2.2 连接复用与保活

连接池心跳（防止连接超时）：

HikariCP 配置：
dataSource.setConnectionTestQuery("SELECT 1");
dataSource.setLeakDetectionThreshold(60000);  // 60秒泄漏检测

ProxySQL 心跳配置：
mysql-ping_interval_server_msec=10000  // 每10秒ping一次
mysql-ping_timeout_server_msec=800

连接复用模式：

好的模式：
1. 获取连接
2. 执行 1-N 个查询
3. 归还连接池
4. 从步骤1重复

不好的模式：
// 不要在请求之间保持连接打开
global.dbConn = pool.getConnection();  // 反模式！

好的示例（Java/伪代码）：
conn = pool.getConnection();
try {
  user = conn.query("SELECT * FROM users WHERE id=?", userId);
  orders = conn.query("SELECT * FROM orders WHERE user_id=?", userId);
  return {user, orders};
} finally {
  pool.returnConnection(conn);
}

3. 性能调优与监控

监控连接池健康：

监控指标：
├─ 活跃连接（当前使用中）
├─ 空闲连接（等待使用）
├─ 队列深度（等待连接的请求）
├─ 连接创建速率
└─ 连接等待时间

HikariCP 指标：
hikaricp_active_connections: 当前执行查询数
hikaricp_idle_connections: 可复用数
hikaricp_pending_threads: 等待连接数
hikaricp_connection_timeout_total: 超时次数

告警阈值：
- 活跃连接 > max的80%
- 队列深度 > 10
- 连接创建速率 > 每秒1个（频繁重建）
- 连接等待时间 > 1秒（竞争严重）

ProxySQL 监控：
SHOW STATS_MYSQL_CONNECTION_POOL;

调优步骤：

如果 queue_depth > 0（有连接等待）：
1. 增加 max_connections
   [ProxySQL] max_connections = 2000
   [HikariCP] dataSource.setMaximumPoolSize(200);

2. 减少查询时间
   -- 优化慢查询
   -- 添加缺失索引

3. 增加 min_connections
   -- 保持连接预先打开
   -- 减少连接创建开销

批量操作优化：

不良：每次操作使用一个连接
for batch in batches:
  conn = pool.getConnection();
  INSERT batch;
  pool.returnConnection(conn);

更好：批量多次操作
conn = pool.getConnection();
for batch in batches:
  INSERT batch;
pool.returnConnection(conn);

最佳：预准备语句批量插入
conn = pool.getConnection();
stmt = conn.prepareStatement("INSERT INTO users VALUES (?,?,?)");
for user in users:
  stmt.setString(1, user.name);
  stmt.addBatch();
stmt.executeBatch();
pool.returnConnection(conn);

结果：10K行插入快1000倍