MyException - 我的异常网
当前位置:我的异常网» MySQL » SQL优化-逻辑优化-子查询优化(MySQL)

SQL优化-逻辑优化-子查询优化(MySQL)

www.MyException.Cn  网友分享于:2015-02-08  浏览:0次
SQL优化--逻辑优化--子查询优化(MySQL)

1)子查询概念:当一个查询是另一个查询的子部分时,称之为子查询(查询语句中嵌套有查询语句)。

子查询出现的位置有:

a)目标列位置:子查询如果位于目标列,则只能是标量子查询,否则数据库可能返回类似“错误:  子查询必须只能返回一个字段”的提示。

b)FROM子句位置:相关子查询出现在FROM子句中,数据库可能返回类似“在FROM子句中的子查询无法参考相同查询级别中的关系”的提示,所以相关子查询不能出现在FROM子句中;非相关子查询出现在FROM子句中,可上拉子查询到父层,在多表连接时统一考虑连接代价然后择优。

c)WHERE子句位置:出现在WHERE子句中的子查询,是一个条件表达式的一部分,而表达式可以分解为操作符和操作数;根据参与运算的不同的数据类型,操作符也不尽相同,如INT型有“>、<、=、<>”等操作,这对子查询均有一定的要求(如INT型的等值操作,要求子查询必须是标量子查询)。另外,子查询出现在WHERE子句中的格式,也有用谓词指定的一些操作,如IN、BETWEEN、EXISTS等。

d)JOIN/ON子句位置:JOIN/ON子句可以拆分为两部分,一是JOIN块类似于FROM子句,二是ON子句块类似于WHERE子句,这两部分都可以出现子查询。子查询的处理方式同FROM子句和WHERE子句。

e)GROUPBY子句位置:目标列必须和GROUPBY关联1。可将子查询写在GROUPBY位置处,但子查询用在GROUPBY处没有实用意义。

f)ORDERBY子句位置:可将子查询写在ORDERBY位置处。但ORDERBY操作是作用在整条SQL语句上的,子查询用在ORDERBY处没有实用意义。

2)子查询的分类

从对象间的关系看:

a)相关子查询。

子查询的执行依赖于外层父查询的一些属性值。子查询因依赖于父查询的参数,当父查询的参数改变时,子查询需要根据新参数值重新执行(查询优化器对相关子查询进行优化有一定意义),如:

SELECT * FROM t1 WHERE col_1 = ANY

 (SELECT col_1 FROM t2 WHERE t2.col_2 = t1.col_2);

/* 子查询语句中存在父查询的t1表的col_2列 */

b)非相关子查询。

子查询的执行,不依赖于外层父查询的任何属性值。这样子查询具有独立性,可独自求解,形成一个子查询计划先于外层的查询求解,如:

SELECT * FROM t1 WHERE col_1 = ANY

(SELECT col_1 FROM t2 WHERE t2.col_2 = 10);

//子查询语句中(t2)不存在父查询(t1)的属性

从特定谓词看:

a)[NOT] IN/ALL/ANY/SOME子查询。

语义相近,表示“[取反] 存在/所有/任何/任何”,左面是操作数,右面是子查询,是最常见的子查询类型之一。

b)[NOT] EXISTS子查询。

半连接语义,表示“[取反] 存在”,没有左操作数,右面是子查询,也是最常见的子查询类型之一。

c)其他子查询。

除了上述两种外的所有子查询。

从语句的构成复杂程度看:

a)SPJ子查询。

由选择、连接、投影操作组成的查询。

b)GROUPBY子查询。

SPJ子查询加上分组、聚集操作组成的查询。

c)其他子查询。

GROUPBY子查询中加上其他子句如Top-N 、LIMIT/OFFSET、集合、排序等操作。

后两种子查询有时合称非SPJ子查询。

从结果的角度看:

a)标量子查询。

子查询返回的结果集类型是一个简单值。

b)单行单列子查询。

子查询返回的结果集类型是零条或一条单元组。相似于标量子查询,但可能返回零条元组。

c)多行单列子查询。

子查询返回的结果集类型是多条元组但只有一个简单列。

d)表子查询。

子查询返回的结果集类型是一个表(多行多列)。

3)子查询的优化方法

a)子查询合并(Subquery Coalescing)

