【GreatSQL优化器-11】finalize_table_conditions
【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以后得到的结果，这里条件被删除了
}
]
},表一：reduce_cond_for_table操纵
Item类型操纵结果Item_func::COND_AND_FUNC递归reduce_cond_for_table()为空的话删除，与cond不同的话替换Item_func::COND_OR_FUNC递归reduce_cond_for_table()与cond不同的话替换Item_func::TRIG_COND_FUNC递归reduce_cond_for_table()与cond不同的话替换Item_func::EQ_FUNC通过test_if_ref()判断该条件是否使用ref方式扫描※重要true返回空，false返回原始cond其他类型不操纵直接返回原始cond表二：不能生成Item cache的Item
序号Item类型1常数类型2表的列3子查询4ROW对象5prepare的参数6已经被cache了三、实际例子说明
接下来看几个例子来说明上面的代码：
greatsql > EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 ON t1.c1=t2.cc1 AND t1.c1=t3.ccc1 AND t3.ccc1 SELECT * FROM t1 JOIN t2 JOIN t3 ON t1.c1=t2.cc1 AND t1.c1=t3.ccc1 WHERE t1.c2 SELECT * FROM t1 JOIN t2 JOIN t3 ON t1.c1=t2.cc1 AND t1.c1=t3.ccc1 WHERE t1.c2
页:
[1]