摘要：在資料庫查詢中,往往會需要查詢多個表的資料,比如查詢會員資訊同時查詢關於這個會員的訂單資訊,如果分語句查詢的話,效率會很低,就需要用到join關鍵字來連表查詢了。
Join並行
Join並行1. 多表join介紹2. 多表Join的方式不使用Join buffer使用Join buffer3. Join執行流程（老執行器）
1. 多表join介紹
JOIN子句用於根據兩個或多個表之間的相關列來組合它們。 例如：
Orders：
Customers：
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
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()