大家好，我是藍胖子,前段時間mysql經常碰到慢查詢報警，我們線上的慢sql閾值是1s，出現報警的表資料有 7000多萬，經常出現報警的是一個group by的count查詢，於是便開始著手最佳化這塊，遂有此篇，記錄下自己最佳化過程中的心得。
最佳化慢sql前，肯定是要懂sql的查詢邏輯，所以我先介紹下group by 語句的執行邏輯。
group by 執行邏輯
環境準備
拿下面這張表舉例，這是一張記錄資料夾id和使用者id關聯關係的表。其中dir_id代表資料夾id，uid代表使用者id，還有個唯一索引是uniq_dir_id。
create table t_dir_user
(
id bigint unsigned auto_increment
primary key,
dir_id bigint default 0 not null,
uid bigint default 0 not null,
constraint uniq_dir_id
unique (dir_id, uid)
)
表一共有7000多萬的資料。下面開始介紹使用group by 語句時sql執行的原理。
沒有用到索引的情況
先說下結論，group by後面的列如果不能使用上索引，那麼則會產生臨時表且很可能產生檔案排序的情況。
group by 語句有分 使用到索引和沒有使用到索引的情況，先看看沒有使用到索引的情況。假如我想查詢在一些資料夾範圍內，使用者關注的資料夾數量。那我可以寫出下面這樣的sql。
explain select count(1), uid
from t_dir_user
where dir_id in (1803620,4368250,2890924,2033475,3038030)
group by uid;
使用explain分析時，會發現這個查詢是使用到索引的，且Extra 那一欄會出現下面的資訊。
Using index condition; Using temporary; Using filesort
上述資訊代表了查詢是使用到了索引來做where條件查詢，並且使用到了臨時表和檔案排序。
注意?? ❗️ 臨時表和檔案排序這兩個操作都是效能不佳的操作，寫sql時應儘量避免。
現在來對這種情況做更加具體的分析，在上述例子中，mysql相當於建立了一張臨時表，具體是記憶體的臨時表還是磁碟的臨時表要看臨時表資料量大小，記憶體放不下會放到磁碟上。
臨時表一列存放需要分組的值，上述案例中就是 uid，一列存放統計出來的count值，mysql會一遍掃描uniq_dir_id索引，一邊向這個臨時表中寫入資料或更新count值，當索引掃描完成後，再將填滿資料的臨時表做下排序然後返回給客戶端。注意這個排序的行為，如果需要排序的資料量很大則會產生檔案排序，否則則是記憶體排序。
使用到索引的情況
再來看看group by 後跟的列能使用到索引的情況。
先說下結論，使用到索引的時候，mysql會使用內建的聚合函式來進行操作，而不是建立臨時表。並且節省了排序這一步，這種方式會更高效。
還是拿上面t_dir_user 這張表舉例，這次我們要查一定資料夾範圍內，一個資料夾與多少個使用者關聯。我們可以這樣寫sql，
explain select count(1), dir_id
from t_dir_user
where dir_id in (1803620,4368250,2890924,2033475,3038030)
group by dir_id;
此時explain分析後你會發現，雖然使用的是相同的索引，但是Extra這一欄的資訊已經變了，Extra資訊如下，
Using index condition; Using aggregate; Using index
Using aggregate 這條sql會使用mysql內建的聚合函式進行分組聚合的操作。
我們來具體分析下，因為group by此次是按dir_id資料夾id進行分組的，而dir_id剛好可以用上dir_id和uid建立的聯合索引uniq_dir_id，並且索引是有序的，這樣mysql在掃描索引的時候，就是一個資料夾id的索引資料掃描完成後，再次去掃描下一個資料夾id的索引資料，掃描的同時會對該資料夾id的count值進行累加。 這樣一個資料夾的索引資料掃描完成後剛好就能知道這個資料夾id關聯的uid的count值，並將這個值傳送給客戶端。
所以，整個過程其實是一邊掃描索引對特定資料夾id的count值進行累加，一邊將累加後的結果返回給客戶端的過程。
注意??，mysql返回給客戶端的結果並不是全部查詢出來後才返回給客戶端，而是可以邊查邊返回的。
整個過程是沒有用上臨時表的。這樣的查詢會更加高效。
使用索引的情況下如何最佳化千萬級count group by查詢
在瞭解完group by語句的執行邏輯後，我對線上的sql進行了分析，發現線上的sql的group by列是屬於已經使用了索引的情況。那為啥還會慢呢？
因為即使是使用了索引，group by的過程還是會有掃描索引和進行累加的過程，由於掃描的資料量太大了，最終導致了sql整體耗時還是很慢，超過了1s的閾值。
既然如此，那就換一種最佳化思路，這也是對大資料量的聚合統計的一種常用手段。 業務大部分時候都是讀多寫少的，可以建立一張新表專門用於記錄對應的資料夾管理的使用者數，每次關聯關係發生變化時，同時再更新下這張統計表的數量即可。而業務在查詢數量時，則直接查統計表中的資料。 這種最佳化非常適合大資料量的統計。
除此以外，甚至還可以使用elasticsearch 這型別資料庫存資料，在這個案例裡，相當於就把t_dir_user整張表的資料同步到elasticsearch中，並且做mysql到elasticsearch叢集資料的實時同步機制，這樣以後在查詢對應資料夾的關聯人數時，可以直接在elasticsearch進行查詢。elasticsearch會對每個欄位建立倒排索引。由於倒排索引中會儲存該索引的記錄條數，在這個案例中就是dir_id對應的記錄條數，所以在用elasticsearch進行dir_id的分組count查詢時是相當快的。
我們線上已經有elasticsearch同步部分mysql表的機制了，基於此，我選擇了方案2，直接在之前同步表中新增了t_dir_user這張表，並且修改了業務查詢資料夾下關聯人數的邏輯，改由直接查詢elasticsearch。
其實，你可以發現由於elasticsearch的倒排索引內直接記錄了數量資訊，這個和由mysql建立新的統計表記錄數量，原理其實是一致的，就是將高頻的讀count查詢改由低頻的更新操作。