连接数问题

MySQL 连接数问题深度解析：从错误原因到根治方案

“too many connections” 错误是 MySQL 中常见的连接数超限问题，表面原因是当前连接数超过了 max_connections 限制，但本质往往是连接管理不当或应用设计缺陷。本文将从错误原理、诊断方法到优化方案，全面解决连接数过多的问题。

“too many connections” 错误原理

MySQL 对同时建立的连接数有上限控制（由 max_connections 参数定义），当新请求的连接数超过该值时，会拒绝连接并抛出错误：
ERROR 1040 (08004): Too many connections

连接数上限的限制因素

max_connections 并非可以无限调大，受以下因素制约：

内存资源：每个 MySQL 连接会占用一定内存（约 200KB~ 几 MB，取决于查询复杂度），过多连接会耗尽服务器内存。
文件描述符：MySQL 每个连接对应一个文件描述符，操作系统对进程的文件描述符有上限（如 ulimit -n 通常默认 1024）。
性能瓶颈：过多连接会导致 MySQL 线程上下文切换频繁，CPU 负载飙升，反而降低性能。

默认值与有效上限

默认 max_connections 为 151（MySQL 5.7+），最小为 1，理论最大可设为 100000（但受系统限制）。
实际有效上限：通常建议不超过 500~1000（除非服务器内存充足且连接均为短连接）。

连接数过多的常见原因

解决 “too many connections” 的核心是找到连接数激增的根源，而非单纯调大 max_connections。常见原因包括：

应用未正确释放连接

连接泄漏：应用使用连接后未调用 close() 释放（如 Java 未关闭 Connection，Python 未关闭 cursor），导致连接长期处于 Sleep 状态。
连接池配置不当：连接池的 maxPoolSize 设得过大（如超过 max_connections），或未设置 maxIdleTime 回收闲置连接。

慢查询 / 长事务阻塞连接

耗时过长的查询（如未加索引的全表扫描）或未提交的长事务，会导致连接长期处于 Running 或 Locked 状态，无法释放，新连接不断累积。

短连接风暴

应用使用短连接（每次请求创建新连接，用完即关），在高并发场景下（如秒杀），短时间内大量创建连接，超过 max_connections。

恶意连接攻击

攻击者通过脚本频繁创建连接，耗尽连接数（如 DoS 攻击），导致正常请求无法连接。

连接数问题的诊断方法

通过 MySQL 内置命令和工具，可快速定位连接数过多的原因：

查看当前连接状态

-- 查看当前连接数及状态（前100行）
show processlist;

-- 查看所有连接（包括Sleep状态）
show full processlist;

关键字段解析：

Id：连接 ID。
User：连接的用户名。
Host：连接来源的 IP 和端口。
db：当前连接的数据库。
Command：连接状态（Sleep：闲置；Query：执行查询；Locked：等待锁）。
Time：连接持续时间（秒）。

统计连接状态分布

-- 统计不同状态的连接数
select Command, count(*) as count from information_schema.processlist group by Command;

-- 统计不同来源IP的连接数
select Host, count(*) as count from information_schema.processlist group by Host order by count desc;

-- 统计连接持续时间超过60秒的连接
select Id, User, Host, Time, Command from information_schema.processlist where Time > 60;

通过以上命令可快速判断：

是否有大量 Sleep 连接（可能是连接泄漏）。
是否有来源单一 IP 的大量连接（可能是恶意攻击）。
是否有长时间 Running 的连接（可能是慢查询）。

查看连接数配置与上限

-- 查看当前连接数、最大连接数、已使用连接数
show variables like 'max_connections';  -- 最大连接数
show global status like 'Threads_connected';  -- 当前连接数
show global status like 'Threads_running';   -- 正在活跃的连接数（执行查询的）

健康指标：

正常情况下，Threads_connected 应远小于 max_connections（如不超过 70%）。
若 Threads_connected 接近 max_connections，说明连接数已饱和。

解决方案：从临时缓解到根治

1. 临时缓解：增加连接数上限

若需紧急恢复服务，可临时调大 max_connections（重启后失效）：

-- 临时设置最大连接数为1000
set global max_connections = 1000;

注意：

调大前需确认服务器内存充足（按每个连接 1MB 估算，1000 连接约需 1GB 内存）。
若提示 “ERROR 1231 (42000): Variable ‘max_connections’ can’t be set to the value of ‘1000’”，说明受操作系统文件描述符限制，需先调大系统的 open files 限制（如 ulimit -n 65535）。

2. 永久配置：修改配置文件

在 my.cnf（Linux）或 my.ini（Windows）中配置，重启 MySQL 生效：

[mysqld]
max_connections = 1000  # 最大连接数
open_files_limit = 65535  # 文件描述符限制（需大于max_connections）

3. 根治连接数过多的核心优化

（1）优化连接管理，避免连接泄漏

合理配置连接池：
- 连接池的 maxPoolSize 应小于 max_connections（如设为 max_connections * 0.8）。
- 设置 maxIdleTime（如 300 秒），自动回收闲置连接（如 HikariCP 的 idleTimeout）。
检查应用代码：确保所有连接在使用后关闭（如 Java 的 try-with-resources 自动关闭连接）。

（2）减少闲置连接：自动关闭超时连接

通过以下参数设置闲置连接的自动关闭时间：

[mysqld]
wait_timeout = 300  # 非交互式连接（如应用连接）的闲置超时时间（秒）
interactive_timeout = 300  # 交互式连接（如mysql客户端）的闲置超时时间（秒）

建议设为 300 秒（5 分钟），避免闲置连接长期占用资源。

（3）优化慢查询与长事务

定位慢查询：开启慢查询日志，找出耗时超过long_query_time（如 1 秒）的查询，加索引或优化 SQL。

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1  # 超过1秒的查询记录为慢查询

限制事务时长：应用中设置事务超时（如 Spring 的 @Transactional(timeout=30)），避免长事务阻塞连接。

（4）减少短连接开销：使用连接复用

使用长连接：应用与 MySQL 建立长连接（如连接池默认使用长连接），避免频繁创建 / 关闭连接的开销。
启用连接复用机制：如使用 mysqlnd 驱动（PHP）、pymysql 的连接池（Python），或通过 ProxySQL 等中间件实现连接复用。

（5）限制恶意连接：控制单用户 / IP 连接数

通过max_user_connections限制单个用户的最大连接数：

-- 限制用户user1的最大连接数为50
grant all privileges on *.* to 'user1'@'%' with max_user_connections 50;

通过防火墙（如 iptables）限制来源 IP 的连接频率，拦截恶意攻击