MySQL · 源碼分析 · 聚合函數（Aggregate Function）的實作過程

title: MySQL · 源碼分析 · 聚合函數（Aggregate Function）的實作過程

author: 道客

總覽

聚合函數（Aggregate Function）顧名思義，就是将一組資料進行統一計算，常常用于分析型資料庫中，當然在應用中是非常重要不可或缺的函數計算方式。比如我們常見的COUNT/AVG/SUM/MIN/MAX等等。本文主要分析下該類函數實作的一些架構，不涉及到每個函數的詳盡分析。聚合函數（Aggregate Function）實作的大部分代碼在item_sum.h和item_sum.cc。

下面我們看一下代碼，聚合函數（Aggregate Function）有哪些類型：

enum Sumfunctype {
    COUNT_FUNC,           // COUNT
    COUNT_DISTINCT_FUNC,  // COUNT (DISTINCT)
    SUM_FUNC,             // SUM
    SUM_DISTINCT_FUNC,    // SUM (DISTINCT)
    AVG_FUNC,             // AVG
    AVG_DISTINCT_FUNC,    // AVG (DISTINCT)
    MIN_FUNC,             // MIN
    MAX_FUNC,             // MAX
    STD_FUNC,             // STD/STDDEV/STDDEV_POP or DISTINCT
    VARIANCE_FUNC,        // VARIANCE/VAR_POP and VAR_SAMP or DISTINCT
    SUM_BIT_FUNC,         // BIT_AND, BIT_OR and BIT_XOR
    UDF_SUM_FUNC,         // user defined functions
    GROUP_CONCAT_FUNC,    // GROUP_CONCAT or GROUP_CONCAT DISTINCT
    JSON_AGG_FUNC,        // JSON_ARRAYAGG and JSON_OBJECTAGG
    ROW_NUMBER_FUNC,      // Window functions
    RANK_FUNC,
    DENSE_RANK_FUNC,
    CUME_DIST_FUNC,
    PERCENT_RANK_FUNC,
    NTILE_FUNC,
    LEAD_LAG_FUNC,
    FIRST_LAST_VALUE_FUNC,
    NTH_VALUE_FUNC
  };

類Item_sum是聚合函數的基類。接下來我們繼續看一下總體和主要的聚合函數具體在代碼中的類結構和繼承關系，

MySQL · 源碼分析 · 聚合函數（Aggregate Function）的實作過程

COUNT/SUM/AVG/STD/VAR函數

MIN/MAX函數

BIT_OR/BIT_AND/BIT_XOR函數

不帶GROUP BY聚合

下面我們來介紹下如何工作的，先來看看不帶GROUP BY的聚合過程。該過程借助了一個輔助類Aggregator，而GROUP BY并不使用該輔助類。

在優化階段，需要進行setup，比如初始化distinct或者sorting需要Temp table或者Temp tree結構，友善下階段的聚合函數。具體根據不同函數有不同的實作。

JOIN::optimize--> 
JOIN::make_tmp_tables_info--> 
setup_sum_funcs--> 
Item_sum::aggregator_setup-->  
Aggregator_simple::setup-->
Item_sum::setup-->

在執行階段，結果輸出函數end_send_group調用init_sum_functions來對該SQL查詢的所有SUM函數進行聚合計算。

JOIN::exec()--> 
do_select()--> 
sub_select()--> 
evaluate_join_record()--> 
end_send_group()--> 
init_sum_functions--> for all sum functions
reset_and_add()--> 
aggregator_clear()/aggregator_add()--> 
Item_sum_xxx::clear()/Item_sum_xxx::add()

在計算distinct聚合時候，還需要必須實作aggregator::endup()，因為Distinct_aggregator::add() 隻是通過某種方式采集了unique的行，但是并未儲存，需要在這個階段進行儲存。這個過程也可以了解，因為在Distinct聚合過程中（add），實際上無法判斷是否為唯一。當然這個不适用于GROUP BY場景，因為GROUP BY場景本身就是通過臨時表解決了唯一的問題。

帶GROUP BY聚合

MySQL對于帶GROUP BY的聚合，采用了使用Temp table的方式儲存了（GROUP BY KEY, AGGR VALUE）。

JOIN::exec()--> 
do_select()--> 
sub_select()--> 
evaluate_join_record()--> 
sub_select_op()--> 
QEP_tmp_table::put_record-->
end_update-->
init_tmptable_sum_functions/update_tmptable_sum_func--> // 每個group by的key都會調用至少一次
reset_sum_func-->Item_sum_xxx::reset_field()/Item_sum_xxx::update_field()

