性能優(yōu)化技巧：部分預關聯

raqsoft 2021-01-22

展開全文

一、問題背景與適用場景

在《性能優(yōu)化技巧：預關聯》中，我們測試了將數據表事先全部加載進內存并做好關聯后的查詢性能優(yōu)化問題，但如果內存不夠大，不能將維表和事實表全部裝入，那怎么辦呢？此時，可以將維表預先裝入內存，建好索引，實現維表部分的預關聯，省去一半hash計算。

我們下面再來測試一下這種場景，這次用數據量最大、內存裝不下的lineitem表做測試，在SPL部分預關聯中，將其它7張表預先裝進內存，而lineitem在查詢時才實時讀入。

二、 SQL測試

依然用 Oracle 數據庫作為 SQL 測試的代表，從lineitem表里查詢每年零件訂單的總收入。

1. 兩表關聯

查詢的SQL語句如下：

select

l_year,

sum(volume) as revenue

from

(

select

extract(year from l_shipdate) as l_year,

(l_extendedprice * (1 - l_discount) ) as volume

from

lineitem,

part

where

p_partkey = l_partkey

and length(p_type)>2

) shipping

group by

l_year

order by

l_year;

2. 六表關聯

查詢的SQL語句如下：

select

l_year,

sum(volume) as revenue

from

(

select

extract(year from l_shipdate) as l_year,

(l_extendedprice * (1 - l_discount) ) as volume

from

supplier,

lineitem,

orders,

customer,

part,

nation n1,

nation n2

where

s_suppkey = l_suppkey

and p_partkey = l_partkey

and o_orderkey = l_orderkey

and c_custkey = o_custkey

and s_nationkey = n1.n_nationkey

and c_nationkey = n2.n_nationkey

and length(p_type) > 2

and n1.n_name is not null

and n2.n_name is not null

and s_suppkey > 0

) shipping

group by

l_year

order by

l_year;

3. 測試結果

	兩表關聯	六表關聯
運行時間(秒)	235	2669

這兩個測試數據依然是多次運行后取最快的那次。

從測試結果可以看出，六表關聯比兩表關聯慢了2669/235=11.4倍！性能下降非常多。

三、 SPL部分預關聯測試

1. 部分預關聯

實現預關聯的SPL腳本如下：

	A
1	>env(region, file(path+"region.ctx").create().memory().keys@i(R_REGIONKEY))
2	>env(nation, file(path+"nation.ctx").create().memory().keys@i(N_NATIONKEY))
3	>env(supplier, file(path+"supplier.ctx").create().memory().keys@i(S_SUPPKEY))
4	>env(customer, file(path+"customer.ctx").create().memory().keys@i(C_CUSTKEY))
5	>env(part, file(path+"part.ctx").create().memory().keys@i(P_PARTKEY))
6	>env(orders,file(path+"orders.ctx").create().memory().keys@i(O_ORDERKEY))
7	>nation.switch(N_REGIONKEY,region)
8	>customer.switch(C_NATIONKEY,nation)
9	>supplier.switch(S_NATIONKEY,nation)
10	>orders.switch(O_CUSTKEY,customer)

腳本中前6行分別將6個維表讀入內存，生成內表，并建好索引，再設成全局變量。后4行完成維表間連接。在SPL服務器啟動時，就先運行此腳本，完成環(huán)境準備。

2. 兩表關聯

編寫SPL腳本如下：

	A
1	=file("/home/btx/lineitem.btx").cursor@tb(L_PARTKEY,L_EXTENDEDPRICE,L_DISCOUNT,L_SHIPDATE)
2	=A1.switch@i(L_PARTKEY,part).select(len(L_PARTKEY.P_TYPE)>2)
3	=A2.groups(year(L_SHIPDATE):l_year; sum(L_EXTENDEDPRICE * (1 - L_DISCOUNT)):revenue)

臨時裝載需要用游標，然后在游標上進行關聯，之后的寫法和全內存差不多。

3. 六表關聯

編寫SPL腳本如下：

	A
1	=file("/home/btx/lineitem.btx").cursor@tb(L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,L_SHIPDATE)
2	=A1.switch@i(L_ORDERKEY,orders;L_PARTKEY,part;L_SUPPKEY,supplier)
3	=A2.select(len(L_PARTKEY.P_TYPE)>2 && L_ORDERKEY.O_CUSTKEY.C_NATIONKEY.N_NAME!=null && L_SUPPKEY.S_NATIONKEY.N_NAME != null && L_SUPPKEY.S_SUPPKEY>0 )
4	=A3.groups(year(L_SHIPDATE):l_year; sum(L_EXTENDEDPRICE * (1 - L_DISCOUNT)):revenue)

類似地，建立好游標及關聯后的寫法和全內存差不多，一樣非常簡潔易懂。

4. 運行結果

	兩表關聯	六表關聯
運行時間(秒)	266	472

六表關聯僅僅比兩表關聯慢1.8倍，增加的時間主要用于事實表lineitem中L_ORDERKEY和L_SUPPKEY字段的關聯以及增加的過濾條件計算量（引用這些關聯表字段）的時間。因為有了部分預關聯，維表之間關聯運算本身不再消耗時間，而維表與lineitem表關聯的時間，也因為事先建好索引而提高了性能（可以減少一半的hash計算）。