技术分享 | derived_condition_pushdown 影响外层 where 条件中用户自定义变量的使用

作者：姚嵩

爱可生南区交付服务部经理，爱好音乐，动漫，电影，游戏，人文，美食，旅游，还有其他。虽然都很菜，但毕竟是爱好。

本文来源：原创投稿

*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

---

摘抄： https://dev.mysql.com/doc/refman/8.0/en/derived-condition-pushdown-optimization.html

说明：

MySQL 8.0.22开始，新增了 optimizer_switch 参数，新增了 derived_condition_pushdown 变量，

启⽤用该变量后，可能会导致最外层 where 条件中使⽤了⽤户变量的 SQL 语句得到⾮预期的结果；

简单介绍：

derived_condition_pushdown 按字⾯意思就是派⽣条件下推；
MySQL8.0.22 开始对⽀持符合条件的子查询‘’进⾏派⽣条件下推，derived_condition_pushdown=ON 后，

对于查询：

SELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i > constant

在许多情况下可能将外部的 where 条件下推到派⽣表，这会导致语句优化为：

SELECT * FROM (SELECT i, j FROM t1 WHERE i > constant) AS dt

这减少了派⽣表返回的⾏数，从⽽加快查询的速度。

测试语句：

select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;

变更 optimizer_switch 的语句(值可选OFF或ON)：

set optimizer_switch=‘derived_condition_pushdown=on’; – 设置当前会话

set global optimizer_switch=‘derived_condition_pushdown=on’; – 设置全局值(影响后续新建的会话)

set persist optimizer_switch=‘derived_condition_pushdown=on’; – 设置全局值，并固化到配置⽂件mysqld-auto.cnf；

测试 MySQL 版本：
MySQL8.0.23

当derived_condition_pushdown=ON时：

测试语句1：

set optimizer_switch='derived_condition_pushdown=on';
set @r=0;
select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;
explain select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;
set @r=1;
select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;
explain select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;

测试结果1：

测试语句2:

set @r=0;
select @r := 603014203924416,@i := 0 where @r<>0 ;
explain select @r := 603014203924416,@i := 0 where @r<>0 ;
set @r=1;
select @r := 603014203924416,@i := 0 where @r<>0 ;
explain select @r := 603014203924416,@i := 0 where @r<>0 ;

测试结果2:

测试结果说明：
当设置了 derived_condition_pushdown=ON 时： MySQL 执⾏了派⽣条件下推的优化，

将语句1

select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;

变更为语句2：

select @r := 603014203924416,@i := 0 where @r<>0 ;

⽽语句2中，是先筛选 where @r<>0 条件，然后再展示 @r := 603014203924416,@i := 0 的结果，

所以会利⽤ sesson 已有的@r的值进⾏ where 条件匹配，结果不符合预期。

当 derived_condition_pushdown=OFF 时：

测试语句1：

set optimizer_switch='derived_condition_pushdown=off';
set @r=0;
select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;
explain select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;
set @r=1;
select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;
explain select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;

测试结果1：

测试结果说明：

当设置了 derived_condition_pushdown=OFF 时：MySQL 执⾏语句1：

select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;

的步骤为：

1. 先执⾏派⽣表： select @r := 603014203924416,@i := 0，此时@r为603014203924416 ；
2. 再对结果集进⾏筛选 @r <> 0，得出结果，结果正确。

注意：

在 MySQL8.0.21 版本及之前版本，还没有参数 optimizer_switch,还没有derived_condition_pushdown变量，等价于 derived_condition_pushdown=off。

在 MySQL8.0.22 版本及之后版本，参数 optimizer_switch 引⼊了 derived_condition_pushdown 变量，在开启的时候，会导致下⾯的 SQL 语句及类似语句得到⾮预期的结果：

select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;

解决⽅法：

⽅法1:

set persist optimizer_switch='derived_condition_pushdown=off';

当然，也可以在执⾏ SQL 语句前，执⾏ session 级别的更改，只影响执⾏参数变更的 session ：

set optimizer_switch='derived_condition_pushdown=on';

⽅法2:

改写 SQL ，让最外层的 where 条件不包含⽤户变量：

原语句：

select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;

改写后的语句：

select * from (select @r id,@i num from (select @r := 603014203924416,@i := 0) vars ) a where
id<>0 ;

⽅法3:

提前设置@r的值：

select @r := 603014203924416,@i := 0 ;
select * from (select @r := 603014203924416,@i := 0) vars where @r<>0 ;