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

閱讀導(dǎo)航

1. 概述

2.?規(guī)范邏輯數(shù)據(jù)庫設(shè)計(jì)

3. 使用高效索引設(shè)計(jì)

4. 使用高效的查詢設(shè)計(jì)

5. 使用技術(shù)分析低性能

6. 總結(jié)

?

1. 概述

在比較大的范圍內(nèi)找出能夠大幅提高性能的區(qū)域，并且專注于分析這個(gè)區(qū)域，這是最有效的優(yōu)化SQL Server性能的方式。否則，大量的時(shí)間和精力可能被浪費(fèi)在不能提高很大性能的區(qū)域。在這里并沒有討論關(guān)于多用戶并發(fā)所帶來的性能問題。

能獲得最大性能提高的區(qū)域一般是：邏輯數(shù)據(jù)庫設(shè)計(jì)，索引設(shè)計(jì)，查詢設(shè)計(jì)。然而，最大的性能問題經(jīng)常由于缺乏這些方面研究的原因造成。如果性能是被列為一個(gè)需要關(guān)注的問題，聰明的做法是首先專注于這些方面， 因?yàn)樾阅艿拇蠓岣呓?jīng)常是用相對較小的時(shí)間精力完成。

下面開始進(jìn)入正題。

2. 規(guī)范邏輯數(shù)據(jù)庫設(shè)計(jì)

合理規(guī)范性的邏輯數(shù)據(jù)庫設(shè)計(jì)可以產(chǎn)生最佳性能。大量的窄表是標(biāo)準(zhǔn)數(shù)據(jù)庫的特性。少量的寬表是非標(biāo)準(zhǔn)數(shù)據(jù)的特性。高度標(biāo)準(zhǔn)數(shù)據(jù)庫通常關(guān)聯(lián)著復(fù)雜的表的 聯(lián)合查詢，這個(gè)可能損害數(shù)據(jù)庫的性能。不管怎么樣，SQL Server優(yōu)化在快速查詢、高效聯(lián)接、可用有效索引方面是非常有效的，下面是規(guī)范化的好處：

• 如果是窄表，應(yīng)該加快排序和創(chuàng)建索引
• 如果是寬表，最好使用聚集索引
• 索引往往是越窄的表，越應(yīng)該精確
• 更好的利用段去控制表的物理空間
• 每個(gè)表的索引越少，對提高UPDATE操作的性能越有幫助
• 越少的NULLs列，越少的冗余數(shù)據(jù)，越能增加數(shù)據(jù)庫的緊湊性

對于SQL Server，標(biāo)準(zhǔn)化將有助于提升而不是損害性能。隨著標(biāo)準(zhǔn)化的提高，因此需要一定數(shù)量并且復(fù)雜的表連接來檢索數(shù)據(jù)。只要標(biāo)準(zhǔn)化不會導(dǎo)致很多查詢出現(xiàn)超過四個(gè)表的連接，就應(yīng)進(jìn)行標(biāo)準(zhǔn)化進(jìn)程。

如果邏輯數(shù)據(jù)庫設(shè)計(jì)已經(jīng)固定，并且不可能進(jìn)行整體重新設(shè)計(jì)，而且通過研究表明一個(gè)大表存在性能瓶頸，在這樣的情況下，可以有選擇性的對這個(gè)大表進(jìn)行 標(biāo)準(zhǔn)化。如果過存儲過程進(jìn)行訪問數(shù)據(jù)，那么架構(gòu)的改變不會影響應(yīng)用程序。如果不是這樣，可以通過創(chuàng)建視圖來隱藏這種改變，因?yàn)橐晥D可以產(chǎn)生單個(gè)表的錯(cuò)覺。

3. 使用高效索引設(shè)計(jì)

不像很多非關(guān)系系統(tǒng)，不把關(guān)系索引考慮作為邏輯數(shù)據(jù)庫設(shè)計(jì)的一部分。索引能被刪除、添加和更新，除了影響性能以外，不會影響數(shù)據(jù)庫架構(gòu)或者應(yīng)用程序 設(shè)計(jì)。實(shí)現(xiàn)良好的SQL Server性能，高效索引設(shè)計(jì)是非常重要的。由于這些原因，不要猶豫展示不同索引帶來的性能改變吧。

大多數(shù)情況下，優(yōu)化器將可靠地選擇最高效的索引。所有的策略應(yīng)該提供良好的索引優(yōu)化的選擇，相信這是正確的決定。這可以在多種情況下，減少分析時(shí)間并且能提供良好的性能。

