我是如何在SQLServer中處理每天四億三千萬記錄的 轉自網路

標籤：des   blog   http   io   ar   os   使用   for   strong   

首先聲明，我只是個程式員，不是專業的DBA，以下這篇文章是從一個問題的解決過程去寫的，而不是一開始就給大家一個正確的結果，如果文中有不對的地方，請各位元據庫大牛給予指正，以便我能夠更好的處理此次業務。

　項目背景

　　這是給某資料中心做的一個項目，項目難度之大令人髮指，這個項目真正的讓我感覺到了，商場如戰場，而我只是其中的一個小兵，太多的戰術，太多的高層之間的較量，太多的內幕了。具體這個項目的情況，我有空再寫相關的博文出來。

　　這個項目是要求做環境監控，我們暫且把受監控的裝置稱為採集裝置，採集裝置的屬性稱為監控指標。項目要求：系統支援不少於10w個監控指標，每個監控指標的資料更新不大於20秒，儲存延遲不超過120秒。那麼，我們可以通過簡單的計算得出較理想的狀態——要儲存的資料為：每分鐘30w，每個小時1800w，也就是每天4億3千兩百萬。而實際，資料量會比這個大5%左右。（實際上大部分是資訊垃圾，可以通過資料壓縮排行處理的，但是別人就是要搞你，能咋辦）

　　上面是項目要求的指標，我想很多有不少大資料處理經驗的同學都會呲之以鼻，就這麼點？嗯，我也看了很多大資料處理的東西，但是之前沒處理過，看別人是頭頭是道，什麼分布式，什麼讀寫分離，看起來確實很容易解決。但是，問題沒這麼簡單，上面我說了，這是一個非常惡劣的項目，是一個行業惡性競爭典型的項目。

　　1、沒有更多的伺服器，而是這個伺服器除了搭配資料庫、集中採集器(就是資料解析、警示、儲存的程式)，還要支援30w點的北向介面(SNMP)，在程式沒有最佳化之前CPU常年佔用80%以上。因為項目要求要使用雙機熱備，為了省事，減少不必要的麻煩，我們把相關的服務放在一起，以便能夠充分利用HA的特性（外部購買的HA系統)

　　2、系統資料正確性要求極其變態，要求從底層採集系統到最上層的監控系統，一條資料都不能差
我們的系統架構如下，可以看到，其中資料庫壓力非常之大，尤其在LevelA節點：

　　3、硬體設定如下：

　　CPU：英特爾® 至強® 處理器 E5-2609 (4核, 2.40GHz, 10MB, 6.4 GT/s)

　　記憶體：4GB (2x2GB) DDR3 RDIMM Memory, 1333MHz,ECC

　　硬碟：500GB 7200 RPM 3.5‘‘ SATA3 硬碟，Raid5.

　　4、資料庫版本

　　採用的是SQLServer2012標準版，HP提供的正版軟體，缺少很多企業版的NB功能。

　寫入瓶頸

　　首先遇到的第一個攔路虎就是，我們發現現有的程式下，SQLServer根本處理不了這麼多的資料量，具體情況是怎樣的呢?

　　我們的儲存結構

　　一般為了儲存大量的曆史資料，我們都會進行一個物理的分表，否則每天上百萬條的記錄，一年下來就是幾億條。因此，原來我們的表結構是這樣的：

CREATE TABLE [dbo].[His20140822]([No] [bigint] IDENTITY(1,1) NOT NULL,[Dtime] [datetime] NOT NULL,[MgrObjId] [varchar](36) NOT NULL,[Id] [varchar](50) NOT NULL,[Value] [varchar](50) NOT NULL, CONSTRAINT [PK_His20140822] PRIMARY KEY CLUSTERED ([No] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

　　No作為唯一的標識、採集裝置Id(Guid)、監控指標Id(varchar(50))、記錄時間、記錄值。並以採集裝置Id和監控指標Id作為索引，以便快速尋找。

　　批量寫入

　　寫入當時是用BulKCopy，沒錯，就是它，號稱寫入百萬條記錄都是秒級的

    public static int BatchInert(string connectionString, string desTable, DataTable dt, int batchSize = 500)    {        using (var sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction)        {            BulkCopyTimeout = 300,            NotifyAfter = dt.Rows.Count,            BatchSize = batchSize,            DestinationTableName = desTable        })        {            foreach (DataColumn column in dt.Columns)                sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName);            sbc.WriteToServer(dt);        }        return dt.Rows.Count;    }

