MySQL 查看客户端连接信息

查看客户端连接详细信息

select * from information_schema.processlist \G

结果示例：

ID      | 1
USER    | root
HOST    | 127.0.0.1:60626
DB      | <null>
COMMAND | Query
TIME    | 0
STATE   | executing
INFO    | select * from information_schema.processlist

查看当前连接数量

select count(*) from information_schema.processlist

查看ip来源和对应的连接数量

SELECT * FROM (
    select 
        substring_index(host,':',1) as ip , 
        count(*) as connection_count 
    from information_schema.processlist 
    group by ip
) t order by t.connection_count desc

注意针对连接数进行了从大到小排序。

结果示例：

+------------+------------------+
| ip         | connection_count |
+------------+------------------+
| 127.0.0.1  | 1                |
+------------+------------------+

查看各个 database 下的ip来源和对应的连接数量

SELECT * FROM (
    select 
        substring_index(host,':',1) as ip, 
        db, 
        count(*) as connection_count 
    from information_schema.processlist 
    group by ip, db
) t order by t.connection_count desc

注意针对连接数进行了从大到小排序。

结果示例:

+------------+--------+------------------+
| ip         | db     | connection_count |
+------------+--------+------------------+
| 127.0.0.1  | <null> | 1                |
+------------+--------+------------------+

治理连接数 - 应用层限制

1、使用连接池
2、连接池最大连接数不宜过多。连接数并不是越多越好，可参考这篇讨论 数据库链接池终于搞对了，这次直接从100ms优化到3ms！ 。