那些年我們一起優(yōu)化的SQL

隨著業(yè)務(wù)不斷迭代，系統(tǒng)中出現(xiàn)了較多的SQL慢查。慢查雖不致命，但會讓商家感知到系統(tǒng)較慢，影響使用體驗。在進行慢查優(yōu)化過程中，我們積累了一些經(jīng)驗。本文將基于我們的實戰(zhàn)經(jīng)歷，講解工作中比較常見的慢查原因，以及如何去優(yōu)化。

本文講解基于MySQL 5.7。

本節(jié)主要針對常見的慢查進行分類，講解怎么去優(yōu)化。

2.1 建立索引的正確姿勢

數(shù)據(jù)量較大的時候，如果沒有索引，那么數(shù)據(jù)庫只能全表一行一行的遍歷判斷數(shù)據(jù)，因此優(yōu)化SQL的時候，第一步要做的就是確定有沒有合適的可用的索引。在業(yè)務(wù)本身比較復(fù)雜的情況下，一個表會涉及各種各樣的查詢方式，因此我們需要建立各種各樣的索引去提高查詢。然而索引數(shù)量過多又會影響增刪改的效率，并且也會占用更多額外的空間去存儲索引，因此我們要懂得怎么去正確的建立索引，避免索引濫用。

2.1.1 索引不要包含選擇性過低字段

選擇性過低，即通過該字段只能過濾掉少部分的數(shù)據(jù)，是沒必要建立索引的，因為如果該數(shù)據(jù)只是占小部分，即使沒有索引直接查詢數(shù)據(jù)表也不用過多的遍歷即可找到目標(biāo)數(shù)據(jù)，沒有必要基于索引查詢。

SQL：

select * from my_table where col_a=1 and col_b=1

索引：
index (col_a,col_b)

col_b為邏輯刪除字段，包含0未刪除和1已刪除，數(shù)據(jù)庫中的值只有很少量部分是邏輯刪除的。但是在業(yè)務(wù)中我們一般都只查未刪除的，那么這種情況col_b是完全不必要在索引中的，可以把col_b從組合索引中去掉。

2.1.2 選擇性高的字段前置或者單獨建立索引

SQL：

索引：
index(col_a,col_b,col_c)

假設(shè)選擇性col_c>col_b>col_a，拋開業(yè)務(wù)本身需要，組合索引建立的順序盡可能建為index(col_c,col_b,col_a)。

原因是組合索引底層的存儲先按照第一個進行排序，第一個字段相同再按照第二字段排序，如果選擇性低的字段放在前面，因此選擇性高的字段放前面相對而言IO的次數(shù)可能會減少一些。比如基于第一個字段過濾完會有10000條數(shù)據(jù)，基于第二個字段過濾完只有100條數(shù)據(jù)，如果先查第一個字段則需要在10000條數(shù)據(jù)的基礎(chǔ)上再進行過濾查詢，而基于第二字段過濾完只需要基于100條數(shù)據(jù)進行過濾查詢即可。

而如果col_c選擇性特別高，經(jīng)過col_c過濾后只剩下極少的數(shù)據(jù)甚至一條數(shù)據(jù)，單獨建立col_c索引就可以。

需要注意的是這個規(guī)則特別提到了拋開業(yè)務(wù)本身需要，比如如果col_a選擇性比col_b高一點相差不大，但是col_b在業(yè)務(wù)場景中通用性更強，那么col_b放在前面更合適,可以減少創(chuàng)建的索引。

2.1.3 盡量使用覆蓋索引

SQL：

索引：
index(col_a,col_b)

如果col_a和col_b過濾完后還有大量數(shù)據(jù)，那么建議建一個index(col_a,col_b,col_c)索引，否則MySQL需要通過大量回表去查詢col_c的數(shù)據(jù)再去求和。

2.1.4 小結(jié)

1、選擇性低的字段不用建立索引。

2、具有唯一性或者高選擇性的字段無需與其他字段建立組合索引。

3、除了業(yè)務(wù)需求上的考慮，盡量將選擇性高的索引字段前置。

4、在經(jīng)過索引過濾后數(shù)據(jù)量依舊很大的情況下可以考慮通過覆蓋索引優(yōu)化。

2.2 使用索引的正確姿勢

除了SQL本身沒有適用的索引，有了相關(guān)的索引但是對應(yīng)的索引沒有生效是比較常見的情況，以下列舉一些常見的失效場景，在日常的開發(fā)中，我們要盡量避免。

