这篇文章将为大家详细讲解有关MySQL中怎么实现多表join，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

Join并行

Join并行1. 多表join介绍2. 多表Join的方式不使用Join buffer使用Join buffer3. Join执行流程（老执行器）

1. 多表join介绍

JOIN子句用于根据两个或多个表之间的相关列来组合它们。 例如：

Orders：

Customers：

SELECTOrders.OrderID,Customers.CustomerName,Orders.OrderDateFROMOrdersINNERJOINCustomersONOrders.CustomerID=Customers.CustomerID;

2. 多表Join的方式

Hash join使用新执行器实现，在这里不做讨论

MySQL支持的都是Nested-Loop Join，以及它的变种。

不使用Join buffer

a) Simple Nested-Loop

对r表的每一行，完整扫描s表，根据r[i]-s[i]组成的行去判断是否满足条件，并返回满足条件的结果给客户端。

mysql>showcreatetablet1;+-------+----------------------------------------------------------------------------------------------------------------+|Table|CreateTable|+-------+----------------------------------------------------------------------------------------------------------------+|t1|CREATETABLE`t1`(`id`int(11)NOTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci|+-------+----------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)mysql>showcreatetablet3;+-------+--------------------------------------------------------------------------------------------------------------------+|Table|CreateTable|+-------+--------------------------------------------------------------------------------------------------------------------+|t3|CREATETABLE`t3`(`id`int(11)DEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci|+-------+--------------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)mysql>explainselect/*+NO_BNL()*/*fromt1,t3wheret1.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|Usingwhere|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+2rowsinset,1warning(0.00sec)

b) Index Nested-Loop

对r表的每一行，先根据连接条件去查询s表索引，然后回表查到匹配的数据，并返回满足条件的结果给客户端。

mysql>showcreatetablet2;+-------+---------------------------------------------------------------------------------------------------------------------------------------+|Table|CreateTable|+-------+---------------------------------------------------------------------------------------------------------------------------------------+|t2|CREATETABLE`t2`(`id`int(11)NOTNULL,KEY`index1`(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci|+-------+---------------------------------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)mysql>explainselect*fromt1,t2wheret1.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|Usingindex|+----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+-------------+2rowsinset,1warning(0.00sec)

使用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的每一行，遍历joinbufferfor(eachrecordinjoinbuffer){get_record();rc=generate_full_extensions(get_curr_rec());//如果不符合条件，直接返回if(rc!=NESTED_LOOP_OK)returnrc;}}while(!(error=iterator->Read()))}

mysql>explainselect*fromt1,t3wheret1.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|Usingwhere;Usingjoinbuffer(BlockNestedLoop)|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+2rowsinset,1warning(0.00sec)

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=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](constuchar*a,constuchar*b){returnh->cmp_ref(a,b)<0;});}dsmrr_next(){do{if(rowids_buf_cur==rowids_buf_last){dsmrr_fill_buffer();}//firstmatchif(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)returnrc;}}

mysql>showcreatetablet1;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+|Table|CreateTable|+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+|t1|CREATETABLE`t1`(`f1`int(11)DEFAULTNULL,`f2`int(11)DEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci|+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)mysql>showcreatetablet2;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|Table|CreateTable|+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|t2|CREATETABLE`t2`(`f1`int(11)NOTNULL,`f2`int(11)NOTNULL,`f3`char(200)DEFAULTNULL,KEY`f1`(`f1`,`f2`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci|+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)mysql>explainSELECT/*+BKA()*/t2.f1,t2.f2,t2.f3FROMt1,t2WHEREt1.f1=t2.f1ANDt2.f2BETWEENt1.f1andt1.f2andt2.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|Usingwhere||1|SIMPLE|t2|NULL|ref|f1|f1|4|test1.t1.f1|7|11.11|Usingindexcondition;Usingjoinbuffer(BatchedKeyAccess)|+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------------------------------------+2rowsinset,1warning(0.00sec)

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(eachrecordinchain){get_record_by_pos(rec_ptr);rc=generate_full_extensions(rec_ptr);if(rc!=NESTED_LOOP_OK)returnrc;}}}

mysql>showcreatetablecity;+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|Table|CreateTable|+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|city|CREATETABLE`city`(`ID`int(11)NOTNULLAUTO_INCREMENT,`Name`char(35)NOTNULLDEFAULT'',`Country`char(3)NOTNULLDEFAULT'',`Population`int(11)NOTNULLDEFAULT'0',PRIMARYKEY(`ID`),KEY`Population`(`Population`),KEY`Country`(`Country`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci|+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)mysql>showcreatetablecountry;+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|Table|CreateTable|+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|country|CREATETABLE`country`(`Code`char(3)NOTNULLDEFAULT'',`Name`char(52)NOTNULLDEFAULT'',`SurfaceArea`float(10,2)NOTNULLDEFAULT'0.00',`Population`int(11)NOTNULLDEFAULT'0',`Capital`int(11)DEFAULTNULL,PRIMARYKEY(`Code`),UNIQUEKEY`Name`(`Name`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci|+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1rowinset(0.01sec)mysql>EXPLAINSELECTcity.Name,country.NameFROMcity,countryWHEREcity.country=country.CodeANDcountry.NameLIKE'L%'ANDcity.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|Usingwhere;Usingindex||1|SIMPLE|city|NULL|ref|Population,Country|Country|12|test1.country.Code|1|100.00|Usingwhere;Usingjoinbuffer(BatchedKeyAccess(unique))|+----+-------------+---------+------------+-------+--------------------+---------+---------+--------------------+------+----------+--------------------------------------------------------------+2rowsinset,1warning(0.01sec)

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

sub_select<--------------------------------------------+|->iterator::read()//读一行数据||->evaluate_join_record()//检查这行数据是否符合条件||->next_select()---+|||sub_select_op<--------+||->op->put_record()//前表数据写入joincache||->put_record_in_cache()||->join->record()||->join_matching_records()||->(qep_tab->next_select)(join,qep_tab+1,0)//继续调用next_select|->end_send()

关于MySQL中怎么实现多表join就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。