從源碼角度，深度解讀 MySQL 優化器的 GROUP BY 優化策略

本文主要探討了 MySQL 8.0.22 中 GROUP BY 的工作原理，并從源碼角度剖析了查詢優化器中的優化邏輯。

本文分享自華為云社區《【華為云MySQL技術專欄】MySQL優化器中GROUP BY優化策略介紹》，作者：GaussDB 數據庫。

1. 背景介紹

在 MySQL 中，GROUP BY 功能至關重要，它允許用戶依據一個或多個列的值對結果集進行分組，通常與聚合函數（如 COUNT, SUM, AVG 等）結合使用。在日常查詢中，包含 GROUP BY 子句的查詢效率往往較低，主要原因是 GROUP BY 操作涉及臨時表的構建，這會引發頻繁的磁盤 I/O 操作，或是在計算聚合函數時增加了額外的計算開銷。

本文主要介紹 GROUP BY 的工作原理，并結合代碼剖析 MySQL 優化器對 GROUP BY 子句的優化策略。下文將基于 MySQL 8.0.22，聚焦 GROUP BY 在優化器中的源碼實現。

2. 工作原理

在 MySQL 中，查詢優化器對 GROUP BY 子句進行了多種優化，以提高處理復雜聚合查詢的效率。總體來說，GROUP BY 的實現方式大概分為四種：

1）松散索引掃描

松散索引掃描實際上就是 MySQL 利用索引掃描實現 GROUP BY，并不需要掃描所有滿足條件的索引鍵，即可完成操作得到結果。

松散索引掃描必須滿足以下條件：

SELECT 語句訪問單表；
GROUP BY fileld，fileld 必須為索引的最左前綴；
查詢中如果使用了聚合函數只能是 MIN () 和 MAX ()。聚合函數中的列必須在索引中，并且必須緊跟在 GROUP BY 子句中的列之后；
查詢中除了 GROUP BY 子句中引用的部分外，索引的其他部分必須是常量（聚合函數 MIN () 和 MAX () 中的列除外）；
對于索引中的列，必須索引完整的列值，而不僅僅是前綴。如果僅僅使用前綴，是不能用于松散索引掃描的。

為了方便理解，我們可以創建一張 orders 表，包含一個二級索引。

-- 創建表

CREATE TABLE orders (

id INT AUTO_INCREMENT PRIMARY KEY,

customer_id INT NOT NULL,

order_date DATE NOT NULL,

product_id INT NOT NULL,

quantity INT NOT NULL);

-- 插入數據

INSERT INTO orders (customer_id, order_date, product_id, quantity)

VALUES (1,  '2024-01-01',  101,  5),

(1,  '2024-01-01',  102,  10),

(2,  '2024-01-02',  101,  3),

(2,  '2024-01-02',  103,  2),

(1,  '2024-01-03',  102,  15),

(2,  '2024-01-03',  101,  7),

(1,  '2024-01-04',  103,  1),

(2,  '2024-01-04',  102,  5);

-- 創建索引

CREATE INDEX idx\_customer\_id\_order\_date_quantity ON orders(customer_id, order_date, quantity);

我們先來看看這條 SQL 語句的執行計劃，group by customer_id 可以使用二級索引，并且可以滿足松散索引的條件。在執行計劃的 Extra 列中顯示 Using index for group-by，表明該查詢使用的是松散索引掃描。

# 1、松散索引掃描

mysql> explain select customer\_id, MAX(order\_date) from orders group by customer_id;

+----+-------------+--------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+

| id | select\_type | table | partitions | type | possible\_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+

| 1 | SIMPLE | orders | NULL | range | idx\_customer\_id\_order\_date\_quantity | idx\_customer\_id\_order\_date\_quantity | 4 | NULL | 3 | 100.00 | Using index for group-by |

+----+-------------+--------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+

2）緊湊索引掃描

如果查詢不符合松散索引掃描的條件，仍有可能使用索引。如果 WHERE 子句與 GROUP BY 子句結合后的字段符合最左前綴原則，那么查詢也可以利用索引，這種情況稱為緊湊索引掃描。

例如，這條 SQL 的執行計劃：group by order_date 無法使用二級索引，但 where customer_id=1 與 group by order_date 結合后的字段（customer_id，order_date）能滿足最左前綴原則，因此也能走緊湊索引掃描，且走緊湊索引的過程中就完成分組操作，并且可以避免對結果進行額外的排序。