需要注意的是，索引失效這里指的是沒有利用到索引的二分查詢進行數(shù)據(jù)過濾。因為存在ICP，所以會存在觸發(fā)了失效場景執(zhí)行計劃還是顯示使用了索引的情況。

2.2.1 最左匹配截斷

SQL：

索引:
index(col_a,col_b)

組合索引的匹配規(guī)則是從左往右匹配，無論是作為過濾條件還是排序條件都要遵循這個原則。如果要使用col_b字段走索引，查詢條件則必須要攜帶col_a字段。

補充說明:
1、col_b作為排序字段如果要走索引，只要保證組合索引中col_b前面的字段都可以包含在過濾條件或者排序條件中即可，也不需要保證col_b作為組合索引中的最后一個字段。

比如：

col_a和col_b都走不了索引，因為col_a在組合索引左邊，但是col_a不在查詢條件中。

2.2.2 隱式轉(zhuǎn)換

字段類型：
col_a(VARCHAR)
col_b(DATETIME)

索引：
index1(col_a)
index2(col_b)

SQL：

失效原因
字段類型和查詢數(shù)據(jù)的值類型不一致，會導(dǎo)致字段上的索引失效。

• col_a是字符類型，使用了數(shù)字類型進行查詢。

• col_b是datetime類型，針對datetime/date/time類型，MySQL增刪查改都要基于字符串形式日期去處理，否則MySQL就需要額外進行轉(zhuǎn)換。（雖然底層儲存的是數(shù)字類型，但是并不是存儲時間戳，底層是處理是統(tǒng)一將外部傳入的字符串進行轉(zhuǎn)換，比如是date類型通過將 “2021-12-01” 字符串轉(zhuǎn)數(shù)字 20211201 這種形式去存儲）。

2.2.3 in + order by 導(dǎo)致排序失效

索引：
index(col_a,col_b)

SQL：

解決方式：

• 如果col_a的過濾性不高，在組合索引中可以通過將col_b字段前置，將col_a移動到組合索引后面，只用于避免或減少回表。

• 如果col_a的過濾性高，過濾后的數(shù)據(jù)相對較少，則維持當(dāng)前的索引即可，剩余不多的數(shù)據(jù)通過filesort進行排序。

• 如果存在大量數(shù)據(jù)，并且經(jīng)過col_b過濾后還是存在大量數(shù)據(jù)，建議基于別的數(shù)據(jù)存儲實現(xiàn)，比如Elasticsearch。

另外SQL建議調(diào)整為只查詢id（或者其他已經(jīng)在索引中的字段），再根據(jù)id去查對應(yīng)的數(shù)據(jù)?？梢源偈筍QL走覆蓋索引進一步優(yōu)化、也可以促使MySQL底層在進行filesort使用更優(yōu)的排序算法。

2.2.4 范圍查詢阻斷組合索引

索引：
index(col_a,col_b)

SQL：

解決方式：
可以調(diào)整下索引順序，col_a放在最后面。index(col_b,col_a)

2.2.5 后綴匹配不能走索引

索引:
index(col_a,col_b)

SQL：

以上SQL會導(dǎo)致索引失效。前綴匹配比如name%是可以走索引的，但是后綴匹配比如%name會導(dǎo)致沒辦法基于索引樹進行二分查找。如果需要進行后綴匹配，數(shù)據(jù)量較大建議基于Elasticsearch實現(xiàn)。

2.2.6 or查詢導(dǎo)致失效

索引：
index(col_a,col_b)

SQL：

or查詢會導(dǎo)致索引失效，可以將col_a和col_b分別建立索引，利用Mysql的index merge(索引合并)進行優(yōu)化。本質(zhì)上是分別兩個字段分別走各自索引查出對應(yīng)的數(shù)據(jù)，再將數(shù)據(jù)進行合并。

2.2.7 使用函數(shù)查詢或運算

索引：
index(col_a,col_b)

SQL：

2.2.8 不等于、不包含(只用到ICP)

索引：
index(col_a,col_b,col_c)

SQL：

2.2.9 選擇性過低，直接走全表

選擇性過低會導(dǎo)致索引失效。由于通過二級索引查詢后還有回表查詢的開銷，如果通過該字段只能過濾少量的數(shù)據(jù)，整體上還不如直接查詢數(shù)據(jù)表的性能，則MySQL會放棄這個索引，直接使用全表掃描。底層會根據(jù)表大小、IO塊大小、行數(shù)等信息進行評估決定。

