SQL Server 資料庫效能最佳化

標籤：cat   rds   put   商務邏輯   sys   包括   調用   create   into   

分析比較執行時間計劃讀取情況

1. 查看執行時間和cpu

set statistics time onselect * from Bus_DevHistoryDataset statistics time off

執行後在訊息裡可以看到

2. 查看查詢對I/O的操作情況

set statistics io onselect * from Bus_DevHistoryDataset statistics io off

執行之後的結果：

掃描計數：索引和表執行次數

邏輯讀取：資料緩衝中讀取的頁數

物理讀取：從磁碟中讀取的頁數

預讀：查詢過程中，從磁碟放入緩衝的頁數

lob邏輯讀取：從資料緩衝中讀取image、text、ntext或大型資料的頁數

lob物理讀取：從磁碟中讀取image、text、ntext或大型資料的頁數

lob預讀：查詢過程中，從磁碟放入緩衝的image、text、ntext或大型資料的頁數

如果物理讀取次數和預計次數比較多，可以使用索引進行最佳化。

上述兩種資訊的查看如果不想寫sql，可以通過設定完成：

工具->選項

3. 查看執行計畫

選中查詢語句，點擊

 

 

 

一、資料庫設計最佳化

1、不要使用遊標。 

使用遊標不僅佔用記憶體，而且還用不可思議的方式鎖定表，它們可以使DBA所能做的一切效能最佳化等於沒做。遊標裡每執行一次fetch就等於執行一次select。

2、建立適當的索引

每當為一個表添加一個索引，select會更快，可insert和delete卻大大變慢，因為建立了維護索引需要許多額外的工作。

（1）採用函數處理的欄位不能利用索引 

（2）條件內包括了多個本表的欄位運算時不能進行索引 

3、使用事務 

對於一些耗時的操作，使用事務可以達到很好的最佳化效果。 

4、小心死結 

按照一定的次序來訪問你的表。如果你先鎖住表A，再鎖住表B，那麼在所有的預存程序中都要按照這個順序來鎖定它們。 如果某個預存程序先鎖定表B，再鎖定表A，這可能會導致一個死結。

5、不要開啟大的資料集 

6、不要使用伺服器端遊標 

與伺服器端遊標比起來，用戶端資料指標可以減少伺服器和網路的系統開銷，並且還減少鎖定時間。 

7、不要忽略同時修改同一記錄的問題

有時候，兩個使用者會同時修改同一記錄，這樣，後一個修改者修改了前一個修改者的操作，某些更新就會丟失。處理這種情況，建立一個timestamp欄位，在寫入前檢查它，如果允許，就合并修改，如果存在衝突，提示使用者。

8、盡量不要使用text資料類型 

除非使用text處理一個很大的資料，否則不要使用它。因為它不易於查詢，速度慢，用的不好還會浪費大量的空間。一般varchar可以更好的處理資料。 

9、避免在索引列上使用計算  

where子句中，如果索引列是函數的一部分，最佳化器將不使用索引而使用全表掃描。例如： 

（低效）select ... from [dept] where [sal]*12>25000; 

（高效）select ... from [dept] where [sal]>25000/12;

10、不同類型的索引效能是不一樣的，應儘可能先使用效能高的

數字類型的索引尋找效率高於字串類型，定長字串char、nchar的索引效率高於變長字串varchar、nvarchar的索引。

（低效）select ... from tableName where username=‘張三‘ and age>=21

（高效）select ... from tableName where age>=21 and username=‘張三‘

二、SQL語句最佳化 

1、不要使用select * 

在select中指定所需要的列，將帶來的好處： 

（1）減少記憶體耗費和網路的頻寬 

（2）更安全

（3）給查詢最佳化工具機會從索引讀取所有需要的列

2、使用參數查詢 

主要是防止SQL注入，提高安全性。 

3、使用exists或not exists代替in或not in 

（高效）select * from [emp] where [empno]>0 and exists (select ‘X‘ from [dept] where [dept].[deptno]=[emp].[deptno] and [loc]=‘MELB‘);

