【GreatSQL优化器-06】条件过滤导致选择非最佳

【GreatSQL优化器-06】条件过滤导致选择非最佳

一、condition_fanout_filter导致计划非最佳

GreatSQL 的优化器对于 join 的表需要根据行数和 cost 来确定最后哪张表先实行哪张表后实行，这内里就涉及到预估满意条件的表数据，condition_fanout_filter会根据一系列方法计算出一个数据过滤百分比，这个比百分比就是 filtered 系数，这个值区间在[0,1]，值越小代表过滤效果越好。用这个系数乘以总的行数就可以得出最后需要扫描的表行数的数量，可以大幅节省开销和实行时间。
这个功能是由OPTIMIZER_SWITCH_COND_FANOUT_FILTER这个OPTIMIZER_SWITCH来控制的，默认是打开的。因此一般情况下不需要特意去关闭，但是如果碰到实行特别慢的一些情况可以思量关闭。
下面用一个例子来说明condition_fanout_filter有可能导致选择错误的情况：

1. # 创建2张表，都只在第二列创建索引，其中t3的最后一列也创建一个索引。
2. CREATE TABLE t3 (ccc1 INT, ccc2 int,ccc3 datetime(6));
3. INSERT INTO t3 VALUES (1,2,'2021-03-25 16:44:00.123456'),(2,10,'2021-03-25 16:44:00.123456'),(3,4,'2022-03-25 16:44:00.123456'),(4,6,'2023-03-25 16:44:00.123456'),(null,7,'2024-03-25 16:44:00.123456'),(4,3,'2024-04-25 16:44:00.123456'),(null,8,'2025-03-25 16:44:00.123456'),(3,4,'2022-06-25 16:44:00.123456'),(5,4,'2021-11-25 16:44:00.123456');
4. CREATE TABLE t4 (d1 INT, d2 int, d3 varchar(100));
5. INSERT INTO t4 VALUES (1,2,'aa1'),(2,1,'bb1'),(2,3,'cc1'),(3,3,'cc1'),(4,2,'ff1'),(4,4,'ert'),(4,2,'f5fg'),(null,2,'ee'),(5,30,'cc1'),(5,4,'fcc1'),(4,10,'cc1'),(6,4,'ccd1'),(null,1,'fee'),(1,2,'aa1'),(2,1,'bb1'),(2,3,'cc1'),(3,3,'cc1'),(4,2,'ff1'),(4,4,'ert'),(4,2,'f5fg'),(null,2,'ee'),(5,30,'cc1'),(5,4,'fcc1'),(4,10,'cc1'),(6,4,'ccd1'),(null,1,'fee'),(1,2,'aa1'),(2,1,'bb1'),(2,3,'cc1'),(3,3,'cc1'),(4,2,'ff1'),(4,4,'ert'),(4,2,'f5fg'),(null,2,'ee'),(5,30,'cc1'),(5,4,'fcc1'),(4,10,'cc1'),(6,4,'ccd1'),(null,1,'fee');
6. CREATE INDEX idx3_2 ON t3(ccc2);
7. CREATE INDEX idx3_3 ON t3(ccc3);
8. CREATE INDEX idx4_2 ON t4(d2);

复制代码

实行一个join命令，where条件涉及的列不含t4的索引列，但是包含t3的索引列。
起首查看条件过滤开启的情况，效果是t4先实行全表扫描，预估的扫描行数为39 * 33.33%=13行，而t3实行ref索引扫描，行数为1 * 11.11%=0.1行，总行数为2行

1. greatsql> EXPLAIN SELECT * FROM t4 join t3 ON t4.d1=t3.ccc1 and t4.d2=t3.ccc2 where t4.d1<5 and t3.ccc3 < '2023-11-15';
2. +----+-------------+-------+------------+------+---------------+--------+---------+-----------+------+----------+-------------+
3. | id | select_type | table | partitions | type | possible_keys | key    | key_len | ref       | rows | filtered | Extra       |
4. +----+-------------+-------+------------+------+---------------+--------+---------+-----------+------+----------+-------------+
5. |  1 | SIMPLE      | t4    | NULL       | ALL  | idx4_2        | NULL   | NULL    | NULL      |   39 |    33.33 | Using where |
6. |  1 | SIMPLE      | t3    | NULL       | ref  | idx3_2,idx3_3 | idx3_2 | 5       | db1.t4.d2 |    1 |    11.11 | Using where |
7. +----+-------------+-------+------------+------+---------------+--------+---------+-----------+------+----------+-------------+

复制代码

以上例子因为condition_fanout_filter的设置不同而导致选择了不同的驱动表，最后的扫描行为也不一样。但是明显先实行t3的索引范围扫描比t4的全表扫描服从高，因此这个例子可以看出condition_fanout_filter的预估过滤百分比有更多主观性，最终可能导致错误的优化路径。
附表：join_type访问方法的范例
join_type访问方法的范例说明JT_UNKNOWN无效JT_SYSTEM表只有一行，比如select * from (select 1)JT_CONST表最多只有一行满意，比如WHERE table.pk = 3JT_EQ_REF=符号用在唯一索引JT_REF=符号用在非唯一索引JT_ALL全表扫描JT_RANGE范围扫描JT_INDEX_SCAN索引扫描JT_FTFulltext索引扫描JT_REF_OR_NULL包含null值，比如"WHERE col = ... OR col IS NULLJT_INDEX_MERGE一张表实行多次范围扫描最后合并效果

以上各类扫描方式由快到慢排序为：system > const > eq_ref > ref > range > index > ALL

二、不关condition_fanout_filter的解决办法

如果不关闭condition_fanout_filter有没有办法逼迫指定毗连顺序呢？答案是有的。一共如下3个方法，可以按照自己的需要举行灵活操作。
1、使用 qb_name 提示词来指定毗连顺序
[code]greatsql> EXPLAIN SELECT /*+ qb_name(qb1) JOIN_ORDER(@qb1 t3,t4) */ * FROM t4 join t3 ON t4.d1=t3.ccc1 and t4.d2=t3.ccc2 where t4.d1 CREATE INDEX idx4_1 ON t4(d1);greatsql> EXPLAIN SELECT * FROM t4 join t3 ON t4.d1=t3.ccc1 and t4.d2=t3.ccc2 where t4.d1 EXPLAIN SELECT /*+ qb_name(qb1) JOIN_FIXED_ORDER(@qb1) */ * FROM t3 join t4 ON t4.d1=t3.ccc1 AND t4.d2=t3.ccc2 WHERE t4.d1