小編給大家分享一下在SQLServer中如何處理千萬(wàn)單位記錄，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！
這是給某數(shù)據(jù)中心做的一個(gè)項(xiàng)目，項(xiàng)目難度之大令人發(fā)指，這個(gè)項(xiàng)目真正的讓我感覺(jué)到了，商場(chǎng)如戰(zhàn)場(chǎng)，而我只是其中的一個(gè)小兵，太多的戰(zhàn)術(shù)，太多的高層之間的較量，太多的內(nèi)幕了。具體這個(gè)項(xiàng)目的情況，我有空再寫相關(guān)的博文出來(lái)。
這個(gè)項(xiàng)目是要求做環(huán)境監(jiān)控，我們暫且把受監(jiān)控的設(shè)備稱為采集設(shè)備，采集設(shè)備的屬性稱為監(jiān)控指標(biāo)。項(xiàng)目要求：系統(tǒng)支持不少于10w個(gè)監(jiān)控指標(biāo)，每個(gè)監(jiān)控指標(biāo)的數(shù)據(jù)更新不大于20秒，存儲(chǔ)延遲不超過(guò)120秒。那么，我們可以通過(guò)簡(jiǎn)單的計(jì)算得出較理想的狀態(tài)——要存儲(chǔ)的數(shù)據(jù)為：每分鐘30w，每個(gè)小時(shí)1800w，也就是每天4億3千兩百萬(wàn)。而實(shí)際，數(shù)據(jù)量會(huì)比這個(gè)大5%左右。（實(shí)際上大部分是信息垃圾，可以通過(guò)數(shù)據(jù)壓縮進(jìn)行處理的，但是別人就是要搞你，能咋辦）
上面是項(xiàng)目要求的指標(biāo)，我想很多有不少大數(shù)據(jù)處理經(jīng)驗(yàn)的同學(xué)都會(huì)呲之以鼻，就這么點(diǎn)？嗯，我也看了很多大數(shù)據(jù)處理的東西，但是之前沒(méi)處理過(guò)，看別人是頭頭是道，什么分布式，什么讀寫分離，看起來(lái)確實(shí)很容易解決。但是，問(wèn)題沒(méi)這么簡(jiǎn)單，上面我說(shuō)了，這是一個(gè)非常惡劣的項(xiàng)目，是一個(gè)行業(yè)惡性競(jìng)爭(zhēng)典型的項(xiàng)目。
沒(méi)有更多的服務(wù)器，而是這個(gè)服務(wù)器除了搭配數(shù)據(jù)庫(kù)、集中采集器(就是數(shù)據(jù)解析、告警、存儲(chǔ)的程序)，還要支持30w點(diǎn)的北向接口(SNMP)，在程序沒(méi)有優(yōu)化之前CPU常年占用80%以上。因?yàn)轫?xiàng)目要求要使用雙機(jī)熱備，為了省事，減少不必要的麻煩，我們把相關(guān)的服務(wù)放在一起，以便能夠充分利用HA的特性（外部購(gòu)買的HA系統(tǒng))
系統(tǒng)數(shù)據(jù)正確性要求極其變態(tài)，要求從底層采集系統(tǒng)到最上層的監(jiān)控系統(tǒng)，一條數(shù)據(jù)都不能差
我們的系統(tǒng)架構(gòu)如下，可以看到，其中數(shù)據(jù)庫(kù)壓力非常之大，尤其在LevelA節(jié)點(diǎn)：
硬件配置如下：
CPU：英特爾? 至強(qiáng)? 處理器 E5-2609 (4核, 2.40GHz, 10MB, 6.4 GT/s)
內(nèi)存：4GB (2x2GB) DDR3 RDIMM Memory, 1333MHz,ECC
硬盤：500GB 7200 RPM 3.5’’ SATA3 硬盤，Raid5.
數(shù)據(jù)庫(kù)版本
采用的是SQLServer2012標(biāo)準(zhǔn)版，HP提供的正版軟件，缺少很多企業(yè)版的NB功能。
推薦一下自己的linuxC/C++交流群：973961276！整理了一些個(gè)人覺(jué)得比較好的學(xué)習(xí)書(shū)籍、視頻資料以及大廠面經(jīng)視頻共享在群文件里面，有需要的小伙伴可以自行添加哦！~
首先遇到的第一個(gè)攔路虎就是，我們發(fā)現(xiàn)現(xiàn)有的程序下，SQLServer根本處理不了這么多的數(shù)據(jù)量，具體情況是怎樣的呢?
一般為了存儲(chǔ)大量的歷史數(shù)據(jù)，我們都會(huì)進(jìn)行一個(gè)物理的分表，否則每天上百萬(wàn)條的記錄，一年下來(lái)就是幾億條。因此，原來(lái)我們的表結(jié)構(gòu)是這樣的：
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作為的標(biāo)識(shí)、采集設(shè)備Id(Guid)、監(jiān)控指標(biāo)Id(varchar(50))、記錄時(shí)間、記錄值。并以采集設(shè)備Id和監(jiān)控指標(biāo)Id作為索引，以便快速查找。
寫入當(dāng)時(shí)是用BulKCopy，沒(méi)錯(cuò)，就是它，號(hào)稱寫入百萬(wàn)條記錄都是秒級(jí)的
重慶分公司
重慶分公司
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;
}上面的架構(gòu)，在每天4千萬(wàn)的數(shù)據(jù)都是OK的。但是，調(diào)整為上述背景下的配置時(shí)，集中監(jiān)控程序就內(nèi)存溢出了，分析得知，接收的太多數(shù)據(jù)，放在了內(nèi)存中，但是沒(méi)有來(lái)得及寫入到數(shù)據(jù)庫(kù)中，最終導(dǎo)致了生成的數(shù)據(jù)大于消費(fèi)的數(shù)據(jù)，導(dǎo)致內(nèi)存溢出，程序無(wú)法工作。
是因?yàn)镽AID磁盤的問(wèn)題？是數(shù)據(jù)結(jié)構(gòu)的問(wèn)題？是硬件的問(wèn)題？是SQLServer版本的問(wèn)題？是沒(méi)有分區(qū)表的問(wèn)題？還是程序的問(wèn)題？
當(dāng)時(shí)時(shí)間只有一個(gè)星期，一個(gè)星期搞不好，項(xiàng)目監(jiān)管就要我們滾蛋了，于是，有了連續(xù)工作48小時(shí)的壯舉，有了到處打電話求人的抓雞……
但是，這個(gè)時(shí)候需要的是冷靜，再冷靜……SQLServer版本？硬件？目前都不大可能換的。RAID磁盤陣列，應(yīng)該不是。那么到底是什么，真TM的冷靜不下來(lái)。
大家可能體會(huì)不到現(xiàn)場(chǎng)那種緊張的氣氛，其實(shí)過(guò)了這么久，我自己也都很難再回到那種情境。但是可以這么說(shuō)，或許我們現(xiàn)在有了各種方法，或者處于局外人我們有更多思考，但是當(dāng)一個(gè)項(xiàng)目壓迫你快到放棄的時(shí)候，你那時(shí)的想法、考慮在現(xiàn)場(chǎng)環(huán)境因素的制約下，都可能出現(xiàn)重大的偏差。有可能讓你快速的思考，也有可能思維停滯。有些同事在這種高壓的環(huán)境下，甚至出現(xiàn)了更多的低級(jí)錯(cuò)誤，思維已經(jīng)完全亂了，效率更低了……36小時(shí)沒(méi)有合眼，或者只在工地上（下雨天到處都是泥巴，干了的話到時(shí)都是泥灰）瞇兩三個(gè)小時(shí)，然后繼續(xù)干，連續(xù)這么一個(gè)星期！或者還要繼續(xù)！
很多人給了很多想法，但是好像有用，又好像沒(méi)用。等等，為什么是“好像有用，又好像沒(méi)用”？我隱隱約約中，好像抓住了一絲方向，到底是什么？對(duì)了，驗(yàn)證，我們現(xiàn)在是跑在現(xiàn)場(chǎng)環(huán)境下，之前沒(méi)有問(wèn)題，不代表現(xiàn)在的壓力下沒(méi)有問(wèn)題，要在一個(gè)大型系統(tǒng)中分析這么個(gè)小功能，影響太大了，我們應(yīng)該分解它。是的，是“單元測(cè)試”，就是單個(gè)方法的測(cè)試，我們需要驗(yàn)證每個(gè)函數(shù)，每個(gè)獨(dú)立的步驟到底耗時(shí)在哪里？
修改BulkCopy的參數(shù)
首先，我想到的是，修噶BulkCopy的各項(xiàng)參數(shù)，BulkCopyTimeout、BatchSize，不斷的測(cè)試調(diào)整，結(jié)果總是在某個(gè)范圍波動(dòng)，實(shí)際并沒(méi)有影響?；蛟S會(huì)影響一些CPU計(jì)數(shù)，但是遠(yuǎn)遠(yuǎn)沒(méi)有達(dá)到我的期望，寫入的速度還是在5秒1w~2w波動(dòng)，遠(yuǎn)遠(yuǎn)達(dá)不到要求20秒內(nèi)要寫20w的記錄。
按采集設(shè)備存儲(chǔ)
是的，上述結(jié)構(gòu)按每個(gè)指標(biāo)每個(gè)值為一條記錄，是不是太多的浪費(fèi)？那么按采集設(shè)備+采集時(shí)間作為一條記錄是否可行?問(wèn)題是，怎么解決不同采集設(shè)備屬性不一樣的問(wèn)題？這時(shí)，一個(gè)同事發(fā)揮才能了，監(jiān)控指標(biāo)+監(jiān)控值可以按XML格式存儲(chǔ)。哇，還能這樣？查詢呢，可以用for XML這種形式。
于是有了這種結(jié)構(gòu)：No、MgrObjId、Dtime、XMLData
結(jié)果驗(yàn)證，比上面的稍微好點(diǎn)，但是不是太明顯。
數(shù)據(jù)表分區(qū)???
那個(gè)時(shí)候還沒(méi)有學(xué)會(huì)這個(gè)技能，看了下網(wǎng)上的文章，好像挺復(fù)雜的，時(shí)間不多了，不敢嘗試。
停止其他程序
我知道這個(gè)肯定是不行的，因?yàn)檐浖?、硬件的架?gòu)暫時(shí)沒(méi)法修改。但是我希望驗(yàn)證是不是這些因素影響的。結(jié)果發(fā)現(xiàn)，提示確實(shí)明顯，但是還是沒(méi)有達(dá)到要求。
難道是SQLServer的瓶頸?
沒(méi)轍了，難道這就是SQLServer的瓶頸？上網(wǎng)查了下相關(guān)的資料，可能是IO的瓶頸，尼瑪，還能怎么辦，要升級(jí)服務(wù)器，要更換數(shù)據(jù)庫(kù)了嗎，但是，項(xiàng)目方給嗎？
等等，好像還有個(gè)東西，索引，對(duì)索引！索引的存在會(huì)影響插入、更新
是的，去掉索引之后查詢肯定慢，但是我必須先驗(yàn)證去掉索引是否會(huì)加快寫入。如果果斷把MgrObjId和Id兩個(gè)字段的索引去掉。
運(yùn)行，奇跡出現(xiàn)了，每次寫入10w條記錄，在7~9秒內(nèi)完全可以寫入，這樣就達(dá)到了系統(tǒng)的要求。
一個(gè)表一天要4億多的記錄，這是不可能查詢的，在沒(méi)有索引的情況下。怎么辦??？我又想到了我們的老辦法，物理分表。是的，原來(lái)我們按天分表，那么我們現(xiàn)在按小時(shí)分表。那么24個(gè)表，每個(gè)表只需存儲(chǔ)1800w條記錄左右。
然后查詢，一個(gè)屬性在一個(gè)小時(shí)或者幾個(gè)小時(shí)的歷史記錄。結(jié)果是：慢！慢??！慢?。?！去掉索引的情況下查詢1000多萬(wàn)的記錄根本是不可想象的。還能怎么辦？
繼續(xù)分表，我想到了，我們還可以按底層的采集器繼續(xù)分表，因?yàn)椴杉O(shè)備在不同的采集器中是不同的，那么我們查詢歷史曲線時(shí)，只有查單個(gè)指標(biāo)的歷史曲線，那么這樣就可以分散在不同的表中了。
說(shuō)干就干，結(jié)果，通過(guò)按10個(gè)采集嵌入式并按24小時(shí)分表，每天生成240張表(歷史表名類似這樣：His_001_2014112615)，終于把一天寫入4億多條記錄并支持簡(jiǎn)單的查詢這個(gè)問(wèn)題給解決掉了?。?！
在上述問(wèn)題解決之后，這個(gè)項(xiàng)目的難點(diǎn)已經(jīng)解決了一半，項(xiàng)目監(jiān)管也不好意思過(guò)來(lái)找茬，不知道是出于什么樣的戰(zhàn)術(shù)安排吧。
過(guò)了很長(zhǎng)一段時(shí)間，到現(xiàn)在快年底了，問(wèn)題又來(lái)了，就是要拖死你讓你在年底不能驗(yàn)收其他項(xiàng)目。
這次要求是這樣的：因?yàn)樯鲜鍪悄M10w個(gè)監(jiān)控指標(biāo)，而現(xiàn)在實(shí)際上線了，卻只有5w個(gè)左右的設(shè)備。那么這個(gè)明顯是不能達(dá)到標(biāo)書(shū)要求的，不能驗(yàn)收。那么怎么辦呢？這些聰明的人就想，既然監(jiān)控指標(biāo)減半，那么我們把時(shí)間也減半，不就達(dá)到了嗎：就是說(shuō)按現(xiàn)在5w的設(shè)備，那你要10s之內(nèi)入庫(kù)存儲(chǔ)。我勒個(gè)去啊，按你這個(gè)邏輯，我們?nèi)绻挥?00個(gè)監(jiān)控指標(biāo)，豈不是要在0.1秒內(nèi)入庫(kù)？你不考慮下那些受監(jiān)控設(shè)備的感想嗎？
但是別人要玩你，你能怎么辦？接招唄。結(jié)果把時(shí)間降到10秒之后，問(wèn)題來(lái)了，大家仔細(xì)分析上面邏輯可以知道，分表是按采集器分的，現(xiàn)在采集器減少，但是數(shù)量增加了，發(fā)生什么事情呢，寫入可以支持，但是，每張表的記錄接近了400w，有些采集設(shè)備監(jiān)控指標(biāo)多的，要接近600w，怎么破？
于是技術(shù)相關(guān)人員開(kāi)會(huì)討論相關(guān)的舉措。
有同事提出了，where子句的順序，會(huì)影響查詢的結(jié)果，因?yàn)榘茨闼⑦x之后的結(jié)果再處理，可以先刷選出一部分?jǐn)?shù)據(jù)，然后繼續(xù)進(jìn)行下一個(gè)條件的過(guò)濾。聽(tīng)起來(lái)好像很有道理，但是SQLServer查詢分析器不會(huì)自動(dòng)優(yōu)化嗎？原諒我是個(gè)小白，我也是感覺(jué)而已，感覺(jué)應(yīng)該跟VS的編譯器一樣，應(yīng)該會(huì)自動(dòng)優(yōu)化吧。
具體怎樣，還是要用事實(shí)來(lái)說(shuō)話：
結(jié)果同事修改了客戶端之后，測(cè)試反饋，有較大的改善。我查看了代碼：
難道真的有這么大的影響？等等，是不是忘記清空緩存，造成了假象？
于是讓同事執(zhí)行下述語(yǔ)句以便得出更多的信息：
--優(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
結(jié)果如下：
優(yōu)化之前反而更好了？
仔細(xì)查看IO數(shù)據(jù)，發(fā)現(xiàn)，預(yù)讀是一樣的，就是說(shuō)我們要查詢的數(shù)據(jù)記錄都是一致的，物理讀、表掃描也是一直的。而邏輯讀取稍有區(qū)別，應(yīng)該是緩存命中數(shù)導(dǎo)致的。也就是說(shuō)，在不建立索引的情況下，where子句的條件順序，對(duì)查詢結(jié)果優(yōu)化作用不明顯。
那么，就只能通過(guò)索引的辦法了。
建立索引不是簡(jiǎn)單的事情，是需要了解一些基本的知識(shí)的，在這個(gè)過(guò)程中，我走了不少?gòu)澛?，最終才把索引建立起來(lái)。
下面的實(shí)驗(yàn)基于以下記錄總數(shù)做的驗(yàn)證：
按單個(gè)字段建立索引
這個(gè)想法，主要是受我建立數(shù)據(jù)結(jié)構(gòu)影響的，我內(nèi)存中的數(shù)據(jù)結(jié)構(gòu)為：Dictionary。我以為先建立MgrObjId的索引，再建立Id的索引，SQLServer查詢時(shí)，就會(huì)更快。
先按MgrObjId建立索引，索引大小為550M，耗時(shí)5分25秒。結(jié)果，如上圖的預(yù)估計(jì)劃一樣，根本沒(méi)有起作用，反而更慢了。
按多個(gè)條件建立索引
OK，既然上面的不行，那么我們按多個(gè)條件建立索引又如何？CREATE NONCLUSTERED INDEX Idx_His20141008 ON dbo.his20141008(MgrObjId,Id,Dtime)
結(jié)果，查詢速度確實(shí)提高了一倍：
等等，難道這就是索引的好處？花費(fèi)7分25秒，用1.1G的空間換取來(lái)的就是這些？肯定是有什么地方不對(duì)了，于是開(kāi)始翻查資料，查看一些相關(guān)書(shū)籍，最終，有了較大的進(jìn)展。
首先，我們需要明白幾個(gè)索引的要點(diǎn)：
索引之后，按索引字段重復(fù)最少的來(lái)排序，會(huì)達(dá)到最優(yōu)的效果。以我們的表來(lái)說(shuō)，如果建立了No的聚集索引，把No放在where子句的第一位是很好的，其次是Id，然后是MgrObjId，最后是時(shí)間，時(shí)間索引如果表是一個(gè)小時(shí)的，好不要用
where子句的順序決定了查詢分析器是否使用索引來(lái)查詢。比如建立了MgrObjId和Id的索引，那么where MgrObjId='' and Id='' and Dtime=''就會(huì)采用索引查找，而where Dtime='' and MgrObjId='' and Id=''則不一定會(huì)采用索引查找。
把非索引列的結(jié)果列放在包含列中。因?yàn)槲覀儣l件是MgrObjId和Id以及Dtime，因此返回結(jié)果中只需包含Dtime和Value即可，因此把Dtime和Value放在包含列中，返回的索引結(jié)果就有這個(gè)值，不用再查物理表，可以達(dá)到最優(yōu)的速度。
跟上述幾點(diǎn)原則，我們建立以下的索引：CREATE NONCLUSTERED INDEX Idx_His20141008 ON dbo.his20141008(MgrObjId,Id) INCLUDE(Value,Dtime)
耗費(fèi)時(shí)間為：6分多鐘，索引大小為903M。
我們看看預(yù)估計(jì)劃：
可以看到，這里完全使用了索引，沒(méi)有額外的消耗。而實(shí)際執(zhí)行的結(jié)果，1秒都不到，竟然不用一秒就在1100w的記錄中把結(jié)果篩選了出來(lái)！！帥呆了??！
既然寫入完成了、讀取完成了，怎么結(jié)合呢？我們可以把一個(gè)小時(shí)之前的數(shù)據(jù)建立索引，當(dāng)前一個(gè)小時(shí)的數(shù)據(jù)就不建立索引。也就是，不要再創(chuàng)建表的時(shí)候建立索引?。?/p>
可以嘗試讀寫分離，寫兩個(gè)庫(kù)，一個(gè)是實(shí)時(shí)庫(kù)，一個(gè)是只讀庫(kù)。一個(gè)小時(shí)內(nèi)的數(shù)據(jù)查詢實(shí)時(shí)庫(kù)，一個(gè)小時(shí)之前的數(shù)據(jù)查詢只讀庫(kù)；只讀庫(kù)定時(shí)存儲(chǔ)，然后建立索引；超過(guò)一個(gè)星期的數(shù)據(jù)，進(jìn)行分析處理再存儲(chǔ)。這樣，無(wú)論查詢什么時(shí)間段的數(shù)據(jù)，都能夠正確處理了——一個(gè)小時(shí)之內(nèi)的查詢實(shí)時(shí)庫(kù)，一個(gè)小時(shí)到一個(gè)星期內(nèi)的查詢只讀庫(kù)，一個(gè)星期之前的查詢報(bào)表庫(kù)。
如果不需要物理分表，則在只讀庫(kù)中，定時(shí)重建索引即可。
看完了這篇文章，相信你對(duì)“在SQLServer中如何處理千萬(wàn)單位記錄”有了一定的了解，如果想了解更多相關(guān)知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀！