（低效）select * from [emp] where [empno]>0 and [deptno] in (select [deptno] from [dept] where [loc]=‘MELB‘);

4、is null或is not null操作

判斷欄位是否為空白一般是不會應用索引的，因為索引不索引空值。不能用null作索引，任何包含null值的列都將不會被包含在索引中。也就是說如果某列存在空值，即使對該列建索引也不會提高效能。任何在where子句中使用is null或is not null的語句最佳化器都不允許使用索引。 

推薦方案：用其他相同功能的操作運算代替，如：a is not null改為a>0或a>‘‘等。 

5、<及>操作 

大於或小於一般情況不用調整，因為它有索引就會採用索引尋找，但有的情況下可以對它進行最佳化。如一個表有100萬記錄，那麼執行>2與>=3的效果就有很大區別了。

（低效）select * from [emp] where [deptno]>2;

（高效）select * from [emp] where [deptno]>=3;

6、like操作 

like操作可以應用萬用字元查詢，裡面的萬用字元組合可能達到幾乎是任意的查詢，但是如果用不好則會產生效能上的問題，如lide ‘%5400%‘ 這種查詢不會引用索引，而like ‘X5400%‘ 則會引用範圍索引。

7、where後面的條件順序影響 

where子句後面的條件順序對大資料量表的查詢會產生直接的影響。如：

select * from zl_yhjbqk where dy_dj=‘1KV以下‘ and xh_bz=1; 

select * from zl_yhjbqk where dy_dj=1 and dy_dj=‘1KV以下‘; 

以上兩個查詢，兩個欄位都沒進行索引，所以執行的時候都是全表掃描，第一條SQL的dy_dj=‘1KV以下‘條件在記錄集內比率為99%，而xh_bz=1的比率只為0.5%，在進行第一條SQL的時候99%條記錄都進行dy_dj及xh_bz的比較。而在進行第二條SQL的時候0.5%條記錄都進行dy_dj及xh_bz的比較，以此可以得出第二條SQL的CPU佔用率明顯比第一條低。 

8、用union替換or（適用於索引列） 

通常情況下，用union替換where子句中的or將會起到較好的效果。對索引列使用or將造成全表掃描。注意：這個規則只針對多個索引列有效。如果有column沒有被索引，查詢效率可能會因為你沒有選擇or而降低。下面的例子中loc_id和region上都有建索引。

（低效）select loc_id,loc_desc,begion from location where loc_id=10 or begion=‘MELBOURNE‘; 

（高效）select loc_id,loc_desc,begion from location where loc_id=10

            union

           select loc_id,loc_desc_begion from location where begion=‘MELBOURNE‘; 

9、最佳化group by 

提高group by語句的效率，可以通過將不需要的記錄在group by之前過濾掉。

（低效）select [job],avg([sal]) from [emp] group by [job] having job=‘PRESIDENT‘ or job=‘MANAGER‘; 

（高效）select [job],avg([sal]) from [emp] where [job]=‘PRESIDENT‘ or job=‘MANAGER‘ group by [job];

10、使用預存程序 

可以考慮使用預存程序封裝那些複雜的SQL語句或商務邏輯，這樣有幾個好處：

（1）預存程序的執行計畫可以被緩衝在記憶體中較長的時間，減少了重新編譯的時間。

（2）預存程序減少了用戶端和伺服器的繁複互動。 

（3）如果程式發布後需要做某些改變你可以直接修改預存程序而不用修改程式，避免需要重新安裝部署程式。  

11、用sp_configure ‘query governor cost limit‘或者SET QUERY_GOVERNOR_COST_LIMIT來限制查詢消耗的資源。當評估查詢消耗的資源超出限制時，伺服器自動取消查詢,在查詢之前就扼殺掉。SET LOCKTIME設定鎖的時間。

12、使用select top或set rowcount來限制操作的行。

13、如果使用了in或or等時發現查詢沒有走索引，使用顯式申明指定索引： SELECT * FROM PersonMember (INDEX = IX_Title) WHERE processid IN (‘男‘，‘女‘)。

