从数据库中优化读取

huangapple go评论17阅读模式
英文:

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&lt;String&gt; toSearch) throws SQLException {
	String query = &quot;SELECT * FROM strings where string =  ?&quot;;
	StringBuilder sB = new StringBuilder(query);
	for (int i=0; i&lt;toSearch.size()-1; i++) {
		sB.append(&quot;OR string=?&quot;);
	}
	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&lt;String&gt; toReturn = new ArrayList&lt;&gt;();
	while (resultSet.next()) {

		toReturn.add(resultSet.getString(&quot;string&quot;));

	}
	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(&quot;SELECT * FROM strings WHERE string = ANY(?)&quot;);
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.

huangapple
  • 本文由 发表于 2020年7月30日 20:46:59
  • 转载请务必保留本文链接:https://go.coder-hub.com/63173500.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定