优化Join运算的系列方法(3)

 

5 综合案例

实际业务中,多表关联运算十分常见,外键表、同维表、主子表这几种关联类型可能会混合出现。下面我们来看一个综合案例。

5.1 表结构和查询目标

某电商平台中和订单编号这个字段相关的有6个表,主要表结构如下:

订单表

订单明细表

订单优惠表

订单发货表

订单支付表

订单评价表

订单编号

订单编号

订单编号

订单编号

订单编号

订单编号

用户编号

商品编号

优惠类型

快递编码

支付渠道

评分

卖家编号

数量

优惠金额


支付时间

评论时间

下单时间

金额



是否分期

评价

订单状态







1:N

1:N

1:1

1:1

1:1

他们都靠订单编号字段进行关联,下面是订单表和另外5个表的对应关系:

用户信息表

用户地址信息表

用户编号

用户编号

用户名

手机

注册时间

区县

VIP级别

地址


1:1

用户表和用户地址表,这两个表是按照用户编号字段11的关系,这是同维表情况。

商品信息表

类别信息表

商品编号

类别编号

名称

大类名称

类别编号

二级名称

厂商编号



1:1

商品信息表和类别信息表是通过类别编码进行关联,这是外键表的情况。

卖家信息表

卖家编号

开户行

联系

名称

信用级

最后还有一个卖家信息表。这里一共有11个表,假设要做这样一个查询:现在想知道江浙沪三省的VIP用户在6月份内从5星级卖家那里购买的所有电脑类商品的详情,并且要求只统计那些优惠总金额大于100元、用户评分4分以上的使用邮政配送的订单,而且这些订单不能是分期付款的。

使用SQL实现:

SELECT * FROM

     订单表,

     (SELECT用户编号 FROM 用户信息表 用户,用户地址信息表 地址 WHERE 用户.用户编号=地址.用户编号 AND 用户.VIP级别>0 AND (地址.=江苏 OR 地址.=浙江 OR 地址.=上海) ) 用户,

     (SELECT卖家编号 FROM 卖家信息表 WHERE 卖家信息表.等级=5) 卖家,

     (SELECT订单编号 FROM 订单优惠表 GROUP BY 订单编号 HAVING SUM(优惠金额)>100) 优惠,

     (SELECT订单编号 FROM 订单发货表 WHERE 快递编码=1) 快递,

     (SELECT订单编号 FROM 订单支付表 WHERE 是否分期=false) 支付,

     (SELECT订单编号 FROM 订单评价表 WHERE 评分>=4) 评价,

     (SELECT订单编号 FROM 订单明细表,(SELECT * FROM 商品信息表 JOIN 类别信息表 ON 商品信息表.类别编号=类别信息表.类别编号 WHERE 大类名称='电脑') WHERE 订单明细表.商品编号=商品信息表.商品编号) 明细

WHERE

订单表.用户编号=用户.用户编号

AND 订单表.卖家编号=卖家.卖家编号

AND 订单表.订单编号=优惠.订单编号

AND 订单表.订单编号=快递.订单编号

AND 订单表.订单编号=支付.订单编号

AND 订单表.订单编号=评价.订单编号

AND 订单表.订单编号=明细.订单编号

 

5.2 分析关联类型

这个SQL看上去似乎很清楚,理解起来也不难,但是性能却可能惨不忍睹。为了优化查询的性能,我们需要先对这个查询进行拆分,得到以下几个子步骤:

P1=对用户表、用户地址表关联,得到江浙沪三省VIP用户的用户编号,这是同维表情况;

P2=卖家信息表取游标,条件是信用级别=5,得到卖家编码;

P3=对订单优惠表按照订单编号分组,按条件(优惠总金额>=100元)过滤;

P4=订单发货表取游标,条件是快递编码=1(邮政快递编码);

P5=订单支付表取游标,条件是是否分期=false

P6=订单评价表取游标,条件是评分>=4

P7=商品信息表和类别信息表用类别编码做外键关联,用条件(大类=电脑)过滤;

P8=订单明细表通过商品编号字段对P7做外键关联;

P9=订单表依次对P1P2做外键关联;

这时P3P4P5P6P8P9这几个子查询都是同维或者主子表的关系,对它们通过订单编码字段做有序归并,就得到了需要查询的结果。

5.3 数据预处理

得到关联类型后就可以有针对性地进行预处理。

首先,对无序的同维表、主子表进行排序处理,比如订单发货表、订单支付表、订单评价表通常是无序的,就要先对这些表进行排序;

第二步,还可以对外键表进行外键序号化,比如卖家信息表是订单表的外键表,就可以外键序号化。

5.4 查询实现