接下來介紹索引。檢查SQL查詢的WHERE子句，因?yàn)檫@個(gè)是優(yōu)化的主要焦點(diǎn)。在WHERE子句中列出的列都有可能成為索引的備選。假如有太多的語句需要檢查，挑選有代表性的一組，或者僅僅是速度緩慢的那組。

最好使用窄索引。窄索引比混合索引和復(fù)合索引更加高效。窄索引每頁行越多，索引級別應(yīng)該越低，這樣才能提高性能。SQL Server優(yōu)化只是維護(hù)統(tǒng)計(jì)數(shù)據(jù)在復(fù)合索引最重要的列上。因此，如果復(fù)合索引的第一列可選擇性很差，那么就不優(yōu)化這個(gè)索引。

優(yōu)化器可以快速、高效的分析成百上千的索引和表連接的可能性。有更多的窄索引提供給優(yōu)化器，優(yōu)化器就會有更多可能的選擇，這對性能很有幫助。有較少的寬索引、復(fù)合索引提供給優(yōu)化程器，優(yōu)化器只有很少選擇的可能性，這對性能會有影響。

索引數(shù)目太多性能可能會降低，因?yàn)樯婕暗礁逻@些索引的開銷。然而，大量的面向更新操作需要更多的讀操作，而不是寫操作。假如，嘗試新索引時(shí)提高了性能，那就不要猶豫，使用這個(gè)所以吧。

使用聚集索引。適當(dāng)?shù)氖褂镁奂饕梢詷O大的提升性能。甚至聚集索引可以使UPDATE和DELETE操作提速，因?yàn)檫@些操作需要很多讀操作。可能 每個(gè)表只有單一的聚集索引，因此，要靈活地利用這個(gè)索引。返回行數(shù)的查詢或者涉及一個(gè)范圍值的查詢都是一個(gè)可能被聚集索引提高性能的候選。

例子：

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列，對于非聚集索引可能不是一個(gè)適合的候選。盡量在返回很少行數(shù)據(jù)的列上使用非聚集索引。

檢查列數(shù)據(jù)的唯一性。這樣將幫助決定，什么樣的列作為聚集索引、非聚集索引、無需索引的備選。

查詢語句檢查數(shù)據(jù)的唯一性，例子：

1: SELECT COUNT (DISTINCT COLNAME) FROM TABLENAME

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

在索引選擇時(shí)，查詢語句返回行數(shù)也是一個(gè)重要的因素。優(yōu)化器會考慮非聚集索引花費(fèi)在每個(gè)返回行至少一頁I/O的成本。以這樣的速度，并不需要很長的時(shí)間就可以變得更高效的掃描整個(gè)表。理性對待結(jié)果集，要么限制結(jié)果集的大小，要么使用聚集索引定位巨大結(jié)果集。

4. 使用高效的查詢設(shè)計(jì)

某些查詢語句本身是資源密集型。這關(guān)系到基本數(shù)據(jù)和索引在大多數(shù)RDBMSs（關(guān)系型數(shù)據(jù)庫管理系統(tǒng)）的常見問題，而不是在特定SQL Server中。它 們并不低效，優(yōu)化器將會盡可能實(shí)現(xiàn)高效的查詢語句。然而，它們是資源密集型，SQL面向結(jié)果集的本性可能使它們出現(xiàn)低效。優(yōu)化器的智能程度不可能消除這些 結(jié)構(gòu)的固有資源成本。和更加簡單的語句相比，他們內(nèi)在的消耗更大。盡管SQL Server使用最優(yōu)的訪問計(jì)劃，但還是會有限制的。

例如：

• 大型結(jié)果集
• IN和OR語句
• 高度非唯一WHERE子句
• !=（不等于）
• 某些列函數(shù)，比如SUM
• WHERE子句中的表達(dá)式或數(shù)據(jù)轉(zhuǎn)換
• WHERE子句的局部變量

有些因素可能需要使用這些查詢語句結(jié)構(gòu)。如果優(yōu)化器可以限制結(jié)果集，然后再應(yīng)用資源密集型的查詢，那么他們的影響將會減少。

例如:

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'

