mysql5.7 derived_merge=on 弄丢了我的 order by！

衍生表的優化：合并 | 具化

一、mysql優化器對于衍生表的優化處理可以從兩方面進行：

将衍生表合并到外部查詢
将衍生表具化為内部臨時表

1、示例 1：

SELECT * FROM (SELECT * FROM t1) AS derived_t1;

衍生表

derived_t1 合并處理後，實際執行的查詢類似如下：

SELECT * FROM t1;

複制

2、示例 2:

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

複制

衍生表 derived_t2 合并處理後，實際執行的查詢類似如下：

SELECT t1.*, t2.f1
  FROM t1 JOIN t2 ON t1.f2=t2.f1
  WHERE t1.f1 > 0;

複制

如果是具化操作的話,

derived_t1

和

derived_t2

會被作為獨立的表來進行查詢。

mysql 優化器會盡量避免去具化衍生表。

如果合并操作是的外部表超過61個，則優化器會選擇具化表。

二、優化器關于衍生表中 order by 的處理：

1、在 sql 滿足如下全部條件時，衍生表的 order by 會被放到外部查詢延遲執行，反之，則會被忽略：

外部查詢無分組、聚合操作。
外部查詢沒有使用 DISTINCT , HAVING 或 ORDER BY等操作。
外部查詢隻有衍生表這個唯一的查詢源。

2、可以通過以下幾種方式進行優化器的衍生表合并：

關閉 derived_merge：mysql5.7預設是開啟的。
子查詢使用一些特定操作來組織優化器合并操作：
- 集合函數 ( SUM() , MIN() , MAX() , COUNT() 等等 )
- DISTINCT
- GROUP BY
- HAVING
- LIMIT
- UNION 或者 UNION ALL
- Subqueries in the select list
- Assignments to user variables
- Refererences only to literal values (in this case, there is no underlying table)

三、實際應用

筆者曾經遇到需要查詢關聯同一身份證資訊的所有使用者中最新關聯的使用者記錄：

SELECT id, name, created_at FROM(
    SELECT table1.*, max(table1.created_at) FROM(
        SELECT * FROM users ORDER BY created_at desc
    ) table1 GROUP BY id_no
) table2
ORDER BY id

複制

但是，并沒有得到想要的結果，檢視執行計劃如下：

mysql5.7 derived_merge=on 弄丢了我的 order by！

隻有一個衍生表，但是，看我們的sql，明明有三層查詢。

想到之前，mysql版本做過更新，目前為5.7版本，考慮到mysql5.7版本對于衍生表的優化處理，首先能夠确定的一點是優化器對衍生表做了合并處理，但是僅僅是合并，也不應該影響預期的查詢結果。

參考第二節中介紹的，進一步觀察可知，最内部的 SELECT * FROM users ORDER BY created_at desc 不滿足第二.2中的條件，是以 order by 丢失導緻查詢結果不符合預期。

sql調整：确定記錄不超過10000，是以添加 limit 1000 來阻止優化器對衍生表進行合并操作

SELECT id， name, created_at FROM(
    SELECT table1.*, max(table1.created_at) FROM(
        SELECT * FROM users ORDER BY created_at desc LIMIT 10000
    ) table1 GROUP BY id_no
) table2
ORDER BY id

複制

檢視執行計劃如下：

兩層衍生表，符合sql預期，執行結果也符合預期。

或者，也可以執行如下調整：使用 HAVING 1=1 等true條件

SELECT id， name, created_at FROM(
    SELECT table1.*, max(table1.created_at) FROM(
        SELECT * FROM users HAVING 1=1 ORDER BY created_at desc 
    ) table1 GROUP BY id_no
) table2
ORDER BY id

複制

檢視執行計劃如下：

同樣阻止了優化器的衍生表合并操作。