SQL Server 效能最佳化之——系統化方法提高效能

原文 http://www.cnblogs.com/BoyceYang/archive/2013/06/15/3138142.html

閱讀導航

1. 概述

2. 規範邏輯資料庫設計

3. 使用高效索引設計

4. 使用高效的查詢設計

5. 使用技術分析低效能

6. 總結

 

1. 概述

在比較大的範圍內找出能夠大幅提高效能的地區，並且專註於分析這個地區，這是最有效最佳化SQL Server效能的方式。否則，大量的時間和精力可能被浪費在不能提高很大效能的地區。在這裡並沒有討論關於多使用者並發所帶來的效能問題。

能獲得最大效能提高的地區一般是：邏輯資料庫設計，索引設計，查詢設計。然而，最大的效能問題經常由於缺乏這些方面研究的原因造成。如果效能是被列為一個需要關注的問題，聰明的做法是首先專註於這些方面， 因為效能的大幅提高經常是用相對較小的時間精力完成。

下面開始進入正題。

2. 規範邏輯資料庫設計

合理規範性的邏輯資料庫設計可以產生最佳效能。大量的窄表是標準資料庫的特性。少量的寬表是非標準資料的特性。高度標準資料庫通常關聯著複雜的表的 聯集查詢，這個可能損害資料庫的效能。不管怎麼樣，SQL Server最佳化在快速查詢、高效聯結、可用有效索引方面是非常有效，下面是正常化的好處：

• 如果是窄表，應該加快排序和建立索引
• 如果是寬表，最好使用叢集索引
• 索引往往是越窄的表，越應該精確
• 更好的利用段去控製表的物理空間
• 每個表的索引越少，對提高UPDATE操作的效能越有協助
• 越少的NULLs列，越少的冗餘資料，越能增加資料庫的緊湊性

對於SQL Server，標準化將有助於提升而不是損害效能。隨著標準化的提高，因此需要一定數量並且複雜的表串連來檢索資料。只要標準化不會導致很多查詢出現超過四個表的串連，就應進行標準化進程。

如果邏輯資料庫設計已經固定，並且不可能進行整體重新設計，而且通過研究表明一個大表存在效能瓶頸，在這樣的情況下，可以有選擇性的對這個大表進行 標準化。如果過預存程序進行訪問資料，那麼架構的改變不會影響應用程式。如果不是這樣，可以通過建立視圖來隱藏這種改變，因為視圖可以產生單個表的錯覺。

3. 使用高效索引設計

不像很多非關係系統，不把關係索引考慮作為邏輯資料庫設計的一部分。索引能被刪除、添加和更新，除了影響效能以外，不會影響資料庫結構描述或者應用程式 設計。實現良好的SQL Server效能，高效索引設計是非常重要的。由於這些原因，不要猶豫展示不同索引帶來的效能改變吧。

大多數情況下，最佳化器將可靠地選擇最高效的索引。所有的策略應該提供良好的索引最佳化的選擇，相信這是正確的決定。這可以在多種情況下，減少分析時間並且能提供良好的效能。

接下來介紹索引。檢查SQL查詢的WHERE子句，因為這個是最佳化的主要焦點。在WHERE子句中列出的列都有可能成為索引的備選。假如有太多的語句需要檢查，挑選有代表性的一組，或者僅僅是速度緩慢的那組。

最好使用窄索引。窄索引比混合索引和複合索引更加高效。窄索引每頁行越多，索引層級應該越低，這樣才能提高效能。SQL Server最佳化只是維護統計資料在複合索引最重要的列上。因此，如果複合索引的第一列可選擇性很差，那麼就不最佳化這個索引。

最佳化器可以快速、高效的分析成百上千的索引和表串連的可能性。有更多的窄索引提供給最佳化器，最佳化器就會有更多可能的選擇，這對效能很有協助。有較少的寬索引、複合索引提供給最佳化程器，最佳化器只有很少選擇的可能性，這對效能會有影響。

索引數目太多效能可能會降低，因為涉及到更新這些索引的開銷。然而，大量的面向更新操作需要更多的讀操作，而不是寫操作。假如，嘗試新索引時提高了效能，那就不要猶豫，使用這個所以吧。