在第一個(gè)例子中，SUM操作使用索引并不能使其加速。每行都需要被讀和求和。設(shè)想在ZIP列有一個(gè)索引，優(yōu)化器將可能使用這個(gè)來初始限制結(jié)果集，然后再應(yīng)用SUM函數(shù)。這可能會更快。

在第二個(gè)例子中，局部變量直到運(yùn)行時(shí)才被賦值。然而優(yōu)化器無法拖延到運(yùn)行時(shí)才選擇訪問計(jì)劃，必須在編譯時(shí)進(jìn)行選擇。然而，在編譯期間，當(dāng)生成訪問計(jì) 劃時(shí)，@VAR的值還不能確定，因此不能使用輸入的@VAR作為索引選擇。可以使用AND子句對結(jié)果集進(jìn)行限制。使用存儲過程是一個(gè)可選技術(shù)，這樣可以傳 遞參數(shù)，將參數(shù)賦值給存儲過程中@VAR值。

大多數(shù)RDBMSs的大型結(jié)果集是很耗費(fèi)性能。可以嘗試不返回大型結(jié)果集到客戶端作為最終數(shù)據(jù)選擇。允許數(shù)據(jù)庫后臺執(zhí)行預(yù)定函數(shù)，并限定結(jié)果集的大小，這種做法效率很高。

5. 使用技術(shù)分析低性能

首先分離查詢，或者分離比較慢的查詢。當(dāng)有少數(shù)SQL查詢速度慢，經(jīng)常表現(xiàn)為整個(gè)應(yīng)用程序速度慢。對能夠顯示生成SQL的工具，使用這個(gè)工具的診斷或調(diào)試模式記錄生成的SQL。使用嵌入式SQL工具會更加簡單。分離速度慢的查詢之前，先做一下下面的步驟：

• 單獨(dú)運(yùn)行疑似速度慢的語句，使用工具（例如ISQL、SAF）驗(yàn)證實(shí)際上是不是很慢。
• 使用SET STATISTICS IO ON，檢查語句的I/O消耗和已選擇的訪問計(jì)劃。優(yōu)化器的目的是最小的I/O。記錄邏輯I/O。以這個(gè)為基準(zhǔn)測量改進(jìn)成果
• 如果查詢涉及視圖或者存儲過程，從中提取這些語句并單獨(dú)運(yùn)行。當(dāng)嘗試使用不同索引時(shí)，訪問計(jì)劃是可以改變。
• 有些表可以生成I/O作為觸發(fā)器運(yùn)行，這時(shí)要注意可能和這些表有關(guān)系的觸發(fā)器和視圖。
• 檢查速度慢的語句表的索引。利用之前列出的技術(shù)檢查是否有更好的索引，如果有必要就修改。
• 改變索引后重新運(yùn)行查詢，并觀察I/O和訪問計(jì)劃的改變。
• 改進(jìn)工作完成，運(yùn)行主程序看看所有的性能是不是有所提升。

檢查程序的I/O或CPU限制的行為。通常這個(gè)對確定查詢語句是否在I/O或CPU臨界狀態(tài)很有用。我們要花費(fèi)精力在提高真正的性能瓶頸上，例如， 如果一個(gè)查詢是CPU臨界狀態(tài)，就算增加更多的內(nèi)存給SQL Server也太可能有性能的提高，當(dāng)然更多的內(nèi)存還是能提高緩存命中率。下面的步驟是檢查SQL Server的I/O和CPU臨界狀態(tài)：

• 使用OS/2 CPU監(jiān)控程序。
• 當(dāng)運(yùn)行查詢時(shí)，如果CPU使用率保持很高（>70%），這表明是CPU臨界狀態(tài)。
• 當(dāng)運(yùn)行查詢時(shí)，如果CPU使用率保持很低（<50%），這表明也是CPU臨界狀態(tài)。
• 使用STATISTICS IO比較CPU利用率信息

6. 總結(jié)

SQL Server能夠提高大型數(shù)據(jù)庫的性能。要挖掘這個(gè)性能的潛力，需要有高效的數(shù)據(jù)庫設(shè)計(jì)、索引和查詢語句。這些區(qū)域是最可能成為捕獲到重大性能提升的備選區(qū)域。嘗試使用索引是一個(gè)很特別建議。通常，系統(tǒng)化的方法在分析性能問題上，不僅投入時(shí)間少，而且能產(chǎn)生巨大性能提升。

?

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

總結(jié)

以上是生活随笔為你收集整理的SQL Server 性能优化之——系统化方法提高性能的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

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