【GreatSQL优化器-17】DYNAMIC RANGE

【GreatSQL优化器-17】DYNAMIC RANGE

一、DYNAMIC RANGE介绍

GreatSQL 的优化器有一种扫描方式是动态范围扫描方式，类似于“已读乱回”模式，这种模式是在表有多个索引的情况下，对驱动表连接的时候部分选择索引的情况。优化器没有找到好的索引可以使用，但发现在知道前面表的列值后，可能会使用某些索引。对于前面表中的每个行组合，优化器检查是否可以使用 range 或 index merge 访问方法来检索行。虽然这不是很快，但比执行完全没有索引的连接要快。

下面用一个简单的例子来说明直方图怎么应用在优化器。

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 t3 ON t1.c1=t3.ccc1 or t1.c2<5;
+----+-------------+-------+------------+------+-------------------+------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys     | key  | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+-------+------------+------+-------------------+------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | t3    | NULL       | ALL  | idx3_1            | NULL | NULL    | NULL |    5 |   100.00 | NULL                                           |
|  1 | SIMPLE      | t1    | NULL       | ALL  | PRIMARY,idx1,idx2 | NULL | NULL    | NULL |    7 |    43.67 | Range checked for each record (index map: 0x7) |
-- 这里的结果出现了Range checked，说明进行了DYNAMIC_RANGE
+----+-------------+-------+------------+------+-------------------+------+---------+------+------+----------+------------------------------------------------+

greatsql> SELECT * FROM t1 join t3 ON t1.c1=t3.ccc1 or t1.c2<5;
+----+------+---------------------+------+------+
| c1 | c2   | date1               | ccc1 | ccc2 |
+----+------+---------------------+------+------+
|  1 |   10 | 2021-03-25 16:44:00 |    1 | aa1  |
|  2 |    1 | 2022-03-26 16:44:00 |    1 | aa1  |
|  3 |    4 | 2023-03-27 16:44:00 |    1 | aa1  |
|  2 |    1 | 2022-03-26 16:44:00 |    2 | bb1  |
|  3 |    4 | 2023-03-27 16:44:00 |    2 | bb1  |
|  2 |    1 | 2022-03-26 16:44:00 |    3 | cc1  |
|  3 |    4 | 2023-03-27 16:44:00 |    3 | cc1  |
|  2 |    1 | 2022-03-26 16:44:00 |    4 | dd1  |
|  3 |    4 | 2023-03-27 16:44:00 |    4 | dd1  |
|  2 |    1 | 2022-03-26 16:44:00 | NULL | ee   |
|  3 |    4 | 2023-03-27 16:44:00 | NULL | ee   |
+----+------+---------------------+------+------+

greatsql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
             "attaching_conditions_to_tables": {
              "original_condition": "((`t1`.`c1` = `t3`.`ccc1`) or (`t1`.`c2` < 5))",
              "attached_conditions_computation": [
                {
                  "table": "`t1`",
                  "rechecking_index_usage": { -- 这里对t1进行了recheck
                    "recheck_reason": "not_first_table",
                    "range_analysis": {
                      "table_scan": {
                        "rows": 7,
                        "cost": 3.05
                      },
                      "potential_range_indexes": [
                        {
                          "index": "PRIMARY",
                          "usable": true,
                          "key_parts": [
                            "c1"
                          ]
                        },
                        {
                          "index": "idx1",
                          "usable": true,
                          "key_parts": [
                            "c2",
                            "c1"
                          ]
                        },
                        {
                          "index": "idx2",
                          "usable": true,
                          "key_parts": [
                            "c2",
                            "date1",
                            "c1"
                          ]
                        }
                      ],
                      "best_covering_index_scan": {
                        "index": "idx2",
                        "cost": 0.952742,
                        "chosen": true
                      },
                      "setup_range_conditions": [
                      ],
                      "group_index_range": {
                        "chosen": false,
                        "cause": "not_single_table"
                      },
                      "skip_scan_range": {
                        "chosen": false,
                        "cause": "not_single_table"
                      },
                      "analyzing_range_alternatives": {
                        "range_scan_alternatives": [
                        ],
                        "analyzing_roworder_intersect": {
                          "usable": false,
                          "cause": "too_few_roworder_scans"
                        }
                      },
                      "analyzing_index_merge_union": [
                        {