SQLServer中處理億萬級別的數據
當前位置:點晴教程→知識管理交流
→『 技術文檔交流 』
先說結論: 在SQLServer中處理億萬級別的數據(歷史數據),可以按以下方面進行: 1、去掉表的所有索引 2、用SqlBulkCopy進行插入 3、分表或者分區,減少每個表的數據總量 4、在某個表完全寫完之后再建立索引 5、正確的指定索引字段 6、把需要用到的字段放到包含索引中(在返回的索引中就包含了一切) 7、查詢的時候只返回所需的字段 首先聲明,我只是個程序員,不是專業的DBA,以下這篇文章是從一個問題的解決過程去寫的,而不是一開始就給大家一個正確的結果,如果文中有不對的地方,請各位數據庫大牛給予指正,以便我能夠更好的處理此次業務。 項目背景這是給某數據中心做的一個項目,項目難度之大令人發指,這個項目真正的讓我感覺到了,商場如戰場,而我只是其中的一個小兵,太多的戰術,太多的高層之間的較量,太多的內幕了。具體這個項目的情況,我有空再寫相關的博文出來。 這個項目是要求做環境監控,我們暫且把受監控的設備稱為采集設備,采集設備的屬性稱為監控指標。項目要求:系統支持不少于10w個監控指標,每個監控指標的數據更新不大于20秒,存儲延遲不超過120秒。那么,我們可以通過簡單的計算得出較理想的狀態——要存儲的數據為:每分鐘30w,每個小時1800w,也就是每天4億3千兩百萬。而實際,數據量會比這個大5%左右。(實際上大部分是信息垃圾,可以通過數據壓縮進行處理的,但是別人就是要搞你,能咋辦) 上面是項目要求的指標,我想很多有不少大數據處理經驗的同學都會呲之以鼻,就這么點?嗯,我也看了很多大數據處理的東西,但是之前沒處理過,看別人是頭頭是道,什么分布式,什么讀寫分離,看起來確實很容易解決。但是,問題沒這么簡單,上面我說了,這是一個非常惡劣的項目,是一個行業惡性競爭典型的項目。
寫入瓶頸首先遇到的第一個攔路虎就是,我們發現現有的程序下,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的參數 按采集設備存儲 于是有了這種結構: 結果驗證,比上面的稍微好點,但是不是太明顯。 數據表分區??? 停止其他程序 難道是SQLServer的瓶頸? 等等,好像還有個東西,索引,對索引!索引的存在會影響插入、更新。 去掉索引是的,去掉索引之后查詢肯定慢,但是我必須先驗證去掉索引是否會加快寫入。如果果斷把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 FREEPROCCACHE
DBCC DROPCLEANBUFFERSSET STATISTICS IO ONselect Dtime,Value from dbo.his20140825 WHERE Dtime>='' AND Dtime<='' AND MgrObjId='' AND Id=''SET STATISTICS IO OFF--優化之后DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERSSET STATISTICS IO ONselect Dtime,Value from dbo.his20140825 WHERE MgrObjId='' AND Id='' AND Dtime>='' AND Dtime<=''SET STATISTICS IO OFF 結果如下: 優化之前反而更好了? 仔細查看IO數據,發現,預讀是一樣的,就是說我們要查詢的數據記錄都是一致的,物理讀、表掃描也是一直的。而邏輯讀取稍有區別,應該是緩存命中數導致的。也就是說,在不建立索引的情況下,where子句的條件順序,對查詢結果優化作用不明顯。 那么,就只能通過索引的辦法了。 建立索引的嘗試建立索引不是簡單的事情,是需要了解一些基本的知識的,在這個過程中,我走了不少彎路,最終才把索引建立起來。 下面的實驗基于以下記錄總數做的驗證: 按單個字段建立索引 這個想法,主要是受我建立數據結構影響的,我內存中的數據結構為: 先按MgrObjId建立索引,索引大小為550M,耗時5分25秒。結果,如上圖的預估計劃一樣,根本沒有起作用,反而更慢了。 按多個條件建立索引 OK,既然上面的不行,那么我們按多個條件建立索引又如何? 結果,查詢速度確實提高了一倍: 等等,難道這就是索引的好處?花費7分25秒,用1.1G的空間換取來的就是這些?肯定是有什么地方不對了,于是開始翻查資料,查看一些相關書籍,最終,有了較大的進展。 正確的建立索引首先,我們需要明白幾個索引的要點:
跟上述幾點原則,我們建立以下的索引: 耗費時間為:6分多鐘,索引大小為903M。 我們看看預估計劃: 可以看到,這里完全使用了索引,沒有額外的消耗。而實際執行的結果,1秒都不到,竟然不用一秒就在1100w的記錄中把結果篩選了出來!!帥呆了!! 怎么應用索引?既然寫入完成了、讀取完成了,怎么結合呢?我們可以把一個小時之前的數據建立索引,當前一個小時的數據就不建立索引。也就是,不要再創建表的時候建立索引!! 還能怎么優化可以嘗試讀寫分離,寫兩個庫,一個是實時庫,一個是只讀庫。一個小時內的數據查詢實時庫,一個小時之前的數據查詢只讀庫;只讀庫定時存儲,然后建立索引;超過一個星期的數據,進行分析處理再存儲。這樣,無論查詢什么時間段的數據,都能夠正確處理了——一個小時之內的查詢實時庫,一個小時到一個星期內的查詢只讀庫,一個星期之前的查詢報表庫。 如果不需要物理分表,則在只讀庫中,定時重建索引即可。 總結如何在SQLServer中處理億萬級別的數據(歷史數據),可以按以下方面進行:
轉自:http://www.cnblogs.com/marvin/p/4123745.html 該文章在 2024/6/6 9:52:49 編輯過 |
關鍵字查詢
相關文章
正在查詢... |