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默认是开启的。
子查询使用一些特定操作来组织优化器合并操作:
三、实际应用
笔者曾经遇到需要查询关联同一身份证信息的所有用户中最新关联的用户记录:
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
) 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 = ORDER BY created_at desc
) table1 GROUP BY id_no
) table2
ORDER BY id
查看执行计划如下:
同样阻止了优化器的衍生表合并操作。
最新文章
- JavaScript打开窗口与关闭页面操作大全
- 可以考虑使用SublimeText编辑器替代notepad++了
- 微信Accesstoken通过xml文件方式保存
- Jenkins-测试自动化环境搭建(Python+RobotFramework+selenium)
- PCB电路板上防潮绝缘抗腐蚀的三防漆
- Java IO (4) - Writer
- skiplist 跳表(1)
- hdoj 2473 Junk-Mail Filter【并查集节点的删除】
- Html表格自动换行
- Moving Acerage
- coreOS+Docker新一代企业轻量级Linux
- security
- Laravel-表单篇-controller
- libcurl提交表单上传文件
- 多线程11_张孝祥 java5的线程锁技术
- Java设计模式———静态工厂
- Android BLE与终端通信(四)——实现服务器与客户端即时通讯功能
- 【CSS学习】--- 字体样式
- LeetCode【110. 平衡二叉树】
- 移植busybox构建最小根文件系统
热门文章
- MySQL笔记总结-其他
- go 基础 结构体
- kubernetes的headless service介绍
- 分析 Nanocore
- 在Eclipse上实现简单的JDBC增删查改操作
- 五分钟学会Python装饰器,看完面试不再慌
- 在php中如何实现cookie即时生效,不用刷新就可以使用
- 关于flex弹性布局
- RabbitMQ Hello world(二)
- 解决 docker.io 上拉取 images Get https://registry-1.docker.io/v2/: net/http: TLS handshake timeout