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

創(chuàng)新互聯(lián)是一家集網(wǎng)站建設(shè),南票企業(yè)網(wǎng)站建設(shè),南票品牌網(wǎng)站建設(shè),網(wǎng)站定制,南票網(wǎng)站建設(shè)報價,網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,南票網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強企業(yè)競爭力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時我們時刻保持專業(yè)、時尚、前沿，時刻以成就客戶成長自我，堅持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實用型網(wǎng)站。

一個用戶問題，數(shù)據(jù)從ECS遷移到RDS，相同的語句，查詢性能下降了幾十倍。而實際上RDS這個實例在內(nèi)存上的配置與原來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執(zhí)行語句過程中涉及到兩大流程：優(yōu)化器和執(zhí)行器。其中優(yōu)化器最主要的任務(wù)，是選擇索引和在多表連接時選擇連接順序。在這個case中，join順序的選擇影響了執(zhí)行性能。

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

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

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

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

MySQL Tips: MySQL中optimizer_search_depth默認值為62.也就是說默認為全排列計算。

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

量化分析

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

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

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

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

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

用戶實踐 
實際上更重要的是對于用戶來說：

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

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

如下是筆者構(gòu)造的一個60個表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;  圖中標紅部分顯示了兩次優(yōu)化器的執(zhí)行時間差異。

小結(jié)

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

多表連接的參數(shù)

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

名稱欄目：相同sql不同機器上效率差異case有哪些-創(chuàng)新互聯(lián)
網(wǎng)頁鏈接：http://weahome.cn/article/dcpsih.html