英文:
Optimization reading from database
问题
我需要检查数据库中是否存在约2,000条记录，我在数据库大小为1,000,000时测试了ID，需要46秒。这太长了，因为将来这个数据库可能会有超过500,000,000条记录。是否有加速从数据库中搜索的方法？我在Java中使用JDBC，以下是代码：
public int search(List<String> toSearch) throws SQLException {
String query = "SELECT * FROM strings WHERE string = ?";
StringBuilder sB = new StringBuilder(query);
for (int i = 0; i < toSearch.size() - 1; i++) {
sB.append(" OR string = ?");
}
System.out.println(toSearch.size());
PreparedStatement prep = con.prepareStatement(sB.toString());
int i = 1;
for (String string : toSearch) {
prep.setString(i, string);
i++;
}
long data = System.currentTimeMillis();
ResultSet resultSet = prep.executeQuery();
long data2 = System.currentTimeMillis();
System.out.println((data2 - data) / 1000);
List<String> toReturn = new ArrayList<>();
while (resultSet.next()) {
toReturn.add(resultSet.getString("string"));
}
return toReturn.size();
}
表名为strings，列名为string。
英文:
I need to check in database exist ~2k records, I test id on db size 1.000.000 and it takes 46s. Its too long, because in future this db can have more than 500.000.000 records. Is any way to speed up searching from db ? I use JDBC in java, here's code :
public int search(List<String> toSearch) throws SQLException {
String query = "SELECT * FROM strings where string = ?";
StringBuilder sB = new StringBuilder(query);
for (int i=0; i<toSearch.size()-1; i++) {
sB.append("OR string=?");
}
System.out.println(toSearch.size());
PreparedStatement prep = con.prepareStatement(sB.toString());
int i=1;
for (String string : toSearch) {
prep.setString(i, string);
i++;
}
long data = System.currentTimeMillis();
ResultSet resultSet = prep.executeQuery();
long data2 = System.currentTimeMillis();
System.out.println((data2 - data) / 1000);
List<String> toReturn = new ArrayList<>();
while (resultSet.next()) {
toReturn.add(resultSet.getString("string"));
}
return toReturn.size();
}
Table name is strings, column string.
答案1
得分: 0
首先，您需要在string列上创建一个索引。
CREATE INDEX indexName ON strings(string);
不要为H2构建具有可变数量参数的查询。使用以下方法：
PreparedStatement ps = con.prepareStatement("SELECT * FROM strings WHERE string = ANY(?)");
ps.setObject(1, toSearch.toArray(new String[0]));
如果您使用一些旧不受支持的H2版本，请使用其TABLE()函数，如您版本附带的文档中所述（PDF中的“Prepared Statements and IN(...)”部分）。
对于其他DBMS，您可能需要其他技巧来避免动态生成SQL代码，= ANY(?)将在PostgreSQL及其派生版本以及H2中起作用，但在其他DBMS中不起作用。
英文:
First of all, you need to have an index on string column.
CREATE INDEX indexName ON strings(string);
Do not construct queries with variable number of arguments for H2. Use
PreparedStatement ps = con.prepareStatement("SELECT * FROM strings WHERE string = ANY(?)");
ps.setObject(1, toSearch.toArray(new String[0]));
If you use some old unsupported version of H2, use its TABLE() function instead as described in documentation distributed with your version (Prepared Statements and IN(...) section in PDF).
With other DBMS you may need other tricks to avoid dynamic generation of SQL code, = ANY(?) will work in PostgreSQL and its forks and in H2, but not in other DBMS.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论