我当前遇到一个使用许多sql select的应用程序的性能问题。
编程语言是java,我使用的是mysql数据库。它包含大约1000万条记录。它需要做的是在数据库中以邮编和门牌号为参数查找记录。当它找不到记录时,它需要只使用邮政编码进行查询,并获得房屋编号最低的记录。当在数据库中找不到邮政编码时,应用程序需要处理此问题。
因此,执行单个查询的代码如下所示:
Statement select = "select * from zipcode_addresses where zipcode = ? and houseNo =?";
ResultSet rs = select.executeQuery();
if(rs.next()) {
dealWithResult(rs);
}
else {
Statement alternativeSelect = "select * from zipcode_addresses where zipcode = ? group by houseNo having min(houseNo)";
ResultSet rs = alternativeSelect.executeQuery();
if(rs.next()) {
dealWithResult(rs);
} else {
System.err.println("Could not find zipcode :" + zipcode);
}
}
有没有一种正确的方法来进行批量选择查询,以处理找不到的数据?
谢谢!
更新
表格结构如下:
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| zipcode | varchar(6) | NO | PRI | NULL | |
| house_no | int(11) | NO | PRI | NULL | |
| sanddcode | varchar(45) | NO | | NULL | |
| depot | varchar(3) | NO | | NULL | |
| network_point | varchar(6) | NO | | NULL | |
| region | varchar(3) | NO | | NULL | |
| seq | int(11) | NO | | NULL | |
| cluster_id | varchar(1) | NO | | NULL | |
| strand_id | int(11) | NO | | NULL | |
| strand_props_id | int(11) | NO | | NULL | |
| version_id | int(11) | NO | PRI | NULL | |
+-----------------+-------------+------+-----+---------+-------+
版本id、邮政编码和house_no的主键邮政编码和house_no上的索引以及邮政编码上的另一个索引,都使用BTREE索引。
该应用程序有时可能用于执行100万个不同的选择查询,而这一点花费的时间太长了。
您的代码片段没有显示如何准备语句。如果您的语句被多次调用,那么您应该查看PreparedStatement对象:
http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html
然后可以缓存您的语句以减少将来的开销。
您可以创建一个具有两个参数的存储过程,并使用House Number作为可选参数,或者让过程查找是否存在。
很大程度上取决于使用模式。您运行了多少查询,邮政编码丢失的频率等。首先,我会在可能的情况下使用PreparedStatements。我对MySQL不太熟悉,但它们通常被缓存并由连接数据库重用,这将有助于提高性能。接下来,如果邮政编码缺失很常见,我可能会在内存中建立一个邮政编码缓存,以短路对缺失进行3次查询。之后,我可能创建一个ZIP+门牌号的视图。更进一步取决于你的应用程序是如何工作的,但这些东西会有所帮助。
第二个SQL查询中的"group-by"是不必要的,会降低性能。为了获得最大性能,请替换此select(代码中的第二个(。。。
select * from zipcode_addresses where zipcode = ?
group by houseNo having min(houseNo)
用这个。。。
select min(houseNo) from zipcode_addresses where zipcode = ?
此外,请确保您在zipcode+houseNo上有一个索引(它看起来像是您的索引-来自更新后的帖子(。