作者：姚嵩
爱可生南区交付服务部经理，爱好音乐，动漫，电影，游戏，人文，美食，旅游，还有其他。虽然都很菜，但毕竟是爱好。
本文来源：原创投稿
*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
摘抄： 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 ;
的步骤为：
-
先执⾏派⽣表： select @r := 603014203924416,@i := 0，此时@r为603014203924416 ； -
再对结果集进⾏筛选 @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 ;