对IN子句中的大列表使用绑定变量时的性能问题

Question

我使用的是Sybase，它的代码如下所示：

String[] ids = ... an array containing 80-90k strings, which is retrieved from another table and varies.
for (String id : ids) {
    // wrap every id with single-quotes 
}
String idsAsString = String.join(",", ids); 
String query = String.format("select * from someTable where idName in (%s)", idsAsString);
getNamedParameterJDBCTemplate().query(query, resultSetExtractor ->{
    // do stuff with results
});

我已经计时了到达resultSetExtractor内部的时间，而且它从来不超过4秒。

但是为了保护代码的安全，我尝试使用绑定变量路由。因此，该代码如下所示：

String[] ids = ... an array containing 80-90k strings, which is retrieved from another table and varies.
String query = "select * from someTable where idName in (:ids)";
Map<String, Object> params = new HashMap<>();
params.put("ids", Arrays.asList(ids));
getNamedParameterJDBCTemplate().query(query, params, resultSetExtractor ->{
    // do stuff with results 
});

但是这样做需要花费4到5分钟的时间，才能最终抛出以下异常：

21-10-2019 14:04:01 DEBUG DefaultConnectionTester:126 - Testing a Connection in response to an Exception:
com.sybase.jdbc4.jdbc.SybSQLException: The token datastream length was not correct. This is an internal protocol error.

我还有其他一些代码，我将大小为1-10的数组作为绑定变量传递，并注意到这些查询从即时到长达10秒。

我感到惊讶的是，绑定变量的方式是完全不同的，更不用说有那么大的不同了。有人能解释一下这是怎么回事吗？绑定变量是否在幕后做了一些不同的事情，而不是通过JDBC发送格式化的字符串？还有另一种方法来保护我的代码而不大幅降低性能吗？

Answer 1

您应该通过一个显示计划/查询计划来验证数据库末尾的实际情况，但是使用' in‘子句最多只需要对'in’子句中的每个值执行一个索引搜索，因此10个值可以进行10个搜索，80k搜索可以执行80k搜索，因此速度要慢得多。Oracle实际上禁止在“in”子句中放置1000多个值，尽管Sybase没有那么严格，但这并不意味着这是个好主意。您可以通过在数据库中放置大量的值来冒堆栈和其他问题的风险--我看到这样的查询会在堆栈失败的情况下取出一个生产数据库实例。

最好是创建一个临时表，将80k值加载到其中，并使用前面使用in子句搜索的列在临时表和主表之间进行内部连接。