当ROW_NUMBER遇到TOP

页面导航:首页 > 数据库 > MsSql > 当ROW_NUMBER遇到TOP

当ROW_NUMBER遇到TOP

来源: 作者: 时间:2016-01-20 09:48 【

值班期间研发同事打来电话,说应用有超时,上服务器上检查发现有SQL大批量地执行,该SQL消耗IO资源较多,导致服务器存在IO瓶颈,细看SQL,发现自己都被整蒙了,不知道这SQL是要干啥
值班期间研发同事打来电话,说应用有超时,上服务器上检查发现有SQL大批量地执行,该SQL消耗IO资源较多,导致服务器存在IO瓶颈,细看SQL,发现自己都被整蒙了,不知道这SQL是要干啥,处理完问题赶紧研究下。
 
SQL类似于:
 
 
WITH T1 AS 
(
    SELECT TOP ( 100 )
            ID ,
            ROW_NUMBER() OVER ( ORDER BY C1 ) AS RID
    FROM     [dbo].[TB002]
)
SELECT *
FROM   T1
WHERE  T1.RID > (1-1)*2147483647
    AND T1.RID < 1*2147483647
 
第一赶脚是写这代码的研发同事想分页,但是这每页的数据量有点吓人啊(是我太胆小么?)
 
再仔细看下,赶脚又不是分页,上面还有TOP(100)呢?
 
如果把TOP(100) 放到CTE外面,很容易理解,根据RID列过滤完后再取前100行数据。
 
对于上面的TOP(100) 在CTE内部SQL执行步骤如下
 
1>对表TB002中C1列排序计算每行的RID值,得到临时结果集T1
 
2>对临时结果集T1中数据“随机”取100条(注意:因为CTE中TOP(100) 没有对应ORDER BY 子句,因此无法保证返回的100条数据是有序的,即使在不少场景下返回的数据是按RID排序的) 得到临时结果集T2
 
3>将临时结果集T2的数据按照T1.RID > (1-1)*2147483647 AND T1.RID < 1*2147483647 的条件过滤,得到最终结果集T3
 
4>强最终结果集T3返回给客户端
 
--=========================华丽分割线=======================================--
 
在SQL SERVER 世界里,ROW_NUMBER函数已经有些泛滥成灾,很多不明真相的群众磕着瓜子就把ROW_NUMBER函数写到应用查询中,甚至不少研发同事(抱歉有些人躺枪了)把ROW_NUMBER函数用到登峰造极的程度,当看到一条SQL里使用到N多ROW_NUMBER函数和子查询再加N多大表关联查询,我都对自己DBA的身份表示怀疑,完全看不懂啊!!!
 
--=========================华丽分割线=======================================--
 
回归正题,ROW_NUMBER函数的引入是为了更简单地实现分页,SQL SERVER 查询引擎会将CTE外部的条件引入到CET内部,以避免CTE内部语句执行时访问“无用”数据,如对下面的语句
 
 
;WITH T1 AS 
(
   
    SELECT  ID ,
            ROW_NUMBER() OVER ( ORDER BY ID ) AS RID
    FROM     [dbo].[TB002]
)
SELECT *
FROM   T1
WHERE  T1.RID > 10
    AND T1.RID < 30
 
由于表TB002上ID有索引,因此查询会利用索引访问前30条记录,丢弃不满足RID>10的第1到10条数据。
 
由于这种优化的存在,使得查询无需先执行
 
SELECT  ID ,ROW_NUMBER() OVER ( ORDER BY ID ) AS RID FROM  [dbo].[TB002]
然后再执行WHERE  T1.RID > 10 AND T1.RID < 30 的过滤操作。
 
 
 
但如果CTE内部加入TOP子句,就使得CTE外部的T1.RID > 10 AND T1.RID < 30条件不能引入到CET内部(查询优化器首先得保障返回结果集的正确性,然后才考虑执行的高效性)。对于研发同事也一样,他们首先关注查询结果是否正确,然后才考虑查询效率是否高效,那么引入TOP是否能保证数据正确呢?
 
为了掩饰,我们将查询做轻微调整如下:
 
 
;WITH T1 AS 
(
    SELECT TOP(10) ID ,
            ROW_NUMBER() OVER ( ORDER BY ID ) AS RID
    FROM     [dbo].[TB002]
)
SELECT *
FROM   T1
WHERE  T1.RID > 10
AND T1.RID < 30
 
我们会悲哀地发现,查询返回结果为空,这显然不是一个好兆头,为什么会返回空呢?
 
轻轻推敲一下,我们就会发现,CTE内部的执行结果总是“巧合”地返回RID为1到10的数据,而外部条件RID>10又将这10条数据过滤掉,SO返回为空。
 
PS: 查询优化器真的是“顺手”返回前10条数据,因为恰好这10条数据“在手边”,不能保证其他场景下也是返回RID为1到10的数据,当然也不是查询优化器故意“坑人”哈
 
--=========================华丽分割线=======================================--
 
至此,我总算明白为啥要将写SQL的那位兄弟要传入入2147483647 这么大一个页数量,估计是传小了查不出数据,所以一劳永逸传个最大值,想想也是醉了!
 
--=========================华丽分割线=======================================
 
总结:
 
编写SQL的目的在于实现业务需求,而不是显示个人SQL能力,也没有“一招鲜吃遍天”可以秒杀所有问题的写法,在尊重业务需求的前提下,依据业务场景,考虑数据分布和当前以及未来的数据量,用尽可能简单的SQL地实现业务需求才是王道。
Tags:

相关文章

    文章评论

    最 近 更 新
    热 点 排 行
    Js与CSS工具
    代码转换工具
    
    <