【GreatSQL优化器-04】贪婪搜索算法浅析-CSDN博客

本文链接：https://blog.csdn.net/GreatSQL2021/article/details/143964794

【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