共計 6650 個字符，預計需要花費 17 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

丸趣 TV 小編給大家分享一下在 SQLServer 中如何處理千萬單位記錄，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

項目背景

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

這個項目是要求做環(huán)境監(jiān)控，我們暫且把受監(jiān)控的設備稱為采集設備，采集設備的屬性稱為監(jiān)控指標。項目要求：系統(tǒng)支持不少于 10w 個監(jiān)控指標，每個監(jiān)控指標的數據更新不大于 20 秒，存儲延遲不超過 120 秒。那么，我們可以通過簡單的計算得出較理想的狀態(tài)——要存儲的數據為：每分鐘 30w，每個小時 1800w，也就是每天 4 億 3 千兩百萬。而實際，數據量會比這個大 5% 左右。（實際上大部分是信息垃圾，可以通過數據壓縮進行處理的，但是別人就是要搞你，能咋辦）

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

沒有更多的服務器，而是這個服務器除了搭配數據庫、集中采集器(就是數據解析、告警、存儲的程序)，還要支持 30w 點的北向接口(SNMP)，在程序沒有優(yōu)化之前 CPU 常年占用 80% 以上。因為項目要求要使用雙機熱備，為了省事，減少不必要的麻煩，我們把相關的服務放在一起，以便能夠充分利用 HA 的特性（外部購買的 HA 系統(tǒng))

系統(tǒng)數據正確性要求極其變態(tài)，要求從底層采集系統(tǒng)到最上層的監(jiān)控系統(tǒng)，一條數據都不能差
我們的系統(tǒng)架構如下，可以看到，其中數據庫壓力非常之大，尤其在 LevelA 節(jié)點：

硬件配置如下：
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.

數據庫版本
采用的是 SQLServer2012 標準版，HP 提供的正版軟件，缺少很多企業(yè)版的 NB 功能。

推薦一下自己的 linuxC/C++ 交流群：973961276！整理了一些個人覺得比較好的學習書籍、視頻資料以及大廠面經視頻共享在群文件里面，有需要的小伙伴可以自行添加哦！~

寫入瓶頸

首先遇到的第一個攔路虎就是，我們發(fā)現(xiàn)現(xiàn)有的程序下，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)、監(jiān)控指標 Id(varchar(50))、記錄時間、記錄值。并以采集設備 Id 和監(jiān)控指標 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 的。但是，調整為上述背景下的配置時，集中監(jiān)控程序就內存溢出了，分析得知，接收的太多數據，放在了內存中，但是沒有來得及寫入到數據庫中，最終導致了生成的數據大于消費的數據，導致內存溢出，程序無法工作。

瓶頸到底在哪里？

是因為 RAID 磁盤的問題？是數據結構的問題？是硬件的問題？是 SQLServer 版本的問題？是沒有分區(qū)表的問題？還是程序的問題？

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

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

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

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

逐步測試驗證系統(tǒng)瓶頸

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

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

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

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

數據表分區(qū)???
那個時候還沒有學會這個技能，看了下網上的文章，好像挺復雜的，時間不多了，不敢嘗試。

停止其他程序
我知道這個肯定是不行的，因為軟件、硬件的架構暫時沒法修改。但是我希望驗證是不是這些因素影響的。結果發(fā)現(xiàn)，提示確實明顯，但是還是沒有達到要求。

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

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

去掉索引

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

運行，奇跡出現(xiàn)了，每次寫入 10w 條記錄，在 7~9 秒內完全可以寫入，這樣就達到了系統(tǒng)的要求。

查詢怎么解決?

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

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

繼續(xù)分表，我想到了，我們還可以按底層的采集器繼續(xù)分表，因為采集設備在不同的采集器中是不同的，那么我們查詢歷史曲線時，只有查單個指標的歷史曲線，那么這樣就可以分散在不同的表中了。

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

查詢優(yōu)化

在上述問題解決之后，這個項目的難點已經解決了一半，項目監(jiān)管也不好意思過來找茬，不知道是出于什么樣的戰(zhàn)術安排吧。

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

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

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

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

在不加索引的情況下怎么優(yōu)化查詢？

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

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

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

難道真的有這么大的影響？等等，是不是忘記清空緩存，造成了假象？
于是讓同事執(zhí)行下述語句以便得出更多的信息：

-- 優(yōu)化之前 DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSSET STATISTICS IO ONselect Dtime,Value from dbo.his20140825 WHERE Dtime =  AND Dtime =  AND MgrObjId=  AND Id= SET STATISTICS IO OFF-- 優(yōu)化之后 DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSSET STATISTICS IO ONselect Dtime,Value from dbo.his20140825 WHERE MgrObjId=  AND Id=  AND Dtime =  AND Dtime = SET STATISTICS IO OFF

結果如下：

優(yōu)化之前反而更好了？

仔細查看 IO 數據，發(fā)現(xiàn)，預讀是一樣的，就是說我們要查詢的數據記錄都是一致的，物理讀、表掃描也是一直的。而邏輯讀取稍有區(qū)別，應該是緩存命中數導致的。也就是說，在不建立索引的情況下，where 子句的條件順序，對查詢結果優(yōu)化作用不明顯。

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

建立索引的嘗試

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

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

按單個字段建立索引
這個想法，主要是受我建立數據結構影響的，我內存中的數據結構為：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 的空間換取來的就是這些？肯定是有什么地方不對了，于是開始翻查資料，查看一些相關書籍，最終，有了較大的進展。

正確的建立索引

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

索引之后，按索引字段重復最少的來排序，會達到最優(yōu)的效果。以我們的表來說，如果建立了 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 放在包含列中，返回的索引結果就有這個值，不用再查物理表，可以達到最優(yōu)的速度。

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

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

我們看看預估計劃：

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

怎么應用索引?

既然寫入完成了、讀取完成了，怎么結合呢？我們可以把一個小時之前的數據建立索引，當前一個小時的數據就不建立索引。也就是，不要再創(chuàng)建表的時候建立索引！！

還能怎么優(yōu)化

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

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

看完了這篇文章，相信你對“在 SQLServer 中如何處理千萬單位記錄”有了一定的了解，如果想了解更多相關知識，歡迎關注丸趣 TV 行業(yè)資訊頻道，感謝各位的閱讀！