相似度算出来的结果却相差很大。当维度更大时，这种差距会更大。为了使结果更符合直观感受，我们使用异或距离来衡量向量相似度，异或距离是两向量对位不同的维度数。比如上例中

需求这种机制下还易于实现真正的湖仓一体。湖仓一体要求能存能算，既能充分保留原始数据又具备强计算能力可以充分发挥数据价值。但使用数据库实现湖仓一体会面临只能算不能存（只能

从一体机、超融合到云计算、HTAP，我们不断尝试将多种应用场景融合在一起并试图通过一种技术来解决一类问题，借以达到使用简单高效的目标。现在很热的湖仓一体（Lakehouse）也一样，如果能将数据湖和数据仓库融合在一起就可以同时发挥二者的价值。数据湖和数据仓库一直以来都有十分密切的联系但同时存在显著的差异。数据湖更注重原始信息的保留，将原始数据“原汁原味”地保存下来是数据湖的首要目标。但原始数据中有很多垃圾数据，原样保留就意味着垃圾数据都要存进数据湖？没错，数据湖就是这样一个数据垃圾场，不管什么样的数据一股脑存进去再说。所以，数据湖面临的第一个问题是海量（垃圾）数据存储问题。得益于现代存储技术的长足进步，现在海量数据存储的成本很低（如分布式文件系统）完全可以满足数据湖存储的需要。但数据光存起来还不行，还要使用也就是计算才能发挥价值。数据湖数据五花八门，各种类型的数据处理方式也不一样。其中最核心也最重要的是结构化数据处理，无论是历史沉淀还是业务新增，结构化数据处理仍然是重中之重，很多半结构化和非结构化数据计算最后也会转到结构化数据计算上。不过很遗憾，由于数据湖的存储（文件系统）本身没有计算能力，没法在数据湖上直接处理数据，想要处理这些数据还需要借助其他技术（如数据仓库），“能存不能算”是目前数据湖面临的主要问题。数据仓库就刚好相反了，数据仓库基于

类场景中通常要使用列存来发挥计算优势，但只有列存是远远不够的，有些复杂计算需要针对计算特点专门设计数据存储形式（比如有序存储、数据类型转换、预计算等）。而这些对性能要求高的复杂计算在

SQL，不要说非技术人员难以完成，即使对于专业程序员也不是件容易的事，常常成为很多软件企业应聘考试的重头戏。三行五行的

数据库语言的目标要说清这个目标，先要理解数据库是做什么的。数据库这个软件，名字中有个“库”字，会让人觉得它主要是为了存储的。其实不然，数据库实现的重要功能有两条：计算、事务！也就是我们常说的

报表性能是个很重要的问题，报表慢让用户体验极其恶劣，可能90%的报表因为计算简单和数据量小都不会有性能的困扰，但是剩下的10%的有性能隐患的报表一旦出问题，就可以毁掉之前所有的美好，不仅是用户体验恶劣，实施方更是会被拖累的苦不堪言，没完没了的安排精锐部队去救火，成本投入什么时候是个头？所以，大家都希望能选一个高性能的报表工具，一劳永逸的解决这个头疼，但报表的性能问题并不是那么简单，怎么去考察性能也就不那么简单了如图，在报表的生命周期中，性能问题大致会出现在两个阶段：1：数据源准备和计算阶段2：报表计算和呈现阶段实际上，大多数报表性能问题会出现在第1阶段，就是数据源准备数据和计算慢，这个环节的工作通常不是报表工具做的，这个锅也不应该让报表工具来背。第二阶段，报表的计算和呈现，这才是报表本身的本领，也是评测对比报表工具性能的要点所以，我们考察对比报表工具的性能差异，就是看报表计算的基本功够不够，而不是看把报表呈现出来的全周期性能下面我们以润乾报表为例，给出用于测试报表工具计算和呈现环节性能的方法和用例，第一个用例侧重于格子的计算，第二个用例侧重于展示效果的渲染和呈现。这是报表报表工具耗时较多的两个环节，也就是可以考察对比出报表工具性能的环节。大家在实际考察的时候，可以根据自身报表的特点，来选择不同的侧重来考察，统计的时间，都是刨去数据源环节，从报表计算开始到呈现截止先看侧重单元格计算的用例和测试过程，格间计算格间计算格间计算就是报表单元格里的计算，也是检验一个报表工具性能的最基本方面，可以设计一个有足够多格间计算的表格，再不断加大数据量，来对比不同产品之间的性能差异测试用例设计原则和方法：报表单个格子和格子之间的计算要多，计算越复杂，才越有可能测出工具的问题不断加大数据量来测试，数据少基本看不出差异，只有数据量大，才会暴露出性能问题，才能考察出工具的能力控制好可用内存，确保每次测试相同时间记录要明确，要略掉数据源读取的时间，只统计报表计算和呈现的时间（如下图润乾报表后台信息中的计算+生成html+呈现完成的时间）我们就根据上面的原则，设计一个报表，去测试对比报表工具的格间计算性能用例：分组汇总统计表数据库表：“销售订单汇总表”，此表结构如下其存放数据为按照“货主地区”、“货主城市”及“雇员ID”对“订单金额”及“订单数”做的分组求和汇总。基于此表数据制作如下报表报表中，除了取数后根据货主地区、城市及雇员分组呈现订单金额和订单数据量外，还在报表内增加了计算各雇员总订单额在地区及城市内排名和占比，以及订单数量在地区及城市内的排名情况，这些都是考察报表的格间计算能力的这个报表在渲染方面没有特殊要求，计算出来的数据直接呈现即可在数据量较小的情况下是看不出来差别的，我们可以通过加大数据量进一步考察结合我们用例的数据情况，在保持“货主地区”或“货主城市”两个分组数据完全固定不变的情况下，通过增加组内雇员人数的方法来增大数据量，让格间计算量变多来看性能变化情况报表的数据集为：select

