Join并行 MySQL多表join分析

时间:2020-08-07 10:27:01 来源: segmentfault


Join并行

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

1. 多表join介绍

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

Orders:

Customers:

SELECTOrders.OrderID,Customers.CustomerName,Orders.OrderDate

FROMOrders

INNERJOINCustomersONOrders.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的每一行,遍历joinbuffer

for(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

!(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](constuchar*a,constuchar*b){returnh->cmp_ref(a,b)<0;});

}

dsmrr_next(){

do{

if(rowids_buf_cur==rowids_buf_last){

dsmrr_fill_buffer();

}

//firstmatch

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)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分析

关于我们 加入我们 广告服务 网站地图

All Rights Reserved, Copyright 2004-2020 www.ctocio.com.cn

如有意见请与我们联系 邮箱:5 53 13 8 [email protected]

豫ICP备20005723号    IT专家网 版权所有