sql數(shù)據(jù)庫培訓(xùn)石油院13優(yōu)化查詢

1、優(yōu)化T-SQL查詢王輝內(nèi)容查詢的邏輯順序和物理順序三種物理連接算法查詢的有效參數(shù)查詢優(yōu)化方法論擴(kuò)展基于集合的操作表變量與臨時(shí)表查詢優(yōu)化最佳實(shí)踐和查詢有關(guān)的DMV查詢的邏輯處理3From&JoinWhereOrder BySelectResultSetHavingGrouping and AggregationRowsResultSetResultSet非聚合查詢聚合查詢Order By查詢邏輯流關(guān)系圖查詢的物理處理4語句:USE Northwind; SELECT C.CustomerID, COUNT(O.OrderID) AS NumOrders FROM dbo.Customers AS

2、 C LEFT OUTER JOIN dbo.Orders AS O ON C.CustomerID = O.CustomerID WHERE C.City = London GROUP BY C.CustomerID HAVING COUNT(O.OrderID) 5 ORDER BY NumOrders;內(nèi)容查詢的邏輯順序和物理順序三種物理連接算法查詢的有效參數(shù)查詢優(yōu)化方法論擴(kuò)展基于集合的操作表變量與臨時(shí)表查詢優(yōu)化最佳實(shí)踐和查詢有關(guān)的DMV嵌套循環(huán)連接算法6嵌套循環(huán)算法掃描執(zhí)行計(jì)劃中上方的表，然后對(duì)于每一行再在下方的表中搜索匹配的行。當(dāng)大表連接的字段上有索引時(shí)，這種算法比較有效。合并連接算

3、法連接字段需要排序，這樣的輸入使用合并連接最有效7哈希連接算法當(dāng)查詢優(yōu)化器找不到合適的、好的索引時(shí)，就會(huì)使用哈希連連接算法，它將在內(nèi)存中創(chuàng)建哈希表來代替索引的二叉平衡樹8三種連接算法比較嵌套循環(huán)連接合并連接哈希連接適用場(chǎng)景外層循環(huán)小，內(nèi)存循環(huán)條件列有序輸入兩端都有序數(shù)據(jù)量大，且沒有索引CPU低低（如果沒有顯式排序）高內(nèi)存低低（如果沒有顯式排序）高IO可能高可能低低可能高可能低內(nèi)容查詢的邏輯順序和物理順序三種物理連接算法查詢的有效參數(shù)查詢優(yōu)化方法論擴(kuò)展基于集合的操作表變量與臨時(shí)表查詢優(yōu)化最佳實(shí)踐和查詢有關(guān)的DMV查詢的有效參數(shù)查詢優(yōu)化程序查看 WHERE 子句中所有的條件，以決定這些條件在限制

4、SQL Server 訪問數(shù)據(jù)時(shí)是否有用 SARG 格式：數(shù)據(jù)字段 部分運(yùn)算符 部分運(yùn)算符 數(shù)據(jù)字段在查詢子句中，SARGs 代表用來搜尋的常數(shù)或變量可以直接與索引鍵值做比較，該字段擁有正確的統(tǒng)計(jì)，不要對(duì)數(shù)據(jù)字段做運(yùn)算大多數(shù) DBA 利用 T-SQL 在撰寫視圖、存儲(chǔ)過程、用戶自定義函數(shù)時(shí)，可能已經(jīng)注意到了 SARGs 的格式需求。但程序設(shè)計(jì)師在利用一般程序語言如 /C# 等撰寫前端程序時(shí)，往往會(huì)組合 SQL 語法，透過 ADO 或 傳遞到后端數(shù)據(jù)庫執(zhí)行，這時(shí)可能就會(huì)對(duì) SARGs 有所疏忽了 11注意事項(xiàng)避免在Where子句中對(duì)字段進(jìn)行NULL值判斷盡量避免在Where子句中使用！=或操作盡

5、量避免在Where子句中使用or來連接條件謹(jǐn)慎使用IN和NOT IN盡量避免使用非打頭字母搜索必要時(shí)強(qiáng)制查詢計(jì)劃使用某個(gè)索引（如：在Where中使用局部變量）避免在Where子句中對(duì)字段進(jìn)行表達(dá)式操作避免在Where子句中對(duì)字段進(jìn)行函數(shù)操作使用Exists替代子查詢避免使用兩個(gè)不同類型的列進(jìn)行表的連接使用 AND 運(yùn)算符的查詢AND 運(yùn)算符的處理程序傳回符合 Where 子句內(nèi)所有條件的記錄每一個(gè)搜尋條件漸進(jìn)地限制傳回的記錄數(shù)使用 Where 子句內(nèi)任何搜尋條件可用的索引索引建立原則與性能考慮在高選擇性的查詢條件上定義索引評(píng)估建立多個(gè)單一字段的索引還是多個(gè)字段的復(fù)合索引13查詢使用 OR 運(yùn)算

