如何設計一個資料庫,能夠存下如此「大量」的數據?
大家好,我是一個DB新手,現在有一個需求,請問大家該如何解決呢?
問題的背景:我們的團隊在做「量化投資分析」的事情,需要從數據介面、網路上等各個地方獲取證券的信息,主要是「實時的價格變動信息」。然後對這些數據進行實時的分析,將發現的機會記錄下來。我們要對500個證券品種進行監控,在開盤時,每5秒會更新一下價格數據。這樣算下來的話,每個證券品種一天就會產生2880條記錄,如果是500個的話,就會有144萬條數據。而這,還僅僅是一天中產生的數據。請問,如果使用MySQL資料庫,我們該如何設計資料庫和表,來承載這樣的數據量呢?
我們要對500個證券品種進行監控,在開盤時,每5秒會更新一下價格數據。這樣算下來的話,每個證券品種一天就會產生2880條記錄,如果是500個的話,就會有144萬條數據。而這,還僅僅是一天中產生的數據。
》》設你每條記錄存 (主鍵,股票代碼,時間戳,價格。。。)加起來也不會超過100位元組這樣的話:每天產生的量在 144W*100byte/1024/1024 約137M 每周 137*5天(周末不開盤,並忽略法定假日)=685M 144W*5=720W條記錄 每月 685M*4=2.67G 720W*4=2880W條記錄如果是單表的,整個數據量隨著時間的累積是變的越來越大,也許存一周的數據還能滿足要求,但是存一個月的數據 可能會讓你的用戶感覺系統使用起來各種慢。 但這也不是絕對的,可以根據你的業務設計出更好的結構。這時就要結合的你的業務看你想幹嘛了:
猜:1 只是把行情拿下來,什麼 也不做: 這種就可以一個表,啥都不管,只放就行了 ------ 這種情況應該是不存在的,拿了數據當然是分析用了2 只分析當天的數據表(A),把分析的結果放到另一個結果表(result_A,設結果表中每個證券產品每天只有一條數據)中 :
每天分析完當天的數據之後,把 當天產生的數據歸檔到歷史表(histroy_A下面會再說到這個表)里,並且清空A的數據,那麼下一次使用時,又是一個新表,表也不會太大。3 在情況2的基礎上,還會對每周的數據進行分析,查看一周的情況:
這時候又分兩種情況 3.1 每周的分析數據可以在每天的分析數據表(result_A)中得到總結,那麼直接看 結果。3.2 每周的分析數據需要對整周的證券價格進行分析, 那麼 histroy_A 這個表就最好是一個按周分區的分區表,這樣分析每周的數據時,剛好看一個分區的數據就行了。
上面三種只是假設,真實業務應該比這複雜太多。你可以根據自己的業務想著具體怎麼樣做才是最好的。綜合大多數場景,設計出較好的結構。
你這個應用,應該有不少統計、分析類的操作,使用ucloud(UCloud – 專業雲計算服務商)的UDB產品,可以很方便的為一個主庫建立多個從庫,把這種統計、分析的操作放在從庫上操作,並且主從環境的配置也可以不一樣,比如,主庫可以是ssd 6G內存的,從庫可以是普通盤 8G內存的。這樣也能節省成本和時間。
QQ 273002188 歡迎一起學習QQ 群 236941212oracle,mysql,PG 相互交流數據的尺寸並不大,一般的資料庫都可以搞定,提前做好sharding即可。
但是由於數據的記錄數比較多,同時需要實時分析,因為我做過一些股票數據的處理,我假設你可能需要頻繁的訪問歷史數據,所以一個建議是除了原始數據外可以做幾個統計表,比如30秒,1分鐘,5分鐘,15分鐘等,這些表按更大的時間區間把數據提前準備好,實時分析的時候比訪問原始數據更有效率。
另外就是索引的問題,推測你會用到大量的範圍查詢,建議時間使用毫秒數來存儲並建立索引(非主鍵)。
mysql單庫很長時間夠你用了,如果一定要搞大,mysql有很多分布的方案,挑個合適的就行。
比如cobar的方式可以按月存表,stock_01 stock_02 ... 表放到不同的db,db可以放到不同server,安裝cobar並配置好數據源就可以當整庫使用(某些功能不支持,具體見文檔)。不局限mysql的話,也可以試試大數據的方式,比如最經典的hadoop,存儲方面底層的hdfs支持自動擴容,查詢方面,提供了hivesql。hivesql和mysql都是兼容標準sql又提供自己特性的sql方言,所以查詢寫法上都很類似,當然有些特性不一樣,或者語法有細微差異,具體也可以參見文檔。====
看了下回答里,有提到分時和日K之類的區分,感覺這個具體業務邏輯,不應該通過調整架構來實現,分布的方案,也盡量是簡單通用的。比如這個日K的需求,一般應該是建專門的統計表來實現,通過定時跑腳本的方式生成統計數據。統計表量大的話,本身也可以做分庫分表,日K按月存,月K按年存。區區一百萬條,每天100位元組也才100M,隨便亂寫,怎麼順手怎麼來。
謝邀。
不知道你們的【實時分析】,需要哪種程度的實時。
如果實時性要求不高,mysql隨便怎麼玩都行,因為一天100w數據真心不多。
但如果需要超高性能,參考某證券公司:
1.前置機接收到新增數據後,發送到3個地方:日誌、mysql、業務中間件集群。
2.日誌:在存儲上以日誌的形式保存一份數據,當做備份存檔。
3.Mysql:在mysql伺服器上存一份數據,機器用ssd當內存來節約錢。mysql是給業務中間件當緩存用。
4.業務中間件集群:用C++寫的一套並行業務中間件,運行在PC集群上。PC是4核I5(無HT),每個節點32G內存。當數據增多時,直接增加機器就行。以前菜鳥,覺得每天幾十萬條數據好多,服務器會頂不住要怎麼辦要怎樣優化,後來發現,是我想多了。
這「大量」上的雙引號用的不錯
電腦配置如下圖(windows7;interl 2.5G cpu;8G內存):
操作步驟(.net開發的winform程序,沒有使用線程):
1.遍曆日線文件所在的文件夾中的文件2.從文件中取出股票最近25交易日的日線數據3.在資料庫中查詢記錄是否存在,存在就刪除這條記錄並追加該記錄,不存在就直接追加。每個月約22個交易日,故overlap的數據記錄是三條。資料庫沒有優化前(沒有索引):
MY SQL:需要至少兩天時間(48h)完成上述操作。MS SQL: 需要約半天的時間(6h)完成上述操作。資料庫優化後(建立索引等):
MY SQL:不是很穩定,有時候幾分鐘,有時候2小時。MS SQL: 需要5分鐘左右完成上述操作。不管用什麼用的資料庫:建索引,建索引,建索引。OpenTSDB絕對不會後悔
不知道你聽說過hive,spark,mapreduce,hdfs沒有
數據量一點不大。 MySQL sharding 就夠了。
汗顏!想起自己用EXCEL的vlookeup查了66W單元格,每次改動原表格要等3分鐘
Index只要不建偏,怎麼玩都行。(其實就這點數據,即使建偏估計也沒多大影響,頂多略微有點慢)
分散式啊,多加帶寬,多加緩存,
前兩個月正好做這些工作,用的是新浪雲。可是我只是個碼農研究生,發現不懂證券分析,只好停下來去實習了。要是離的近,可以交流一下,坐標廣州。
感覺大神的回答都很超神,小的我get不來。分表分庫的,確實我能想到的也是這麼干,不過要多說一句就是必須根據實際的查詢用例來分,不能簡單的按日分表按月建庫。就拿股票來說吧,你查分時k線,當然是按日一個表搞定。如果是查日k,按日分表就苦逼了吧,你得查多少個表啊老表。小白一名,第一次哆嗦,大神微槽我,怕臉皮薄下次不敢來。
用實時資料庫,一秒100w條記錄無壓力
數據量有點小吧,日數據量上TB,PB再考慮吧。
如上各位牛人所說,MySQL毫無壓力,如果查詢多做一下讀寫分離,掛兩個從庫就行
做量化的是需要很長的歷史分時做回測的,一直想考慮用hbase來存儲歷史tick數據~~
推薦閱讀:
※MySQL 查詢 select * from table where id in (幾百或幾千個 id) 如何提高效率?
※uuid作為主鍵,還是用自增呢?
※兩個800萬條目的表,相同主鍵,總數差了1個,如何高效的將其找出來?
※考OCP做DBA的職業規劃路線是否具有以下優勢或者特質?歡迎拍磚。