如何使 SQL Server高效 — 設計（ITPUT 討論匯總）

1、     您認為在設計SQL Server對象時，主要會考慮哪些因素來避免出現效能問題?

討論匯總——總體設計

l  應該根據系統架構類型或主要操作進行有針對性的設計: 如 OLTP 、OLAP 、ETL 等

l  邏輯清晰，使應用程式更便於開發。有良好的擴充性和維護性，減少資料冗餘等

l  在DB管理上，得從CPU、HDD配置著手，在DB開發上，從商務程序、邏輯、功能、DB結構、SQL指令準確設計和完成目標

l  設計檢視時應針對某個具體需求，不應過多考慮其重用性

l  觸發器也應該避免使用，考慮將觸發器完成的功能改寫到觸發語句中。對於調式，效能跟蹤來說，不用觸發器也會更直觀些

l  先考慮會出現什麼樣的效能問題（訪問效能？插入效能？）；接著考慮資料的變化特點（是小表還是大表？資料的變化頻率如何？裡面資料重複性如何？），同時也考慮業務特點（是訪問多，還是插入多，還是即訪問多還有插入多）；然後結合SQLServer的原理和特點進行設計，比如要不要使用索引，是使用叢集索引比較好，還是非叢集索引比較好？表的資料要怎麼儲存才能充分發揮SQL Server的特性（如從SQL Server資料存放區、page、片段等方面考慮。還有要不要使用讀寫分離？要不要從整個資料庫層級也考慮？）；最後，考慮對設計完成後的對象，進行測試，然後尋找最優的方案

l  邏輯資料庫和表的設計。資料庫的邏輯設計、包括表與表之間的關係是最佳化關係型資料庫效能的核心。一個好的邏輯資料庫設計可以為最佳化資料庫和應用程式打下良好的基礎。標準化的資料庫邏輯設計包括用多的、有相互關係的窄表來代替很多列的長資料表

l  讀寫分離架構（如報告資料庫和交易資料庫部署分離等）；關注實體儲存體；檔案布局(資料檔案和記錄檔隔離，記錄檔最好使用高速裝置)

l  考慮資料庫規模和重點表的規模，資料庫檔案組以及內部分區也需要考慮在內，這裡可以跟磁碟一起考慮

l  硬體的二級緩衝會影響，把資料、日誌、索引放到不同的I/O裝置上，增加讀取速度。資料量（尺寸）越大，提高I/O越重要.

討論匯總——表及索引設計

l  對錶的應用情境進行分析，分為日誌表、參數表和頻繁更新刪除表

l  合理的表資料量估算，分為大表、中表、小表，考慮大表分區/分區

l  清晰的邏輯和物理模型設計，要准從第三範式，必要時也需要反範式（參考下面的某些情境）

l  邏輯結構上可以適當冗餘，避免太多表關聯；物理結構上選擇效能高的磁碟策略，表分區，讀寫分離

l  設計表對象時首先考慮功能，再考慮效能。如果表中欄位過多，考慮拆分對多個小對象（小表）；如果表中有訪問頻率不高，但有需較大儲存空間的欄位，考慮拆分另外儲存

l  考慮好archive 機制，保證線上業務表資料量不能太大，其餘的到曆史表，不然SQL Server 單表到億層級，怎麼都不好使

l  根據業務需求，給各種資料選擇恰當的資料類型，節約資料空間的同時提高效率

l  用佔用空間盡量小的資料類型儲存資料, 如date, smalldatetime、smallint、tinyint等

l  設計索引時先考慮查詢頻率和更新頻率，切忌為偶爾執行的語句建立索引，建立複合索引時需考慮列的可選擇性和使用頻率來確定先後順序

l  再次對錶的索引進行評估（需要應用開發人員的參與，擷取其SQL），初步構建索引，從索引列的選擇性、索引列的傾斜度，索引列的使用頻度、索引列的使用順序、表索引的個數的綜合衡量

l  索引的效率也很重要，具體要看業務會怎麼樣去用，設計合理的索引組合，同時需要定期跟蹤

l  建立高效的索引, 使用索引的包含列、過濾索引等功能

討論匯總——tempdb

