值班期間研發(fā)同事打來電話,說應(yīng)用有超時,上服務(wù)器上檢查發(fā)現(xiàn)有SQL大批量地執(zhí)行,該SQL消耗IO資源較多,導(dǎo)致服務(wù)器存在IO瓶頸,細看SQL,發(fā)現(xiàn)自己都被整蒙了,不知道這SQL是要干啥,處理完問題趕緊研究下,
當(dāng)ROWNUMBER遇到TOP
。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
第一趕腳是寫這代碼的研發(fā)同事想分頁,但是這每頁的數(shù)據(jù)量有點嚇人。ㄊ俏姨懶∶?)
再仔細看下,趕腳又不是分頁,上面還有TOP(100)呢?
如果把TOP(100) 放到CTE外面,很容易理解,根據(jù)RID列過濾完后再取前100行數(shù)據(jù)。
對于上面的TOP(100) 在CTE內(nèi)部SQL執(zhí)行步驟如下
1>對表TB002中C1列排序計算每行的RID值,得到臨時結(jié)果集T1
2>對臨時結(jié)果集T1中數(shù)據(jù)“隨機”取100條(注意:因為CTE中TOP(100) 沒有對應(yīng)ORDER BY 子句,因此無法保證返回的100條數(shù)據(jù)是有序的,即使在不少場景下返回的數(shù)據(jù)是按RID排序的) 得到臨時結(jié)果集T2
3>將臨時結(jié)果集T2的數(shù)據(jù)按照T1.RID > (1-1)*2147483647 AND T1.RID < 1*2147483647 的條件過濾,得到最終結(jié)果集T3
4>強最終結(jié)果集T3返回給客戶端
--=========================華麗分割線=======================================--
在SQL SERVER 世界里,ROW_NUMBER函數(shù)已經(jīng)有些泛濫成災(zāi),很多不明真相的群眾磕著瓜子就把ROW_NUMBER函數(shù)寫到應(yīng)用查詢中,甚至不少研發(fā)同事(抱歉有些人躺槍了)把ROW_NUMBER函數(shù)用到登峰造極的程度,當(dāng)看到一條SQL里使用到N多ROW_NUMBER函數(shù)和子查詢再加N多大表關(guān)聯(lián)查詢,我都對自己DBA的身份表示懷疑,完全看不懂啊。!
--=========================華麗分割線=======================================--
回歸正題,ROW_NUMBER函數(shù)的引入是為了更簡單地實現(xiàn)分頁,SQL SERVER 查詢引擎會將CTE外部的條件引入到CET內(nèi)部,以避免CTE內(nèi)部語句執(zhí)行時訪問“無用”數(shù)據(jù),如對下面的語句
;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條數(shù)據(jù),
電腦資料
《當(dāng)ROWNUMBER遇到TOP》(http://www.szmdbiao.com)。由于這種優(yōu)化的存在,使得查詢無需先執(zhí)行
SELECT ID ,ROW_NUMBER() OVER ( ORDER BY ID ) AS RID FROM [dbo].[TB002]
然后再執(zhí)行WHERE T1.RID > 10 AND T1.RID < 30 的過濾操作。
但如果CTE內(nèi)部加入TOP子句,就使得CTE外部的T1.RID > 10 AND T1.RID < 30條件不能引入到CET內(nèi)部(查詢優(yōu)化器首先得保障返回結(jié)果集的正確性,然后才考慮執(zhí)行的高效性)。對于研發(fā)同事也一樣,他們首先關(guān)注查詢結(jié)果是否正確,然后才考慮查詢效率是否高效,那么引入TOP是否能保證數(shù)據(jù)正確呢?
為了掩飾,我們將查詢做輕微調(diào)整如下:
;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
我們會悲哀地發(fā)現(xiàn),查詢返回結(jié)果為空,這顯然不是一個好兆頭,為什么會返回空呢?
輕輕推敲一下,我們就會發(fā)現(xiàn),CTE內(nèi)部的執(zhí)行結(jié)果總是“巧合”地返回RID為1到10的數(shù)據(jù),而外部條件RID>10又將這10條數(shù)據(jù)過濾掉,SO返回為空。
PS: 查詢優(yōu)化器真的是“順手”返回前10條數(shù)據(jù),因為恰好這10條數(shù)據(jù)“在手邊”,不能保證其他場景下也是返回RID為1到10的數(shù)據(jù),當(dāng)然也不是查詢優(yōu)化器故意“坑人”哈
--=========================華麗分割線=======================================--
至此,我總算明白為啥要將寫SQL的那位兄弟要傳入入2147483647 這么大一個頁數(shù)量,估計是傳小了查不出數(shù)據(jù),所以一勞永逸傳個最大值,想想也是醉了!
--=========================華麗分割線=======================================
編寫SQL的目的在于實現(xiàn)業(yè)務(wù)需求,而不是顯示個人SQL能力,也沒有“一招鮮吃遍天”可以秒殺所有問題的寫法,在尊重業(yè)務(wù)需求的前提下,依據(jù)業(yè)務(wù)場景,考慮數(shù)據(jù)分布和當(dāng)前以及未來的數(shù)據(jù)量,用盡可能簡單的SQL地實現(xiàn)業(yè)務(wù)需求才是王道。