14、如果要插入大的二進位值到Image列，使用預存程序，千萬不要用內嵌insert來插入(不知JAVA是否)。因為這樣應用程式首先將二進位值轉換成字串(尺寸是它的兩倍)，伺服器受到字元後又將他轉換成二進位值。預存程序就沒有這些動作: 方法：Create procedure p_insert as insert into table(Fimage) values (@image), 在前台調用這個預存程序傳入二進位參數，這樣處理速度明顯改善。

15、分析select emp_name form employee where salary>3000 在此語句中若salary是Float類型的，則最佳化器對其進行最佳化為Convert(float,3000)，因為3000是個整數，我們應在編程時使用3000.0而不要等運行時讓DBMS進行轉化。同樣字元和整型資料的轉換。

三、處理百萬級以上資料提高查詢速度的方法

1、盡量避免在where子句中使用!=或<>操作符，否則將使引擎放棄使用索引而進行全表掃描。

2、應考慮在where及order by涉及的列上建立索引。 

3、盡量避免在where子句中對欄位進行null值判斷，否則將導致全表掃描。 

4、就是避免在where子句中使用or來串連條件，否則將導致全表掃描。

select id from t where num=10 or num=20  改寫為

select id from t  where num=10

union all 

select id from t where num=20 

5、盡量避免使用前置百分比符號。

select id from t where name like ‘%abc%‘ 

6、in 和not in也要慎用，很多時候可以用exists和not exists，否則會導致全表掃描。

7、如果在where子句中使用參數，也會導致全表掃描。 

select id from t where [email protected]  可以改為強制查詢使用索引 

select id from t with(index(索引名)) where [email protected] 

8、盡量避免在where子句中對欄位進行運算式操作，否則將導致全表掃描。 

select id from t where num/2=100  

應改為： 

select id from t where num=100*2 

9、盡量避免在where子句中對欄位進行函數操作，否則將導致全表掃描。

select id from t where substring(name,1,3)=‘abc‘ 

應改為： 

select id from t where name like ‘abc%‘ 

10、並不是所有索引對查詢都有效，SQL根據表中資料來進行查詢最佳化，當索引列有大量資料重複時，SQL查詢可能不會去利用索引。

11、索引並不是越多越好，索引提交了select效率，但是降低了insert和update的效率。一個表的索引數最好不要超過6個。 

12、盡量使用數字型欄位，若只含數值資訊的欄位盡量不要設計為字元型，這會降低查詢和串連的效能，並會增加儲存開銷。因為引擎在處理查詢和串連時會逐個比較字串中每個字元，而對於數字型而言只需要比較一次就夠了。 

13、儘可能使用varchar/nvarchar代替char/nchar，因為首先變長欄位儲存空間小，可以節省儲存空間；其次對於查詢來說，在一個相對較小的欄位內搜尋效率顯然要高些。 

14、任何地方都不要使用select *，用具體的欄位列表代替*，不要返回用不到的欄位。 

15、盡量避免使用遊標，因為遊標的效率較差，如果遊標操作的資料超過1萬行，那麼就考慮改寫。 

16、盡量避免大事務操作，提高系統並發能力。 

17、利用set rowcount實現高效能的分頁。

 

Declare @ID int Declare @MoveRecords int [email protected]和@PageSize是傳入參數 Set @[email protected] * @PageSize+1  --下面兩行實現快速滾動到我們要取的資料的行，並把ID記錄下來 Set Rowcount @MoveRecords Select @ID=ID from Table1 Order by ID  Set Rowcount @PageSize Select * From Table1 Where ID>[email protected] Order By ID Set Rowcount 0  

四、資料庫主鍵選取
常見的資料庫主鍵選取方式有：
         ●自動成長欄位
         ●Uniqueidentifier
         ●“COMB（Combine）”類型
1、自動成長欄位
優點：
      （1）簡單、效率高。
缺點：
      （1）自增一般使用int型，有資料條數的限制。
      （2）在資料庫進行資料合併時會比較麻煩。
