【GreatSQL优化器-09】make_join_query_block

徐锦洪  金牌会员 | 2025-1-8 13:45:44 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 976|帖子 976|积分 2928

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要 登录 才可以下载或查看，没有账号？立即注册

x
【GreatSQL优化器-09】make_join_query_block

一、make_join_query_block先容

GreatSQL优化器对于多张表join的连接顺序在前面的章节先容过的best_access_path函数已经执行了，接着就是把where条件举行切割然后推给合适的表。这个过程就是由函数make_join_query_block来执行的。
下面用几个简朴的例子来说明join连接中条件推送是什么。
  1. CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT,date1 DATETIME);
  2. 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');
  3. CREATE TABLE t2 (cc1 INT PRIMARY KEY, cc2 INT);
  4. INSERT INTO t2 VALUES (1,3),(2,1),(3,2),(4,3),(5,15);
  5. CREATE TABLE t3 (ccc1 INT, ccc2 varchar(100));
  6. INSERT INTO t3 VALUES (1,'aa1'),(2,'bb1'),(3,'cc1'),(4,'dd1'),(null,'ee');
  7. CREATE INDEX idx1 ON t1(c2);
  8. CREATE INDEX idx2 ON t1(c2,date1);
  9. CREATE INDEX idx2_1 ON t2(cc2);
  10. CREATE INDEX idx3_1 ON t3(ccc1);
复制代码
下面这个例子((t1.c1 = t3.ccc1) or (t3.ccc1 < 3))条件推送给t1
  1. greatsql> EXPLAIN FORMAT=TREE SELECT * FROM t1 join t3 ON t1.c1=t3.ccc1 or t3.ccc1<3;
  2. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | EXPLAIN                                                                                                                                                                                                                                                         |
  4. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | -> Filter: ((t1.c1 = t3.ccc1) or (t3.ccc1 < 3))  (cost=5.26 rows=35)
  6.     -> Inner hash join (no condition)  (cost=5.26 rows=35)
  7.         -> Index scan on t1 using idx2  (cost=0.34 rows=7)
  8.         -> Hash
  9.             -> Table scan on t3  (cost=0.75 rows=5)
  10. |
  11. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  12. 1 row in set (0.00 sec)
复制代码
下面例子(t1.c1 < 3)条件推给t1，(ccc1=t1.c1)条件推给t3
  1. greatsql> EXPLAIN FORMAT=TREE SELECT * FROM t1 join t3 ON t1.c1=t3.ccc1 and t3.ccc1<3;
  2. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | EXPLAIN                                                                                                                                                                                                                          |
  4. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | -> Nested loop inner join  (cost=2.40 rows=2)
  6.     -> Filter: (t1.c1 < 3)  (cost=1.70 rows=2)
  7.         -> Index scan on t1 using idx2  (cost=1.70 rows=7)
  8.     -> Index lookup on t3 using idx3_1 (ccc1=t1.c1)  (cost=0.30 rows=1)
  9. |
  10. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
复制代码
下面例子((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
  1. 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%';
  2. | -> 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)
  3.     -> Inner hash join (no condition)  (cost=14.27 rows=85)
  4.         -> Index scan on t2 using idx2_1  (cost=0.09 rows=5)
  5.         -> Hash
  6.             -> Filter: ((t3.ccc1 = t1.c2) or (t3.ccc1 is null) or (t3.ccc2 like 'a%'))  (cost=4.70 rows=17)
  7.                 -> Inner hash join (no condition)  (cost=4.70 rows=17)
  8.                     -> Table scan on t3  (cost=0.07 rows=5)
  9.                     -> Hash
  10.                         -> 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更低的索引，下面是代码解析。
  1. bool JOIN::optimize() {
  2.   make_join_query_block();
  3. }
  5. static bool make_join_query_block(JOIN *join, Item *cond) {
  6.   for (uint i = join->const_tables; i < join->tables; i++) {
  7.       // 这四个变量说明见表一
  8.       JOIN_TAB *const tab = join->best_ref[i];
  9.       const plan_idx first_inner = tab->first_inner();
  10.       const table_map used_tables = tab->prefix_tables();
  11.       const table_map current_map = tab->added_tables();
  12.       if (cond)
  13.         // 这里通过table_map属性决定了是否给这个表添加条件，见下面表二、表四和表五说明
  14.         tmp = make_cond_for_table(thd, cond, used_tables, current_map, false);
  15.      // 如果recheck_reason=true，这里需要重新做一次确认，找出cost最低的索引。见表六
  16.       if (recheck_reason)
  17.         test_if_order_by_key();
  18.         test_if_cheaper_ordering();
  19.         test_quick_select();
  20.       }  
  21.       /* Add conditions added by add_not_null_conds(). */
  22.       if (and_conditions(&tmp, tab->condition())) return true;
  23.       if (join->attach_join_conditions(i)) return true;
  24.   }
  25. }
  26. // 条件添加基本原则是条件带有表列的添加到该表，但是如果属性不一致的话也不会添加，只会添加到最后一张表。具体解释见下面实际例子。
