相同sql不同機(jī)器上效率差異case有哪些

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

一個(gè)用戶問題，數(shù)據(jù)從ECS遷移到RDS，相同的語句，查詢性能下降了幾十倍。而實(shí)際上RDS這個(gè)實(shí)例在內(nèi)存上的配置與原來ECS上的實(shí)例相當(dāng)。

本文簡單說明這個(gè)case的原因及建議。

用戶反饋性能變慢的語句為 （修改了真實(shí)表名和列名）
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執(zhí)行語句過程中涉及到兩大流程：優(yōu)化器和執(zhí)行器。其中優(yōu)化器最主要的任務(wù)，是選擇索引和在多表連接時(shí)選擇連接順序。在這個(gè)case中，join順序的選擇影響了執(zhí)行性能。

確定join執(zhí)行順序就需要估算所有join操作的代價(jià)。默認(rèn)配置下MySQL會(huì)估算所有可能的組合。
MySQL Tips: MySQL里限制一個(gè)查詢的join表數(shù)目上限為61.

對(duì)于一個(gè)有61個(gè)表參與的join操作，理論上需要61!(階乘)次的評(píng)估。當(dāng)然這是最壞情況下，實(shí)際上減枝算法會(huì)讓這個(gè)數(shù)字看起來稍微好一點(diǎn)，但是仍然很恐怖。

在多表join的場景下，為了避免優(yōu)化器占用太多時(shí)間，MySQL提供了一個(gè)參數(shù) optimizer_search_depth 來控制遞歸深度。
這個(gè)參數(shù)對(duì)算法的控制可以簡單描述為：對(duì)于所有的排列，只取前當(dāng)前join順序的前optimizer_search_depth個(gè)表估算代價(jià)。舉例來說，20張表的，假設(shè)optimizer_search_depth為4，那么評(píng)估次數(shù)為20*19*18*17，雖然也很大（因此我們特別不建議這么多表的join），比20！好多了。

于是optimizer_search_depth的選擇就成了問題。

MySQL Tips: MySQL中optimizer_search_depth默認(rèn)值為62.也就是說默認(rèn)為全排列計(jì)算。

這樣能夠保證得到最優(yōu)的執(zhí)行計(jì)劃，只是在有些場景下，決定執(zhí)行計(jì)劃的時(shí)間會(huì)遠(yuǎn)大于執(zhí)行時(shí)間本身。

量化分析

在ECS上，是用戶自己維護(hù)的MySQL，沒有設(shè)置optimizer_search_depth，因此為默認(rèn)的62. 
在RDS上，我們的配置是4。 
分析到這里大家能猜到原因是RDS配置的4導(dǎo)致沒有得到最優(yōu)的執(zhí)行計(jì)劃。

下圖是optimizer_search_depth=4時(shí)的explain結(jié)果（隱藏了業(yè)務(wù)相關(guān)的表名、字段名)  下圖是optimizer_search_depth=62是的場景，當(dāng)然這個(gè)case的join表是8個(gè)，因此62和8在這里是等效的。  從圖1可以看到，由于optimizer_search_depth=4，優(yōu)化器認(rèn)為自己選擇了最優(yōu)的join順序(22039*1*1*1)，優(yōu)于(41360*1*1*1)，而實(shí)際上后者才是全局最優(yōu)。

有趣的是，在這個(gè)case里面如果多看一層，就能得到最有解，因?yàn)榈谝粋€(gè)join順序的第五個(gè)表評(píng)估rows為82720。

這意味著，在這個(gè)case里面，設(shè)置為5與設(shè)置為62能得到相同的執(zhí)行計(jì)劃，當(dāng)然設(shè)置為5時(shí)的優(yōu)化器執(zhí)行代價(jià)更小。這其實(shí)也就是提供optimizer_search_depth的本意：減少優(yōu)化器執(zhí)行時(shí)間，而且概率上還存在局部最優(yōu)就是全局最優(yōu)解的情況。

關(guān)于實(shí)踐 
可配置的參數(shù)提供靈活性的同時(shí)，也提出一個(gè)頭疼的問題：應(yīng)該設(shè)置為多少才合適。 
實(shí)際上當(dāng)用戶執(zhí)行一個(gè)多表join的時(shí)候，對(duì)這個(gè)語句的整體RT的期望值就不會(huì)高。因此可以先定義一個(gè)預(yù)期，比如優(yōu)化器決策join順序的時(shí)間不能超過500ms。 
用戶規(guī)格與cpu相關(guān)，因此這個(gè)只能是建議值。

用戶實(shí)踐 
實(shí)際上更重要的是對(duì)于用戶來說：

1) 當(dāng)出現(xiàn)實(shí)例遷移后，多表join執(zhí)行結(jié)果差異較大的時(shí)候，要考慮調(diào)整這個(gè)值。該參數(shù)是允許線程單獨(dú)設(shè)置，因此對(duì)于應(yīng)用層來說，每個(gè)連接應(yīng)該都能得到一個(gè)較優(yōu)的值。

2) 反過來，當(dāng)設(shè)置為默認(rèn)的optimizer_search_depth=62時(shí)，我們我們?nèi)绾卧u(píng)估我們這個(gè)設(shè)置是否過大?
MySQL Tips:MySQL profiling 可以用于查看各執(zhí)行環(huán)節(jié)的消耗時(shí)間。

如下是筆者構(gòu)造的一個(gè)60個(gè)表join查詢的查詢，使用profiling查看執(zhí)行環(huán)節(jié)消耗的過程。
  set profiling=1;
  set optimizer_search_depth=4;
  explain select .......
  show profile for query 2;   
  結(jié)果如圖  繼續(xù)執(zhí)行
  set optimizer_search_depth=40;
  explain select .......
  show profile for query 4;  圖中標(biāo)紅部分顯示了兩次優(yōu)化器的執(zhí)行時(shí)間差異。

小結(jié)

1)根據(jù)機(jī)器配置估算一個(gè)可接受的時(shí)間，用于優(yōu)化器選擇join順序。
2)用profiling確定是否設(shè)置了過大的optimizer_search_depth。
3)業(yè)務(wù)上優(yōu)化，盡量不要使用超過10張表的多表join。
4)PS:不要相信銀彈。MySQL文檔說設(shè)置為0則表示能夠自動(dòng)選擇optimizer_search_depth的合理值，實(shí)際上代碼上策略就是，如果join表數(shù)N<=7，則optimizer_search_depth=N+1，否則選N.

多表連接的參數(shù)

關(guān)于“相同sql不同機(jī)器上效率差異case有哪些”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。