索引：

index(col_a)

SQL：

2.2.10 asc和desc混用

索引：
index(col_a,col_b,col_c)

SQL：

desc 和asc混用時會導(dǎo)致索引失效，不建議混用。

2.2.11 小結(jié)

根據(jù)以上例子，總結(jié)幾個索引失效的場景：

1. 組合索引左匹配原則

2. 發(fā)生隱式轉(zhuǎn)換

3. 組合索引，in + order by in會阻斷排序用索引

4. 范圍查詢會阻斷組合索引,索引涉及到范圍查詢的索引字段要放在組合索引的最后面。

5. 前模糊匹配導(dǎo)致索引失效

6. or查詢，查詢條件部分有索引，部分無索引，導(dǎo)致索引失效。

7. 查詢條件使用了函數(shù)運算、四則運算等。

8. 使用了!=、not in

9. 選擇性過低

10. asc和desc混用

2.3 編寫SQL的正確姿勢

懂得怎么建立索引，也懂得了怎么避免索引失效，但是有些場景即使索引走對了，也會慢查，這時我們要考慮優(yōu)化我們SQL寫法。

2.3.1 深分頁

索引：

index(col_c)

SQL：

覆蓋索引：?如果業(yè)務(wù)需要的字段比較少，可以通過保證SQL查詢的字段和查詢條件都在索引上，避免回表。

延遲關(guān)聯(lián)：?通過延遲關(guān)聯(lián)，通過編寫完全基于索引查詢數(shù)據(jù)的SQL，再根據(jù)id查詢詳細的字段數(shù)據(jù)。

2.3.2 order by id

索引：
index(col_a)

SQL：

MySQL INNODB二級索引最終葉子結(jié)點引用的都是主鍵id，因此我們可以利用這個點去使用id排序。

但是在本場景中，col_b截斷了索引，導(dǎo)致SQL沒法利用id進行索引排序。而主鍵索引的權(quán)重會比較高，可能會導(dǎo)致MySQL沒有正確選擇索引，從而選擇了可能性能更差的主鍵索引去做排序，查詢條件通過遍歷掃描數(shù)據(jù)。

因此在不能保證id排序可以走索引的情況下，建議改用其他字段排序。如果查詢結(jié)果集確定會比較少排序字段可以不在索引上，如果結(jié)果集較大還是要把排序字段加到索引中。

在掌握了SQL優(yōu)化的理論知識之后，我們怎么驗證編寫的SQL是否有按照預(yù)期使用了比較合適的索引？這就需要學(xué)會分析SQL的執(zhí)行情況。

執(zhí)行計劃：我們可以通過explain關(guān)鍵字查看SQL的執(zhí)行計劃，通過執(zhí)行計劃分析SQL的執(zhí)行情況。

執(zhí)行計劃字段描述：

extra字段常見值：

using index condition補充說明：
using index condition表示使用了ICP（Index Condition Pushdown索引下推），ICP是MySQL 5.6之后新增的特性，部分字段因為某些情況無法走索引查詢，則會展示using where（在server層進行數(shù)據(jù)過濾），ICP是在存儲引擎層進行數(shù)據(jù)過濾，而不是在服務(wù)層過濾，利用索引現(xiàn)有的數(shù)據(jù)過濾調(diào)一部分數(shù)據(jù)。

using where 和 using index condition的區(qū)別：

為什么需要ICP機制：

索引:

index(col_a,col_b)

SQL：

如果沒有using index condtion，col_a會走索引查詢，匹配到對應(yīng)的數(shù)據(jù)后，回表查出剩余字段信息，再去匹配col_b。假設(shè)col_a過濾后還有10000條數(shù)據(jù)，而通過col_b過濾后只會剩余1000條數(shù)據(jù)，則有9000條的數(shù)據(jù)是沒必要的回表。

本質(zhì)上索引樹上是包含col_b字段的，只是col_b不能利用索引樹二分查找特性（因為使用了前模糊匹配），但是可以利用索引上現(xiàn)有的數(shù)據(jù)進行遍歷，減少無效回表。有了ICP后，基于索引就可以過濾col_a和col_b字段，過濾后只會剩下1000條數(shù)據(jù)，減少了大量的回表操作。

小結(jié)：
通過執(zhí)行計劃我們可以分析出SQL最終使用了什么索引，對索引的使用是處于什么情況，進而可以得出還有沒有優(yōu)化空間。