卢涛：大量数据汇总查询的优化方法
DataIndex■◆■■■★，用于表示不同行的数据■■◆◆，相当于行号，其中取值-1表示表头■■◆★◆、表尾信息★★■■，因为每个普查表只有一个表头★◆■■★，一个表尾，所以只要保存一次。
物化视图（有时也称作快照）通常用在一个数据仓库环境，用于预计算与保存大的连接和聚集（汇总），目标是在工作时间避免这些耗时操作的相关开销，加速对数据库的查询。比如■◆，如果我们每次查询个体户的汇总数都从基本表重新生成就会产生大量I/O并消耗CPU和内存资源★■■■★，势必影响其他用户的查询◆★★◆。
物化视图的■★★★■■“物化”的含义就是实际存储了数据，而不是如普通“视图★■”只存储了查询SQL的定义，也就是说，它是一个物理表。但物化视图不是普通的物理表，首先它是可以根据参数定义实现自动刷新的★■■■，其次，它是可以被数据库查询优化器自动识别并判断是否能用于一个SQL查询的◆◆◆◆◆★。后一个特点在数据库术语中被称为“查询重写■★”★■★■，即◆◆★★★，我们提交的SQL查询虽然是针对基层表或基于基层表的视图的，但是整个查询的结果或部分结果在某个物化视图中已经存在★◆◆★，数据库查询优化器通过计算，得出从物化视图获取数据成本更低的结论，它就改写了我们提交的SQL，把它重新写成对物化视图的查询，否则仍然执行对基层表的查询。当然这一切都是后台自动化操作，不需要提交SQL的人干预，这就是它与上一节的第2、3种方法的区别。上一节的第2、3种方法我们必须明确是对临时表的操作。
2.查询的语句如果是把物化视图内容作为子查询,物化视图不用包含grouping_id函数。
既然我们已经了解了存储结构中存在的问题，那么下一步该如何改进也一目了然了。从上述分析可知，每个普查表只有一个表头，一个表尾■◆★■★★，和表体的若干行记录■■★，记录个数不定◆■■。我们可以将表头，表尾信息存储在一个数据库表，记作613m，每个调查表占数据库表的一行。表体各列，包括字符型和数值型指标，存储在另一个数据库表，记作gt，每个调查表的一行占数据库表的一行◆■★★◆，这2个表之间通过SurveyObjectId关联★■■★。2张表均废除完全只有一个值的ReportPeriod列。
方法1在需要频繁查询的场合是不可取的，理由是查询基层表的代价非常大，如果多次执行★★◆★，会耗费大量的计算机资源和时间◆■★★，正如前文指出的，其中大部分是不必要的重复。按照这种思路实现，统计业务人员每次查询都要等待较长时间★■。方法2比方法1前进了一步，但是除了最细的分组组合可以直接查询，其他组合仍然需要对临时表作分组汇总★★■★◆◆，也有一些运算量。方法3单独执行的运算量最大■★★★■■，但把结果保存以后，其他组合无需作分组汇总直接就能查出结果★★■，缺点是分组较多时执行比较慢，保存结果占用的空间也较大◆■。我们应当根据不同的要求尽量采用后2种方法，避免采用方法1★★★★★◆。下面是3种方法的语句及执行时间对比：
将已经存在数据库表中的数据转换为我们重新设计的格式最好通过数据库提供的功能来进行。用软件的第三方程序设计语言处理方式是低效的，因为它包含了取数、重组◆★■◆■◆、存数多个步骤，每个步骤还包含了数据库内外数据类型转换，应用软件和数据库的数据传输等过程。而数据库提供的功能只在数据库内部即可完成■◆◆★。为了尽可能减少对原始表全表扫描的次数就得到所需结果，我通过编写如下SQL语句实现。实际转换用时共约1小时，尽管编写、测试这些语句也花了不少时间■■◆，但这是非常值得的★◆■★，为下一步工作打下了良好的基础。
说明：方法3的第2种实现用了cube关键字，比第1种的grouping sets多了县级与省级的交叉分组★★◆■，如果统计业务人员不需要这个，则产生了多余的记录■◆★■，影响下步查询◆■■■◆★，但它本身的生成比grouping sets快★◆■。
数据库的高级版本已经提供了内置的“物化视图★◆◆★”功能来帮助实现这方面的需求。
软件采取这种表结构的主要目的是通用性和灵活性，只要改变ModelId就可以适应指标的增加和修改，也不需要修改应用软件。但是，这种结构包含了很多冗余数据，在实际应用中有下面的缺点：
综上所述■★◆◆，通过建立多个物化视图，再建立几个有可能“查询重写”的视图★■■，我们可以实现多用户实时快速查询汇总表，当然如果还要进行特定主题的资料开发■◆◆★◆，还是需要有针对性地建立更多的物化视图或视图◆◆◆★◆。
每次输入一长串SQL语句很麻烦◆■★，也容易出错，我们可以用★■★■◆“视图”来保存SQL语句◆■，把对物理表的查询用对视图的查询代替，来达到方便操作的需求◆◆。但视图只是简化了表达，实际操作和定义它的SQL语句是完全相同的，该有的对物理表的操作一步也不少◆■。
方法2，对划分最细的组合的分组（比如县级和行业小类）用一个SQL语句查询基层表实现，并把结果保存在临时表★★，其他分组基于临时表分别用一个SQL语句实现◆★■★■。如果基层数据改变，需要重新生成临时表。
需要注意，基层表上增加了物化视图日志后，它的每步操作的开销会显著增加，因为要记录2次★■■★★，既记录在原始表上，又要判断是否需要并记录在物化视图日志上，如果是大批量数据插入或删除，其性能会大幅度下降。因此，在这样的操作前，要及时关闭物化视图日志的刷新，等批量操作完成后◆★■★◆★，再完全刷新物化视图。关于物化视图的进一步内容，可参考中国统计出版社的《第二次全国农业普查数据处理aPRAS软件技术手册》277页。
转换后的613m有数十万行，gt表有数千万行◆■■◆★★。2张表总共占用磁盘空间约5.4G，比原始表“瘦身”80%。
前面提到■★★◆★■，如果基层表发生了变化★◆◆■■，方法2和方法3需要重新生成临时表。但究竟基层表什么时候发生变化，变化了的部分是否影响汇总结果，并不明确，如果采用保守的估计，每次重新生成临时表，方法2和方法3就退化为方法1，甚至效率更低于方法1，如果采用乐观的估计，有可能临时表的数据是过时的，由此产生的进一步查询结果也是错误的。我们陷入了要数据正确还是查询快速的两难处境。
软件中它的存储是这样的★■■★◆，V表存储除期末从业人员数的其他指标，N表存储期末从业人员数。表的各列含义如下■◆◆■◆★：
有些不十分熟悉SQL语言的统计业务人员有时会提出类似下面的要求：将613表中全体个体户和从业人员数按地区、行业大类分组汇总出一个表，符合有营业执照条件的个体户和从业人员数按地区、行业大类分组汇总出一个表，符合办理了税务登记证条件的个体户和从业人员数按地区、行业大类分组汇总出一个表■■■■◆。数据管理人员接到这样的需求■★，如果只是机械地照搬，用3个SQL语句来做★★■，其中每个SQL语句限定不同的条件，也未尝不可，但是这么做需要对报表扫描3次才能得到结果，每次扫描还需要在gt表和t613m表之间建立关联(也可以不关联，将普查小区信息也存在gt表★★，但会产生新的数据冗余，因为普查小区信息对区内个体户是相同的)，而关联对数据库而言是比较昂贵的操作◆★■■★◆。这些扫描和关联重复进行是对计算机资源利用率不高的体现。对SQL持反对意见■■◆，赞成用第三方程序设计语言处理的人们会把它作为SQL不行的论据，幸运的是，SQL的CASE WHEN结构为解决此类问题提供了较好的办法。
统计业务人员对分组的要求并不是固定的，按地区分组有时候要求汇总到省一级★■◆◆■★，有时要求到县一级，按行业分组，613表只能划分到门类和大类，611表能划分到门类和大、中、小类。每种分组还会要求与其他分组进行复合（交叉），比如同时列出分省并且省内分行业大类的数据◆★◆★。
至于为何不采用一个INSERT ALL语句实现同时插入2个表★■■★◆，主要是考虑到在影响不大的情况下◆★★■★★，尽量少采用数据库的特性，有利于读者掌握一种更普遍适用的方法。
1.浪费存储空间★★。调查表中一行包含多少有值指标就要产生多少行的记录，每行包括ReportPeriod（全都是一个值），SurveyObjectId（30个字符），DataIndex，ModelId◆■■★★，和Value◆◆◆，全国数千万个体户在V表产生了数亿个记录，占用磁盘空间约28G, V表和N表共占30G。
按以往的经验，语句越复杂◆◆■★◆◆，处理的数据越多，就越费时，有时一个复杂语句反而不如多个简单语句执行起来速度快★◆■■◆★。因此，必须经过实践检验。有了上面两种方法的时间对比■★■■，CASE WHEN方法仅比单独一个WHERE方法的语句慢1/4-1/3，现在我们可以放心利用CASE WHEN结构的语句了。
而含有聚集函数的物化视图如果要求在基层表数据增删改时能够快速刷新，还应符合下列限制条件：
2.读取信息不便。由于每一个体户的信息分散在若干行，首先要根据DataIndex筛选出这几行，再对这几行查找ModelId才能确定指定列的值■◆★■■◆。
在全国第二次经济普查的单位清查数据管理工作中◆★，经常接到统计业务人员关于快速查询基于大量数据的汇总的问题。经过反复实践■◆■，我采用了以下的查询优化方法◆◆，较好地满足了统计业务人员的要求。
方法3◆★，用一个SQL语句查询基层表一次性实现要求的各个分组（及其组合），并保存到临时表，根据需要提供直接查询■★。如果基层数据改变，需要重新生成临时表。
Value，真正的数据，比如第一行填写个体户姓名是“张一◆★■★★◆”，则在V表中记录一行DataIndex取值1，ModelId取值3，Value取值◆◆★◆■■“张一◆★”★■★★。
单位清查软件的原始数据库表结构对于数据表采用了 “纵表”的存储方式。普查表里的一行多列数据被划分成若干行，按原表各列数据的数据类型不同分别存储到V表（字符型指标）和N表（数值型指标）■★◆，而没有数据的指标则不存储。
下面示例显示采用了CASE WHEN结构的语句(第1行)相当于3个没有采用了CASE WHEN结构的语句(第2-4行)。WHERE条件限定参与运算的记录，而CASE WHEN结构对每个记录进行条件判断得出不同的值★■。一个SQL语句只能有一个WHERE子句，但可以有多个CASE WHEN结构，这就实现了1次扫描计算多个条件的要求。
3■◆■◆◆■.查询的语句如果是把物化视图内容作为查询语句的一部分,同时物化视图包含rollup、grouping sets或cube★★◆★◆,物化视图必须包含grouping_id函数,函数参数要包括group by引用的列和表达式■◆◆■。
现在我们按上述条件建立物化视图，并更新一行数据◆◆★★，查看汇总结果的变化及运行时间。
3.查询性能低下。比正常表格更大的存储空间和更多的读取信息步骤以及由此带来的审核、汇总算法的复杂性决定了要耗费更多的计算机资源和时间处理这些数据，并随着数据量的增大■★★◆，所需资源和时间也呈线性增长，性能问题在县、地级尚不甚明显★■，省级已经非常显著，而在全国就令人难以接受了■■★。
min(case when◆■◆★◆..)语句的含义是将分散在各行的指标按指标代码重组到1行的各列，在此不赘述，本文第2部分对CASE WHEN结构还有进一步的描述。
我们将不同地区的表体数据按照分区分别转换的理由是单个语句处理数据量太大，耗时较长◆★■◆★■，容易因网络通信中断而执行失败■■◆★★★，失败了就要完全重来，而把工作拆成多个部分，处理数据量减少，需要的时间短了，失败的概率会降低，即使某个语句执行失败◆■■■◆★，也不影响其他已经成功的语句，能最大限度地节约时间■★★■。
ModelId，指标id，取值1-22★★，分别对应是表头★★、表尾的10个和表体的11个指标。
所以■◆■◆，我们可以记录下上次汇总以后发生变化的基层数据◆■★，根据记录判断是否对某个汇总表的结果产生影响★■★■，如果不影响，则直接输出上次保存的汇总表的结果■◆■★，如果有影响■■◆★◆，则重新对此汇总表进行计算，这种计算可能是局部的◆★★■■◆，也可能是全表的，视变化的数据量和不同计算的开销而定◆★■★。显然■★■■，这种复杂的操作全凭手工是不可行的，要用编程来实现★■。
有没有方法可以解决这个两难的问题？首先来分析应对这种需求怎么人工处理。理想的状况是只对变化了的部分进行重新汇总查询◆■◆，这样运算量比全部重新汇总小很多★■◆◆■，数据库中基层数据的变化有3种■◆★★，新增、修改和删除。新增和删除必然会影响到所有的分组，比如我们新增一家位于某省的行业代码为11的单位，那么该省和全国合计，这个行业和各行业合计的单位汇总数据应加1，同理，删除一家这样的单位，相应的汇总单位数就会减1，如果把某省的行业代码为11的一个单位的行业代码改为16，那么这个省和全国合计单位数不会变化，但该省和全国的行业代码为11的单位汇总数据就要减1，同时该省和全国的行业代码为16的单位汇总数据应加1，如果一个单位同时修改行政区划代码和行业代码，修改前后的省区■★■◆★、行业的单位汇总数据都会受到影响，但如果一个单位只是修改法人名称◆■★★◆★，那么所有的汇总数据都不受影响。如果变化的数据不是1行而是多行，一个单位修改的列涉及多个分组条件，也能以此类推地处理。
我们看到，对视图vi_xian的查询被数据库查询优化器重写为对物化视图MV_XIAN_NEW的查询，查询速度非常快。而且记录修改以后，汇总数据也得到了快速更新，结果与从基本表查询完全一致。
尽管编程从思路上分析是简单明确的，记录基层数据的变化可以通过数据库提供的“触发器”实现，但具体实现起来★■◆■◆，编写工作量和难度都是很大的，稍不留神就可能出错或者锁定了资源★◆■◆，对性能产生负面的影响。所以不到万不得已，没有其他的选择时不采用编程■■★◆★■。
