為什么80%的碼農(nóng)都做不了架構(gòu)師？>>> ??

今天客戶系統(tǒng)升級，通過DMVs性能分析查了一下，升級后發(fā)現(xiàn)一個語句執(zhí)行時間比較長，執(zhí)行語句要好幾秒鐘，調(diào)出語句如下：

select?distinct?field003?from?ufi2j0n11179717502375?where?
field003?not?in?('','40288135120d660501120de2f8870140','40288135120d660501120de4b9ee014b',
'40288135120d660501120de9c3ba016c','40288135120d660501120df0460c01b2','40288135120d660501120df1dc2d01d3')?and???requestid?in(select?requestid?from?ufi8s6u81179717475734?where?field001?in?(select?requestid?from? uft3a6h61176948132312?where?field066?is?not??null?and??field197?between?convert(datetime,?'2008-08-16')?and?convert(datetime,'2008-09-15'))?)

?? 后來看了一下，這幾表的數(shù)據(jù)

--?表 dbo.uft3a6h61176948132312?：?988行
-- 表：dbo.ufi2j0n11179717502375??：713行
-- 表：?dbo.ufi8s6u81179717475734?：??273行

? ?發(fā)現(xiàn)這三張表都沒有超過1千行數(shù)據(jù)，建立索引意義不大，為何如此慢，看看執(zhí)行計劃：

??

? 分析：發(fā)現(xiàn)是表dbo.uft3a6h61176948132312?訪問開銷最大，但表中數(shù)據(jù)不到一千行。執(zhí)行看看結(jié)果：

(5?行受影響)
表?'uft3a6h61176948132312'。掃描計數(shù)?1，邏輯讀取?27161?次，物理讀取?0?次，預(yù)讀?0?次，lob?邏輯讀取?0?次，lob?物理讀取?0?次，lob?預(yù)讀?0?次。
表?'Worktable'。掃描計數(shù)?0，邏輯讀取?0?次，物理讀取?0?次，預(yù)讀?0?次，lob?邏輯讀取?0?次，lob?物理讀取?0?次，lob?預(yù)讀?0?次。
表?'ufi8s6u81179717475734'。掃描計數(shù)?1，邏輯讀取?37?次，物理讀取?0?次，預(yù)讀?0?次，lob?邏輯讀取?0?次，lob?物理讀取?0?次，lob?預(yù)讀?0?次。
表?'ufi2j0n11179717502375'。掃描計數(shù)?1，邏輯讀取?46?次，物理讀取?0?次，預(yù)讀?0?次，lob?邏輯讀取?0?次，lob?物理讀取?0?次，lob?預(yù)讀?0?次。

??? 這里發(fā)現(xiàn)表uft3a6h61176948132312的訪問有近3萬次IO。?一開始以為是in的緣故，將in換成exists結(jié)果也是一樣,這時考慮用inner join來重新寫sql語句，語句如下：

select?distinct?a.field003?from?ufi2j0n11179717502375??a
inner?join?ufi8s6u81179717475734?b?on?a.requestid=b.requestid
inner?join??uft3a6h61176948132312?c?on?b.field001=c.requestid
where?a.field003?not?in?('','40288135120d660501120de2f8870140','40288135120d660501120de4b9ee014b','40288135120d660501120de9c3ba016c','40288135120d660501120df0460c01b2','40288135120d660501120df1dc2d01d3')?and???c.field066?is?not??null?and??c.field197?between?
convert(datetime,?'2008-08-16')?and?convert(datetime,'2008-09-15')

? 查看執(zhí)行計劃：

? 分析：這時發(fā)現(xiàn)執(zhí)行計劃發(fā)生了變化，最外層的表變成了dbo.ufi2j0n11179717502375，執(zhí)行結(jié)果如下：

(5?行受影響)
表?'Worktable'。掃描計數(shù)?0，邏輯讀取?0?次，物理讀取?0?次，預(yù)讀?0?次，lob?邏輯讀取?0?次，lob?物理讀取?0?次，lob?預(yù)讀?0?次。
表?'ufi2j0n11179717502375'。掃描計數(shù)?1，邏輯讀取?46?次，物理讀取?0?次，預(yù)讀?0?次，lob?邏輯讀取?0?次，lob?物理讀取?0?次，lob?預(yù)讀?0?次。
表?'ufi8s6u81179717475734'。掃描計數(shù)?1，邏輯讀取?37?次，物理讀取?0?次，預(yù)讀?0?次，lob?邏輯讀取?0?次，lob?物理讀取?0?次，lob?預(yù)讀?0?次。
表?'uft3a6h61176948132312'。掃描計數(shù)?1，邏輯讀取?421?次，物理讀取?0?次，預(yù)讀?0?次，lob?邏輯讀取?0?次，lob?物理讀取?0?次，lob?預(yù)讀?0?次。

?? 這時發(fā)現(xiàn)整個IO次數(shù)比先前少了很多。

? 總結(jié)：

????? 根據(jù)這兩個執(zhí)行計劃分析，sql server 2005優(yōu)化器對于in語句沒有正確選擇聯(lián)結(jié)算法，錯誤的采用了采用了”嵌套循環(huán)算法“。

????? 根據(jù)嵌套循環(huán)算法IO次數(shù)：421*（其他兩個表的關(guān)聯(lián)匹配行數(shù)）≈27163次 （訪問表“uft3a6h61176948132312”IO次數(shù)），而這時由于返回的行數(shù)比較多，又沒有建立索引，這時最佳的算法是使用“hash聯(lián)結(jié)算法“

轉(zhuǎn)載于:https://my.oschina.net/ldm95/blog/745893

總結(jié)

以上是生活随笔為你收集整理的SQL 语句优化--IN语句优化案例的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。