【GreatSQL最佳化器-03】查詢開銷估算

GreatSQL發表於2024-11-20
SQL

【GreatSQL最佳化器-03】查詢開銷估算

一、cost和read_time介紹

GreatSQL的最佳化器在建立執行計劃的時候是根據每張表的行數和資料分佈以及讀資料硬碟消耗等資訊來判斷先查詢哪張表後查詢哪張表，要不要使用索引，這些表資源資訊就被稱為cost，俗稱為"開銷"。在這之前已經執行了update_ref_and_keys（參考【GreatSQL最佳化器-02】）和extract_const_tables（參考【GreatSQL最佳化器-01】），拿到了const tables資訊和表的keyuse_array索引資訊，這裡就開始計算單張表掃描的開銷，做一個初步的估計，用來給後面的choose_table_order()搜尋最佳表順序提供原始資料資訊。

最佳化器透過estimate_rowcount函式初步計算單表開銷，這個函式最後會計算出3個重要的資料。

名稱 說明 計算公式
found_records 表的總行數 tab->table()->file->stats.records
read_time 讀取所有資料需要的開銷 io_cost + cpu_cost + import_cost
worst_seeks 掃描全表需要的最差開銷 find_worst_seeks(tab->table(), tab->found_records, tab->read_time)根據上面2項計算得出

下面用一個簡單的例子來說明這三個數字怎麼檢視：

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');
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 INDEX idx1 ON t1(c2);
greatsql> CREATE INDEX idx2 ON t1(c2,date1);
greatsql> CREATE INDEX idx2_1 ON t2(cc2);
greatsql> SET optimizer_trace = 'enabled=ON' ;

greatsql> SELECT * FROM t2,t1,(select 10) t3 where t1.c1=t2.cc2;
+-----+------+----+------+---------------------+----+
| cc1 | cc2  | c1 | c2   | date1               | 10 |
+-----+------+----+------+---------------------+----+
|   3 |    2 |  2 |    1 | 2022-03-26 16:44:00 | 10 |
|   1 |    3 |  3 |    4 | 2023-03-27 16:44:00 | 10 |
|   4 |    3 |  3 |    4 | 2023-03-27 16:44:00 | 10 |
|   2 |    1 |  1 |   10 | 2021-03-25 16:44:00 | 10 |
+-----+------+----+------+---------------------+----+
> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
          {
            "rows_estimation": [
              {
                "table": "`t2`",  -- 按照上面查詢順序t2放第一個
                "table_scan": {
                  "rows": 5, 因為t2非const表，因此這裡顯示的是所有行
                  "cost": 0.25, 這個是t2查詢5條的開銷，
                  "worst_seeks": 2 這裡是另外加上去的，為了檢視方便
                }
              },
              {
                "table": "`t1`", 按照上面查詢順序t1放第二個
                "table_scan": {
                  "rows": 4, 因為t1非const表，因此這裡顯示的是所有行
                  "cost": 0.25,
                  "worst_seeks": 2 這裡是另外加上去的，為了檢視方便
                }
              },
              {
                "table": " `t3`", 按照上面查詢順序t3放第三個
                "rows": 1,
                "cost": 1, ※這裡有疑問，實際的read_time=worst_seeks=0.25，但是程式碼用了固定值1
                "worst_seeks": 0.25 這裡是另外加上去的，為了檢視方便
                "table_type": "system", 因為t3是const表，因此這裡顯示的system
                "empty": false
              }
            ]
          },

附表：代價係數

代價係數 值 說明
ROW_EVALUATE_COST 0.1 掃描一行需要的開銷
KEY_COMPARE_COST 0.05 比較row id需要的開銷
MEMORY_TEMPTABLE_CREATE_COST 1.0 建立臨時表的開銷，等於讀10行
MEMORY_TEMPTABLE_ROW_COST 0.1 讀或寫一行到臨時表
DISK_TEMPTABLE_CREATE_COST 20.0 建立MyISAM表的開銷
DISK_TEMPTABLE_ROW_COST 0.5 按順序生成 MyISAM 行的開銷
MEMORY_BLOCK_READ_COST 0.25 讀一個block從一個memory buffer pool
IO_BLOCK_READ_COST 1.0 從磁碟讀取block

二、estimate_rowcount程式碼執行過程

實際程式碼執行過程如下，其中test_quick_select()函式在下面第三節介紹：