Item_sum繼承Item_result_field，意味着該類作為計算函數的同時，也儲存輸出的結果。具體可以看對應Item_sum_xxx::val_xxx的實作，該函數負責對上層結果或者用戶端結果進行輸出。

但是，對于特殊聚合函數如AVG/STD/VAR_POP等函數，在累加過程中，臨時儲存的變量值有多個，實際的輸出結果必須通過加工處理，尤其是在GROUP BY的場景下，多個臨時變量需要儲存到Temp table中，下次累加的時候取出來，直到最終結果輸出。是以，需要額外的輔助Item_result_field類，幫助該聚合函數進行最終結果輸出。下圖為各個輔助Item_result_field的繼承關系。

舉例來說，對于Item_avg_field類的最終結果（SELECT AVG(c1) FROM t1 GROUP BY c2）則需要通過Item_avg_field::val_xxx計算後進行輸出，如：

double Item_avg_field::val_real() {
  // fix_fields() never calls for this Item
  double nr;
  longlong count;
  uchar *res;

  if (hybrid_type == DECIMAL_RESULT) return val_real_from_decimal();

  float8get(&nr, field->ptr);
  res = (field->ptr + sizeof(double));
  count = sint8korr(res);

  if ((null_value = !count)) return 0.0;
  return nr / (double)count;
}

調用的堆棧如下：

#0  Item_avg_field::val_real
#1  0x0000000003380a3f in Item::send
#2  0x0000000002b56af1 in THD::send_result_set_row
#3  0x00000000036a82d4 in Query_result_send::send_data
#4  0x0000000002bb001f in end_send
#5  0x0000000002ba7a7d in evaluate_join_record
#6  0x0000000002bc3deb in QEP_tmp_table::end_send
#7  0x0000000002ba51e7 in sub_select_op
#8  0x0000000002ba5572 in sub_select
#9  0x0000000002ba4928 in do_select
#10 0x0000000002b9e571 in JOIN::exec

當然，這有個小Tips就是，如果核心需要實作多線程并行計算聚合函數的時候，我們就可以通過改造

對中間結果輸出save_in_field_inner函數，讓每個中間結果如2個value或者以上會按照自己的設計儲存到相應的field->ptr中，保留到臨時表中，堆棧如下：

// 這個函數是fake函數，主要其實就是調用預設的Item::save_in_field_inner基類函數。
type_conversion_status Item_avg_field::save_in_field_inner(Field *to,
                                                           bool no_conversions) {
  if (需要保留中間結果)
     to->store((char *)field->ptr, field->field_length, cs);
  else
     return Item::save_in_field_inner(to, no_conversions);
}

#0  0x0000000003549600 in Item_avg_field::save_in_field_inner
#1  0x000000000337b54f in Item::save_in_field
#2  0x0000000002b239e0 in fill_record
#3  0x0000000002b2406e in fill_record_n_invoke_before_triggers
#4  0x0000000003773357 in Query_result_insert::store_values
#5  0x0000000003772c99 in Query_result_insert::send_data
#6  0x0000000002bb001f in end_send
#7  0x0000000002ba7a7d in evaluate_join_record
#8  0x0000000002bc3deb in QEP_tmp_table::end_send
#9  0x0000000002ba51e7 in sub_select_op
#10 0x0000000002ba5572 in sub_select
#11 0x0000000002ba4928 in do_select
#12 0x0000000002b9e571 in JOIN::exec

聚合函數的優化

不帶where子句的簡單COUNT

在簡單求計數統計時候（SELECT COUNT(*) FROM t1），Server層和Innodb層實作了handler::ha_records用于直接傳回準确的計數。由于加了WHERE子句會調用evaluate_join_record評估是否該傳回行否和統計條件。詳細調用堆棧如下：

#0  ha_innobase::records
#1  0x0000000002a19b4a in handler::ha_records
#2  0x0000000002bb07fe in get_exact_record_count
#3  0x0000000002bb1389 in end_send_count
#4  0x0000000002ba47f3 in do_select
#5  0x0000000002b9e571 in JOIN::exec

無GROUP BY的MIN/MAX單行優化

如果恰好對index所在的列求MIN/MAX，而且隻傳回一行沒有GROUP BY的情況下，那麼這個是可以進行優化的，可以看執行計劃的Extra資訊變成Select tables optimized away而非使用Using temporary。

