??斗地主捕鱼电竞提现秒到 广告位招租 - 15元/月全站展示
??支付宝搜索579087183领大额红包 ??伍彩集团官网直营彩票
??好待遇→招代理 ??伍彩集团官网直营彩票
??络茄网 广告位招租 - 15元/月全站展示
子查询合并Derived_merge

转载   Darren_Chen   2018-11-12   浏览量:15


1、Derived_merge简介

MySQL Reference manual是这么描述的:

The derived_merge flag controls whether the optimizer attempts to merge subqueries and views in the FROM clause into the outer query block, assuming that no other rule prevents merging. By default, the flag is on to enable merging. Setting the flag to off prevents merging.


其实derived_merge是系统变量optimizer_switch众多参数中的一个参数选项,从5.7.6版本(包括5.7.6)开始支持,默认值是derived_merge=on,用来控制优化器是否合并衍生表或视图的。


注意

本文实验的所有环境都是MySQL8.0.13;

derived_merge是MySQL5,6和MySQL5.7比较重要的一个区别,对SQL优化很是重要,笔者曾遇到过相关案例,类似于本文第4部分案例。


2.Derived_merge示例

select * from (select * from t_group)as t1;

子查询合并后等价于select * from  t_group;

设置derived_merge=on,从执行计划和warnings中可以看到from后面的子查询被合并了,

mysql> set optimizer_switch='derived_merge=on';

mysql> desc select * from (select * from t_group)as t1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_group | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `employees`.`t_group`.`emp_no` AS `emp_no`,`employees`.`t_group`.`dept_no` AS `dept_no`,`employees`.`t_group`.`from_date` AS `from_date`,`employees`.`t_group`.`to_date` AS `to_date` from `employees`.`t_group`
1 row in set (0.00 sec)


设置derived_merge=off,从执行计划和warnings中可以看到from后面的子查询仍然是独立的一个子查询,并没有去掉括号被合并

mysql> set optimizer_switch='derived_merge=off';

mysql> desc select * from (select * from t_group)as t1;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
|  2 | DERIVED     | t_group    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> desc select * from (select * from t_group)as t1;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
|  2 | DERIVED     | t_group    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `t1`.`emp_no` AS `emp_no`,`t1`.`dept_no` AS `dept_no`,`t1`.`from_date` AS `from_date`,`t1`.`to_date` AS `to_date` from (/* select#2 */ select `employees`.`t_group`.`emp_no` AS `emp_no`,`employees`.`t_group`.`dept_no` AS `dept_no`,`employees`.`t_group`.`from_date` AS `from_date`,`employees`.`t_group`.`to_date` AS `to_date` from `employees`.`t_group`) `t1`
1 row in set (0.00 sec)


3、防止Derived_merge的一些技巧

因为derived_merge默认是on,但是有时候我们又不想通过修改配置参数不让子查询合并,那么还有其他办法吗?当然,可以通过在子查询中添加关键字的方法:

聚合函数 (SUM(), MIN(), MAX(), COUNT(), and so forth)

distinct

group by

having

limit

union or union all

使用变量符号@


例如:

mysql> desc select * from (select count(*) from t_group)as t1;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
|  2 | DERIVED     | t_group    | NULL       | ALL    | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> desc select * from (select distinct * from t_group)as t1;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL            |
|  2 | DERIVED     | t_group    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using temporary |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
2 rows in set, 1 warning (0.00 sec)

mysql> desc select * from (select dept_no from t_group group by dept_no)as t1;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL            |
|  2 | DERIVED     | t_group    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using temporary |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
2 rows in set, 1 warning (0.00 sec)

mysql> desc select * from (select * from t_group having emp_no > 15000)as t1;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
|  2 | DERIVED     | t_group    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> desc select * from (select * from t_group union select * from t_order)as t1;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | NULL            |
|  2 | DERIVED      | t_group    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL            |
|  3 | UNION        | t_order    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL            |
| NULL | UNION RESULT | <union2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

mysql> desc select * from t_group t join (select @rn:=10001 emp_no)e on t.emp_no=e.emp_no;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL           |
|  1 | PRIMARY     | t          | NULL       | ALL    | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where    |
|  2 | DERIVED     | NULL       | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
3 rows in set, 2 warnings (0.00 sec)


4、如果子查询数据量特别大,子查询合并可以起到优化SQL的效果,相当于减少了子查询执行次数。

例如如下一条SQL,当关闭子查询合并功能的时候,SQL执行需要9秒多,开启子查合并功能后,时间为5秒,效率提高了1倍;

如果当前使用的是MySQL5,6版本,不支持derived_merge功能,这个时候我们可以通过改写SQL,减少子查询的方法来提高SQL效率,这也是优化SQL的一条思路。