类软件通常会提供自助查询功能，有些软件还能支持关联查询，但实际使用的大多数还是单表的，也就是我们常说的宽表，而提供的自助关联查询功能则很少被业务人员使用，这是几乎所有

一、问题背景与适用场景在对数据集进行排序运算时，有时会遇到这样一种场景：数据集T已经按字段a有序，而字段b无序，现在我们要将T按a、b排序，我们称之为前半有序(a有序)的排序。此时我们能想到一种优化的排序方法：从T中先取出a值相同的一组记录，再这一组内对b排序。然后再依次取出下一组a值相同的记录，重复这个动作，直到完成T中所有记录的排序。这种方法的好处是不需要对T中所有记录进行大排序，一次只需取出一小组，对内存容量要求大大减低，只需能装下每个小组即可。遗憾的是SQL并不支持这种优化算法，只能所有记录进行大排序，而SPL提供了对这种算法的支持，下面我们实例测试一下，并与Oracle作对比。二、测试环境与任务测试机有两个Intel2670

一、问题背景与适用场景什么是后半有序？如果数据集T已经按字段a、b有序，现在我们要将T按b排序或分组时，因为在a值相同的段内，b都是有序的，这种要排序或分组的字段在分段内有序的情况就称为后半有序。我们知道，快速排序算法的原理就是一种递归的分段排序再归并的算法，对于后半有序这样的已经大体有序的数组，快速排序的速度已经能够很快。所以如果采用快速排序算法对T按b排序后，就可以使用《性能优化技巧：有序分组》中介绍的优化原理进行分组了。SPL提供了后半有序分组方法，我们实例测试一下，并且与SPL和Oracle的hash分组算法作对比。二、测试环境与任务测试机有两个Intel2670

一、问题背景与适用场景在《性能优化技巧：小事实表与大维表关联》中，我们尝试了小事实表与大维表关联时的性能优化方法，该方法利用了小事实表可以装入内存的特点，将关联键汇集排序后到大维表中查找，避免了遍历大维表的动作。如果事实表与维表都大到不能装载到内存时，这个办法就不再有效了，那么，还有什么办法提高性能呢？SQL的方案是对两个表做HASH分堆，拆小到内存可以放下的地步，分别写入外存，然后再分别读入进行内存关联。如果运气不好，拆出来的某个堆还是太大，就需要做二次HASH。同时，两个表都需要做一遍HASH分堆动作，也就是需要把所有数据都缓存一遍。如果维表是有序存放的，我们就可以将平均分段，由于有序存储，所以可以计算出每一段值的边界值，然后再用这个边界值将事实表再分堆。这样，维表本身由于有序存储，可以直接按段读取，而不需要再分堆；只有事实表被缓存出去，也就是只有一个表被分堆缓存，所以这种办法可以称为单边方案。而且，由于维表可以被相对平均分段，不可能象HASH方法那样出现运气不好导致某堆太大的情况，一次分堆一定能解决问题，性能将得到保障。SPL提供了这种关联方法，下面我们实例测试一下，并且与使用HASH

