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

成都創(chuàng)新互聯(lián)公司專注于滄源企業(yè)網(wǎng)站建設(shè),自適應(yīng)網(wǎng)站建設(shè),商城開發(fā)。滄源網(wǎng)站建設(shè)公司,為滄源等地區(qū)提供建站服務(wù)。全流程按需定制，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，成都創(chuàng)新互聯(lián)公司專業(yè)和態(tài)度為您提供的服務(wù)

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

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

 偽代碼【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)的代價(jià)取決于，內(nèi)外循環(huán)代價(jià)的乘積。即 【驅(qū)動(dòng)表行數(shù)】N*M【到匹配表中查找一次代價(jià)】
     innodb B+ 樹索引的高度一般是3 至 4，也就是說(shuō)一般情況下不管是哪個(gè)表作為匹配表，其一次查詢代價(jià)是常量 T
     即Join代價(jià): N【表行數(shù)】*T【常量】所以 要用小結(jié)果集作為驅(qū)動(dòng)表，另外強(qiáng)調(diào)一點(diǎn)是小結(jié)果集而不是小表，因?yàn)樾?、大 是相對(duì)的，完全有可能大表通過過濾的結(jié)果
     集比小表還要小的 多。所以強(qiáng)調(diào)小結(jié)果集。

案例：1.2 億大表關(guān)聯(lián) ，優(yōu)化前執(zhí)行3個(gè)小時(shí)沒有結(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  ; 

說(shuō)明：sql 功能是獲取 每個(gè)省的 電話號(hào)碼數(shù)量。cis_data_qixin_score：號(hào)碼表，TMP_A1_INFO_MOBILE_H_20151201  號(hào)碼H碼表，TMP_A1_DICT_AREA_20151201
        號(hào)碼H碼對(duì)應(yīng)省份表
執(zhí)行計(jì)劃：
+----+-------------+----------------------+-------+-------------------+-------------------+---------+------+-----------+---------------------------------------+
| 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                           |
+----+-------------+----------------------+-------+-------------------+-------------------+---------+------+-----------+---------------------------------------+

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

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

執(zhí)行計(jì)劃：
+----+-------------+----------------------+-------+-------------------+-------------+---------+----------------------------+-----------+----------------------------------------------------+
| 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)


解析：通過 對(duì)號(hào)碼前7位分組得到物化表【大概35w】自動(dòng)創(chuàng)建索引   PHONE_h_code 作為匹配表 。join 代價(jià)為350000T
      改寫前后join 代價(jià)之比為： 124364159T /350000T = 355 哈哈 是不是有種飛起來(lái)的趕腳。

結(jié)果： 優(yōu)化后的sql  4  分鐘 搞定。

感謝各位的閱讀！關(guān)于“join 優(yōu)化的基本原則有哪些”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！

本文標(biāo)題：join優(yōu)化的基本原則有哪些
文章轉(zhuǎn)載：http://jinyejixie.com/article36/gggdsg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站收錄、網(wǎng)站策劃、手機(jī)網(wǎng)站建設(shè)、全網(wǎng)營(yíng)銷推廣、網(wǎng)站維護(hù)、小程序開發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)