1. 代码100分首页
  2. 代码基础

数据库实践丨MySQL多表join分析「终于解决」

小小码 • • 代码基础 • 阅读 154
一键激活最新全家桶

数据库实践丨MySQL多表join分析「终于解决」摘要：在数据库查询中,往往会需要查询多个表的数据,比如查询会员信息同时查询关于这个会员的订单信息,如果分语句查询的话,效率会很低,就需要用到join关键字来连表查询了。 Join并行 Join并行1…

数据库实践丨MySQL多表join分析

摘要：在数据库查询中,往往会需要查询多个表的数据,比如查询会员信息同时查询关于这个会员的订单信息,如果分语句查询的话,效率会很低,就需要用到join关键字来连表查询了。

Join并行

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

1. 多表join介绍

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

Orders：

数据库实践丨MySQL多表join分析「终于解决」

Customers：

数据库实践丨MySQL多表join分析「终于解决」

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

数据库实践丨MySQL多表join分析「终于解决」

2. 多表Join的方式

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

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

不使用Join buffer

a) Simple Nested-Loop

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

数据库实践丨MySQL多表join分析「终于解决」

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

数据库实践丨MySQL多表join分析「终于解决」

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

数据库实践丨MySQL多表join分析「终于解决」

 

从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

数据库实践丨MySQL多表join分析「终于解决」

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

数据库实践丨MySQL多表join分析「终于解决」

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://daima100.com/6844.html

赞 (0)
小小码小小码
0 0
上一篇 2023-04-01
下一篇 2023-04-01

相关推荐

  • Flink 作业提交流程「建议收藏」 代码基础

    Flink 作业提交流程「建议收藏」

    Flink 作业提交流程「建议收藏」大家好，我是小寒~ 今天给大家带来一篇 flink 作业提交相关的文章。 我们都知道，在开发完一个 flink 应用程序后，打包成 jar 包，然后通过 FLink CLI 或者 Web UI 提交作

    小小码 小小码
    2023-05-25
    131
  • mongoDB new file allocation failure 代码基础

    mongoDB new file allocation failure

    mongoDB new file allocation failure db version v3.2.8 rs.initiate(conf) { “ok” : 0, “errmsg” : “new file allocation failure”, “code…

    小小码 小小码
    2023-03-25
    171
  • 使用Python tkinter实现复选按钮 代码基础

    使用Python tkinter实现复选按钮

    使用Python tkinter实现复选按钮复选按钮（Checkbutton）是一种常见的GUI控件，它用于提供多项选择，让用户可以选择其中的任意一项或多项。Python的GUI库Tkinter提供了复选按钮的实现方法，可以轻松地在图形界面中添加和使用。

    admin admin
    2024-02-14
    106
  • Vmvare扩展虚拟机磁盘大小 代码基础

    Vmvare扩展虚拟机磁盘大小

    Vmvare扩展虚拟机磁盘大小Vmvare设置好虚拟机的磁盘大小之后，发现磁盘空间不够了，这个时候怎么扩展磁盘的大小呢？ 首先，在确保虚拟机关闭的情况下，右键设置，选择硬盘，扩展，这样就可以增加磁盘的大小。 但是由于未进行分区和磁

    小小码 小小码
    2022-12-28
    145
  • SQL 入门教程：排序（ORDER BY）数据「终于解决」 代码基础

    SQL 入门教程：排序（ORDER BY）数据「终于解决」

    SQL 入门教程：排序（ORDER BY）数据「终于解决」目录汇总：SQL 入门教程：面向萌新小白的零基础入门教程 下面的 SQL 语句返回某个数据库表的单个列。但请看其输出，并没有特定的顺序。 输入▼ SELECT prod_name FROM Produ

    小小码 小小码
    2023-04-19
    158
  • Python实现图像处理的cv2库 代码基础

    Python实现图像处理的cv2库

    Python实现图像处理的cv2库在当今这个数字化的时代，图像处理已经成为非常重要的一个领域。无论是商业市场还是科研领域，图像处理都是必不可少的一环。而Python如今已经成为一个相当流行的编程语言，因此许多开发者借助Python实现各种图像处理的需求。在这个过程中，cv2库就成为了一个实用的选择。本文将会详细介绍Python的cv2库以及它在图像处理中的应用。

    admin admin
    2024-05-23
    78
  • 【ORACLE】自动产生AWR报告 代码基础

    【ORACLE】自动产生AWR报告

    【ORACLE】自动产生AWR报告 1. LINUX系统下： ##sh脚本，sh脚本调用sql脚本 #!/bin/bash if [ -f ~/.bash_profile ]; then source ~/.bash_prof…

    小小码 小小码
    2023-02-20
    151
  • 终于有人把不同标签的加工内容与落库讲明白了丨DTVision分析洞察篇 代码基础

    终于有人把不同标签的加工内容与落库讲明白了丨DTVision分析洞察篇

    终于有人把不同标签的加工内容与落库讲明白了丨DTVision分析洞察篇上一篇文章详细给大家介绍了标签的设计与加工，在标签生命周期流程中，标签体系设计完成后，便进入标签加工与上线运行阶段，一般来说数据开发团队会主导此过程，但我们需要关心以下几个问题： ·标签如何

    小小码 小小码
    2023-06-04
    147

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注