没有发生子查询合并的SQL执行情况:

set optimizer_switch='derived_merge=off';

mysql> select
    -> total.emp_no,
    -> total.salary,
    -> total.to_date,
    -> total.last_name,
    -> total.hire_date
    -> from
    -> (select
    -> s.emp_no emp_no,
    -> s.salary salary,
    -> s.to_date to_date,
    -> e.last_name last_name,
    -> e.hire_date hire_date
    -> from salaries s
    -> inner join employees e where s.emp_no=e.emp_no) total;
2844047 rows in set (9.48 sec)

mysql> desc select
    -> total.emp_no,
    -> total.salary,
    -> total.to_date,
    -> total.last_name,
    -> total.hire_date
    -> from
    -> (select
    -> s.emp_no emp_no,
    -> s.salary salary,
    -> s.to_date to_date,
    -> e.last_name last_name,
    -> e.hire_date hire_date
    -> from salaries s
    -> inner join employees e where s.emp_no=e.emp_no limit 10000000) total;
+----+-------------+------------+------------+------+----------------+---------+---------+-----------------+---------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys  | key     | key_len | ref             | rows    | filtered | Extra |
+----+-------------+------------+------------+------+----------------+---------+---------+-----------------+---------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL           | NULL    | NULL    | NULL            | 2995588 |   100.00 | NULL  |
|  2 | DERIVED     | e          | NULL       | ALL  | PRIMARY        | NULL    | NULL    | NULL            |  299512 |   100.00 | NULL  |
|  2 | DERIVED     | s          | NULL       | ref  | PRIMARY,emp_no | PRIMARY | 4       | testdb.e.emp_no |      10 |   100.00 | NULL  |
+----+-------------+------------+------------+------+----------------+---------+---------+-----------------+---------+----------+-------+
3 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `total`.`emp_no` AS `emp_no`,`total`.`salary` AS `salary`,`total`.`to_date` AS `to_date`,`total`.`last_name` AS `last_name`,`total`.`hire_date` AS `hire_date` from (/* select#2 */ select `testdb`.`s`.`emp_no` AS `emp_no`,`testdb`.`s`.`salary` AS `salary`,`testdb`.`s`.`to_date` AS `to_date`,`testdb`.`e`.`last_name` AS `last_name`,`testdb`.`e`.`hire_date` AS `hire_date` from `testdb`.`salaries` `s` join `testdb`.`employees` `e` where (`testdb`.`s`.`emp_no` = `testdb`.`e`.`emp_no`) limit 10000000) `total`
1 row in set (0.00 sec)


发生子查询合并的SQL执行情况:

mysql> set optimizer_switch='derived_merge=on';

mysql> select
    -> total.emp_no,
    -> total.salary,
    -> total.to_date,
    -> total.last_name,
    -> total.hire_date
    -> from
    -> (select
    -> s.emp_no emp_no,
    -> s.salary salary,
    -> s.to_date to_date,
    -> e.last_name last_name,
    -> e.hire_date hire_date
    -> from salaries s
    -> inner join employees e where s.emp_no=e.emp_no) total;
2844047 rows in set (5.03 sec)

mysql> desc select
    -> total.emp_no,
    -> total.salary,
    -> total.to_date,
    -> total.last_name,
    -> total.hire_date
    -> from
    -> (select
    -> s.emp_no emp_no,
    -> s.salary salary,
    -> s.to_date to_date,
    -> e.last_name last_name,
    -> e.hire_date hire_date
    -> from salaries s
    -> inner join employees e where s.emp_no=e.emp_no) total;
+----+-------------+-------+------------+------+----------------+---------+---------+-----------------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys  | key     | key_len | ref             | rows   | filtered | Extra |
+----+-------------+-------+------------+------+----------------+---------+---------+-----------------+--------+----------+-------+
|  1 | SIMPLE      | e     | NULL       | ALL  | PRIMARY        | NULL    | NULL    | NULL            | 299512 |   100.00 | NULL  |
|  1 | SIMPLE      | s     | NULL       | ref  | PRIMARY,emp_no | PRIMARY | 4       | testdb.e.emp_no |     10 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------+---------+---------+-----------------+--------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `testdb`.`s`.`emp_no` AS `emp_no`,`testdb`.`s`.`salary` AS `salary`,`testdb`.`s`.`to_date` AS `to_date`,`testdb`.`e`.`last_name` AS `last_name`,`testdb`.`e`.`hire_date` AS `hire_date` from `testdb`.`salaries` `s` join `testdb`.`employees` `e` where (`testdb`.`s`.`emp_no` = `testdb`.`e`.`emp_no`)
1 row in set (0.00 sec)


