使用 JDBC 參數查詢很慢，使用串聯 SQL 查詢很快

我有一個可以使用 mysql 或 SQL Server 執行的應用程序。有一個查詢可以在 mysql 上合理的時間內執行，但在 SQL Server 上執行速度很慢。我的應用程序使用休眠，但是這個特定的查詢是用原始 SQL 編碼的，所以我確切地知道正在執行什麼 SQL。如果我直接在數據庫客戶端中在 SQL Server 上執行相同的查詢，它會執行得很快。只有在我的應用程序中執行時它才會很慢。

我發現如果我不使用 JDBC 參數並將值連接到 SQL 字元串中，它的執行速度會快得多。然而，這不是一個好的解決方案；它不防範 SQL 注入或處理可能需要轉義的字元。

我找到了在連接上使用 sendStringParametersAsUnicode=false 的建議，但這不適合我的情況，因為這些列都是 nvarchar。無論如何，我確實嘗試過這個，但它並沒有讓任何事情變得更快並且讓一些事情變得更慢。我還發現提示 jtds 驅動程序更快的資訊，但它與休眠不兼容，因此我必須使用 Microsoft 驅動程序（mssql-jdbc-7.0.0.jre8.jar）。

我執行緩慢的程式碼如下所示：

   String sql = " SELECT DISTINCT FOO.BAR_ID_ "
           [big join here across multiple tables]
           "WHERE FOO.ID_= :fooId " +
           " AND BAZ.NAME_ LIKE :likeParameter ";
   Query query = getSession().createSQLQuery(sql)
           .setParameter("fooId", fooId)
           .setParameter("likeParameter", likeParameter);
   return query.list();

如果我把它改成這樣，它會執行得很快：

String sql = " SELECT DISTINCT FOO.BAR_ID_ "
           [big join here across multiple tables]
           " WHERE FOO.ID_= " + fooId  +
           " AND BAZ.NAME_ LIKE '" + likeParameter + "'";
   Query query = getSession().createSQLQuery(sql);
   return query.list();

LIKE 參數在兩端都有萬用字元，我希望這可以防止在該列上使用索引，但是對於串聯情況也是如此，並且它執行得很快。顯然，除了 Unicode 問題之外，SQL Server 和 JDBC 參數還有其他一些問題。我能做些什麼來解決這個問題？

更新：這裡是查詢計劃：

使用線上數據快速查詢：https ://www.brentozar.com/pastetheplan/?id=SkkH0NiU4

帶參數的慢查詢：https ://www.brentozar.com/pastetheplan/?id=SkBxhQoLE

不是一個完整的答案，但這裡有一些通過查看計劃的診斷和建議。

您正在執行 SQL 2016，因此計劃中有大量有用的資訊。

首先是您正在執行 SQL 2016 SP1 (13.0.4224.16) 的 GDR 更新檔。SQL 2016 SP2 於大約一年前的 2108 年 4 月 24 日發布，一年後應用服務包需要支持。因此，您可以在 SQL 2016 SP2 或 SQL 2017 上對此進行測試，以查看其行為是否有所不同。

優化參數化查詢時，SQL Server 必須選擇將用於所有未來值的計劃fooId和likeParameter。顯然，它猜測您通常會有一個選擇性的 LIKE 謂詞，因此使用名稱索引平均而言會更便宜。使用硬編碼值，SQL Server 不需要建構適用於多個不同 LIKE 謂詞的計劃，因此會選擇不使用非聚集索引的計劃。

為了評估謂詞，NAME_ LIKE @pSQL Server 正在對 NAME_ 上的非聚集索引進行範圍掃描。如果您碰巧傳遞了一個以幾個字面字元開頭的 LIKE 謂詞，這非常有用，但是當它以萬用字元開頭時，將需要對索引進行完整掃描。這裡掃描並評估 14M 行中的每一行的 LIKE 謂詞。

在快速計劃中，SQL 使用聚集索引查找 21,518 個單獨的組件，並根據 LIKE 謂詞檢查每個組件的名稱。

從計劃：

計劃之間的區別在於單個運算符，它佔了所有增加的成本：從索引中掃描 14,675,300 行

$$ PDL_COMPONENT $$.$$ IDX_COMPONENT_NAME $$，佔用 37 秒的 CPU 時間：

 <RunTimeInformation>
   <RunTimeCountersPerThread 
     Thread="0" 
     ActualRows="741" 
     Batches="0" 
     ActualEndOfScans="1" 
     ActualExecutions="1" 
     ActualExecutionMode="Row" 
     ActualElapsedms="37038" 
     ActualCPUms="37028" />
 </RunTimeInformation>

不是參數嗅探，因為 ParameterCompiledValue 與 ParemeterRuntimeValue 相同：

   <ParameterList>
     <ColumnReference Column="@P1" ParameterDataType="nvarchar(127)" ParameterCompiledValue="N'%geronimo%'" ParameterRuntimeValue="N'%geronimo%'" />
     <ColumnReference Column="@P0" ParameterDataType="int" ParameterCompiledValue="(9)" ParameterRuntimeValue="(9)" />
   </ParameterList>

在這裡使用帶有硬編碼值的查詢並不是最糟糕的主意。或者您可以嘗試重新編譯並關閉計劃記憶體而不放棄對查詢進行參數化。

而且我不知道數據庫中的索引和外鍵是什麼，但是使用DISTINCT的程式碼味道真的很糟糕，因為它經常表明一個或多個JOIN是不正確的，它會影響查詢的基數估計。

我們注意到我們的系統中有類似的行為。

並且還遇到了使用硬編碼參數而不是使用編寫查詢setParameter()可以解決問題。

我們正在使用MS SQL Server，經過進一步調查，我們注意到問題的根本原因是 sql server 驅動程序的預設配置，它將查詢參數作為 unicode 傳輸。這導致我們的索引被忽略，因為它們基於查詢列上的 ascii 值。

解決方案是在 jdbc url 中設置此屬性：sendStringParametersAsUnicode=false

有關詳細資訊，請參閱Stackoveflow 上的此問答。

引用自：https://dba.stackexchange.com/questions/231109