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

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

一、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默认是开启的。

子查询使用一些特定操作来组织优化器合并操作：

DISTINCT

GROUP BY

HAVING

LIMIT

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) table1GROUP BYid_no

) table2ORDER 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) table1GROUP BYid_no

) table2ORDER 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) table1GROUP BYid_no

) table2ORDER BY id

查看执行计划如下：

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

原文链接：https://www.cnblogs.com/niejunlei/p/12903234.html

本站声明:网站内容来源于网络,若有侵权,请联系我们,我们将及时处置。