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

作者：姚嵩
爱可生南区交付服务部经理，爱好音乐，动漫，电影，游戏，人文，美食，旅游，还有其他。虽然都很菜，但毕竟是爱好。
本文来源：原创投稿
*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

摘抄： https://dev.mysql.com/doc/ref...
说明： 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：

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

文章图片

测试语句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。
【技术分享 | derived_condition_pushdown 影响外层 where 条件中用户自定义变量的使用】在 MySQL8.0.22 版本及之后版本，参数 optimizer_switch 引?了 derived_condition_pushdown 变量，在开启的时候，会导致下?的 SQL 语句及类似语句得到?预期的结果：