很多大数据计算都是用SQL实现的。运行慢的时候，SQL一定要优化，但是经常会遇到让人瞪眼的情况。比如存储过程中有3条大致这样的语句，执行起来很慢：selecta,b,sum(x)fromTgroupbya,bwhere...;selectc,d,max(y)从Tgroupbyc,dwhere...;selecta,c,avg(y),min(z)fromTgroupbya,cwhere...;其中T是一个上亿行的巨表，应该按照三种方式分组，分组后的结果集并不大。分组操作需要遍历数据表。这3条SQL语句需要遍历大表3次。上亿行数据遍历一次都需要很长时间，更不用说三次了。在这个分组操作中，相对于遍历硬盘的时间，CPU的计算时间几乎可以忽略不计。如果能在一次遍历中计算出各种分组汇总，虽然CPU的计算量没有减少，但是可以大大减少从硬盘读取的数据量，速度可以提高一倍。如果SQL支持这样的语法：fromT--数据来自T表selecta,b,sum(x)groupbya,bwhere...--遍历中的第一个分组selectc,d,max(y)groupbyc,dwhere...--遍历中的第二种分组selecta,c,avg(y),min(z)groupbya,cwhere...;--遍历中的第三个分组可以一次返回多个结果集，可以大大提高性能。不幸的是，SQL没有这种语法，不可能写出这样的语句。只有一个workaround，就是使用groupa,b,c,d的写法，先算出一个更详细的分组结果集，但是先保存为一个临时表，可以用来进一步计算目标结果withSQL。SQL大致如下：createtableT_tempasselecta,b,c,d,sum(casewhen...thenxelse0end)sumx,max(casewhen...thenyelsenullend)maxy,sum(casewhen...thenyelse0end)sumy,count(casewhen...then1elsenullend)county,min(casewhen...thenzelsenullend)minzgroupbya,b,c,d;selecta,b,sum(sumx)fromT_tempgroupbya,bwhere...;从T_temp组中选择c、d、max(maxy)按c、dwhere...；从T_temp组中选择a、c、sum(sumy)/sum(county)、min(minz)按a、cwhere...;这样只需要遍历一次，但是需要把不同的WHERE条件转移到之前的casewhen，代码就复杂了很多，计算量也会增加。而且计算临时表时，分组字段变多，结果集可能会很大。最后遍历多次临时表，计算性能不会很快。大结果集的分组计算需要硬盘缓存，其性能也很差。也可以使用存储过程的数据库游标逐条取数据进行计算，但这需要你自己实现WHERE和GROUP动作。写起来太麻烦，数据库游标遍历数据的性能只会更差！你只能做Stare！TopN运营也会遇到这种无奈。比如用OracleSQL写top5大致是这样：select*from(selectxfromTorderbyxdesc)whererownum<=5T表有10亿条数据。从SQL语句来看，是所有数据排序后，取出前5，其余排序结果无用！大排序的成本非常高，数据量太大内存放不下，会有多个硬盘数据切换，计算性能会很差！避免大排序并不难。在内存中保留5条记录的小集合。遍历数据时，将计算出的前5条数据保存在这个小集合中。如果新得到的数据比当前第5号高，则插入，舍弃当前第5号，如果小于当前第5号，则不做任何动作。这样10亿条数据只需要遍历一次，内存占用很小，计算性能会大大提高。该算法本质上将TopN视为与求和、计数相同的聚合操作，只不过它返回的是一个集合而不是单个值。如果SQL可以这样写：selecttop(x,5)fromT可以避免大排序。不幸的是，SQL没有明确的聚合数据类型，聚合函数只能返回一个值，所以不能写这样的语句！不过还好，completeset的TopN比较简单。SQL虽然是这么写的，但是数据库通常会优化工程，采用上面的方法来避免大排序。所以Oracle计算那个SQL的速度并不慢。但是，如果TopN情况比较复杂，在子查询中使用或者与JOIN混合使用时，优化引擎通常不起作用。比如分组后计算每组的TopN，用SQL写起来有点难度。Oracle的SQL是这样写的：select*from(selecty,x,row_number()over(partitionbyyorderbyxdesc)rnfromT)wherern<=5这时候数据库的优化引擎就晕了，上面那种把TopN理解为聚合操作的方法就不再用了。只能做排序，结果运算速度急剧下降！如果SQL的分组TopN可以这样写：selecty,top(x,5)fromTgroupbyy把top当做sum一样的聚合函数，这样不仅更易读，而且在高处也容易操作速度。很不幸的是，不行。还盯着！关联计算也是一个很常见的案例。以订单关联多表后的过滤计算为例，SQL一般是这样的：selecto.oid,o.orderdate,o.amountfromordersoleftjoincityciono.cityid=ci.cityid在o.shid=sh.shid上左加入托运人sh在o.eid=e.eid上左加入员工e在o.suid=su.suid上左加入供应商suwhereci.state='NewYork'ande.title='manager'and...order表有千万条数据，city,shipper,employee,supplier等表数据量不大。过滤条件字段可能来自这些表，参数从前端传递到后台，会动态变化。SQL一般使用HASHJOIN算法来实现这些关联，计算并比较HASH值。一次只能解析一个JOIN，有N个JOIN需要执行N次。每次关联后，需要为下一轮保留中间结果。计算过程复杂，数据会遍历多次。计算性能不好。通常，这些关联码表足够小，可以先读入内存。如果订单表中的关联字段是预先序列化的，比如将员工编号字段的值转换为对应员工表记录的序列号。那么在计算的时候，可以使用员工号字段的值（即员工表的序号）直接取内存中员工表对应位置的记录。性能比HASHJOIN快很多，只需要遍历一次订单表，速度提升。会很明显！即SQL可以这样写：selecto.oid,o.orderdate,o.amountfromordersoleftjoincitycono.cid=c.#--传递订单表的城市编号theserialnumber#Associatedcitytableleftjoinshippershono.shid=sh.#--Ordertableshippernumberthroughserialnumber#Associatedshippertableleftjoinemployeeeono.eid=e.#--订单表员工号通过序号#关联员工表leftjoinsuppliersuono.suid=su.#--ordertablesuppliernumberThroughserialnumber#关联供应商表whereci.state='NewYork'ande.title='manager'and...不幸的是，SQL使用无序集的概念。即使这些数字已经序列化，数据库也无法利用此功能。它不能在这些无序的对应关联表集合上使用序列号快速定位的机制。索引查找，而数据库并不知道数字是序列化的，还是会计算HASH值比较，性能还是很差！就算有好办法，我也实施不了，只能再一次发呆！还有高并发的账户查询。这个操作非常简单：selectid,amt,tdate,…fromTwhereid='10100'andtdate>=to_date('2021-01-10','yyyy-MM-dd')andtdate