6、符傳回在 Where 子句中符合任何一個(gè)條件的記錄每一個(gè)搜尋條件都會(huì)增加可傳回的記錄筆數(shù)可以針對(duì) OR 運(yùn)算符所串起的每一部分使用不同的索引如果在 OR 運(yùn)算符連接的部分中，某個(gè)字段沒有適用的索引，都必須采用表掃描或簇索引掃描可以使用多個(gè)索引當(dāng)用 OR 做參數(shù)是否有輸入的判斷時(shí)，將一并使索引失效Where (key1 =0 OR Key1 =Key1) AND (key2 =0 OR Key2 =Key2) 利用存儲(chǔ)過程內(nèi)含 IF 寫法，提供較佳的重建執(zhí)行計(jì)劃在OR運(yùn)算符兩端的條件涉及了不同的表會(huì)導(dǎo)致索引掃描14內(nèi)容查詢的邏輯順序和物理順序三種物理連接算法查詢的有效參數(shù)查詢優(yōu)化方法論擴(kuò)展基于集

7、合的操作表變量與臨時(shí)表查詢優(yōu)化最佳實(shí)踐和查詢有關(guān)的DMV查詢調(diào)優(yōu)方法論16在實(shí)例級(jí)別分析等待-定位區(qū)域關(guān)聯(lián)等待和查詢-定位資源確定行動(dòng)方針下鉆到數(shù)據(jù)庫/文件級(jí)別下鉆到對(duì)象/組件級(jí)別調(diào)優(yōu)索引/查詢分析等待（Waits）17SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms FROM sys.dm_os_wait_stats ORDER BY wait_type;分析等待（Top Waits）WITH Waits AS ( SELECT wait_type, wait_

8、time_ms / 1000. AS wait_time_s, 100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn FROM sys.dm_os_wait_stats WHERE wait_type NOT LIKE %SLEEP% - filter out additional irrelevant waits ) SELECT W1.wait_type, CAST(W1.wait_time_s AS DECIMAL(12, 2)

9、AS wait_time_s, CAST(W1.pct AS DECIMAL(12, 2) AS pct, CAST(SUM(W2.pct) AS DECIMAL(12, 2) AS running_pct FROM Waits AS W1 JOIN Waits AS W2 ON W2.rn = W1.rn GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct HAVING SUM(W2.pct) - W1.pct =代替對(duì)于小數(shù)據(jù)集使用表變量而不是臨時(shí)表對(duì)于需要反復(fù)使用的大數(shù)據(jù)集使用臨時(shí)表而不是表變量盡量使用WHERE替代HAVING多表連

10、接中建議給表添加別名連接和子查詢的比較優(yōu)先使用存儲(chǔ)過程最佳實(shí)踐：T-SQL編程和優(yōu)化(語法)聚合查詢的最佳實(shí)踐在一個(gè)查詢中不要 Group By 很多字段針對(duì)大的查詢，可以考慮把聚合放到臨時(shí)表中，然后再和外部表連接如何在分組時(shí)用到許多列，嘗試把聚合移動(dòng)到子查詢中在聚合中使用CASE語句來實(shí)現(xiàn)逐行的邏輯更新差異數(shù)據(jù)UPDATE SET = FROM 子句INSERT SELECT FROM 子句DELETE FROM 子句FROM INNER | LEFT | RIGHT | FULL OUTER JOIN ON = WHERE 若是通過鏈接服務(wù)器訪問其他產(chǎn)品的數(shù)據(jù)，如 Oracle，可考慮透過

11、 OpenQuery 先行在來源端過濾數(shù)據(jù)33使用 T-SQL 應(yīng)注意的事項(xiàng)SELECT盡量不要傳回所有的數(shù)據(jù)表內(nèi)字段且不設(shè)定過濾條件索引順序上的第一個(gè)字段才適合當(dāng)作過濾條件Distinct、Order By 等語法盡量是查詢結(jié)果需要才使用，則 UNION ALL 也較僅僅寫 UNION 為佳Union 的字段越多，差異越大若未將事務(wù)隔離級(jí)別降低到 Read mitted，或是通過鎖定提示 NOLOCK 來減低被鎖定的機(jī)會(huì)，最好設(shè)定 SET LOCK_TIMEOUT 選項(xiàng)，避免用戶無盡地等待34INSERT、DELETE 和 UPDATESELECTINTO 一定比逐筆做 INSERT 快，清空數(shù)據(jù)表采用 TRUNCATE TABLE 一定比 DELETE TABLE 不加 WHERE 來得快UPDATE 和 DELETE 采用 WHERE 子句時(shí)，記得條件也要符合 SARGs 格式 INSERT 大量數(shù)據(jù)時(shí)，盡量不要使用循環(huán)，可以使用CTE，如果要使用循環(huán)，也放到一個(gè)事務(wù)中35大量數(shù)據(jù)加載 可以考慮先刪掉索引，停止檢查約束，尤其是利用多個(gè)用戶端同時(shí)做大量數(shù)據(jù)加載的動(dòng)作同樣是大量加載，BULK INSERT 語法通?？爝^ BCP 工具輸入時(shí)直接采用表鎖考慮通過多臺(tái)前端計(jì)算機(jī)同時(shí)執(zhí)行 BCP 工具程序或 BULK IN