那些年我們一起優化的SQL

一、背景

随着業務不斷疊代，系統中出現了較多的SQL慢查。慢查雖不緻命，但會讓商家感覺到系統較慢，影響使用體驗。在進行慢查優化過程中，我們積累了一些經驗。本文将基于我們的實戰經曆，講解工作中比較常見的慢查原因，以及如何去優化。

本文講解基于MySQL 5.7。

二、慢查優化

本節主要針對常見的慢查進行分類，講解怎麼去優化。

2.1 建立索引的正确姿勢

資料量較大的時候，如果沒有索引，那麼資料庫隻能全表一行一行的周遊判斷資料，是以優化SQL的時候，第一步要做的就是确定有沒有合适的可用的索引。在業務本身比較複雜的情況下，一個表會涉及各種各樣的查詢方式，是以我們需要建立各種各樣的索引去提高查詢。然而索引數量過多又會影響增删改的效率，并且也會占用更多額外的空間去存儲索引，是以我們要懂得怎麼去正确地建立索引，避免索引濫用。

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

選擇性過低，即通過該字段隻能過濾掉少部分的資料，是沒必要建立索引的，因為如果該資料隻是占小部分，即使沒有索引直接查詢資料表也不用過多的周遊即可找到目标資料，沒有必要基于索引查詢。

SQL：

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

索引：

index (col_a,col_b)

col_b為邏輯删除字段，包含0未删除和1已删除，資料庫中的值隻有很少量部分是邏輯删除的。但是在業務中我們一般都隻查未删除的，那麼這種情況col_b是完全不必要在索引中的，可以把col_b從組合索引中去掉。

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

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

index(col_a,col_b,col_c)

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

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

而如果col_c選擇性特别高，經過col_c過濾後隻剩下極少的資料甚至一條資料，單獨建立col_c索引就可以。

需要注意的是這個規則特别提到了抛開業務本身需要，比如如果col_a選擇性比col_b高一點相差不大，但是col_b在業務場景中通用性更強，那麼col_b放在前面更合适,可以減少建立的索引。

2.1.3 盡量使用覆寫索引

SELECT sum(col_c) FROM my_table where col_a=1 and col_b=1

index(col_a,col_b)

如果col_a和col_b過濾完後還有大量資料，那麼建議建一個 index(col_a,col_b , col_c) 索引，否則MySQL需要通過大量回表去查詢col_c的資料再去求和。

2.1.4 小結

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

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

3、除了業務需求上的考慮，盡量将選擇性高地索引字段前置。

4、在經過索引過濾後資料量依舊很大的情況下可以考慮通過覆寫索引優化。

2.2 使用索引的正确姿勢

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

需要注意的是，索引失效這裡指的是沒有利用到索引的二分查詢進行資料過濾。因為存在ICP，是以會存在觸發了失效場景執行計劃還是顯示使用了索引的情況。

2.2.1 最左比對截斷

select * from my_table where col_b=1  

select * from my_table order by col_b

索引:

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

補充說明:

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

比如：

select * from my_table order by col_a,col_b

col_a和col_b都可以走索引。

2、如果col_b是作為過濾條件，則col_b前面的字段都應該在過濾條件中。

select * from my_table where col_b=1 order by col_a

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

2.2.2 隐式轉換

字段類型：

col_a(VARCHAR)

col_b(DATETIME)

index1(col_a)

index2(col_b)

select * from my_table where col_a=1  

select * from my_table where col_b=1603296000000

失效原因

字段類型和查詢資料的值類型不一緻，會導緻字段上的索引失效。

col_a是字元類型，使用了數字類型進行查詢。
col_b是datetime類型，針對datetime/date/time類型，MySQL增删查改都要基于字元串形式日期去處理，否則MySQL就需要額外進行轉換。（雖然底層儲存的是數字類型，但是并不是存儲時間戳，底層是處理是統一将外部傳入的字元串進行轉換，比如是date類型通過将 “2021-12-01” 字元串轉數字 20211201 這種形式去存儲）。

2.2.3 in + order by 導緻排序失效

select * from my_table where col_a in (1,2) order by col_b

解決方式：

如果col_a的過濾性不高，在組合索引中可以通過将col_b字段前置，将col_a移動到組合索引後面，隻用于避免或減少回表。
如果col_a的過濾性高，過濾後的資料相對較少，則維持目前的索引即可，剩餘不多的資料通過filesort進行排序。
如果存在大量資料，并且經過col_b過濾後還是存在大量資料，建議基于别的資料存儲實作，比如Elasticsearch。

另外SQL建議調整為隻查詢id（或者其他已經在索引中的字段），再根據id去查對應的資料。可以促使SQL走覆寫索引進一步優化、也可以促使MySQL底層在進行filesort使用更優的排序算法。

2.2.4 範圍查詢阻斷組合索引

select * from table where col_a >'2021-12-01' and col_b=10

可以調整下索引順序，col_a放在最後面。index(col_b,col_a)

2.2.5 字尾比對不能走索引

select * from table where col_a=1 and col_b like '%name%'

以上SQL會導緻索引失效。字首比對比如name%是可以走索引的，但是字尾比對比如%name會導緻沒辦法基于索引樹進行二分查找。如果需要進行字尾比對，資料量較大建議基于Elasticsearch實作。

2.2.6 or查詢導緻失效

select * from table where col_a=1 or col_b=''

or查詢會導緻索引失效，可以将col_a和col_b分别建立索引，利用Mysql的index merge(索引合并)進行優化。本質上是分别兩個字段分别走各自索引查出對應的資料，再将資料進行合并。

