大家好，我是藍胖子,前段時間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查詢改由低頻的更新操作。

總結

以上是生活随笔為你收集整理的mysql group by 执行原理及千万级别count 查询优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。