Join 並行

1. 多表join介紹

2. 多表Join的方式

不使用Join buffer
使用Join buffer

3. Join執行流程（老執行器）

1. 多表 join 介紹

JOIN 子句用於根據兩個或多個表之間的相關列來組合它們。例如：

Orders ：

OrderID	CustomerID	OrderDate
10308	2	1996-09-18
10309	37	1996-09-19
10310	77	1996-09-20

Customers ：

CustomerID	CustomerName	ContactName	Country
1	Alfreds Futterkiste	Maria Anders		Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo		Mexico
3	Antonio Moreno Taquería	Antonio Moreno		Mexico

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDateFROM OrdersINNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

OrderID	CustomerName	OrderDate
10308	Ana Trujillo Emparedados y helados	9/18/1996
10365	Antonio Moreno Taquería	11/27/1996
10383	Around the Horn	12/16/1996
10355	Around the Horn	11/15/1996
10278	Berglunds snabbköp	8/12/1996

2. 多表 Join 的方式

Hash join 使用新執行器實現，在這裡不做討論

MySQL 支援的都是 Nested-Loop Join ，以及它的變種。

不使用 Join buffer

a) Simple Nested-Loop

對 r 表的每一行，完整掃描 s 表，根據 r[i]-s[i] 組成的行去判斷是否滿足條件，並返回滿足條件的結果給客戶端。