2.2.7 使用函數查詢或運算

select * from table where col_a=1 and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(col_b);  

select * from table where col_a=1 and col_b+1=10

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

select * from table where col_a=1 and col_b not in (1,2)  

select * from table where col_a=1 and col_b != 1

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

選擇性過低會導緻索引失效。由于通過二級索引查詢後還有回表查詢的開銷，如果通過該字段隻能過濾少量的資料，整體上還不如直接查詢資料表的性能，則MySQL會放棄這個索引，直接使用全表掃描。底層會根據表大小、IO塊大小、行數等資訊進行評估決定。

index(col_a)

select * from table where col_a>'2017-10-22'

2.2.10 asc和desc混用

select * from my_table where col_a=1 order by col_b desc,col_c asc

desc 和asc混用時會導緻索引失效，不建議混用。

2.2.11 小結

根據以上例子，總結幾個索引失效的場景：

組合索引左比對原則
發生隐式轉換
組合索引，in + order by in會阻斷排序用索引
範圍查詢會阻斷組合索引,索引涉及到範圍查詢的索引字段要放在組合索引的最後面。
前模糊比對導緻索引失效
or查詢，查詢條件部分有索引，部分無索引，導緻索引失效。
查詢條件使用了函數運算、四則運算等。
使用了!=、not in
選擇性過低
asc和desc混用

2.3 編寫SQL的正确姿勢

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

2.3.1 深分頁

index(col_c)

select * from my_table where col_c=1 limit 1000,10

為什麼深分頁會效率變差，首先我們要了解一下分頁的原理。

MySQL limit不會傳遞到引擎層，隻是在服務層進行資料過濾。查詢資料時，先由引擎層通過索引過濾出一批資料（索引過濾），然後服務層進行二次過濾（非索引過濾）。

引擎層過濾後會将擷取的資料暫存，服務層一條一條資料擷取，擷取時引擎層回表獲得完成資料交給服務層，服務層判斷是否比對查詢條件（非索引過濾），如果比對會繼續判斷是否滿足limit限制的資料範圍，符合并且範圍内的資料都查完了才傳回。

是以如果深分頁，會導緻大量的無效回表（前1000條進行了回表，實際上隻需要1000-1010的資料），是以優化的方式就是避免深分頁帶來的額外回表。

# 避免深分頁

select * from my_table where id>上次查詢的資料id值 limit 100




# 延遲關聯 避免大量回表

SELECT * FROM my_table t1,(select id from my_table where col_c=1 limit 1000,100) t2 where t1.id=t2.id

避免深分頁：我們可以改成id過濾，每次都隻查詢大于上次查詢的資料id。這樣每次隻查詢100條，回表也隻需要回表100條。

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

延遲關聯：通過延遲關聯，通過編寫完全基于索引查詢資料的SQL，再根據id查詢詳細的字段資料。

2.3.2 order by id

select * from table where col_a=1 and col_b=2 order by id

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

但是在本場景中，col_b截斷了索引，導緻SQL沒法利用id進行索引排序。而主鍵索引的權重會比較高，可能會導緻MySQL沒有正确選擇索引，進而選擇了可能性能更差的主鍵索引去做排序，查詢條件通過周遊掃描資料。

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

三、慢查分析

在掌握了SQL優化的理論知識之後，我們怎麼驗證編寫的SQL是否有按照預期使用了比較合适的索引？這就需要學會分析SQL的執行情況。

執行計劃：我們可以通過explain關鍵字檢視SQL的執行計劃，通過執行計劃分析SQL的執行情況。

執行計劃字段描述：

extra字段常見值：

using index condition補充說明：

using index condition表示使用了ICP（Index Condition Pushdown索引下推），ICP是MySQL 5.6之後新增的特性，部分字段因為某些情況無法走索引查詢，則會展示using where（在server層進行資料過濾），ICP是在存儲引擎層進行資料過濾，而不是在服務層過濾，利用索引現有的資料過濾調一部分資料。

using where 和 using index condition的差別：

為什麼需要ICP機制：

select * from my_table where col_a="value" and col_b like "%value%"

如果沒有using index condtion，col_a會走索引查詢，比對到對應的資料後，回表查出剩餘字段資訊，再去比對col_b。假設col_a過濾後還有10000條資料，而通過col_b過濾後隻會剩餘1000條資料，則有9000條的資料是沒必要的回表。

本質上索引樹上是包含col_b字段的，隻是col_b不能利用索引樹二分查找特性（因為使用了前模糊比對），但是可以利用索引上現有的資料進行周遊，減少無效回表。有了ICP後，基于索引就可以過濾col_a和col_b字段，過濾後隻會剩下1000條資料，減少了大量的回表操作。

小結：

通過執行計劃我們可以分析出SQL最終使用了什麼索引，對索引的使用是處于什麼情況，進而可以得出還有沒有優化空間。

四、總結

我們要有品質意識，做好預防而不是做補救，SQL優化在開發階段就要考慮清楚，而不是等上線後出現慢查了才去優化。

做好SQL優化可以記住一個口訣，有用高。SQL要有索引(建立正确的索引)，索引要可用（避免索引失效），最後要考慮高效（覆寫索引、索引的選擇性）。

原文連結：https://mp.weixin.qq.com/s?__biz=MzAxOTY5MDMxNA==&mid=2455764220&idx=1&sn=796061f8797e823be8dfcb1fa2835796&utm_source=tuicool&utm_medium=referral