# 關聯文章： - [MySQL對derived table的優化處理與使用限制](http://mp.weixin.qq.com/s?__biz=MzkzMTIzMDgwMg==&mid=2247501528&idx=1&sn=03ea1cd0eb8f42bacda0583879ad494c&ch ...

關聯文章：

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

MySQL一個關於derived table的bug描述與規避

關聯文章：

一、Bug描述

二、bug規避

三、總結