MySQL中怎么實(shí)現(xiàn)多表join

這篇文章將為大家詳細(xì)講解有關(guān)MySQL中怎么實(shí)現(xiàn)多表join，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。

Join并行

Join并行1. 多表join介紹2. 多表Join的方式不使用Join buffer使用Join buffer3. Join執(zhí)行流程（老執(zhí)行器）

1. 多表join介紹

JOIN子句用于根據(jù)兩個(gè)或多個(gè)表之間的相關(guān)列來組合它們。 例如：

Orders：

Customers：

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate  FROM Orders  INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

2. 多表Join的方式

Hash join使用新執(zhí)行器實(shí)現(xiàn)，在這里不做討論

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

不使用Join buffer

a) Simple Nested-Loop

對(duì)r表的每一行，完整掃描s表，根據(jù)r[i]-s[i]組成的行去判斷是否滿足條件，并返回滿足條件的結(jié)果給客戶端。

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

對(duì)r表的每一行，先根據(jù)連接條件去查詢s表索引，然后回表查到匹配的數(shù)據(jù)，并返回滿足條件的結(jié)果給客戶端。

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表讀取一部分?jǐn)?shù)據(jù)到j(luò)oin cache中，當(dāng)r表數(shù)據(jù)讀完或者join cache滿后，做join操作。

JOIN_CACHE_BNL::join_matching_records(){   do {     //讀取s表的每一行     qep_tab->table()->file->position(qep_tab->table()->record[0]);     //針對(duì)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表讀取一部分?jǐn)?shù)據(jù)到j(luò)oin cache中，s表中記錄r表被連接的列的值作為索引，查詢所有符合條件的索引，然后將這些符合條件的索引排序，然后統(tǒng)一回表查詢記錄。

其中，對(duì)于每一個(gè)cached record，都會(huì)有一個(gè)key，通過這個(gè)key去s表掃描所需的數(shù)據(jù)。

dsmrr_fill_buffer(){   while((rowids_buf_cur < rowids_buf_end) &&         !(res = h3->handler::multi_range_read_next(&range_info))){     //下壓的index條件     if (h3->mrr_funcs.skip_index_tuple &&         h3->mrr_funcs.skip_index_tuple(h3->mrr_iter, curr_range->ptr))       continue;     memcpy(rowids_buf_cur, h3->ref, h3->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 (h3->mrr_funcs.skip_record &&         h3->mrr_funcs.skip_record(h3->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的時(shí)候，會(huì)記錄一個(gè)key的hash table，僅針對(duì)不同的key去s表中查詢。（疑問，為什么只有unique的時(shí)候才能用這種方式？不是unique的話，s表中可能會(huì)掃描出多條數(shù)據(jù)，也可以用這種方式去處理，減少s表的重復(fù)掃描）。

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執(zhí)行流程（老執(zhí)行器）

sub_select <--------------------------------------------+   | -> iterator::read() // 讀一行數(shù)據(jù)                    |   | -> evaluate_join_record()  //檢查這行數(shù)據(jù)是否符合條件 |   | -> next_select() ---+                               |                         |                               |  sub_select_op  <--------+                               |   | -> op->put_record() // 前表數(shù)據(jù)寫入join cache        |     | -> put_record_in_cache()                          |     | -> join->record()                                 |       | -> join_matching_records()                      |         | -> (qep_tab->next_select)(join, qep_tab + 1, 0) // 繼續(xù)調(diào)用next_select     | -> end_send()

關(guān)于MySQL中怎么實(shí)現(xiàn)多表join就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。