数据库大表设计方案总结

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.在设计分区表的时候需要确定分区目标的优先级，主要有以下几个方面。高性能、实施难度、高可用性（故障屏蔽能力）、数据维护能力 。具体需要根据实际应用情况来确定到底按照哪几列来进行分区。