一、问题背景与适用场景通常分组计算都采用hash方案，即先计算分组字段的hash值，hash值相同的记录被分拣到一个小集合里，然后在这个小集合中遍历找分组字段值相同的聚合成一组。分组的复杂度(比较次数)，取决于hash函数的重码率。在hash空间比较小时，重码率就高，比较次数就会多，性能会受较大影响。为了提高性能，就需要分配较大的内存来存放

一、问题背景与适用场景在主子表关联查询中，有时会遇到这样一种情况：按条件过滤后的事实表数据量很小，能够全部装载进内存或仅比内存略大一点；而要关联的维表数据量很大，比内存要大很多。这种时候，如果维表是按键有序存储时，因为事实表涉及的维表记录较少，可以一次性用二分查找方法找出来，而不必象HASH运算那样遍历这个大维表，从而提高运算性能。SPL提供了这种方法，我们实例测试一下，并且与使用HASH

一、问题背景与适用场景在《性能优化技巧：有序归并》中我们见证了有序归并算法提升主子表的关联性能，在集算器中，还有进一步提高性能的办法—附表。集算器组表支持主子表保存在同一文件中，先用主表创建组表文件，再在主表上附加上子表，子表必须含有主表的维字段，并通过这个字段进行关联，这样的子表称为附表。附表在存储时，关联键只需在主表保存一次，子表就不用保存了，在读取时就可以减少读硬盘的时间。并且子表已经通过关联键附加在主表记录上了，相当于已经预先关联好了，和有序归并关联相比，就可以减少关联时的比对运算，从而提高关联性能。下面我们就用实例来对比测试一下集算器附表与有序归并的关联性能。二、测试环境测试机有两个Intel2670

一、问题背景与适用场景在《性能优化技巧：有序归并》中我们验证了有序归并算法提高关联性能的效果，那么还有没有进一步提升的空间呢？能不能再减少数据的读取量从而再提速呢？通常主子关联后还会再施加更多运算，比如过滤。有序归并算法将主子表先分别读出再做关联，当其中一个关联表被过滤后剩下记录少了很多时，另一个关联表也会有大量记录不能关联上，但做归并关联之前并不知道，仍然会将这些不可能关联上的数据读出，浪费了时间。如果我们能根据被过滤表的主键去另一个表查找可被关联记录再来做关联，那样就能省去很多读取动作了。而由于组表是按键有序存放的，恰恰能使我们可以用键值高速定位记录，从而实现这个想法。下面我们就做个测试，与常规有序归并对比一下，看看性能提升的效果，并在测试过程中进一步分析一下提速原理。二、测试环境测试机有两个Intel2670

分段算法。但这会发生外存倒换的问题，数据需要先分段写出再读入，多出一写一读，外存读本来就不快，写就更慢，这样性能会差出很多。有序归并关联则没有这个问题，两个表的数据都只要遍历一次就行了，不仅是

在事实表与维表的关联查询时，常常会遇到需要对维表的数据进行过滤或者针对维表做计算的情况，这时可以有两种处理方式：1、先做关联（如果是内存则可以是预关联），再对关联后的事实表进行过滤。就象在《性能优化技巧：预关联》和《性能优化技巧：外键序号化》中做的那样。2、先对维表进行过滤，再与事实表做关联。我们知道，建立关联时需要维表有索引，过滤之后，原先的索引不再可用了，需要重建索引来产生新的索引。这两种方式孰优孰劣，不能一概而论，应当和维表与事实表的数据规模对比有关。下面我们通过实验来探讨一下这些性能优化技巧的效果。一、测试环境采用TPCH标准生成的8张数据表，共50G数据。TPCH数据表的结构网上有很多介绍，这里就不再赘述了。测试机有两个Intel2670

一、问题背景与适用场景在《性能优化技巧：部分预关联》一文中，我们介绍了将维表内存化并预关联的技术，但事实表与维表关联时，仍需进行hash计算和比对，怎么提高这一步的性能呢？我们今天再介绍一种优化技巧：外键序号化。外键序号化的思路是，如果维表的主键是从1开始的自然数(也就是记录所在行号)，那么就可以用键值直接按行号定位维表记录，而不再需要计算和比对HASH值了，从而加快与维表关联的速度，进一步提升性能。而且，直接用序号定位，还不需要建立索引，占用内存也会小很多。我们下面来介绍如何在SPL中使用外键序号化技巧，并使用上文的测试环境，针对同一个查询问题，以序号化后的数据与之前的数据做对照实验，验证序号化的性能提升效果。二、序号化准备要使用外键序号化技巧，必须要保证维表的主键值正好是序号(记录行号)，而实际业务中维表的主键值往往不是这样，所以要先将维表的主键转换成序号。转换的方法如下：1）新建一个键值-序号对应表，保存维表的键值和自然序号的对应关系；2）把维表的键值替换为自然序号，得到一个新的维表文件；3）把事实表里的外键值修改为序号，修改的依据是键值-序号对应表，修改后得到一个新的事实表。本次实验要用到的三张维表分别是supplier、part与orders，事实表是lineitem，下面分别对它们实现序号化。1.