　　存在什麼問題？

　　上面的架構，在每天4千萬的資料都是OK的。但是，調整為上述背景下的配置時，集中監控程式就記憶體溢出了，分析得知，接收的太多資料，放在了記憶體中，但是沒有來得及寫入到資料庫中，最終導致了產生的資料大於消費的資料，導致記憶體溢出，程式無法工作。

　　瓶頸到底在哪裡？

　　是因為RAID磁碟的問題？是資料結構的問題？是硬體的問題？是SQLServer版本的問題？是沒有分區表的問題？還是程式的問題？

　　當時時間只有一個星期，一個星期搞不好，項目監管就要我們滾蛋了，於是，有了連續工作48小時的壯舉，有了到處打電話求人的抓雞……

　　但是，這個時候需要的是冷靜，再冷靜……SQLServer版本？硬體？目前都不大可能換的。RAID磁碟陣列，應該不是。那麼到底是什麼，真TM的冷靜不下來。

　　大家可能體會不到現場那種緊張的氣氛，其實過了這麼久，我自己也都很難再回到那種情境。但是可以這麼說，或許我們現在有了各種方法，或者處於局外人我們有更多思考，但是當一個項目壓迫你快到放棄的時候，你那時的想法、考慮在現場環境因素的制約下，都可能出現重大的偏差。有可能讓你快速的思考，也有可能思維停滯。有些同事在這種高壓的環境下，甚至出現了更多的低級錯誤，思維已經完全亂了，效率更低了……36小時沒有合眼，或者只在工地上（下雨天到處都是泥巴，幹了的話到時都是泥灰）眯兩三個小時，然後繼續幹，連續這麼一個星期！或者還要繼續！

　　很多人給了很多想法，但是好像有用，又好像沒用。等等，為什麼是“好像有用，又好像沒用”？我隱隱約約中，好像抓住了一絲方向，到底是什嗎？對了，驗證，我們現在是跑在現場環境下，之前沒有問題，不代表現在的壓力下沒有問題，要在一個大型系統中分析這麼個小功能，影響太大了，我們應該分解它。是的，是“單元測試”，就是單個方法的測試，我們需要驗證每個函數，每個獨立的步驟到底耗時在哪裡？

　　逐步測實驗證系統瓶頸

　　修改BulkCopy的參數

　　首先，我想到的是，修噶BulkCopy的各項參數，BulkCopyTimeout、BatchSize，不斷的測試調整，結果總是在某個範圍波動，實際並沒有影響。或許會影響一些CPU計數，但是遠遠沒有達到我的期望，寫入的速度還是在5秒1w~2w波動，遠遠達不到要求20秒內要寫20w的記錄。

　　按採集裝置儲存

　　是的，上述結構按每個指標每個值為一條記錄，是不是太多的浪費？那麼按採集裝置+採集時間作為一條記錄是否可行?問題是，怎麼解決不同採集裝置屬性不一樣的問題？這時，一個同事發揮才能了，監控指標+監控值可以按XML格式儲存。哇，還能這樣？查詢呢，可以用for XML這種形式。

　　於是有了這種結構：No、MgrObjId、Dtime、XMLData

　　結果驗證，比上面的稍微好點，但是不是太明顯。

　　資料表分區???

　　那個時候還沒有學會這個技能，看了下網上的文章，好像挺複雜的，時間不多了，不敢嘗試。

　　停止其他程式

　　我知道這個肯定是不行的，因為軟體、硬體的架構暫時沒法修改。但是我希望驗證是不是這些因素影響的。結果發現，提示確實明顯，但是還是沒有達到要求。

　　難道是SQLServer的瓶頸?

　　沒轍了，難道這就是SQLServer的瓶頸？上網查了下相關的資料，可能是IO的瓶頸，尼瑪，還能怎麼辦，要升級伺服器，要更換資料庫了嗎，但是，項目方給嗎？

　　等等，好像還有個東西，索引，對索引！索引的存在會影響插入、更新

　　去掉索引

