join 优化的基本原则有哪些

这篇文章给大家分享的是有关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码对应省份表
执行计划：
+----+-------------+----------------------+-------+-------------------+-------------------+---------+------+-----------+---------------------------------------+
| id | select_type | table                | type  | possible_keys     | key               | key_len | ref  | rows      | Extra                                 |
+----+-------------+----------------------+-------+-------------------+-------------------+---------+------+-----------+---------------------------------------+
|  1 | PRIMARY     | c                    | ALL   | NULL              | NULL              | NULL    | NULL |        41 | Using temporary; Using filesort       |
|  1 | PRIMARY     |            | ALL   | NULL              | NULL              | NULL    | NULL | 124364159 | Using join buffer (Block Nested Loop) |
|  1 | PRIMARY     | b                    | ref   | idx_mobile_h_code | idx_mobile_h_code | 33      | func |         1 | Using index condition; Using where    |
|  2 | DERIVED     | cis_data_qixin_score | index | PRIMARY,idx_phone | idx_phone         | 62      | NULL | 124364159 | Using index                           |
+----+-------------+----------------------+-------+-------------------+-------------------+---------+------+-----------+---------------------------------------+

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

执行计划：
+----+-------------+----------------------+-------+-------------------+-------------+---------+----------------------------+-----------+----------------------------------------------------+
| id | select_type | table                | type  | possible_keys     | key         | key_len | ref                        | rows      | Extra                                              |
+----+-------------+----------------------+-------+-------------------+-------------+---------+----------------------------+-----------+----------------------------------------------------+
|  1 | PRIMARY     | c                    | ALL   | NULL              | NULL        | NULL    | NULL                       |        41 | Using temporary; Using filesort                    |
|  1 | PRIMARY     | b                    | ALL   | idx_mobile_h_code | NULL        | NULL    | NULL                       |    318794 | Using where; Using join buffer (Block Nested Loop) |
|  1 | PRIMARY     |            | ref   |        |  | 23      | cis_gather.b.mobile_h_code |       390 | Using where                                        |
|  2 | DERIVED     |            | ALL   | NULL              | NULL        | NULL    | NULL                       | 124364170 | Using temporary                                    |
|  3 | DERIVED     | cis_data_qixin_score | index | PRIMARY,idx_phone | idx_phone   | 62      | NULL                       | 124364170 | Using index                                        |
+----+-------------+----------------------+-------+-------------------+-------------+---------+----------------------------+-----------+----------------------------------------------------+
5 rows in set (0.00 sec)


解析：通过 对号码前7位分组得到物化表【大概35w】自动创建索引   PHONE_h_code 作为匹配表 。join 代价为350000T
      改写前后join 代价之比为： 124364159T /350000T = 355 哈哈 是不是有种飞起来的赶脚。

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

感谢各位的阅读！关于“join 优化的基本原则有哪些”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！