复制代码
表一：上面四个变量解释
变量解释说明tab当前检测是否需要加条件的表这里是排序后的表first_inner多张表join的时候排序后的第一张表比方：SELECT * FROM t1 LEFT OUTER JOIN (t2 JOIN t3) ON Xused_tables包括当前表以及之前的左连接表的信息如果该值=0代表所有表，used_tables信息在之前的set_prefix_tables()获得current_map当前检测表信息包罗被添加的一些信息表二：make_cond_for_table()动作
场景解释返回AND条件遍历Item_cond包罗的所有list，判断list是否有包罗该表左连接的表的列，有的话到场新的Item_cond_andnew Item_cond_andOR条件遍历Item_cond包罗的所有list，判断list是否有包罗该表左连接的表的列，有的话到场新的Item_cond_ornew Item_cond_or其他Item如果条件没有涉及左连接的表，或者给所有表添加条件cond->is_expensive()nullptr(无条件)其他Item如果条件涉及左连接的表而且item与表的属性一致(见表四)该Item条件表三：is_expensive_processor()函数
Itemis_expensive说明Item_udf_functrue这个Item作为条件不会添加到所有join表内里Item_func_sptrue这个Item作为条件不会添加到所有join表内里普通Itemfalse这个Item作为条件可能会添加到join表内里表四：Item的table_map属性
table_map利用的Item说明举例INNER_TABLE_BITItem_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_depthOUTER_REF_TABLE_BITItem_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_BITRAND_TABLE_BITItem_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_BITyes无无OUTER_REF_TABLE_BITallow_outer_refs无无RAND_TABLE_BIT无无yes表六：表的索引是否要重新check
recheck_reason说明DONT_RECHECK没有索引的表不需要重新checkNOT_FIRST_TABLE不是第一张表需要重新checkLOW_LIMIT带有limit语句的sql需要重新check三、实际例子说明

接下来看几个例子来说明上面的代码。
首先看一下最后确定的连接顺序，为t1,t3,t2，因为条件不带有RAND_TABLE_BIT的Item，因此最后是按照cond含有的列推送给对应表来实现的。
例子一：
  1. 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%';
  2. +----+-------------+-------+------------+-------+-------------------+--------+---------+------+------+----------+---------------------------------------------------------+
  3. | id | select_type | table | partitions | type  | possible_keys     | key    | key_len | ref  | rows | filtered | Extra                                                   |
  4. +----+-------------+-------+------------+-------+-------------------+--------+---------+------+------+----------+---------------------------------------------------------+
  5. |  1 | SIMPLE      | t1    | NULL       | index | PRIMARY,idx1,idx2 | idx2   | 11      | NULL |    7 |   100.00 | Using index                                             |
  6. |  1 | SIMPLE      | t3    | NULL       | ALL   | idx3_1            | NULL   | NULL    | NULL |    5 |    48.80 | Using where; Using join buffer (hash join)              |
  7. |  1 | SIMPLE      | t2    | NULL       | index | PRIMARY           | idx2_1 | 5       | NULL |    5 |   100.00 | Using where; Using index; Using join buffer (hash join) |
  8. +----+-------------+-------+------------+-------+-------------------+--------+---------+------+------+----------+---------------------------------------------------------+
