資料庫索引失效的一種情境：分析問題的思路和策略，索引分析問題

資料庫索引失效的一種情境：分析問題的思路和策略，索引分析問題

這是公司研發團隊發現的一個關於資料庫索引失效方面的問題，我們的工程師對該問題進行了分析和解決並寫了這份小結。歸根揭底還是對開發架構和技術應用的把握上存在紕漏，但個人覺得在分析問題->找出原因->確認解決方案這一思路和策略上本文能起到一定借鑒作用，所以稍微梳理了一下拿出來和大家分享。

問題的現狀是測試人員反饋某一個功能操作耗時很長（需要20秒以上），而開發人員核對代碼發現無論從商務邏輯上還是代碼實現上都沒有問題，涉及到的資料查詢等功能在資料庫中也建立了合適的索引以確保查詢效率，於是我們的工程師就去研究了，過程如下：

1.      首先應用內嵌入了一個系統監控平台JavaMelody（此為google出品的一個開源神器 https://code.google.com/p/javamelody/）

2.      通過該神器觀測到該操作的方法執行幀資訊，發現其中耗時最多的一步：

3.      下鑽至此方法內部，看到其調用的sql為：

4.      對該sql進行分析，將其摘入PLSQL Developer工具內，並對變數賦值進行驗證執行，反覆驗證多次發現每次速度都還不到1秒：

5.      就此產生疑惑：為何在伺服器上執行需要20多秒，而在用戶端工具內卻飛快呢？猜測跟JDBC有關。為了證實這個想法，做了單元測試，果不其然，在單元測試中同樣需要20多秒，為毛通過JDBC查詢就這麼慢呢？

6.      前思後想，在JDBC中和在用戶端中查詢最大的區別就在於前者使用了綁定變數，而後者是一個靜態sql。為了進一步驗證，把單元測試中的sql變成了靜態（非變數綁定形式），發現快多了，和在第4步PLSQL Developer用戶端工具中執行一樣快，委屈了JDBC：

原來問題在於變數綁定。

問題找到了，那就要分析導致這種結果的原因。簡單說來，我們的故事是這樣的：

MapSqlParameterSource parameters = new MapSqlParameterSource();parameters.addValue("queryTimeFrom", queryTimeFrom );parameters.addValue("queryTimeTo", queryTimeTo);

其中queryTimeFrom和queryTimeTo是方法傳進來的參數，類型為java.util.Date。

執行查詢調用的是：

List<DrugOrderAdmin> result = getJdbcTemplate().query(sql.toString(), parameters, new DrugOrderAdminMapper());

本以為傳進sql裡的參數是Date類型，但其實經過了spring架構內部代碼那麼一折騰，最後變成了Timestamp類型：

其中 isDateValue方法的定義為：

再到oracle中，原本sql中的 AND PLAN_TIME >=?AND PLAN_TIME <= ?  這個條件，由於傳入的參數是Timestamp類型，導致oracle解譯器把sql解釋為：

AND TO_TIMESTAMP(PLAN_TIME) >= ?AND TO_TIMESTAMP(PLAN_TIME) <= ?

這樣一來，PLAN_TIME欄位上的索引就被忽略，走了全表掃描（除非另外建一個函數索引TO_TIMESTAMP(PLAN_TIME)），導致最終查詢速度產生了如此巨大的差別。

原因找到了，解決方案也就很明確了，即在綁定變數時強制指定JDBC類型為DATE：

parameters.addValue("queryTimeFrom", queryTimeFrom);改成:parameters.addValue("queryTimeFrom", queryTimeFrom , Types.DATE);parameters.addValue("queryTimeTo", queryTimeTo); 改成:parameters.addValue("queryTimeTo", queryTimeTo, Types.DATE);

再回到PLSQL Developer中，類比一下傳入參數為Timestamp類型的情況，這次採用綁定變數的方式，即在所有需要傳入參數的地方預留一個變數（以&開頭命名），在執行時用戶端會自動彈出變數輸入對話方塊：

這次可以看到，以綁定變數方式執行查詢，並且綁定變數為Timestamp類型時，PLSQL Developer同樣需要很久，之前的疑惑不釋而解：

總結：在使用綁定變數進行sql查詢時，一定要注意傳入參數的類型和column類型一致，能夠明確指定參數類型的情況下盡量明確指定，否則類型不當會導致列的索引失效。