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

但是，并没有得到想要的结果，查看执行计划如下：

只有一个衍生表，但是，看我们的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

查看执行计划如下：

同样阻止了优化器的衍生表合并操作。