764
odps之sql性能優化
前一段時間做了一些作業成本優化的工作，這裏做下總結。
首先說明本篇中談及的優化主要的目標是在不大幅度增加作業運行時長的條件下對作業運行成本的優化。
1. odps的優化引擎並沒有那麼智能
odps自帶的優化引擎會對sql作業做一定的優化處理，如列裁剪、分區裁剪和謂詞下推。但是還會存在一些不會優化處理的地方。甚至有些會和我們想象的存在一定的差異。具體碰到的情況有如下幾種
1.1 多路輸出(MULTI INSERT)中的想象差異
為了避免多次讀取同一份數據，我們會使用multi insert的語句。如果在from語句中有一些較複雜的處理，如select語句有耗時的udf處理或者where語句有耗時的udf處理時會存在一些問題。
如如下sql：
FROM (
SELECT
tolower(bi_udf:bi_get_url_domain(host,1)) root_domain,
tolower(secods:url_path(flow_str_concat('https://a.cn',uri))) path
from odl_beaver_log
where ds='20170818' and hh='07'
and tolower(parse_url(uri,'EXT'))!='do'
)
insert overwrite table a(ds='20170818')
select root_domain...
insert overwrite table b
select ....
insert overwrite table c
select ....
我們查看一下該sql實際的執行計劃(具體查看計劃請使用MaxCompute Studio工具)
注意：這裏的sql與下麵的執行計劃的圖不是一致的，這裏隻是舉例說明
通過執行計劃的圖我們可以看出其處理流程並沒按我們設想的先把from中的結果計算出來，然後再多路輸出。而是先按多路輸出進行列裁剪，然後再分別進行SEL(select語句)和FIL(where語句)的處理。那這裏就會造成重複的計算處理，如果多路輸出的表越多，造成的重複計算越多。
解決方法：如果from語句中有較複雜的sql處理邏輯，建議先將from語句的結果存為一張臨時表，然後再進行後麵的多路輸出的處理
1.2 where和select語句中對同一字段的重複的udf處理並不會合並
在where語句中，有時我們會對同一字段的值進行多次的判斷，且需要通過一個udf將該字段進行轉換了後來進行多次的判斷處理。同樣select語句中也會存在這樣的情況，一個udf處理的結果在多個表達式中用到。實際這裏優化器不會對這樣重複的表達式進行合並處理。這樣就造成了重複的計算處理。如如下sql：
select * from odl_beaver_log
where ds='20170818' and hh='07'
and not to_lower(uri) like '%abtest%'
and not to_lower(uri) like '%gettimestamp%'
and not to_lower(uri) like '%tb_eagleeyex_t%'
解決方法：通過做一個子查詢，先將該表達式在select語句中處理，然後再過濾。
select * from(
select ..., to_lower(uri) uri
from odl_beaver_log
where ds='20170818' and hh='07'
) a
where not uri like '%abtest%'
and not uri like '%gettimestamp%'
and not uri like '%tb_eagleeyex_t%'
1.3 隱藏的隱式轉換處理
在sql語句中如果條件2邊的類型不一致時會自動進行隱式轉換處理。例如如下sql：
select
sum(case when ret_code=200 then 1 else 0 end) as status_200_cnt,
sum(case when ret_code in (301,302,419,420) then 1 else 0 end) as status_302_cnt,
sum(case when ret_code>=300 and ret_code<=399 and ret_code not in (301,302) then 1 else 0 end) as status_3xx_cnt,
sum(case when ret_code=401 then 1 else 0 end) as status_401_cnt,
sum(case when ret_code=403 then 1 else 0 end) as status_403_cnt,
sum(case when ret_code=404 then 1 else 0 end) as status_404_cnt
from a
這裏表中的ret_code的字段類型為string,那在執行上述sql時就會多次對ret_code進行string->bigint的處理。這裏同樣是存在多次的重複計算。
解決方法：這裏先做一個子查詢將ret_code轉換為bigint類型，參考sql如下：
select
sum(case when ret_code=200 then 1 else 0 end) as status_200_cnt,
sum(case when ret_code in (301,302,419,420) then 1 else 0 end) as status_302_cnt,
sum(case when ret_code>=300 and ret_code<=399 and ret_code not in (301,302) then 1 else 0 end) as status_3xx_cnt,
sum(case when ret_code=401 then 1 else 0 end) as status_401_cnt,
sum(case when ret_code=403 then 1 else 0 end) as status_403_cnt,
sum(case when ret_code=404 then 1 else 0 end) as status_404_cnt
from(
select ...,cast(ret_code as bigint) ret_code from a
) a
1.4 篩選條件的重排
在where語句中如果有多個filter處理條件，這裏是支持短路求值的，在這樣的場景下，where語句有多個filter處理條件，其中有一個條件計算成本低且能過濾較多的記錄數，而另一個條件計算成本高，那我們希望在sql執行時能先執行前麵那個過濾條件，而在目前odps的處理邏輯中是按照順序從左到右執行的(注意：這裏要考慮到sql優化引擎的謂詞下推的處理，具體的執行順序請參考執行計劃的內容)
解決方法：評估where條件中各個篩選邏輯的計算成本和過濾的記錄數的情況，調整sql，優先讓計算成本低且過濾記錄數較多的條件先執行。
能根據sql執行的實際情況重排篩選條件的這個需求已經給odps那邊提了，他們有安排開發處理：https://sqi.alibaba-inc.com/arsenal//requirementDetail.htm?id=43362&departmentId=26718&bu=dataworks，但具體時間點完成還不清楚，如果能確定哪個比較優化，我們先在sql中手工處理了。
2. udf的優化
在odps的sql作業中，我們會用到較多的udf函數，其中有很多是自定義開發的udf函數，那這些函數如果性能有問題，會導致sql作業性能低，計算成本消耗較多。在實際的優化分析中，有碰到如下幾類情形。
2.1 自定義開發的udf的功能與係統自帶udf功能重複
在sql中的使用的自定義udf實際上係統自帶的udf是支持的，一般來說自定義開發的udf的性能是沒有係統自帶的udf性能高的，所以一般情況下，建議使用係統自帶的udf。
2.2 選擇合適的udf
在開發中選擇適合的udf對計算性能的優化也很明顯，下麵舉幾個例子來說明。
1）從字符串中解析出多個key值
業務中較常見從字符串中解析出多個值的情況，如keyvalue格式拚接的字符串，json字符串，url字符串等。
一般我們從json中解析key，係統有提供函數GET_JSON_OBJECT，那如果我們要解析多個key，那寫法可能就是如下的：
select
GET_JSON_OBJECT(json_str, '$.key1'),
GET_JSON_OBJECT(json_str, '$.key2'),
GET_JSON_OBJECT(json_str, '$.key3'),
GET_JSON_OBJECT(json_str, '$.key4')
from a
這樣會對json_str做多次的解析，影響了性能。如果能一次將多個key解析出來，那就隻需要解析一次了。自定義的udf：secods:json_tuple可以解決如上的問題。需注意，secods:json_tuple是一個udtf，如果select語句中還有其他字段時，寫法上有一點不一樣，參考sql如下：
select
col1,
col2,
key1,
key2,
key3,
key4
from a
lateral view secods:json_tuple(json_str,"key1","key2","key3","key4") json_view as key1,key2,key3,key4
同樣keyvalue格式拚接的字符串要解析出多個key值的時候，同樣也可以使用str_to_map來替代多次使用KEYVALUE，注意這裏str_to_map是一個udf，返回的是一個Map類型。
在url字符串的場景，會有要解析出不同的part的情況，如HOST, PATH, QUERY, REF, PROTOCOL，這樣就需要多次使用parse_url來解析處理，如果有一個udf可以一次解析出多個part，這樣性能也會優化很多。不過目前還沒有這個udf。
另外針對json和keyvalue格式，是否我們在底層就存儲為map類型的字段這樣會更好。
2）也有係統自帶的udf性能沒有自定義的高的情況
經測試parse_url就沒有secods:url_path secods:url_host的性能高
2.3 優化自定義的udf
如果沒有係統自帶的udf可以替換，且不是多次解析的情況下，也可以從自定義udf的代碼層麵來優化。
如python的udf中對正則表達式先進行編譯後再使用。這裏我們可以使用一些profiling的工具來對自定義udf的性能進行分析，扁鵲中有帶了java的profiling功能https://www.atatech.org/articles/38367,python的支持不是很好，那python代碼使用profiling來分析，就是要自己準備一下環境。
那如何來快速定位存在性能問題的udf呢，首先我們可以通過查看作業執行的日誌來看，在每個Task的日誌中會打印如下的日誌信息
Filter cursor process data time in milliseconds:1788.45
Filter cursor process data time in milliseconds:6.61
Filter cursor process data time in milliseconds:7.281
Filter cursor process data time in milliseconds:89.214
Filter cursor process data time in milliseconds:7.271
Select cursor process data time in milliseconds:2.782
Select cursor process data time in milliseconds:2.247
Select cursor process data time in milliseconds:2898.92
com.taobao.bi.odps.udf.endecode.UDFMd5 finally processed 107528 records. produced 107528 records. elapsed time in milliseconds: 404
通過這些日誌就可以看到哪個操作執行比較耗時。對應的關係猜測是按執行計劃中的順序號排列，待與odps相關同學確認後再更新。同時通過查看對應的執行計劃就可以看到這一步中有哪些udf的操作。另外目前日誌中會對java的udf把執行所消耗的時間會打印出來。如果是python的udf那就需要自己準備一些測試的sql來進行測試定位了。
3. odps係統參數優化
在一些場景下，我們也可以通過手工調整odps係統參數的值來達到成本優化的效果
3.1 hbo失效
當我們的作業發生修改後，那當時hbo就會失效，對於一些耗資源較多的作業，那成本的增長就非常的明顯，那我們在作業修改上線時可以手工配置odps.sql.mapper.cpu和odps.sql.reducer.cpu的值來減少成本的巨大波動。這2個值默認為100，如果有修改上線後成本增長較多，那說明這2個參數在hbo生效時會減少，一般為50。另大家可以通過查看以前該作業hbo有生效的日誌來查看這2個參數的具體值。
3.2 map任務執行時間太短
對於簡單的sql加工作業，map任務執行的時間非常短(幾秒～十幾秒)，但是可能會有很多的map任務，這種場景下我們可以調大odps.sql.mapper.split.size(單位M，默認值256)，減少map任務的個數，增大每個map任務的執行時長。
最後更新：2017-08-31 18:02:28