join 优化的基本原则有哪些

92次阅读

共计 4413 个字符，预计需要花费 12 分钟才能阅读完成。

这篇文章给大家分享的是有关 join 优化的基本原则有哪些的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，一起跟随丸趣 TV 小编过来看看吧。

1、join 优化的基本原则：
a：小结果集驱动大结果集
b: 确保被驱动的表被索引
c: 不能确保驱动表被索引加大 join_buffer_size 的大小。

原理：mysql 的 join 算法只有一种 Nested Loop Join 算法。其最基本原理是循环取驱动表中的每一条记录，
到匹配表中过滤，得到结果集 list，再次循环 list 每条记录到下个匹配表中过滤，以此类推。

伪代码【2 表关联】：
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 嵌套循环的代价取决于，内外循环代价的乘积。即【驱动表行数】N*M【到匹配表中查找一次代价】
innodb B+ 树索引的高度一般是 3 至 4，也就是说一般情况下不管是哪个表作为匹配表，其一次查询代价是常量 T
即 Join 代价: N【表行数】*T【常量】所以要用小结果集作为驱动表，另外强调一点是小结果集而不是小表，因为小、大是相对的，完全有可能大表通过过滤的结果
集比小表还要小的多。所以强调小结果集。

案例：1.2 亿大表关联，优化前执行 3 个小时没有结果。。。。。。阿拉好想唱 “等你爱我爱我哪怕只有一次也就足够 ……..”
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 ;

分析：由执行计划可知 cis_data_qixin_score 通过号码去重后的衍生表 DERIVED2 的 124364159 条记录【实际大概 8 千万，执行计划统计的不太精确】作为驱动表和匹
配表 idx_mobile_h_code 35W 条记录进行 Jion。这个正犯了“兵家之大忌”，大的结果集作为驱动表其代价为 124364159T 。又不能选择小表作为驱动表，
怎么办？现在的妹子不都是天天企盼着要减肥嘛，那我们也对“驱动表”这个妹子瘦瘦身吧。

sql 功能分析：通过号码表和号码码表 substr(a.PHONE,1,7)=b.mobile_h_code 关联得到号码的省份的 code, 在和省份表关联得到省份名称，最后通过省份名称分组得
出所有省份的号码数量。也就是说 PHONE 的前七位的的数字对应相同的省份 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 ;