MySQL连接数监控与管理：常用查询语句汇总

引言

在MySQL数据库运维中，连接数监控是保障系统稳定运行的重要环节。连接数过多可能导致数据库响应变慢，甚至拒绝服务；连接数配置过小又可能限制业务并发能力。

本文将汇总MySQL连接数相关的常用查询语句，帮助你全面掌握数据库连接状态。

1. 连接数核心指标

2. 基础查询

2.1 查看当前总连接数

-- 查看当前建立的连接总数SHOWSTATUSLIKE'Threads_connected';

输出示例：

+-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 128 | +-------------------+-------+ 1 row in set (0.00 sec)

2.2 查看最大连接数配置

-- 显示服务器允许的最大并发连接数SHOWVARIABLESLIKE'max_connections';

输出示例：

+-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 500 | +-----------------+-------+ 1 row in set (0.00 sec)

2.3 查看详细连接信息

-- 显示所有连接的详细信息SHOWPROCESSLIST;-- 或显示完整信息（不截断）SHOWFULLPROCESSLIST;

输出示例：

+-------+------+---------------------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+------+---------------------+------+---------+------+----------+------------------+ | 1 | root | localhost | test | Query | 0 | starting | SHOW PROCESSLIST | | 2 | app1 | 192.168.1.100:54321 | db1 | Sleep | 120 | | NULL | | 3 | app2 | 192.168.1.101:54322 | db2 | Execute | 5 | updating | UPDATE t SET ... | +-------+------+---------------------+------+---------+------+----------+------------------+

3. 统计分析查询

3.1 按用户分组统计连接数

-- 查看每个用户的连接数SELECTuser,COUNT(*)ASconnections,GROUP_CONCAT(host)AShostsFROMinformation_schema.processlistGROUPBYuserORDERBYconnectionsDESC;

输出示例：

+-------+-------------+----------------------------------+ | user | connections | hosts | +-------+-------------+----------------------------------+ | app | 85 | 192.168.1.10:1234,192.168.1.11 | | root | 25 | localhost,127.0.0.1 | | system| 15 | localhost | +-------+-------------+----------------------------------+

3.2 按来源IP统计连接数

-- 查看每个IP的连接数SELECTSUBSTRING_INDEX(host,':',1)ASip,COUNT(*)ASconnectionsFROMinformation_schema.processlistGROUPBYipORDERBYconnectionsDESC;

3.3 按命令类型统计

-- 查看不同状态的连接数SELECTcommandAS命令类型,COUNT(*)AS连接数,ROUND(COUNT(*)*100.0/SUM(COUNT(*))OVER(),2)AS占比FROMinformation_schema.processlistGROUPBYcommandORDERBY连接数DESC;

输出示例：

+------------+--------+--------+ | 命令类型 | 连接数 | 占比 | +------------+--------+--------+ | Sleep | 85 | 66.41 | | Query | 25 | 19.53 | | Execute | 10 | 7.81 | | Binlog Dump| 5 | 3.91 | | Connect | 3 | 2.34 | +------------+--------+--------+

3.4 查看空闲连接数

-- 查看Sleep状态的连接数SELECTCOUNT(*)ASidle_connections,ROUND(AVG(time))ASavg_idle_secondsFROMinformation_schema.processlistWHEREcommand='Sleep';-- 查看长时间空闲的连接（超过600秒）SELECTid,user,host,timeASidle_seconds,dbFROMinformation_schema.processlistWHEREcommand='Sleep'ANDtime>600ORDERBYtimeDESC;

4. 高级监控查询

4.1 连接数使用率计算

-- 方法1：使用performance_schemaSELECT(SELECTVARIABLE_VALUEFROMperformance_schema.global_statusWHEREVARIABLE_NAME='Threads_connected')AScurrent_connections,(SELECTVARIABLE_VALUEFROMperformance_schema.global_variablesWHEREVARIABLE_NAME='max_connections')ASmax_connections,ROUND((SELECTVARIABLE_VALUEFROMperformance_schema.global_statusWHEREVARIABLE_NAME='Threads_connected')/(SELECTVARIABLE_VALUEFROMperformance_schema.global_variablesWHEREVARIABLE_NAME='max_connections')*100,2)ASconnection_usage_rate;

-- 方法2：使用SHOW STATUS（兼容性更好）SELECTCONCAT(ROUND((SELECTVARIABLE_VALUEFROMinformation_schema.GLOBAL_STATUSWHEREVARIABLE_NAME='THREADS_CONNECTED')/(SELECTVARIABLE_VALUEFROMinformation_schema.GLOBAL_VARIABLESWHEREVARIABLE_NAME='MAX_CONNECTIONS')*100,2),'%')ASconnection_usage_rate;

输出示例：

+---------------------+-----------------+----------------------+ | current_connections | max_connections | connection_usage_rate| +---------------------+-----------------+----------------------+ | 128 | 500 | 25.60 | +---------------------+-----------------+----------------------+

4.2 历史最大连接数

-- 查看历史最大连接数SHOWSTATUSLIKE'Max_used_connections';-- 查看达到最大连接数的时间SHOWSTATUSLIKE'Max_used_connections_time';

输出示例：

+------------------------------+---------------------+ | Variable_name | Value | +------------------------------+---------------------+ | Max_used_connections | 356 | | Max_used_connections_time | 2024-01-15 14:30:25 | +------------------------------+---------------------+

4.3 连接趋势分析

