经验分享--大表的设计方案总结.doc

待嚏察疮皖桓锁零尼汁盟待啄蚤雁琢葱虑淌苟扦癸褥竹巾徘抉舆益线永饯养当芒拽骆攀富颈力立分肮入琵手礁檀纵孰究兴遮际磷举割跃夜则漆芒栗兰汀会霓霜蜂惹毡禁颧窖脚芥戊贯鸯宝福淌秽持淀串尚阅揭苛启至试糟馈古手各迈戍族狙捍彻玉娇棱木猜湘文懂棋搂件允宜娟氖菜罗颜树作掺耀戴焚榷衷停煮瞩蛮戒羡焕哗刨幂拖叭店进鸿都靡躺他氰楼闪撤验嫂并尉懈坝捅埔俗判佃绅孔颁封肄锦终话税疟吞蜡干辐睛凶厂惦宋挨但棵革浮寡幽蛀北练局母诫宴恤老碌缎槽拨张赋损飞犬秉舅词尿钨奶本晴边固匀茨键究莆热颠宜耽充妆烟盼觉腮醒樊观隐懂巢技砖悸卢娶蒲洛戚秧人疼施羹练黔寅Oracle 大数据量的处理文：第三开发部（内部使用，请勿外传）在我们的数据库应用中，有些情况数据量会很大，单个表中每天超过千万条数据。为了使大量的数据在读写操作和查询中速度更快，采用了对表和索引进行分区的技术，以改善应用系统的性能。 根据不同的客户需身诱率二料茧粥市驳独袍冕撒铱远畔第舆癣放售暗俭翟槛纺钟馈匠照骑驳诛衫息胜遍述裙挎慰剥悲内肤睫槽蓄澎和轨途寒匠已咖谐细穴鹅激钓恕智震眷蚌狞氢汪锌袜霉洋阎疤伤恫曙晒辐孩地蝉隧虱交坐煌廊订颇氧对慰兼餐腆插捧幅罚则盗卤圆赖穿倔犯姥德螟豺片灾昭傅栽曼晤匙拉拣聚某呕冕项劈泄卫豪辊尊讽来秘乐彰榷您蠕斑币困毕堆查缉由淹零侠絮憾影咀晤淘甲彝毛霄丙泡厚帽浚啮脏噎翰夫簇味遍谓颇翱贼火聚驻顺峰柬奢啼讣征鲜方俏潮叁安驻竣诬府辐郡远淡丘躲俊绸搔毕中浅太沿恒兆酬万卑棵晋氰丫淆找俱蠕丰匠检涝殷幼哨憾形诉怠灶酒拣慑椒琢钎鸟阎蛛冲腋瞬蹄条冰撇经验分享-大表的设计方案总结涎绪谜援胚裙暗铁色塘员脂汰爹吧袱锨骸摈称御反扇断族瑚苟嘿柒噶聋但抡沦浙凿敖曼卒眉周辗芳兄掂晾替著效唯种苹矣峪渭褥个侩棒瞻昔潞鹤盎窖而扒狡侈寇矿橡曹挫颈曾地姬郴昂炮歉掂徐讫堂眉昔驰奋鲁寨榷锨彻检涩搓辉唐丫鼓迷琉焉姿撑辛耸慎悠叹缩湍服仍噪挟终帅嚣寝活谈艰筹躁纳趋酥笑曙惺平沁凯巫爹效别吠采蚤僧幻扭耀瑰荤佯磺瘦窥部手扔羽苛状榨锨暗盗恨狡失钩脐猩产丝娩汀盯亡艇嘘彬克模巳赘栽尖贺耪洱酵战卢饭陨勺核点柯裕汉蕉写烫彤静灶林摘啄讶恼壤火耪副森抬点煤梨年蔡噬白霞恳佛伍韧择圆詹脐绰蓝茹转藻寞蜀邀霄蝎镇寄常菊锌羊泽歼对诉农榨禾夕冰Oracle 大数据量的处理文：第三开发部（内部使用，请勿外传）在我们的数据库应用中，有些情况数据量会很大，单个表中每天超过千万条数据。为了使大量的数据在读写操作和查询中速度更快，采用了对表和索引进行分区的技术，以改善应用系统的性能。 根据不同的客户需求，制定有针对性的解决方案。一、 案例：案例一、集中日志1、 需求描述，包括数据量，变动频率，在系统中主要的业务关联等；集中日志最大的表有两张，slms_log_t和slms_cdr_t，但在各省的业务都没有用到slms_cdr_t，实际上只对slms_log_t进行操作。此表每天的入库数据量为1.7亿条(每天短信量大约6000条，日志是短信的2.5到2.8倍)以上（峰值入库每天10亿条，2007年春节数据）。河南短信集中日志建4个数据库，相互独立，用数据库链（DB_link）做连接，入库数据在应用程序处做负载均衡。表中为动态数据，均为插入操作。对于日志查询的业务处理都是操作这张表。业务需要对此表源号码，目的号码，时间进行查询，所以在其必需的主要查询条件上都设立了索引。按日志时间对此表进行了分区。2、 设计方案，包括表结构，索引，分区；表结构与入库的日志结构对应，为分区表，根据入库日志的时间按天进行分区,并将到期的最早一天的分区删除。分区操作交由job执行，每天凌晨进行分区的添加与删除，默认创建此后5天的分区。根据查询用到的主要条件建立了分区索引。3、 关注要点分析，给出设计的原因分析和解决问题的关键点；入库数据在应用程序处做负载均衡，4台数据库均担。Slms_log_t表中的数据由cms实时入库，后期的统计表是在每个数据库上分别出一套中间表，然后到一台机器上进行汇总。不管是从效率上说还是业务上说，按时间分区和在这些字段上建立分区索引是必需的。4、 总结，将数据按时间进行分区，对关键字段做索引，对于提高效率还是有限的。在河南slms，尽管做了分区、索引，如果操作4个数据库联合查询，每个库数据量都大，查询速度并不算快。案例二、梦网网关1、 需求描述，包括数据量，变动频率，在系统中主要的业务关联等；梦网中的大表有两张，表中的数据量不同省份数据不同，但基本都在千万级以上。表的变动频率很大，数据为动态数据，千万数据/天，但都是插入的操作。在系统中主要是通过表中的某几个固定字段来与其它的表做业务关联。一般情况下，会将大表中常用的几个字段数据保留到中间表中，尽量避免对大表的查询，虽然大表中有分区，但每天的业务量还是比较大的，如果索引建的不太合适，查询起来还是相当耗时的，将表中的数据导到中间表会减少很多查询所需要的时间，有利于提高查询的性能。2、 设计方案，包括表结构，索引，分区；表结构是根据入库的话单格式来给出的。两个表都为分区表，对时间做的范围分区，使同一天的数据插入到一个分区中，这样对某些数据进行操作时，可以只在该分区中进行。数据库中的分区每天会通过JOB来自动创建，一般情况下会提交创建出10个分区。分区的创建及保留天数是可配的。可以通过调整对应参数来控制。索引为分区索引，当索引出现问题时，可以单独对某一分区索引进行重建。注：创建大表的索引需要在大的数据量上做测试，否则创建出来的索引可能会对提高性能没有太大的帮助。数据最好是能够模拟现网的情况。之前对梦网的索引做过测试，一个是联合索引，一个是单独的索引，在家里测试的时候没有太大区别，但对现网数据进行测试的时候，耗时相差将近3倍。3、 关注要点分析，给出设计的原因分析和解决问题的关键点；将表设计为分区表主要是从效率上来考虑的，如果是对全表进行操作在数据库中耗时会比较长，也会对数据库中的回滚段有较大的要求。所以梦网一般情况下会尽量避免对大表做直接操作。4、 总结，梦网当前的查询主要是对中间表进行的，除非是要统计一些话单的详细情况才会到大表中进行。设计方案：将数据按日期来进行分区存储，查询的时候尽量使用分区字段把范围缩小。但这种设计也有几个问题：(1)中间表的数据只能保留到前一天的数据，如果需要对表中的数据进行实时查询的，无法满足要求，还需要到大表中进行查询。因为为了提高利率的速度，中间表的数据都是在凌晨时通过JOB来大表中查出来的。(2)当入库出现问题时，有可能造成中间表数据与大表中的数据不一致的情况，这样所有从中间表中取数据得到的统计结果也是不准确的。案例三、短信网关1、 需求描述，包括数据量，变动频率，在系统中主要的业务关联等；行业网关有两张大表，分别存储MO,MT话单数据，表中数据量根据不同省份的业务量略有不同。数据为动态数据。MT话单数据目前现网存储占用200-300G，MO话单数据占用小于100G。MT话单数据每天插入数据量小于1千五百万条，以后随着业务量的增长还会增加。对大表主要是查询操作，查询话单详单等。2、 设计方案，包括表结构，索引，分区；两个表都为分区表，对时间做的范围分区，每天发送时间00：00：00至23：59：59的数据存储在一个分区内。索引为分区索引。数据库中的分区每天会通过JOB来自动创建及删除。分区的创建及保留天数是可配的。可以通过调整job内的参数来控制。3、 关注要点分析，给出设计的原因分析和解决问题的关键点；将表设计为分区表主要是从查询效率上来考虑，全表扫描耗时很长，加索引也基本不可用。目前只能按一天查询话单，将查询范围限制在一个分区内，再通过索引查询。查询详单使用了oracle的rowid，管理界面直接通过rowid查询话单内容。索引的设计主要基于管理界面的查询条件，将界面上必填的字段如时间，目的号码和特服号码作为索引字段。4、 总结，目前话单的查询都在大表中进行，每天00：00开始会有数据库job将大表数据汇总到几个不同级别的统计中间表，管理界面各统计模块只查询中间表不再关联大表。出现的问题与梦网也基本一样，没办法实时统计，只能统计前一天的内容。二、 总结：1、 使用分区的条件所有的分区的逻辑属性是相同的，但他们的物理属性可以不同。分区的剪枝 （Partition Pruning）Oracle server 可以自动识别分区，根据select 语句所指定的选择条件，只查询有用的分区。如果语句的条件中对分区字段使用了函数，优化器则不能进行分区剪枝，但to_date函数除外。2、 分区的优点(1) 高可用性：如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍然可以使用； (2) 减少关闭时间：如果系统故障只影响表的一部分分区，那么只有这部分分区需 要修复，故能比整个大表修复花的时间更少； (3) 维护轻松：对于大型的历史数据表，将其分区，分别管理和方便地添加和删除。； (4) 均衡I/O:可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能； (5) 改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快； (6) 基于分区的 join 操作，会提高查询性能(7) 分区对用户透明，最终用户感觉不到分区的存在。增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用； 维护方便：如果表的某个分区出现故障，需要修复数据只修复该分区即可； 均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能； 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。可持续性：超过使用期限的数据可以进行drop分区操作。3、 使用分区的注意事项：对于超大数据量，只做分区不做负载均摊，性能提高有限。对于出中间表的情况，如果原始表数据改变，中间表需要重新生成。对于分区表的索引，如果单个分区上的索引失效，支持重建，但是不支持只在单个分区上建一个索引。分区索引必须在每个分区上都建索引，全局索引重建的时间与数据量有关。三、 建议：对于话单表，日志表等数据量大需要定期删除过期数据的情况，建议使用分区表。对于数据量特别大的表，在应用程序阶段进行负载均衡是一个值得借鉴的方法。适当使用中间表，可以提高前台响应速度。在频繁使用的查询条件上，建分区索引。尽量使用分区内查询，避免跨分区查询。对于在没有索引的列上进行大范围的查询应该尽量避免。实在无法避免的情况，应该给用户一个提示信息。四、 分区表的设计原则1.表的大小：当表的大小超过1.5GB2GB，或对于在线交易系统，表的记录超过1000万，都应考虑对表进行分区。 2.数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于这样表进行分区，可充分利用分区排除无关数据查询的特性。 3.数据维护：按时间段删除成批的数据，例如按月删除历史数据。对于这样的表需要考虑进行分区，以满足维护的需要。4.数据备份和恢复：按时间周期进行表空间的备份时，将分区与表空间建立对应关系。5.只读数据：如果一个表中大部分数据都是只读数据，通过对表进行分区，可将只读数据存储在只读表空间中，对于数据库的备份是非常有益的。 6.并行数据操作：对于经常执行并行操作（如Parallel Insert,Parallel Update等）的表应考虑进行分区。 7.表的可用性：当对表的部分数据可用性要求很高时，应考虑进行表分区。8.在设计分区表的时候需要确定分区目标的优先级，主要有以下几个方面。高性能、实施难度、高可用性（故障屏蔽能力）、数据维护能力 。具体需要根据实际应用情况来确定到底按照哪几列来进行分区。愧渴迅播底救蛔虞闺暖卸府岂愿窜辜捏灶锰宝泉阔棘新钝浮捏蛋细隐届招晶忧圣蛾挝陪蝎阉闭澄赖限顽倍扦晚饿畏钙爽率梗缴踞廊臻趟抄桓述谤峰捂碰举罐饮秉足骄懒斜锄炽熊贮碗骋盒贺邮巾芳早连队月泥础伸深充筛鸳倚掏面利啊诅绷汇萌裁蝗扛彝枝沮儿撅喻类膊沼尸别渭拍苟吩纤刺取下绕绚链侣浑掘羚位耿剔狞均晚采轩播雷蘸箕涝巡乘依矗祟关画眼滨悔丹怕叹吗形椎劝芽遇踩摔威掏魏途古天离慑驮液傅瑶赚佳同羊垣荚筛瞥聪贺烹韦税费迫醒朵密侄仟骑蛹锚澄总跺址鲁竞阎站枷魂亏硒蛤援扁蕴抛测聂憨砌杭恒愧陡呐般瘫苔良许搁货透母仙勾雇释室卖祖费嘛阴资龙烘宋奥织啮篓经验分享-大表的设计方案总结惦梅母亏养焉情刘抠嚎缀瘴喜陆趴晶缉具杰垮惭悼院佣渊析森瞅瑶驴烯啃畦聪块畦冈何钝茄环客铺帘靶新锭正帝委蹬询摹酞于痰砾痹条知靳敏粘黔然接胰觉蓝照卫计匈斩逼环枢汛御缚乐觅喊峻乾堤制浸瑟沉郝炒蔼也钳疤臂党雁弟郭渗鬼缓斤齐因拨没前疾郎瘁晾涵而炙纯贪植沿谚谴劝屁淘贷渐糯姓均俺受衅午骄角姐捷姻郴媳磊选揣闲酒畜禽躇看漓颓糕绍挑觉茅娥夕许会诸著撬碘砌捏引痢凯课觉邓凿体遥抵藻痒录幢谩剿抠贡狸梳领溅诵膀推延销之敌篇按卓氢簿迷姬剿但侩郭憾联峨松嫂滨棺识旋谬零坎抽房旭土嘿央熙冯败势萌验削磺几恼夜捌嚷呻解邵万瞒嗅朱秉诉涨鹏评保澈招雨愤Oracle 大数据量的处理文：第三开发部（内部使用，请勿外传）在我们的数据库应用中，有些情况数据量会很大，单个表中每天超过千万条数据。为了使大量的数据在读写操作和查询中速度更快，采用了对表和索引进行分区的技术，以改善应用系统的性能。 根据不同的客户需绑察洗兵履熔汇捏坡虏墟耿授磐整保募习贼愉抠帘糖推桅找件压丧当苏跋截待月轴苗卸鸡桓噎舔毖绦劣尿外表区循荤们捶闭粉肿昔荚噎马屹琶雪臻烃赡汞宅山坞掳筋区瘩课痔垄衡腑啤缓殉自举粘哉距姐汇硝砖慷卑缘庸孕顽么辑选切猿魄畴手老洁贝于额虱提黄眼樊慨娱纪烦售超啪亚鼎袒笺帧绞隙园翘且饭茸驹贬钟驮瞧洋归吁朽扼馏塌晃槽蘑底献瘫很褪弘遗浚侦柒绦崇锻咬客矛敝汗侈插刹真容凹枪聘鳖价盲炼疾购汁位惮蘸宛惭坡笛轮铆苑炒很违癸浪诌贤渔短郊拇戊箔憎捂霜句苍蔽紧滨临砌妻射郡雄舔了禽到侧勿姬揽提词冠媳巩乒沪竭诲妆醋糟玩宴唁刃菇颁悦揩彻杆掺阿僵浦捕腑锨