數(shù)據(jù)庫(kù) SQL千萬(wàn)級(jí)數(shù)據(jù)規(guī)模處理概要
2020-10-29 21:53:56
供稿：網(wǎng)友
1. 數(shù)據(jù)太多。放在一個(gè)表肯定不行。
比如月周期表。一個(gè)月1000萬(wàn)，一年就1.2億，如此累計(jì)下去肯定不行的。所以都是基于一個(gè)周期數(shù)據(jù)一個(gè)表。甚至一個(gè)周期數(shù)據(jù)就要分幾個(gè)分表。主要是考慮實(shí)際的數(shù)據(jù)量而定。當(dāng)你創(chuàng)建一個(gè)新表時(shí)，可能這個(gè)表需要有索引，但是都要先取消索引，或者先建立表，導(dǎo)入數(shù)據(jù)后，再建立索引。
必要時(shí)處理完，統(tǒng)計(jì)完后，就備份到磁帶或者其他介質(zhì)。然后清掉。
從問(wèn)題域來(lái)看，一個(gè)周期內(nèi)的數(shù)據(jù)關(guān)聯(lián)性最大。比如統(tǒng)計(jì)一個(gè)客戶(hù)某個(gè)帳期的話(huà)單總額，同比上月增幅，還有就是零話(huà)費(fèi)客戶(hù)等。如此種種，參照的數(shù)據(jù)不外乎本周期，或者兩個(gè)周期，甚至更多就是一個(gè)季度，或者半年的樣子（類(lèi)似三個(gè)月連續(xù)零話(huà)費(fèi)，或者三個(gè)月連續(xù)欠費(fèi)未交之類(lèi)的，保存量之類(lèi)的報(bào)表可能會(huì)要一年的數(shù)據(jù)）。而且這樣的情況在數(shù)據(jù)挖掘或者高級(jí)管理報(bào)表中比較常見(jiàn)，一般營(yíng)業(yè)部門(mén)使用的界面中，是不可能含有這樣的統(tǒng)計(jì)的。
所以數(shù)據(jù)按表分開(kāi)，甚至于可以按數(shù)據(jù)庫(kù)分開(kāi)，更便于管理。
大家要打消一種固有的思路，這些數(shù)據(jù)，跟環(huán)衛(wèi)工人處理垃圾一樣，是幾乎有點(diǎn)帶人工處置的多步驟方式，也就是不會(huì)作為常規(guī)數(shù)據(jù)（如客戶(hù)基本資料等）長(zhǎng)期存在和頻繁使用的。所以我們可以改變思路，就是想盡辦法，在需要的時(shí)候，做最佳處理，而在不需要時(shí)，清理掉它。也就是說(shuō)，比如分表，你可以分100個(gè)表，1000個(gè)表都可以。只要方便統(tǒng)計(jì)和得到所需數(shù)據(jù)即可。
view只是說(shuō)你能在寫(xiě)select語(yǔ)句時(shí)簡(jiǎn)單一點(diǎn)，對(duì)速度沒(méi)有任何提高。
主要是，你的分表的方式能建立減少訪問(wèn)所有數(shù)據(jù)，就能提高速度。比如你做某個(gè)統(tǒng)計(jì)，那些數(shù)據(jù)恰好在某個(gè)分表內(nèi)。舉例說(shuō)，你有10個(gè)分部，而你統(tǒng)計(jì)id=1這個(gè)分部時(shí)，你恰好把數(shù)據(jù)放在第一個(gè)分表里，你就可以在存儲(chǔ)器內(nèi)通過(guò)判斷，只訪問(wèn)第一個(gè)分表，從而提高統(tǒng)計(jì)速度。如果你的統(tǒng)計(jì)需要統(tǒng)計(jì)全部分表內(nèi)的數(shù)據(jù)，那處理速度還是一樣慢。
2. 假設(shè)每個(gè)表的數(shù)據(jù)在數(shù)十萬(wàn)條，那統(tǒng)計(jì)起來(lái)是沒(méi)有任何瓶頸的。常規(guī)的數(shù)據(jù)庫(kù)都應(yīng)該沒(méi)任何問(wèn)題。
3. 預(yù)處理的必要性。
有人問(wèn)：我統(tǒng)計(jì)一千萬(wàn)條數(shù)據(jù)匯總，要多久多久，能否提高。。。試想你把中國(guó)人所有的存款加總，需要多長(zhǎng)時(shí)間吧？看看這個(gè)問(wèn)題的規(guī)模，其實(shí)再?gòu)?fù)雜的數(shù)據(jù)庫(kù)dbms，我們說(shuō)他都逃不過(guò)：找出符合條件的數(shù)據(jù)，一條一條的加總這個(gè)計(jì)算過(guò)程。暫且不提where條件了。預(yù)處理的必要性在于，如此規(guī)模的數(shù)據(jù)處理，本身就是一個(gè)非常耗時(shí)的過(guò)程，我們有必要提前，處理其結(jié)果到一個(gè)表內(nèi)，或者多個(gè)表里面。用戶(hù)查詢(xún)時(shí)，再顯示出來(lái)。比如說(shuō)1000萬(wàn)數(shù)據(jù)分10個(gè)分部，要看每個(gè)分部的應(yīng)收增長(zhǎng)，那我們可以預(yù)先統(tǒng)計(jì)數(shù)據(jù)到分部費(fèi)用表中，則用戶(hù)端報(bào)表顯示時(shí)，就非常快。如果任何數(shù)據(jù)匯總都要從原始數(shù)據(jù)去統(tǒng)計(jì)，那是不現(xiàn)實(shí)的。所以我們可以設(shè)置原始數(shù)據(jù)表，中間結(jié)果表，結(jié)果表，匯總表，月結(jié)表，期間表之類(lèi)的東西。逐步統(tǒng)計(jì)歸屬。
另外要提的是，這樣的動(dòng)作肯定非常耗時(shí)，而且！這樣的數(shù)據(jù)如果由服務(wù)器的存儲(chǔ)過(guò)程定期定時(shí)執(zhí)行的話(huà)，處理的規(guī)模就只有一次，任何客戶(hù)端，都只從結(jié)果表里產(chǎn)生報(bào)表。如果不用此方法，任何客戶(hù)端報(bào)表都從原始數(shù)據(jù)產(chǎn)生，理論上是可以，但是這樣的千萬(wàn)條數(shù)據(jù)匯總的處理會(huì)做N次。而且時(shí)間上也是不容許的。
還有，這樣的統(tǒng)計(jì)過(guò)程最好是分開(kāi)db進(jìn)行存放，而公用的數(shù)據(jù)比如客戶(hù)基本資料，最好拷貝一份到這個(gè)新db中來(lái)處理。這樣可以不干擾到正常的使用。
可以在晚上，或者另開(kāi)db或者在另外的server上跑這個(gè)過(guò)程。處理完后，寫(xiě)一個(gè)標(biāo)志告訴主db，則客戶(hù)端可以統(tǒng)計(jì)這些報(bào)表了。
4. 對(duì)單行數(shù)據(jù)做計(jì)算字段。舉個(gè)例子，比如一條記錄的產(chǎn)生時(shí)間是2009-01-01 12:00:00.001,如果你的統(tǒng)計(jì)剛好需要對(duì)某個(gè)時(shí)段進(jìn)行統(tǒng)計(jì)，那最好增加字段，比如hour字段，下一個(gè)批處理命令下去，取得小時(shí)數(shù)，然后再統(tǒng)計(jì)。
5. select語(yǔ)句中忌諱對(duì)column做函數(shù)。因?yàn)楹瘮?shù)將導(dǎo)致查詢(xún)條件不走索引，而改走遍歷所有數(shù)據(jù)。這樣你就是查一條數(shù)據(jù)，也會(huì)遍歷所有數(shù)據(jù)，那豈不是可憐。
6. 條件盡量都是數(shù)字，也就是都用id，比如分部，鎮(zhèn)區(qū)，業(yè)務(wù)種類(lèi)，接入類(lèi)型，客戶(hù)地址，等等，都需要用到fk方式的編碼，主表里只用數(shù)字id，請(qǐng)記住是數(shù)字型id。整數(shù)型數(shù)字是計(jì)算最快的數(shù)據(jù)類(lèi)型。如果金額極大，可以用decimal(小數(shù)=0）。varchar類(lèi)型是效率很低的，不過(guò)好像有sql的md5算法，我想可以嘗試這個(gè)方法（我還沒(méi)試過(guò)）。
7. 索引，這個(gè)是海量數(shù)據(jù)查詢(xún)首要解決的問(wèn)題。
沒(méi)有索引，就是遍歷。索引沒(méi)有覆蓋到，也會(huì)走遍歷。
8. 復(fù)雜的統(tǒng)計(jì)，用存儲(chǔ)器做分步處理，然后得到結(jié)果，同比一條select語(yǔ)句實(shí)現(xiàn)要輕松和明白得多。
而且對(duì)表的占用時(shí)間要短得多。當(dāng)然，很復(fù)雜的統(tǒng)計(jì)可能要用到條件判斷，循環(huán)等，一條select語(yǔ)句是無(wú)法處理的。多層的where中的子句也是效率低，容易占用表的寫(xiě)法。
原則上，這里我所討論的問(wèn)題都不是那種基于網(wǎng)站內(nèi)容管理的小case，主要對(duì)企業(yè)運(yùn)用而言。比如舉例說(shuō)查一個(gè)“存量客戶(hù)增幅表”，問(wèn)題都不是簡(jiǎn)單到直接對(duì)比兩個(gè)月的話(huà)費(fèi)總額這么簡(jiǎn)單，還得找出之前他的話(huà)費(fèi)如何，比如超過(guò)多少錢(qián)的才列入統(tǒng)計(jì)對(duì)象。所以，我的理解：復(fù)雜的問(wèn)題，必須存儲(chǔ)過(guò)程。真正做過(guò)幾個(gè)項(xiàng)目才會(huì)明白，寫(xiě)sql語(yǔ)句會(huì)比編程代碼還要多。真正的程序，其實(shí)是sql。
最后說(shuō)一句，如果經(jīng)驗(yàn)足夠豐富，寫(xiě)出的統(tǒng)計(jì)過(guò)程，其執(zhí)行時(shí)間在數(shù)分鐘甚至幾個(gè)小時(shí)都是正常的。所以初學(xué)者應(yīng)該明白，數(shù)據(jù)量是與處理時(shí)間成正比的。如果平時(shí)處理幾條數(shù)據(jù)感覺(jué)很快，數(shù)據(jù)量猛然增加幾個(gè)數(shù)量級(jí)，不要認(rèn)為時(shí)間上還能優(yōu)化到幾秒鐘。
ERP里的MRP展開(kāi)計(jì)算，通常能到幾個(gè)小時(shí)的。這都是正常的。(主要是物料多，bom多，計(jì)算步驟太多造成)
9. 補(bǔ)充一點(diǎn)。如果數(shù)據(jù)量超過(guò)我們標(biāo)題的千萬(wàn)級(jí)，甚至幾十億數(shù)量級(jí)。那也不存在問(wèn)題，還是分而治之的思路，就是把數(shù)據(jù)在多臺(tái)服務(wù)器上并行運(yùn)行。就好像為災(zāi)區(qū)捐款一樣，靠一個(gè)人的力量是不行的。人多力量大。類(lèi)似數(shù)據(jù)分揀之類(lèi)的，只需要原始數(shù)據(jù)和基本資料，還有一些計(jì)費(fèi)策略之類(lèi)的。完全可以分布在多臺(tái)server上同時(shí)處理，也是必要的。主要根據(jù)你的數(shù)據(jù)量和單臺(tái)處理的速度以及你要求的總的處理時(shí)間而決定的。有人說(shuō)select語(yǔ)句難道也需要分布？只能說(shuō)，如果確實(shí)有必要，也能做到。比如你要返回所有話(huà)單異常的數(shù)據(jù)，那也可以從每臺(tái)執(zhí)行檢索，然后匯合到一起，我想是可以的。
總而言之：
一。合理設(shè)計(jì)表結(jié)構(gòu)，使得統(tǒng)計(jì)匯總最高效（包括fk設(shè)計(jì)和用數(shù)字id，不用varchar，索引設(shè)計(jì)，計(jì)算字段）；
二。合理分表，使得單表數(shù)據(jù)規(guī)模適當(dāng)；
三。用存儲(chǔ)器分多個(gè)步驟處理。
四。數(shù)據(jù)預(yù)先處理。
五。分布在多臺(tái)server上同時(shí)處理。
也就是分而治之與預(yù)處理。
