MySQL optimizer_switch参数之condition_fanout_filter开关

原创 aisql 2024-01-30

409

1、官网介绍

Condition Filtering Flags
condition_fanout_filter (default on)
Controls use of condition filtering.
For more information, see Section 8.2.1.13, “Condition Filtering”.

condition_fanout_filter 默认值为on
可通过命令 select @@optimizer_switch 查看
我们都知道在多表联连中MySQL通常都选用小表做为驱动表
如果condition_fanout_filter = off 则只考虑引擎级别索引树的过滤即explain的rows 用于多表关联行数的估计，谁是小表。
如果condition_fanout_filter = on 则考虑的是引擎级别的过滤再 * server层的过虑。即 explain中的 rows*filterd /100 为最终行数

在官网中有详细的介绍，可移步这里查看

由于默认为on 即在多表关联中要考虑filterd来确认谁是驱动表，但有时候也因为这个特性，让MySQL走错误的执行计划，下面介始我在生产中遇到的一个实例

2、生产实例

由于已经给研发同学上过5堂性能优化的课了，对于普通语句研发同学都能优化了，下述语句研发同学知道用指定驱动表hint方式优化。但研发问我。MySQL为什么这么傻，总是选错驱动表，我就和他们说说大概的原理，并写一篇小文以做记录。

有如下语句

SELECT
COUNT(*)
FROM  erp_bill_finance_voucher a
INNER JOIN erp_bill_index bi ON bi.`billid`=a.`billid` AND bi.profileid=a.profileid AND bi.billtype=905 
WHERE a.profileid=200004255 AND a.billdate >= '2023-11-15' AND bi.billdate >= '2023-11-15' 
AND a.billdate < '2024-01-16' AND bi.billdate < '2024-01-16' AND a.ifred = 0 
复制

执行计划如下

表a的where 条件中都没有索引但表bi是有的。
由于 condition_fanout_filter = on 所以a表mysql 估算的最终行数为 rows*filterd /100
即 1003026 *0.56/100.00 = 5616 行

但由于a表全表扫描了 1003026 导致此SQL执行较慢

我将 condition_fanout_filter关闭

EXPLAIN
SELECT
/*+ set_var(optimizer_switch='condition_fanout_filter=off') */
COUNT(*)
FROM  erp_bill_finance_voucher a
INNER JOIN erp_bill_index bi ON bi.`billid`=a.`billid` AND bi.profileid=a.profileid AND bi.billtype=905 
WHERE a.profileid=200004255 AND a.billdate >= '2023-11-15' AND bi.billdate >= '2023-11-15' 
AND a.billdate < '2024-01-16' AND bi.billdate < '2024-01-16' AND a.ifred = 0 
复制

但第二个的性能比第一个提升一倍
我实际生产中的语句 where 条件与join表都更多。提升更明细。有5倍左右的性能提升。

上述语句还有一个优化方式就是指定驱动表

EXPLAIN
SELECT
/*+ join_prefix(bi) */
COUNT(*)
FROM  erp_bill_finance_voucher a
INNER JOIN erp_bill_index bi ON bi.`billid`=a.`billid` AND bi.profileid=a.profileid AND bi.billtype=905 
WHERE a.profileid=200004255 AND a.billdate >= '2023-11-15' AND bi.billdate >= '2023-11-15' 
AND a.billdate < '2024-01-16' AND bi.billdate < '2024-01-16' AND a.ifred = 0 
复制