數(shù)據(jù)庫大表設(shè)計方案總結(jié)

1、oracle 大數(shù)據(jù)量的處理文：第三開發(fā)部（內(nèi)部使用，請勿外傳）在我們的數(shù)據(jù)庫應(yīng)用中，有些情況數(shù)據(jù)量會很大，單個表中每天超過千萬條數(shù)據(jù)。為了使大量的數(shù)據(jù)在讀寫操作和查詢中速度更快，采用了對表和索引進(jìn)行分區(qū)的技術(shù)，以改善應(yīng)用系統(tǒng)的性能。 根據(jù)不同的客戶需求，制定有針對性的解決方案。一、 案例：案例一、集中日志1、 需求描述，包括數(shù)據(jù)量，變動頻率，在系統(tǒng)中主要的業(yè)務(wù)關(guān)聯(lián)等；集中日志最大的表有兩張，slms_log_t和slms_cdr_t，但在各省的業(yè)務(wù)都沒有用到slms_cdr_t，實際上只對slms_log_t進(jìn)行操作。此表每天的入庫數(shù)據(jù)量為1.7億條(每天短信量大約6000條，日志是短信的2

2、.5到2.8倍)以上（峰值入庫每天10億條，2007年春節(jié)數(shù)據(jù)）。河南短信集中日志建4個數(shù)據(jù)庫，相互獨立，用數(shù)據(jù)庫鏈（db_link）做連接，入庫數(shù)據(jù)在應(yīng)用程序處做負(fù)載均衡。表中為動態(tài)數(shù)據(jù)，均為插入操作。對于日志查詢的業(yè)務(wù)處理都是操作這張表。業(yè)務(wù)需要對此表源號碼，目的號碼，時間進(jìn)行查詢，所以在其必需的主要查詢條件上都設(shè)立了索引。按日志時間對此表進(jìn)行了分區(qū)。2、 設(shè)計方案，包括表結(jié)構(gòu)，索引，分區(qū)；表結(jié)構(gòu)與入庫的日志結(jié)構(gòu)對應(yīng)，為分區(qū)表，根據(jù)入庫日志的時間按天進(jìn)行分區(qū),并將到期的最早一天的分區(qū)刪除。分區(qū)操作交由job執(zhí)行，每天凌晨進(jìn)行分區(qū)的添加與刪除，默認(rèn)創(chuàng)建此后5天的分區(qū)。根據(jù)查詢用到的主要條件建

3、立了分區(qū)索引。3、 關(guān)注要點分析，給出設(shè)計的原因分析和解決問題的關(guān)鍵點；入庫數(shù)據(jù)在應(yīng)用程序處做負(fù)載均衡，4臺數(shù)據(jù)庫均擔(dān)。slms_log_t表中的數(shù)據(jù)由cms實時入庫，后期的統(tǒng)計表是在每個數(shù)據(jù)庫上分別出一套中間表，然后到一臺機器上進(jìn)行匯總。不管是從效率上說還是業(yè)務(wù)上說，按時間分區(qū)和在這些字段上建立分區(qū)索引是必需的。4、 總結(jié)，將數(shù)據(jù)按時間進(jìn)行分區(qū)，對關(guān)鍵字段做索引，對于提高效率還是有限的。在河南slms，盡管做了分區(qū)、索引，如果操作4個數(shù)據(jù)庫聯(lián)合查詢，每個庫數(shù)據(jù)量都大，查詢速度并不算快。案例二、夢網(wǎng)網(wǎng)關(guān)1、 需求描述，包括數(shù)據(jù)量，變動頻率，在系統(tǒng)中主要的業(yè)務(wù)關(guān)聯(lián)等；夢網(wǎng)中的大表有兩張，表中的數(shù)

4、據(jù)量不同省份數(shù)據(jù)不同，但基本都在千萬級以上。表的變動頻率很大，數(shù)據(jù)為動態(tài)數(shù)據(jù)，千萬數(shù)據(jù)/天，但都是插入的操作。在系統(tǒng)中主要是通過表中的某幾個固定字段來與其它的表做業(yè)務(wù)關(guān)聯(lián)。一般情況下，會將大表中常用的幾個字段數(shù)據(jù)保留到中間表中，盡量避免對大表的查詢，雖然大表中有分區(qū)，但每天的業(yè)務(wù)量還是比較大的，如果索引建的不太合適，查詢起來還是相當(dāng)耗時的，將表中的數(shù)據(jù)導(dǎo)到中間表會減少很多查詢所需要的時間，有利于提高查詢的性能。2、 設(shè)計方案，包括表結(jié)構(gòu)，索引，分區(qū)；表結(jié)構(gòu)是根據(jù)入庫的話單格式來給出的。兩個表都為分區(qū)表，對時間做的范圍分區(qū)，使同一天的數(shù)據(jù)插入到一個分區(qū)中，這樣對某些數(shù)據(jù)進(jìn)行操作時，可以只在該分區(qū)

