mysql8.0 优化器干的坏事儿

构建测试数据

create table test_1
(
  sname varchar(10),
  score int
);
insert into test_1
select '张三',80 union all
select '李四',90 union all
select '王五',50 union all
select '陈二',70;
复制

先看一下版本

SELECT @@VERSION 
复制

5.6.16-log

执行如下语句

select * from 
(
select(@i1:= @i1 + 1) as sort_i, sname,score from test_1 ,(select   @i1:= 0) t1 
) t2 where score >60;

复制

-------以上结果 在5.6版本中完全正确

我们切换到8.0版本

select @@version
复制

8.0.25
同样构造上述的测试数据
再执行同样的语句

select * from 
(
select(@i1:= @i1 + 1) as sort_i, sname,score from test_1 ,(select   @i1:= 0) t1 
) t2 where score >60

复制

what?? 为什么得到的sort_i 是1，2，3？

我们来看优化器改写的语句

explain
select * from 
(
select(@i1:= @i1 + 1) as sort_i, sname,score from test_1 ,(select   @i1:= 0) t1 
) t2 where score >60
show warnings;
复制

发现优化器 改写了我们的语句

/* select#1 */ select `t2`.`sort_i` AS `sort_i`,`t2`.`sname` AS `sname`,`t2`.`score` AS `score` 
from (/* select#2 */ select (@i1:=((@`i1`) + 1)) AS `sort_i`,`sys`.`test_1`.`sname` AS `sname`,`sys`.`test_1`.`score` AS `score` from `sys`.`test_1` where (`sys`.`test_1`.`score` > 60)) `t2`
复制

把外层嵌套干掉了。

我先不说mysql8.0的解决办法。

再回头看看5.6版本优化器改写的后。

explain extended
select * from 
(
select(@i1:= @i1 + 1) as sort_i, sname,score from test_1 ,(select   @i1:= 0) t1 
) t2 where score >60;

show warnings;
复制

得到优化器改写后的语句

/* select#1 */ select `t2`.`sort_i` AS `sort_i`,`t2`.`sname` AS `sname`,`t2`.`score` AS `score`
 from (/* select#2 */ select (@i1:=((@`i1`) + 1)) AS `sort_i`,`userdb0001`.`test_1`.`sname` AS `sname`,`userdb0001`.`test_1`.`score` AS `score` from `userdb0001`.`test_1`) `t2` where (`t2`.`score` > 60)
复制

发现在5.6版本，没有将我的外部条件推入派生表优化

8.0版本有两种解方案：
第一种 修改参数配置
MySQL 8.0.22开始，新增了 optimizer_switch 参数，新增了 derived_condition_pushdown 变量
启⽤用该变量后，可能会导致最外层 where 条件中使⽤了⽤户变量的 SQL 语句得到⾮预期的结果

该变量的主要作用是。将个外层条件下推到派生表中，让派生表返回的行数减少，以达到优化。

我们将优化参数改掉看结果

set optimizer_switch='derived_condition_pushdown=off';
复制

然后再执行

select * from 
(
select(@i1:= @i1 + 1) as sort_i, sname,score from test_1 ,(select   @i1:= 0) t1 
) t2 where score >60
复制

结果变正确了。

第二种方案是 即然8.0优化器要改写我们的语句，我们就不让它改。因为mysql 派生优化在某些特定条件下是不会进行的
查询资料得到 当派生表内有如下操作时，将不会进行优化
DISTINCT
GROUP BY
HAVING
LIMIT
UNION 或者 UNION ALL

在这里。我们加上一个limit 让mysql不自动优化

select * from 
(
select(@i1:= @i1 + 1) as sort_i, sname,score from test_1 ,(select   @i1:= 0) t1 
limit 100
) t2 where score >60

复制

结果也变正确了。