寫在前面
? ? ? ?網(wǎng)上很多的文章都是教科書式的說教，缺乏實用價值笛丙。這也是筆者想寫此系列文章的初衷漾脂，希望把實際工作的實戰(zhàn)經(jīng)驗分享給大家，幫助大家解決實際問題胚鸯。后續(xù)的一系列文章都是筆者在實際工作遇到的問題骨稿，比較具有代表性，從實戰(zhàn)的角度進(jìn)行分析總結(jié)姜钳，希望能夠給大家?guī)韼椭?br> 關(guān)鍵字：數(shù)據(jù)庫坦冠、Mysql、Mybatis哥桥、Dubbo
一辙浑、問題背景
? ? ? ?其實并沒有什么血案，題目只是個噱頭而已拟糕，吸引大家進(jìn)來看看判呕，呵呵倦踢！既來之則安之，那就看下去吧侠草！定不負(fù)厚望辱挥，讓你有所收獲！如有不妥之處边涕，也請批評指正晤碘！
? ? ? ?言歸正傳，哪個碼農(nóng)不和數(shù)據(jù)庫打交道奥吩？雖然不要求碼農(nóng)像DBA那樣爐火純青哼蛆、登峰造極，但至少應(yīng)該了解基本的sql使用原則與優(yōu)化方法霞赫。
? ? ? ?首先介紹一下項目背景腮介，我們開發(fā)的是一個互聯(lián)網(wǎng)應(yīng)用，使用Java端衰、Spring Boot叠洗、Dubbox、mysql旅东、Mybatis等相關(guān)技術(shù)實現(xiàn)灭抑。采用典型的Dubbo API、Provider和Consumer三層架構(gòu)抵代，其中腾节，API定義業(yè)務(wù)服務(wù)接口，供Provider和Consumer使用荤牍；Provider實現(xiàn)業(yè)務(wù)邏輯并提供服務(wù)案腺，Consumer使用業(yè)務(wù)服務(wù)，這兩者打包為獨立運行的jar康吵，并部署到Web容器中劈榨。
? ? ? ?眼看上線日期節(jié)點臨近，卻突然間蹦出來一個錯誤晦嵌，而且困擾了一位開發(fā)小兄弟許久同辣，查不到具體原因，這可如何是好惭载？如果解決不了旱函，就真的要有血案發(fā)生了。關(guān)鍵時刻還得老將出馬棕兼！
二陡舅、問題分析
? ? ? ?先來看看錯誤是什么：
2019-05-07 10:45:30,072 [ERROR] [net.xxxxx.job.platform.config.app.job.ExportXxXxxMessageJob_Worker-1] c.d.d.j.e.h.i.DefaultJobExceptionHandler - JOb 'net.xxxxx.job.platform.config.app.job.ExportXxXxxMessageJob' exception occure in job processing com.alibaba.dubbo.rpc.RpcException: Failed to invoke the method selectXXXXX in the service net.xxxxxx.xxxx.xxxx.xxxxx.XxXxxxService. Tried 1 times of the providers [10.xx.xxx.213:10200] (1/4) from the registry 10.xx.xxx.209:2181 on the consumer 10.xx.xxx.213 using the dubbo version 2.8.4. Last error is: Failed to invoke remote service: interface net.xxxxxx.xxxx.xxxx.xxxxx.XxXxxxService, method: selectXXXXX, cause: Unable to invoke request
at com.alibaba.dubbo.rpc.cluster.support.FailoverClusterInvoker.doInvoke(FailoverInvoker.java:108) ~[dubbox-2.8.4.jar!/:2.8.4]
at com.alibaba.dubbo.rpc.cluster.support.AbstractClusterInvoker.invoke(AbstractClusterInvoker.java:227) ~[dubbox-2.8.4.jar!/:2.8.4]
at com.alibaba.dubbo.rpc.cluster.support.wrapper.MockClusterInvoker.invoke(MockClusterInvoker.java:72) ~[dubbox-2.8.4.jar!/:2.8.4]
at com.alibaba.dubbo.rpc.proxy.InvokerInvocationHandler.invoke(InvokerInvocationHandler.java:52) ~[dubbox-2.8.4.jar!/:2.8.4]
at com.alibaba.dubbo.common.bytecode.proxy5.selectXXXXX(proxy5.java) ~[dubbox-2.8.4.jar!/:2.8.4]
at xxx.xxx
......
Caused by: javax.ws.rs.ProcessingException: Unable to invoke request
at org.jboss.resteasy.client.jaxrs.engines.ApacheHttpClient4Engine.invoke(ApachHttpClient4Engine.java:287) ~[resteasy-client-3.0.7.Final.jar!/:?]
at org.jboss.resteasy.client.jaxrs.internal.ClientInvocation.invoke(ClientInvocation.java:407) ~[resteasy-client-3.0.7.Final.jar!/:?]
at org.jboss.xxx
......
? ? ? ?Dubbo中RPC調(diào)用時出錯，詢問了一下具體情況伴挚，這個錯誤在開發(fā)環(huán)境（Dev Env）上沒有出現(xiàn)過靶衍，當(dāng)部署到測試環(huán)境（Test Env）的時候就冒了出來。
? ? ? ?這個問題看似很尋常茎芋，不就是dubbo遠(yuǎn)程方法調(diào)用失敗嘛颅眶！一開始確實順著這個思路去查的，檢查了provider的配置田弥，檢查了consumer的配置涛酗，檢查了注入對象，檢查了傳參偷厦，都沒毛病吧烫尽！確定配置和代碼均是正確的只泼，為什么會出現(xiàn)這個錯誤呢剖笙？實際上這個方法是給后臺的定時任務(wù)Job調(diào)用的，那位兄弟一直都是在開發(fā)環(huán)境上開發(fā)調(diào)試请唱，一切都很順利弥咪，當(dāng)部署到測試環(huán)境后，突然冒出這個問題十绑，感覺很蹊蹺聚至，而且一直認(rèn)為代碼沒有問題。使用postman從前端發(fā)起調(diào)用debug了一下本橙，發(fā)現(xiàn)執(zhí)行到某個操作的時候竟然執(zhí)行時間超過30秒扳躬，而這步操作對應(yīng)執(zhí)行了一條sql語句，這肯定不正常吧跬ぁ贷币！到此，問題基本上明了了狂鞋，開發(fā)環(huán)境上數(shù)據(jù)量小片择，測試環(huán)境上數(shù)據(jù)量大，所以開發(fā)環(huán)境上沒有暴露出問題骚揍。
? ? ? ?把對應(yīng)的sql語句拿出來分析一下字管，首先在測試數(shù)據(jù)庫上手工執(zhí)行了一下該條語句，果不其然信不，執(zhí)行時間長達(dá)59秒之多嘲叔，這也太恐怖了！問題定位到了抽活，這就不難理解上面報錯的原因了硫戈，又查看了項目中dubbo的配置。
項目中dubbo的配置如下：
dubbo: active: true
application:
name: ${spring.application.name}
registry:
protocol: zookeeper
client: curator
protocol:
name: rest
host: 127.0.0.1
provider:
version: 1.0.0
timeout: 30000
retries: 0
loadbalance: random
? ? ? ?這下一切就都清楚了下硕，原來項目中配置的dubbo服務(wù)調(diào)用超時時間為30秒（provider.timeout: 30000）丁逝，所以dubbo服務(wù)調(diào)用失敗的原因是超時汁胆。好了，那接下來我們就對該條sql語句進(jìn)行分析和優(yōu)化霜幼。在分析和優(yōu)化之前嫩码，我先把優(yōu)化的結(jié)果貼出來。
| 優(yōu)化前執(zhí)行時間（單位秒） | 優(yōu)化后執(zhí)行時間（單位秒） |
|---|---|
| 59.010000s | 0.017000s |
該條sql中涉及到的數(shù)據(jù)庫表如下（真實的表名罪既、字段名铸题、和索引名都做了替換）：
| 序號 | 表名 | 數(shù)據(jù)量 |
|---|---|---|
| 1 | table1 | 9,825,006 |
下面開始正式講解分析及優(yōu)化的過程，該條sql語句如下：
select customer_id, sum(amount) as totalamount, count(1) as totalnum
from table1 where
available_time_end BETWEEN date_add(NOW(), interval 24 hour) and date_add(now(), interval 48 hour)
group by customer_id;
? ? ? ?這條sql語句實現(xiàn)的功能其實并不復(fù)雜琢感，查詢統(tǒng)計table1表中未來24小時至48小時這段時間將要到期的數(shù)據(jù)丢间。這位兄弟工作時間不長，完全以實現(xiàn)功能為導(dǎo)向驹针，根本沒考慮性能問題烘挫。不過這樣也好，這樣才能給老碼農(nóng)以表現(xiàn)的機會牌捷，也體現(xiàn)了老碼農(nóng)存在的價值墙牌，呵呵！姜還是老的辣嘛暗甥！
二話不說喜滨，先來EXPLAIN一下，查看Mysql的執(zhí)行計劃：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | table1 | index | idx_customer_id_order_serial_num_df | idx_customer_id_order_serial_num_df | 775 | (NULL) | 9825006 | Using where |
rows列顯示的預(yù)計掃描數(shù)據(jù)量基本就是表table1的全部數(shù)據(jù)撤防，雖然使用了索引idx_customer_id_order_serial_num_df虽风，但是還是檢查了表table1中的所有行，所以效率不高寄月。
再來看一下索引的定義：
| Name | Fields | Index Type | Index Method |
|---|---|---|---|
| idx_customer_id_order_serial_num_df |
customer_id,order_serial_num, df
|
NORMAL | BTREE |
| idx_status_available_time_end |
status, available_time_end
|
NORMAL | BTREE |
? ? ? ?看到這里辜膝，忽然眼前一亮，計上心頭漾肮！還有一個索引idx_status_available_time_end可以利用厂抖，這是個聯(lián)合索引，建立在status, available_time_end兩個字段之上克懊，而且available_time_end正好又是where的查詢條件忱辅。但是mysql的聯(lián)合索引使用最左前綴匹配原則，即最左優(yōu)先谭溉，在檢索數(shù)據(jù)時從聯(lián)合索引的最左邊開始匹配墙懂。顯然，我們應(yīng)該充分利用這個索引（idx_status_available_time_end）來提高效率扮念∷鸢幔看了一下status的定義和取值，呵呵！暗自高興一番巧勤，status是個smallint類型嵌灰，并且只有3個取值：0、1和2踢关。何不把status作為冗余的查詢條件伞鲫，然后再union all呢粘茄？對签舞，就這么干！于是把原來的查詢改寫如下：
select customer_id, sum(amount) as totalamount, count(1) as totalnum
from voucher
where status = 0 and (available_time_end BETWEEN date_add(NOW(), interval 24 hour) and date_add(now(), interval 48 hour))
group by customer_id
UNION ALL
select customer_id, sum(amount) as totalamount, count(1) as totalnum
from voucher
where status = 1 and (available_time_end BETWEEN date_add(NOW(), interval 24 hour) and date_add(now(), interval 48 hour))
group by customer_id
UNION ALL
select customer_id, sum(amount) as totalamount, count(1) as totalnum
from voucher
where status = 2 and (available_time_end BETWEEN date_add(NOW(), interval 24 hour) and date_add(now(), interval 48 hour))
group by customer_id
看一下執(zhí)行時間：
OK, Time: 0.017000s
飛起來一樣柒瓣！提升這么明顯儒搭，看來思路是對的。
EXPLAIN一下看看執(zhí)行計劃：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | table1 | range | idx_customer_id_order_serial_num_df,idx_status_available_time_end | idx_status_available_time_end | 8 | (NULL) | 122 | Using index condition; Using temporary; Using filesort |
| 2 | UNION | table1 | range | idx_customer_id_order_serial_num_df,idx_status_available_time_end | idx_status_available_time_end | 8 | (NULL) | 21 | Using index condition; Using temporary; Using filesort |
| 3 | UNION | table1 | range | idx_customer_id_order_serial_num_df,idx_status_available_time_end | idx_status_available_time_end | 8 | (NULL) | 1 | Using index condition; Using temporary; Using filesort |
| (NULL) | UNION RESULT | <union1,2,3> | ALL | (NULL) | (NULL) | (NULL) | (NULL) | (NULL) | Using temporary |
（EXPLAIN輸出說明：ID列表明了該語句所在的層級芙贫，如果ID相同從上到下執(zhí)行搂鲫，如果ID不同則ID越大的越先執(zhí)行，其作用類似于執(zhí)行計劃中縮進(jìn)磺平。）
? ? ? ?跟預(yù)想的一樣魂仍，使用了索引idx_status_available_time_end，并且每次檢查的行數(shù)為122拣挪、21擦酌、1，數(shù)據(jù)量大幅減少菠劝，性能提升非常明顯赊舶！
? ? ? ?順便插一句，為什么用Union All而不用Union呢赶诊？因為Union不僅要做去重（去掉重復(fù)行）處理笼平，還要做排序處理，而Union All則不會舔痪，在本例的使用場景中寓调，不會存在重復(fù)的數(shù)據(jù)也沒有排序的需求，所以使用效率更高的Union All锄码。這也說明了使用什么樣的技巧完全取決于使用場景與需求夺英。
三、總結(jié)
? ? ? ?因為要培養(yǎng)新人巍耗，所以大概給那位小兄弟講了一下sql優(yōu)化的基本原則和方法秋麸，嘮叨半天，總結(jié)一下炬太，教學(xué)相長嘛灸蟆！
? ? ? ?首先來講講sql語句的執(zhí)行順序：
? ? ? ?sql查詢語句的執(zhí)行順序可不是你所使用的語法順序。所有的查詢語句都是從from開始執(zhí)行的，在執(zhí)行過程中炒考，每個步驟都會為下一個步驟生成一個虛擬表可缚，這個虛擬表將作為下一個執(zhí)行步驟的輸入。
(1) from
(2) join斋枢，on
(3) join
(4) where
(5) group by
(6) avg, sum....
(7) having
(8) select
(9) distinct
(10) order by
(11) Limit或top
第一步：首先對from子句中的前兩個表執(zhí)行一個笛卡爾乘積帘靡，此時生成虛擬表 vt1；
第二步：接下來便是應(yīng)用on篩選器瓤帚，on中的邏輯表達(dá)式將應(yīng)用到vt1中的各個行描姚，篩選出滿足on邏輯表達(dá)式條件的行，生成虛擬表vt2戈次；
第三步：如果是outer join轩勘，那么這一步就將添加外部行，left outer join就把左表在第二步中過濾的添加進(jìn)來怯邪，如果是right outer join 那么就將右表在第二步中過濾掉的行添加進(jìn)來绊寻，這樣生成虛擬表vt3。如果from子句中的表數(shù)目多于兩個悬秉，那么就將vt3和第三個表連接從而計算笛卡爾乘積澄步，生成虛擬表，重復(fù)1-3的步驟和泌，最終得到一個新的虛擬表vt3村缸；
第四步：應(yīng)用where篩選器，對于上一步產(chǎn)生的虛擬表vt3應(yīng)用where篩選器允跑，生成虛擬表vt4王凑；
第五步：group by子句將vt4中的相同值組合成為一組，得到虛擬表vt5聋丝；
第六步：應(yīng)用cube或者rollup選項索烹，為vt5生成超組，生成vt6弱睦；
第七步：應(yīng)用having篩選器百姓，生成vt7，只有符合條件的記錄才會被插入到虛擬表VT7中况木；
第八步：處理select子句垒拢，將vt7中的在select中出現(xiàn)的列篩選出來，生成vt8火惊；
第九步：應(yīng)用distinct子句求类，移除vt8中相同的行，生成vt9屹耐；
第十步：應(yīng)用order by子句尸疆。按照排序條件排序vt9，此時返回的是一個游標(biāo)，而不是虛擬表寿弱。排序的成本是很高的犯眠，所以order by要慎用；
第十一步：應(yīng)用limit或者top選項症革，取出指定行的記錄筐咧，產(chǎn)生虛擬表VT10, 并將結(jié)果返回給客戶端。
還有就是查詢優(yōu)化的基本原則和注意事項：
- 盡量使用索引噪矛；
- 盡量避免全表掃描量蕊；
- 在聯(lián)表操作時，盡量選擇數(shù)據(jù)量較小的表做基表摩疑，這是最基本的優(yōu)化原則危融；
- 確保被驅(qū)動的表被索引，不能確保驅(qū)動表被索引的情況下雷袋，加大 join_buffer_size 的大小辞居；
- 子查詢會生成臨時表楷怒，但是臨時表是沒有任何索引的，子查詢生成的臨時表只能進(jìn)行全表掃描瓦灶；
- 當(dāng)查詢結(jié)果超過總數(shù)據(jù)一定比例的時候鸠删，走索引的查詢開銷反而比全表掃描要大，這時mysql則會放棄索引而選擇進(jìn)行全表掃描贼陶。
? ? ? ?每種數(shù)據(jù)庫都會有查詢優(yōu)化器刃泡，Mysql也不例外。Mysql的查詢優(yōu)化器會對select查詢進(jìn)行優(yōu)化碉怔，所以要善用EXPLAIN來查看執(zhí)行計劃烘贴。要明確的一點是，explain只能解釋select語句撮胧，所以不要試圖執(zhí)行explain update之類的語句桨踪。
? ? ? ?最后，我們結(jié)合上面提到的優(yōu)化原則以及具體到Mysql數(shù)據(jù)庫芹啥，看看有哪些具體的優(yōu)化方法：
- 查詢語句應(yīng)該盡量避免全表掃描锻离，首先應(yīng)該考慮在where子句以及order by子句的字段上建立索引；
- 應(yīng)盡量使用exist和not exist代替in和not in墓怀，因為后者很有可能會導(dǎo)致全表掃描而放棄使用索引汽纠；
- 應(yīng)盡量避免在where子句中對字段進(jìn)行null判斷，因為null判斷會導(dǎo)致全表掃描傀履；
- 應(yīng)盡量避免在where子句中使用or作為連接條件虱朵，同樣會導(dǎo)致全表掃描；
- 應(yīng)盡量避免在where子句中使用!= 或者<>操作符，同樣會導(dǎo)致全表掃描卧秘；
- 使用like "%abc%" 或者like "%abc"同樣也會導(dǎo)致全表掃描呢袱，而like "abc%"會使用索引；
- 在使用union操作符時翅敌，應(yīng)該考慮是否可以使用Union All來代替羞福，因為Union操作符在進(jìn)行結(jié)果合并時，會對產(chǎn)生的結(jié)果進(jìn)行排序蚯涮，刪除重復(fù)記錄治专，在沒有該需要的情況使用Union All，后者僅僅是將結(jié)果合并返回遭顶，能大幅提高性能张峰；
- 應(yīng)盡量避免在where子句中使用表達(dá)式操作符，因為會導(dǎo)致全表掃描棒旗；
- 應(yīng)盡量避免在where子句中對字段使用函數(shù)喘批，同樣會導(dǎo)致全表掃描；
- select語句中盡量避免使用“*”铣揉，因為在sql語句在解析的過程中饶深，會將“*”轉(zhuǎn)換成所有列的列名，而這個工作是通過查詢數(shù)據(jù)字典完成的逛拱，有一定的開銷敌厘；
- where子句中，表連接條件應(yīng)該寫在其他條件之前朽合，因為where子句的解析是從后面向前的俱两，所以盡量把能夠過濾掉多數(shù)記錄的限制條件放在where子句的末尾；
- 若數(shù)據(jù)庫表上存在諸如index(a,b,c)之類的聯(lián)合索引曹步，則where 子句中條件字段的出現(xiàn)順序應(yīng)該與索引字段的出現(xiàn)順序一致宪彩，否則將無法使用索引；
- From子句中表的出現(xiàn)順序同樣會對sql語句的執(zhí)行性能造成影響箭窜，from子句在解析時是從后向前的毯焕，即寫在末尾的表將被優(yōu)先處理，應(yīng)該選擇記錄數(shù)較少的表作為基表放在后面磺樱；
- 盡量使用>= 操作符替代>操作符纳猫；
- OR改寫成IN：OR的效率是n級別，IN的效率是log(n)級別竹捉；
? ? ? ?? ? ? ?? ? ? ?? ? ? ?? ? ? ?? ? ? ?? ? ? ?? ? ? ?? ? ? ?? ? ? ?? ? ? ?2019年9月4日星期三 于團(tuán)結(jié)湖瑞辰國際中心
