数据库实践丨MySQL多表join分析（转载）

最新推荐文章于 2022-09-19 02:10:46 发布
最新推荐文章于 2022-09-19 02:10:46 发布
阅读量211 收藏
点赞数
原文链接：https://blog.csdn.net/devcloud/article/details/107806704?utm_medium=distribute.pc_feed.none-task-blog-personrec_tag-11.nonecase&depth_1-utm_source=distribute.pc_feed.none-task-blog-personrec_tag-11.nonecase&request_id=5f2b1e048c9fb674c672337d
版权

Join并行

Join并行

  1. 多表join介绍

  2. 多表Join的方式

不使用Join buffer
使用Join buffer
3. Join执行流程（老执行器）

  1. 多表join介绍

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

Orders：

OrderID

CustomerID

OrderDate

10308

2

1996-09-18

10309

37

1996-09-19

10310

77

1996-09-20

Customers：

CustomerID

CustomerName

ContactName

Country

1

Alfreds Futterkiste

Maria Anders

Germany

2

Ana Trujillo Emparedados y helados

Ana Trujillo

Mexico

3

Antonio Moreno Taquería

Antonio Moreno

Mexico

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

CustomerName

OrderDate

10308

Ana Trujillo Emparedados y helados

9/18/1996

10365

Antonio Moreno Taquería

11/27/1996

10383

Around the Horn

12/16/1996

10355

Around the Horn

11/15/1996

10278

Berglunds snabbköp

8/12/1996

  1. 多表Join的方式

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

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

不使用Join buffer

a) Simple Nested-Loop

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

https://bbs-img.huaweicloud.com/blogs/img/1590740474814073308.png

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表索引，然后回表查到匹配的数据，并返回满足条件的结果给客户端。

https://bbs-img.huaweicloud.com/blogs/img/1590740504680078364.png

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

https://bbs-img.huaweicloud.com/blogs/img/1590740544053014713.png

从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

https://bbs-img.huaweicloud.com/blogs/img/1590740562247042839.png

从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执行流程（老执行器）

https://bbs-img.huaweicloud.com/blogs/img/1590740585103016482.png

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()
点击这里→了解更多精彩内容

原文链接：https://blog.csdn.net/devcloud/article/details/107806704?utm_medium=distribute.pc_feed.none-task-blog-personrec_tag-11.nonecase&depth_1-utm_source=distribute.pc_feed.none-task-blog-personrec_tag-11.nonecase&request_id=5f2b1e048c9fb674c672337d