复制代码
表一：是否把cond条件推送给表
CONDt1[t1,]t3[t1,t3,]t2(t1.c1=t2.cc1)nonoyes(t3.ccc1 = t1.c2)noyesyes(t3.ccc1 is null)noyesyes(t3.ccc2 like 'a%')noyesyes
注：这里的中括号代表当前检测表的左连接表，中括号右边就是当前正在检测的表
表二：表的table_map值
CONDt1t3t2best_ref->table_ref->map()0x0100x1000x001best_ref->prefix_tables()INNER_TABLE_BIT+ OUTER_REF_TABLE_BIT+0x010INNER_TABLE_BIT+ OUTER_REF_TABLE_BIT+0x010+0x100INNER_TABLE_BIT+OUTER_REF_TABLE_BIT +RAND_TABLE_BIT+0x010+0x100+0x001best_ref->added_tables()INNER_TABLE_BIT+ OUTER_REF_TABLE_BIT+0x0100x100RAND_TABLE_BIT+0x001
注：这里的INNER_TABLE_BIT和OUTER_REF_TABLE_BIT在函数JOIN::set_prefix_tables()默认加上了
看一下结果是否符合预期，确实如上表所述。这里看到又执行了一次test_quick_select()来确定走哪个索引。
  1. "attaching_conditions_to_tables": {
  2.               "original_condition": "(((`t3`.`ccc1` = `t1`.`c2`) and (`t2`.`cc1` = `t1`.`c1`)) or (`t3`.`ccc1` is null) or (`t3`.`ccc2` like 'a%'))",
  3.               "attached_conditions_computation": [
  4.                 {
  5.                   "table": "`t2`",
  6.                   "rechecking_index_usage": { 这里对索引重新做了一次check
  7.                     "recheck_reason": "not_first_table",
  8.                     "range_analysis": {
  9.                       "table_scan": {
  10.                         "rows": 5,
  11.                         "cost": 3.6
  12.                       },
  13.                       "potential_range_indexes": [
  14.                         {
  15.                           "index": "PRIMARY",
  16.                           "usable": true,
  17.                           "key_parts": [
  18.                             "cc1"
  19.                           ]
  20.                         },
  21.                         {
  22.                           "index": "idx2_1",
  23.                           "usable": false,
  24.                           "cause": "not_applicable"
  25.                         }
  26.                       ],
  27.                       "best_covering_index_scan": {
  28.                         "index": "idx2_1",
  29.                         "cost": 0.751098,
  30.                         "chosen": true
  31.                       },
  32.                       "setup_range_conditions": [
  33.                       ],
  34.                       "group_index_range": {
  35.                         "chosen": false,
  36.                         "cause": "not_single_table"
  37.                       },
  38.                       "skip_scan_range": {
  39.                         "chosen": false,
  40.                         "cause": "not_single_table"
  41.                       }
  42.                     }
  43.                   }
  44.                 }
  45.               ],
  46.               "attached_conditions_summary": [
  47.                 {
  48.                   "table": "`t1`",
  49.                   "attached": null
  50.                 },
  51.                 {
  52.                   "table": "`t3`",
  53.                   "attached": "((`t3`.`ccc1` = `t1`.`c2`) or (`t3`.`ccc1` is null) or (`t3`.`ccc2` like 'a%'))"
  54.                 },
  55.                 {
  56.                   "table": "`t2`",
  57.                   "attached": "(((`t3`.`ccc1` = `t1`.`c2`) and (`t2`.`cc1` = `t1`.`c1`)) or (`t3`.`ccc1` is null) or (`t3`.`ccc2` like 'a%'))"
  58.                 }
  59.               ]
  60.             }
  61.           },
  62.           {
  63.             "finalizing_table_conditions": [
  64.               {
  65.                 "table": "`t3`",
  66.                 "original_table_condition": "((`t3`.`ccc1` = `t1`.`c2`) or (`t3`.`ccc1` is null) or (`t3`.`ccc2` like 'a%'))",
  67.                 "final_table_condition   ": "((`t3`.`ccc1` = `t1`.`c2`) or (`t3`.`ccc1` is null) or (`t3`.`ccc2` like 'a%'))"
  68.               },
  69.               {
  70.                 "table": "`t2`",
  71.                 "original_table_condition": "(((`t3`.`ccc1` = `t1`.`c2`) and (`t2`.`cc1` = `t1`.`c1`)) or (`t3`.`ccc1` is null) or (`t3`.`ccc2` like 'a%'))",
  72.                 "final_table_condition   ": "(((`t3`.`ccc1` = `t1`.`c2`) and (`t2`.`cc1` = `t1`.`c1`)) or (`t3`.`ccc1` is null) or (`t3`.`ccc2` like 'a%'))"
  73.               }
  74.             ]
  75.           },
  76.           {
  77.             "refine_plan": [
  78.               {
  79.                 "table": "`t1`"
  80.               },
  81.               {
  82.                 "table": "`t3`"
  83.               },
  84.               {
  85.                 "table": "`t2`"
  86.               }
  87.             ]
  88.           }
  89.         ]
  90.       }
  91.     }
