|
|
【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.recordsread_time读取所有数据需要的开销io_cost + cpu_cost + import_costworst_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_COST0.1扫描一行需要的开销KEY_COMPARE_COST0.05比较row id需要的开销MEMORY_TEMPTABLE_CREATE_COST1.0创建临时表的开销，等于读10行MEMORY_TEMPTABLE_ROW_COST0.1读或写一行到临时表DISK_TEMPTABLE_CREATE_COST20.0创建MyISAM表的开销DISK_TEMPTABLE_ROW_COST0.5按顺序生成 MyISAM 行的开销MEMORY_BLOCK_READ_COST0.25读一个block从一个memory buffer poolIO_BLOCK_READ_COST1.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();
- }
- }
[code]例子2：SELECT * FROM t1 join t2 ON t1.c1=t2.cc1 and t1.c1n_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 |
|
