在MySQL對于join操作的處理過程中，join buffer是一個重要的概念，也是MySQL對于table join的一個重要的優化手段。雖然這個概念實現并不復雜，但是這個是實現MySQL join連接優化的一個重要方法，在'暴力'連接的時候可以極大提高join查詢的效率。

關于這個概念的權威說明當然是來自MySQL文檔中對于這個概念的說明，說明的文字不多，但是言簡意賅，說明了這個優化的主要實現思想：Assume you have the following join:

Table name Typet1 ranget2 reft3 ALLThe join is then done as follows: - While rows in t1 matching range - Read through all rows in t2 according to reference key - Store used fields from t1, t2 in cache - If cache is full - Read through all rows in t3 - Compare t3 row against all t1, t2 combinations in cache- If row satisfies join condition, send it to client - Empty cache - Read through all rows in t3 - Compare t3 row against all stored t1, t2 combinations in cache - If row satisfies join condition, send it to client二、join buffer cache存儲空間的分配

下面函數中table_count表示的就是所有join table中在該table之前的非const table數量，因為這個table要緩存自己之前所有table中的每條記錄中'需讀取'(tables[i].table->read_set置位)。

其中兩重循環每次執行都是復制下需要緩存的field的描述結構(及其對應的數據源)，或者說，二重循環只是為了賦值和保存元數據，而最后的cache->buff=(uchar*) my_malloc(size,MYF(0))才是真正的分配滿足條件的記錄內容。

static intjoin_init_cache(THD *thd,JOIN_TAB *tables,uint table_count){…… for (i=0 ; i < table_count ; i++) { bool have_bit_fields= FALSE; uint null_fields=0,used_fields; Field **f_ptr,*field; MY_BITMAP *read_set= tables[i].table->read_set; for (f_ptr=tables[i].table->field,used_fields=tables[i].used_fields ; used_fields ; f_ptr++) { field= *f_ptr; if (bitmap_is_set(read_set, field->field_index)) {used_fields--;length+=field->fill_cache_field(copy);…… } } cache->length=length+blobs*sizeof(char*); cache->blobs=blobs; *blob_ptr=0; /* End sequentel */ size=max(thd->variables.join_buff_size, cache->length); if (!(cache->buff=(uchar*) my_malloc(size,MYF(0)))) DBUG_RETURN(1); /* Don’t use cache */ /* purecov: inspected */ cache->end=cache->buff+size; reset_cache_write(cache); DBUG_RETURN(0);}三、普通的多表查詢實現

這個'普通'當然也可以理解為'樸素'、'直觀'的意思，也是大部分情況下的執行流程。普通查詢其實就是對于對于各個表格進行遞歸調用，和矩陣的乘法一樣一樣的，這個對應非常直觀，也非常通用。

而這個常規的查詢動作就是通過sub_select函數來實現，這個函數本質性上是執行

tsecer_select(){for (r = first ; r != end ; r = next){if(sofartest()){nexttable.tsecer_select()}}}

其中的sofartest()表示'使用所有當前已讀取表格可以進行的判斷'，也就是where中下推的表達式。例如 select * from a, b where a.a > 10 and b.b + a.a = 10，在a表讀取之后，其實已經可以執行 a.a > 10的判斷。當然這個是一個甚至算不上偽代碼的描述方法，而真正的代碼對應為：

enum_nested_loop_statesub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records){…… error= (*join_tab->read_first_record)(join_tab); rc= evaluate_join_record(join, join_tab, error);…… while (rc == NESTED_LOOP_OK) { error= info->read_record(info); rc= evaluate_join_record(join, join_tab, error); }…… return rc;}static enum_nested_loop_stateevaluate_join_record(JOIN *join, JOIN_TAB *join_tab, int error){…… if (select_cond) { select_cond_result= test(select_cond->val_int()); /* check for errors evaluating the condition */ if (join->thd->is_error()) return NESTED_LOOP_ERROR; }…… if (found) { enum enum_nested_loop_state rc; /* A match from join_tab is found for the current partial join. */ rc= (*join_tab->next_select)(join, join_tab+1, 0); if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)return rc; if (join->return_tab < join_tab)return NESTED_LOOP_OK; /*Test if this was a SELECT DISTINCT query on a table thatwas not in the field list; In this case we can abort ifwe found a row, as no new rows can be added to the result. */ if (not_used_in_distinct && found_records != join->found_records)return NESTED_LOOP_NO_MORE_ROWS; }……}

這里可以看到，這個地方是一個遞歸，用來產生一個笛卡爾叉乘集合，從程序實現和數學表達上看都非常簡潔可愛。在MySQL的實現中，tsecer_select函數中的for循環大致相當sub_select中的while循環，而tsecer_select函數中循環體內的內容被放在了evaluate_join_record函數中，其中的sofartest對應evaluate_join_record::test(select_cond->val_int())；tsecer_select中的nexttable.tsecer_select()語句對應evaluate_join_record::(*join_tab->next_select)(join, join_tab+1, 0)。

當使用join buffer cache時，next_select函數指向sub_select_cache

