首页 / MYSQL / mysql5.7 derived_merge=on 影响你的查询了吗?

mysql5.7 derived_merge=on 影响你的查询了吗?

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql5.7 derived_merge=on 影响你的查询了吗?，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2543字，纯文字阅读大概需要4分钟。

内容图文

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

一、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 影响你的查询了吗? - 文章图片

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

查看执行计划如下：

mysql5.7 derived_merge=on 影响你的查询了吗? - 文章图片

两层衍生表，符合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

查看执行计划如下：

mysql5.7 derived_merge=on 影响你的查询了吗? - 文章图片

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

 

内容总结

以上是互联网集市为您收集整理的mysql5.7 derived_merge=on 影响你的查询了吗?全部内容，希望文章能够帮你解决mysql5.7 derived_merge=on 影响你的查询了吗?所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/873158.html

来源：【匿名】