5、中進(jìn)行。數(shù)據(jù)庫中的分區(qū)每天會通過job來自動創(chuàng)建，一般情況下會提交創(chuàng)建出10個分區(qū)。分區(qū)的創(chuàng)建及保留天數(shù)是可配的。可以通過調(diào)整對應(yīng)參數(shù)來控制。索引為分區(qū)索引，當(dāng)索引出現(xiàn)問題時，可以單獨對某一分區(qū)索引進(jìn)行重建。注：創(chuàng)建大表的索引需要在大的數(shù)據(jù)量上做測試，否則創(chuàng)建出來的索引可能會對提高性能沒有太大的幫助。數(shù)據(jù)最好是能夠模擬現(xiàn)網(wǎng)的情況。之前對夢網(wǎng)的索引做過測試，一個是聯(lián)合索引，一個是單獨的索引，在家里測試的時候沒有太大區(qū)別，但對現(xiàn)網(wǎng)數(shù)據(jù)進(jìn)行測試的時候，耗時相差將近3倍。3、 關(guān)注要點分析，給出設(shè)計的原因分析和解決問題的關(guān)鍵點；將表設(shè)計為分區(qū)表主要是從效率上來考慮的，如果是對全表進(jìn)行操作在數(shù)據(jù)庫中耗時

6、會比較長，也會對數(shù)據(jù)庫中的回滾段有較大的要求。所以夢網(wǎng)一般情況下會盡量避免對大表做直接操作。4、 總結(jié)，夢網(wǎng)當(dāng)前的查詢主要是對中間表進(jìn)行的，除非是要統(tǒng)計一些話單的詳細(xì)情況才會到大表中進(jìn)行。設(shè)計方案：將數(shù)據(jù)按日期來進(jìn)行分區(qū)存儲，查詢的時候盡量使用分區(qū)字段把范圍縮小。但這種設(shè)計也有幾個問題：(1)中間表的數(shù)據(jù)只能保留到前一天的數(shù)據(jù)，如果需要對表中的數(shù)據(jù)進(jìn)行實時查詢的，無法滿足要求，還需要到大表中進(jìn)行查詢。因為為了提高利率的速度，中間表的數(shù)據(jù)都是在凌晨時通過job來大表中查出來的。(2)當(dāng)入庫出現(xiàn)問題時，有可能造成中間表數(shù)據(jù)與大表中的數(shù)據(jù)不一致的情況，這樣所有從中間表中取數(shù)據(jù)得到的統(tǒng)計結(jié)果也是不準(zhǔn)確

7、的。案例三、短信網(wǎng)關(guān)1、 需求描述，包括數(shù)據(jù)量，變動頻率，在系統(tǒng)中主要的業(yè)務(wù)關(guān)聯(lián)等；行業(yè)網(wǎng)關(guān)有兩張大表，分別存儲mo,mt話單數(shù)據(jù)，表中數(shù)據(jù)量根據(jù)不同省份的業(yè)務(wù)量略有不同。數(shù)據(jù)為動態(tài)數(shù)據(jù)。mt話單數(shù)據(jù)目前現(xiàn)網(wǎng)存儲占用200-300g，mo話單數(shù)據(jù)占用小于100g。mt話單數(shù)據(jù)每天插入數(shù)據(jù)量小于1千五百萬條，以后隨著業(yè)務(wù)量的增長還會增加。對大表主要是查詢操作，查詢話單詳單等。2、 設(shè)計方案，包括表結(jié)構(gòu)，索引，分區(qū)；兩個表都為分區(qū)表，對時間做的范圍分區(qū)，每天發(fā)送時間00：00：00至23：59：59的數(shù)據(jù)存儲在一個分區(qū)內(nèi)。索引為分區(qū)索引。數(shù)據(jù)庫中的分區(qū)每天會通過job來自動創(chuàng)建及刪除。分區(qū)的創(chuàng)建及

8、保留天數(shù)是可配的?？梢酝ㄟ^調(diào)整job內(nèi)的參數(shù)來控制。3、 關(guān)注要點分析，給出設(shè)計的原因分析和解決問題的關(guān)鍵點；將表設(shè)計為分區(qū)表主要是從查詢效率上來考慮，全表掃描耗時很長，加索引也基本不可用。目前只能按一天查詢話單，將查詢范圍限制在一個分區(qū)內(nèi)，再通過索引查詢。查詢詳單使用了oracle的rowid，管理界面直接通過rowid查詢話單內(nèi)容。索引的設(shè)計主要基于管理界面的查詢條件，將界面上必填的字段如時間，目的號碼和特服號碼作為索引字段。4、 總結(jié)，目前話單的查詢都在大表中進(jìn)行，每天00：00開始會有數(shù)據(jù)庫job將大表數(shù)據(jù)匯總到幾個不同級別的統(tǒng)計中間表，管理界面各統(tǒng)計模塊只查詢中間表不再關(guān)聯(lián)大表。出現(xiàn)

