本周技術研究部(TRD)的一名DBA 對我們編寫SQL時的一些問題,進行了彙報講演,以下是來自它的腳本,我在它講演的基礎上寫出了自己想表述的,以便於大家相互交流學習。
本周技術研究部(TRD)的一名DBA 對我們編寫SQL時的一些問題,進行了彙報講演,以下是來自它的腳本,我在它講演的基礎上寫出了自己想表述的,以便於大家相互交流學習。
/* --註意:準備數據(可略過,非常耗時) CREATE TABLE CHECK1_T1 ( ID INT, C1 CHAR(8000) ) CREATE TABLE CHECK1_T2 ( ID INT, C1 CHAR(8000) ) DECLARE @I INT SET @I=1 WHILE @I<=10000 BEGIN INSERT INTO CHECK1_T1 SELECT @I,'C1' INSERT INTO CHECK1_T2 SELECT 10000+@I,'C1' SET @I=@I+1 END CREATE TABLE CHECK2_T1 ( ID INT, C1 CHAR(8000) ) DECLARE @I INT SET @I=1 WHILE @I<=10000 BEGIN INSERT INTO CHECK2_T1 SELECT @I,'C1' SET @I=@I+1 END INSERT INTO CHECK2_T1 VALUES(10001,'C2') INSERT INTO CHECK2_T1 VALUES(10002,'C1') CREATE TABLE CHECK3_T1 ( ID INT, C1 CHAR(7000) ) CREATE TABLE CHECK3_T2 ( ID INT, C1 CHAR(7000) ) DECLARE @I INT SET @I=1 WHILE @I<=20000 BEGIN IF @I%2 =0 BEGIN INSERT INTO CHECK3_T1 SELECT @I,'C1' END ELSE BEGIN INSERT INTO CHECK3_T1 SELECT @I,'C2' END IF @I%100=0 BEGIN INSERT INTO CHECK3_T2 SELECT @I,'C1' INSERT INTO CHECK3_T2 SELECT @I+50000,'C2' END SET @I=@I+1 END CREATE TABLE CHECK4_T1 ( ID INT, C1 CHAR(500), ) DECLARE @I INT SET @I=1 WHILE @I<=500000 BEGIN IF @I%100000 =0 BEGIN INSERT INTO CHECK4_T1 SELECT @I,'C2' END ELSE BEGIN INSERT INTO CHECK4_T1 SELECT @I,'C1' END SET @I=@I+1 END CREATE NONCLUSTERED INDEX NCIX_C1 ON CHECK4_T1(C1) CREATE TABLE CHECK5_T1 ( ID INT, C1 CHAR(10), ) DECLARE @I INT SET @I=1 WHILE @I<=10000 BEGIN INSERT INTO CHECK5_T1 SELECT @I,'C1' IF @I%2=0 BEGIN INSERT INTO CHECK5_T1 SELECT @I,'C1' END SET @I=@I+1 END */ --===================================== --1、 Union all 代替 Union DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE --測試一:(26s) 執行計劃:表掃描->排序->合併聯接 SELECT ID,C1 FROM CHECK1_T1 --1W條數據 UNION SELECT ID,C1 FROM CHECK1_T2 --1W條數據 --測試二: (4s) 執行計劃:表掃描->表掃描串聯 SELECT ID,C1 FROM CHECK1_T1 --1W條數據 UNION ALL SELECT ID,C1 FROM CHECK1_T2 --1W條數據 --總結:測試一中的union 排序和去重合併是相當耗時的,如果不要此功能,大數據時最好加上ALL --===================================== --2、 Exists 代替 Count(*) DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE ----測試一: (7s) 執行計劃:表掃描-> 流聚合-> 計算矢量 DECLARE @COUNT INT SELECT @COUNT=COUNT(*) FROM CHECK2_T1 WHERE C1='C1' --1W條數據 IF @COUNT>0 BEGIN PRINT 'S' END ----測試二: (0s) 執行計劃:常量掃描/表掃描-> 嵌套迴圈-> 計算標量 IF EXISTS(SELECT 1 FROM CHECK2_T1 WHERE C1='C1') --1W條數據 BEGIN PRINT 'S' END --總結:判斷是否存在,用Exist即可,沒必要用COUNT(*)將表的所有記錄統計出來,掃描一次 --===================================== --3、 IN(Select COL1 From Table)的代替方式 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE --測試一: (3s)執行計劃:表掃描 -> 哈希匹配 SELECT ID,C1 FROM CHECK3_T2 --400行 WHERE ID IN (SELECT ID FROM CHECK3_T1 WHERE C1='C1') --2W行 --測試二:(1s)執行計劃:表掃描-> 並行度 -> 點陣圖 -> 排序 -> 合併聯接 -> 並行度 SELECT A.ID,A.C1 FROM CHECK3_T2 A INNER JOIN CHECK3_T1 B ON A.ID=B.ID WHERE B.C1='C1' --測試三:(3s)執行計劃:表掃描-> 哈希匹配 SELECT A.ID,A.C1 FROM CHECK3_T2 A WHERE EXISTS (SELECT 1 FROM CHECK3_T1 B WHERE B.ID=A.ID AND B.C1='C1') --總結:能用INNER JOIN 儘量用它,SQL SERVER在查詢時會將關聯表進行優化 --===================================== --4、 Not Exists 代替 Not In --測試一:(8s) 執行計劃:表掃描-> 嵌套迴圈 -> 哈希匹配 SELECT ID,C1 FROM CHECK3_T1 --2W行 WHERE ID NOT IN (SELECT ID FROM CHECK3_T2 WHERE C1='C1') --400行 --測試二:(4s) 執行計劃:表掃描-> 哈希匹配 SELECT A.ID,A.C1 FROM CHECK3_T1 A WHERE NOT EXISTS (SELECT 1 FROM CHECK3_T2 B WHERE B.ID=A.ID AND B.C1='C1') --總結:儘量不使用NOT IN ,因為會調用嵌套迴圈,建議使用NOT EXISTS代替NOT IN --===================================== --5、 避免在條件列上使用任何函數 DROP TABLE CHECK4_T1 CREATE NONCLUSTERED INDEX NCIX_C1 ON CHECK4_T1(C1) --加上非聚集索引 ---測試一:(4s)執行計劃: 索引掃描 SELECT * FROM CHECK4_T1 WHERE RTRIM(C1)='C2' ---測試二:(0s)執行計劃: 索引查找 SELECT * FROM CHECK4_T1 WHERE C1='C2' --總結:where條件里對索引欄位使用了函數,會使索引查找變成索引掃描,從而查詢效率大幅下降 --===================================== --6、 用sp_executesql執行動態sql DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE CREATE PROC UP_CHECK5_T1 ( @ID INT ) AS SET NOCOUNT ON DECLARE @count INT, @sql NVARCHAR(4000) SET @sql = 'SELECT @count=count(*) FROM CHECK5_T1 WHERE ID = @ID' EXEC sp_executesql @sql, N'@count INT OUTPUT, @ID int', @count OUTPUT, @ID PRINT @count CREATE PROC UP_CHECK5_T2 ( @ID INT ) AS SET NOCOUNT ON DECLARE @sql NVARCHAR(4000) SET @sql = 'DECLARE @count INT;SELECT @count=count(*) FROM CHECK5_T1 WHERE ID = ' + CAST(@ID AS VARCHAR(10)) + ';PRINT @count' EXEC(@sql) ---測試一:瞬時 DECLARE @N INT SET @N=1 WHILE @N<=1000 BEGIN EXEC UP_CHECK5_T1 @N SET @N=@N+1 END ---測試二:2s DECLARE @N INT SET @N=1 WHILE @N<=1000 BEGIN EXEC UP_CHECK5_T2 @N SET @N=@N+1 END CREATE CLUSTERED INDEX CIX_ID ON CHECK5_T1(ID) DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE --查看緩存計劃 SELECT a.size_in_bytes '占用位元組數', total_elapsed_time / execution_count '平均時間', total_logical_reads / execution_count '邏輯讀', usecounts '重用次數', SUBSTRING(d.text, (statement_start_offset / 2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(text) ELSE statement_end_offset END - statement_start_offset) / 2) + 1) '語句' FROM sys.dm_exec_cached_plans a CROSS apply sys.dm_exec_query_plan(a.plan_handle) c, sys.dm_exec_query_stats b CROSS apply sys.dm_exec_sql_text(b.sql_handle) d WHERE a.plan_handle = b.plan_handle ORDER BY total_elapsed_time / execution_count DESC; --總結:通過執行下麵緩存計劃可以看出,第一種完全使用了緩存計劃,查詢達到了很好的效果; --而第二種則將緩存計劃浪費了,導致緩存很快被占滿,這種做法是相當不可取的 --===================================== --7、 Left Join 的替代法 --測試一 執行計劃:表掃描 -> 哈希匹配 SELECT A.ID,A.C1 FROM CHECK3_T1 A --2W行 LEFT JOIN CHECK3_T2 B ON A.ID=B.ID WHERE B.C1='C1' --400行 --測試二 執行計劃:表掃描 -> 哈希匹配 SELECT A.ID,A.C1 FROM CHECK3_T1 A RIGHT JOIN CHECK3_T2 B ON A.ID=B.ID WHERE a.C1='C1' --測試三 執行計劃:表掃描 -> 哈希匹配 SELECT A.ID,A.C1 FROM CHECK3_T1 A INNER JOIN CHECK3_T2 B ON A.ID=B.ID WHERE B.C1='C1' --總結:三條語句,在執行計划上完全一樣,都是走的INNER JOIN的計劃, --因為測試一和測試二中,WHERE語句都包含了LEFT 和RIGHT表的欄位,SQLSERVER若發現只要有這個表的欄位,則會自動按照INNER JOIN進行處理 --補充測試:(1s)執行計劃:表掃描-> 並行度 -> 點陣圖 -> 排序 -> 合併聯接 -> 並行度 SELECT A.ID,A.C1 FROM CHECK3_T2 A --400行 INNER JOIN CHECK3_T1 B ON A.ID=B.ID WHERE A.C1='C1' --2W行 --總結:這裡有一個比較有趣的地方,若主表和關聯表數據差別很大時,走的執行計划走的另一條路 --===================================== --8、 ON(a.id=b.id AND a.tag=3) --測試一 SELECT A.ID,A.C1 FROM CHECK3_T1 A INNER JOIN CHECK3_T2 B ON A.ID=B.ID AND A.C1='C1' --測試二 SELECT A.ID,A.C1 FROM CHECK3_T1 A INNER JOIN CHECK3_T2 B ON A.ID=B.ID WHERE A.C1='C1' --總結:內連接:無論是左表和右表的篩選條件都可以放到WHERE子句中 --測試一 SELECT A.ID,A.C1,B.C1 FROM CHECK3_T1 A LEFT JOIN CHECK3_T2 B ON A.ID=B.ID AND B.C1='C1' --測試二 SELECT A.ID,A.C1,B.C1 FROM CHECK3_T1 A LEFT JOIN CHECK3_T2 B ON A.ID=B.ID WHERE B.C1='C1' --總結:左外連接:當右表中的過濾條件放入ON子句後和WHERE子句後的結果不一樣 --===================================== --9、 賦值給變數,加Top 1 --測試一:(3s) 執行計劃:表掃描 DECLARE @ID INT SELECT @ID=ID FROM CHECK1_T1 WHERE C1='C1' SELECT @ID --測試二:(0s)執行計劃:表掃描-> 前幾行 DECLARE @ID INT SELECT TOP 1 @ID=ID FROM CHECK1_T1 WHERE C1='C1' SELECT @ID --總結:給變數賦值最好都加上TOP 1,一從查詢效率上增強,二為了準確性,若表CHECK1_T1有多個值,則會取最後一條記錄賦給@ID --===================================== --10、 考慮是否適合用CASE語句 DECLARE @S INT=1 SELECT * FROM CHECK5_T1 WHERE C1=(CASE @S WHEN 1 THEN C1 ELSE 'C2' END) SELECT * FROM CHECK5_T1 WHERE @S=1 OR C1='C2' /*--===================================== 12、檢查語句是否需要Distinct. 執行計劃:表掃描-> 哈希匹配-> 並行度-> 排序 select distinct c1 from CHECK3_T1 13、禁用Select *,指定具體列名 select c1 from CHECK4_T1 select * from CHECK4_T1 14、Insert into Table(*),指定具體的列名 15、Isnull,沒有必要的時候不要對欄位使用isnull,同樣會產生無法有效利用索引的問題, 和避免在篩選列上使用函數同樣的原理。 16、嵌套子查詢,加上查詢條件,確保子查詢的結果集最小 --=====================================*/