专栏首页后端码事mysql5.7 derived_merge=on 弄丢了我的 order by!

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_t1derived_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

查看执行计划如下:

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 基于spring-boot、grpc、zookeeper的分布式微服务架构

    Protocol buffer 已经开源一段时间了,项目示例目前使用proto3版本,最新版本对一些定义做了简化,添加了一些新的特性,并且在语言支持上做了扩展。

    WindWant
  • maven 安装alipay-sdk包到本地及远程仓库

    WindWant
  • Mysql Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operat

    resolve:将比较等式的一边进行字符串转换,如:“CONVERT(a.field_a USING utf8) COLLATE utf8_unicode_ci...

    WindWant
  • Thinking in SQL系列之数据挖掘C4.5决策树算法

    作者简介:牛超 ? 10多年数据库技术积累,长期从事ORACLE数据库管理与开发工作。精通企业级数据库应用设计、SQL、算法实现、异常分析、性能优化。目前...

    数据和云
  • 边缘计算牵手工业互联网,靠谱的未来长啥样?

    不管是5G基建、特高压,还是新能源汽车充电桩、工业互联网等,这几大方向都对边缘计算有着极大的需求。

    边缘计算
  • 这4件事,让你了解边缘计算的真实面貌

    边缘计算已在技术时代精神中占据一席之地,具备创新力和前沿性。几年来,人们一直认为边缘计算一定会成为未来的一种计算方式。但实际上,讨论仍然只是假设性的,因为支持边...

    k3s中文社区
  • 数据蒋堂 | 报表工具的SQL植入风险

    所有的报表工具都会提供参数功能,主要都是用于根据用户输入的查询条件来选取合适的数据。比如希望查询指定时间段的数据,就可以把时间段作为参数传递给报表,报表在从数据...

    数据派THU
  • 边缘计算核心技术辨析

    边缘计算(Edge Computing)是云计算向边缘的延伸,本文对边缘计算、雾计算、MEC、Cloudlet、分布式云等边缘计算领域相关概念和技术的定义、架构...

    CloudBest
  • 边缘计算核心技术辨析

    边缘计算(Edge Computing)是云计算向边缘的延伸,本文对边缘计算、雾计算、MEC、Cloudlet、分布式云等边缘计算领域相关概念和技术的定义、架构...

    边缘计算
  • HBase 基本入门篇

    无论是 NoSQL,还是大数据领域,HBase 都是非常"炙热"的一门数据库。本文将对 HBase 做一些基础性的介绍,旨在入门。

    黄泽杰

扫码关注云+社区

领取腾讯云代金券