在執行計劃中，如果使用了緊湊索引掃描，就會去除 Using temporary，使用 Using index 進行分組。

\# 2、緊湊索引掃描

mysql> explain select customer\_id, MAX(quantity) from orders where customer\_id=1 group by order_date;

+----+-------------+--------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------------+

| id | select\_type | table | partitions | type | possible\_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------------+

| 1 | SIMPLE | orders | NULL | ref | idx\_customer\_id\_order\_date\_quantity | idx\_customer\_id\_order\_date\_quantity | 4 | const | 4 | 100.00 | Using index |

+----+-------------+--------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------------+

3）臨時表

如果無法直接使用索引來優化分組操作，MySQL 可能會使用臨時表來存儲中間結果。在這種情況下，MySQL 會執行全表掃描或索引掃描，并創建一個臨時表來存儲每個分組的數據，同時還需要更新每個分組對應的值。如果結果集非常大甚至超過了內存的限制，MySQL 會將部分結果寫入磁盤上的臨時文件，然后再進行排序和分組操作。這樣會導致大量的磁盤 I/O 操作，執行代價也會很大。

\# 設置sql\_mode(默認使用ONLY\_FULL\_GROUP\_BY模式)

mysql> SET sql_mode =’’;

\# 3、臨時表

mysql> explain select customer\_id, product\_id from orders group by order_date;

+----+-------------+--------+------------+------+-------------------------------------+------+---------+------+------+----------+-----------------+

| id | select\_type | table | partitions | type | possible\_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+-------------------------------------+------+---------+------+------+----------+-----------------+

| 1 | SIMPLE | orders | NULL | ALL | idx\_customer\_id\_order\_date_quantity | NULL | NULL | NULL | 8 | 100.00 | Using temporary |

+----+-------------+--------+------------+------+-------------------------------------+------+---------+------+------+----------+-----------------+

4）外部排序

在 MySQL 8.0.13 以前的版本中，支持在 SQL 語句中使用帶有 ASC 或 DESC 關鍵字的 GROUP BY 子句。此外，即使查詢結果不需要排序，也會默認返回按組順序排序的結果。但是在 MySQL 8.0.13 及其以后的版本中，GROUP BY 子句不再支持排序功能。如果 GROUP BY 走索引，那么返回的結果就是有序的；如果 GROUP BY 未走索引，那么返回的結果是無序的。

總之，MySQL 8.0.13 及其以后的版本的 GROUP BY 子句不會再對結果集做額外的外部排序操作。

3、GROUP BY 優化源碼介紹

MySQL 使用查詢優化器來決定如何執行 GROUP BY 查詢。涉及對索引的選擇、是否使用臨時表等決策。

1）數據結構

（1）JOIN

JOIN 類主要負責生成執行計劃，它包含了處理帶有 GROUP BY 子句的查詢所需的一些關鍵屬性：

streaming_aggregation：表示是否使用流式聚合來處理分組操作。
grouped：標記查詢是否包含 GROUP BY 子句。如果查詢中有 GROUP BY 子句，該值為 true。
implicit_grouping：表示是否隱式分組。如果查詢中沒有顯式的 GROUP BY 子句，但存在聚合函數（如 SUM (), AVG () 等），則視為隱式分組。
group_optimized_away：標記是否將 GROUP BY 子句優化掉了。如果 GROUP BY 子句中的所有字段都是常量，MySQL 可以將這些字段優化掉，以簡化查詢處理。
m_ordered_index_usage：是否使用有序索引進行分組或排序操作。
group_list：group_list 用于存儲 GROUP BY 子句的信息，包括分組字段。
tmp_table_param：存儲與創建臨時表相關的參數，用于處理分組查詢時可能需要創建的臨時表。

class JOIN {

bool streaming_aggregation{false}; // 是否使用流式聚合來處理分組操作

bool grouped; // 標記查詢是否包含GROUP BY子句

bool implicit_grouping; // 表示是否隱式分組

bool group\_optimized\_away{false}; // 標記是否將GROUP BY子句優化掉了。

enum ORDERED\_INDEX\_USAGE {

ORDERED\_INDEX\_VOID = 0, // No ordered index avail.

ORDERED\_INDEX\_GROUP_BY, // Use index for GROUP BY

ORDERED\_INDEX\_ORDER_BY // Use index for ORDER BY

} m\_ordered\_index\_usage{ORDERED\_INDEX_VOID};

ORDER\_with\_src order, group_list;

Temp\_table\_param tmp\_table\_param;

}

