说说Top子句对查询计划的影响

运维 数据库运维 SQL Server
之前我们谈论过SQL数据库中"简单的"SELECT TOP中大家可能没有注意到的细节。今天将和大家分享Top子句对查询计划的影响——简单说慎用TOP。

1子查询的影响

Nest loop适用于被连接的数据

如果两个表做join操作,会有三种join方式: Nested join, Merge Join, Hash Join

Nested Join适用于结果集较小表

Hash Join适用于结果集很大的表

示例如下

  1. create table moderatetable1(id int identity(1,1) primary key, c1 int ,c2 int,c3 int,c4 int)  
  2. create table moderatetable2(id int identity(1,1) primary key, c1 int ,c2 int,c3 int,c4 int)  
  3. declare @n int=0  
  4. while @n<100000  
  5. begin 
  6. insert moderatetable1(c1,c2,c3,c4) values(@n,@n,@n,@n)  
  7. insert moderatetable2(c1,c2,c3,c4) values(@n,@n,@n,@n)  
  8. set @n+=1  
  9. end 
  10. create index index1 on moderatetable1(C1)  
  11. create index index1 on moderatetable2(C2)  
  12. go  
  13. set statistics io on 
  14. select t1.c1 from moderatetable1 t1 inner join moderatetable2 t2  
  15. on t1.c1=t2.c1  
  16. go 

下图是上面查询的执行计划和io统计信息

 

IO情况

(100000 行受影响)

表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

表 'moderatetable2'。扫描计数 1,逻辑读取 361 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

表 'moderatetable1'。扫描计数 1,逻辑读取 176 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

(1 行受影响)

如果只想取前50行,可以指定top 50:

  1. select top 50 t1.c1 from moderatetable1 t1 inner join moderatetable2 t2 
  2. on t1.c1=t2.c1

 

(50 行受影响)

表 'moderatetable1'。扫描计数 50,逻辑读取 124 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

表 'moderatetable2'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

(1 行受影响)

我们看到,当指定了top 50之后,查询计划成了nested join. 当使用TOP时,SQLSEVER会认为这是一个较小的数据集,所以会使用nested join.对于这个查询,IO的开销比较李小. 但SQLSERVER经常会估计错误(即使统计信息是正确的).

我们看一下下面的查询:

  1. select top 500 t1.c1 from moderatetable1 t1 inner join moderatetable2 t2  
  2. on t1.c1=t2.c1 
 

 

(500 行受影响)

表 'moderatetable1'。扫描计数 500,逻辑读取 1080 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

表 'moderatetable2'。扫描计数 1,逻辑读取 4 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

(1 行受影响)

返回行数增加到了500,SQLSERVER仍然使用nested join,得到了较差的IO

随着TOP的行数的增多,IO开销会越来越大. 但也不是总是这样,当top值达到一个临界点后,执行计划会变更成hash join.

  1. select top 20000 t1.c1 from moderatetable1 t1 inner join moderatetable2 t2  
  2. on t1.c1=t2.c1 

 

(20000 行受影响)

表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

表 'moderatetable2'。扫描计数 1,逻辑读取 74 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

表 'moderatetable1'。扫描计数 1,逻辑读取 176 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

(1 行受影响)

慎用TOP ...

原文链接:http://www.cnblogs.com/stswordman/archive/2011/06/14/2080396.html

【编辑推荐】

  1. 分析TOP语句放到表值函数外,效率异常低下的原因
  2. 双TOP二分法生成分页SQL类
  3. SQL Server数据库中简单的SELECT TOP
  4. 利用top构造Sql Server分页查询
责任编辑:艾婧 来源: stswordman的博客
相关推荐

2011-08-04 13:07:59

数据库查询TOP子句

2020-12-04 07:51:24

CQRS模型查询

2009-09-16 13:02:12

LINQ查询子句

2017-09-22 11:01:00

Oracle数据库中直方图

2010-04-02 15:04:14

Oracle递归查询

2015-11-04 14:03:44

BYOD自带设备企业

2013-09-29 09:49:04

编程生活方式

2011-06-01 17:45:22

SEO

2011-05-06 16:22:58

2009-06-12 09:44:04

LINQ查询复合from子句

2013-03-28 10:53:11

2009-08-27 13:10:54

LINQ from子句

2021-10-13 22:38:42

数字货币货币人类

2011-05-24 16:01:51

OpenFlow影响

2023-09-05 15:48:00

2022-12-15 08:00:38

JavaScript错误性能

2022-11-17 08:00:18

JavaScript错误性能

2022-09-27 15:00:18

物联网IoT

2014-07-22 09:25:48

LTEPTN4G

2021-08-09 07:47:40

Git面试版本
点赞
收藏

51CTO技术栈公众号