【GreatSQL 优化器 -11】finalize_table_conditions
作者：GreatSQL
- 2025-01-15 福建
本文字数：5767 字
阅读完需：约 19 分钟
【GreatSQL 优化器-11】finalize_table_conditions
一、finalize_table_conditions 介绍
GreatSQL 的优化器在对 join 做完表排序后，在make_join_query_block函数对表添加条件，添加完条件在finalize_table_conditions会对条件再次进行确认，对 ref 扫描的条件进行删除，对需要 cache 的条件进行替换，生成的条件就是表执行查询最后用的条件。
下面用一个简单的例子来说明finalize_table_conditions做什么事情。
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);
greatsql > EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 ON t1.c1=t2.cc1 AND t1.c1=t3.ccc1 AND t3.ccc1<5;+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+| 1 | SIMPLE | t1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 3 | 100.00 | Using where || 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | db1.t1.c1 | 1 | 100.00 | NULL || 1 | SIMPLE | t3 | NULL | ref | idx3_1 | idx3_1 | 5 | db1.t1.c1 | 1 | 100.00 | NULL |+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ { "attaching_conditions_to_tables": { "original_condition": "((`t2`.`cc1` = `t1`.`c1`) and (`t3`.`ccc1` = `t1`.`c1`) and (`t1`.`c1` < 5))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`c1` < 5)" }, { "table": "`t2`", "attached": "(`t2`.`cc1` = `t1`.`c1`)" }, { "table": "`t3`", "attached": "(`t3`.`ccc1` = `t1`.`c1`)" } ] } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`c1` < 5)", "final_table_condition ": "(`t1`.`c1` < 5)" }, { "table": "`t2`", "original_table_condition": "(`t2`.`cc1` = `t1`.`c1`)", 原始添加的条件 "final_table_condition ": null 经过finalize_table_conditions以后得到的结果，这里条件被删除了 }, { "table": "`t3`", "original_table_condition": "(`t3`.`ccc1` = `t1`.`c1`)", 原始添加的条件 "final_table_condition ": null 经过finalize_table_conditions以后得到的结果，这里条件被删除了 } ] },
复制代码
二、finalize_table_conditions 代码解释
finalize_table_conditions的操作在优化器的中后阶段，用来对之前生成的每张表的条件进行替换或者删除。
bool JOIN::optimize(bool finalize_access_paths) { if (finalize_table_conditions(thd)) return true;}
bool JOIN::finalize_table_conditions(THD *thd) { // 遍历之前已经排序好的表，找到每张表的条件，然后进行裁剪 for (uint i = const_tables; i < tables; i++) { Item *condition = best_ref[i]->condition(); if (condition == nullptr) continue; // 这里进行条件删减，操作见下面表一 reduce_cond_for_table(); if (condition != nullptr) { condition = condition->compile( // 这个函数确认cond条件是否需要cache，true的话给carg->cache_item赋值，以便下面函数生成对应的Item_cache // 如果条件属性是INNER_TABLE_BIT并且不满足表二的话需要创建对应的Item_cache &Item::cache_const_expr_analyzer, (uchar **)&analyzer_arg, // 这个函数对于需要Item_cache的Item生成对应的Item_cache &Item::cache_const_expr_transformer, (uchar *)&cache_arg); trace_cond.add("final_table_condition ", condition); } }}
复制代码
表一：reduce_cond_for_table 操作
表二：不能生成 Item cache 的 Item
三、实际例子说明
接下来看几个例子来说明上面的代码：
greatsql > EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 ON t1.c1=t2.cc1 AND t1.c1=t3.ccc1 AND t3.ccc1<5; { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "rows_to_scan": 7, "filtering_effect": [ ], "final_filtering_effect": 0.428571, "access_type": "scan", "resulting_rows": 3, "cost": 1.7, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 3, "cost_for_plan": 1.7, "rest_of_plan": [ { "plan_prefix": [ "`t1`" ], "table": "`t2`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", 确定t2使用了ref方式扫描并且用到了主键索引 "index": "PRIMARY", "rows": 1, "cost": 3.3, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "range", "range_details": { "used_index": "PRIMARY" }, "chosen": false, "cause": "heuristic_index_cheaper" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 3, "cost_for_plan": 5, "rest_of_plan": [ { "plan_prefix": [ "`t1`", "`t2`" ], "table": "`t3`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", 确定t3使用了ref方式扫描并且用到了idx3_1索引 "index": "idx3_1", "rows": 1, "cost": 1.05, "chosen": true }, { "access_type": "range", "range_details": { "used_index": "idx3_1" }, "cost": 2.06, "rows": 4, "chosen": false, "cause": "cost" } ] }, "added_to_eq_ref_extension": false }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`c1` < 5)", "final_table_condition ": "(`t1`.`c1` < 5)" }, { "table": "`t2`", "original_table_condition": "(`t2`.`cc1` = `t1`.`c1`)", 这里发现t2.cc1等号条件用到了ref方式扫描，因此被裁剪了 "final_table_condition ": null 条件被删除 }, { "table": "`t3`", "original_table_condition": "(`t3`.`ccc1` = `t1`.`c1`)", 这里发现t3.ccc1等号条件用到了ref方式扫描，因此被裁剪了 "final_table_condition ": null 条件被删除 } ] }, { "refine_plan": [ { "table": "`t1`" }, { "table": "`t2`" }, { "table": "`t3`" } ]
复制代码
下面加一个带有 INNER_TABLE_BIT 属性的 Item 条件，看看条件转换后的结果。
greatsql> SELECT * FROM t1 JOIN t2 JOIN t3 ON t1.c1=t2.cc1 AND t1.c1=t3.ccc1 WHERE t1.c2<@@optimizer_search_depth; { "attaching_conditions_to_tables": { "original_condition": "((`t2`.`cc1` = `t1`.`c1`) and (`t3`.`ccc1` = `t1`.`c1`) and (`t1`.`c2` < ))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`c2` < )" }, { "table": "`t2`", "attached": "(`t2`.`cc1` = `t1`.`c1`)" }, { "table": "`t3`", "attached": "(`t3`.`ccc1` = `t1`.`c1`)" } ] } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`c2` < )", "final_table_condition ": "(`t1`.`c2` < <cache>())" 这里看到条件里面的系统变量被转变为cache了 }, { "table": "`t2`", "original_table_condition": "(`t2`.`cc1` = `t1`.`c1`)", "final_table_condition ": null }, { "table": "`t3`", "original_table_condition": "(`t3`.`ccc1` = `t1`.`c1`)", "final_table_condition ": null } ] },
复制代码
以下例子也会把条件转换为 cache，因为 f1(1)是 INNER_TABLE_BIT 属性，如果改为 f1(t1.c2)就不能转为 cache 了，因为 f1(t1.c2)是 NO DETERMINISTIC 不确定的，非 INNER_TABLE_BIT 属性。
SET GLOBAL log_bin_trust_function_creators=1;SET sql_mode=ORACLE;DELIMITER $$CREATE OR REPLACE FUNCTION f1 (id int) RETURN INT DETERMINISTIC ISBEGIN RETURN id;END;$$DELIMITER ;greatsql> SELECT * FROM t1 JOIN t2 JOIN t3 ON t1.c1=t2.cc1 AND t1.c1=t3.ccc1 WHERE t1.c2<f1(1);
复制代码
四、总结
从上面优化器的步骤我们认识了finalize_table_conditions函数的使用方法，也知道了什么时候表的条件需要进行删除或者转换，最后学会了 Item cache 的生成条件。到这里一个优化器的工作快要结束了，最后还有一个临时表需要创建，这个下一期讲。
划线
评论
复制
发布于: 刚刚阅读数: 5
版权声明: 本文为 InfoQ 作者【GreatSQL】的原创文章。
原文链接:【http://xie.infoq.cn/article/c2b1e2ef532ea31e38d47584a】。文章转载请联系作者。
GreatSQL
关注
GreatSQL社区 2023-01-31 加入
GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。 社区：https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL
评论