如何发现连接数过多的 IP
注意要使用高权限的管理员账号，否则统计会不全。
总连接数：
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连接某个库的连接数过多？
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
示例
mysql root@127.0.0.1:test> select count(*) from information_schema.processlist;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set
Time: 0.031s
mysql root@127.0.0.1:test> SELECT * FROM (
-> select substring_index(host,':',1) as ip , count(*) as connection_co
-> unt from information_schema.processlist group by ip
-> ) t order by t.connection_count desc
+-----------+------------------+
| ip | connection_count |
+-----------+------------------+
| localhost | 1 |
+-----------+------------------+
1 row in set
Time: 0.015s
mysql root@127.0.0.1:test> 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 |
+-----------+------+------------------+
| localhost | test | 1 |
+-----------+------+------------------+
1 row in set
Time: 0.009s
应用层如何限制
1、使用连接池
2、连接池最大连接数不宜过多。连接数并不是越多越好，可参考这篇讨论 数据库链接池终于搞对了，这次直接从100ms优化到3ms！ 。