目錄
一、group by
group by 優化方法 — 索引
松散索引掃描（Loose Index Scan）
為什麼松散索引掃描的效率會很高?
緊湊索引掃描（Tight Index Scan）
group by 優化方法 — 直接排序
二、group by 與 distinct
三、排序不一緻問題
一、group by
當我們執行 group by 操作在沒有合适的索引可用的時候，通常先掃描整個表提取資料并建立一個臨時表，然後按照 group by 指定的列進行排序。在這個臨時表裡面，對于每一個 group 的資料行來說是連續在一起的。完成排序之後，就可以發現所有的 groups，并可以執行聚集函數（aggregate function）。可以看到，在沒有使用索引的時候，需要建立臨時表和排序。在執行計劃中通常可以看到“Using temporary; Using filesort”。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | create table t1(id int primary key, a int, b int, index(a)); delimiter ;; create procedure idata() begin declare i int; set i=1; while(i<=1000)do insert into t1 values(i, i, i); set i=i+1; end while; end;; delimiter ; call idata(); |
一個常見的使用臨時表的例子是 group by，我們來看一下這個語句：
| 1 | select id%10 as m, count(*) as c from t1 group by m; |
這個語句的邏輯是把表 t1 裡的資料，按照 id%10 進行分組統計，并按照 m 的結果排序後輸出。它的 explain 結果如下：
| 1 2 3 4 5 6 7 | mysql> explain select id%10 as m, count(*) as c from t1 group by m; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+ | 1 | SIMPLE | t1 | NULL | index | PRIMARY,a | a | 5 | NULL | 1000 | 100.00 | Using index; Using temporary; Using filesort | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+ 1 row in set, 1 warning (0.01 sec) |
在 Extra 字段裡面，我們可以看到三個資訊：
- Using index，表示這個語句使用了覆寫索引，選擇了索引 a，不需要回表；
- Using temporary，表示使用了臨時表；
- Using filesort，表示需要排序。
這個語句的執行流程是這樣的：
1. 建立記憶體臨時表，表裡有兩個字段 m 和 c，主鍵是 m；
2. 掃描表 t1 的索引 a，依次取出葉子節點上的 id 值，計算 id%10 的結果，記為 x；
3. 如果臨時表中沒有主鍵為 x 的行，就插入一個記錄 (x,1); 如果表中有主鍵為 x 的行，就将 x 這一行的 c 值加 1；
4. 周遊完成後，再根據字段 m 做排序，得到結果集傳回給用戶端。
這個流程的流程圖如下：
接下來，我們再看一下這條語句的執行結果：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> select id%10 as m, count(*) as c from t1 group by m; +------+-----+ | m | c | +------+-----+ | 0 | 100 | | 1 | 100 | | 2 | 100 | | 3 | 100 | | 4 | 100 | | 5 | 100 | | 6 | 100 | | 7 | 100 | | 8 | 100 | | 9 | 100 | +------+-----+ 10 rows in set (0.01 sec) |
如果你的需求并不需要對結果進行排序，那你可以在 SQL 語句末尾增加 order by null，也就是改成：
| 1 | select id%10 as m, count(*) as c from t1 group by m order by null; |
這樣就跳過了最後排序的階段，直接從臨時表中取資料傳回。傳回結果如下：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> select id%10 as m, count(*) as c from t1 group by m order by null; +------+-----+ | m | c | +------+-----+ | 1 | 100 | | 2 | 100 | | 3 | 100 | | 4 | 100 | | 5 | 100 | | 6 | 100 | | 7 | 100 | | 8 | 100 | | 9 | 100 | | 0 | 100 | +------+-----+ 10 rows in set (0.00 sec) |
由于表 t1 中的 id 值是從 1 開始的，是以傳回的結果集中第一行是 id=1；掃描到 id=10 的時候才插入 m=0 這一行，是以結果集裡最後一行才是 m=0。
這個例子裡由于臨時表隻有 10 行，記憶體可以放得下，是以全程隻使用了記憶體臨時表。但是，記憶體臨時表的大小是有限制的，參數 tmp_table_size 就是控制這個記憶體大小的，預設是 16M。
如果我執行下面這個語句序列：
| 1 2 | set tmp_table_size=1024; select id%100 as m, count(*) as c from t1 group by m order by null limit 10; |
把記憶體臨時表的大小限制為最大 1024 位元組，并把語句改成 id % 100，這樣傳回結果裡有 100 行資料。但是，這時的記憶體臨時表大小不夠存下這 100 行資料，也就是說，執行過程中會發現記憶體臨時表大小到達了上限（1024 位元組）。
那麼，當記憶體放不下時，這時候就會把記憶體臨時表轉成磁盤臨時表，磁盤臨時表預設使用的引擎是 InnoDB。這時，傳回結果如下：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> select id%100 as m, count(*) as c from t1 group by m order by null limit 10; +------+----+ | m | c | +------+----+ | 0 | 10 | | 1 | 10 | | 2 | 10 | | 3 | 10 | | 4 | 10 | | 5 | 10 | | 6 | 10 | | 7 | 10 | | 8 | 10 | | 9 | 10 | +------+----+ 10 rows in set (0.01 sec) |
如果這個表 t1 的資料量很大，很可能這個查詢需要的磁盤臨時表就會占用大量的磁盤空間。
可以看出，相同的語句，由于調整 tmp_table_size 參數大小，查詢結果排序方式卻不同。這就是因為第一個查詢使用的是記憶體臨時表，上面已經提到了，是按照表 t1 的索引 a 順序取出資料，模 10 得 0 的 id 是最後一行；第二個查詢使用的是硬碟臨時表，預設用 InnoDB 的引擎，主鍵是 id%10，是以存入硬碟後再按主鍵樹順序取出，0 就排到第一行了，InnoDB 表是順序存儲的。
group by 優化方法 — 索引
可以看到，不論是使用記憶體臨時表還是磁盤臨時表，group by 邏輯都需要構造一個帶唯一索引的表，執行代價都是比較高的。如果表的資料量比較大，上面這個 group by 語句執行起來就會很慢，我們有什麼優化的方法呢？
要解決 group by 語句的優化問題，你可以先想一下這個問題：執行 group by 語句為什麼需要臨時表？ group by 的語義邏輯，是統計不同的值出現的個數。但是，由于每一行的 id%100 的結果是無序的，是以我們就需要有一個臨時表，來記錄并統計結果。 那麼，如果掃描過程中可以保證出現的資料是有序的，是不是就簡單了呢？
确實是這樣，如果可以確定輸入的資料是有序的，那麼計算 group by 的時候，就隻需要從左到右，順序掃描，依次累加。比如資料結構（0, 0, 1, 1, 2, 2），那麼這個過程如下：
- 當碰到第一個 1 的時候，已經知道累積了 X 個 0，結果集裡的第一行就是 (0,X);
- 當碰到第一個 2 的時候，已經知道累積了 Y 個 1，結果集裡的第二行就是 (1,Y);
按照這個邏輯執行的話，掃描到整個輸入的資料結束，就可以拿到 group by 的結果，不需要臨時表，也不需要再額外排序。也就是說，如果語句執行過程可以一邊讀資料，一邊直接得到結果，是不需要額外記憶體的，否則就需要額外的記憶體，來儲存中間結果。
是以，我們自然想到索引。MySQL 建立的 B+Tree 索引原生就是有序的，如果通過讀取索引就完成 group by 操作，那麼就可避免建立臨時表和排序。是以使用索引進行 group by 的最重要的前提條件是所有 group by 的參照列（分組依據的列）來自于同一個索引，且索引按照順序存儲所有的 key（即BTREE index，而HASH index沒有順序的概念）。
MySQL 有兩種索引掃描方式完成 group by 操作，分别是松散索引掃描和緊湊索引掃描以及臨時表實作 group by。在松散索引掃描方式下，分組操作和範圍預測（如果有的話）一起執行完成的。在緊湊索引掃描方式下，先對索引執行範圍掃描（range scan），再對結果元組進行分組。
松散索引掃描（Loose Index Scan）
何謂松散索引掃描實作 group by 呢？實際上就是當 MySQL 利用索引掃描來實作 group by 的時候，并不需要掃描所有滿足條件的索引鍵即可完成操作得出結果。
松散索引掃描僅考慮索引中的一部分，當查詢中沒有 where 條件的時候，松散索引掃描讀取的索引元組的個數和 groups 的數量相同，如果 where 條件包含範圍查詢，松散索引掃描查找每個 group 中第一個滿足範圍條件的鍵，并再次讀取盡可能少的鍵。松散索引掃描隻需要讀取很少量的資料就可以完成 group by 操作，因而執行效率非常高。
使用松散索引掃描需要滿足以下條件：
1. 查詢在單一表上。
2. group by 指定的所有列是索引的一個最左字首，并且沒有其它的列。比如表 t1(c1,c2,c3,c4）上建立了索引 (c1,c2,c3）。如果查詢包含 “group by c1,c2”，那麼可以使用松散索引掃描。但是 “group by c2,c3” (不是索引最左字首) 和 “group by c1,c2,c4” (c4字段不在索引中)無法使用。
3. 如果在選擇清單 select list 中存在聚集函數，隻能使用 min() 和 max() 兩個聚集函數，并且指定的是同一列（如果 min() 和 max() 同時存在），這一列必須在索引中，且緊跟着 group by 指定的列。比如 select t1,t2,min(t3),max(t3) from t1 group by c1,c2。這源于索引的有序排序，優化器意識到 min/max 位于最左/右塊，進而避免範圍掃描。
4. 如果查詢中存在除了 group by 指定的列之外的索引其他部分，那麼必須以常量的形式出現（除了min() 和 max() 兩個聚集函數）。比如 select c1,c3 from t1 group by c1,c2 不能使用松散索引掃描。而 select c1,c3 from t1 where c3 = 3 group by c1,c2 可以使用松散索引掃描。
5. 索引中的列必須索引整個資料列的值，而不是一個字首索引。比如，c1 varchar(20), INDEX (c1(10))，這個索引沒發用作松散索引掃描。
如果查詢能夠使用松散索引掃描，那麼執行計劃中 Extra 中提示 “using index for group-by”。
假設 t1(c1,c2,c3,c4) 表上有一個索引 idx(c1,c2,c3)，松散索引掃描通路方法可用于以下查詢：
| 1 2 3 4 5 6 7 | SELECT c1, c2 FROM t1 GROUP BY c1, c2; SELECT DISTINCT c1, c2 FROM t1; SELECT c1, MIN(c2) FROM t1 GROUP BY c1; SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2; SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2; SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2; SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2; |
執行以下查詢無法使用松散索引掃描：
- 除了 MIN() 或 MAX() 之外還有聚合功能，比如 SUM() ：
| 1 | SELECT c1, SUM(c2) FROM t1 GROUP BY c1; |
- group by 子句中的列不會形成索引的最左字首：
| 1 | SELECT c1, c2 FROM t1 GROUP BY c2, c3; |
- 查詢列與 group by 不相等：
| 1 | SELECT c1, c3 FROM t1 GROUP BY c1, c2; |
要查詢包含 where c3=const 可以使用松散索引掃描。
除了已經支援的 MIN() 和 MAX() 引用之外，松散索引掃描通路方法可以應用于選擇清單中的其他形式的聚合函數引用，如：AVG(DISTINCT)，SUM(DISTINCT)、COUNT(DISTINCT)。AVG(DISTINCT) 和 SUM(DISTINCT) 采取單個列參數，COUNT(DISTINCT) 可以有多個列參數。
假設 t1(c1,c2,c3,c4) 表上有一個索引 idx(c1,c2,c3)，松散索引掃描通路方法可用于以下查詢：
| 1 2 | SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1; SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1; |
為什麼松散索引掃描的效率會很高?
因為在沒有 where 子句，也就是必須經過全索引掃描的時候，松散索引掃描需要讀取的鍵值數量與分組的組數量一樣多，也就是說比實際存在的鍵值數目要少很多。而在 where 子句包含範圍判斷式或者等值表達式的時候， 松散索引掃描查找滿足範圍條件的每個組的第1個關鍵字，并且再次讀取盡可能最少數量的關鍵字。
緊湊索引掃描（Tight Index Scan）
group by 在無法使用松散索引掃描時，還可以選擇緊湊索引掃描，若兩者都不可選，則隻能借助臨時表。
緊密索引掃描可以是完整索引掃描，也可以是範圍索引掃描，具體取決于查詢條件。
如果 where 條件有範圍掃描，那麼緊湊索引掃描僅讀取滿足這些條件的鍵（索引元組），否則執行全索引掃描。這種方式讀取所有 where 條件定義的範圍内的鍵，或者掃描整個索引，因而稱作緊湊索引掃描。對于緊湊索引掃描，隻有在所有滿足範圍條件的鍵被找到之後才會執行分組操作。
要使緊湊索引掃描起作用，在查詢中存在常量相等的 where 條件字段（索引中的字段），且該字段在 group by 指定的字段的前面或者中間，來自于相等條件的常量能夠填充搜尋鍵中的間隙，因而可以構成一個索引的完整字首，索引字首能夠用于索引查找。而如果需要排序 group by 結果，并且能夠形成索引字首的搜尋關鍵字，還可以避免額外的排序操作，因為使用有順序的索引的字首進行搜尋已經按順序檢索到了所有關鍵字。
緊湊索引掃描實作 group by 和松散索引掃描的差別主要在于他需要在掃描索引的時候，讀取所有滿足條件的索引鍵，然後再根據讀取的資料來完成 group by 操作得到相應結果。
這時候的執行計劃的 Extra 資訊中已經沒有 “Using index for group-by” 了，但并不是說 MySQL 的 group by 操作并不是通過索引完成的，隻不過是需要通路 where 條件所限定的所有索引鍵資訊之後才能得出結果。這就是通過緊湊索引掃描來實作 group by 的執行計劃輸出資訊。
假設 t1(c1,c2,c3,c4) 表上有一個索引 idx(c1,c2,c3)，以下查詢不适用于之前描述的松散索引掃描通路方法，但仍然可以使用緊湊索引掃描通路方法。
- 這 group by 有一個間隙，但它被覆寫的條件為 c2 = ‘a’：
| 1 | SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3; |
- 這 group by 不是從列的第一部分開始，但是有一個條件為該部分提供了一個常數：
| 1 | SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3; |
在 MySQL 中，MySQL Query Optimizer 首先會選擇嘗試通過松散索引掃描來實作 group by 操作，當發現某些情況無法滿足松散索引掃描實作 group by 的要求之後，才會嘗試通過緊湊索引掃描來實作。
當 group by 條件字段并不連續或者不是索引字首部分的時候，MySQL Query Optimize 無法使用松散索引掃描，設定無法直接通過索引完成 group by 操作，因為缺失的索引鍵資訊無法得到。但是，如果查詢語句中存在一個常量值來引用缺失的索引鍵，則可以使用緊湊索引掃描完成 group by 操作，因為常量填充了搜尋關鍵字中的“間隙”，可以形成完整的索引字首，這些索引字首可以用于索引查找。而如果需要排序 group by 結果，并且能夠形成索引字首的搜尋關鍵字，MySQL 還可以避免額外的排序操作，因為使用有順序的索引的字首進行搜尋已經按順序檢索到了所有關鍵字。
group by 優化方法 — 直接排序
如果我們明明知道，一個 group by 語句中需要放到臨時表上的資料量特别大，卻還是要按照“先放到記憶體臨時表，插入一部分資料後，發現記憶體臨時表不夠用了再轉成磁盤臨時表”，看上去就有點兒傻。
在 group by 語句中加入 SQL_BIG_RESULT 這個提示（hint），就可以告訴優化器：這個語句涉及的資料量很大，請直接用磁盤臨時表。
MySQL 的優化器一看，磁盤臨時表是 B+ 樹存儲，存儲效率不如數組來得高。是以，既然你告訴我資料量很大，那從磁盤空間考慮，還是直接用數組來存吧。
是以，下面這個語句
| 1 | select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m; |
的執行流程就是這樣的：
1. 初始化 sort_buffer，确定放入一個整型字段，記為 m；
2. 掃描表 t1 的索引 a，依次取出裡面的 id 值, 将 id%100 的值存入 sort_buffer 中；
3. 掃描完成後，對 sort_buffer 的字段 m 做排序（如果 sort_buffer 記憶體不夠用，就會利用磁盤臨時檔案輔助排序）；
4. 排序完成後，就得到了一個有序數組。
根據有序數組，得到數組裡面的不同值，以及每個值的出現次數。這一步的邏輯，已經從前面了解過了。執行流程如下圖。
| 1 2 3 4 5 6 7 | mysql> desc select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------+ | 1 | SIMPLE | t1 | NULL | index | PRIMARY,a | a | 5 | NULL | 1000 | 100.00 | Using index; Using filesort | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------+ 1 row in set, 1 warning (0.01 sec) |
從執行計劃上來看，從 Extra 字段可以看到，這個語句的執行沒有再使用臨時表，而是直接用了排序算法，認為用 sort_buffer 直接排序性能更好。
以上就介紹了MySQL查詢優化MySQL分組查詢Group By實作原理詳解，包括了MySQL查詢優化方面的内容，希望對MySQL有興趣的朋友有所幫助。
二、group by 與 distinct
上面講完 group by 的原理之後，還有一種與去重的語句 distinct。如果我們不需要對分組做聚合操作，那麼 group by 與 distinct 誰的性能更好呢？
如果表 t 的字段 a 上沒有索引，那麼下面這兩條語句的性能是不是相同的：
| 1 2 | select a from t group by a order by null; select distinct a from t; |
首先需要說明的是，這種 group by 的寫法，并不是 SQL 标準的寫法。标準的 group by 語句，是需要在 select 部分加一個聚合函數，比如：
| 1 | select a,count(*) from t group by a order by null; |
這條語句的邏輯是：按照字段 a 分組，計算每組的 a 出現的次數。在這個結果裡，由于做的是聚合計算，相同的 a 隻出現一次。
沒有了 count(*) 以後，也就是不再需要執行“計算總數”的邏輯時，第一條語句的邏輯就變成是：按照字段 a 做分組，相同的 a 的值隻傳回一行。而這就是 distinct 的語義，是以不需要執行聚合函數時，distinct 和 group by 這兩條語句的語義和執行流程是相同的，是以執行性能也相同。
這兩條語句的執行流程是下面這樣的。
1. 建立一個臨時表，臨時表有一個字段 a，并且在這個字段 a 上建立一個唯一索引；
2. 周遊表 t，依次取資料插入臨時表中：如果發現唯一鍵沖突，就跳過；否則插入成功；
3. 周遊完成後，将臨時表作為結果集傳回給用戶端。
三、排序不一緻問題
源起，阿裡雲論壇有人反應 MySQL 5.6 分頁有重複值（排序字段沒有用索引，或則直接是全表掃描），MariaDB 已經是優化後的方案，和 5.6 一緻。阿裡資料庫月報也對此進行了回複：MySQL · 答疑解惑 · MySQL Sort 分頁
測試表和資料：
| 1 2 3 4 5 6 7 8 | create table t1(id int primary key, c1 int, c2 varchar(128)); insert into t1 values(1,1,'a'); insert into t1 values(2,2,'b'); insert into t1 values(3,2,'c'); insert into t1 values(4,2,'d'); insert into t1 values(5,3,'e'); insert into t1 values(6,4,'f'); insert into t1 values(7,5,'g'); |
假設每頁 3 條記錄，第一頁 limit 0,3 和第二頁 limit 3,3 查詢結果如下：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | # MySQL 5.6; mysql> select * from t1 order by c1 limit 0,3; +----+------+------+ | id | c1 | c2 | +----+------+------+ | 1 | 1 | a | | 3 | 2 | c | | 4 | 2 | d | +----+------+------+ 3 rows in set (0.00 sec) mysql> select * from t1 order by c1 limit 3,3; +----+------+------+ | id | c1 | c2 | +----+------+------+ | 4 | 2 | d | | 5 | 3 | e | | 6 | 4 | f | +----+------+------+ 3 rows in set (0.01 sec) |
我們可以看到 id 為 4 的這條記錄居然同時出現在兩次查詢中，這明顯是不符合預期的，而且在 5.5 版本中沒有這個問題。
使用優先隊列排序的目的就是在不能使用索引有序性的時候，如果要排序，并且使用了 limit n，那麼隻需要在排序的過程中，保留 n 條記錄即可，這樣雖然不能解決所有記錄都需要排序的開銷，但是隻需要 sort buffer 少量的記憶體就可以完成排序，上面已經說明。之是以 MySQL 5.6 出現了第二頁資料重複的問題，是因為使用了優先隊列排序，其使用了堆排序的排序方法，而堆排序是一個不穩定的排序方法，也就是相同的值（例子中的值2）可能排序出來的資料和讀出來的資料順序不一緻，無法保證排序前後資料位置的一緻，是以導緻分頁重複的現象。
避免這個問題在阿裡月報有說：MySQL · 答疑解惑 · MySQL Sort 分頁
但在 MySQL 5.7 版本中此問題又沒有了。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | # MySQL 5.7 mysql> select * from t1 order by c1 limit 0,3; +----+------+------+ | id | c1 | c2 | +----+------+------+ | 1 | 1 | a | | 2 | 2 | b | | 3 | 2 | c | +----+------+------+ 3 rows in set (0.00 sec) mysql> select * from t1 order by c1 limit 3,3; +----+------+------+ | id | c1 | c2 | +----+------+------+ | 4 | 2 | d | | 5 | 3 | e | | 6 | 4 | f | +----+------+------+ 3 rows in set (0.00 sec) |
<參考>
極客時間《MySQL 45講實戰》
http://www.codes51.com/article/detail_1774816_1.html
http://mysql.taobao.org/monthly/2015/06/04
https://dev.mysql.com/doc/refman/5.7/en/group-by-optimization.html
轉載自：http://www.ywnds.com/?p=10174
![資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)