7.4 查詢SQL優化__第七章 性能優化和診斷_使用手冊_分析型數據庫-阿裏雲

二級分區查詢優化

一級分區包含多個二級分區；計算時，每個二級分區依次執行條件查詢，並將所有二級分區的結果進行匯總。由於每個二級分區都要參與所有條件篩選（索引查詢），當二級分區較多時，查詢性能較差。如果能夠預知數據的分布，確定二級分區的範圍，可以在查詢條件中增加二級分區列條件，這樣可以快速過濾無效的二級分區，減少搜索範圍。

例子：

select * from table where id = 3 and time between ‘2016-04-01 00:00:00’ and ‘2016-04-01 12:00:00’;

如果根據業務場景確認滿足time between ‘2016-04-01 00:00:00’ and ‘2016-04-01 12:00:00’ 的二級分區列為20160401，則可以將該SQL改寫為：

select * from table where id = 3 and time between ‘2016-04-01 00:00:00’ and ‘2016-04-01 12:00:00’ and pid = 20160401;

pid為二級分區列名。

條件改寫

當SQL中條件為函數時，無法走索引過濾，自動走掃描。在大多數情況下，性能會比較差，因此盡量改寫條件去除函數。例如以下SQL：

Select * from table where year（date_test） > 1990

應該改為

Select * from table where date_test >  ‘1990-00-00’

多表查詢

首先我們來複習一下可加速join的條件：

對於COMPUTENODE Local/Merge計算模式的表Join查詢，需要滿足以下幾個條件：

• 連接計算的所有事實表必須在同一個表組。
• 所有事實表的一級分區數相同。
• 事實表Join時， on條件必須包含分區列且必須是等值查詢，其他列無限製。
• Left join時，右表是事實表時，左表不能是維度表。

子查詢使用

對於子查詢，分析型數據庫會首先執行子查詢，並將子查詢的結果保存在內存中，然後將該子查詢作為一個邏輯表，執行條件篩選。由於子查詢沒有索引，所有條件篩選走掃描。因此如果子查詢結果較大時，性能比較差；反之當子查詢結果集較小時，掃描性能反而超過索引查詢。

對於join查詢，由於分析型數據庫默認采用hash join算法，如果其中一張表結果集（條件篩選後）較大時，掃描性能會比索引差很多，因此盡量不要采用子查詢。例如以下SQL：

Select A.id from table1 A join (select table2.id from table2 where table2.y = 6) B on A.id= B.id where A.x=5

當滿足條件x=5 和y=6的條數較多時，應改成：

Select A.id from table1 A join table2 B on A.id = B.id where B.y = 6 and A.x=5

一個例外情況是，當結果集較大的表是實時表，應盡量采用子查詢。原因在於，實時表的最新數據（基線合並後寫入的數據），索引能力很弱，查詢性能非常差。子查詢可以減少搜索範圍，性能反而更好。

小表廣播/Full MPP Mode查詢優化

Full MPP Mode 與COMPUTENODE Local/Merge模式選擇

COMPUTENODE Local/Merge模式是分析型數據庫支持的一種極速查詢模式，通過對用戶SQL的有效甄別，快速判斷是否和數據分布相匹配，從而達到優化數據shuffle邏輯，下沉大量計算操作的目的。

使用Full MPP Mode一般需用戶使用Hint顯示指定。

用戶也可通過在查詢hint中指定來強製查詢使用MPP或COMPUTENODE模式。典型使用COMPUTENODE Local/Merge模式的場景有：

• 不包含表連接或者子查詢的任意查詢。
• 表連接條件為等值條件且連接列均為一級分區列的任意查詢。
• 子查詢包含了分區列的的groupby操作的任意查詢。

小表廣播

小表廣播是基於COMPUTENODE Local/Merge模式擴展出的一種支持子查詢內部不包含分區列的join操作的查詢模式。典型的使用場景有：

• 非分區列的連接。如：

select name from student1 a join student2 b on a.name = b.name

兩個表分區列都為id，由於表連接為非分區列，該查詢不能使用模式。若student2表數據量不超過3萬條，使用小表廣播後，查詢可以改寫為：

select name from student1 a join (select /*+broadcast=true*/ name from student2) b on a.id = b.id

即可通過COMPUTENODE模式進行快速查詢。

• 子查詢內不包含分區列。如：

select name from student1 where name in (select name from student2 where id = 10)

該查詢無法使用COMPUTENODE模式計算出正確結果，若id=10的name記錄數不大於3萬條，可修改為小表廣播後：

select name from student1 where name in (select /*+broadcast=true*/ name from student2 where id = 10)

Full MPP Mode最優寫法

當查詢無法使用COMPUTENODE模式進行查詢時，對使用MPP查詢的SQL進行必要的修改，可以很大程度的提高查詢的速度。以TPC-H測試中第2個查詢為例，原查詢為：

select
 s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
from 
(select p_partkey, p_mfgr from part p where p_size = 15 and p_type like '%BRASS') inner join partsupp ps on p_partkey = ps_partkey
inner join supplier s on s_suppkey = ps_suppkey 
inner join (
    select p_partkey as min_p_partkey, min(ps_supplycost) as min_ps_supplycost from part p
    inner join partsupp ps on p_partkey = ps_partkey 
    inner join supplier s on s_suppkey = ps_suppkey 
    inner join nation n on s_nationkey = n_nationkey 
    inner join region r on n_regionkey = r_regionkey and r_name = 'EUROPE'
    group by p_partkey
)A on ps_supplycost = A.min_ps_supplycost and p_partkey =A.min_p_partkey
inner join nation n on s_nationkey = n_nationkey 
inner join region r on n_regionkey = r_regionkey  and r_name = 'EUROPE'
order by
    s_acctbal desc,
    n_name,
    s_name,
    p_partkey
limit 100

/*+engine=MPP*/select
 s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
from 
region r 
inner join nation n on n_regionkey = r_regionkey and r_name = 'EUROPE'
inner join supplier s on s_nationkey = n_nationkey
inner join partsupp ps on s_suppkey = ps_suppkey
inner join (select p_partkey, p_mfgr from part p where p_size = 15 and p_type like '%BRASS') p on p_partkey = ps_partkey 
inner join (
    select p_partkey as min_p_partkey, min(ps_supplycost) as min_ps_supplycost from region r
    inner join nation n on n_regionkey = r_regionkey and r_name = 'EUROPE'
    inner join supplier s on s_nationkey = n_nationkey 
    inner join partsupp ps on s_suppkey = ps_suppkey 
    inner join part p on p_partkey = ps_partkey
    group by p_partkey
)A on ps_supplycost = A.min_ps_supplycost and p_partkey =A.min_p_partkey
order by
    s_acctbal desc,
    n_name,
    s_name,
    p_partkey
limit 100