bool JOIN::make_join_plan() {
  if (estimate_rowcount()) return true;
}

bool JOIN::estimate_rowcount() {
        // 遍歷每張表，計算每張表的上面3個值
        for (JOIN_TAB *tab = join_tab; tab < tab_end; tab++) {
            // 計算下面幾個值
        tab->set_records(tab->found_records = tab->table()->file->stats.records);
        const Cost_estimate table_scan_time = tab->table()->file->table_scan_cost();
        tab->read_time = table_scan_time.total_cost();
        tab->worst_seeks =
            find_worst_seeks(tab->table(), tab->found_records, tab->read_time);
                // 這個函式是副功能，用於發現可能用於 GROUP BY 或 DISTINCT 查詢的索引或可能用於 SKIP SCAN 的索引。
                // 主要給skip_scan_keys和const_keys新增可以用的索引
                add_loose_index_scan_and_skip_scan_keys(this, tab);
                // 如果上面計算得到的read_time<= 2.0那就不做快速查詢test_quick_select()直接返回了，但是如果大於的話就要找是否有索引用快速查詢來估算開銷了。
                get_quick_record_count();
        }
}

上面涉及的引數值見下表。

細項 說明 計算公式
table_scan_cost() 引擎層讀表的開銷 ((stats.data_file_length) / IO_SIZE + 2) * table->cost_model()->page_read_cost(1.0)
find_worst_seeks() 全表掃描所需的最多開銷 worst_seeks = min(table->file->worst_seek_times(tab->found_records / 10), tab->read_time * 3); min_worst_seek = table->file->worst_seek_times(2.0); 結果為：std::max(worst_seeks, min_worst_seek);

三、計算單表cost舉例說明

例子1：SELECT * FROM t1 join t2 ON t1.c1=t2.cc1 and t2.cc1=t1.c1 or t1.c1=5;
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 4,
                    "cost": 3.5 這裡算出來的開銷大於2了，因此要走test_quick_select()估算
                  },
                  "potential_range_indexes": [ 這裡開始迴圈t1的所有索引，找出條件涉及的索引
                    {
                      "index": "PRIMARY", 條件涉及到了t1.c1，因此這裡PRIMARY索引被使用
                      "usable": true,
                      "key_parts": [
                        "c1"
                      ]
                    },
                    {
                      "index": "idx1", 條件不涉及idx1，因此這裡idx1索引不被使用
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx2", 條件不涉及idx2，因此這裡idx2索引不被使用
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "best_covering_index_scan": { 透過find_shortest_key()找到的最短的索引，指覆蓋所有需要的資料的聯合索引table->covering_keys
                    "index": "idx2", 這個值為table->covering_keys，聯合索引包含了主鍵資訊
                    "cost": 1.40548, 開銷小於上面的最早算出來的3.5，因此被選擇。這裡io_cost=1.00548，cpu_cost=4行*0.1（讀每行開銷）
                    "chosen": true
                  },
                  "setup_range_conditions": [ 沒有找到mm tree
                  ],
                  "group_index_range": { 不是GROUP語句
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "skip_scan_range": { 沒有配置skip_scan，不需要skip_scan
                    "chosen": false,
                    "cause": "not_single_table"
                  }
                }
              },
              {
                "table": "`t2`",
                "table_scan": {
                  "rows": 5,
                  "cost": 1 表t2算出來的開銷小於2，因此不繼續用快速查詢計算了。
                }
              }
            ]
          },
-- t1選擇索引 idx2，因為cost更小
-- t2的選擇結果需要結合後面的choose_table_order()看，下一期再講
greatsql> EXPLAIN SELECT * FROM t1 join t2 ON t1.c1=t2.cc1 and t2.cc1=t1.c1 or t1.c1=5;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+---------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                                                   |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+---------------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | PRIMARY       | idx2   | 11      | NULL |    4 |   100.00 | Using index                                             |                               這裡選擇了idx2索引掃描，跟上面算出來的結論一致
|  1 | SIMPLE      | t2    | NULL       | index | PRIMARY       | idx2_1 | 5       | NULL |    5 |   100.00 | Using where; Using index; Using join buffer (hash join) |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+---------------------------------------------------------+

看另一個例子：

