全部标签
写点什么
OpenHarmony算法元宇宙MySQL移动开发学习方法学习方法Web3.0高效工作数据库Python音视频前端AI大数据团队管理程序员运维深度思考低代码redisgolang微服务架构flutter 查看更多

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

作者：GreatSQL
  • 2024-12-11
    福建

  • 本文字数：6340 字

    阅读完需：约 21 分钟

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

一、condition_fanout_filter 导致计划非最佳

GreatSQL 的优化器对于 join 的表需要根据行数和 cost 来确定最后哪张表先执行哪张表后执行，这里面就涉及到预估满足条件的表数据，condition_fanout_filter会根据一系列方法计算出一个数据过滤百分比，这个比百分比就是 filtered 系数，这个值区间在[0,1]，值越小代表过滤效果越好。用这个系数乘以总的行数就可以得出最后需要扫描的表行数的数量，可以大幅节省开销和执行时间。


这个功能是由OPTIMIZER_SWITCH_COND_FANOUT_FILTER这个OPTIMIZER_SWITCH来控制的，默认是打开的。因此一般情况下不需要特意去关闭，但是如果遇到执行特别慢的一些情况可以考虑关闭。


下面用一个例子来说明condition_fanout_filter有可能导致选择错误的情况：


# 创建2张表，都只在第二列创建索引，其中t3的最后一列也创建一个索引。CREATE TABLE t3 (ccc1 INT, ccc2 int,ccc3 datetime(6));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');CREATE TABLE t4 (d1 INT, d2 int, d3 varchar(100));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');CREATE INDEX idx3_2 ON t3(ccc2);CREATE INDEX idx3_3 ON t3(ccc3);CREATE INDEX idx4_2 ON t4(d2);
复制代码


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


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';+----+-------------+-------+------------+------+---------------+--------+---------+-----------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref       | rows | filtered | Extra       |+----+-------------+-------+------------+------+---------------+--------+---------+-----------+------+----------+-------------+|  1 | SIMPLE      | t4    | NULL       | ALL  | idx4_2        | NULL   | NULL    | NULL      |   39 |    33.33 | Using where ||  1 | SIMPLE      | t3    | NULL       | ref  | idx3_2,idx3_3 | idx3_2 | 5       | db1.t4.d2 |    1 |    11.11 | Using where |+----+-------------+-------+------------+------+---------------+--------+---------+-----------+------+----------+-------------+
复制代码


接着查看条件过滤关闭的情况，结果是 t3 先执行范围扫描，预估的扫描行数为 6 * 100%=6 行，而 t4 执行 ref 索引扫描，行数为 6 * 100%=6 行，总行数为 39 行。


greatsql> EXPLAIN SELECT /*+ set_var(optimizer_switch='condition_fanout_filter=off') */ * FROM t4 join t3 ON t4.d1=t3.ccc1 and t4.d2=t3.ccc2 where t4.d1<5 and t3.ccc3 < '2023-11-15';+----+-------------+-------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref         | rows | filtered | Extra                              |+----+-------------+-------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+|  1 | SIMPLE      | t3    | NULL       | range | idx3_2,idx3_3 | idx3_3 | 9       | NULL        |    6 |   100.00 | Using index condition; Using where ||  1 | SIMPLE      | t4    | NULL       | ref   | idx4_2        | idx4_2 | 5       | db1.t3.ccc2 |    6 |   100.00 | Using where                        |+----+-------------+-------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+
复制代码


接下来关掉 condition_fanout_filter 然后强制用 t4 & t3 来连接，对比一下计算出来的 cost 实际达到多少。从下面 2 个结果可以看出，t4 走了全表扫描实际的 cost 达到 21.70，是估计值的 2 倍多。


greatsql> EXPLAIN FORMAT=TREE 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';+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN                                                                                                                                                                                                                                                                                                                                                        |+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| -> Nested loop inner join  (cost=10.00 rows=2)    -> Filter: ((t4.d1 < 5) and (t4.d2 is not null))  (cost=4.15 rows=13)        -> Table scan on t4  (cost=4.15 rows=39)    -> Filter: ((t3.ccc1 = t4.d1) and (t3.ccc3 < TIMESTAMP'2023-11-15 00:00:00'))  (cost=0.32 rows=0.1)        -> Index lookup on t3 using idx3_2 (ccc2=t4.d2)  (cost=0.32 rows=1) |+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
greatsql> EXPLAIN FORMAT=TREE SELECT /*+ set_var(optimizer_switch='condition_fanout_filter=off') qb_name(qb1) JOIN_ORDER(@qb1 t4,t3) */ * FROM t4 join t3 ON t4.d1=t3.ccc1 and t4.d2=t3.ccc2 where t4.d1<5 and t3.ccc3 < '2023-11-15';+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN                                                                                                                                                                                                                                                                                                                                                       |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| -> Nested loop inner join  (cost=21.70 rows=50)    -> Filter: ((t4.d1 < 5) and (t4.d2 is not null))  (cost=4.15 rows=39)        -> Table scan on t4  (cost=4.15 rows=39)    -> Filter: ((t3.ccc1 = t4.d1) and (t3.ccc3 < TIMESTAMP'2023-11-15 00:00:00'))  (cost=0.32 rows=1)        -> Index lookup on t3 using idx3_2 (ccc2=t4.d2)  (cost=0.32 rows=1) |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
复制代码