参考链接

Section 8.2.2.3, “Optimizing Derived Tables and View References”.

转载自://blog.51cto.com/darrenmemos/2316124

招聘 不方便扫码就复制添加关注:程序员招聘谷,微信号:jobs1024


上一篇:

MySQL8.0简介
MySQL8.0正式版8.0.11已发布,官方表示MySQL8要比MySQL5.7快2倍,还带来了大量的改进和更快的性能!注意:从MySQL5.7升级到MySQL8.0仅支持通过使用in-place方式进行升级,并且不支持从MySQL8.0降级到MySQL5.7(或从某个MySQL8.0版本降级到任意一个更早的MySQL8.0版本)。唯...
MYSQL5.7基于SSL的主从复制
本文档使用的是mysql版本为5.7.22,linux内核为3.10.0-862.el7.x86_64。一、首先主从复制的原理:1、master服务器的binarylog(二进制)将用户输入的增删改的SQL语句通过二进制日志记录下来,并通知存储引擎提交事务。2、slave将master的binarylog复制到自己的中继日志。首先,slave开启一个工作线程(I/O线程),I/O线程在master
【超简单】MySQL存储引擎的选择与配置
【超简单】MySQL存储引擎的选择与配置存储引擎简介MySQL中的数据用各种不同的技术存储在文件(或内存)中。每一种技术都使用不同的存储机构,索引技巧,锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术,能够获得额外的速度或者功能,从而改善应用的整体功能。这些不同的技术以及配套的相关功能在MySQL中被称为存储引擎。下面就为大家介绍两种MySQL中比较常见的两种存储引擎:MyISAM和
MySQL 5.6中的变量
这篇文章介绍的是MySQL?5.6中的变量,基本是我以前学习MySQL5.6手册时整理而来。?基础概念MySQL中的变量可分为以下几种:1、MySQL系统变量:该类型变量反映了MySQL服务器是如何配置的。每一个系统变量都有一个默认值。系统变量可以在MySQL配置文件中进行设置,或使用MySQL启动选项进行设置。大多数系统变量都可以在MySQL运行时使用SET命令进行动态修改。2、
MySQL 5.6中的字符集
这篇文章介绍的是MySQL5.6中的字符集,基本是我以前学习MySQL5.6手册时整理而来。?概论基础概念字符集(characterset)是编码和字符符号的映射集合。排序规则(collation)是用于比较字符集中字符的规则集。?现在我们自定义一个简单的字符集characterset。假设我们有一个仅有四个字母的字母表:A、B、a、b。我们给每个字母一个数字:A=0,B
【纯干货、小技巧】遗忘MySQL用户密码怎么办?
遗忘MySQL用户密码怎么办?在信息化社会,充分有效地管理和利用各类信息资源,是进行科学研究和决策管理的前提条件。数据库技术是管理信息系统、办公自动化系统、决策支持系统等各类信息系统的核心部分,是进行科学研究和决策管理的重要技术手段。数据库,就是本身可视为电子化的文件柜--存储电子文件的处所,用户可以对文件中的数据进行新增、截取、更新、删除等操作。数据库指的是以一定方式储存在一起、能为多个用户共享
MySQL逻辑架构(一)
MySQL逻辑系统架构:可以分为连接器,连接池,Sql接口,解释器,优化库,缓冲区,缓存,引擎MySQL逻辑结构刻意看成二层架构,第一层通常叫做SQLLayer在数据库系统处理底层数据包括权限判断,SQL解析执行计划优化,querychche的出理等等第二层就是存储引擎,通常叫做storageEngineLayer也就是底层数据存取操作的实现部分有多种存储引擎共同组成MySQL存储引擎介绍:MyS
Centos7安装默认mariadb5.5
一、安装MariaDB1、安装$yum-yinstallmariadbmariadb-server2、启动MariaDB$systemctlstartmariadb3、设置开机启动$systemctlenablemariadb4、配置安全策略$mysql_secure_installation首先设置密码,会提示先输入密码Entercurrentpasswordforroot(enterforno
MySQL5.7查询性能改进
MySQL5.7查询性能改进
MySQL5.5升级至5.7
一、准备工作新的服务器(10.12.21.184),作为从库在21.184上下载MySQL5.6、5.7的最新稳定版本的二进制包hostrole10.12.21.120master10.12.21.184slave二、操作1.主从搭建xtrbackup全备(20.120)根据全备在20.184上启动新的5.5数据库,作为20.120的从库启动主从,等待从库追上主库2.升级从库1.解压文件包cd/d