一、问题背景与适用场景在《性能优化技巧：预关联》中，我们测试了将数据表事先全部加载进内存并做好关联后的查询性能优化问题，但如果内存不够大，不能将维表和事实表全部装入，那怎么办呢？此时，可以将维表预先装入内存，建好索引，实现维表部分的预关联，省去一半hash计算。我们下面再来测试一下这种场景，这次用数据量最大、内存装不下的lineitem表做测试，在SPL部分预关联中，将其它7张表预先装进内存，而lineitem在查询时才实时读入。二、SQL测试依然用

一、问题背景与适用场景SQL中JOIN的性能是个老大难问题，特别是关联表较多时，计算性能会急剧下降。SQL实现JOIN一般是采用HASH分堆的办法，即先计算关联键的HASH值，再将相同HASH值的记录放到一起再做遍历对比。每一个JOIN都要做一轮这样的运算。如果数据量相对于内存并不是很大，可以事先全部加载到内存中，那么可以利用内存指针的机制，事先把关联关系建立好。这样做运算时就不必再做HASH与对比运算了。具体来说，就是在数据加载时一次性把HASH和对比运算做完，用指针方式保存关联结果，然后每次运算可以直接引用到关联记录，从而提高运算的性能。不幸的是，SQL没有指针数据类型，无法实现这个优化逻辑，即使数据量可以在内存中装下，也很难利用预关联技巧提速，基于SQL的内存数据库也大都有这个缺点。而SPL有指针数据类型，就可以实现这种机制。我们下面来测试一下SQL实现单表计算和多表关联计算的差距，再用SPL利用预关联技巧同样做一遍，看一下两者的差距对比。二、测试环境采用TPCH标准生成的8张数据表，共50G数据（要小到能放进内存）。TPCH数据表的结构网上有很多介绍，这里就不再赘述了。测试机有两个Intel2670

TopN是常见的运算，用SQL写出来是这样（以Oracle为例）：select

我们知道，大数据运算性能的瓶颈常常是在外存（也就是硬盘）IO上，因为外存访问性能要比内存低一两个数量级。因此，做性能优化时，减少硬盘的访问量有时要比减少CPU计算量更为重要。同一个任务，如果能使用硬盘访问量更少的算法，即使CPU计算量不变甚至略多一点，也会获得更好的性能。分组汇总需要对数据集进行遍历。同一个数据集可能会按不同维度进行分组，这样原则上就要遍历多次，大数据时就会涉及多遍硬盘访问。但是，如果我们能在一次遍历过程中计算出多个维度的分组结果，那就会减少很多硬盘访问量。可惜，SQL无法写出这样的运算（在遍历中返回多个分组结果），只能遍历多次，或者寄希望于数据库引擎是否能优化。而SPL则支持这种遍历复用的语法，可以一次遍历计算出多个分组结果，从而提高性能。下面我们做一下测试，以Oracle为例看数据库是否会对多次遍历的计算进行优化，以及在SPL中采用遍历复用算法对性能的影响。一、

看看问题S银行POS交易情况报表很慢，业务人员要等待一个多小时（3700秒）才能看到结果，严重影响业务。报表表样如下：解决步骤首先，要理解业务和计算特征。POS交易情况表连接数据库取数，定义了4个数据集，分别对应4个SQL。以POS1

位，如：1234567890，如下图：科目字段的值实际上是一个分层的代码，而前面表里上百个指标就是根据需求对不同层次科目数据的统计结果，具体的做法是通过截取科目的前几位来确定层次，然后按需求

问题说明T银行通过某互联网渠道对客户发放贷款。放款、还款明细数据存放在Mysql中，每天都会增量增长。T银行经常需要执行跑批任务，统计汇总指定日期之前的所有历史数据。跑批任务由Mysql的SQL语句实现，运行总时间7.9小时，占用了过多的跑批时间，甚至影响了其他的跑批任务，必须优化。从数据量来看，截止到2019-05-09，放款表（2千万条数据，9G）；还款表（4千万条数据，16G），两个表每天都要增长几万条数据。跑批用的服务器内存只有16G，并不算大。实际跑批动作由两句SQL构成，下面说明如下：脱敏之后的SQL1语句：select'2019-05-09'

