join 优化的基础原则有哪些

发布时间：2023-09-01 12:34:15 所属栏目：MySql教程来源：

导读：这篇文章给大家分享的是有关join 优化的基本原则有哪些的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。1、join 优化的基本原则： a：小结果集驱动大结果集 b: 确保被驱动的表被索引

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

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 ;

说明：sql 功能是获取每个省的电话号码数量。cis_data_qixin_score：号码表，TMP_A1_INFO_MOBILE_H_20151201 号码H码表，TMP_A1_DICT_AREA_20151201

号码H码对应省份表

执行计划：

+----+-------------+----------------------+-------+-------------------+-------------------+---------+------+-----------+---------------------------------------+

+----+-------------+----------------------+-------+-------------------+-------------------+---------+------+-----------+---------------------------------------+

| 1 | PRIMARY | b | ref | idx_mobile_h_code | idx_mobile_h_code | 33 | func | 1 | Using index condition; Using where |

+----+-------------+----------------------+-------+-------------------+-------------------+---------+------+-----------+---------------------------------------+

分析：由执行计划可知 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

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 ;

执行计划：

+----+-------------+----------------------+-------+-------------------+-------------+---------+----------------------------+-----------+----------------------------------------------------+

+----+-------------+----------------------+-------+-------------------+-------------+---------+----------------------------+-----------+----------------------------------------------------+

| 1 | PRIMARY | | ref | | | 23 | cis_gather.b.mobile_h_code | 390 | Using where |

+----+-------------+----------------------+-------+-------------------+-------------+---------+----------------------------+-----------+----------------------------------------------------+

5 rows in set (0.00 sec)

解析：通过对号码前7位分组得到物化表【大概35w】自动创建索引 PHONE_h_code 作为匹配表。join 代价为350000T

改写前后join 代价之比为： 124364159T /350000T = 355 哈哈是不是有种飞起来的赶脚。

结果：优化后的sql 4 分钟搞定。

（编辑：银川站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

join 优化的基础原则 有哪些

join 优化的基础原则有哪些