mysql> explain select min(c1) from ttt;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

是以結果會在優化階段就已經計算完畢傳回到上層，堆棧如下：

#0  ha_innobase::index_first
#1  0x00000000032bb4bc in handler::ha_index_first 
#2  0x0000000002a15215 in get_index_min_value
#3  0x0000000002a168ce in opt_sum_query
#4  0x0000000002c1973e in JOIN::optimize

當然還有類似MIN(1)/MAX(1)的常量處理也類似，連innodb層都不會涉及到，這裡就不再贅述了。

使用松散索引掃描Using index for group-by方式的聚合

這種是适用于特殊場景：MIN/MAX，因為不需要去掃描所有行去找到最大最小值。掃描的方式可以通過index直接跳到最大和最小的聚合值的位置。比如下面的例子，需要找到每個唯一c1的最最小值，恰好c1,c2是一個index上的屬性列，那麼可以通過定位c1，直接在索引上尋找(c1, min(c2))，無需掃描所有行。

create table t1 (c1 int not null, c2 char(6) not null, c3 int not  null, key(c1, c2, c3));
insert into t1 values (1, 'Const1', 2);
insert into t1 values (2, 'Const2', 4);
insert into t1 values (3, 'Const3', 4);
insert into t1 values (4, 'Const4', 9);
insert into t1 values (5, 'Const5', 9);
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
# using IndexRangeScanIterator + QUICK_GROUP_MIN_MAX_SELECT Using index for group-by
explain select min(c2)  from ttt2 group by c1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1  | NULL       | range | c1            | c1   | 4       | NULL |    2 |   100.00 | Using index for group-by |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

詳細堆棧如下：

#1  0x00000000032bbc19 in handler::ha_index_last
#2  0x00000000029f34d4 in QUICK_GROUP_MIN_MAX_SELECT::reset
#3  0x0000000002a65c51 in IndexRangeScanIterator::Init
#4  0x0000000002ba5c88 in sub_select
#6  0x0000000002b9e571 in JOIN::exec

#1  index_first/index_next_different
#2  0x0000000002a65dcd in IndexRangeScanIterator::Read
#3  0x0000000002a65c51 in IndexRangeScanIterator::Init
#4  0x0000000002ba5c88 in sub_select
#6  0x0000000002b9e571 in JOIN::exec

綜述

綜上所述，本篇文章主要從源碼層面對MySQL 8.0 實作的聚合函數（Aggregate Function）進行了一下簡要的分析。聚合函數（Aggregate Function）在無GROUP BY的情況下，利用定義成員變量儲存對應計算結果的中間值，在有GROUP BY的情況下利用了Temp Table來儲存對應的GROUP BY的鍵和聚合值，另外還介紹了一些聚合函數（Aggregate Function）的優化方式。當然這裡面還有兩類重要的聚合就是ROLL UP和WINDOWS函數，由于篇幅限制，未來篇章會單獨介紹。希望該篇文章能夠幫助廣大讀者了解MySQL聚合函數（Aggregate Function）的實作原理。

MySQL · 源碼分析 · 聚合函數（Aggregate Function）的實作過程

author: 道客

總覽

不帶GROUP BY聚合

帶GROUP BY聚合

聚合函數的優化

綜述

繼續閱讀

spring data JPA中的主鍵政策

一文搞定 MySQL 索引

MySQL（第五篇）—資料的完整性限制（2）（索引、引用完整性）資料的完整性限制（2）三、索引四、引用完整性

【MySQL索引】MySQL索引分類，90%的開發都不知道一、根據底層資料結構劃分二、根據索引字段個數劃分三、根據是否是在主鍵上建立的索引進行劃分四、根據資料與索引的存儲關聯性劃分五、其他分類六、總結

JS--------for循環删除數組元素

Python中字元串常見操作總結

Lucence的基本原理

sql執行計劃 explain 以及結合索引優化sql

MySQL - order by排序優化

2021-08-08 mysql索引

SQLServer 提升查詢速度

詳解SQL中幾種常用的表連接配接方式

oracle 中不使用已有的索引解決辦法

十四、MySQL備份和恢複資料庫1、備份和恢複的方法2、使用mysqldump導出3、恢複轉儲檔案4、字元編碼問題5、鎖表系列結語

對first_name建立唯一索引uniq_idx_firstname問題描述Sql語句

記一次因MySQL編碼問題導緻的慢查詢排查