（2）Temp_table_param

Temp_table_param 類主要用于管理內部臨時表的參數和配置。當 MySQL 執行查詢時，有時需要創建臨時表來存儲中間結果，特別是在進行復雜的連接操作、分組、排序或子查詢時。Temp_table_param 類提供了創建和管理這些臨時表所需的機制。臨時表中涉及的 GROUP BY 的變量如下：

precomputed_group_by：標記是否已經預先計算了分組操作。如果為 true，表示在查詢執行的過程中，分組操作已經被優化或者通過索引直接完成，因此不需要創建臨時表來處理分組。
allow_group_via_temp_table：標記是否允許使用臨時表來處理分組操作。如果為 true，表示 MySQL 可以創建臨時表來存儲分組后的結果。如果為 false，則表示 MySQL 不應使用臨時表進行分組。
sum_func_count：記錄查詢中聚合函數的數量。如果優化器優化掉（使用常量替換聚合函數），此值需要更新。

class Temp\_table\_param {

bool precomputed\_group\_by;

bool allow\_group\_via\_temp\_table{true};

uint sum\_func\_count;

}

2）優化 GROUP BY

在 MySQL 8.0 中，查詢優化器對 DISTINCT、GROUP BY 和 ORDER BY 的邏輯是一起實現的。函數 optimize_distinct_group_order () 用于優化涉及 DISTINCT、GROUP BY 和 ORDER BY 的查詢。這個函數的目標是盡可能地減少排序操作和臨時表的使用，從而提高查詢效率。

（1）單表場景的 GROUP BY 優化

首先，代碼檢查是否是單表查詢，并且存在 GROUP BY 子句（group_list 非空）。同時，檢查是否有聚合函數（sum_func_count 為 0）。

如果存在 GROUP BY 子句并且沒有 rollup 并且 GROUP BY 字段存在唯一索引，那么就不需要做 GROUP 操作。并將 group_list 清空。因為唯一索引的存在意味著每組只會對應一個結果行，無需再進行分組。這一步通過走唯一索引，可以避免額外的排序或分組操作，從而提高查詢效率。

（2）去除 GROUP BY 中的常量

去除 GROUP BY 子句的常量，并檢查 group_list 是否只包含簡單的表達式。消除完常量后 group_list 為空并且原先就是有 GROUP BY 子句（grouped 為 true），這意味著 group_list 都被優化掉了，group_optimized_away 被設置為 true。

（3）計算 GROUP BY 所需要的 buffer

計算結果保存在 join 的 tmp_table_param 中。

總體來看，optimize_distinct_group_order () 函數通過單表場景的優化、去除常量表達式等方面來優化排序 GROUP BY，從而提高查詢效率。

bool JOIN::optimize\_distinct\_group_order() {

// 1、單表場景的GROUP BY優化

if (!group\_list.empty(); rollup\_state == RollupState::NONE

list\_contains\_unique\_index(tab, find\_field\_in\_order_list,

(void *)group_list.order)) {

group_list.clean();

grouped = false;

}

// 2、去除GROUP BY子句的常量

ORDER *old\_group\_list = group_list.order;

group\_list = ORDER\_with_src(

remove\_const(group\_list.order, where_cond,

rollup\_state == RollupState::NONE, simple\_group, true), group_list.src);

if (group_list.empty() && grouped) {

group\_optimized\_away = true;

}

// 3、計算GROUP BY需要的buffer大小

calc\_group\_buffer(this, group_list.order);

send\_group\_parts = tmp\_table\_param.group_parts; /* Save org parts */

}

3）臨時表

在優化器無法利用索引的時候，MySQL 就必須讀取需要的數據至臨時表，然后通過臨時表完成 GROUP BY 操作。make_tmp_tables_info () 函數的主要目的是基于查詢執行計劃，為涉及到的每一個 QEP_TAB（Query Execution Plan Tab）對象生成臨時表的元信息。這包括確定每個臨時表的列、數據類型、存儲引擎、以及是否需要排序等屬性。

（1）松散索引掃描優化

松散索引掃描保證了 grouping+min/max 的提前完成，此時 tmp_table_param->precomputed_group_by=true，把分組聚集結果寫入第一個 tmp table。

（2）創建臨時表

如果無法走索引的情況，那么需要創建臨時表。根據 GROUP BY 對應的字段和查詢的字段生成臨時表完成 GROUP BY。

