【GreatSQL优化器-09】make_join_query_block
一、make_join_query_block介绍
GreatSQL优化器对于多张表join的连接顺序在前面的章节介绍过的best_access_path函数已经执行了，接着就是把where条件进行切割然后推给合适的表。这个过程就是由函数make_join_query_block来执行的。
下面用几个简单的例子来说明join连接中条件推送是什么。
```sql
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT,date1 DATETIME);
INSERT INTO t1 VALUES (1,10,'2021-03-25 16:44:00.123456'),(2,1,'2022-03-26 16:44:00.123456'),(3,4,'2023-03-27 16:44:00.123456'),(5,5,'2024-03-25 16:44:00.123456'),(7,null,'2020-03-25 16:44:00.123456'),(8,10,'2020-10-25 16:44:00.123456'),(11,16,'2023-03-25 16:44:00.123456');
CREATE TABLE t2 (cc1 INT PRIMARY KEY, cc2 INT);
INSERT INTO t2 VALUES (1,3),(2,1),(3,2),(4,3),(5,15);
CREATE TABLE t3 (ccc1 INT, ccc2 varchar(100));
INSERT INTO t3 VALUES (1,'aa1'),(2,'bb1'),(3,'cc1'),(4,'dd1'),(null,'ee');
CREATE INDEX idx1 ON t1(c2);
CREATE INDEX idx2 ON t1(c2,date1);
CREATE INDEX idx2_1 ON t2(cc2);
CREATE INDEX idx3_1 ON t3(ccc1);
```
下面这个例子((t1.c1 = t3.ccc1) or (t3.ccc1 < 3))条件推送给t1
```SQL
greatsql> EXPLAIN FORMAT=TREE SELECT * FROM t1 join t3 ON t1.c1=t3.ccc1 or t3.ccc1<3;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: ((t1.c1 = t3.ccc1) or (t3.ccc1 < 3)) (cost=5.26 rows=35)
-> Inner hash join (no condition) (cost=5.26 rows=35)
-> Index scan on t1 using idx2 (cost=0.34 rows=7)
-> Hash
-> Table scan on t3 (cost=0.75 rows=5)
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```
下面例子(t1.c1 < 3)条件推给t1，(ccc1=t1.c1)条件推给t3
```SQL
greatsql> EXPLAIN FORMAT=TREE SELECT * FROM t1 join t3 ON t1.c1=t3.ccc1 and t3.ccc1<3;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=2.40 rows=2)
-> Filter: (t1.c1 < 3) (cost=1.70 rows=2)
-> Index scan on t1 using idx2 (cost=1.70 rows=7)
-> Index lookup on t3 using idx3_1 (ccc1=t1.c1) (cost=0.30 rows=1)
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```
下面例子((t3.ccc1 = t1.c2) or (t3.ccc1 is null) or (t3.ccc2 like 'a%'))条件推给t3，(((t3.ccc1 = t1.c2) and (t2.cc1 = t1.c1)) or (t3.ccc1 is null) or (t3.ccc2 like 'a%'))条件推给t2
```SQL
greatsql> EXPLAIN SELECT * FROM t2,t1,t3 WHERE t1.c1=t2.cc1 AND t1.c2=t3.ccc1 OR t3.ccc1 IS NULL OR t3.ccc2 LIKE 'a%';
| -> Filter: (((t3.ccc1 = t1.c2) and (t2.cc1 = t1.c1)) or (t3.ccc1 is null) or (t3.ccc2 like 'a%')) (cost=14.27 rows=85)
-> Inner hash join (no condition) (cost=14.27 rows=85)
-> Index scan on t2 using idx2_1 (cost=0.09 rows=5)
-> Hash
-> Filter: ((t3.ccc1 = t1.c2) or (t3.ccc1 is null) or (t3.ccc2 like 'a%')) (cost=4.70 rows=17)
-> Inner hash join (no condition) (cost=4.70 rows=17)
-> Table scan on t3 (cost=0.07 rows=5)
-> Hash
-> Index scan on t1 using idx2 (cost=0.95 rows=7)
```
二、make_join_query_block代码解释
make_join_query_block函数通过join表顺序和每张表的table_map属性以及cond条件的属性来决定cond条件添加到哪张表,并且可能会重新对表的索引进行check找出cost更低的索引，下面是代码解析。
```c++
bool JOIN::optimize() {
make_join_query_block();
}
static bool make_join_query_block(JOIN *join, Item *cond) {
for (uint i = join->const_tables; i < join->tables; i++) {
// 这四个变量说明见表一
JOIN_TAB *const tab = join->best_ref[i];
const plan_idx first_inner = tab->first_inner();
const table_map used_tables = tab->prefix_tables();
const table_map current_map = tab->added_tables();
if (cond)
// 这里通过table_map属性决定了是否给这个表添加条件，见下面表二、表四和表五说明
tmp = make_cond_for_table(thd, cond, used_tables, current_map, false);
// 如果recheck_reason=true，这里需要重新做一次确认，找出cost最低的索引。见表六
if (recheck_reason)
test_if_order_by_key();
test_if_cheaper_ordering();
test_quick_select();
}
/* Add conditions added by add_not_null_conds(). */
if (and_conditions(&tmp, tab->condition())) return true;
if (join->attach_join_conditions(i)) return true;
}
}
// 条件添加基本原则是条件带有表列的添加到该表，但是如果属性不一致的话也不会添加，只会添加到最后一张表。具体解释见下面实际例子。
```
表一：上面四个变量解释
| 变量 | 解释 | 说明 |
|---|---|---|
| tab | 当前检测是否需要加条件的表 | 这里是排序后的表 |
| first_inner | 多张表join的时候排序后的第一张表 | 例如：SELECT * FROM t1 LEFT OUTER JOIN (t2 JOIN t3) ON X |
| used_tables | 包括当前表以及之前的左连接表的信息 | 如果该值=0代表所有表，used_tables信息在之前的set_prefix_tables()获得 |
| current_map | 当前检测表信息 | 包含被添加的一些信息 |
表二：make_cond_for_table()动作
| 场景 | 解释 | 返回 |
|---|---|---|
| AND条件 | 遍历Item_cond包含的所有list，判断list是否有包含该表左连接的表的列，有的话加入新的Item_cond_and | new Item_cond_and |
| OR条件 | 遍历Item_cond包含的所有list，判断list是否有包含该表左连接的表的列，有的话加入新的Item_cond_or | new Item_cond_or |
| 其他Item | 如果条件没有涉及左连接的表，或者给所有表添加条件cond->is_expensive() | nullptr(无条件) |
| 其他Item | 如果条件涉及左连接的表并且item与表的属性一致(见表四) | 该Item条件 |
表三：is_expensive_processor()函数
| Item | is_expensive | 说明 |
|---|---|---|
| Item_udf_func | true | 这个Item作为条件不会添加到所有join表里面 |
| Item_func_sp | true | 这个Item作为条件不会添加到所有join表里面 |
| 普通Item | false | 这个Item作为条件可能会添加到join表里面 |
表四：Item的table_map属性
| table_map | 使用的Item | 说明 | 举例 |
|---|---|---|---|
| INNER_TABLE_BIT | Item_trigger_field，Item_sp_variable，Item_param，Item_func_connection_id，Item_func_get_system_var，Item_func_user，Item_load_file, Item_func_sp，Item_func_get_user_var | 确定的常量，在表每行都一样 | f1(1)@@optimizer_search_depth |
| OUTER_REF_TABLE_BIT | Item_field,Item_ref,Item_view_ref,Item_outer_ref | 内部field需要外部query block的item信息 | select (select t1.a from t1 as t2 limit 1) from t1 group by t1.pk，t1.a就是OUTER_REF_TABLE_BIT |
| RAND_TABLE_BIT | Item_func_rand,Item_func_sleep,Item_func_uuid,Item_func_sysdate_local，Item_func_reject_if,Item_func_sp，Item_func_get_user_var | 不确定的，表每行都要换数据，非只有一行表，跟INNER_TABLE_BIT相反 | f1(t1.c1) |
| PSEUDO_TABLE_BITS | 包含以上三个 |
表五：表连接添加的属性
| table_map | 第一张表 | 中间的表 | 最后一张表 |
|---|---|---|---|
| INNER_TABLE_BIT | yes | 无 | 无 |
| OUTER_REF_TABLE_BIT | allow_outer_refs | 无 | 无 |
| RAND_TABLE_BIT | 无 | 无 | yes |
表六：表的索引是否要重新check
| recheck_reason | 说明 |
|---|---|
| DONT_RECHECK | 没有索引的表不需要重新check |
| NOT_FIRST_TABLE | 不是第一张表需要重新check |
| LOW_LIMIT | 带有limit语句的sql需要重新check |
三、实际例子说明
接下来看几个例子来说明上面的代码。
首先看一下最后确定的连接顺序，为t1,t3,t2，因为条件不带有RAND_TABLE_BIT的Item，因此最后是按照cond含有的列推送给对应表来实现的。
例子一：
```sql
greatsql> EXPLAIN SELECT * FROM t2,t1,t3 WHERE t1.c1=t2.cc1 AND t1.c2=t3.ccc1 OR t3.ccc1 IS NULL OR t3.ccc2 LIKE 'a%';
+----+-------------+-------+------------+-------+-------------------+--------+---------+------+------+----------+---------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-------------------+--------+---------+------+------+----------+---------------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | index | PRIMARY,idx1,idx2 | idx2 | 11 | NULL | 7 | 100.00 | Using index |
| 1 | SIMPLE | t3 | NULL | ALL | idx3_1 | NULL | NULL | NULL | 5 | 48.80 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | t2 | NULL | index | PRIMARY | idx2_1 | 5 | NULL | 5 | 100.00 | Using where; Using index; Using join buffer (hash join) |
+----+-------------+-------+------------+-------+-------------------+--------+---------+------+------+----------+---------------------------------------------------------+
```
表一：是否把cond条件推送给表
| COND | t1 | [t1,]t3 | [t1,t3,]t2 |
|---|---|---|---|
| (t1.c1=t2.cc1) | no | no | yes |
| (t3.ccc1 = t1.c2) | no | yes | yes |
| (t3.ccc1 is null) | no | yes | yes |
| (t3.ccc2 like 'a%') | no | yes | yes |
注：这里的中括号代表当前检测表的左连接表，中括号右边就是当前正在检测的表
表二：表的table_map值
| COND | t1 | t3 | t2 |
|---|---|---|---|
| best_ref->table_ref->map() | 0x010 | 0x100 | 0x001 |
| best_ref->prefix_tables() | INNER_TABLE_BIT+ OUTER_REF_TABLE_BIT+0x010 | INNER_TABLE_BIT+ OUTER_REF_TABLE_BIT+0x010+0x100 | INNER_TABLE_BIT+OUTER_REF_TABLE_BIT +RAND_TABLE_BIT+0x010+0x100+0x001 |
| best_ref->added_tables() | INNER_TABLE_BIT+ OUTER_REF_TABLE_BIT+0x010 | 0x100 | RAND_TABLE_BIT+0x001 |
注：这里的INNER_TABLE_BIT和OUTER_REF_TABLE_BIT在函数JOIN::set_prefix_tables()默认加上了
看一下结果是否符合预期，确实如上表所述。这里看到又执行了一次test_quick_select()来确定走哪个索引。
```sql
"attaching_conditions_to_tables": {
"original_condition": "(((`t3`.`ccc1` = `t1`.`c2`) and (`t2`.`cc1` = `t1`.`c1`)) or (`t3`.`ccc1` is null) or (`t3`.`ccc2` like 'a%'))",
"attached_conditions_computation": [
{
"table": "`t2`",
"rechecking_index_usage": { 这里对索引重新做了一次check
"recheck_reason": "not_first_table",
"range_analysis": {
"table_scan": {
"rows": 5,
"cost": 3.6
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"cc1"
]
},
{
"index": "idx2_1",
"usable": false,
"cause": "not_applicable"
}
],
"best_covering_index_scan": {
"index": "idx2_1",
"cost": 0.751098,
"chosen": true
},
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
},
"skip_scan_range": {
"chosen": false,
"cause": "not_single_table"
}
}
}
}
],
"attached_conditions_summary": [
{
"table": "`t1`",
"attached": null
},
{
"table": "`t3`",
"attached": "((`t3`.`ccc1` = `t1`.`c2`) or (`t3`.`ccc1` is null) or (`t3`.`ccc2` like 'a%'))"
},
{
"table": "`t2`",
"attached": "(((`t3`.`ccc1` = `t1`.`c2`) and (`t2`.`cc1` = `t1`.`c1`)) or (`t3`.`ccc1` is null) or (`t3`.`ccc2` like 'a%'))"
}
]
}
},
{
"finalizing_table_conditions": [
{
"table": "`t3`",
"original_table_condition": "((`t3`.`ccc1` = `t1`.`c2`) or (`t3`.`ccc1` is null) or (`t3`.`ccc2` like 'a%'))",
"final_table_condition ": "((`t3`.`ccc1` = `t1`.`c2`) or (`t3`.`ccc1` is null) or (`t3`.`ccc2` like 'a%'))"
},
{
"table": "`t2`",
"original_table_condition": "(((`t3`.`ccc1` = `t1`.`c2`) and (`t2`.`cc1` = `t1`.`c1`)) or (`t3`.`ccc1` is null) or (`t3`.`ccc2` like 'a%'))",
"final_table_condition ": "(((`t3`.`ccc1` = `t1`.`c2`) and (`t2`.`cc1` = `t1`.`c1`)) or (`t3`.`ccc1` is null) or (`t3`.`ccc2` like 'a%'))"
}
]
},
{
"refine_plan": [
{
"table": "`t1`"
},
{
"table": "`t3`"
},
{
"table": "`t2`"
}
]
}
]
}
}
```
如果条件带有RAND_TABLE_BIT的Item，那么即使cond带有表的列，也不会推送给对应的表，而是推送到最后一张表。看下面的t1.c1 < rand()这个条件。
例子二：
```sql
greatsql> SELECT * FROM t2,t1,t3 WHERE t1.c1=t2.cc1 AND t1.c2=t3.ccc1 OR t3.ccc1 IS NULL AND t1.c1 < rand();
"attached_conditions_summary": [
{
"table": "`t1`",
"attached": null
},
{
"table": "`t3`",
"attached": "((`t3`.`ccc1` = `t1`.`c2`) or (`t3`.`ccc1` is null))"
},
{
"table": "`t2`",
"attached": "(((`t3`.`ccc1` = `t1`.`c2`) and (`t2`.`cc1` = `t1`.`c1`)) or ((`t3`.`ccc1` is null) and (`t1`.`c1` < rand())))" 看到条件t1.c1 < rand()没有推送给t1而是推送到最后一张表t2去了
}
]
}
},
{
"finalizing_table_conditions": [
{
"table": "`t3`",
"original_table_condition": "((`t3`.`ccc1` = `t1`.`c2`) or (`t3`.`ccc1` is null))",
"final_table_condition ": "((`t3`.`ccc1` = `t1`.`c2`) or (`t3`.`ccc1` is null))"
},
{
"table": "`t2`",
"original_table_condition": "(((`t3`.`ccc1` = `t1`.`c2`) and (`t2`.`cc1` = `t1`.`c1`)) or ((`t3`.`ccc1` is null) and (`t1`.`c1` < rand())))",
"final_table_condition ": "(((`t3`.`ccc1` = `t1`.`c2`) and (`t2`.`cc1` = `t1`.`c1`)) or ((`t3`.`ccc1` is null) and (`t1`.`c1` < rand())))"
}
]
},
{
"refine_plan": [
{
"table": "`t1`"
},
{
"table": "`t3`"
},
{
"table": "`t2`"
}
```
看一下每张表的属性：
| COND | t1 | t3 | t2 |
|---|---|---|---|
| best_ref->table_ref->map() | 0x010 | 0x100 | 0x001 |
| best_ref->prefix_tables() | INNER_TABLE_BIT+ OUTER_REF_TABLE_BIT+0x010 | INNER_TABLE_BIT+ OUTER_REF_TABLE_BIT+0x010+0x100 | INNER_TABLE_BIT+OUTER_REF_TABLE_BIT +RAND_TABLE_BIT+0x010+0x100+0x001 |
| best_ref->added_tables() | INNER_TABLE_BIT+ OUTER_REF_TABLE_BIT+0x010 | 0x100 | RAND_TABLE_BIT+0x001 |
四、总结
从上面优化器最早的步骤我们认识了make_join_query_block函数的作用，知道了通过join表顺序和每张表的table_map属性以及cond条件的属性来决定cond条件添加到哪张表,并且可能会重新对表的索引进行check找出cost更低的索引，需要注意的是有的带有表列的条件不会被添加到对应表，因为Item的属性跟表的属性不一致所以最后只会被添加到最后一张join表。
Enjoy GreatSQL 😃
关于 GreatSQL
GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。
相关链接： GreatSQL社区 Gitee GitHub Bilibili
GreatSQL社区：
社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html
技术交流群：
微信：扫码添加
GreatSQL社区助手微信好友，发送验证信息加群。
文章整理自互联网，只做测试使用。发布者：Lomu，转转请注明出处：https://www.it1024doc.com/6173.html
