前幾次的編碼最佳實踐系列，我們都著眼于Java代碼，今天我們換個話題，看看另外一個領域，和Java代碼大相徑庭的SQL。
這次作為素材出場的，是來自項目中的一段SQL，用于BlackWhiteList Adapter，在每次請求時檢查一下，看當前用戶是否在黑白名單中。
先介紹一下數據庫結構，很簡單的三張表：BWLIST記錄黑白名單信息，BWLISTXADDRESS記錄每個黑白名單下的地址列表，BWLISTXAPPLICATION 記錄每個application關聯到的黑白名單。查詢時有三個輸入：PARTNER_ID和APP_ID是當前application的唯一標識，通過這兩個參數就可以在BWLISTXAPPLICATION中找到對應的黑白名單的id(可能有多個)，然后通過黑白名單的id就可以在BWLISTXADDRESS找到對應的所有地址，結合第三個參數ADDRESS就可以得知到當前地址是否在黑背名單中。
還是直接看SQL吧，典型的3表聯合查詢：
String sql = "select A2.ID, A2.ADDRESS, A2.ADDRESS_TYPE, A2.DESCRIPTION, A1.type BWLIST_TYPE, A3.LIST_LEVEL, A3.SC_ID, A3.PARTNER_ID, A3.APP_ID
from BWLIST A1, BWLISTXADDRESS A2, BWLISTXAPPLICATION A3
where A1.id = A2.BWLIST_ID and A2.BWLIST_ID = A3.BWLIST_ID
and A3.PARTNER_ID = ? and A3.APP_ID = ?
and (INSTR(?, A2.ADDRESS ) > 0)";
直接看where 后面的內容，"A1.id = A2.BWLIST_ID and A2.BWLIST_ID = A3.BWLIST_ID "好理解, BWLISTXADDRESS和BWLISTXAPPLICATION兩個表的BWLIST_ID字段是外鍵，對應到表BWLIST的主鍵ID，用來做關聯。后面的"A3.PARTNER_ID = ? and A3.APP_ID = ? "是用來唯一標識當前application，為了加速查詢，建有一個PARTNER_ID+APP_ID的索引：UNIQUE INDEX SYS_C0098362 (APP_ID(150), SP_PK, BWLIST_ID)。
關鍵在最后一個where條件：(INSTR(?, A2.ADDRESS ) > 0)"。這里使用INSTR()而不是簡單的=，是考慮到地址可能有多種格式，比如"13900000000"，"tel:13900000000", "tel:+8613900000000"，實際都是一個號碼。因此考慮在數據庫將A2.ADDRESS保存為"13900000000"，這樣無論當前輸入的地址格式是"13900000000","tel:13900000000", 還是"tel:+8613900000000"，都可以被正確處理。
同樣為了加速查詢，開發的同事為A2.ADDRESS這個字段增加了索引：UNIQUE INDEX SYS_C0098354 (ADDRESS(250), BWLIST_ID)。
這樣在上述幾個索引的支持下，前面的sql語句的where條件，按說基本都被索引優化到了 ————— 但是，相信一些有經驗的同事可能第一時間就已經反映過來，最后的這個基于ADDRESS字段的索引，有問題！
問題出在(INSTR(?, A2.ADDRESS ) > 0)，INSTR()是一個SQL函數，作為一個基本常識，大家都知道的：如果索引列是SQL函數的參數，那么索引在查詢時是用不上的。
很遺憾，當時編寫這個SQL的同學可能不知道或者一時沒有反應過來，結果上述的SQL被寫入到產品。隨后更糟糕的是，在壓力測試中，居然沒有被發現，原因是測試時使用的數據規模太小，只為被測試的黑白名單準備了幾十個地址，所以雖然索引無法被利用，但是對于區區幾十條記錄，不走索引反而能更快一些...... 就這樣逃過測試，發布并部署運行于客戶線上。
然后，后面的事情就可以想象了，客戶實際跑的時候，BWLISTXADDRESS中記錄的條數遠不是幾十，而是幾十萬，幾百萬......而黑白名單過濾功能一旦開啟，是每個請求都要檢查一次，上面的SQL每次都要執行一次。于是數據庫理所當然的頂不住，整個系統的速度都被拖累，客戶就抱怨說黑白名單開啟之后性能出現大幅下降。而痛苦的是我們自己測試時不能重現問題，嗯，用那個只有幾十個地址的名單當然重現不出來......
總之這個小bug引來了后面一堆的事情，我們不繼續吐糟，回頭來看看，問題是如何一步一步的產生：
1. 開發人員犯錯，常識性的小錯誤：索引對SQL函數無效
2. 對于性能敏感的SQL，沒有做慎重的處理：如果當時有用查詢分析器看一下執行計劃，就可以避免出現類似的索引失效而不自知的情況;如果有其他有經驗的同事review這個SQL，也可以在早期發現問題
3. 性能測試時數據建模失誤，沒有模擬到真實線上數據的規模，以至于最后一道關卡被突破，讓性能問題逃過了性能測試
第3條是另外一個話題，我們這里重點來看1和2:
1的問題本質上是一個老生常談的問題：如何避免在同一個坑中跌倒多次？這里所說的同一個坑，針對不同的對象有不同的含義：對于個人，上次犯下的錯誤下次會不會還繼續？對于一個團隊，A同學出錯的地方B同學是否能避免？對于整個公司，A產品線遇到的問題B產品線能不能有所借鑒？
可以說，我們現在的這個編碼最佳實踐的系列，就是為了解決類似的問題：將我們不同的產品線犯下的一些典型問題總結下來，分享給其他人，避免同一個坑不停的有人踩的尷尬和無奈。
而2的問題在于我們的WOW(Way Of Working)還不夠完善，對于性能敏感的關鍵代碼，應該保持足夠的謹慎和細致，類似的每次查詢都要執行一次的SQL，怎么都要看看執行計劃才能放心寫入產品。我們也應該有完善的code review機制來保證當有疏漏的時候應該能及時補救。
最后我們再回到原始問題，關于這個SQL，我們現在知道INSTR()函數用不上索引，通常的解決方案是使用函數索引，但是對于"INSTR(?, A2.ADDRESS ) > 0"，函數索引也無能為力。因此只好修改業務處理方式，不再在SQL查詢這個層次處理地址格式的問題，將格式問題拋給Java代碼：在數據庫中保存標準格式如"tel:13900000000"，業務處理流程中對輸入的地址格式做標準化，將地址匹配簡化為簡單的"="操作，這樣可以極大的節約數據庫查詢開銷。
最終修訂版本的SQL如下:
String sql = "select A2.ID, A2.ADDRESS, A2.ADDRESS_TYPE, A2.DESCRIPTION, A1.type BWLIST_TYPE, A3.LIST_LEVEL, A3.SC_ID, A3.PARTNER_ID, A3.APP_ID
from BWLIST A1, BWLISTXADDRESS A2, BWLISTXAPPLICATION A3
where A1.id = A2.BWLIST_ID and A2.BWLIST_ID = A3.BWLIST_ID
and A3.PARTNER_ID = ? and A3.APP_ID = ?
and A2.ADDRESS=?";
這也是性能優化的常見方式：將復雜的業務邏輯盡量留給Java代碼，盡可能的保持數據庫操作的簡單和高效。