看看问题C银行有个资金**报表很慢，业务人员要等待1分30秒才能看到结果。资金**报表非常重要，访问人员众多，访问频次很高，目前的响应速度严重影响业务。报表表样如下：解决步骤首先，要理解业务和计算特征。资金**报表连接数据库取数，定义了6个数据集，分别对应6个SQL。报表设计如下图：其中：ds1是营业网点，ds2到ds4是资金数据，ds5是当前币种数据。ds2、ds3、ds4的SQL比较类似，是报表的主要数据。ds2做了脱敏之后如下：SELECT

问题描述P保险公司的车险业务中，需要用往年历史保单来关联新的保单，在跑批中称为历史保单关联任务。在提醒老客户续保时需要计算指定时间段的往年保单，例如省级公司需要定期计算特定月份内可续保保单对应的历史保单。目前这类批量数据处理任务是由存储过程实现的，性能差、运行时间长，运行时间随着新增保单的天数成正比的增长。计算十天的新增保单关联历史保单，运行时间47分钟；三十天则需要112分钟，接近2小时；如果时间跨度更大，运行时间就会长的无法忍受，基本就变成不可能完成的任务了。分析解决第一步，理解计算任务特征。跑批的存储过程很长，有2000多行。用到的数据表包括：保单表和保单-车辆明细表。对于较大的省份，保单表和保单-车辆明细表都有几千万数据存量，每天新增保单的增量数据有一到两万条。两个表结构大体如下（因脱敏的需要，所有实际字段名均用xxxx替代，仅保留注释）：保单表xxxx

