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

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

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

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

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

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

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

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

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

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

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

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

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