推薦：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子句的条件顺序，对查询结果优化作用不明显。
那么，就只能通过索引的办法了。
建立索引不是简单的事情，是需要了解一些基本的知识的，在这个过程中，我走了不少弯路，最终才把索引建立起来。
下面的实验基于以下记录总数做的验证：
按单个字段建立索引
这个想法，主要是受我建立数据结构影响的，我内存中的数据结构为：Dictionary。我以为先建立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中文網其他相關文章！
