英文版：http://www.codeproject.com/KB/database/OptimizeDBUseIndexing.aspx不過好像只將了八點進過數月你和你的團隊設計開發了個成功的互連網應用。你有很好的用戶端介面，因此在一個短的時間段內，能吸引數千個使用者註冊和使用你的網站。你的客戶，管理層，團隊和你每天都很開心。生活不是玫瑰花園。當使用者數量在你的網站每天迅速發展壯大，問題開始出現。客戶開始通過電子郵件抱怨網站訪問非常緩慢（和一些生氣的郵件），客戶要求改進，同時開始損失你的使用者。你開始分析這個應用。不久你發現了問題，當應用嘗試儲存和更新資料時資料庫執行非常緩慢。資料庫中的資料表已經變得很大，包含幾十萬行資料。測試團隊在產品網站執行一個測試，排序提交過程取得資料需要5分鐘才能成功，然而使用者只要2/3秒成功測試網站。這是一個老故事，全世界有數千成熟的應用項目。幾乎每個開發人員包括我花費部分時間看著他成長。所以，我知道Step1: Apply proper indexing in the table columns in the database第一步，在表的列上建立適當的索引那麼，建立適當的索引在資料庫最佳化中應該是第一步考慮的。但是我覺得合理的索引首先考慮，是因為以下兩點：1、這將是最短時間內最有效可能改善應用效能的措施；2、資料庫中提交建立索引將不需要你做對應用的修改，因此不需要重建，部署；當然，如果你能找到它這是快速效能改進，並且索引在當前資料庫中沒有適當地完成。然而，如果索引都已經完成了，我將不建議你走這一步。什麼是索引？我認為，你知道什麼是索引。但是，我看到一些人存在混淆這個。所以，讓我們再嘗試一次索引，讓我們讀個小故事。在一個古老城市有一個大的圖書館。他們有數千圖書，但是圖書在書架上沒有任何排序，因此每次一個人詢問在圖書管詢問一本書時，圖書館必須查看每本書直到找到這人想要的書。在圖書館找到想要的書要花費數小時，以及客人等待很長時間。[表沒有主鍵，所以需要在所有資料中尋找，資料庫引擎掃描全表找到符合的行，哪些執行非常慢]當圖書館中書一天天增加以及來借書的人也越來越多，圖書館管理員就非常悲慘。然後某天，一個明智的人來到圖書館，看到圖書館的情形，他勸告他按照編號每本書和根據他們的數字安排這些在書架。“我將得到哪些好處”圖書管理員問，明智的人回答說“那麼，現在，如果某人給您一個帳簿編號並且請求那本書，包含book’s數位您能迅速發現架子，並且，在那之內擱置，您能發現書作為這些根據他們的數字非常迅速被安排”。[圖書編號聽起來像在資料表中建立主鍵，當你建立一個主鍵在資料表中一個叢集索引樹建立以及所有資料頁包含表中所有行在檔案系統中按照他們主鍵的值進行物理排序，每一個資料頁包含行的主鍵進行排序。所以，在表中查詢一些行時，資料庫伺服器先找到符合的資料頁首先使用叢集索引樹，然後尋找資料頁中包含的關鍵字的資料行]“這就是我需要的！”興奮的圖書管理員立即開始編號圖書和擺放那些不同的書架。他花費一整天做這個安排，但是到最會，他測試尋找一本書效果很好，圖書管理員非常愉快。[當你建立表主鍵時。在內部，在資料檔案按照主索引值進行，叢集索引樹是建立和資料頁是物理排序。同樣你容易理解，一個表只能建立唯一一個叢集索引，也只能使用一個列的值做主鍵。就好像書只能使用一個標準編號]等等！有一個問題沒能解決。在第二天，一個人詢問一本書只知道書名（不知道書的編號），可憐的圖書管理員，沒有辦法，從1開始尋找，最後發現在67號架子上。這花費了管理員20分鐘來尋找書。最早，還未編排的時候他使用2、3個小時尋找書，因此還需要改善。但是，比較搜尋時間使用30秒，這20分鐘似乎是非常高的時間對於管理員說。所以他問聰明的人應該如何改善。[當你有一個產品表時主鍵時產品ID，但是你沒有其他索引在這張表裡。因此，當根據產品名稱查詢產品時，資料庫引擎沒有方式，會掃描所有物理排序的資料頁，直到找到需要的書。]這個聰明的人告訴圖書館管理員“好吧，因為你已經按照書的編號排序了所有的書，你不可能重新整理這些。所以，更好為所有書名建立一個目錄或索引和它對應號碼的地方。但是，在這目錄裡，整理書名的字母順序排列和分組書名，如果有一些想找的書名“Database Management System”，你可以通過以下步驟找到書籍”1、跳過進入“D”部分的書名，並且找到書名放在哪裡2、找到書的編號“你是個天才”圖書管理員大聲說。經過數小時他建立書名目錄，使用書名測試後現在只需要一分鐘就能找到需要的書。圖書管理員認為，人們會請求書名等幾個標準尋找。所以，他建立其他相似的目錄。並且發現共同的標準（書號，名字，作者名稱），只需要1分鐘就能尋找。苦難的圖書管理員很快緩解了人們的排隊，因為能真正的快速。圖書管理員從此生活愉快。故事結束。現在，我確定你現在理解了什麼是索引，為什麼他們重要以及內部是如何工作的。例如如果我們有一張產品表，建立一個叢集索引（建立主鍵列時自動建立），在產品名稱列建立非叢集索引。如果我們做了這些，資料庫引擎建立一個非叢集索引的索引樹（像，圖書名稱那樣排序）從產品名稱排序在索引頁上。每索引頁與他們對應的主關鍵字一起將包含一些產品範圍名稱。所以，當一個產品查詢時，這個產品的名稱查詢，資料庫引擎將首先查詢產品名稱的非叢集索引樹。一次尋找，資料庫引擎然後尋找叢集索引樹根據主鍵尋找到實際的書所載的行。索引樹看起來就像：圖示：索引樹結構這叫做B+Tree（平衡的樹）。當搜尋樹索引從根節點開始時，中間節點包含範圍值並且只sql引擎到何處去，葉子節點是包含實際給定的值。如果這是一個叢集索引，葉子節點是物理資料頁。如果是非叢集索引，葉子節點包含索引值，沿著索引值資料庫引擎能找到叢集索引樹上的值。通常，在索引樹種找到一個需要的值和跳過從那裡的實際行花費的資料庫引擎的時間極小。所以，索引時一般改善資料查詢操作。因此，在你的資料庫中時刻應用索引最快的返回結果集。根據這些步驟，保證適當的索引建立在你的資料庫中。在你的資料庫中，每個表都建立主鍵。這將保證每個表都有一個叢集索引的建立，並且，這些頁在表中進行物理排序。所以一些從資料表中取資料操作使用主鍵或一些排序操作，在表上可以最快的排序。下列條件適合建立非叢集索引：1、經常使用的查詢條件；2、使用JOIN其他表；3、使用外鍵；4、高選擇性（總行數在（0%-5%）之間的列）5、使用排序的列；6、對象是XML（主要和二次索引需要被創造。 更多在此在以後的文章上）下面一個再表上建立索引的例子CREATE INDEXNCLIX_OrderDetails_ProductID ONdbo.OrderDetails(ProductID) 或者您使用sql管理器來建立step2：建立適當的遮蓋索引因此，在你的資料庫中你有建立所有適當的索引的，好？假如，你在銷售表（SelesID,SalesDate,SalesPersonID,ProductID,Qty）建立一個外鍵列（productID）的過程中。現在，假設那，ProductID是一個高選擇性列（選擇未超過5%總數的行ProductID值，在查詢中），一些查詢語句從這張錶快速查詢，好？是，比較與外鍵列上建立索引，一個全表掃描（掃描所有相關頁在表裡的需要資料）。但是，然而，改善查詢的範圍。讓我們假定那些，銷售表包含10000行記錄，用sql查詢400行（4%記錄行）。語句：SELECT SalesDate, SalesPersonID FROM Sales WHERE ProductID = 112 讓我們嘗試理解資料庫引擎怎麼被執行。1、銷售表先尋找非叢集索引ProductID列，所以，它尋找非叢集索引樹上尋找ProductID=112；2、在索引頁包含ProductID=112也存在所有的叢集索引列（所有主索引值，那是SalesIDs，那有ProductID=112假定主鍵列是已經建立在銷售表中）；3、在主鍵（400這裡），在符合的資料頁中，資料庫引擎查詢叢集索引列尋找真實列位置；4、查詢主鍵，當找到，從符合的行中，資料庫引擎查詢SalesDate和SalesPersonID列值。請記住上面的幾步，查詢ProductID=112的資訊，資料庫引擎查詢叢集索引，取出附加的行。那像是，沿著包含的叢集索引，如果非叢集索引頁可能也包含在詢問指定的其他兩個列（SalesDate，SalesPersonID）。sql引擎在上述步驟必須執行第三步和第四步，因此，能由“seeking”快速的選擇與其的結果，從索引頁代碼：CREATE INDEX NCLIX_Sales_ProductID--Index nameON dbo.Sales(ProductID)--Column on which index is to be createdINCLUDE(SalesDate, SalesPersonID)--Additional column values to include 第三步：重組索引好，在你的表中你建立了所有合適的索引。或，多半，索引已經被建立在資料庫表中。但是，你或許沒有根據您的期望獲得比較好的效果。有可能，索引發生了片段。什麼是索引片段？索引片段是在索引頁上進行插入，更新，刪除操作造成的索引頁分裂。如果索引有很多的片段，掃描和查詢索引需要的時間將更多。所以取資料操作執行很慢。兩種類型的片段會出現：內部片段：由於資料在刪除或更新操作，索引或資料頁會分散（造成許多空的行）。增加查詢的時間。外部片段：在索引頁中資料插入或更新操作在配置一個新的索引頁在檔案系統中，表現為減少where查詢結果集。並且，資料庫伺服器不可能利用預讀操作，下相關資料頁沒有被保證接觸的，這些下頁可能任何地方在資料檔案。怎麼知道是否發生了索引片段？在你的資料庫中執行下列sql。（sql2005或較早的資料庫，替換資料庫名“AdventureWorks）SELECT object_name(dt.object_id) Tablename,si.nameIndexName,dt.avg_fragmentation_in_percent ASExternalFragmentation,dt.avg_page_space_used_in_percent ASInternalFragmentationFROM( SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats (db_id('AdventureWorks'),null,null,null,'DETAILED')WHERE index_id <> 0) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_idAND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC 根據結果，你會發現索引片段發生在哪裡，使用以下標準。1、ExternalFragmentation 值 > 10 表明對應的內部片段發生了，2、InternalFragmentation 值 < 75 表明出現了外部片段。怎麼管理這些片段？你有兩條路：1、重新整理索引，執行以下語句：ALTER INDEX ALL ON TableName REORGANIZE2、重新建立索引，執行以下語句：ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=90,ONLINE=ON) 在表裡你也能重新建立或整理個別索引，使用索引名稱代替“ALL”關鍵字。或者，使用sql管理器重建索引。什麼時候使用整理索引，什麼時候使用重新建立索引？外部片段值在10-15以及內部片段在60-75之間可以選擇整理，其他情況選擇重建索引。重建索引時，有個重要的事情，當在一個特別的表上重建索引時，整個表將鎖住，因此在一個大的產品資料庫，鎖住是不被允許的，因為，重建索引需要數小時。幸運的是，在SQL Server 2005中有一個辦法。你能使用ONLINE操作在重建索引時，可以重建索引，且不鎖表。第二部分：：：：：第四步：移動sql語句從應用程式中到資料庫伺服器我知道你可以不喜歡這個建議。你擷取已經使用ORM產生訪問sql。或者，你或你的團隊可能有個原則，保持sql在你的應用裡。但是，然而，如果你需要最佳化資料庫訪問效能，或如果你需要在你的應用裡尋找故障，我將建議你移動你的sql語句寫在資料庫中。為什嗎？好，我有以下理由：1、從應用把sql移出來，你可以使用預存程序/視圖/函數/觸發器 去除一些重複的SQL。這將確保你的sql代碼的可用性。2、實現所有的SQL代碼使用資料庫物件，將可能找到其中無效率的SQL，那是效能慢的責任。並且，這將你處理SQL代碼。3、你的SQL可以使用先進的索引集數（去看看系列的最新部分）。並且，這將協助您消滅您已經寫了的所有程式SQL，儘管實際上索引將讓你在你的應用中迅速找到故障點的效能問題，也許這四步步能立刻給您一個真正的表現助力。但是，這主要將使您執行其他隨後最佳化步和容易地申請不同進一步優選您的資料存取慣例的其他技術。如果您使用ORM實施在您的應用的資料存取資料，你可能發現你的應用，能很好的執行在你的開發測試環境裡。但是生產環境就不一定了。選擇ORM時，您可以隨時轉換您的資料庫類型。第五步：識別無效率的SQL,分析使用最優的方法無論多好的索引在你的資料庫中，如果你使用簡單的條件訪問資料庫，你一定得到緩慢的表現。我們總是想寫好的代碼，不是嗎？當我們為一個特殊要求寫一個資料操作。。。但是，許多選擇，我們有不同的才幹，經曆和觀點的人必須在一個團隊裡一起工作。應此我們的團隊以不同的方式寫代碼，並且丟失了最優的方法。當寫代碼時，我們首先想到的是讓他工作。但是我們的代碼在生產環境跑，就會有問題。現在時間驗證代碼，時間最好的驗證你的代碼。我有一些Sql調優方法你能做。但是，我肯定你已經知道了大多數。問題是，在實際中，你沒有在你的代碼中很好的實現（當然，你總是有一些好的理由不這麼做）。但是會發生什麼，你的代碼跑得緩慢，並且你的客戶變得不開心。所以，知道最優的方法是不夠的。更多重要的部分是，你寫更好的SQL，這是更重要的事。一些sql最優的方法不要寫“SELECT *”的Sql語句1、不必要的列會增加所消耗的時間；2、資料庫引擎不能利用“覆蓋索引（Covered Index）”（之前文章有討論），執行會緩慢。避免不必要的列和表的join條件1、查詢不必要的列會增加查詢開銷，特別是增加的列時大的類型時；2、包含不必要的表的join查詢，會強制資料庫引擎去取不必要的資料和增加執行事件。不要使用含有COUNT()的子查詢例如：SELECT column_list FROM table WHERE 0 < (SELECT count(*) FROM table2 WHERE ..) 可以使用：SELECT column_list FROM table WHERE EXISTS (SELECT * FROM table2 WHERE ...) 1、當你使用COUNT()時，sql server不知道你要做的是一個存在的驗證。他計數所有匹配的值，通過表掃描或掃描最小的非叢集索引；2、當你使用EXISTS時，sql server知道你是做存在的驗證。他會找第一個匹配的值，它返回true就停止查看。同樣適用使用COUNT()代替IN或ANY。嘗試避免串連兩種類型的列1、當join兩列不同類型時，一列必須轉換成另一列類型。誰的類型低轉換誰。2、如果你join表屬於不相容的類型，其中一個能使用索引，則查詢最佳化不能使用這個索引。例如：SELECT column_list FROM small_table, large_table WHEREsmalltable.float_column = large_table.int_column 這種情況下int列會轉換成float列。因為int低於float。它不能使用larget表的int列的索引，雖然能使用small表float列上的索引。嘗試避免鎖住1、總是訪問表在同樣順序預存程序和觸發器。2、保持你的事務儘可能短。盡量少的資料交換。3、在一個事物中不要等待使用者的中間輸入。寫SQL使用寧可使用“組合途徑（set based approach）”不要使用“程式途徑（Procedural approach）”1、資料庫引擎基於集合sql被設定最佳。因此，應該避免使用程式方法（使用遊標處理集合行）操作大的結果集（超過1000行）；2、怎樣清除“程式sql”？看這些例子：--使用內聯子查詢替換使用者定義函數。--使用有關係的自查詢替換遊標代碼；--如果程式編碼真的是必須的。至少，使用一個表變數替代遊標操作結果。不要使用COUNT(*)獲得表的記錄數避免使用動態SQL避免使用暫存資料表替換LIKE查詢，使用全字元查詢文本資料嘗試使用UNION替換OR操作大對象使用消極式載入策略使用VARCHAR(MAX), VARBINARY(MAX) 和 NVARCHAR(MAX) 1、在sqlserver2000一行不能儲存超過8000bytes的值。SQLserver內部頁有8kb的限制。所以儲存更多資料在單列中，你需要使用TEXT,NTEXT或IMAGE等資料類型。在使用者自訂函數中實施跟隨好練習在預存程序中實施跟隨好練習在觸發器中實施跟隨好練習在視圖中實施跟隨好練習在事務中實施跟隨好練習第三部分；；；第六步：應用一些進階的索引技巧在xml列上建立索引主要的xml索引CREATE PRIMARY XML INDEXindex_nameON <object> ( xml_column ) 次要的索引CREATE XML INDEXindex_nameON <object> ( xml_column )USING XML INDEX primary_xml_index_nameFOR { VALUE | PATH | PROPERTY }第七步：使用非正常，曆史表和預先列；第四部分；；；；；第八步：診斷效能問題，有效使用sql profiler和Performance Monitoring tool http://www.codeproject.com/KB/database/DiagnoseProblemsSQLServer.aspx
