SQLServer利用鎖提示優化Row_number()-程序員需知_MySQL

來源：懂視網責編：小采時間：2020-11-09 20:10:54

SQLServer利用鎖提示優化Row_number()-程序員需知_MySQL

SQLServer利用鎖提示優化Row_number()-程序員需知_MySQL:網站中一些老頁面仍采用Row_number類似的開窗函數進行分頁處理,此時如果遭遇挖墳帖的情形可能就需要漫長的等待且消耗巨大.這里給大家介紹根據Row_number()特性采用特定鎖Hint提升查詢速度. 直接上菜腳本環境可在SQL Server優化技巧之SQL Serve

推薦度：

點擊下載本文 文檔為doc格式

導讀SQLServer利用鎖提示優化Row_number()-程序員需知_MySQL:網站中一些老頁面仍采用Row_number類似的開窗函數進行分頁處理,此時如果遭遇挖墳帖的情形可能就需要漫長的等待且消耗巨大.這里給大家介紹根據Row_number()特性采用特定鎖Hint提升查詢速度. 直接上菜腳本環境可在SQL Server優化技巧之SQL Serve

網站中一些老頁面仍采用Row_number類似的開窗函數進行分頁處理,此時如果遭遇挖墳帖的情形可能就需要漫長的等待且消耗巨大.這里給大家介紹根據Row_number()特性采用特定鎖Hint提升查詢速度.

直接上菜

腳本環境可在SQL Server優化技巧之SQL Server中的"MapReduce"找到

如下查詢在分頁中比較常見

set statistics time on
 
 select * from 
(
select ProductID, rn = ROW_NUMBER() OVER (ORDER BY ProductID)
from [bigTransactionHistory]
) as t
where t.rn between 15631801 and 15631802

這條查詢在我的電腦上執行了15S,這還是數據全在內存中的情形!如圖1-1

一個簡單的執行計劃執行如此之長有點匪夷所思,畢竟邏輯讀才6W多,且無物理讀

,而且CPU時間與占用時間相差無幾,排除了阻塞之類的因素后我們把消耗定位在這個查詢本身上.這時提一個Row_number()的特點,它可在萬千數據中將其序列化讓我們找到我們想要的精確數據點,但就此默認的實現方式上是為每一行數據都加一個行鎖.

我們開啟Trace Flag 1200再次執行語句捕捉下執行時的鎖.可以看到Row_number()在實現上未進行鎖升級如圖1-2

dbcc traceon(3604,1200,-1)

select * from 
(
select ProductID, rn = ROW_NUMBER() OVER (ORDER BY ProductID)
from [bigTransactionHistory]
) as t
where t.rn between 15631801 and 15631802

到此我們對此問題的解決方式也就出來了:可采用鎖hint的形式手動為其升級

這里我采用頁鎖,如圖1-3

而兩者從執行計劃上看是相同的,預估也完全一樣如圖1-4

select * from 
(
select ProductID, rn = ROW_NUMBER() OVER (ORDER BY ProductID)
from [bigTransactionHistory] with(paglock)
) as t
where t.rn between 15631801 and 15631802

可以看到我們通常的查看執行計劃的方式在此就不太適合了,需要我們對資源消耗有更詳細的認知.

注:平時我們還可用Trace Profiler捕捉鎖,但需注意慎用.

Row_number()默認看不到鎖升級,全局性能瓶頸下可能回升級

如果你的應用不在乎臟讀,nolock方式更愉快:)

其它:當數據被更新發生阻塞時,有時業務同事會問到底更新了哪條數據有木有?

這里寫了個簡單的查詢以便找到具體更新被鎖住的行,如圖2-1

begin tran ttt
update dbo.[bigProduct] set size=111 where ProductID<1100
-- rollback when finish test
--rollback tran ttt

--open another session

SELECT * FROM [bigProduct] with(nolock)
WHERE
 %%LOCKRES%% IN
 (
 SELECT 
 tl.resource_description
 FROM sys.dm_tran_locks AS tl
 INNER JOIN sys.partitions AS t2 ON
 t2.hobt_id = tl.resource_associated_entity_id
 WHERE 
 t2.object_id = OBJECT_ID('bigProduct')
 AND tl.resource_type = 'KEY'
 )

結語:系統內任何元素都有可能成為影響平衡的絆腳石.找到它,理解它,利用它.

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com

SQLServer利用鎖提示優化Row_number()-程序員需知_MySQL

推薦度：

點擊下載本文 文檔為doc格式

標簽：提示程序 sql

熱門焦點

SQLServer利用鎖提示優化Row_number()-程序員需知_MySQL

SQLServer利用鎖提示優化Row_number()-程序員需知_MySQL

SQLServer利用鎖提示優化Row_number()-程序員需知_MySQL

最新推薦

猜你喜歡

熱門推薦