Java JDBC数据库查询优化咨询：批量存在性校验过慢问题

首先，你的当前代码用OR拼接2000个等值条件，这种写法在数据量小的时候可能还能凑合用，但数据量到100万就耗时46秒，未来到5亿的话肯定会彻底卡爆——数据库优化器很难高效处理这么多OR条件，大概率会走全表扫描，完全发挥不了索引的作用。下面给你几个针对性的优化方案，从简单到复杂，一步步来：

1. 用IN子句替代多个OR #

这是最直接的优化，数据库对IN的优化逻辑比一堆OR友好得多，尤其是当string字段有索引的时候。修改你的SQL生成逻辑：

public int search(List<String> toSearch) throws SQLException {
    // 改用IN子句
    String query = "SELECT string FROM strings WHERE string IN (";
    StringBuilder sB = new StringBuilder(query);
    for (int i = 0; i < toSearch.size(); i++) {
        if (i > 0) {
            sB.append(", ");
        }
        sB.append("?");
    }
    sB.append(")");
    
    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();
}

注意：有些数据库（比如MySQL）默认限制IN子句的参数数量为1000，如果你的toSearch超过这个数，可以拆分批次（比如每500条查一次），然后合并结果。

2. 给string字段添加索引 #

这是重中之重！如果你的strings表还没给string字段加索引，现在立刻加：

CREATE INDEX idx_strings_string ON strings(string);

等值查询场景下，B-tree索引能把查询时间从全表扫描的O(n)降到O(log n)，5亿数据的话，这个索引能让查询速度提升几个数量级。如果string字段有重复值，还可以考虑用覆盖索引（不过这里你只查string字段，普通索引已经是覆盖索引了）。

3. 拆分批量查询 #

如果IN的参数数量超过数据库限制，或者单条SQL太长导致解析耗时，把2000条数据拆分成多个小批次（比如每500条一组），分别查询后合并结果：

public int search(List<String> toSearch) throws SQLException {
    int batchSize = 500;
    int totalCount = 0;
    
    for (int start = 0; start < toSearch.size(); start += batchSize) {
        int end = Math.min(start + batchSize, toSearch.size());
        List<String> batch = toSearch.subList(start, end);
        
        String query = "SELECT string FROM strings WHERE string IN (";
        StringBuilder sB = new StringBuilder(query);
        for (int i = 0; i < batch.size(); i++) {
            if (i > 0) sB.append(", ");
            sB.append("?");
        }
        sB.append(")");
        
        PreparedStatement prep = con.prepareStatement(sB.toString());
        int paramIdx = 1;
        for (String s : batch) {
            prep.setString(paramIdx++, s);
        }
        
        ResultSet rs = prep.executeQuery();
        while (rs.next()) {
            totalCount++;
        }
        rs.close();
        prep.close();
    }
    return totalCount;
}

这种方式避免了单条SQL过大，也降低了数据库的单次解析压力。

4. 使用临时表+JOIN（适合超大数据量场景） #

当数据库数据量到5亿级时，即使是IN查询，可能性能也会打折扣，这时候可以用临时表配合JOIN：

1. 创建临时表（注意不同数据库的临时表语法略有差异，比如MySQL用CREATE TEMPORARY TABLE）：

CREATE TEMPORARY TABLE temp_search (string VARCHAR(255) PRIMARY KEY);

2. 把要查询的2000条数据批量插入临时表：

// 批量插入临时表
String insertSql = "INSERT INTO temp_search(string) VALUES (?)";
PreparedStatement insertPrep = con.prepareStatement(insertSql);
for (String s : toSearch) {
    insertPrep.setString(1, s);
    insertPrep.addBatch();
}
insertPrep.executeBatch();
insertPrep.close();

3. 用JOIN查询原表：

String joinQuery = "SELECT s.string FROM strings s JOIN temp_search ts ON s.string = ts.string";
PreparedStatement joinPrep = con.prepareStatement(joinQuery);
ResultSet rs = joinPrep.executeQuery();
// 统计结果...
int count = 0;
while (rs.next()) {
    count++;
}
rs.close();
joinPrep.close();
return count;

临时表可以加主键索引，JOIN操作的效率会比IN更高，尤其是当查询列表很大的时候。

5. 调整JDBC参数优化性能 #

• 设置合适的fetchSize：默认的JDBC fetch size很小（比如MySQL默认是10），会导致多次从数据库拉取数据，设置prep.setFetchSize(1000);可以减少网络IO次数。
• 关闭自动提交：如果你的查询是一系列操作，可以暂时关闭con.setAutoCommit(false);，查询完成后再提交，减少事务开销。

6. 避免查询不必要的字段 #

你原来的代码用SELECT *，但实际上只需要string字段，改成SELECT string可以减少结果集的数据传输量，尤其是当表中有其他大字段时，这个优化效果很明显。

内容的提问来源于stack exchange，提问作者SzymonZ