-- 查看连接数变化趋势（需要启用performance_schema）SELECTDATE_FORMAT(event_time,'%Y-%m-%d %H:%i')AStime_minute,COUNT(DISTINCTprocesslist_id)ASconnection_countFROMperformance_schema.events_statements_historyWHEREevent_time>DATE_SUB(NOW(),INTERVAL1HOUR)GROUPBYtime_minuteORDERBYtime_minute;

5. 一键诊断脚本

5.1 连接状况完整诊断

-- MySQL连接状况完整诊断脚本SELECT'=== 连接数配置 ==='ASsection;SHOWVARIABLESLIKE'max_connections';SHOWVARIABLESLIKE'wait_timeout';SHOWVARIABLESLIKE'interactive_timeout';SELECT'=== 当前连接状态 ==='ASsection;SELECT(SELECTCOUNT(*)FROMinformation_schema.processlist)AStotal_connections,(SELECTCOUNT(*)FROMinformation_schema.processlistWHEREcommand='Sleep')ASidle_connections,(SELECTCOUNT(*)FROMinformation_schema.processlistWHEREcommand!='Sleep')ASactive_connections;SELECT'=== 连接使用率 ==='ASsection;SELECTCONCAT(ROUND(threads_connected/max_connections*100,2),'%')ASusage_rate,threads_connected,max_connections,max_used_connectionsFROM(SELECT(SELECTVARIABLE_VALUEFROMinformation_schema.GLOBAL_STATUSWHEREVARIABLE_NAME='THREADS_CONNECTED')ASthreads_connected,(SELECTVARIABLE_VALUEFROMinformation_schema.GLOBAL_VARIABLESWHEREVARIABLE_NAME='MAX_CONNECTIONS')ASmax_connections,(SELECTVARIABLE_VALUEFROMinformation_schema.GLOBAL_STATUSWHEREVARIABLE_NAME='MAX_USED_CONNECTIONS')ASmax_used_connections)ASt;SELECT'=== 按用户分布 ==='ASsection;SELECTuser,COUNT(*)asconnections,GROUP_CONCAT(DISTINCTSUBSTRING_INDEX(host,':',1))asclient_ipsFROMinformation_schema.processlistGROUPBYuserORDERBYconnectionsDESC;SELECT'=== 按状态分布 ==='ASsection;SELECTcommand,COUNT(*)ascount,ROUND(COUNT(*)*100.0/SUM(COUNT(*))OVER(),2)aspercentageFROMinformation_schema.processlistGROUPBYcommandORDERBYcountDESC;SELECT'=== 长时间空闲连接 (>300秒) ==='ASsection;SELECTid,user,host,time,db,commandFROMinformation_schema.processlistWHEREcommand='Sleep'ANDtime>300ORDERBYtimeDESC;SELECT'=== 正在执行的查询 ==='ASsection;SELECTid,user,host,time,state,LEFT(info,100)asshort_infoFROMinformation_schema.processlistWHEREcommand='Query'ANDinfoNOTLIKE'%processlist%'ORDERBYtimeDESC;

5.2 实时监控脚本

-- 实时刷新连接状态（每隔2秒执行）SELECTNOW()AScheck_time,COUNT(*)AStotal_conn,SUM(CASEWHENcommand='Sleep'THEN1ELSE0END)ASidle,SUM(CASEWHENcommand='Query'THEN1ELSE0END)ASquerying,SUM(CASEWHENcommand='Locked'THEN1ELSE0END)ASlockedFROMinformation_schema.processlist;

6. 连接管理命令

6.1 终止异常连接

-- 查看需要终止的连接SELECTid,user,host,time,command,state,infoFROMinformation_schema.processlistWHEREtime>1000ANDcommand!='Sleep';-- 终止指定连接KILL1234;-- 1234为连接ID-- 批量终止空闲连接（谨慎使用）SELECTCONCAT('KILL ',id,';')ASkill_commandFROMinformation_schema.processlistWHEREcommand='Sleep'ANDtime>3600;-- 空闲超过1小时

6.2 调整连接数配置

-- 动态调整最大连接数（临时生效，重启后失效）SETGLOBALmax_connections=1000;-- 永久修改需要编辑配置文件-- [mysqld]-- max_connections = 1000-- 调整超时时间（减少空闲连接）SETGLOBALwait_timeout=600;-- 10分钟SETGLOBALinteractive_timeout=600;

7. 监控告警阈值建议

8. 常见问题排查

8.1 连接数暴涨排查

-- 1. 查看哪个用户在大量创建连接SELECTuser,host,COUNT(*)FROMinformation_schema.processlistGROUPBYuser,hostORDERBYCOUNT(*)DESC;-- 2. 查看应用连接池配置是否合理-- 检查应用程序的连接池最大连接数设置-- 3. 检查是否有连接泄漏SELECTuser,COUNT(*)asconnection_count,MAX(time)asmax_idle_timeFROMinformation_schema.processlistGROUPBYuserHAVINGMAX(time)>3600;

8.2 达到最大连接数无法连接

-- 如果已经无法连接，使用预留连接mysql-u root-p--max_connections=1-- 或修改配置文件后重启-- 在my.cnf中添加：-- max_connections = 1000-- 然后重启MySQL服务

总结

核心监控指标：

• ✅ 当前连接数
• ✅ 连接使用率
• ✅ 空闲连接占比
• ✅ 各用户连接分布

记住：预防胜于治疗，定期监控连接状态，及时调整配置，避免连接数成为系统瓶颈！