对于重复查询,JDBCforMySQL的java性能非常慢
我有一个程序需要用Java编写大量的查询,选择JDBC来操作mysql数据库。我的代码框架如下:
PreparedStatement stmt = conn.preparedStatement(
"SELECT name FROM users WHERE id = ?" )
Iterator<String, Double> it = map.entrySet().iterator();
//map is relativelly large, holding about 100,000 records,
//each need to query mysql, and do some computing
//I record the time consuming of query & computing
while ( it.hasNext() ) {
String id = it.next().getKey();
stmt.setString(1, id); //set the missing param with id
long queryStart = System.currentTimeMillis();
ResultSet rs = st.executeQuery();
long queryEnd = System.currentTimeMillis();
while ( rs.next() ) {
//computing
}
rs.close();
st.clearParameters();
long computeEnd = System.currentTimeMillis();
System.out.println(index+" has done...");
System.out.println(" query time: "+ (queryEnd-queryStart));
System.out.println(" compute time: "+ (computeEnd-queryEnd));
}
对于大约100-200个循环,性能在开始时表现良好。但之后它突然下降了
控制台窗口中的结果打印为:
1 has done...
query time: 0
compute time: 0
2 has done...
query time: 0
compute time: 0
3 has done...
...
...
191 has done...
query time: 1
compute time: 0
192 has done...
query time: 0
compute time: 1
193 has done...
query time: 1018
compute time: 0
194 has done...
query time: 1142
compute time: 0
195 has done...
query time: 1122
compute time: 0
我的数据库在本地主机上。为什么会发生这种情况,什么会对性能产生如此显著的影响
如何提高性能
顺便说一句:对象语句,连接。。。是用java定义的。sql,我没有使用com。mysql。jdbc版本,我不知道有什么区别
# 1 楼答案
有助于提高性能的几个技巧:
可以尝试在MYSQL服务器配置中启用查询缓存吗? 请参阅:Query Caching in mysql
是否为此表设置了索引
# 2 楼答案
当您发出一个查询时,每次它通过网络,由db执行并返回给您。为了减少这种往返,可以使用一堆ID执行select查询
写一个如下的查询怎么样
从id所在的用户中选择名称(id0、id1、.idn)
编写一个方法来构造“id”子句,并在语句中设置它并执行它
如果ID数很高,则逐批使用ID集执行相同的查询