技术分享 | 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:

技术分享 | 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 影响外层 where 条件中用户自定义变量的使用

测试结果说明:
当设置了 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 影响外层 where 条件中用户自定义变量的使用

测试结果说明:

当设置了 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 ;
上一篇:Mysql数据管理(DML语言)


下一篇:condition精准控制