背景
最近對生產上最為耗時的幾條sql語句進行分析優(yōu)化，發(fā)現(xiàn)最為耗時的幾條sql中都出現(xiàn)了upper函數(shù)，通過分析它們的執(zhí)行計劃初步估計是由于upper函數(shù)的使用導致的性能低下，所以選其中一句比較簡單的sql進行了測試和分析。
sql語句如下：
SELECT CIFSTATE,CIFSEQ,CIFNAME,
EMAIL,MOBILEPHONE1,CIFNO
from
PER.zx_pcif
where upper(IDNO)=upper(?)
這條sql的功能是從pcif表中通過IDNO身份證號來查詢客戶個人信息，整個sql比較簡單，但是執(zhí)行的平均耗時卻達到了3.69秒， 其他耗時3.7秒和3.8秒的sql也是簡單的select查詢操作。
性能分析
初步分析
通過分析這些sql語句的執(zhí)行計劃，如下圖所示：
發(fā)現(xiàn)sql整個執(zhí)行計劃中，第一步通過索引來查詢記錄最為消耗性能，占比達到99%。而sql中是通過設置 where upper(IDNO)=upper(?) 來進行查找，所以初步推斷是由于upper()函數(shù)的使用導致了性能的下降。為此搭建了測試環(huán)境對upper()函數(shù)性能進行了驗證。
測試驗證
實驗環(huán)境：
系統(tǒng)：MacBook Pro 處理器：2.2GHZ Intel Core i7內存：16G
數(shù)據(jù)庫：MySql 5.7.13 數(shù)據(jù)量：500萬
測試流程：
1.建立表user，其主要主要字段如下：
其中id為主鍵，idno為身份證號，在idno上建立索引。
2.向表user中插入隨機生成的500萬條記錄；
3.使用uppper()函數(shù)來執(zhí)行查找，sql如下：
select * from user where upper(idno) = upper('12010419870329934X');
執(zhí)行結果如下:
結果顯示總共花費的時間為2.066秒。
4.不使用uppper()函數(shù)來執(zhí)行查找，sql如下：
select * from user where idno = '52262319560201341X';
執(zhí)行結果如下:
結果顯示總共花費的時間為0.00042秒
結果分析：
結果對比
數(shù)據(jù)顯示使用uppder()函數(shù)的耗時是不使用uppder()的4919倍，所以upper函數(shù)的使用會嚴重影響sql的執(zhí)行效率。
優(yōu)化方案
方案一
將現(xiàn)在數(shù)據(jù)庫表中IDNO字段數(shù)據(jù)全部轉為大寫，然后執(zhí)行sql之前，將參數(shù)轉為大寫，最后在sql中用“=”來執(zhí)行查詢。
方案二
數(shù)據(jù)庫中數(shù)據(jù)維持現(xiàn)狀，然后執(zhí)行sql之前，將身份證號參數(shù)分別轉為大小寫兩個參數(shù)，最后在sql中用“=”和“or”進行組合執(zhí)行查詢。
