推薦:sql教學
這是給某資料中心做的一個項目,項目難度之大令人髮指,這個項目真正的讓我感覺到了,商場如戰場,而我只是其中的一個小兵,太多的戰術,太多的高層之間的較量,太多的內幕了。具體這個專案的情況,我有空再寫相關的博文出來。
這個專案是要求做環境監控,我們暫且把受監控的設備稱為採集設備,採集設備的屬性稱為監控指標。專案需求:系統支援不少於10w個監控指標,每個監控指標的資料更新不大於20秒,儲存延遲不超過120秒。那麼,我們可以透過簡單的計算得出較理想的狀態-要儲存的資料為:每分鐘30w,每個小時1800w,也就是每天4億3千兩百萬。而實際,數據量會比這個大5%左右。 (其實大部分是資訊垃圾,可以用資料壓縮處理的,但是別人就是要搞你,能咋辦)
上面是專案要求的指標,我想很多有不少大數據處理經驗的同學都會呲之以鼻,就這麼點?嗯,我也看了很多大數據處理的東西,但是之前沒處理過,看別人是頭頭是道,什麼分佈式,什麼讀寫分離,看起來確實很容易解決。但是,問題沒這麼簡單,上面我說了,這是一個非常惡劣的項目,是產業惡性競爭典型的項目。
推薦自己的linuxC/C 交流群:973961276!整理了一些個人覺得比較好的學習書籍、視頻資料以及大廠面經視頻共享在群文件裡面,有需要的小伙伴可以自行添加哦! ~
首先遇到的第一個攔路虎就是,我們發現現有的程式下,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子句的条件顺序,对查询结果优化作用不明显。
那么,就只能通过索引的办法了。
建立索引不是简单的事情,是需要了解一些基本的知识的,在这个过程中,我走了不少弯路,最终才把索引建立起来。
下面的实验基于以下记录总数做的验证:
按单个字段建立索引
这个想法,主要是受我建立数据结构影响的,我内存中的数据结构为:Dictionary44c8bf3c168555d7e46180150f40c496>
。我以为先建立MgrObjId的索引,再建立Id的索引,SQLServer查询时,就会更快。
先按MgrObjId建立索引,索引大小为550M,耗时5分25秒。结果,如上图的预估计划一样,根本没有起作用,反而更慢了。
按多个条件建立索引
OK,既然上面的不行,那么我们按多个条件建立索引又如何?CREATE NONCLUSTERED INDEX Idx_His20141008 ON dbo.his20141008(MgrObjId,Id,Dtime)
结果,查询速度确实提高了一倍:
等等,难道这就是索引的好处?花费7分25秒,用1.1G的空间换取来的就是这些?肯定是有什么地方不对了,于是开始翻查资料,查看一些相关书籍,最终,有了较大的进展。
首先,我们需要明白几个索引的要点:
where MgrObjId='' and Id='' and Dtime=''
就會採用索引查找,而where Dtime='' and MgrObjId=' ' and Id=''
則不一定會採用索引查找。 跟上述幾點原則,我們建立以下的索引:CREATE NONCLUSTERED INDEX Idx_His20141008 ON dbo.his20141008(MgrObjId,Id) INCLUDE(Value,Dtime)
耗費時間為:6分多鐘,索引大小為903M。
我們看看預估計畫:
可以看到,這裡完全使用了索引,沒有額外的消耗。而實際執行的結果,1秒都不到,竟然不用一秒就在1100w的記錄中把結果篩選了出來! !帥呆了! !
既然寫入完成了、讀取完成了,怎麼結合呢?我們可以把一個小時之前的資料建立索引,目前一個小時的資料就不建立索引。也就是,不要再建立表格的時候建立索引! !
可以嘗試讀寫分離,寫兩個函式庫,一個是即時函式庫,一個是唯讀函式庫。一個小時內的數據查詢實時庫,一個小時之前的數據查詢只讀庫;只讀庫定時存儲,然後建立索引;超過一個星期的數據,進行分析處理再存儲。這樣,無論查詢什麼時間段的數據,都能夠正確處理了——一個小時之內的查詢實時庫,一個小時到一個星期內的查詢只讀庫,一個星期之前的查詢報表庫。
如果不需要實體分錶,則在唯讀函式庫中,定時重建索引即可。
如何在SQLServer中處理億萬層級的資料(歷史資料),可以按以下方面進行:
以上是學習在SQLServer中處理千萬單位記錄的詳細內容。更多資訊請關注PHP中文網其他相關文章!