相同sql不同機器上效率差異case有哪些

這篇文章將為大家詳細講解有關相同sql不同機器上效率差異case有哪些，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

一個用戶問題，數據從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)，而實際上后者才是全局最優。

有趣的是，在這個case里面如果多看一層，就能得到最有解，因為第一個join順序的第五個表評估rows為82720。

這意味著，在這個case里面，設置為5與設置為62能得到相同的執行計劃，當然設置為5時的優化器執行代價更小。這其實也就是提供optimizer_search_depth的本意：減少優化器執行時間，而且概率上還存在局部最優就是全局最優解的情況。

關于實踐 
可配置的參數提供靈活性的同時，也提出一個頭疼的問題：應該設置為多少才合適。 
實際上當用戶執行一個多表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.

多表連接的參數

關于“相同sql不同機器上效率差異case有哪些”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。