用户信息表很大,但查询目标是VIP级别的用户,符合VIP这个条件的用户并不多,进行过滤后就可以装入内存,所以P1子查询可以全部装入内存;同样,用户地址信息表作为用户信息表的同维表也很大,但属于江浙沪三省的用户并不多,经过过滤后可以全部导入内存。把这两个同维表关联后,然后再完成订单表的关联计算,来看看这个子查询的写法:


A

1

=file("用户信息表").cursor@b().select(VIP级别>=1)

2

=file("用户地址信息表").cursor@b().select(=="江苏"||=="浙江"||=="上海")

3

=joinx(A1:用户信息,用户编号;A2,用户编号).new(用户信息.用户编号:用户编号).fetch()

4

=file("订单表").cursor@t().select(year(下单时间)==2018 && month(下单时间)==6)

5

=A4.switch@i(用户编号,A3:用户编号)

6

=A5.new(订单编号.订单编号:订单编号, 订单编号.卖家编号:卖家编号,用户编号.用户编号:用户编号).fetch()

7

return A6

A1,得到用户信息表的游标,并按条件过滤;

A2,得到用户地址信息表的游标,并按条件过滤;

A3,对A1A2按照用户编号字段进行有序归并,返回的结果只取用户编号;

A4,得到订单表的游标,并按条件过滤;

A5,把A4A3做外键关联;

A6,返回结果只取订单编号、卖家编号和用户编号字段;

A7,返回执行结果;

把这个脚本保存为P9.dfx

 

接下来实现商品信息表和类别信息表的关联。类别信息表是商品信息表的外键,这个表很大无法装入内存。但是大类是电脑的类别信息就不多了,所以用大类等于电脑这个条件先过滤一下后就可以装入内存。下面是个子查询,把大类是电脑的所有商品的编码全部导入内存:


A

1

=file("类别信息表").import@b().select(大类名称=="电脑")

2

=file("商品信息表").cursor@b().select(VIP级别>=1)

3

=A2.switch@i(商品编号,A1:商品编号)

4

=A3.new(商品编号).fetch()

5

return A4

A1,得到类别信息表的数据,并按条件过滤后取出;

A2,得到商品信息表游标,并按条件过滤;

A3,把A2的商品编号字段替换为A1的对应记录;

A4,结果只取商品编号字段;

A5,返回执行结果;

这个脚本保存为P7.dfx

 

上面是两个子查询的处理,整个查询的实现是这样:


A

1

=file("订单优惠表").cursor@b().group(订单编号;sum(优惠金额):优惠总额).select(优惠总额>=100)

2

=file("订单发货表").cursor@b().select(快递编码==1)

3

=file("订单支付表").cursor@b().select(是否分期==false)

4

=file("订单评价表").cursor@b().select(评分>=4)

5

=file("卖家信息表").import@b()

6

=call("P9.dfx")

7

=A6.switch@i(卖家编号,A5:#).select(卖家编号.信用级==5).sort(订单编号)

8

=call("P7.dfx")

9

=file("订单明细表").cursor@b()

10

=A9.switch@i(商品编号,A8:商品编号)

11

=joinx(A7:订单,订单编号; A10:明细,订单编号; A1,订单编号;A2,订单编号;A3,订单编号;A4,订单编号)

A1,得到订单优惠表游标;

A2,得到订单发货表游标;

A3,得到订单支付表游标;

A4,得到订单评价表游标;

A5,得到卖家信息表数据(这里认为卖家信息表的数据可以导入内存);

A6,调用P9.dfx

A7,把A6的结果的卖家编号替换成卖家信息表里的对应记录,按条件(信用级=5)进行过滤,并排序;

A8,调用P7.dfx,得到大类是电脑的所有商品的编码;

A9,得到订单明细表游标;

A10,把订单明细表的商品编号替换成A8结果里的对应记录;

A11,对A7A10A1A2A3A4进行有序归并;

5.5 查询技巧

技巧一:如果维表在内存中放不下,先别着急,可以看看总的查询条件里是否对这个维表进行了过滤。如果有,那么就可以把条件提取出来对维表进行过滤,很多时候过滤之后的结果就可以装入内存了。

技巧二:如果维表可以装入内存,并且已经外键序号化,那么就不要先过滤维表。因为能装入内存时用序号化做外键关联是最快的。例子中就是对卖家信息表先做关联,然后再进行条件过滤。

总结

使用集算器解决 JOIN 运算性能问题时,可以按照这个流程来处理:首先判断 JOIN 运算类型;如果是外键表,就装入内存并做外键序号化,如果无法装入内存也要尽量先用条件过滤,有条件的尽量做外键序号化;如果是同维表或主子表,要判断是否有序,有序则可以直接做有序归并,如果无序的则要先进行排序。同时,如果几个表是同步分段的还可以通过并行来提高性能。


以下是广告时间

对润乾软件产品感兴趣的小伙伴,欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
漫画了解好多乾
对号入座
或者这里
好多乾详细规则
仔细研究