　　是的，去掉索引之後查詢肯定慢，但是我必須先驗證去掉索引是否會加快寫入。如果果斷把MgrObjId和Id兩個欄位的索引去掉。

　　運行，奇蹟出現了，每次寫入10w條記錄，在7~9秒內完全可以寫入，這樣就達到了系統的要求。

　　查詢怎麼解決?

　　一個表一天要4億多的記錄，這是不可能查詢的，在沒有索引的情況下。怎麼辦！？我又想到了我們的老辦法，物理分表。是的，原來我們按天分表，那麼我們現在按小時分表。那麼24個表，每個表只需儲存1800w條記錄左右。

　　然後查詢，一個屬性在一個小時或者幾個小時的記錄。結果是：慢！慢！！慢！！！去掉索引的情況下查詢1000多萬的記錄根本是不可想象的。還能怎麼辦？

　　繼續分表，我想到了，我們還可以按底層的採集器繼續分表，因為採集裝置在不同的採集器中是不同的，那麼我們查詢曆史曲線時，只有查單個指標的曆史曲線，那麼這樣就可以分散在不同的表中了。

　　說幹就幹，結果，通過按10個採集嵌入式並按24小時分表，每天產生240張表(曆史表名類似這樣：His_001_2014112615)，終於把一天寫入4億多條記錄並支援簡單的查詢這個問題給解決掉了！！！

　查詢最佳化

　　在上述問題解決之後，這個項目的痛點已經解決了一半，項目監管也不好意思過來找茬，不知道是出於什麼樣的戰術安排吧。

　　過了很長一段時間，到現在快年底了，問題又來了，就是要拖死你讓你在年底不能驗收其他項目。

　　這次要求是這樣的：因為上述是類比10w個監控指標，而現在實際上線了，卻只有5w個左右的裝置。那麼這個明顯是不能達到標書要求的，不能驗收。那麼怎麼辦呢？這些聰明的人就想，既然監控指標減半，那麼我們把時間也減半，不就達到了嗎：就是說按現在5w的裝置，那你要10s之內入庫儲存。我勒個去啊，按你這個邏輯，我們如果只有500個監控指標，豈不是要在0.1秒內入庫？你不考慮下那些受監控裝置的感想嗎？

　　但是別人要玩你，你能怎麼辦？接招唄。結果把時間降到10秒之後，問題來了，大家仔細分析上面邏輯可以知道，分表是按採集器分的，現在採集器減少，但是數量增加了，發生什麼事情呢，寫入可以支援，但是，每張表的記錄接近了400w，有些採集裝置監控指標多的，要接近600w，怎麼破？

　　於是技術相關人員開會討論相關的舉措。

　　在不加索引的情況下怎麼最佳化查詢？

　　有同事提出了，where子句的順序，會影響查詢的結果，因為按你刷選之後的結果再處理，可以先刷選出一部分資料，然後繼續進行下一個條件的過濾。聽起來好像很有道理，但是SQLServer查詢分析器不會自動最佳化嗎？原諒我是個小白，我也是感覺而已，感覺應該跟VS的編譯器一樣，應該會自動最佳化吧。

　　具體怎樣，還是要用事實來說話：

　　結果同事修改了用戶端之後，測試反饋，有較大的改善。我查看了代碼：

　　難道真的有這麼大的影響？等等，是不是忘記清空緩衝，造成了假象？

　　於是讓同事執行下述語句以便得出更多的資訊：

--最佳化之前DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSSET STATISTICS IO ONselect Dtime,Value from dbo.his20140825 WHERE  Dtime>=‘‘ AND Dtime<=‘‘ AND MgrObjId=‘‘ AND Id=‘‘SET STATISTICS IO OFF--最佳化之後DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSSET STATISTICS IO ONselect Dtime,Value from dbo.his20140825 WHERE MgrObjId=‘‘ AND Id=‘‘ AND Dtime>=‘‘ AND Dtime<=‘‘SET STATISTICS IO OFF

　　結果如下：

　　最佳化之前反而更好了？

　　仔細查看IO資料，發現，預讀是一樣的，就是說我們要查詢的資料記錄都是一致的，物理讀、表掃描也是一直的。而邏輯讀取稍有區別，應該是快取命中數導致的。也就是說，在不建立索引的情況下，where子句的條件順序，對查詢結果最佳化作用不明顯。

