966
關於一個用戶遷移數據庫前後的性能差異分析
一個用戶工單:數據從ECS遷移到RDS，相同的語句，查詢性能下降了幾十倍。而實際上RDS這個實例在內存上的配置與原來ECS上的實例相當。
本文簡單說明這個case的原因及建議。
用戶反饋性能變慢的語句為 （修改了真實表名和列名）
select count(1) from HR hr join H h on h.hid = hr.hid
join A e on e.aid = h.eid
join A t on t.aid = e.pid
join A c on c.aid = t.pid
join A p on p.aid = c.pid
left join U u on u.uid = hr.uId
left join E emp on emp.eid = hr.oid
where ( hr.s in (1,2,3,4) and hr.cn = 0 );
背景
MySQL執行語句過程中涉及到兩大流程：優化器和執行器。其中優化器最主要的任務，是選擇索引和在多表連接時選擇連接順序。在這個case中，join順序的選擇影響了執行性能。
確定join執行順序就需要估算所有join操作的代價。默認配置下MySQL會估算所有可能的組合。
MySQL Tips: MySQL裏限製一個查詢的join表數目上限為61.
對於一個有61個表參與的join操作，理論上需要61!(階乘)次的評估。當然這是最壞情況下，實際上減枝算法會讓這個數字看起來稍微好一點，但是仍然很恐怖。
在多表join的場景下，為了避免優化器占用太多時間，MySQL提供了一個參數 optimizer_search_depth 來控製遞歸深度。
這個參數對算法的控製可以簡單描述為：對於所有的排列，隻取前當前join順序的前optimizer_search_depth個表估算代價。舉例來說，20張表的，假設optimizer_search_depth為4，那麼評估次數為20*19*18*17，雖然也很大（因此我們特別不建議這麼多表的join），比20！好多了。
於是optimizer_search_depth的選擇就成了問題。
MySQL Tips: MySQL中optimizer_search_depth默認值為62.也就是說默認為全排列計算。
這樣能夠保證得到最優的執行計劃，隻是在有些場景下，決定執行計劃的時間會遠大於執行時間本身。
量化分析
在ECS上，是用戶自己維護的MySQL，沒有設置optimizer_search_depth，因此為默認的62。在RDS上，我們的配置是4。 分析到這裏大家能猜到原因是RDS配置的4導致沒有得到最優的執行計劃。
下圖是optimizer_search_depth=4時的explain結果（隱藏了業務相關的表名、字段名)
下圖是optimizer_search_depth=62是的場景，當然這個case的join表是8個，因此62和8在這裏是等效的。
從圖1可以看到，由於optimizer_search_depth=4，優化器認為自己選擇了最優的join順序(22039*1*1*1)，優於(41360*1*1*1)，而實際上後者才是全局最優。
關於實踐
可配置的參數提供靈活性的同時，也提出一個頭疼的問題：應該設置為多少才合適。 實際上當用戶執行一個多表join的時候，對這個語句的整體RT的期望值就不會高。因此可以先定義一個預期，比如優化器決策join順序的時間不能超過500ms。 用戶規格與cpu相關，因此這個隻能是建議值。
用戶實踐
實際上更重要的是對於用戶來說：
1) 當出現實例遷移後，多表join執行結果差異較大的時候，要考慮調整這個值。該參數是允許線程單獨設置，因此對於應用層來說，每個連接應該都能得到一個較優的值。
2) 反過來，當設置為默認的optimizer_search_depth=62時，我們我們如何評估我們這個設置是否過大?
MySQL Tips:MySQL profiling 可以用於查看各執行環節的消耗時間。
如下是筆者構造的一個60個表join查詢的查詢，使用profiling查看執行環節消耗的過程。
set profiling=1;
set optimizer_search_depth=4;
explain select …….
show profile for query 2;
結果如圖
繼續執行
set optimizer_search_depth=40;
explain select …….
show profile for query 4;
小結
1)根據機器配置估算一個可接受的時間，用於優化器選擇join順序。
2)用profiling確定是否設置了過大的optimizer_search_depth。
3)業務上優化，盡量不要使用超過10張表的多表join。
4)PS:不要相信銀彈。MySQL文檔說明設置為0則表示能夠自動選擇optimizer_search_depth的合理值，實際上代碼上策略就是，如果join表數N<=7，則optimizer_search_depth=N+1，否則選N.
最後更新：2017-04-03 08:26:15