经济普查全国数据库优化方案
随着各省、自治区、直辖市（以下简称：各省级单位）的第一次全国经济普查（以下简称：经济普查）数据上报工作接近尾声，国家级数据处理工作正大规模地展开，经济普查全国数据库的建设也被提上日程。国家级数据处理的主要任务包括下面几项：
1给各省级单位报送的数据建立处理环境，执行统一的审核、汇总程序，并将结果与同时上报的审核错误清单和汇总数据进行比较，如果两者不同或有其他问题，通知原报送单位重新报送；
2将各省级单位报送的数据合并到一个处理环境中，执行各专业要求的审核、汇总程序，并由各专业做进一步的审核、查询得出最终确定的数据集。将来在此数据集基础上可以构建全国基本单位名录库和其他专业的全国数据库，提供给各级政府统计部门、其他政府部门和科研机构使用，即建立经济普查全国数据库。
3按处理地从全国处理环境中合并导出各省级单位数据并建立独立的处理环境，再次分别执行统一的审核、汇总程序，并由各专业确认无误后反馈各地区。
国家级数据处理的流程和省级、地（市）级没有本质的差别，国家级和省级处理的最明显差别是数据量上的差别，填报目录（法人单位＋产业活动单位）记录超过了700万条，其他30余张专业基层表的记录从几十万到数百万不等。因此，实现快速地从如此大容量的数据库中提取数据（查询）、分析、统计以及提取数据后进行数据展示，已成为亟待解决的难题。
经济普查数据汇集到国家级的时候，数据库的性质已经逐渐地发生了改变，从一个联机事务处理（OLTP）系统转变为一个决策分析支持（DSS）系统。联机事务处理系统有大量的用户同时连接，并发操作很多，有大量的数据增删改，而每次更改涉及的记录数较少，对系统的响应时间要求较高。决策分析支持系统是大数据量的查询，大批量的数据导入和导出，涉及的记录数很多，对系统的响应时间要求不太高，但是对一个长时间操作耗费的总时间要求提高。
由于两种类型系统应用特点的巨大差异，在联机事务处理系统中有效率的设计在决策分析支持系统中变得不再有效率，需要进行分析、调整、优化。
一、减少数据冗余
在数据采集阶段，调查对象的数据的一些统计特征，例如某专业基层表的填满率，数据量地区分布等是未知的，尽管可以从历史数据中获得某些信息，但全国的统计特征信息不一定适用于地方，因此数据采集系统中不需要考虑数据的统计特征。数据汇集到国家级后，即使个别数据还会进行订正、增补，但总体来说，数据的整体特征已经固定，不会有大的改变。为了提高进一步处理的效率，就得针对既有数据的统计特征进行数据结构的调整，其中最首要的，是减少数据冗余。所谓冗余数据，有两种含义，第一种，是指在数据库中多个地方重复存储的数据，第二种，指的是基层没有填写，而由于应用程序设计的原因在数据库表中填充并遗留下的大量空白。减少数据冗余并不应该随着硬件系统处理能力、运算速度和存储容量的提高而被忽视，相反，重视并减少冗余更能发挥硬件系统的能力。
通过对几张定长二维表的统计，我们发现它们均存在第二种冗余，冗余的比例从60%至80%不等。以规模以上工业企业能源购进、消费及库存表的二维子表(下面简称606表)为例，参加填报的单位约有27万，共530万条记录，而其中至少一个有效字段(不包括uuid和数据项行代码)有数的记录仅95.4万，冗余比率达到了82%。而恰恰是606表，其导出文件长度和导入耗费时间均列第一批上报的各表的首位。经过测试，我们用数据库的SQL命令删除冗余记录后，应用程序的执行没有发生错误，而无论是审核、汇总、导入、导出还是查询时间都大幅度下降。原因有以下几方面，物理存储数据块的减少使I/O访问的次数减少，记录数的减少一方面使表扫描行数和叠加计算的次数减少，另一方面使索引文件的长度变小，维护开销降低。
也许开发人员会提出异议，606表在业务规则中是定长二维表，删除冗余记录后就变成了不定长表，这不是违背了业务的需求？这种担心是有道理的，但不是不可解决的，我们完全可以在数据展示上给用户呈现一张定长二维表，后台存储格式是用户不关心的，但对应用程序的执行性能却是关键的。事实上，ePras程序已经做到了将不定长表存储格式数据展示成为定长二维表。只是按不定长表存储定长二维表在数据导入时需要和不定长表一样考虑空行覆盖等问题。
606表产生如此巨大的冗余，这是由企业生产经营情况决定的，大部分企业都只购进、消费及库存了22种能源的少数几种，这个比例就是1减去上面给出的82%，即18%。
607，612，621表也都存在和606表相同的第二种冗余，可以用同一种办法加以优化。
除了定长二维表，不定长表也存在数据冗余，不过主要是第一种，其影响也不如上述各表大。以规模以上工业企业产品生产、销售、库存表为例(下面简称603表)，603表的字段设计完全与表样一致，除了保存产品代码外，还保存了产品名称和计量单位，实际上，产品代码本身就涵盖了产品名称和计量单位信息，在工业企业产品目录中一一对应。ePras程序也正是通过检索录入的产品代码，从产品目录中取得对应产品名称和计量单位填充到相应的字段的。这种冗余在数据采集阶段，对数据展示有一定程度的帮助，不必每次显示每个产品都查表了，能提高显示速度。但对于国家数据处理，上文已经提及，只对个别基层数据还会进行订正、增补，显示的响应时间不是那么重要，何况，产品代码在603表和产品目录中都是主键的一部分，数据库管理系统会利用索引去取数，效率也不低。当然，汇总表中产品数量巨大，保存这些冗余信息，以空间换取时间还是值得的。
如果我们在603表中不存储产品名称和计量单位，将可以节省1/5的存储空间。我们用SQL命令更新冗余字段后，经过测试，应用程序的执行没有发生错误，而无论是审核、汇总、导入、导出还是查询时间都大幅度下降(对比见下表)。
605，611，613表也都存在和603表相同的第一种冗余，可以用同一种办法加以优化。
二、改变主键
采用uuid作为主键或主键的一部分的初衷是为了解决基层数据重码覆盖问题，但到了上报阶段，各省上报的数据在省内都已经消除了重码，采用处理地行政区划码+单位代码的方式完全可以保证唯一性。这里的“+”号表示将多个字符串合并后填回uuid字段，而不是指用多个字段组合作为主键，因此uuid存在的前提变得不再成立，而随着记录数的扩大，由uuid的产生算法带来的随机性、长度冗余在查询中的负面影响日益严重。
主键的随机性使得大批量导入的时候维护索引的开销巨大，而32个字符长度包含的信息又很有限，它跟业务无关，仅仅是区分一个填报单位，成本和回报不匹配。主键的重新设置有多种选择方案，一种是上文提到的处理地行政区划码+单位代码，这种编码的优点是对于分地区查询、汇总很方便，缺点是分专业处理不方便，我们也可以采取行政区划码+单位所属专业代码+单位代码的编码，这种编码对于分地区处理、分专业查询、汇总都很方便，缺点是代码长度更长，有不符合关系数据库规范化要求的地方。我们应针对业务的不同需要，采用适当的办法重新设置主键。经过改造以后的主键将可以脱离main_table，J601，J602的束缚，在查询的时候直接分地区或分专业处理专业基层表。
更改主键的一种应用是抽取一个居委会或村委会的数据，过去我们只能先根据J601，J602的单位所在地行政区划码查出相应的uuid集合,再从各个基层表查询包含在uuid集合中的uuid单位的数据，SQL语句如下：（以603表为例）
select b.* from J601 a,J603 b where a.Z01_03 = 230101001001 and a.uuid=b.uuid
而现在只要对各个基层表的主键进行条件查询，SQL语句如下：（以603表为例）
select * from J603 b where a.uuid like 230101001001%
更改主键的另一种应用是给各地反馈数据。国家要给各省级反馈处理地属于该地区的数据（也即是国家审定的该地区上报的数据），如果用应用程序来做，需要3个步骤：1.筛选出属于该地区的填报目录（main_table），设为表A；2.将各基层表和表A通过uuid产生关联；3.输出关联成功的单位。而通过Oracle的exp实用程序来做，只需要在命令行加一个参数query=where uuid like 23%即可。
更改主键的第三种应用是给表分区创造了条件，实现表分区后，某一分区的数据可以独立地添加、删除、索引而不影响其它分区中的数据，方便了对特定分区数据的批量处理。
批量改变全国数据的uuid，我们可以通过下面办法实现。
1.构造uuid和新id的对照表idtab并设uuid为主键。
2.更新每个基层表和填报目录的uuid,注意填报目录还要对产业活动单位的par_orgn_code进行替换，否则产业活动单位将和所属法人单位失去关联。
改变uuid的实质是让各表的主键具有更多对统计活动有用的信息，这种信息原本来自多个基层表，用空间换取时间，达到减少重复取信息、加快处理的目的。
当然，批量改变全国数据的uuid是一项耗时巨大的工作，但是正如谚语所说的“磨刀不误砍柴工”，我们对主键一次性的处理以后，为将来的工作带来极大的利益。由于我们保留了uuid和新id的对照表，如果有必要也可以很容易查到某单位的uuid。
三、清除垃圾数据
垃圾数据指的是那些由于各种原因应该从数据库中删除而未删除的数据，包括下面几种。1. main_table中有，而其他基层表中没有的单位，2. main_table中没有，而其他基层表中有的单位，3. 基层表子表中有，而其对应基层表主表中没有的单位。4. 按统计制度的要求,属于某个专业没有填应该填报的调查表,或者填报了不属于本专业的表的单位。
这些数据遗留在数据库中，影响了汇总审核的正确性，有时候还会引起完整性约束方面的问题，产生插入、删除异常。
查找并删除第一种垃圾数据的办法是：
将main_table和相应的基层表按照uuid做左外连接，附加应填报此表的专业代码和行业代码等条件。基层表的uuid为null的main_table 中剩余的uuid即为所求。
查找并删除第二种垃圾数据的办法是：
将main_table和相应的基层表按照uuid做右外连接，附加应填报此表的专业代码和行业代码等条件。main_table 的uuid为null的基层表中剩余的uuid即为所求。
查找并删除第三种垃圾数据的办法是：
将基层表子表和相应的基层表主表按照uuid做左外连接。基层表主表 uuid为null的基层表子表中剩余的uuid即为所求。
垃圾数据被清除后，系统中数据关系更加合理，有利于保证各种计算、分析结果正确。
四、相关人员的作用
数据库系统的优化是一个长期动态的过程，需要符合不同阶段的工作需要，系统设计应满足而不拘泥于业务需求，充分认识性能和效率问题的重要性并在应用程序中实现。
业务高级管理人员负责制定并重新考察业务规则和流程，从而为应用设计提供一种清晰而适当的模型。他们必须确定规则和流程的特定类型而这将影响到整个系统的性能。
应用设计人员必须绕过潜在的系统瓶颈进行设计。此外，他们还应当与系统设计人员进行交流，从而使得每个人都可以理解应用的数据流。
应用开发人员必须与其所选择的实现策略进行充分的交互，使得其在进行语句优化的时候可以顺利地较快确定模块和SQL 语句。
数据库管理员必须仔细地监视系统的活动并将其归档，以此来识别和修正异常的系统性能。有时还需与系统管理员就系统配置进行交互从而方便其他人可以高效地设计和管理系统。
最终用户和二次开发人员也有责任用适当的方法来使用经济普查全国数据库，我们应遵循下面的原则：
1 限定（尽量缩小）查询的范围，例如：在销售额超过10亿的商业企业中查询全国销售额前100位将比直接在所有商业企业中查询有效率。
2 利用存储的中间结果，例如：汇总不必每次对所有基层单位，可以利用粒度较细的分地区、分行业的汇总结果得出更加宏观的数据。
3 避免经常地导出、导入大量数据，如有可能，可请求开发人员或数据库管理员配合在后台直接存取，绕过中间层的开销。
总结
本文论及的优化方法都是在保留现有的数据结构不变的前提下进行的，这些优化可以使应用程序不作修改或作少量修改就能提高性能，而如果要做成一个真正的决策分析支持系统，还需要对用户的需求作详尽的分析，重构一个适应决策分析要求的系统。
参考资料：《经济普查数据上报准备工作》(梁卫华)
《经普项目中基层表物理存储结构的变化》(卓光)
《Oracle 8i 性能设计与优化》（北京希望电子出版社）