复制代码
如果条件带有RAND_TABLE_BIT的Item，那么纵然cond带有表的列，也不会推送给对应的表，而是推送到最后一张表。看下面的t1.c1 < rand()这个条件。
例子二：
  1. 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();
  2.               "attached_conditions_summary": [
  3.                 {
  4.                   "table": "`t1`",
  5.                   "attached": null
  6.                 },
  7.                 {
  8.                   "table": "`t3`",
  9.                   "attached": "((`t3`.`ccc1` = `t1`.`c2`) or (`t3`.`ccc1` is null))"
  10.                 },
  11.                 {
  12.                   "table": "`t2`",
  13.                   "attached": "(((`t3`.`ccc1` = `t1`.`c2`) and (`t2`.`cc1` = `t1`.`c1`)) or ((`t3`.`ccc1` is null) and (`t1`.`c1` < rand())))"  看到条件t1.c1 < rand()没有推送给t1而是推送到最后一张表t2去了
  14.                 }
  15.               ]
  16.             }
  17.           },
  18.           {
  19.             "finalizing_table_conditions": [
  20.               {
  21.                 "table": "`t3`",
  22.                 "original_table_condition": "((`t3`.`ccc1` = `t1`.`c2`) or (`t3`.`ccc1` is null))",
  23.                 "final_table_condition   ": "((`t3`.`ccc1` = `t1`.`c2`) or (`t3`.`ccc1` is null))"
  24.               },
  25.               {
  26.                 "table": "`t2`",
  27.                 "original_table_condition": "(((`t3`.`ccc1` = `t1`.`c2`) and (`t2`.`cc1` = `t1`.`c1`)) or ((`t3`.`ccc1` is null) and (`t1`.`c1` < rand())))",
  28.                 "final_table_condition   ": "(((`t3`.`ccc1` = `t1`.`c2`) and (`t2`.`cc1` = `t1`.`c1`)) or ((`t3`.`ccc1` is null) and (`t1`.`c1` < rand())))"
  29.               }
  30.             ]
  31.           },
  32.           {
  33.             "refine_plan": [
  34.               {
  35.                 "table": "`t1`"
  36.               },
  37.               {
  38.                 "table": "`t3`"
  39.               },
  40.               {
  41.                 "table": "`t2`"
  42.               }
复制代码
看一下每张表的属性：
CONDt1t3t2best_ref->table_ref->map()0x0100x1000x001best_ref->prefix_tables()INNER_TABLE_BIT+ OUTER_REF_TABLE_BIT+0x010INNER_TABLE_BIT+ OUTER_REF_TABLE_BIT+0x010+0x100INNER_TABLE_BIT+OUTER_REF_TABLE_BIT +RAND_TABLE_BIT+0x010+0x100+0x001best_ref->added_tables()INNER_TABLE_BIT+ OUTER_REF_TABLE_BIT+0x0100x100RAND_TABLE_BIT+0x001四、总结

从上面优化器最早的步骤我们认识了make_join_query_block函数的作用，知道了通过join表顺序和每张表的table_map属性以及cond条件的属性来决定cond条件添加到哪张表,而且可能会重新对表的索引举行check找出cost更低的索引，需要留意的是有的带有表列的条件不会被添加到对应表，因为Item的属性跟表的属性不一致所以最后只会被添加到最后一张join表。

Enjoy GreatSQL
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

徐锦洪

金牌会员
这个人很懒什么都没写!

楼主热帖

标签云

AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表