2、GUID
優點：
      （1）安全，保證唯一性。
      （2）不會產生自增欄位那樣資料合併時的問題。
缺點：
      （1）它的長度是16位元組，佔用大量儲存空間。
      （2）該資料類型毫無規律，要在上面建立索引很耗時，所以效率要比使用自增欄位低。
3、COMB
      考慮到上面兩種主鍵類型的優缺點，這裡使用COMB類型可以為兩者找到了一個平衡點。它的設計思路是這樣的：既然GUID類型無規律可言造成索引效率低下，影響系統的效能，那麼能不能通過組合的方式，保留GUID前10個位元組，用後6個位元組表示GUID產生的時間，這樣即保證了唯一性同時增加了有序性，以此來提高索引效率。後6位元組的時間精度可以達到1/300秒，兩個COMB類型資料完全相同的可能性是在這1/300秒內產生的兩個GUID前10個位元組完全相同，這幾乎是不可能的。
（1）SQL Server中SQL命令實現這一思路的方式：

DECLARE @aGuid UNIQUEIDENTIFIER 
SET @aGuid = CAST(CAST(NEWID() AS BINARY(10))+ CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER)
（2）實現COMB資料的C#方式：
///<summary>
/// 返回 GUID 用於資料庫操作，特定的時間代碼可以提高檢索效率
/// </summary>
/// <returns>COMB (GUID 與時間混合型) 類型 GUID 資料</returns>
public static Guid NewComb() 
{ 
     byte[] guidArray = System.Guid.NewGuid().ToByteArray(); 
     DateTime baseDate = new DateTime(1900,1,1); 
     DateTime now = DateTime.Now; 
     // Get the days and milliseconds which will be used to build the byte string 
     TimeSpan days = new TimeSpan(now.Ticks - baseDate.Ticks); 
     TimeSpan msecs = new TimeSpan(now.Ticks - (new DateTime(now.Year, now.Month, now.Day).Ticks)); 
     // Convert to a byte array 
     // Note that SQL Server is accurate to 1/300th of a millisecond so we divide by 3.333333 
     byte[] daysArray = BitConverter.GetBytes(days.Days); 
     byte[] msecsArray = BitConverter.GetBytes((long)(msecs.TotalMilliseconds/3.333333)); 
     // Reverse the bytes to match SQL Servers ordering 
     Array.Reverse(daysArray); 
     Array.Reverse(msecsArray); 
     // Copy the bytes into the guid 
     Array.Copy(daysArray, daysArray.Length - 2, guidArray, guidArray.Length - 6, 2); 
     Array.Copy(msecsArray, msecsArray.Length - 4, guidArray, guidArray.Length - 4, 4); 
     return new System.Guid(guidArray); 
} 
/// <summary>
/// 從 SQL SERVER 返回的 GUID 中產生時間資訊
/// </summary>
/// <param name="guid">包含時間資訊的 COMB </param>
/// <returns>時間</returns>
public static DateTime GetDateFromComb(System.Guid guid) 
{ 
     DateTime baseDate = new DateTime(1900,1,1); 
     byte[] daysArray = new byte[4]; 
     byte[] msecsArray = new byte[4]; 
     byte[] guidArray = guid.ToByteArray(); 
     // Copy the date parts of the guid to the respective byte arrays. 
     Array.Copy(guidArray, guidArray.Length - 6, daysArray, 2, 2); 
     Array.Copy(guidArray, guidArray.Length - 4, msecsArray, 0, 4); 
     // Reverse the arrays to put them into the appropriate order 
     Array.Reverse(daysArray); 
     Array.Reverse(msecsArray); 
     // Convert the bytes to ints 
     int days = BitConverter.ToInt32(daysArray, 0); 
     int msecs = BitConverter.ToInt32(msecsArray, 0); 
     DateTime date = baseDate.AddDays(days); 
     date = date.AddMilliseconds(msecs * 3.333333); 
     return date; 
}

SQL Server 資料庫效能最佳化