以上例子因为condition_fanout_filter的设置不同而导致选择了不同的驱动表，最后的扫描行为也不一样。但是明显先执行 t3 的索引范围扫描比 t4 的全表扫描效率高，因此这个例子可以看出 condition_fanout_filter 的预估过滤百分比有更多主观性，最终可能导致错误的优化路径。


附表：join_type 访问方法的类型



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

二、不关 condition_fanout_filter 的解决办法

如果不关闭condition_fanout_filter有没有办法强制指定连接顺序呢？答案是有的。一共如下 3 个方法，可以按照自己的需要进行灵活操作。


1、使用 qb_name 提示词来指定连接顺序


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<5 and t3.ccc3 < '2023-11-15';+----+-------------+-------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref         | rows | filtered | Extra                              |+----+-------------+-------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+|  1 | SIMPLE      | t3    | NULL       | range | idx3_2,idx3_3 | idx3_3 | 9       | NULL        |    6 |   100.00 | Using index condition; Using where ||  1 | SIMPLE      | t4    | NULL       | ref   | idx4_2        | idx4_2 | 5       | db1.t3.ccc2 |    6 |     3.33 | Using where                        |+----+-------------+-------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+
复制代码


2、在所有 WHERE 条件列上建立索引


greatsql> 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<5 and t3.ccc3 < '2023-11-15';+----+-------------+-------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref         | rows | filtered | Extra                              |+----+-------------+-------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+|  1 | SIMPLE      | t3    | NULL       | range | idx3_2,idx3_3 | idx3_3 | 9       | NULL        |    6 |   100.00 | Using index condition; Using where ||  1 | SIMPLE      | t4    | NULL       | ref   | idx4_2,idx4_1 | idx4_1 | 5       | db1.t3.ccc1 |    5 |    16.67 | Using where                        |+----+-------------+-------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+
复制代码


3、用 JOIN_FIXED_ORDER hint 加上表顺序来强制连接顺序。


greatsql> 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<5 AND t3.ccc3 < '2023-11-15';+----+-------------+-------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref         | rows | filtered | Extra                              |+----+-------------+-------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+|  1 | SIMPLE      | t3    | NULL       | range | idx3_2,idx3_3 | idx3_3 | 9       | NULL        |    6 |   100.00 | Using index condition; Using where ||  1 | SIMPLE      | t4    | NULL       | ref   | idx4_2        | idx4_2 | 5       | db1.t3.ccc2 |    6 |     3.33 | Using where                        |+----+-------------+-------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+
复制代码

三、如何排查类似问题

从以上例子看出，打开条件过滤有时并不总是能提高性能，优化器可能会高估条件过滤的影响，个别场景下使用条件过滤反而会导致性能下降。GreatSQL 的condition_fanout_filter参数默认是打开的，因此需要自己来判断是否需要这个功能。一般来说，遇到以下场景需要特别注意条件过滤错估的情况。


四、总结

这节用了一个例子展示了条件过滤误判的情况，知道了打开条件过滤有时并不总是能提高性能，优化器可能会高估条件过滤的影响，个别场景下使用条件过滤反而会导致性能下降。GreatSQL 的condition_fanout_filter参数默认是打开的，因此需要自己来判断是否需要这个功能。


划线
评论
复制
发布于: 刚刚阅读数: 5

版权声明: 本文为 InfoQ 作者【GreatSQL】的原创文章。

原文链接:【http://xie.infoq.cn/article/093642465a6806d067ce4fad9】。文章转载请联系作者。

用户头像

GreatSQL

关注

GreatSQL社区 2023-01-31 加入

GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。 社区：https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL

评论

发布
暂无评论
Copyright © 2024, Geekbang Technology Ltd. All rights reserved. 极客邦控股（北京）有限公司 | 京 ICP 备 16027448 号 - 5京公网安备京公网安备 11010502039052号 | 产品资质
【GreatSQL优化器-06】条件过滤导致选择非最佳_GreatSQL_InfoQ写作社区