摘要： 在数据库查问中, 往往会须要查问多个表的数据, 比方查问会员信息同时查问对于这个会员的订单信息, 如果分语句查问的话, 效率会很低, 就须要用到 join 关键字来连表查问了。
Join 并行
Join 并行 1. 多表 join 介绍 2. 多表 Join 的形式不应用 Join buffer 应用 Join buffer3. Join 执行流程（老执行器）
1. 多表 join 介绍
JOIN 子句用于依据两个或多个表之间的相干列来组合它们。例如：
Orders：
Customers：
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
2. 多表 Join 的形式
Hash join 应用新执行器实现，在这里不做探讨
MySQL 反对的都是 Nested-Loop Join，以及它的变种。
不应用 Join buffer
a) Simple Nested-Loop
对 r 表的每一行，残缺扫描 s 表，依据 r[i]-s[i] 组成的行去判断是否满足条件，并返回满足条件的后果给客户端。
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> 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
从 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
从 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 执行流程（老执行器）
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()点击关注，第一工夫理解华为云陈腐技术~