9、的問題與夢網(wǎng)也基本一樣，沒辦法實時統(tǒng)計，只能統(tǒng)計前一天的內(nèi)容。二、 總結(jié)：1、 使用分區(qū)的條件所有的分區(qū)的邏輯屬性是相同的，但他們的物理屬性可以不同。分區(qū)的剪枝 （partition pruning）oracle server 可以自動識別分區(qū)，根據(jù)select 語句所指定的選擇條件，只查詢有用的分區(qū)。如果語句的條件中對分區(qū)字段使用了函數(shù)，優(yōu)化器則不能進(jìn)行分區(qū)剪枝，但to_date函數(shù)除外。2、 分區(qū)的優(yōu)點(1) 高可用性：如果表的一個分區(qū)由于系統(tǒng)故障而不能使用，表的其余好的分區(qū)仍然可以使用； (2) 減少關(guān)閉時間：如果系統(tǒng)故障只影響表的一部分分區(qū)，那么只有這部分分區(qū)需 要修復(fù)，故能比整個大表

10、修復(fù)花的時間更少； (3) 維護輕松：對于大型的歷史數(shù)據(jù)表，將其分區(qū)，分別管理和方便地添加和刪除。； (4) 均衡i/o:可以把表的不同分區(qū)分配到不同的磁盤來平衡i/o改善性能； (5) 改善性能：對大表的查詢、增加、修改等操作可以分解到表的不同分區(qū)來并行執(zhí)行，可使運行速度更快； (6) 基于分區(qū)的 join 操作，會提高查詢性能(7) 分區(qū)對用戶透明，最終用戶感覺不到分區(qū)的存在。增強可用性：如果表的某個分區(qū)出現(xiàn)故障，表在其他分區(qū)的數(shù)據(jù)仍然可用； 維護方便：如果表的某個分區(qū)出現(xiàn)故障，需要修復(fù)數(shù)據(jù)只修復(fù)該分區(qū)即可； 均衡i/o：可以把不同的分區(qū)映射到磁盤以平衡i/o，改善整個系統(tǒng)性能； 改善查詢

11、性能：對分區(qū)對象的查詢可以僅搜索自己關(guān)心的分區(qū)，提高檢索速度?？沙掷m(xù)性：超過使用期限的數(shù)據(jù)可以進(jìn)行drop分區(qū)操作。3、 使用分區(qū)的注意事項：對于超大數(shù)據(jù)量，只做分區(qū)不做負(fù)載均攤，性能提高有限。對于出中間表的情況，如果原始表數(shù)據(jù)改變，中間表需要重新生成。對于分區(qū)表的索引，如果單個分區(qū)上的索引失效，支持重建，但是不支持只在單個分區(qū)上建一個索引。分區(qū)索引必須在每個分區(qū)上都建索引，全局索引重建的時間與數(shù)據(jù)量有關(guān)。三、 建議：對于話單表，日志表等數(shù)據(jù)量大需要定期刪除過期數(shù)據(jù)的情況，建議使用分區(qū)表。對于數(shù)據(jù)量特別大的表，在應(yīng)用程序階段進(jìn)行負(fù)載均衡是一個值得借鑒的方法。適當(dāng)使用中間表，可以提高前臺響應(yīng)速度

12、。在頻繁使用的查詢條件上，建分區(qū)索引。盡量使用分區(qū)內(nèi)查詢，避免跨分區(qū)查詢。對于在沒有索引的列上進(jìn)行大范圍的查詢應(yīng)該盡量避免。實在無法避免的情況，應(yīng)該給用戶一個提示信息。四、 分區(qū)表的設(shè)計原則1.表的大小：當(dāng)表的大小超過1.5gb2gb，或?qū)τ谠诰€交易系統(tǒng)，表的記錄超過1000萬，都應(yīng)考慮對表進(jìn)行分區(qū)。 2.數(shù)據(jù)訪問特性：基于表的大部分查詢應(yīng)用，只訪問表中少量的數(shù)據(jù)。對于這樣表進(jìn)行分區(qū)，可充分利用分區(qū)排除無關(guān)數(shù)據(jù)查詢的特性。 3.數(shù)據(jù)維護：待添加的隱藏文字內(nèi)容3按時間段刪除成批的數(shù)據(jù)，例如按月刪除歷史數(shù)據(jù)。對于這樣的表需要考慮進(jìn)行分區(qū)，以滿足維護的需要。4.數(shù)據(jù)備份和恢復(fù)：按時間周期進(jìn)行表空間的備份時，將分區(qū)與表空間建立對應(yīng)關(guān)系。5.只讀數(shù)據(jù)：如果一個表中大部分?jǐn)?shù)據(jù)都是只讀數(shù)據(jù)，通過對表進(jìn)行分區(qū)，可將只讀數(shù)據(jù)存儲在只讀表空間中，對于數(shù)據(jù)庫的備份是非常有益的