　　那麼，就只能通過索引的辦法了。

　　建立索引的嘗試

　　建立索引不是簡單的事情，是需要瞭解一些基本的知識的，在這個過程中，我走了不少彎路，最終才把索引建立起來。

　　下面的實驗基於以下記錄總數做的驗證：

　　按單個欄位建立索引

　　這個想法，主要是受我建立資料結構影響的，我記憶體中的資料結構為：Dictionary<MgrObjId,Dictionary<Id,Property>>。我以為先建立MgrObjId的索引，再建立Id的索引，SQLServer查詢時，就會更快。

　　先按MgrObjId建立索引，索引大小為550M，耗時5分25秒。結果，如的預估計劃一樣，根本沒有起作用，反而更慢了。

　　按多個條件建立索引

　　OK，既然上面的不行，那麼我們按多個條件建立索引又如何？CREATE NONCLUSTERED INDEX Idx_His20141008 ON dbo.his20141008(MgrObjId,Id,Dtime)

　　結果，查詢速度確實提高了一倍：

　　等等，難道這就是索引的好處？花費7分25秒，用1.1G的空間換取來的就是這些？肯定是有什麼地方不對了，於是開始翻查資料，查看一些相關書籍，最終，有了加大的進展。

　　正確的建立索引

　　首先，我們需要明白幾個索引的要點：

• 索引之後，按索引欄位重複最少的來排序，會達到最優的效果。以我們的表來說，如果建立了No的叢集索引，把No放在where子句的第一位是最佳的，其次是Id，然後是MgrObjId，最後是時間，時間索引如果表是一個小時的，最好不要用
• where子句的順序決定了查詢分析器是否使用索引來查詢。比如建立了MgrObjId和Id的索引，那麼where MgrObjId=‘‘ and Id=‘‘ and Dtime=‘‘就會採用索引尋找，而where Dtime=‘‘ and MgrObjId=‘‘ and Id=‘‘則不一定會採用索引尋找。
• 把非索引列的結果列放在包含列中。因為我們條件是MgrObjId和Id以及Dtime，因此返回結果中只需包含Dtime和Value即可，因此把Dtime和Value放在包含列中，返回的索引結果就有這個值，不用再查物理表，可以達到最優的速度。

　　跟上述幾點原則，我們建立以下的索引：CREATE NONCLUSTERED INDEX Idx_His20141008 ON dbo.his20141008(MgrObjId,Id) INCLUDE(Value,Dtime)

　　耗費時間為：6分多鐘，索引大小為903M。

　　我們看看預估計劃：

　　可以看到，這裡完全使用了索引，沒有額外的消耗。而實際執行的結果，1秒都不到，竟然不用一秒就在1100w的記錄中把結果篩選了出來！！帥呆了！！

　　怎麼應用索引?

　　既然寫入完成了、讀取完成了，怎麼結合呢？我們可以把一個小時之前的資料建立索引，當前一個小時的資料就不建立索引。也就是，不要再建立表的時候建立索引！！

　　能怎麼最佳化

　　可以嘗試讀寫分離，寫兩個庫，一個是即時庫，一個是唯讀庫。一個小時內的資料查詢即時庫，一個小時之前的資料查詢唯讀庫；唯讀庫定時儲存，然後建立索引；超過一個星期的資料，進行分析處理再儲存。這樣，無論查詢什麼時間段的資料，都能夠正確處理了——一個小時之內的查詢即時庫，一個小時到一個星期內的查詢唯讀庫，一個星期之前的查詢報表庫。

　　如果不需要物理分表，則在唯讀庫中，定時重建索引即可。

　總結

　　如何在SQLServer中處理億萬層級的資料(曆史資料)，可以按以下方面進行：

• 去掉表的所有索引
• 用SqlBulkCopy進行插入
• 分表或者分區，減少每個表的資料總量
• 在某個表完全寫完之後再建立索引
• 正確的指定索引欄位
• 把需要用到的欄位放到包含索引中(在返回的索引中就包含了一切)
• 查詢的時候只返回所需的欄位

原文地址：http://www.cnblogs.com/marvin/p/HowCanIHandleBigDataBySQLServer.html

我是如何在SQLServer中處理每天四億三千萬記錄的 轉自網路