sql server需要儲存1000萬條資料該怎麼辦？

sql server需要儲存1000萬條資料該怎麼辦？

公司一個大型業務體統，資料量龐大，主要的幾項資料每項資料量均超過1000萬條記錄。採用的是sqlserver，小弟琢磨了很久，一直找不到一種可以高效率儲存和處理資料的好方法，各位高手有何高見和經驗請指點迷津！不勝感激！（每日活動資料量也會上百萬） 根據在Oracle上的應用經驗，通常在一張表中儲存超過2,000,000條以上記錄，則應當考慮對資料表進行分區。如果要使用SQL Server；可以對資料按月進行水平資料分割，即一個月一張表，對於曆史資料可以移出當前資料庫磁碟系統到磁帶、光碟片等介質儲存。當然，使用和維護起來會有一定的麻煩；關於資料存放區以及I/O方面的建議，樓上已經作的簡單說明。

   我的經驗，以前我在處理每天證券交易行情資料時候，是每天讓JOB自己建立一個表，每個表存放當天的資料。每天資料量也是幾百萬的資料，沒有出什麼問題。 最大資料量的資料庫記錄是sqlserver保持的，這點資料量沒問題的，我曾比較過sqlserver和oracle ，兩億五千多萬吧，查詢速度都差不多，缺點：sqlserver在維護索引、建立自動增一的列方面比oracle要差， oracle可以利用分區表簡化對索引的維護，也提高了查詢速度， sqlserver 建立索引也可以，但只能對全表的索引進行整理，比較費勁還有我在sqlserver上建立identity的時候，沒成功，說異常了，機器效能很好的，哎~~~做些實驗吧粗知拙見，大家討論討論

      1.避免IO衝突 a.多檔案組 b.分開存放資料檔案(mdf/ndf)和記錄檔(ldf) c.有條件採用raid5

      2.資料庫最佳化 a.適當最佳化表結構 b.大表建立合理索引包括(索引/分區)視圖、 c.經常做DB備份 在一定條件下設定DB模式為簡單(如果是生產伺服器又不行:( ) d.記錄檔和資料檔案大小應指派一個預設值否則頻繁增長影響效能 有條件分開存放

     3.硬體最佳化記憶體、處理器等等 ..大家補充 分區將資料庫分區可提高其效能並易於維護。通過將一個大表拆分成更小的單個表，只訪問一小部分資料的查詢可以執行得更快，因為需要掃描的資料較少。而且可以更快地執行維護任務（如重建索引或備份表）。 實現分區操作時可以不拆分表，而將表物理地放置在個別的磁碟機上。例如，將表放在某個物理磁碟機上並將相關的表放在與之分離的磁碟機上可提高查詢效能，因為當執行涉及表之間聯結的查詢時，多個磁頭同時讀取資料。可以使用 Microsoft SQL Server 2000 檔案組指定將表放置在哪些磁碟上。

     硬體分區硬體分區將資料庫設計為利用可用的硬體構架。硬體分區的樣本包括： 允許多線程執行的多處理器，使得可以同時執行許多查詢。換句話說，在多處理器上可以同時執行查詢的各個組件，因此使單個查詢的速度更快。例如，查詢內引用的每個表可同時由不同的線程掃描。 RAID（獨立磁碟容錯陣列）裝置允許資料在多個磁碟機中條帶化，使更多的讀/寫磁頭同時讀取資料，因此可以更快地訪問資料。在多個磁碟機中條帶化的表一般比儲存在一個磁碟機上的相同的表掃描速度要快。換句話說，將表與相關的表分開儲存在不同的磁碟機上可以顯著提高聯結那些表的查詢的效能。 水平資料分割水平資料分割將一個表分段為多個表，每個表包含相同數目的列和較少的行。例如，可以將一個包含十億行的表水平資料分割成 12 個表，每個小表代表特定年份內一個月的資料。任何需要特定月份資料的查詢只引用相應月份的表。 具體如何將表進行水平資料分割取決於如何分析資料。

       將表進行分區是為了使查詢引用儘可能少的表。否則，查詢時須使用過多的 UNION 查詢來邏輯合并表，而這會削弱查詢效能。有關查詢水平資料分割的表的更多資訊，請參見視圖使用方式情節。 常用的方法是根據時期/使用對資料進行水平資料分割。例如，一個表可能包含最近五年的資料，但是只定期訪問本年度的資料。在這種情況下，可考慮將資料分區成五個表，每個表只包含一年的資料。 垂直資料分割垂直資料分割將一個表分段為多個表，每個表包含較少的列。垂直資料分割的兩種類型是正常化和行拆分。

      正常化是個標準資料庫進程，該進程從表中刪除冗餘列並將其放到次表中，次表按主鍵與外鍵的關係連結到主表。 行拆分將原始表垂直分成多個只包含較少列的表。拆分的表內的每個邏輯行與其它表內的相同邏輯行匹配。例如，聯結每個拆分的表內的第十行將重新建立原始行。 與水平資料分割一樣，垂直資料分割使查詢得以掃描較少的資料，因此提高查詢效能。

     例如有一個包含七列的表，通常只引用該表的前四列，那麼將該表的後三列拆分到一個單獨的表中可獲得效能收益。 應謹慎考慮垂直資料分割操作，因為分析多個分區內的資料需要有聯結表的查詢，而如果分區非常大將可能影響效能。 請參見 使用分區視圖 使用分區視圖分區視圖允許將大型表中的資料拆分成較小的成員表。根據其中一列中的資料值範圍，將資料在各個成員表之間進行分區。每個成員表的資料範圍都在為分區列指定的 CHECK 條件約束中定義。

     然後定義一個視圖，以使用 UNION ALL 將選定的成員表組合成單個結果集。引用該視圖的 SELECT 語句為分區列指定搜尋條件後，查詢最佳化工具將使用 CHECK 條件約束定義確定哪個成員表包含這些行。 例如，記錄 1998 年銷售額的 sales 表分區成 12 個成員表，每個月是一個成員表。每個成員表在 OrderMonth 列上都定義了約束： CREATE TABLE May1998Sales (OrderID INT PRIMARY KEY, CustomerID INT NOT NULL, OrderDate DATETIME NULL CHECK (DATEPART(yy, OrderDate) = 1998), OrderMonth INT CHECK (OrderMonth = 5), DeliveryDate DATETIME NULL, CHECK (DATEPART(mm, OrderDate) = OrderMonth) ) 填充 May1998Sales 的應用程式必須確保所有行在 OrderMonth 列中均為 5，並且訂購日期指定 1998 年 5 月的某個日期。該表上定義的約束強制實現這一要求。 然後定義一個視圖，以使用 UNION ALL 選定全部 12 個成員表中的資料作為單個結果集： CREATE VIEW Year1998Sales AS SELECT * FROM Jan1998Sales UNION ALL SELECT * FROM Feb1998Sales UNION ALL SELECT * FROM Mar1998Sales UNION ALL SELECT * FROM Apr1998Sales UNION ALL SELECT * FROM May1998Sales UNION ALL SELECT * FROM Jun1998Sales UNION ALL SELECT * FROM Jul1998Sales UNION ALL SELECT * FROM Aug1998Sales UNION ALL SELECT * FROM Sep1998Sales UNION ALL SELECT * FROM Oct1998Sales UNION ALL SELECT * FROM Nov1998Sales UNION ALL SELECT * FROM Dec1998Sales 例如，下面的 SELECT 語句查詢特定月的資訊。 SELECT * FROM Year1998Sales WHERE OrderMonth IN (5,6) AND CustomerID = 64892 SQL Server 查詢最佳化工具識別出此 SELECT 語句中的搜尋條件只引用 May1998Sales 和 Jun1998Sales 表中的行，從而將其搜尋範圍限制在這些表上。

       分區視圖返回正確的結果並不一定非要 CHECK 條件約束。但是，如果未定義 CHECK 條件約束，則查詢最佳化工具必須搜尋所有表，而不是只搜尋符合分區列上的搜尋條件的表。如果不使用 CHECK 條件約束，則視圖的操作方式與帶有 UNION ALL 的任何其它視圖相同。查詢最佳化工具不能對儲存在不同表中的值作出任何假設，也不能跳過對參與視圖定義的表的搜尋。 如果分區視圖所引用的所有成員表都在同一伺服器上，則該視圖是本機資料分割檢視。如果成員表在多台伺服器上，則該視圖是分散式資料分割檢視。分散式資料分割檢視可用於在一組伺服器間分布系統的資料庫處理工作量。 分區視圖使獨立地維護成員表變得更容易。例如，在某個階段結束時： 可以更改當前結果的分區視圖定義以添加最新的階段和除去最早的階段。 可以更改以前結果的分區視圖定義以添加剛從當前結果檢視中除去的階段。

      也可以更新以前的結果檢視以刪除或存檔該視圖所包含的最早階段。 將資料插入到分區視圖中後，就可以使用 sp_executesql 系統預存程序建立 INSERT 語句，該語句帶有在有許多並發使用者的系統中重新使用幾率較高的執行計畫。 建立分區視圖分區視圖在一個或多個伺服器間水平串連一群組成員表中的分區資料，使資料看起來就象來自一個表。Microsoft SQL Server 2000 區分本機資料分割檢視和分散式資料分割檢視。在本機資料分割檢視中，所有的參與表和視圖駐留在同一個 SQL Server 執行個體上。在分散式資料分割檢視中，至少有一個參與表駐留在不同的（遠程）伺服器上。此外，SQL Server 2000 還區分可更新的分區視圖和作為基礎資料表唯讀複本的視圖。

      分散式資料分割檢視可用於實現資料庫伺服器聯合體。聯合體是一組分開管理的伺服器，但它們相互協作分擔系統的處理負荷。這種通過分區資料形成資料庫伺服器聯合體的機制使您能夠擴大一組伺服器，以支援大型的多層 Web 網站的處理需要。有關更多資訊，請參見設計聯盟資料庫伺服器。 在實現分區視圖之前，必須先水平資料分割表。原始表被分成若干個較小的成員表。每個成員表包含與原始表相同數量的列，並且每一列具有與原始表中的相應列同樣的特性（如資料類型、大小、定序）。如果正在建立分散式資料分割檢視，則每個成員表分別位於不同的成員伺服器上。為了獲得最大程度的位置透明度，各個成員伺服器上的成員資料庫的名稱應當是相同的，但不要求非這樣。例如：Server1.CustomerDB、Server2.CustomerDB、Server3.CustomerDB。 成員表設計好後，每個表基於索引值的範圍儲存原始表的一塊水平地區。索引值範圍基於分區列中的資料值。每一成員表中的值範圍通過分區列上的 CHECK 條件約束強制，並且範圍之間不能重疊。例如，不能使一個表的值範圍從 1 到 200000，而另一個表的值範圍從 150000 到 300000，因為這樣將不清楚哪個表包含 150000 與 200000 之間的值。 例如，正在將一個 Customer 表分區成三個表。這些表的 CHECK 條件約束為： -- On Server1: CREATE TABLE Customer_33 …………

 

本文摘自於淩曉web部落