在某些条件下(语义等价:两个查询块产生同样的结果集),多个子查询能够合并成一个子查询(合并后还是子查询,以后可以通过其他技术消除掉子查询)。这样可以把多次表扫描、多次连接减少为单次表扫描和单次连接,如:

SELECT * FROM t1 WHERE a1<10 AND (

EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=1) OR 

EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=2) 

);

可优化为:

SELECT * FROM t1 WHERE a1<10 AND (

EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND (t2.b2=1 OR t2.b2=2) 

/*两个ESISTS子句合并为一个,条件也进行了合并 */

);

b)子查询展开(Subquery Unnesting)

又称子查询反嵌套,又称为子查询上拉。把一些子查询置于外层的父查询中,作为连接关系与外层父查询并列,其实质是把某些子查询重写为等价的多表连接操作(展开后,子查询不存在了,外部查询变成了多表连接)。带来的好处是,有关的访问路径、连接方法和连接顺序可能被有效使用,使得查询语句的层次尽可能的减少。

常见的IN/ANY/SOME/ALL/EXISTS依据情况转换为半连接(SEMI JOIN)、普通类型的子查询消除等情况属于此类,如:

SELECT * FROM t1, (SELECT * FROM t2 WHERE t2.a2 >10) v_t2 

WHERE t1.a1<10 AND v_t2.a2<20;

可优化为:

SELECT * FROM t1, t2 WHERE t1.a1<10 AND t2.a2<20 AND t2.a2 >10

/* 子查询变为了t1、t2表的连接操作,相当于把t2表从子查询中上拉了一层 */

子查询展开的条件:

a)如果子查询中出现了聚集、GROUPBY、DISTINCT子句,则子查询只能单独求解,不可以上拉到外层。

b)如果子查询只是一个简单格式的(SPJ格式)查询语句,则可以上拉子查询到外层,这样往往能提高查询效率。子查询上拉,讨论的就是这种格式,这也是子查询展开技术处理的范围。

把子查询上拉到上层查询,前提是上拉(展开)后的结果不能带来多余的元组,所以子查询展开需要遵循如下规则:

a)如果上层查询的结果没有重复(即SELECT子句中包含主码),则可以展开其子查询。并且展开后的查询的SELECT子句前应加上DISTINCT标志。

b)如果上层查询的SELECT语句中有DISTINCT标志,可以直接进行子查询展开。

如果内层查询结果没有重复元组,则可以展开。

子查询展开的具体步骤:

a)将子查询和外层查询的FROM子句连接为同一个FROM子句,并且修改相应的运行参数。

b)将子查询的谓词符号进行相应修改(如:“IN”修改为“=”)。

c)将子查询的WHERE条件作为一个整体与外层查询的WHERE条件合并,并用AND条件连接词连接,从而保证新生成的谓词与原旧谓词的上下文意思相同,且成为一个整体。

c)聚集子查询消除(Aggregate Subquery Elimination)

通常,一些系统支持的是标量聚集子查询消除。如:

SELECT * FROM t1 WHERE t1.a1>(SELECT avg(t2.a2) FROM t2);


摘自《数据库查询优化器的艺术》一书

文章评论

漫画:程序员的工作
漫画:程序员的工作
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
老程序员的下场
老程序员的下场
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
一个程序员的时间管理
一个程序员的时间管理
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
那些争议最大的编程观点
那些争议最大的编程观点
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
Java程序员必看电影
Java程序员必看电影
编程语言是女人
编程语言是女人
每天工作4小时的程序员
每天工作4小时的程序员
旅行,写作,编程
旅行,写作,编程
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
程序员应该关注的一些事儿
程序员应该关注的一些事儿
为什么程序员都是夜猫子
为什么程序员都是夜猫子
程序员和编码员之间的区别
程序员和编码员之间的区别
程序员都该阅读的书
程序员都该阅读的书
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
10个调试和排错的小建议
10个调试和排错的小建议
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
我的丈夫是个程序员
我的丈夫是个程序员
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
程序员的鄙视链
程序员的鄙视链
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
中美印日四国程序员比较
中美印日四国程序员比较
我是如何打败拖延症的
我是如何打败拖延症的
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
 程序员的样子
程序员的样子
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
如何成为一名黑客
如何成为一名黑客
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有