例子2：SELECT * FROM t1 join t2 ON t1.c1=t2.cc1 and t1.c1<5;
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 4,
                    "cost": 3.5 這裡算出來的開銷大於2了，因此要走test_quick_select()估算
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY", 條件涉及到了t1.c1，因此這裡PRIMARY索引被使用
                      "usable": true,
                      "key_parts": [
                        "c1"
                      ]
                    },
                    {
                      "index": "idx1", 條件不涉及idx1，因此這裡idx1索引不被使用
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx2", 條件不涉及idx2，因此這裡idx2索引不被使用
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "best_covering_index_scan": { 找到聯合索引，包含了主鍵資訊
                    "index": "idx2",
                    "cost": 1.40548,
                    "chosen": true
                  },
                  "setup_range_conditions": [
                  ],
                  "group_index_range": { 不是GROUP語句
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "skip_scan_range": { 沒有配置skip_scan，不需要skip_scan
                    "chosen": false,
                    "cause": "not_single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY", 根據get_best_group_min_max算出來的mm tree找到的最佳索引
                        "ranges": [
                          "c1 < 5"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "in_memory": 0,
                        "rows": 3, 範圍掃描c1 < 5找到的符合的條數3條
                        "cost": 1.31293, 比上面算出來的cost低，因此被選擇
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans" 因為tree->n_ror_scans < 2，所以沒有被選擇
                    }
                  },
                  "chosen_range_access_summary": { 總結上面所有計算的結果得出結論
                    "range_access_plan": {
                      "type": "range_scan", 結論是走索引範圍掃描
                      "index": "PRIMARY",
                      "rows": 3,
                      "ranges": [
                        "c1 < 5"
                      ]
                    },
                    "rows_for_plan": 3, 找到3條資料
                    "cost_for_plan": 1.31293,
                    "chosen": true
                  }
                }
              },
              {
                "table": "`t2`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 5,
                    "cost": 3.6 這裡算出來的開銷大於2了，因此要走test_quick_select()估算
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY", 涉及到主鍵列，因此用到了
                      "usable": true,
                      "key_parts": [
                        "cc1"
                      ]
                    },
                    {
                      "index": "idx2_1", 沒有涉及cc2，因此沒有用到
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "best_covering_index_scan": { 找到的非唯一索引，開銷比上面的小，被選擇
                    "index": "idx2_1",
                    "cost": 1.50439,
                    "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": [
                      {
                        "index": "PRIMARY",
                        "ranges": [
                          "cc1 < 5"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "in_memory": 0,
                        "rows": 4, 根據cc1 < 5條件找到4條資料記錄
                        "cost": 1.4133, 開銷更小，被選擇
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": { 總結上面所有計算的結果得出結論
                    "range_access_plan": {
                      "type": "range_scan",  結論是走索引範圍掃描
                      "index": "PRIMARY",
                      "rows": 4,
                      "ranges": [
                        "cc1 < 5"
                      ]
                    },
                    "rows_for_plan": 4, 找到4條記錄
                    "cost_for_plan": 1.4133,
                    "chosen": true
                  }
                }
              }
            ]
          },

-- 結論：t1表用了範圍掃描，跟上面結論一致
-- t2的選擇結果需要結合後面的best_access_path()看，下一期再講
greatsql> EXPLAIN SELECT * FROM t1 join t2 ON t1.c1=t2.cc1 and t1.c1<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        |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+

四、總結

從上面最佳化器最早的步驟我們認識了最佳化器的cost和計算方式，知道了如何初步估算單表的掃描cost並且按照最小cost選擇最佳索引，這些單表估算出來的cost會在後面greedy_search（貪婪搜尋）的時候用來做為計算依據，然後按照每張表的掃描開銷對錶進行排序，算出哪張表先掃描哪張表後掃描，最後得出最佳執行計劃。

需要注意的是，上面的初步估算cost<=2的時候是不會進行後續快速掃描計算的，因此如果實際運用中想檢視錶的正確cost的話，需要根據當時表的實際資料量來做執行計劃計算，而不是在空表或者資料量很小時候先做一次執行計劃，然後用這個結果得出結論。

Enjoy GreatSQL 😃

關於 GreatSQL

GreatSQL是適用於金融級應用的國內自主開源資料庫，具備高效能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併相容MySQL或Percona Server。

相關連結： GreatSQL社群 Gitee GitHub Bilibili

GreatSQL社群：

社群部落格有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html

image-20230105161905827

技術交流群：

微信：掃碼新增GreatSQL社群助手微信好友，傳送驗證資訊加群。

image-20221030163217640

相關文章