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