l  ,tempdb在SQL Server停掉,重啟時會自動的drop,re-create. 根據model資料庫會預設建立一個新的8MB(mdf file:8MB;ldf file:1MB， ecoverymodel為simple

l  tempdb對IO的要求比較高，最好分配到高IO的磁碟上且與其他的資料檔案分到不用的磁碟上,以提高讀寫效率

l  應該根據根據CPU個數來配置tempdb的檔案數（有幾個CPU建立幾個檔案）

個人補充：tempdb 的檔案初始大小可以設定，通過ALTER DATABASE MODIFY FILE，或者介面操作直接設定檔案的初始大小，這個設定在重啟SQL服務時，重建tempdb會使用這個設定值

個人補充

l  合理劃分資料庫。設計開始的時候就考慮資料庫隔離，不要把什麼東西都放在一個資料庫裡面。資料庫的劃分可以綜合考慮這些方面：業務功能、資料重要性、查詢和讀取比例、資料存放區周期

l  為資料庫選擇合適的恢複模型（RECOVERY MODEL）。這個對資料庫的寫入和備份影響比較大

l  把控需求，明確使用資料庫的必要性以及資料存放區的詳細程度

l  明確常規欄位的定義（特別是在有多個Team Dev的情況下），避免因為設計不同，導致在互動（包括相互查詢）資料時帶來的效能損失

l  確定合理的對象使用期。有的人喜歡緊密結合當前需求來設計表，結果一段時間之後，可能因為某些變化，不得不修改表結構，修改包含資料的表結構，帶來的影響是比較大的；而某些人為了避免這種情況，乾脆設計一個非常寬鬆的表，結果表結構的修改頻率可能是非常低了，但是效能可能會受到比較大的影響（比如設計的一些關鍵字段為了滿足未來需要，設計的長度很寬，導致查詢效能不好）

爭議問題

l  主鍵使用自增列

支援使用的觀點：

使用系統產生的主鍵，避免使用複合鍵，外鍵總是關聯唯一的鍵欄位，不要把社會保障號碼（SSN）或社會安全號碼碼（ID）選作鍵

反對使用的觀點：

如果一個表可以用自增列作主鍵，那麼這個表的主鍵可有可無。例如記錄網站使用者登入資訊的日誌表。這種表可以不要主鍵，將登入時間設為叢集索引即可。

GUID產生時需要耗用cpu，也不在考慮範圍。當表沒有合適的列設主鍵時，我會考慮固定長度的流水號之類的欄位當主鍵。

不管是自增列、GUID，還是流水號當主鍵，只要表本身欄位有合適的欄位作主鍵，哪怕是複合鍵，只要長度不太長(100位元組以內可考慮，50以內最合適)，以及欄位數不太多（3個是能接受的範圍，5個也可考慮），就應該使用這些欄位來當主鍵。理由是：在表的join以及資料查詢中，真正被查詢的，還是這些欄位。偽主鍵們除了佔用空間，沒有一點好處。

如果將一個自增列設為主鍵，它的好處在哪兒？這樣看起來，就是為了給表安一個主鍵而去create的。你可能會說，自增列當主鍵，極大減少page spliting，減 少片段；叢集索引包含在每個非叢集索引的尾部，極大減少索引size等等。我卻覺得：1. 一個表的主鍵，最好能反映出這個表的邏輯設計，意思是，我一看這個表的主鍵，就大致知道這個表存的什麼內容。2:就算你使用了自增列當主鍵，但實際上這個表的邏輯主鍵，你還是必須得建立這樣的索引。因為資料檢索時，往往還是根據這個條件來查詢的。自增列的主鍵，也不會用來當外鍵，因為它的值極度不靠譜。這樣你和別的表關聯時，還是仍然需要使用表的邏輯主鍵欄位來關聯。所以這樣的自增列主鍵，是多餘的，沒有必要的

個人的觀點：

重點是根據應用情境合理選擇，不是絕對的用或不用某種。

對於一個表來說，主鍵不是必須的，聚焦索引也不是必須的。

從功能上來講，主鍵是對錶中資料的一種約束（唯一、不允許NULL值），我們通過唯一索引（或約束），並且在欄位上設定 NOT NULL屬性可以達到同樣的效果。所以玉鍵是可以被取代的，這也說明它不是必需的了（當然，就算沒有等同的可取代的功能，從業務上來講，也並不是每個表都要有一個能夠確保資料唯一的東東）。

在提主鍵的時候，會提聚焦索引，主要是因為主鍵預設是聚焦索引（如果表中原來沒有聚焦索引，並且沒有指定NONCLUSTERED的情況下），這表明主鍵並不是聚焦索引，只是可以把它設定為聚焦索引

所以，單獨討論自增列做主鍵沒有任何意義，我們增加一個自增列，並且把它確認它是唯一和非NULL值的，對於我們的業務資料而言，不存在任何意義（多餘和沒有必要）。

需要考慮的，是主鍵作為聚焦索引的情況，當主鍵為聚焦索引時，我們要考慮的，除了主鍵的特性外，還要考慮聚焦索引的特性。聚焦索引是和資料存放區在一起的，它決定資料的儲存順序，表中資料存放區的非葉子層是聚焦索引值（葉子層是資料）；另外，聚焦索引鍵是非聚焦索引的行定位器（指向資料記錄的指標，如果叢集索引不是唯一的索引，SQL Server 將添加在內部產生的值（稱為唯一值）以使所有重複鍵唯一。此四位元組的值對於使用者不可見。僅當需要使聚集鍵唯一以用於非叢集索引中時，才添加該值）。如果表中沒有聚焦索引，那麼非聚焦索引的行定位器是指向行的指標（由檔案標識符 (ID)、頁碼和頁上的行數產生的行 ID (RID)）。很顯然，非聚焦索引不依賴聚焦索引，而資料存放區我們也並不一定需要保證按照某個順序來儲存，所以聚焦索引也不是必需的。但是從效率上來講，順序讀取比行ID這種無序讀取更有效率，所以對於頻繁查詢的表，聚焦索引是需要的。

結合前面的資訊我們知道，對於可能涉及比較頻繁查詢的情況，是需要聚焦索引的（確保進行有效順序讀取）；同時我們也知道，聚焦索引索引值是非聚焦索引的行定位器（儲存在非聚焦索引的葉子層），所以聚焦索引的寬度應該要儘可能小（每個非聚焦索引中都要存一次，當然是越小空間佔用越少，I/O效率越高），確保唯一（這樣就沒有必要為了保證唯一去加那個附載入的值了）；另外，NULL值沒有比較意義，所以最好是非NULL；資料的寫入順序最好是與聚焦索引索引值產生順序差不多，並且頻繁的修改盡量不會破壞順序（這樣減少片段的產生）；最後，如果聚焦索引的定義發生修改，那就相當於表和索引都要重新組織儲存一次，所以最好的情況是，聚焦索引列定義基本上不會被修改。結合評估這些所有的情況，如果業務資料列是沒有合適的，那麼自增列會是一個不錯的選擇。

當然，也有人可能會說，聚焦索引的檢索效率是最高的，用自增列做主鍵，是不是浪費了這個最高效的機會。實際上，這個有點誤區，聚焦索引的葉子結點是資料，非聚焦索引的葉子結點是行定位器，這意味著，如果表中的資料不只一列的話，聚焦索引Seek所要載入的頁可能比非聚焦索引多（因為它的葉子層是資料，佔用的空間比非聚焦索引的行定位器要多），它的好處是SEEK到索引值的時候，也就可以直接取出對應的記錄了（都在同一頁上）；而非聚焦索引還要通過行定位器去拿對應的記錄；但是如果我們要的資料在索引中就全部包含的話，聚焦索引的效率就可能要低一些了。另外一個就是SCAN的情況，很顯然，非聚焦索引涉及的PAGE比聚焦索引少，SCAN會更有優勢。不是所有的查詢都可以做SEEK，一般查詢頻繁的表也有多種常用的查詢組合，所以總體來說，業務資料列做主鍵並不是在所有的情境下都能體現出優勢。

 

討論帖

後續討論話題：

2、 您認為在T-SQL編寫（包括預存程序、函數和視圖）上，哪些因素會影響SQL Server效率?

3、 在設計資料庫操作程式上，您認為應該注意哪些事項，以確保能夠有效地使用資料庫?

4、 在您的SQL Server使用過程中，有哪些令您非常困惑的效能問題 ?