使用叢集索引。適當的使用叢集索引可以極大的提升效能。甚至叢集索引可以使UPDATE和DELETE操作提速，因為這些操作需要很多讀操作。可能 每個表只有單一的叢集索引，因此，要靈活地利用這個索引。返回行數的查詢或者涉及一個範圍值的查詢都是一個可能被叢集索引提高效能的候選。

例子：

   1:  SELECT * FROM PHONEBOOK

   2:   

   3:  WHERE NAME = ‘李雷’

   4:   

   5:  SELECT * FROM MEMERTABLE

   6:   

   7:  WHERE MEMBER_NO > 5000 AND MEMBER_NO < 6000

通過約束，上面提到的NAME和MEMBER_NO列，對於非叢集索引可能不是一個適合的候選。盡量在返回很少行資料的列上使用非叢集索引。

檢查列資料的唯一性。這樣將協助決定，什麼樣的列作為叢集索引、非叢集索引、無需索引的備選。

查詢語句檢查資料的唯一性，例子：

   1:  SELECT COUNT (DISTINCT COLNAME) FROM TABLENAME

這個語句將返回一個列中不重複值的數量。在表中比較這個數量和總的行數。在一個一萬行的表中，5000個不重複值的列對於非叢集索引可能是一個很好 的備選，20個不重複值的列可能最適合叢集索引，3個不重複值的列根本就不需要使用索引。這些僅僅是個例子，不是一成不變的規則。記住把索引建立在WHERE查詢子句列出的每一個列上。

在索引選擇時，查詢語句返回行數也是一個重要的因素。最佳化器會考慮非叢集索引花費在每個返回行至少一頁I/O的成本。以這樣的速度，並不需要很長的時間就可以變得更高效的掃描整個表。理性對待結果集，要麼限制結果集的大小，要麼使用叢集索引定位巨大結果集。

4. 使用高效的查詢設計

某些查詢語句本身是資源密集型。這關係到基本資料和索引在大多數RDBMSs（關係型資料庫管理系統）的常見問題，而不是在特定SQL Server中。它 們並不低效，最佳化器將會儘可能實現高效的查詢語句。然而，它們是資源密集型，SQL面向結果集的本性可能使它們出現低效。最佳化器的智能程度不可能消除這些 結構的固有資源成本。和更加簡單的語句相比，他們內在的消耗更大。儘管SQL Server使用最優的訪問計劃，但還是會有限制的。

例如：

• 大型結果集
• IN和OR語句
• 高度非唯一WHERE子句
• !=（不等於）
• 某些列函數，比如SUM
• WHERE子句中的運算式或資料轉換
• WHERE子句的局部變數

有些因素可能需要使用這些查詢語句結構。如果最佳化器可以限制結果集，然後再應用資源密集型的查詢，那麼他們的影響將會減少。

例如:

   1:  低效: SELECT SUM(SALARY) FROM TABLE

   2:   

   3:  高效: SELECT SUM(SALARY) FROM TABLE WHERE ZIP='98052'

   4:   

   5:  低效: SELECT * FROM TABLE WHERE LNAME=@VAR

   6:   

   7:  高效: SELECT * FROM TABLE WHERE LNAME=@VAR AND ZIP='98052'

在第一個例子中，SUM操作使用索引並不能使其加速。每行都需要被讀和求和。設想在ZIP列有一個索引，最佳化器將可能使用這個來初始限制結果集，然後再應用SUM函數。這可能會更快。

在第二個例子中，局部變數直到運行時才被賦值。然而最佳化器無法拖延到運行時才選擇訪問計劃，必須在編譯時間進行選擇。然而，在編譯期間，當產生訪問計 劃時，@VAR的值還不能確定，因此不能使用輸入的@VAR作為索引選擇。可以使用AND子句對結果集進行限制。使用預存程序是一個可選技術，這樣可以傳 遞參數，將參數賦值給預存程序中@VAR值。

大多數RDBMSs的大型結果集是很耗費效能。可以嘗試不返回大型結果集到用戶端作為最終資料選擇。允許資料庫後台執行預定函數，並限定結果集的大小，這種做法效率很高。

5. 使用技術分析低效能

首先分離查詢，或者分離比較慢的查詢。當有少數SQL查詢速度慢，經常表現為整個應用程式速度慢。對能夠顯示產生SQL的工具，使用這個工具的診斷或偵錯模式記錄產生的SQL。使用嵌入式SQL工具會更加簡單。分離速度慢的查詢之前，先做一下下面的步驟：