初始化一個臨時分組對象 tmp_group
計算 fields 中隱藏字段的數量
創建臨時表。調用 create_intermediate_table 函數來創建中間臨時表。

參數解釋：qep_tab [curr_tmp_table]：指向當前要創建臨時表的 QEP_TAB 對象的指針。*curr_fields：指向當前字段列表的指針。tmp_group：臨時分組對象。

設置當前創建的臨時表為執行臨時表
臨時表已經分組的，在某些情況下將分組列表轉換為排序列表。如果臨時表已經被分組，并且沒有明確的 ORDER BY 子句，但需要保持分組結果的排序，那么它會將分組列表用作排序列表，以確保輸出結果按照分組字段的順序排列。這樣做可以保證查詢結果的一致性和預期的排序行為。

bool JOIN::make\_tmp\_tables_info() {

// 1、松散索引掃描優化

if (qep\_tab && qep\_tab\[0\].quick() &&

qep\_tab\[0\].quick()->is\_loose\_index\_scan())

tmp\_table\_param.precomputed\_group\_by =

!qep\_tab\[0\].quick()->is\_agg\_loose\_index_scan();

if (need\_tmp\_before_win) {

tmp_tables++;

// 2、創建臨時表

if (create\_intermediate\_table(&qep\_tab\[curr\_tmp\_table\], *curr\_fields, tmp\_group, !group\_list.empty() && simple_group))

return true;

// 3、設置寫入函數

setup\_tmptable\_write\_func(&qep\_tab\[curr\_tmp\_table\], & trace\_this\_outer);

...

// 4、檢查group by是否必須考慮排序，由于MySQL 8.0 GROUP BY不支持排序

// group->direction在一開始解析時就被設置為ORDER\_NOT\_RELEVANT

if (exec\_tmp\_table->group) {

if (order.empty() && !skip\_sort\_order) {

for (ORDER *group = group_list.order; group; group = group->next) {

if (group->direction != ORDER\_NOT\_RELEVANT) {

order = group_list; /* order by group */

break;

}

}

}

group_list.clean();

}

// 以下為ORDER BY排序邏輯(略)

DBUG_PRINT("info", ("Sorting for order by/group by"));

}

4、GaussDB (for MySQL) 兼容性支持

1） GROUP BY 支持排序

為了解決客戶從 MySQL 5.7 版本遷移到 GaussDB (for MySQL) 的兼容性問題，GaussDB (for MySQL) 支持 GROUP BY 隱式排序能力和帶有 ASC/DESC 關鍵字的 GROUP BY 子句的排序功能。

開關 rds_compatibility_mode 設置方式如下：

ALLOW_GROUP_BY_IMPLICIT_SORTING：是否打開 group by 隱式排序
ALLOW_GROUP_BY_ASC_DESC：兼容 GROUP BY field ASC/DESC 語法

# 關閉隱式排序和語法兼容（默認）

mysql> explain select customer\_id, product\_id from orders group by order_date;

+----+-------------+--------+------------+------+-------------------------------------+------+---------+------+------+----------+-----------------+

| id | select\_type | table | partitions | type | possible\_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+-------------------------------------+------+---------+------+------+----------+-----------------+

| 1 | SIMPLE | orders | NULL | ALL | idx\_customer\_id\_order\_date_quantity | NULL | NULL | NULL | 8 | 100.00 | Using temporary |

+----+-------------+--------+------------+------+-------------------------------------+------+---------+------+------+----------+-----------------+

1 row in set, 1 warning (0.00 sec)

mysql> set rds\_compatibility\_mode='ALLOW\_GROUP\_BY\_IMPLICIT\_SORTING,ALLOW\_GROUP\_BY\_ASC\_DESC';

Query OK, 0 rows affected (0.00 sec)

\# 打開隱式排序和語法兼容

mysql> explain select customer\_id, product\_id from orders group by order_date;

+----+-------------+--------+------------+------+-------------------------------------+------+---------+------+------+----------+---------------------------------+

| id | select\_type | table | partitions | type | possible\_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+-------------------------------------+------+---------+------+------+----------+---------------------------------+

| 1 | SIMPLE | orders | NULL | ALL | idx\_customer\_id\_order\_date_quantity | NULL | NULL | NULL | 8 | 100.00 | Using temporary; Using filesort |

+----+-------------+--------+------------+------+-------------------------------------+------+---------+------+------+----------+---------------------------------+

2） GROUP BY 支持并行查詢

GaussDB (for MySQL) 支持并行查詢處理，這包括對帶有 GROUP BY 子句的查詢的并行處理。對于帶有 GROUP BY 子句的查詢，MySQL 可以并行處理不同的分組，從而加速查詢執行。并行查詢詳細介紹見官網地址。

GaussDB (for MySQL) PQ 使用方式：

SET GLOBAL pq\_master\_enable=ON;

SET force\_parallel\_execute=ON;

SET parallel\_cost\_threshold=0;

SET parallel\_rows\_threshold=0;

SET parallel\_default\_dop=1;

\# GROUP BY支持PQ需要額外開啟開關

SET pq\_group\_having=1;

GROUP BY 走 PQ 的執行計劃如下：

mysql> explain select customer\_id, count(*) from orders group by customer\_id;

+----+-------------+-----------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+-------------------------------------------+

| id | select\_type | table | partitions | type | possible\_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+-------------------------------------------+

| 1 | SIMPLE | <gather1> | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Parallel execute (4 workers, test.orders) |

| 1 | SIMPLE | orders | NULL | index | idx\_customer\_id\_order\_date\_quantity | idx\_customer\_id\_order\_date\_quantity | 11 | NULL | 8 | 100.00 | Using index |

+----+-------------+-----------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+-------------------------------------------+

5、優化建議

（1）創建合適的索引

確保 GROUP BY 子句中的所有列都包含在一個索引中，盡可能走索引，盡量避免臨時表的使用。
如果查詢中還包括聚合函數（如 SUM ()、AVG () 等），確保這些函數涉及的列也在索引中，這樣可以避免回表操作。

（2）使用合適的聚合函數

如果查詢不需要聚合函數，盡量避免使用它們，因為這會增加計算負擔。

（3）優化查詢結構

盡量減少 GROUP BY 子句中涉及的列的數量，這可以減少中間結果集的大小，從而提高性能。

（4）參數配置

tmp_table_size：內存臨時表內存大小，默認是 16M。增加內存臨時表的大小，盡量避免走磁盤。
max_heap_table_size：內存臨時表內存大小，默認是 16M。增加內存臨時表的大小，盡量避免走磁盤
internal_tmp_mem_storage_engine：磁盤臨時表默認存儲引擎，允許的值為 TempTable 和 MEMORY。
sort_buffer_size：控制排序操作時使用的緩沖區大小。增加排序操作的緩存大小，可以提高排序操作的性能。

（5）使用 ONLY_FULL_GROUP_BY 模式

MySQL 8.0 引入了一個更嚴格的 SQL 模式 ONLY_FULL_GROUP_BY，它要求任何未在 GROUP BY 子句中列出的非聚合列都不能在 SELECT 列表中出現。這增加了查詢的正確性和一致性。

6、總結

本文主要探討了 MySQL 8.0.22 中 GROUP BY 的工作原理，并從源碼角度剖析了查詢優化器中的優化邏輯。此外，本文還介紹了 GaussDB (for MySQL) 對 GROUP BY 的兼容性以及并行查詢方面的支持。最后，本文提供了實用的優化建議，以幫助提高帶有 GROUP BY 子句的查詢性能。

點擊關注，第一時間了解華為云新鮮技術～

posted @ 2024-09-19 16:40 華為云開發者聯盟閱讀(359) 評論(0) 收藏舉報

刷新頁面返回頂部

從源碼角度，深度解讀 MySQL 優化器的 GROUP BY 優化策略

1. 背景介紹

2. 工作原理

1）松散索引掃描

2）緊湊索引掃描

3）臨時表

4）外部排序

3、GROUP BY 優化源碼介紹

1）數據結構

（1）JOIN

（2）Temp_table_param

2）優化 GROUP BY

（1）單表場景的 GROUP BY 優化

（2）去除 GROUP BY 中的常量

（3）計算 GROUP BY 所需要的 buffer

3）臨時表

（1）松散索引掃描優化

（2）創建臨時表

4、GaussDB (for MySQL) 兼容性支持

1） GROUP BY 支持排序

2） GROUP BY 支持并行查詢

5、優化建議

（1）創建合適的索引

（2）使用合適的聚合函數

（3）優化查詢結構

（4）參數配置

（5）使用 ONLY_FULL_GROUP_BY 模式

6、總結

公告