mysql> show create table t1;+-------+----------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                   |+-------+----------------------------------------------------------------------------------------------------------------+| t1    | CREATE TABLE `t1` ( `id` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+----------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> show create table t3;+-------+--------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                       |+-------+--------------------------------------------------------------------------------------------------------------------+| t3    | CREATE TABLE `t3` ( `id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+--------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> explain select /*+ NO_BNL() */ * from t1, t3 where t1.id = t3.id;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL        ||  1 | SIMPLE      | t3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)

b) Index Nested-Loop

對 r 表的每一行，先根據連線條件去查詢 s 表索引，然後回表查到匹配的資料，並返回滿足條件的結果給客戶端。

mysql> show create table t2;+-------+---------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                          |+-------+---------------------------------------------------------------------------------------------------------------------------------------+| t2    | CREATE TABLE `t2` ( `id` int(11) NOT NULL, KEY `index1` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+---------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> explain select * from t1, t2 where t1.id = t2.id;+----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref        | rows | filtered | Extra       |+----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+-------------+|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL   | NULL    | NULL       |    2 |   100.00 | NULL        ||  1 | SIMPLE      | t2    | NULL       | ref  | index1        | index1 | 4       | test.t1.id |    1 |   100.00 | Using index |+----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)

使用 Join buffer

a) Block Nested Loop

從 r 表讀取一部分資料到 join cache 中，當 r 表資料讀完或者 join cache 滿後，做 join 操作。

JOIN_CACHE_BNL::join_matching_records(){ do {   //讀取s表的每一行   qep_tab->table()->file->position(qep_tab->table()->record[0]);   //針對s的每一行，遍歷join buffer   for(each record in join buffer) {     get_record();     rc = generate_full_extensions(get_curr_rec());     //如果不符合條件，直接返回     if (rc != NESTED_LOOP_OK) return rc;   } } while(!(error = iterator->Read()))}

mysql> explain select  * from t1, t3 where t1.id = t3.id;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                                               ||  1 | SIMPLE      | t3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+2 rows in set, 1 warning (0.00 sec)

b) Batched Key Access

從 r 表讀取一部分資料到 join cache 中， s 表中記錄 r 表被連線的列的值作為索引，查詢所有符合條件的索引，然後將這些符合條件的索引排序，然後統一回表查詢記錄。

其中，對於每一個 cached record ，都會有一個 key ，透過這個 key 去 s 表掃描所需的資料。

dsmrr_fill_buffer(){ while((rowids_buf_cur < rowids_buf_end) &&       !(res = h2->handler::multi_range_read_next(&range_info))){   //下壓的index條件   if (h2->mrr_funcs.skip_index_tuple &&       h2->mrr_funcs.skip_index_tuple(h2->mrr_iter, curr_range->ptr))     continue;   memcpy(rowids_buf_cur, h2->ref, h2->ref_length); } varlen_sort(     rowids_buf, rowids_buf_cur, elem_size,     [this](const uchar *a, const uchar *b) { return h->cmp_ref(a, b) < 0; });}dsmrr_next(){ do{   if (rowids_buf_cur == rowids_buf_last) {     dsmrr_fill_buffer();   }   // first match   if (h2->mrr_funcs.skip_record &&       h2->mrr_funcs.skip_record(h2->mrr_iter, (char *)cur_range_info, rowid))     continue;   res = h->ha_rnd_pos(table->record[0], rowid);   break; } while(true);}JOIN_CACHE_BKA::join_matching_records(){ while (!(error = file->ha_multi_range_read_next((char **)&rec_ptr))) {   get_record_by_pos(rec_ptr);   rc = generate_full_extensions(rec_ptr);     if (rc != NESTED_LOOP_OK) return rc; }}

mysql> show create table t1;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                    |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+| t1    | CREATE TABLE `t1` ( `f1` int(11) DEFAULT NULL, `f2` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> show create table t2;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                   |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t2    | CREATE TABLE `t2` ( `f1` int(11) NOT NULL, `f2` int(11) NOT NULL, `f3` char(200) DEFAULT NULL, KEY `f1` (`f1`,`f2`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> explain SELECT /*+ BKA() */ t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra                                                         |+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------------------------------------+|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL        |    3 |   100.00 | Using where                                                   ||  1 | SIMPLE      | t2    | NULL       | ref  | f1            | f1   | 4       | test1.t1.f1 |    7 |    11.11 | Using index condition; Using join buffer (Batched Key Access) |+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------------------------------------+2 rows in set, 1 warning (0.00 sec)

c) Batched Key Access(unique)

與 Batched Key Access 不同的是， r 中的列是 s 的唯一索引，在 r 記錄寫入 join cache 的時候，會記錄一個 key 的 hash table ，僅針對不同的 key 去 s 表中查詢。（疑問，為什麼只有 unique 的時候才能用這種方式？不是 unique 的話， s 表中可能會掃描出多條資料，也可以用這種方式去處理，減少 s 表的重複掃描）。

JOIN_CACHE_BKA_UNIQUE::join_matching_records(){ while (!(error = file->ha_multi_range_read_next((char **)&key_chain_ptr))) {   do(each record in chain){     get_record_by_pos(rec_ptr);     rc = generate_full_extensions(rec_ptr);       if (rc != NESTED_LOOP_OK) return rc;     } }}

mysql> show create table city;+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                                                                                                                                                       |+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| city  | CREATE TABLE `city` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `Country` char(3) NOT NULL DEFAULT '', `Population` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `Population` (`Population`), KEY `Country` (`Country`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> show create table country;+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                      |+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| country | CREATE TABLE `country` ( `Code` char(3) NOT NULL DEFAULT '', `Name` char(52) NOT NULL DEFAULT '', `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00', `Population` int(11) NOT NULL DEFAULT '0', `Capital` int(11) DEFAULT NULL, PRIMARY KEY (`Code`), UNIQUE KEY `Name` (`Name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)mysql> EXPLAIN SELECT city.Name, country.Name FROM city,country WHERE city.country=country.Code AND  country.Name LIKE 'L%' AND city.Population > 100000;+----+-------------+---------+------------+-------+--------------------+---------+---------+--------------------+------+----------+--------------------------------------------------------------+| id | select_type | table   | partitions | type  | possible_keys      | key     | key_len | ref                | rows | filtered | Extra                                                        |+----+-------------+---------+------------+-------+--------------------+---------+---------+--------------------+------+----------+--------------------------------------------------------------+|  1 | SIMPLE      | country | NULL       | index | PRIMARY,Name       | Name    | 208     | NULL               |    1 |   100.00 | Using where; Using index                                     ||  1 | SIMPLE      | city    | NULL       | ref   | Population,Country | Country | 12      | test1.country.Code |    1 |   100.00 | Using where; Using join buffer (Batched Key Access (unique)) |+----+-------------+---------+------------+-------+--------------------+---------+---------+--------------------+------+----------+--------------------------------------------------------------+2 rows in set, 1 warning (0.01 sec)

3. Join 執行流程（老執行器）

sub_select <--------------------------------------------+ | -> iterator::read() // 讀一行資料                    | | -> evaluate_join_record()  //檢查這行資料是否符合條件 | | -> next_select() ---+                               |                       |                               |sub_select_op  <--------+                               | | -> op->put_record() // 前表資料寫入join cache        |   | -> put_record_in_cache()                          |   | -> join->record()                                 |     | -> join_matching_records()                      |       | -> (qep_tab->next_select)(join, qep_tab + 1, 0) // 繼續呼叫next_select   | -> end_send()

資料庫實踐丨MySQL多表join分析

相關文章