• 單獨運行疑似速度慢的語句，使用工具（例如ISQL、SAF）驗證實際上是不是很慢。
• 使用SET STATISTICS IO ON，檢查語句的I/O消耗和已選擇的訪問計劃。最佳化器的目的是最小的I/O。記錄邏輯I/O。以這個為基準測量改進成果
• 如果查詢涉及視圖或者預存程序，從中提取這些語句並單獨運行。當嘗試使用不同索引時，訪問計劃是可以改變。
• 有些表可以產生I/O作為觸發器運行，這時要注意可能和這些表有關係的觸發器和視圖。
• 檢查速度慢的語句表的索引。利用之前列出的技術檢查是否有更好的索引，如果有必要就修改。
• 改變索引後重新執行查詢，並觀察I/O和訪問計劃的改變。
• 改進工作完成，運行主程式看看所有的效能是不是有所提升。

檢查程式的I/O或CPU限制的行為。通常這個對確定查詢語句是否在I/O或CPU臨界狀態很有用。我們要花費精力在提高真正的效能瓶頸上，例如， 如果一個查詢是CPU臨界狀態，就算增加更多的記憶體給SQL Server也太可能有效能的提高，當然更多的記憶體還是能提高快取命中率。下面的步驟是檢查SQL Server的I/O和CPU臨界狀態：

• 使用OS/2 CPU監控程式。
• 當執行查詢時，如果CPU使用率保持很高（>70%），這表明是CPU臨界狀態。
• 當執行查詢時，如果CPU使用率保持很低（<50%），這表明也是CPU臨界狀態。
• 使用STATISTICS IO比較CPU利用率資訊

6. 總結

SQL Server能夠提高大型資料庫的效能。要挖掘這個效能的潛力，需要有高效的資料庫設計、索引和查詢語句。這些地區是最可能成為捕獲到重大效能提升的備選地區。嘗試使用索引是一個很特別建議。通常，系統化的方法在分析效能問題上，不僅投入時間少，而且能產生巨大效能提升。

 

在此特別感謝@守望dreamstar對本篇文章的支援。

閱讀導航

1. 概述

2. 規範邏輯資料庫設計

3. 使用高效索引設計

4. 使用高效的查詢設計

5. 使用技術分析低效能

6. 總結

 

1. 概述

在比較大的範圍內找出能夠大幅提高效能的地區，並且專註於分析這個地區，這是最有效最佳化SQL Server效能的方式。否則，大量的時間和精力可能被浪費在不能提高很大效能的地區。在這裡並沒有討論關於多使用者並發所帶來的效能問題。

能獲得最大效能提高的地區一般是：邏輯資料庫設計，索引設計，查詢設計。然而，最大的效能問題經常由於缺乏這些方面研究的原因造成。如果效能是被列為一個需要關注的問題，聰明的做法是首先專註於這些方面， 因為效能的大幅提高經常是用相對較小的時間精力完成。

下面開始進入正題。

2. 規範邏輯資料庫設計

合理規範性的邏輯資料庫設計可以產生最佳效能。大量的窄表是標準資料庫的特性。少量的寬表是非標準資料的特性。高度標準資料庫通常關聯著複雜的表的 聯集查詢，這個可能損害資料庫的效能。不管怎麼樣，SQL Server最佳化在快速查詢、高效聯結、可用有效索引方面是非常有效，下面是正常化的好處：

• 如果是窄表，應該加快排序和建立索引
• 如果是寬表，最好使用叢集索引
• 索引往往是越窄的表，越應該精確
• 更好的利用段去控製表的物理空間
• 每個表的索引越少，對提高UPDATE操作的效能越有協助
• 越少的NULLs列，越少的冗餘資料，越能增加資料庫的緊湊性

對於SQL Server，標準化將有助於提升而不是損害效能。隨著標準化的提高，因此需要一定數量並且複雜的表串連來檢索資料。只要標準化不會導致很多查詢出現超過四個表的串連，就應進行標準化進程。

