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