【GreatSQL优化器-04】贪婪搜索算法浅析
【GreatSQL优化器-04】贪婪搜索算法浅析
一、贪婪搜索（greedy_search）介绍
GreatSQL的优化器用greedy_search方法来枚举所有的表连接场景，然后从中根据最小cost来决定最佳连接顺序。这里面就涉及每种场景的cost计算方法，不同计算方法会导致不同的排序结果。
因为枚举所有join场景，当表数量很大的时候就有可能无穷无尽消耗系统资源，因此GreatSQL执行greedy_search的时候使用search_depth 和prune_level变量（分别对应GreatSQL中的 optimizer_search_depth 和 optimizer_prune_level 系统变量），防止无穷无尽的分析各种连接顺序的成本，如果连接表的个数小于search_depth，那么就继续穷举分析每一种连接顺序的cost，否则只对与optimizer_search_depth 值相同数量的表进行穷举分析。该值越大，成本分析的越精确，越容易得到好的执行计划，但是消耗的时间也就越长，否则得到的可能不是最好的执行计划，但可以省掉很多分析连接成本的时间。设置合适的 prune_level 值可以裁剪掉一些不必要的深度，直接跟后面访问数量最少的表进行连接计算。
执行greedy_search复杂度可能是O(N*N^search_depth/search_depth)。如果search_depth > N 那么算法的复杂度就是 O(N!)。通常优化器分析的复杂度都是 O(N!)。
| 名称 | 场景 | 值 |
|---|---|---|
| search_depth | thd->variables.optimizer_search_depth>0 (默认62) | thd->variables.optimizer_search_depth |
| thd->variables.optimizer_search_depth=0 | 表数量<=7,值为表数量+1 | |
| 表数量>7,值为7 | ||
| prune_level | 在设置了参数prune_level(默认设置)后，不再使用穷举的方式扩展执行计划，而是在剩余表中直接选取访问最少纪录数的表计算。如果未设置，则继续跟后面的表连接计算cost获取"最优"的执行计划。因此设置为on的时候执行复杂度小更快。 | thd->variables.optimizer_prune_level 值为0和1，默认值为1 |
下面用一个简单的例子来说明greedy_search是什么。
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');
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);
-- 这里做了一个6张表的连接，这里面就涉及了greedy_search方法来决定哪张表先执行哪张表后执行
-- 看下面最终结果是按照t1,t4,t2,t5,t3,t4顺序来执行连接的
greatsql> EXPLAIN SELECT * FROM t3,t1,t2,t1 AS t4,t2 AS t5,t3 AS t6 WHERE t1.c1=t3.ccc1 and t2.cc1=t3.ccc1 and t1.c1=t4.c1 and t1.c1=t5.cc1 and t1.c1=t6.ccc1;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| 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 |
| 1 | SIMPLE | t4 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | db1.t1.c1 | 1 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | db1.t1.c1 | 1 | 100.00 | NULL |
| 1 | SIMPLE | t5 | 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 |
| 1 | SIMPLE | t6 | NULL | ref | idx3_1 | idx3_1 | 5 | db1.t1.c1 | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
二、greedy_search代码解释
bool JOIN::make_join_plan() {
// Choose the table order based on analysis done so far.
if (Optimize_table_order(thd, this, nullptr).choose_table_order())
return true;
}
bool Optimize_table_order::choose_table_order() {
// 先对所有表做一个排序，小表放前面
merge_sort();
if (straight_join)
optimize_straight_join(join_tables);
else {
if (greedy_search(join_tables)) return true;
}
}
bool Optimize_table_order::greedy_search(table_map remaining_tables) {
do {
if (best_extension_by_limited_search(remaining_tables, idx, search_depth))
return true;
// 如果层数没有超过规定大小，说明已经执行完枚举可以退出。如果超过了，说明不需要执行枚举连接，按照单独连接方式继续执行。
if (size_remain <= search_depth || use_best_so_far) {
return false;
}
--size_remain;
++idx;
}
}
// 用current_search_depth来计数，每进行一次深度连接计算就减一，用来控制join连接枚举分析的层数。
bool Optimize_table_order::best_extension_by_limited_search(
table_map remaining_tables, uint idx, uint current_search_depth) {
for (JOIN_TAB **pos = join->best_ref + idx; *pos && !use_best_so_far; pos++) {
/* Find the best access method from 's' to the current partial plan */
// 该表之后的每个表遍历一遍，计算每种连接的cost
best_access_path(s, remaining_tables, idx, false,
idx ? (position - 1)->prefix_rowcount : 1.0, position);
// 如果算完左连接cost比之前的还大，就不继续算下一张表，直接开始下一次连接计算
if (position->prefix_cost >= join->best_read &&
found_plan_with_allowed_sj) {
trace_one_table.add("pruned_by_cost", true);
continue;
}
// 打开裁剪模式的话
if (prune_level == 1) {
// 如果当前计算出来的rowcount小于之前保存下来的best_rowcount，那么就替换best_rowcount值
if (best_rowcount > position->prefix_rowcount ||
best_cost > position->prefix_cost ||
(idx == join->const_tables && // 's' is the first table in the QEP
s->table() == join->sort_by_table)) {
best_rowcount = position->prefix_rowcount;
best_cost = position->prefix_cost;
} else // 否则就跳过剩下表的穷举搜索计算，直接用下一次连接继续计算
continue;
}
// current_search_depth还在层数允许范围内，递归继续进行下一次优化查询
if ((current_search_depth > 1) && remaining_tables_after) {
if (prune_level == 1 && // 1)
position->key != nullptr && // 2)
position->rows_fetched <= 1.0) // 3)
{
// 如果连接左表cost比之前的大，那么会被裁剪
// 如果满足(EQ_)REF key的join方式并且本次找到的行数只有一行，那么就执行EQ_REF-joined连接计算
eq_ref_extension_by_limited_search();
} else
// 继续执行下一张连接表的连接cost计算
best_extension_by_limited_search(remaining_tables_after, idx + 1,
current_search_depth - 1));
} else // if ((current_search_depth > 1) && ... // 层数还有剩，后面没有表要连接了，那就保存当次计算结果以便下次做比较
{ // 将这次计算出来的rowcount和read_cost存入prefix_rowcount和prefix_read_cost，方便下一次连接比较cost
// 如果此次join的cost更小，那么保存到join->best_read 和 join->best_rowcount
if (consider_plan(idx, &trace_one_table)) return true;
}
}
}
void Optimize_table_order::best_access_path(JOIN_TAB *tab,
const table_map remaining_tables,
const uint idx, bool disable_jbuf,
const double prefix_rowcount,
POSITION *pos) {
// 如果根据前面的结果keyuse_array数组有值的话，那么根据find_best_ref()函数先找出最优索引，按照索引的方式计算cost
if (tab->keyuse() != nullptr &&
(table->file->ha_table_flags() & HA_NO_INDEX_ACCESS) == 0)
best_ref =
find_best_ref(tab, remaining_tables, idx, prefix_rowcount,
&found_condition, &ref_depend_map, &used_key_parts);
// 最主要计算下面3个值
pos->filter_effect = filter_effect = std::min(1.0, tab->found_records * calculate_condition_filter() / rows_after_filtering);
pos->rows_fetched = rows_fetched = rows_after_filtering;
pos->read_cost = scan_read_cost = calculate_scan_cost();
}
优化器内部有一些优化可以根据需要自己重新定义行为，这就涉及到optimizer_switch开关的设置。详细可以设置的optimizer_switch如下表所示，以后每个专门开一期细讲。
附表：优化器涉及的OPTIMIZER_SWITCH表
| OPTIMIZER_SWITCH | 默认 | 说明 |
|---|---|---|
| OPTIMIZER_SWITCH_INDEX_MERGE | ON | 可以在多个索引上进行查询，并将结果合并返回。 |
| OPTIMIZER_SWITCH_INDEX_MERGE_UNION | OFF | 会在使用到的多个索引上同时进行扫描，并取这些扫描结果的并集作为最终结果集。 |
| OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION | ON | 比单纯的Union索引合并多了一步对二级索引记录的主键id排序的过程。 |
| OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT | ON | 会在使用到的多个索引上同时进行扫描，并取这些扫描结果的交集作为最终结果集。 |
| OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN | ON | 只用于NDB引擎，开启后时按照WHERE条件过滤后的数据发送到SQL节点来处理，不开启所有数据节点的数据都发送到SQL节点来处理 |
| OPTIMIZER_SWITCH_INDEX_CONDITION_PUSHDOWN | ON | 当ICP打开时，用于二级索引的range、 ref、 eq_ref或ref_or_null扫描，如果部分where条件能使用索引的字段，server会把这部分下推到引擎层，可以利用index过滤的where条件在存储引擎层进行数据过滤。 |
| OPTIMIZER_SWITCH_MRR | ON | 针对多列索引，也叫组合索引来做基本扫描，然后对匹配的记录按照主键排序，这样按照有序的主键顺序从磁盘上扫描需要的全部记录。根本功能是把对磁盘的随机扫描转化为顺序扫描。 |
| OPTIMIZER_SWITCH_MRR_COST_BASED | ON | cost base的方式还选择启用MRR优化,当发现优化后的代价过高时就会不使用该项优化 |
| OPTIMIZER_SWITCH_BNL | ON | 块嵌套循环（Block Nested Loop, BNL)，将驱动表的数据先缓存在join buffer中，一次读取被驱动表的数据，可以和驱动表的多条记录进行join，这样就可以减少全表扫描的次数。 |
| OPTIMIZER_SWITCH_BKA | OFF | 批量索引访问(Batched Key Access),主要适用于join的表上有索引可利用，无索引只能使用BNL.多表join语句，被join的表/非驱动表必须有索引可用，才能利用BKA.对于多表join语句，当MySQL使用索引访问第二个join表的时候，使用一个join buffer来收集第一个操作对象生成的相关列值 |
| OPTIMIZER_SWITCH_SEMIJOIN | ON | 是否启用semijoin |
| OPTIMIZER_SWITCH_MATERIALIZATION | ON | OPTIMIZER_SWITCH_SEMIJOIN=ON，把内表去重然后生成有对应索引的临时表（有点类似其他数据中的物化视图），然后通过外表的对应键值遍历这张临时表。 |
| OPTIMIZER_SWITCH_LOOSE_SCAN | ON | OPTIMIZER_SWITCH_SEMIJOIN=ON，把inner-table数据基于索引进行分组，取每组第一条数据进行匹配。 |
| OPTIMIZER_SWITCH_FIRSTMATCH | ON | OPTIMIZER_SWITCH_SEMIJOIN=ON，只选用内部表的第1条与外表匹配的记录。 |
| OPTIMIZER_SWITCH_DUPSWEEDOUT | ON | OPTIMIZER_SWITCH_SEMIJOIN=ON，使用临时表对semi-join产生的结果集去重 |
| OPTIMIZER_SWITCH_SUBQ_MAT_COST_BASED | ON | OPTIMIZER_SWITCH_MATERIALIZATION=ON的时候，如果此项为ON选择CANDIDATE_FOR_IN2EXISTS_OR_MAT策略，否则选择SUBQ_MATERIALIZATION策略 |
| OPTIMIZER_SWITCH_USE_INDEX_EXTENSIONS | ON | 索引扩展使用，主要用于INNODB的第二索引，也就是普通的索引,把索引中包含的主键值利用到。比如主键为(a,b),索引为(c). 如果用到了索引c，那么把索引变成(c,a,b) 这样，就可以用到新的组合索引了。不过这种场合用的也比较少，一般是根据组合主键中的第一个字段和普通索引一起来做检索的时候。 |
| OPTIMIZER_SWITCH_COND_FANOUT_FILTER | ON | join时候是否使用条件过滤百分比 |
| OPTIMIZER_SWITCH_DERIVED_MERGE | ON | 派生表合并，类似Oracle的视图合并，当派生SQL中存在以下操作是无法展开：UNION 、GROUP 、DISTINCT、LIMIT及聚合操作 |
| OPTIMIZER_SWITCH_USE_INVISIBLE_INDEXES | OFF | 是否使用不可见索引 |
| OPTIMIZER_SKIP_SCAN | ON | 使用skip scan方式进行范围扫描，当要查询的列都在索引中时，即使where中的条件不是索引的第一部分，也可以使用索引。 |
| OPTIMIZER_SWITCH_HASH_JOIN | ON | 使用hash join方法，导致开销更大，已经弃用 |
| OPTIMIZER_SWITCH_SUBQUERY_TO_DERIVED | OFF | 子查询转换为派生表 |
| OPTIMIZER_SWITCH_PREFER_ORDERING_INDEX | ON | 对于 limit N 带有 group by ，order by 的 SQL 语句 (order by 和 group by 的字段有索引可以使用)，执行计划选择 where 条件中的索引查找过滤数据，而不是根据order by id 的索引进而导致全表扫描 |
| OPTIMIZER_SWITCH_HYPERGRAPH_OPTIMIZER | OFF | 使用超图理论（Hypergraph Theory）来优化查询计划 |
| OPTIMIZER_SWITCH_DERIVED_CONDITION_PUSHDOWN | ON | 对于派生表（DERIVED）的条件下推，即在执行派生表之前应用条件，减少在派生表中处理的数据量。 |
| OPTIMIZER_SWITCH_FAVOR_RANGE_SCAN | OFF | 当index dive不能被跳过的时候，如果此项=ON，那么cost值*0.1 |
| OPTIMIZER_SWITCH_REMOVE_USELESS_OUTERJOIN | OFF | 用于控制优化器在处理不必要的外连接时的行为。不必要的外连接指的是那些永远不会返回任何内连接行的连接。这些外连接在查询执行时会被优化器移除。 |
三、实际例子说明
接下来看几个例子来说明上面的代码。
greatsql> EXPLAIN SELECT * FROM t3,t1,t2,t1 AS t4,t2 AS t5,t3 AS t6 WHERE t1.c1=t3.ccc1 and t2.cc1=t3.ccc1 AND t1.c1=t4.c1 AND t1.c1=t5.cc1 AND t1.c1=t6.ccc1;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| 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 |
| 1 | SIMPLE | t4 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | db1.t1.c1 | 1 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | db1.t1.c1 | 1 | 100.00 | NULL |
| 1 | SIMPLE | t5 | 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 |
| 1 | SIMPLE | t6 | NULL | ref | idx3_1 | idx3_1 | 5 | db1.t1.c1 | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
-- 看一下枚举顺序：
"`t1`" -> `t1` `t4` -> `t3`
-> `t2` -> `t3`
-> `t2` `t5` -> `t3`
-> `t3` `t6` -- cost太大不继续计算下一张表，直接开始下一次连接计算
-> `t3` -> `t3` `t6`
-> `t3`
-> `t3` `t6`
`t1` `t4` -> `t1` -> `t3`
-> `t2` -> `t3`
-> `t2` `t5` -> `t3`
-> `t3` `t6` cost太大不继续计算下一张表，直接开始下一次连接计算
-> `t3` -> `t3` `t6` cost太大不继续计算下一张表，直接开始下一次连接计算
后面类似，因为太多不写出来了。
-- 层数改为1，看看效果。
greatsql> set optimizer_search_depth=1;
-- 发现表的连接顺序跟之前不一样了。
greatsql> explain SELECT * FROM t3,t1,t2,t1 as t4,t2 as t5,t3 as t6 where t1.c1=t3.ccc1 and t2.cc1=t3.ccc1 and t1.c1=t4.c1 and t1.c1=t5.cc1 and t1.c1=t6.ccc1;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| 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 |
| 1 | SIMPLE | t4 | 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 |
| 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | db1.t1.c1 | 1 | 100.00 | NULL |
| 1 | SIMPLE | t5 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | db1.t1.c1 | 1 | 100.00 | NULL |
| 1 | SIMPLE | t6 | NULL | ref | idx3_1 | idx3_1 | 5 | db1.t1.c1 | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
`t1` cost最小，选这个继续走
`t1` `t4`
`t3`
`t2`
`t2` `t5`
`t3` `t6`
`t1` -> `t1` `t4` -> `t3` -> `t2` -> `t2` `t5` -> `t3` `t6` cost最小，选这个继续走
-> `t3` `t6`
-> `t2` `t5`
-> `t3` `t6`
-> `t2`
-> `t2` `t5`
-> `t3` `t6`
-> `t3`
-> `t2`
-> `t2` `t5`
-> `t3` `t6`
四、总结
从上面优化器的步骤我们认识了贪婪算法的过程，知道了2个基本参数search_depth和prune_level，这两个参数可以自定义设置值用来简化算法的步骤，减少资源的消耗，但是也会相应的导致最后结果不精确，所以还是要按照个人需求进行设置。
Enjoy GreatSQL 😃
关于 GreatSQL
GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。
相关链接： GreatSQL社区 Gitee GitHub Bilibili
GreatSQL社区：
社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html
技术交流群：
微信：扫码添加
GreatSQL社区助手微信好友，发送验证信息加群。