如果邏輯資料庫設計已經固定，並且不可能進行整體重新設計，而且通過研究表明一個大表存在效能瓶頸，在這樣的情況下，可以有選擇性的對這個大表進行 標準化。如果過預存程序進行訪問資料，那麼架構的改變不會影響應用程式。如果不是這樣，可以通過建立視圖來隱藏這種改變，因為視圖可以產生單個表的錯覺。

3. 使用高效索引設計

不像很多非關係系統，不把關係索引考慮作為邏輯資料庫設計的一部分。索引能被刪除、添加和更新，除了影響效能以外，不會影響資料庫結構描述或者應用程式 設計。實現良好的SQL Server效能，高效索引設計是非常重要的。由於這些原因，不要猶豫展示不同索引帶來的效能改變吧。

大多數情況下，最佳化器將可靠地選擇最高效的索引。所有的策略應該提供良好的索引最佳化的選擇，相信這是正確的決定。這可以在多種情況下，減少分析時間並且能提供良好的效能。

接下來介紹索引。檢查SQL查詢的WHERE子句，因為這個是最佳化的主要焦點。在WHERE子句中列出的列都有可能成為索引的備選。假如有太多的語句需要檢查，挑選有代表性的一組，或者僅僅是速度緩慢的那組。

最好使用窄索引。窄索引比混合索引和複合索引更加高效。窄索引每頁行越多，索引層級應該越低，這樣才能提高效能。SQL Server最佳化只是維護統計資料在複合索引最重要的列上。因此，如果複合索引的第一列可選擇性很差，那麼就不最佳化這個索引。

最佳化器可以快速、高效的分析成百上千的索引和表串連的可能性。有更多的窄索引提供給最佳化器，最佳化器就會有更多可能的選擇，這對效能很有協助。有較少的寬索引、複合索引提供給最佳化程器，最佳化器只有很少選擇的可能性，這對效能會有影響。

索引數目太多效能可能會降低，因為涉及到更新這些索引的開銷。然而，大量的面向更新操作需要更多的讀操作，而不是寫操作。假如，嘗試新索引時提高了效能，那就不要猶豫，使用這個所以吧。

使用叢集索引。適當的使用叢集索引可以極大的提升效能。甚至叢集索引可以使UPDATE和DELETE操作提速，因為這些操作需要很多讀操作。可能 每個表只有單一的叢集索引，因此，要靈活地利用這個索引。返回行數的查詢或者涉及一個範圍值的查詢都是一個可能被叢集索引提高效能的候選。

例子：

   1:  SELECT * FROM PHONEBOOK

   2:   

   3:  WHERE NAME = ‘李雷’

   4:   

   5:  SELECT * FROM MEMERTABLE

   6:   

   7:  WHERE MEMBER_NO > 5000 AND MEMBER_NO < 6000

通過約束，上面提到的NAME和MEMBER_NO列，對於非叢集索引可能不是一個適合的候選。盡量在返回很少行資料的列上使用非叢集索引。

檢查列資料的唯一性。這樣將協助決定，什麼樣的列作為叢集索引、非叢集索引、無需索引的備選。

查詢語句檢查資料的唯一性，例子：

   1:  SELECT COUNT (DISTINCT COLNAME) FROM TABLENAME

這個語句將返回一個列中不重複值的數量。在表中比較這個數量和總的行數。在一個一萬行的表中，5000個不重複值的列對於非叢集索引可能是一個很好 的備選，20個不重複值的列可能最適合叢集索引，3個不重複值的列根本就不需要使用索引。這些僅僅是個例子，不是一成不變的規則。記住把索引建立在WHERE查詢子句列出的每一個列上。

在索引選擇時，查詢語句返回行數也是一個重要的因素。最佳化器會考慮非叢集索引花費在每個返回行至少一頁I/O的成本。以這樣的速度，並不需要很長的時間就可以變得更高效的掃描整個表。理性對待結果集，要麼限制結果集的大小，要麼使用叢集索引定位巨大結果集。

4. 使用高效的查詢設計

某些查詢語句本身是資源密集型。這關係到基本資料和索引在大多數RDBMSs（關係型資料庫管理系統）的常見問題，而不是在特定SQL Server中。它 們並不低效，最佳化器將會儘可能實現高效的查詢語句。然而，它們是資源密集型，SQL面向結果集的本性可能使它們出現低效。最佳化器的智能程度不可能消除這些 結構的固有資源成本。和更加簡單的語句相比，他們內在的消耗更大。儘管SQL Server使用最優的訪問計劃，但還是會有限制的。

