【GreatSQL优化器-11】finalize_table_conditions

最新推荐文章于 2025-03-12 21:26:49 发布

GreatSQL社区

最新推荐文章于 2025-03-12 21:26:49 发布

阅读量816

点赞数 11

文章标签： GreatSQL MySQL

本文链接：https://blog.csdn.net/greatsql2021/article/details/145153647

版权

【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":

最低0.47元/天解锁文章