join 優(yōu)化的基本原則有哪些

124次閱讀

共計 4413 個字符，預(yù)計需要花費 12 分鐘才能閱讀完成。

這篇文章給大家分享的是有關(guān) join 優(yōu)化的基本原則有哪些的內(nèi)容。丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，一起跟隨丸趣 TV 小編過來看看吧。

1、join 優(yōu)化的基本原則：
a：小結(jié)果集驅(qū)動大結(jié)果集
b: 確保被驅(qū)動的表被索引
c: 不能確保驅(qū)動表被索引加大 join_buffer_size 的大小。

原理：mysql 的 join 算法只有一種 Nested Loop Join 算法。其最基本原理是循環(huán)取驅(qū)動表中的每一條記錄，
到匹配表中過濾，得到結(jié)果集 list，再次循環(huán) list 每條記錄到下個匹配表中過濾，以此類推。

偽代碼【2 表關(guān)聯(lián)】：
for each recode in table_a {
for each recode in table_b that table_a.column=table_b.column {
combination to output;
}
}

解析：Nested Loop Join 嵌套循環(huán)的代價取決于，內(nèi)外循環(huán)代價的乘積。即【驅(qū)動表行數(shù)】N*M【到匹配表中查找一次代價】
innodb B+ 樹索引的高度一般是 3 至 4，也就是說一般情況下不管是哪個表作為匹配表，其一次查詢代價是常量 T
即 Join 代價: N【表行數(shù)】*T【常量】所以要用小結(jié)果集作為驅(qū)動表，另外強調(diào)一點是小結(jié)果集而不是小表，因為小、大是相對的，完全有可能大表通過過濾的結(jié)果
集比小表還要小的多。所以強調(diào)小結(jié)果集。

案例：1.2 億大表關(guān)聯(lián)，優(yōu)化前執(zhí)行 3 個小時沒有結(jié)果。。。。。。阿拉好想唱 “等你愛我愛我哪怕只有一次也就足夠 ……..”
select c.current_name,count(*)
from ( select distinct PHONE from cis_data_qixin_score )a
join TMP_A1_INFO_MOBILE_H_20151201 b on substr(a.PHONE,1,7)=b.mobile_h_code
join TMP_A1_DICT_AREA_20151201 c on c.tele_code=b.prov_telecode
group by c.current_name ;

分析：由執(zhí)行計劃可知 cis_data_qixin_score 通過號碼去重后的衍生表 DERIVED2 的 124364159 條記錄【實際大概 8 千萬，執(zhí)行計劃統(tǒng)計的不太精確】作為驅(qū)動表和匹
配表 idx_mobile_h_code 35W 條記錄進行 Jion。這個正犯了“兵家之大忌”，大的結(jié)果集作為驅(qū)動表其代價為 124364159T 。又不能選擇小表作為驅(qū)動表，
怎么辦？現(xiàn)在的妹子不都是天天企盼著要減肥嘛，那我們也對“驅(qū)動表”這個妹子瘦瘦身吧。

sql 功能分析：通過號碼表和號碼碼表 substr(a.PHONE,1,7)=b.mobile_h_code 關(guān)聯(lián) 得到號碼的省份的 code, 在和省份表關(guān)聯(lián) 得到省份名稱，最后通過省份名稱分組得
出所有省份的號碼數(shù)量。也就是說 PHONE 的前七位的的數(shù)字對應(yīng)相同的省份 code。一言以蔽之。直接對 phone 的前七位分組，再 join。

改寫后 sql:
select c.current_name,sum(a.cou)
from (
select substr(a.PHONE,1,7) PHONE_h_code ,count(*) cou
from (select distinct PHONE from cis_data_qixin_score ) a
group by substr(a.PHONE,1,7) order by null
)a
join TMP_A1_INFO_MOBILE_H_20151201 b on a.PHONE_h_code=b.mobile_h_code
join TMP_A1_DICT_AREA_20151201 c on c.tele_code=b.prov_telecode
group by c.current_name ;