例如：

• 大型結果集
• IN和OR語句
• 高度非唯一WHERE子句
• !=（不等於）
• 某些列函數，比如SUM
• WHERE子句中的運算式或資料轉換
• WHERE子句的局部變數

有些因素可能需要使用這些查詢語句結構。如果最佳化器可以限制結果集，然後再應用資源密集型的查詢，那麼他們的影響將會減少。

例如:

   1:  低效: SELECT SUM(SALARY) FROM TABLE

   2:   

   3:  高效: SELECT SUM(SALARY) FROM TABLE WHERE ZIP='98052'

   4:   

   5:  低效: SELECT * FROM TABLE WHERE LNAME=@VAR

   6:   

   7:  高效: SELECT * FROM TABLE WHERE LNAME=@VAR AND ZIP='98052'

在第一個例子中，SUM操作使用索引並不能使其加速。每行都需要被讀和求和。設想在ZIP列有一個索引，最佳化器將可能使用這個來初始限制結果集，然後再應用SUM函數。這可能會更快。

在第二個例子中，局部變數直到運行時才被賦值。然而最佳化器無法拖延到運行時才選擇訪問計劃，必須在編譯時間進行選擇。然而，在編譯期間，當產生訪問計 劃時，@VAR的值還不能確定，因此不能使用輸入的@VAR作為索引選擇。可以使用AND子句對結果集進行限制。使用預存程序是一個可選技術，這樣可以傳 遞參數，將參數賦值給預存程序中@VAR值。

大多數RDBMSs的大型結果集是很耗費效能。可以嘗試不返回大型結果集到用戶端作為最終資料選擇。允許資料庫後台執行預定函數，並限定結果集的大小，這種做法效率很高。

5. 使用技術分析低效能

首先分離查詢，或者分離比較慢的查詢。當有少數SQL查詢速度慢，經常表現為整個應用程式速度慢。對能夠顯示產生SQL的工具，使用這個工具的診斷或偵錯模式記錄產生的SQL。使用嵌入式SQL工具會更加簡單。分離速度慢的查詢之前，先做一下下面的步驟：

• 單獨運行疑似速度慢的語句，使用工具（例如ISQL、SAF）驗證實際上是不是很慢。
• 使用SET STATISTICS IO ON，檢查語句的I/O消耗和已選擇的訪問計劃。最佳化器的目的是最小的I/O。記錄邏輯I/O。以這個為基準測量改進成果
• 如果查詢涉及視圖或者預存程序，從中提取這些語句並單獨運行。當嘗試使用不同索引時，訪問計劃是可以改變。
• 有些表可以產生I/O作為觸發器運行，這時要注意可能和這些表有關係的觸發器和視圖。
• 檢查速度慢的語句表的索引。利用之前列出的技術檢查是否有更好的索引，如果有必要就修改。
• 改變索引後重新執行查詢，並觀察I/O和訪問計劃的改變。
• 改進工作完成，運行主程式看看所有的效能是不是有所提升。

檢查程式的I/O或CPU限制的行為。通常這個對確定查詢語句是否在I/O或CPU臨界狀態很有用。我們要花費精力在提高真正的效能瓶頸上，例如， 如果一個查詢是CPU臨界狀態，就算增加更多的記憶體給SQL Server也太可能有效能的提高，當然更多的記憶體還是能提高快取命中率。下面的步驟是檢查SQL Server的I/O和CPU臨界狀態：

• 使用OS/2 CPU監控程式。
• 當執行查詢時，如果CPU使用率保持很高（>70%），這表明是CPU臨界狀態。
• 當執行查詢時，如果CPU使用率保持很低（<50%），這表明也是CPU臨界狀態。
• 使用STATISTICS IO比較CPU利用率資訊

6. 總結

SQL Server能夠提高大型資料庫的效能。要挖掘這個效能的潛力，需要有高效的資料庫設計、索引和查詢語句。這些地區是最可能成為捕獲到重大效能提升的備選地區。嘗試使用索引是一個很特別建議。通常，系統化的方法在分析效能問題上，不僅投入時間少，而且能產生巨大效能提升。

 

在此特別感謝@守望dreamstar對本篇文章的支援。