【GreatSQL优化器-08】statistics和index dives

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

【GreatSQL优化器-08】statistics和index dives

一、statistics和index_dives介绍

GreatSQL的优化器对于查询条件带有范围的情况，需要根据 mm tree 来估计该范围内大概有多少行，然后以此来计算cost。对于等号条件，给出了两种方法来估计对应行数--Statistics和index dives，前者不精确后者精确，可以通过系统变量eq_range_index_dive_limit设置阈值来决定用哪种方法来估计等号条件的行数。对于一条查询 SQL 如果等号条件太多的时候执行index dives会占用较多资源，这时候设置一个合理的阈值改为统计值估计可以有效避免占用过多资源，提升执行效率。

名称	定义	说明
Statistics	用统计值来估计等号条件的行数，不精确	意味着SKIP_RECORDS_IN_RANGE模式，计算方式： rows = table->key_info[keyno].records_per_key()
index dives	精确计算等号条件的行数	意味着RECORDS_IN_RANGE，计算方式： rows = this->records_in_range()

下面用一个简单的例子来说明index dives是什么：

greatsql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT,date1 DATETIME);
greatsql> 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');
greatsql> CREATE TABLE t2 (cc1 INT PRIMARY KEY, cc2 INT);
greatsql> INSERT INTO t2 VALUES (1,3),(2,1),(3,2),(4,3),(5,15);
greatsql> CREATE TABLE t3 (ccc1 INT, ccc2 varchar(100));
greatsql> INSERT INTO t3 VALUES (1,'aa1'),(2,'bb1'),(3,'cc1'),(4,'dd1'),(null,'ee');
greatsql> CREATE INDEX idx1 ON t1(c2);
greatsql> CREATE INDEX idx2 ON t1(c2,date1);
greatsql> CREATE INDEX idx2_1 ON t2(cc2);
greatsql> CREATE INDEX idx3_1 ON t3(ccc1);

greatsql> SELECT * FROM t1 WHERE (c1=4 AND c2<10) OR (c2=4 AND c1<9) OR (c2=10 AND c1<9) OR (c2=12 AND c1=8) OR (c2<8 AND date1<'2023-03-25 16:44:00.123456') OR (c2<15 AND date1<'2023-03-25 16:44:00.123456');
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx1",
                        "ranges": [
                          "NULL < c2 < 8",
                          "8 <= c2 < 10",
                          "c2 = 10",
                          "10 < c2 < 12",
                          "c2 = 12",
                          "12 < c2 < 15"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "in_memory": 1,
                        "rows": 9, # 这里的值包含c2 = 10和c2 = 12根据index dive估算出来的值
                        "cost": 4.66,
                        "chosen": false,
                        "cause": "cost"
                      },
                      {
                        "index": "idx2",
                        "ranges": [
                          "NULL < c2 < 4",
                          "c2 = 4",
                          "4 < c2 < 8",
                          "8 <= c2 < 10",
                          "c2 = 10",
                          "10 < c2 < 12",
                          "c2 = 12",
                          "12 < c2 < 15"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false