自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

數(shù)據(jù)庫(kù)實(shí)踐丨MySQL多表join分析

作者：華為云開發(fā)者社區(qū)
數(shù)據(jù)庫(kù) MySQL
在數(shù)據(jù)庫(kù)查詢中,往往會(huì)需要查詢多個(gè)表的數(shù)據(jù),比如查詢會(huì)員信息同時(shí)查詢關(guān)于這個(gè)會(huì)員的訂單信息,如果分語(yǔ)句查詢的話,效率會(huì)很低,就需要用到j(luò)oin關(guān)鍵字來(lái)連表查詢了。

Join并行

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

1. 多表join介紹

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

Orders：

Customers：

 

  1. SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate  
  2. FROM Orders  
  3. 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é)果給客戶端。

 

  1. mysql> show create table t1;  
  2. +-------+----------------------------------------------------------------------------------------------------------------+  
  3. | Table | Create Table                                                                                                   |  
  4. +-------+----------------------------------------------------------------------------------------------------------------+  
  5. | t1    | CREATE TABLE `t1` (  
  6.  `id` int(11) NOT NULL  
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |  
  8. +-------+----------------------------------------------------------------------------------------------------------------+  
  9. 1 row in set (0.00 sec)  
  10. mysql> show create table t3;  
  11. +-------+--------------------------------------------------------------------------------------------------------------------+  
  12. | Table | Create Table                                                                                                       |  
  13. +-------+--------------------------------------------------------------------------------------------------------------------+  
  14. | t3    | CREATE TABLE `t3` (  
  15.  `id` int(11) DEFAULT NULL  
  16. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |  
  17. +-------+--------------------------------------------------------------------------------------------------------------------+  
  18. 1 row in set (0.00 sec)  
  19. mysql> explain select /*+ NO_BNL() */ * from t1, t3 where t1.id = t3.id;  
  20. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  21. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |  
  22. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  23. |  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL        |  
  24. |  1 | SIMPLE      | t3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |  
  25. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  26. 2 rows in set, 1 warning (0.00 sec) 

b) Index Nested-Loop

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

 

  1. mysql> show create table t2;  
  2. +-------+---------------------------------------------------------------------------------------------------------------------------------------+  
  3. | Table | Create Table                                                                                                                          |  
  4. +-------+---------------------------------------------------------------------------------------------------------------------------------------+  
  5. | t2    | CREATE TABLE `t2` (  
  6.  `id` int(11) NOT NULL,  
  7.  KEY `index1` (`id`)  
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |  
  9. +-------+---------------------------------------------------------------------------------------------------------------------------------------+  
  10. 1 row in set (0.00 sec)  
  11. mysql> explain select * from t1, t2 where t1.id = t2.id;  
  12. +----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+-------------+  
  13. | id | select_type | table | partitions | type | possible_keys | key    | key_len | ref        | rows | filtered | Extra       |  
  14. +----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+-------------+  
  15. |  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL   | NULL    | NULL       |    2 |   100.00 | NULL        |  
  16. |  1 | SIMPLE      | t2    | NULL       | ref  | index1        | index1 | 4       | test.t1.id |    1 |   100.00 | Using index |  
  17. +----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+-------------+  
  18. 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操作。 

  1. JOIN_CACHE_BNL::join_matching_records(){  
  2.  do {  
  3.    //讀取s表的每一行 
  4.     qep_tab->table()->file->position(qep_tab->table()->record[0]);  
  5.    //針對(duì)s的每一行，遍歷join buffer  
  6.    for(each record in join buffer) {  
  7.      get_record();  
  8.      rc = generate_full_extensions(get_curr_rec());  
  9.      //如果不符合條件，直接返回  
  10.      if (rc != NESTED_LOOP_OK) return rc;  
  11.    }  
  12.  } while(!(error = iterator->Read()))  
  13. }  
  1. mysql> explain select  * from t1, t3 where t1.id = t3.id;  
  2. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+  
  3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |  
  4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+  
  5. |  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                                               |  
  6. |  1 | SIMPLE      | t3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |  
  7. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+  
  8. 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，通過(guò)這個(gè)key去s表掃描所需的數(shù)據(jù)。 

  1. dsmrr_fill_buffer(){  
  2.  while((rowids_buf_cur < rowids_buf_end) &&  
  3.        !(res = h2->handler::multi_range_read_next(&range_info))){  
  4.    //下壓的index條件  
  5.    if (h2->mrr_funcs.skip_index_tuple &&  
  6.        h2->mrr_funcs.skip_index_tuple(h2->mrr_iter, curr_range->ptr))  
  7.      continue;  
  8.    memcpy(rowids_buf_cur, h2->ref, h2->ref_length);  
  9.  }  
  10.  varlen_sort(  
  11.      rowids_buf, rowids_buf_cur, elem_size,  
  12.      [this](const uchar *a, const uchar *b) { return h->cmp_ref(a, b) < 0; });  
  13. }  
  14. dsmrr_next(){  
  15.  do{  
  16.    if (rowids_buf_cur == rowids_buf_last) {  
  17.      dsmrr_fill_buffer();  
  18.    }  
  19.    // first match  
  20.    if (h2->mrr_funcs.skip_record &&  
  21.        h2->mrr_funcs.skip_record(h2->mrr_iter, (char *)cur_range_info, rowid))  
  22.      continue;  
  23.    res = h->ha_rnd_pos(table->record[0], rowid);  
  24.    break;  
  25.  } while(true);  
  26. }  
  27. JOIN_CACHE_BKA::join_matching_records(){  
  28.  while (!(error = file->ha_multi_range_read_next((char **)&rec_ptr))) {  
  29.    get_record_by_pos(rec_ptr);  
  30.    rc = generate_full_extensions(rec_ptr);  
  31.      if (rc != NESTED_LOOP_OK) return rc;  
  32.  }  
  33. }  
  1. mysql> show create table t1;  
  2. +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+  
  3. | Table | Create Table                                                                                                                                    |  
  4. +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+  
  5. | t1    | CREATE TABLE `t1` (  
  6.  `f1` int(11) DEFAULT NULL, 
  7.  `f2` int(11) DEFAULT NULL  
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |  
  9. +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+  
  10. 1 row in set (0.00 sec)  
  11. mysql> show create table t2;  
  12. +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
  13. | Table | Create Table                                                                                                                                                                                | 
  14. +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
  15. | t2    | CREATE TABLE `t2` (  
  16.  `f1` int(11) NOT NULL,  
  17.  `f2` int(11) NOT NULL,  
  18.  `f3` char(200) DEFAULT NULL,  
  19.  KEY `f1` (`f1`,`f2`)  
  20. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |  
  21. +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
  22. 1 row in set (0.00 sec)  
  23. 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;  
  24. +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------------------------------------+ 
  25. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra                                                      | 
  26. +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------------------------------------+ 
  27. |  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL        |    3 |   100.00 | Using where                                                   | 
  28. |  1 | SIMPLE      | t2    | NULL       | ref  | f1            | f1   | 4       | test1.t1.f1 |    7 |    11.11 | Using index condition; Using join buffer (Batched Key Access) | 
  29. +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------------------------------------+ 
  30. 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ù)掃描）。 

  1. JOIN_CACHE_BKA_UNIQUE::join_matching_records(){  
  2.  while (!(error = file->ha_multi_range_read_next((char **)&key_chain_ptr))) {  
  3.    do(each record in chain){  
  4.      get_record_by_pos(rec_ptr);  
  5.      rc = generate_full_extensions(rec_ptr);  
  6.        if (rc != NESTED_LOOP_OK) return rc;  
  7.      }  
  8.  }  
  9. }  
  1. mysql> show create table city;  
  2. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
  3. | Table | Create Table                                                                                                                                                                                                                                                                                                                                       | 
  4. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
  5. | city  | CREATE TABLE `city` (  
  6.  `ID` int(11) NOT NULL AUTO_INCREMENT,  
  7.  `Name` char(35) NOT NULL DEFAULT '',  
  8.  `Country` char(3) NOT NULL DEFAULT '',  
  9.  `Population` int(11) NOT NULL DEFAULT '0',  
  10.  PRIMARY KEY (`ID`),  
  11.  KEY `Population` (`Population`),  
  12.  KEY `Country` (`Country`)  
  13. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |  
  14. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
  15. 1 row in set (0.00 sec)  
  16. mysql> show create table country;  
  17. +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
  18. | Table   | Create Table                                                                                                                                                                                                                                                                                                                                                   | 
  19. +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
  20. | country | CREATE TABLE `country` (  
  21.  `Code` char(3) NOT NULL DEFAULT '',  
  22.  `Name` char(52) NOT NULL DEFAULT '',  
  23.  `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',  
  24.  `Population` int(11) NOT NULL DEFAULT '0',  
  25.  `Capital` int(11) DEFAULT NULL,  
  26.  PRIMARY KEY (`Code`),  
  27.  UNIQUE KEY `Name` (`Name`)  
  28. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |  
  29. +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
  30. 1 row in set (0.01 sec)  
  31. mysql> EXPLAIN SELECT city.Name, country.Name FROM city,country WHERE city.country=country.Code AND  country.Name LIKE 'L%' AND city.Population > 100000; 
  32. +----+-------------+---------+------------+-------+--------------------+---------+---------+--------------------+------+----------+--------------------------------------------------------------+ 
  33. | id | select_type | table   | partitions | type  | possible_keys      | key     | key_len | ref                | rows | filtered | Extra                                                        | 
  34. +----+-------------+---------+------------+-------+--------------------+---------+---------+--------------------+------+----------+--------------------------------------------------------------+ 
  35. |  1 | SIMPLE      | country | NULL       | index | PRIMARY,Name       | Name    | 208     | NULL               |    1 |   100.00 | Using where; Using index                                     | 
  36. |  1 | SIMPLE      | city    | NULL       | ref   | Population,Country | Country | 12      | test1.country.Code |    1 |   100.00 | Using where; Using join buffer (Batched Key Access (unique)) | 
  37. +----+-------------+---------+------------+-------+--------------------+---------+---------+--------------------+------+----------+--------------------------------------------------------------+ 
  38. 2 rows in set, 1 warning (0.01 sec) 

 3. Join執(zhí)行流程（老執(zhí)行器）

 

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

 

責(zé)任編輯：龐桂玉 來(lái)源： segmentfault
數(shù)據(jù)庫(kù)MySQLjoin
相關(guān)推薦

2022-09-05 10:06:21

MySQL外循環(huán)內(nèi)循環(huán)

2018-07-30 15:00:05

數(shù)據(jù)庫(kù)MySQLJOIN

2017-07-18 17:07:40

數(shù)據(jù)庫(kù) MyCATJoin

2010-05-21 14:01:23

MySQL數(shù)據(jù)庫(kù)

2010-06-07 16:22:55

MySQL數(shù)據(jù)庫(kù)

2011-07-06 10:49:50

MySQL優(yōu)化

2010-06-02 18:07:44

MySQL數(shù)據(jù)庫(kù)

2011-07-06 14:12:20

MySQLPercona

2018-06-26 15:58:06

數(shù)據(jù)庫(kù)MySQL索引優(yōu)化

2021-04-09 08:21:25

數(shù)據(jù)庫(kù)索引數(shù)據(jù)

2023-09-12 09:45:54

Java數(shù)據(jù)庫(kù)

2024-05-28 00:00:30

Golang數(shù)據(jù)庫(kù)

2017-06-22 16:00:07

數(shù)據(jù)庫(kù)NoSQL遷移實(shí)踐

2022-02-10 10:51:35

數(shù)據(jù)庫(kù)

2013-10-08 09:54:41

數(shù)據(jù)庫(kù)安全數(shù)據(jù)庫(kù)管理

2017-03-15 15:14:03

MySQL數(shù)據(jù)庫(kù)高可用性

2011-08-23 18:19:19

Oracle行轉(zhuǎn)列Join用法

2025-04-08 06:00:00

2017-11-22 09:20:41

數(shù)據(jù)庫(kù)在線數(shù)據(jù)遷移Subscriptio
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)