当请求很多时，MySQL连接时快时慢的问题解决

ftsr

问题的出现是这样的，我的一个web应用中有一个列表页面，遇到了n+1问题，就是打开这个页面，列表中有n条信息，就会产生n+1条sql。打开这个页面很慢，需要1分钟多。然而，这个问题只在测试环境出现，生产环境和本地开发环境均为出现此问题。

经过跟踪和分析，发现问题出在获取连接的时候，由于没有使用连接池，这n+1条sql，经常就会遇到获取连接慢的。如下为日志：

2009-11-23 13:41:20,679 INFO [Test] - -----------config------------
2009-11-23 13:41:20,695 INFO [Test] - url: jdbc:mysql://88.88.88.88:3306/xxxdatabase?useUnicode=true&characterEncoding=UTF-8
2009-11-23 13:41:20,695 INFO [Test] - BEGIN: select * from xxxtable where id = 1
2009-11-23 13:41:21,476 INFO [Test] - END: 765|16
2009-11-23 13:41:21,476 INFO [Test] - BEGIN: select * from xxxtable where id = 2
2009-11-23 13:41:21,523 INFO [Test] - END: 47|0
2009-11-23 13:41:21,523 INFO [Test] - BEGIN: select * from xxxtable where id = 3
2009-11-23 13:41:21,585 INFO [Test] - END: 62|0
2009-11-23 13:41:21,585 INFO [Test] - BEGIN: select * from xxxtable where id = 4
2009-11-23 13:41:21,617 INFO [Test] - END: 32|0
2009-11-23 13:41:21,617 INFO [Test] - BEGIN: select * from xxxtable where id = 5
2009-11-23 13:41:21,663 INFO [Test] - END: 31|15
2009-11-23 13:41:21,663 INFO [Test] - BEGIN: select * from xxxtable where id = 6
2009-11-23 13:41:21,679 INFO [Test] - END: 16|0
2009-11-23 13:41:21,679 INFO [Test] - BEGIN: select * from xxxtable where id = 7
2009-11-23 13:41:21,710 INFO [Test] - END: 16|15
2009-11-23 13:41:21,710 INFO [Test] - BEGIN: select * from xxxtable where id = 8
2009-11-23 13:41:21,742 INFO [Test] - END: 32|0
2009-11-23 13:41:21,742 INFO [Test] - BEGIN: select * from xxxtable where id = 9
2009-11-23 13:41:21,757 INFO [Test] - END: 15|0
2009-11-23 13:41:21,757 INFO [Test] - BEGIN: select * from xxxtable where id = 10
2009-11-23 13:41:21,773 INFO [Test] - END: 16|0
2009-11-23 13:41:21,773 INFO [Test] - BEGIN: select * from xxxtable where id = 11
2009-11-23 13:41:21,788 INFO [Test] - END: 15|0
2009-11-23 13:41:21,788 INFO [Test] - BEGIN: select * from xxxtable where id = 12
2009-11-23 13:41:26,819 INFO [Test] - END: 5031|0
2009-11-23 13:41:26,819 INFO [Test] - BEGIN: select * from xxxtable where id = 13
2009-11-23 13:41:26,850 INFO [Test] - END: 31|0
2009-11-23 13:41:26,850 INFO [Test] - BEGIN: select * from xxxtable where id = 14
2009-11-23 13:41:26,850 INFO [Test] - END: 0|0
2009-11-23 13:41:26,850 INFO [Test] - BEGIN: select * from xxxtable where id = 15
2009-11-23 13:41:26,960 INFO [Test] - END: 110|0
2009-11-23 13:41:26,960 INFO [Test] - BEGIN: select * from xxxtable where id = 16
2009-11-23 13:41:26,991 INFO [Test] - END: 15|16
2009-11-23 13:41:26,991 INFO [Test] - BEGIN: select * from xxxtable where id = 17
2009-11-23 13:41:27,022 INFO [Test] - END: 16|15
2009-11-23 13:41:27,022 INFO [Test] - BEGIN: select * from xxxtable where id = 18
2009-11-23 13:41:27,038 INFO [Test] - END: 0|16
2009-11-23 13:41:27,038 INFO [Test] - BEGIN: select * from xxxtable where id = 19
2009-11-23 13:41:32,069 INFO [Test] - END: 5015|0
2009-11-23 13:41:32,069 INFO [Test] - BEGIN: select * from xxxtable where id = 20
2009-11-23 13:41:32,100 INFO [Test] - END: 0|31
2009-11-23 13:41:32,100 INFO [Test] - BEGIN: select * from xxxtable where id = 21
2009-11-23 13:41:32,100 INFO [Test] - END: 0|0
2009-11-23 13:41:32,100 INFO [Test] - BEGIN: select * from xxxtable where id = 22
2009-11-23 13:41:32,116 INFO [Test] - END: 16|0
2009-11-23 13:41:32,116 INFO [Test] - BEGIN: select * from xxxtable where id = 23
2009-11-23 13:41:32,131 INFO [Test] - END: 15|0
2009-11-23 13:41:32,131 INFO [Test] - BEGIN: select * from xxxtable where id = 24
2009-11-23 13:41:32,147 INFO [Test] - END: 16|0
2009-11-23 13:41:32,147 INFO [Test] - BEGIN: select * from xxxtable where id = 25
2009-11-23 13:41:32,163 INFO [Test] - END: 16|0

日志说明：BEGIN是要执行的sql，END中第一个数字是获取连接所消耗的时间，第二个数字是执行sql所消耗的时间。

请注意上面标红的sql，和别的不同，它们获取连接的时候特别的慢，平均需要5秒。这就是页面打开慢的原因。

通过百度和google，对此问题进行搜索，但是没有找到相关材料。

尝试一：
在本地的hosts中添加该ip地址的域名解析，用域名访问该数据库。
失败。

尝试二：
用show status和show processlist还有top等等查看服务器的运行状态是否存在异常。
未发现异常。

尝试三：
直接用localhost访问。
成功。

准备使用的尝试四：
修改 my.cnf
[mysqld]
skip-name-resolve   #Don’t resolve hostnames
重启。

总结：该问题，困扰了我3天，虽然终于解决了，但是没有找到根本原因。只是消除了症状，还希望明白人指点，谢谢非常。

至于一开始为什么不用localhost而用ip，是因为公司给我的那个用户只有用ip访问的权限。