对比SQL中简单嵌套查询与非嵌套查询的异同

SQL Server admin 32℃ 0评论

GPS平台、网站建设、软件开发、系统运维,找森大网络科技!
https://cnsendnet.taobao.com
来自森大科技官方博客
http://www.cnsendblog.com/index.php/?p=2159

 

本文将讨论的是SQL中简单嵌套查询与非嵌套查询的异同,通过这些来更加深刻理解SQL语句。

某天的工作是修复某个项目的bug,接着就发现,其sql极其混乱,有非常多的left join和in操作,还有嵌套查询(只有一个表的嵌套查询)。不知道看到过哪里的资料说,嵌套查询速度慢,于是我把全部嵌套查询都改成join的形式,嵌套查询里面的where条件,我都写到join…on后面去了。突然一个想法冒出来:筛选条件跟在join…on后面 和 跟在整个sql语句最后面的where后面有什么区别呢?还有嵌套查询真的慢么?于是便有下面的测试产生,数据库环境为MS SQL 2005。

一,inner join

先看看非嵌套查询:

  1. select * from t1
  2. inner join t2 on t1.id = t2.id
  3. inner join t3 on t1.id = t3.id
  4. where t1.a=1 and t2.b=1 and t3.c=1
  5. select * from t1
  6. inner join t2 on t1.id = t2.id and t2.b=1
  7. inner join t3 on t1.id = t3.id
  8. where t1.a=1 and t3.c=1
  9. select * from t1
  10. inner join t2 on t1.id = t2.id and t2.b=1
  11. inner join t3 on t1.id = t3.id and t3.c=1
  12. where t1.a=1

在上面三个非嵌套查询,让“and t2.b=1”和“and t3.c=1”分别在join…on和where之间游走,用Management Studio选中“包含实际的执行计划”并执行这三条语句,都得出下面这个执行计划。

1

三个“聚集索引扫描”的谓词从上到下分别是:

1.t3.c=1

2.t1.a=1 (seek谓词:t1.id=t3.id)

3.t2.b=1 (seek谓词:t2.id=t3.id)

故可以认为:在MS SQL2005中,条件跟在join…on后面 和 跟在where后面是等价的。

接着看嵌套查询

  1. selectfrom t1
  2. innerjoin (select * from t2 whereb=1)a on t1.id=a.id
  3. innerjoin t3 onid = t3.id
  4. wherea=1 andt3.c=1
  5. selectfrom t1
  6. innerjoin (select * from t2 whereb=1)a on t1.id=a.id
  7. innerjoin (select * from t3 wherec=1)b on t1.id=b.id
  8. wherea=1
  9. elect * fromt1
  10. innerjoin(selectid,t2.b,t3.c from t3 inner join t2 on t2.id = t3.id where t2.b=1 and t3.c=1)a on t1.id=a.id
  11. wherea=1

第一句sql语句把t2的查询变成子查询,第二句sql语句把t2,t3分别变成子查询,第三句把t2和t3的查询合成一个子查询,再看看实际的执行计划:

2

跟上面非嵌套查询的执行计划一模一样。

故可以认为:简单(注意是简单的,复杂的情况得另外考虑)嵌套查询和其相对应的非嵌套查询形式,执行效率是一样的(网上一些文章指出这是MS SQL优化器针对这些嵌套查询进行了优化)。

接着,在上面两个执行计划的图中又发现一个小问题,为什么明明是select t1 inner join t2 inner join t3,执行计划却把t1和t3先inner join(t1.id = t3.id)再跟t2 inner join(t2.id = t3.id)起来?

经过三个表,四个表,五个表进行连接测试,发现这些顺序都是不确定的。很可能这些顺序是根据SQL优化器内的算法所决定的,由于没有源代码,所以无从考究。

(感谢Keep Walking的补充:

“可以指定顺序,force order选项,和keep plan选项

数量级,索引,统计的不同都可以导致顺序变化”。

我Google了一下option force order和option keep plan,发现SQL优化器做了很多事情,在这文章就不列出来了,大家有兴趣可以Goo一下。)

PS:

1.经测试,在join on后面t1.id = t2.id与t2.id = t1.id等价

 

GPS平台、网站建设、软件开发、系统运维,找森大网络科技!
https://cnsendnet.taobao.com
来自森大科技官方博客
http://www.cnsendblog.com/index.php/?p=2159

转载请注明:森大科技官方博客 » 对比SQL中简单嵌套查询与非嵌套查询的异同

喜欢 (2)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址