forest_tree
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql table multiple join_MySQL DELETE JOIN语句
weixin_42309773的博客
02-17 117
Summary: in this tutorial, we’ll show you how to delete data from multiple tables by usingMySQLDELETEJOINstatement.In the previous tutorial, we showed you several ways to delete records from multiple ...
mysql错误积累
wxg1453149997的专栏
04-26 217
1.Incorrect usage of UNION and ORDER BY union 或者union all 两边的两个结果集要用（）括起来
mysql错误码解释
weixin_33910434的博客
12-25 6392
OS error code 1: Operation not permitted OS error code 2: No such file or directory OS error code 3: No such process OS error code 4: Interrupted system call OS error code 5: Inp...
mysql join 缺陷,mysql多表join联查语句错误：#1241 - Operand should contain 1 column(s)
weixin_42412850的博客
03-17 329
对ecshop系统做一些改造，需要一个三表join的查询，但写出语句却报错了#1241 - Operand should contain 1 column(s)语句如下：SELECT a . * , IFNULL( g.goods_thumb, '' ) AS goods_thumb, g.goods_name AS goods_name_from_goods, g.brand_id, b.bra...
多表联查的bug
Coder_Cola的博客
03-10 174
问题： ....e: org.apache.ibatis.type.TypeException: Could not resolve type alias 'BasicInfo'. Cause: java.lang.ClassNotFoundException: Cannot find class: BasicInfo.... 解决办法： 补全javaType的路径 <association property="basicInfo" javaType=".....BasicInfo"&g
MySQL数据库高级查询和多表查询
09-08
MySQL数据库的高级查询和多表查询是数据库管理中不可或缺的部分，尤其对于大型系统而言，能够高效地处理复杂的数据检索和关联操作至关重要。本篇将详细探讨这两个主题。 首先，我们来看高级查询。在MySQL中，这通常...
MySQL 多表查询实现分析
09-11
MySQL 多表查询实现分析 MySQL 多表查询是指在一个数据库中，存在多个表，这些表都是...通过学习 MySQL 多表查询，我们可以更好地理解数据库中的数据关系，实现复杂数据查询和分析，并提高数据库应用的效率和性能。
mysql多表join时候update更新数据的方法
12-15
sql语句： 代码如下:update item i,resource_library r,resource_review_link l set i.name=...id and i.name=” JOIN UPDATE & JOIN DELETE 代码如下: update a set a.schoolname = b.schoolname from tb_Std as a jo
数据库MySQL-多表查询
12-14
MySQL，作为广泛使用的开源关系型数据库管理系统，提供了丰富的查询功能，包括对多表的查询操作。本篇将详细介绍如何在MySQL中执行多表查询，特别是内连接（INNER JOIN）的使用。 内连接是一种查询方式，它返回的是...
详解Mysql多表联合查询效率分析及优化
01-19
1. 笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN，或者使用’,’ 如： SELECT * FROM table1 CROSS JOIN table2 SELECT * FROM table1 JOIN table2 SELECT * FROM table1,table2  由于其...
join mutiple tables多表连接，复合连接条件即使用多个条件连接这两个表格。隐式连接，implicit join
最新发布
周周的博客
09-19 384
比如在customer表中，顾客id唯一识别了这张表。但存在我们无法使用单一列来准确识别某张表里列的情况。我们有订单id，订单日期，顾客姓名，还有订单状态。黄健代表这两列都有主键，我们称为复合主键。查询结果为空，因为没匹配上。
mysql报错2_mysql2 报错 Unsupported statement: BEGIN
weixin_42314680的博客
02-02 1172
京东云擎里使用 mysql2 的时候，包括 ActiveRecord 和 Sequel，使用 create save 等方法时会出现如下错误ActiveRecord::StatementInvalid - Mysql2::Error: Unsupported statement: BEGIN:Sequel::DatabaseError - Mysql2::Error: Unsupported st...
详解CheckStyle的检查规则（共138条规则）
热门推荐
yang1982_0907的专栏
01-10 6万+
本文主要介绍CheckStyle 5.6.1版本的各个规则配置项目，这个版本的CheckStyle将样式规则分为了以下十七种类型： 1. Annotations（注解：5个） Annotation Use Style（注解使用风格） 这项检查可以控制要使用的注解的样式。 Missing Deprecated（缺少deprecad） 检查ja
Sharding-JDBC 源码分析 —— SQL 解析（二）之SQL解析
芋艿V
07-29 198
????????????关注微信公众号：【芋艿的后端小屋】有福利：RocketMQ / MyCAT / Sharding-JDBC所有源码分析文章列表RocketMQ / MyCAT ...
Hudi Spark SQL源码学习总结-select（查询）
主要分享大数据相关的知识，如Spark、Hudi
08-17 614
上一篇文章Hudi Spark源码学习总结-spark.read.format(“hudi”).load分析了load方法直接查询Hudi表路径的源码逻辑，那么Spark SQL select 表名的方式和load最终走的逻辑是一样的吗？本文带着这个疑问来分析一下select查询Hudi表的源码逻辑通过上面的分析，我们发现Spark查询Hudi表不管是通过load的方式还是通过sqlselect的方法最终走的逻辑都是一样的。都是先查找source=hudi的DataSource，Spark2对应的为。...
连接mycat创建表 ERROR 1003 (HY000): Unsupported statement
龙哥(webnum)说的，每天进步一点点，总有一天会实现梦想！
06-07 2260
ERROR 1003 (HY000): Unsupported statement 应该连接mycat的 8066端口 9066是管理端口。
activiti中的bug列表
分享牛
11-21 2万+
分享牛原创(尊重原创 转载对的时候第一行请注明，转载出处来自分享牛http://blog.csdn.net/qq_30739519)Bug[ACT-1968] - Custom Database schema on PostgreSQL not working[ACT-4113] - REST Task API Not working: /service/runtime/tasks?assigne
MySQL分布式数据库实践：策略与技术探索
本资源是一份关于基于MySQL的分布式数据库实践的演讲稿，标题为《基于MySQL的分布式数据库实践_公开.pdf》，由杨海朝在2011年4月15日的DTCC2011会议上分享。演讲者作为资深MySQL DBA，探讨了在业务快速增长的背景下...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值