enum_nested_loop_statesub_select_cache(JOIN *join,JOIN_TAB *join_tab,bool end_of_records){ enum_nested_loop_state rc; if (end_of_records) { rc= flush_cached_records(join,join_tab,FALSE); if (rc == NESTED_LOOP_OK || rc == NESTED_LOOP_NO_MORE_ROWS) rc= sub_select(join,join_tab,end_of_records); return rc; } if (join->thd->killed) // If aborted by user { join->thd->send_kill_message(); return NESTED_LOOP_KILLED; /* purecov: inspected */ } if (join_tab->use_quick != 2 || test_if_quick_select(join_tab) <= 0) { if (!store_record_in_cache(&join_tab->cache)) return NESTED_LOOP_OK; // There is more room in cache return flush_cached_records(join,join_tab,FALSE); } rc= flush_cached_records(join, join_tab, TRUE); if (rc == NESTED_LOOP_OK || rc == NESTED_LOOP_NO_MORE_ROWS) rc= sub_select(join, join_tab, end_of_records); return rc;}

結合MySQL文檔中的說明，這里的代碼意義就比較明顯。開始對于end_of_records的判斷對應的就是

if (!store_record_in_cache(&join_tab->cache)) return NESTED_LOOP_OK; // There is more room in cache return flush_cached_records(join,join_tab,FALSE);

對應

- Store used fields from t1, t2 in cache - If cache is full

其中store_record_in_cache函數會判斷cache是否已滿，如果cache可以放入更多的緩存，則把之前table的組合記錄存儲在cache中，并返回NESTED_LOOP_OK。注意：這個地方可以說是整個cache優化的關鍵，因為這里并沒有啟動對于table的掃描。反過來說，如果cache數據已經滿了，則調用flush_cached_records函數來進行下面的流程

- Read through all rows in t3 - Compare t3 row against all t1, t2 combinations in cache- If row satisfies join condition, send it to client - Empty cache

這個流程的特殊之處在于遍歷的驅動是通過對于table的每一條記錄來和cache中所有t1、t2組合來進行比較，來判斷是否滿足下推where條件(If row satisfies join condition)，則執行join_tab->next_select函數(send it to client)。

static enum_nested_loop_stateflush_cached_records(JOIN *join,JOIN_TAB *join_tab,bool skip_last){…… info= &join_tab->read_record; do {//遍歷t3表格所有記錄……for (i=(join_tab->cache.records- (skip_last ? 1 : 0)) ; i-- > 0 ;){//遍歷cache中所有t1、t2記錄組合 read_cached_record(join_tab); skip_record= FALSE; if (select && select->skip_record(join->thd, &skip_record)) {// reset_cache_write(&join_tab->cache); return NESTED_LOOP_ERROR; } if (!skip_record) {//滿足下推的where條件//執行下一個table的遍歷 rc= (join_tab->next_select)(join,join_tab+1,0); if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS) { reset_cache_write(&join_tab->cache); return rc; } }…… } while (!(error=info->read_record(info)));五、舉例來說明下這個流程

這個實現的核心思想并不復雜，結合具體的例子來看就更加的簡單直觀。舉個例子，其中使用兩個簡單的table，其中分別存儲一個x，和y的值，我們希望通過一個join操作來計算這兩個表格中所有的滿足 x

x + y

y == 5 * 5,也就是我們最常見的'勾三股四弦五'這樣的經典勾股數數值。

mysql> create table harry (x int);Query OK, 0 rows affected (0.03 sec) mysql> insert harry values (1),(2),(3),(4),(5);Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0 mysql> create table tsecer (y int); Query OK, 0 rows affected (0.01 sec) mysql> insert tsecer values (1),(2),(3),(4),(5); Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0 mysql> explain select * from harry, tsecer where x * x + y * y = 5 * 5;+----+-------------+--------+------+---------------+------+---------+------+------+--------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+---------------+------+---------+------+------+--------------------------------+| 1 | SIMPLE | harry | ALL | NULL | NULL | NULL | NULL | 5 ||| 1 | SIMPLE | tsecer | ALL | NULL | NULL | NULL | NULL | 5 | Using where; Using join buffer |+----+-------------+--------+------+---------------+------+---------+------+------+--------------------------------+2 rows in set (0.00 sec) mysql>1、不使用joinbuffer

在不使用join buffer的情況下，對于harry表的每個x值，對應的tsecer表都要進行一次全表掃描，之后使用這個x和y的組合判斷是否滿足x

x + y

y == 5 * 5這條件。由于x總共有5個值，所以tsecer需要全表掃描的次數就是5次。

對于x的每個值，tsecer表在執行的時候先是把這個值緩存到joinbuffer中，如果buffer緩沖內容非空，那么把此時的x的值存儲在buffer中后直接返回；當join buffer滿或者是最后一條記錄的時候，此時開始啟動對于tsecer表的掃描，對于tsecer表中讀取的每一個記錄，結合前面緩存的每一個記錄，看是否滿足自己判斷條件。對于我們看到的例子，這個地方harry表的5個值都在緩存中，在tsecer表的掃描過程中，對于從tsecer中讀取的每一條記錄，結合緩存中的“每一條”緩存，判斷這個組合結果是否滿足條件，如果任意一個組很滿足，那么就繼續next_select。在這個使用buffer的例子中，可以看到這個地方只是對于tsecer表進行了一次掃描，而通常來說，數據庫的掃描代碼是最高的(因為要涉及到磁盤讀取)，這樣使用buffer的方式將tsecer表的掃描降低為1次，所以這個效率提高很多，特別是在涉及到的多個table，并且/或者 每個table中的記錄數量都很多的情況下。

本質上說，這個效率提高的原因在于提高了從table中獲得的每條記錄的“利用率”，在使用直觀掃描方式時，table的全表掃描只是和一個組合進行匹配，而使用buffer之后則是和cache中的所有組合進行匹配。

以上就是MySQL的join buffer原理的詳細內容，更多關于MySQL join buffer的資料請關注好吧啦網其它相關文章！