关联文章：
- MySQL对derived table的优化解决与应用限度
一、Bug形容
共事遇到一个有意思的语句，说一条SQL在MySQL8.0.25版本运行出的后果显著与给定的where条件不符，而在8.0.26版本上是失常的，语句上加了一个无关的用户变量后在8.0.25版本上后果才是正确的，想不通这是怎么回事，这么有意思的事件天然引起了我的趣味，借此机会深刻理解了一下MySQL对于derived table的优化。为了不便演示成果，让小伙伴们关注到景象的实质，我将语句进行了简化解决。
上面是模仿的表构造与数据。
create table t1(id int,c1 varchar(100));insert into t1 values(1,'gg张三');insert into t1 values(2,'bb李四');insert into t1 values(3,'cc王五');insert into t1 values(4,'dd刘麻子');insert into t1 values(1,'gg张三');insert into t1 values(2,'bb李四');SQL语句：
SELECT temp.type FROM (SELECT SUBSTRING(t.type, 3) type FROM (SELECT distinct t1.c1 type FROM test.t1 ORDER BY t1.c1) t) temp WHERE temp.type='张三' ORDER BY temp.type DESC;在MySQL8.0.25版本的运行后果如下：
mysql> SELECT temp.type -> FROM (SELECT SUBSTRING(t.type, 3) type -> FROM (SELECT distinct t1.c1 type -> FROM test.t1 -> ORDER BY t1.c1) t) temp -> WHERE temp.type='张三' -> ORDER BY temp.type DESC;+--------+| type |+--------+| 李四 |+--------+1 rows in set (0.01 sec)在MySQL8.0.26版本的运行后果如下：
mysql> SELECT temp.type -> FROM (SELECT SUBSTRING(t.type, 3) type -> FROM (SELECT distinct t1.c1 type -> FROM test.t1 -> ORDER BY t1.c1) t) temp -> WHERE temp.type='张三' -> ORDER BY temp.type DESC;+--------+| type |+--------+| 张三 |+--------+1 rows in set (0.00 sec)很显著，这个语句在8.0.25版本运行出的后果与咱们给定where条件不符，咱们要查问对于“张三”的记录，后果返回的后果是”李四“的，很显著的一个bug，然而到8.0.26版本这个问题失去了修改。
怀着对各版本对此语句执行状况的好奇，我先是往前追溯，查看了8.0.24，8.0.23，8.0.22，8.0.21，5.7.39版本上做了测试，发现在8.0.24，8.0.23，8.0.22版本后果与8.0.25雷同，都是谬误后果，而在8.0.21版本上运行后果是正确的，5.7版本上后果也是正确的的。往后追溯，8.0.26，8.0.32版本也都是正确的，因而判断此问题在8.0.22~8.0.25版本上存在此问题。
这个语句最大的特点就是使用了派生表（derived table）,MySQL在8.0.22版本上引入了一个对于派生表的优化器开关derived_condition_pushdown, 默认设置为on。咱们看一下对于这个个性在官网文档中的形容：
MySQL 8.0.22 and later supports derived condition pushdown for eligible subqueries. For a query such as SELECT FROM (SELECT i, j FROM t1) AS dt WHERE i > constant, it is possible in many cases to push the outer WHERE condition down to the derived table, in this case resulting in SELECT FROM (SELECT i, j FROM t1 WHERE i > constant) AS dt.
在8.0.26版本中修复的bug中发现一个与此问题相干的bug。形容如下：
When a condition is pushed down to a materialized derived table, a clone of the derived table expression replaces the column (from the outer query block) in the condition. When the cloned item included a FULLTEXT function, it was added to the outer query block instead of the derived table query block, which led to problems. To fix this, we now use the derived query block to clone such items. (Bug #32820437)
看到这里咱们能够确定，就是8.0.22版本时这个新个性的引入，导致了此问题的产生，庆幸的是这个问题在8.0.26版本中已失去解决。
文章结尾说的问题语句跟这个bug的形容是吻合的，派生表temp内部的过滤条件 temp.type=’张三' 其实是substring(t.type,3)='张三'，应该就是对应bug形容中的”the cloned item included a FULLTEXT function“不论substring函数是不是fulltext函数，总之这个问题随同着这个bug的修复也修复了。这种语句构造下，很多函数都有这个问题，比方trim，replace等。MySQL外部如何解决失去的谬误后果咱们就不去深究了，然而如何躲避这个bug咱们须要理解一下。
二、bug躲避
降级到8.0.26及以上的版本问题天然就解决了，如果不想降级也是有很多形式来躲避此问题的。这个bug的产生次要是因为新个性derived_condition_pushdown的引入，敞开此个性，在这几个版本中就不会呈现这个问题。
mysql> set optimizer_switch="derived_condition_pushdown=off";Query OK, 0 rows affected (0.00 sec)mysql> SELECT temp.type -> FROM (SELECT SUBSTRING(t.type, 3) type -> FROM (SELECT distinct t1.c1 type -> FROM test.t1 -> ORDER BY t1.c1) t) temp -> WHERE temp.type='张三' -> ORDER BY temp.type DESC;+--------+| type |+--------+| 张三 |+--------+1 rows in set (0.00 sec)优化器开关里还有一个派生表相干的开关，就是derived_merge，是否进行派生表合并。敞开这个derived_merge，后果也是正确的。
mysql> set optimizer_switch="derived_merge=off,derived_condition_pushdown=on";Query OK, 0 rows affected (0.00 sec)mysql> SELECT temp.type -> FROM (SELECT SUBSTRING(t.type, 3) type -> FROM (SELECT distinct t1.c1 type -> FROM test.t1 -> ORDER BY t1.c1) t) temp -> WHERE temp.type='张三' -> ORDER BY temp.type DESC;+--------+| type |+--------+| 张三 |+--------+1 rows in set (0.00 sec)也就是说当派生表条件下推撞上派生表合并时，数据库做的解决不对，导致了问题的产生。
因而只有管制不产生合并，或者不产生条件下推，就能躲避此bug。除了敞开优化器开关，在语句级别咱们还有很多形式来躲避，上面列举几个。
1.应用NO_MERGE的hint来阻止derived table合并。
SELECT /*+ NO_MERGE(temp) */ temp.type FROM (SELECT substring(t.type,3) type FROM (SELECT distinct t1.c1 type FROM test.t1 ORDER BY t1.c1) t) temp WHERE temp.type='张三' ORDER BY temp.type DESC;2.应用NO_DERIVED_CONDITION_PUSHDOWN的hint阻止条件下推。
SELECT temp.type FROM (SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN(t) */ substring(t.type,3) type FROM (SELECT distinct t1.c1 type FROM test.t1 ORDER BY t1.c1) t) temp WHERE temp.type='张三' ORDER BY temp.type DESC;3.应用limit子句，能同时阻止合并与条件下推。
例如：
SELECT temp.type FROM (SELECT substring(t.type,3) type FROM (SELECT distinct t1.c1 type FROM test.t1 ORDER BY t1.c1) t limit 100000000000) temp WHERE temp.type='张三' ORDER BY temp.type DESC;4.调配用户变量，阻止derived table 合并。
例如：
SELECT temp.type FROM (SELECT (@i:=0) as num, substring(t.type,3) type FROM (SELECT distinct t1.c1 type FROM test.t1 ORDER BY t1.c1) t) temp WHERE temp.type='张三' ORDER BY temp.type DESC;这种形式就是前文提到的，为什么加了一个与业务逻辑无关的用户变量，后果就正确的起因。
5.应用union all来阻止derived table 合并
SELECT temp.type FROM (SELECT substring(t.type,3) type FROM (SELECT distinct t1.c1 type FROM test.t1 ORDER BY t1.c1) t union all select '1') temp WHERE temp.type='张三' ORDER BY temp.type DESC;这些办法次要是根据优化器应用hint灵便管制优化器的开关，以及derive_merge与derived_condition_pushdown的应用限度。
三、总结
- MySQL8.0.22~MySQL8.0.25 因为优化器新个性
derived_condition_pushdown带来的bug，能够通过derived merge与derived_condition_pushdown的应用限度以及优化器开关hint来无效躲避bug，当然降级到高版本更好。 - 如果想让新个性
derived_condition_pushdown发挥作用，就要避开它的应用限度。 - 一个新个性的呈现，不可避免会随同着一些bug的产生，不要对此心存恐怖，只有深刻理解它，就能取其长，避其短。
Enjoy GreatSQL :)
## 对于 GreatSQL
GreatSQL是由万里数据库保护的MySQL分支，专一于晋升MGR可靠性及性能，反对InnoDB并行查问个性，是实用于金融级利用的MySQL分支版本。
相干链接： GreatSQL社区 Gitee GitHub Bilibili
GreatSQL社区：
社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html
技术交换群：
微信：扫码增加GreatSQL社区助手微信好友，发送验证信息加群。