问题描述L银行跑批任务包含很多存储过程，其中“对公贷款协议明细”存储过程运行时间2个小时，是整个跑批任务的瓶颈，亟需优化。从这个存储过程运行日志可以看出，对公贷款协议明细共有48个步骤，每个步骤包括几个主要SQL和一些辅助SQL组成，共有3300行。从下图可以看出各个步骤执行的时间长度：解决办法首先，深入了解数据和计算特征。对公贷款协议明细存储过程的48个步骤比较复杂，但每个步骤基本上都是三部曲：1、执行主要SQL；2、结果保存为临时表；3、为临时表建索引。执行主要SQL的时候，一般会用到前序步骤生成的临时表。我们以耗时最长的步骤7、8为例来具体看看数据和计算特征。步骤7耗时49分钟，主要SQL语句，是用小表过滤大表，还有其他条件过滤。脱敏后的SQL语句如下：selectB_NO,CUST_NO,BAL,nvl(nullif(a.D_BILL,

现状S银行的手机银行系统要对外提供活期明细查询功能，数据量大、访问量大而且要求秒级响应。银行先采用国内某知名商用HADOOP平台作为查询后台，支持多表实时关联查询，但是并发性能太差，无法满足要求。不得已改用Elastic

问题描述W银行指标查询系统用来计算、展现各项汇总指标，是银行经营指标体系的重要支撑工具。由于明细数据量过大，指标查询系统一直采用预计算方式。但是，随着指标体系的不断发展，预计算方式无法满足指标数量急剧增长的需要，逐步成为制约经营指标体系的瓶颈。预计算方式，是应对明细数据量巨大的无奈之举。W银行的客户有几千万，每天产生几千万条存款明细，几百万条贷款明细。这些明细数据保存在数据库中，业务人员提出指标计算需求，由软件工程师编写SQL语句完成计算。SQL很多很复杂，要计算的明细数据量又很大，如果白天提交给数据库计算，会造成数据库负担过重，甚至无法响应正常业务请求。因此，指标查询系统是把写好的SQL语句保存下来，每天晚上批量提交给数据库执行。执行的结果存入指标结果表，第二天上班的时候，业务人员就可以快速查询了。随着指标越来越多，预计算方式已经无法满足需要。以贷款余额为例，按照五级分类维度，会衍生出五个指标：“一级贷款余额、二级贷款余额…”。再加上四类担保，又会衍生出二十个指标：“一级、A类担保余额、一级B类担保余额…三级C类担保余额…”。再加上十种客户、四种放款方式，会衍生出800个指标。再加上币种、分支机构、日期、客户年龄段、学历等等，衍生出来的指标数量特别巨大。这还只是贷款，还要考虑存款、客户等等。如果这些指标都要预汇总的话，算出来的指标结果，根本无法全部保存！有没有什么办法能够直接从明细数据实时计算出需要的各种指标呢？基于明细数据实时计算，面临的最大的难点是性能问题。指标计算涉及到大表关联、条件过滤、分组汇总、去重计数等多种混合运算。以“某日存款去重客户数”为例，客户表有两千万条记录，当日存款明细表有三千万条记录。两个表通过客户号关联之后，过滤选出符合两个固定条件，多个动态条件的记录，再按照客户号去重计数。固定条件是“多个分支机构和币种”，动态条件是存款类型、客户年龄段、性别、学历、产品等等。仅仅是这一个指标给数据库去算，就需要较长时间。实际上，用户通过指标查询系统灵活定义的一个页面，就可能包含几十到几百个这样的指标，如下图：以图中下方的“近12个月变化趋势”这个“指标块”为例，每个月月末一天（加上当日）都要计算“逾期90天贷款、逾期贷款、不良贷款和非应计贷款”四种指标，每种都要计算余额、笔数、去重用户数三个指标，合计就是13×4×3=156个指标。上面的四个“指标块”也有4×2×4=32个指标，整个页面共有188个指标需要计算。考虑到多个用户并发查询这个页面的情况，十个用户查询就会有近两千个指标需要同时计算出来。几千个指标同时由数据库去算，意味着对几千万条记录的大表要关联、过滤、去重、汇总计算几千次。数据库会不堪重负、业务人员要长时间等待查询结果，这是银行方面无法接受的。W银行的服务器是主流配置的虚拟机，

问题描述X银行用户画像应用中，需要完成客群交集计算。客群数量多达数千个，每个客群包含的客户数量不等，从几十万到上亿都有。要计算出任意N（一般是2-10）个客群共同的客户。例如：滴滴出行客群有几百万客户，手机银行客群有几千万客户，需要求出滴滴出行和手机银行共同的客户数量。对客群交集计算的结果，还要进行维度筛选。比如：滴滴出行和手机银行共同的客户，要对性别、年龄段和地域维度进行筛选，最终计算出满足各个维度条件的客户数量。为了从时间维度做分析，每次要计算一年的结果，因此要保存十二个月的历史数据，每个月一套。针对十二个月的明细数据进行客户交集运算称为一个单任务。X银行基于Hadoop体系某知名OLAP

现状分析B银行的电子银行自助分析系统，需要查询指定日期的客户交易明细。业务人员在界面上随意设置过滤条件，在每天几千万条数据中筛选查询。自助分析的结果可以用于找到目标客户，制定营销计划，评估活动效果等等。但是，现有的系统却只能支持5个并发访问，远远不能满足大量业务人员的访问需要。究其原因，是因为自助分析系统后台无力支撑更大的并发数量。自助分析系统直接连接B银行中央数据仓库作为后台。而数据仓库是全行共有，并不专门为自助分析服务，还承担了包括跑批计算在内的其它应用任务。集群节点已达48个，几乎是该款数据仓库的上限了，无法再扩容。所以，数据仓库只能给自助分析系统开5个连接，想要再多的连接是不可能的。而且就算是这5个连接，也很难保证流畅地使用，一旦碰到数据仓库在执行其它重要任务，自助分析操作就会显得非常卡顿。系统结构如下图：既然数据仓库无法扩容，是否可以考虑换成其他数仓产品呢？这不可行。因为数据仓库涉及到很多部门的很多应用，换其他数据仓库的成本太高，还有稳定性隐患。而且真换了也不一定能更快，还需要大量的测试和优化。一个容易想到的办法，是数据仓库之前再放一个前置数据库，用于承担自助分析的压力。但是，常规关系数据库却无法胜任。一方面，自助分析需要查询全量历史明细数据，前置数据库如果和数据仓库保持同样规模的数据，就需要建设同样规模的集群，行方无法容忍这个重复建设的成本。另一方面，常规的行存数据库也无法达到多并发下千万数据量秒级响应的速度。解决办法首先，分析业务和需求特征。自助分析的计算和数据是有规律可循的。自助分析系统提交的SQL比较简单，只包含select、from、where子句，没有复杂的窗口函数等。From子句针对的是一个宽表，没有表间关联Join。宽表字段有几十个，查询需要的字段一般都只有几个。Where条件中，日期是必选条件，即只能在一天范围内查询。其他过滤条件是客户自主选择和组合的。进一步分析，我们发现90%以上查询请求都集中在十几个日期：查询的前一日和连续13个月的月末最后一天。这个范围之外的数据，查询量不足10%。因此，我们可以将这个范围内的数据定义为热数据，其他数据定义为冷数据。第二，设计优化方案并确定技术要求。根据实际情况和业务需求，我们还是决定采用前置计算层的方案，在前置层保存热数据并负责相应的查询，承担90%以上的计算量；而对冷数据的查询仍然放在中央数据仓库上，但计算量下降到原来的不足10%。这样，前置计算层只需要保持较少量的热数据，不会出现重复建设的情形。这个方案的关键在于前置计算层的实施技术，它必须有足够的高性能以应对90%的查询量，同时还需要有路由能力。因为自助分析的前端是套装软件，只会通过JDBC接口对一个后台数据库发出SQL查询，因而需要这个前置计算层能根据SQL中的日期条件决定由自己做查询还是将查询分发给中央数据仓库。优化方案的结构如下图所示：从图中可以看出，自助分析系统提交查询SQL后，前端计算层要判断其中的日期条件是否针对热数据，如果是，则直接查询数据，返回结果。如果不是，则将SQL转发给数据仓库，结果返回给自助分析系统。这相当于一个网关的作用，能实现数据计算的路径选择，我们称之为数据计算路由。另外，B银行一般的服务器内存都是128G，十多天、每天千万数据量的数据，也没办法全内存计算。这个前置计算层需要采用高性能列存方案，才能提供千万数据量秒级并发的查询速度。同时，前置计算层还要对自助分析系统提供标准JDBC接口，至少支持前面说的简单SQL语句，也就是需要有SQL的解析和执行能力。第三，确定技术选型。有了优化方案，就要选择适用的工具来实现方案。经过最初的分析，我们已经知道无法使用常规关系数据库来搭建这个前置计算层。服务器内存不大，无法装入全部数据，无法使用全内存计算技术（包括各种内存数据库）。采用现成的列存数据库大体能达到性能要求，但一方面造价太高，另一方面列存数据库仍然是关系数据库体系，无法实现路由功能，因此也不可行。使用Java或C++等高级语言可以实现上述算法，但编码量过大，实现周期过长，容易出现代码错误隐患，也很难调试和维护。开源的集算器SPL语言提供上述所有的算法支持，包括高性能列存文件、JDBC和SQL执行、并提供SQL语句解析和转换的基本函数以实现数据计算路由，能够让我们用较少的代码量快速实现这种个性化的计算。集算器SPL语言提供强大的脚本功能，可以用来定义和调整数据计算路由规则。这样，还能记录路由日志，用来分析热数据的时间和空间分布，不断优化路由规则。第四，实现前置计算层及网关方案。前置计算层初始化时，要将十多天的热数据从数据仓库中导出，缓存到本地高性能列存文件中。按照优化前的方式，每天用ETL工具将生产数据库推送来的，前一日的最新数据导入到中央数据仓库。优化之后，同时将推送来的数据在集算器中保存成文件。数据本来就是推送过来的，因此并没有增加生产库导出的负担。自助分析系统中，数据仓库的JDBC驱动，要换成集算器的JDBC驱动，数据连接参数也要相应调整。其他不需要改动，最大程度兼容自助分析系统。集算器接收到自助分析系统提交的查询请求后，先进行SQL解析，拆分出各个SQL子句，根据where子句中的日期条件，决定计算路径。如果是热数据，则访问缓存好的本地高性能列存文件。如果是冷数据，要将标准SQL转换为数据仓库的SQL语句，转发给数据仓库执行，结果返回给自助分析系统。同时，记录数据路由的日志。为了并行访问时分担负载，集算器采用两个节点集群部署。这样做，还可以避免单点故障，实现高可用性。最终用集算器搭建的系统如下图：实际效果系统优化之后已经在线正式运行了，实际效果也非常显著。从单查询请求测试来看，单日明细数据三千万行，集算器和专业数据仓库执行同样的条件查询。集算器仅用2秒完成，数据仓库执行了5秒。数据仓库环境是5个节点集群，每个节点是2*6核CPU，96G内存的实体机；而集算器所在的服务器仅是1*2核CPU，16G内存的虚拟机。从并发请求来看，每台前置服务器支持50并发，全部在5秒之内返回完毕。两台服务器集群支持几百用户访问毫无压力。也就是仅仅增加了两个低端PC服务器就把并发量提高了10倍以上，而且全程操作流畅。从开发难度来看，SPL做了大量封装，提供了丰富的函数，内置了上述优化方案需要的基本算法和存储机制。上面所说的数据计算路由对应的SPL代码如下图：后记解决性能优化难题，最重要的是设计出高性能的计算方案，有效降低计算复杂度，最终把速度提上去。因此，一方面要充分理解计算和数据的特征，另一方面也要熟知常见的高性能算法，才能因地制宜地设计出合理的优化方案。本次工作中用到的基本高性能算法，都可以从下面这门课程中找到：点击这里学习性能优化课程（底部原文中可点击链接），有兴趣的同学可以参考。很遗憾的是，当前业界主流大数据体系仍以关系数据库为基础，无论是传统的MPP还是HADOOP体系以及新的一些技术，都在努力将编程接口向SQL靠拢。兼容SQL确实能让用户更容易上手，但受制于理论限制的SQL却无法实现大多数高性能算法，眼睁睁地看着硬件资源被浪费，还没有办法改进。SQL不应是大数据计算的未来。有了优化方案后，还要用好的程序语言来高效地实现这个算法。虽然常见的高级语言能够实现大多数优化算法，但代码过于冗长，开发效率过低，会严重影响程序的可维护性。开源SPL是个很好的选择，它有足够的算法底层支持，代码能做到很简洁，还提供了友好的可视化调试机制，能有效提高开发效率，以及降低维护成本。正在为

问题描述C保险公司业务系统中，团体保险明细查询速度很慢。查询时输入保单号，要返回团体保单包含的所有被保险人的信息。较小的保单，包含1万个被保险人，返回页面需要等待7.5分钟。较大的保单，包含100万被保险人，返回页面等待了4个小时没有出来。团体保险明细比较大，分两个数据库保存。每个团体保单的数据，在两个库中都有可能出现。数据库是Oracle，SQL语句共163行，如下图：分析解决面对性能问题，需要仔细分析数据和计算的特征，定位性能关键点，通过改变数据的存储方式和计算方法逐步优化。第一步，确认需求前提。团体保险明细查询是应用系统中的一个功能，需要查询最新数据。如果采用ETL定时将数据导出计算的方式，不能满足这个要求。因此，还是要想办法从数据库取数、库外计算，来优化性能。第二步，了解业务需求特征。团体保险明细数据存放在两个数据库db1、db2，每个数据库都有两个表m1、m2。这四个表在查询时要合并查询结果，我们统一称为团体保险明细表。四个团体保险明细表有所不同，但是都可以查询出主要字段：保单号、保险成员号、批改次数、业务编号1、业务编号2、业务标志，还有姓名、性别年龄等个人信息“批改”是针对保险合同的调整，系统将调整后的最新保险明细也保存在团体保险明细表中，不会修改原保险明细，保留轨迹。在数据中通过“批改次数”字段体现。查询时，要查询批改次数最大的一次，也就是最新的数据。明细数据中还有一部分是无效数据。要看业务编号1和业务标志连接成的字符串是否在无效集合中。无效集合是指：同一个保单号的数据中，批改次数小于9，并且业务标志为D或者U时，业务编号2和字母A连接成的字符串形成的集合。如果业务编号1和业务标志连接成的字符串出现在无效集合中，这条记录就是无效的记录，要舍弃掉。第三步，梳理研究计算过程。SQL虽然比较长，但是可以分成几个部分。第一部分是两个数据库的4个团体保险明细表，各自按照保单号查询需要的数据，再用union合并在一起。第二部分是条件过滤，包括去掉无效数据和另外几个简单的条件。第三部分是用窗口函数row_number()

，改为并行取数后，可以看到性能提升明显，代码如下：AB1=now()/记录时间2=connect("oracle").query@ix("SELECT

很多行业都会有展现明细数据类的大清单报表需求，比如电信行业月底要看本月的全部充值记录，银行业要看当月交易记录清单，数据量会达到百万甚至千万级别千万级别的数据，如果等全部取出算完再呈现，需要很长时间，没有人可以接受这么恶劣的用户体验，而且还有另外一个限制因素，就是报表大都是内存计算的，服务器的内存是有限的，一次装不下这么多数据，都得溢出所以就需要有专门来处理大清单报表的功能和机制才可以1

数据查询业务中，有时会碰到数据量很大的清单报表。由于用户输入的查询条件可能很宽泛，因此会从数据库中查出几百上千万甚至过亿行的记录，常见的包括银行流水记录，物流明细等。呈现时如果等着把这些记录全部检索出来再生成报表，那会需要很长时间，用户体验自然会非常恶劣。而且，报表一般采用内存运算机制，大多数情况下内存里也装不下这么多数据。所以，我们一般都会使用分页呈现的方式，尽量快速地呈现出第一页，然后用户可以随意翻页显示，每次只显示一页，也不会造成内存溢出。传统分页呈现的实现，一般都会使用数据库的分页机制来做，利用数据库提供的返回指定行号范围内记录的语法。界面端根据当前页号计算出行号范围（每页显示固定行数）作为参数拼入