文章綱要
該文章結(jié)合18張手繪圖例，21個SQL經(jīng)典案例廓推、近10000字刷袍，將Mysql索引優(yōu)化經(jīng)驗予以總結(jié)，你可以根據(jù)綱要來決定是否繼續(xù)閱讀樊展，完成這篇文章大概需要25-30分鐘呻纹，相信你的堅持是不負(fù)時光的：
- 前言
- 開篇小例子
單索引性能最優(yōu)堆生？
索引越多越好？ - 常用術(shù)語
主鍵索引（聚簇索引）
輔助索引
覆蓋索引
最左匹配
索引下推 - 再談優(yōu)化
覆蓋索引雷酪，減少回表
遵循最左匹配原則
聯(lián)合索引淑仆，字段順序
前綴索引
索引失效
大字段影響檢索性能
GROUP BY如何優(yōu)化
ORDER BY如何優(yōu)化
分頁性能優(yōu)化
ORDER BY再分頁BUG
JOIN性能優(yōu)化 - 寫在最后
前言
mysql是我們最常用的數(shù)據(jù)庫，基本很多業(yè)務(wù)系統(tǒng)都在使用哥力≌岬。可是往往在遇到性能問題的時候，總是束手無策吩跋。比如：
- 明明知道有索引的概念寞射，卻不知道這樣加索引是否能夠真正生效？
- 有的時候锌钮，想繼續(xù)增加索引卻又擔(dān)心索引加的太多桥温，那索引最多能加幾個，加的太多有沒有什么影響呢梁丘？
- 一個表的多個索引中經(jīng)常出現(xiàn)一些重復(fù)的字段侵浸，他們到底存在有沒有意義？還是冗余的索引呢氛谜？
經(jīng)過一周的梳理掏觉，我將工作中最常用的索引優(yōu)化手段和方法梳理出來，足以解釋上述疑問混蔼。同時履腋，相信你跟著我的思路來閱讀這篇文章珊燎，你對mysql索引的理解會有一個更高的層次提升惭嚣，在工作中不再茫然。
今天這篇文章是根據(jù)我在京東內(nèi)部分享的ppt整理而來悔政，從很多很多角度來看待索引優(yōu)化的問題晚吞，比如：索引為什么失效、order by的性能提升與避雷谋国、group by是否能夠提升性能槽地、深分頁存在哪些問題及如何優(yōu)化、join的時候如何選擇驅(qū)動表等芦瘾。
除此之外呢捌蚊，面試中也會提及一些常見的關(guān)于索引的概念，這篇文章也會通過一些例子來幫助你深入淺出索引中的奧秘近弟，比如：索引下推、覆蓋索引和回表等。
同時综慎，通過這篇文章的理解，你在使用其他數(shù)據(jù)庫赦颇，比如mongo或者類似的索引類型，也可以舉一反三赴涵。文中有大量的例子和SQL語句以及執(zhí)行的原理媒怯。如果你肯花上一點時間來跟我一起理解。相信你在SQL優(yōu)化領(lǐng)域會與眾不同髓窜！開始吧扇苞！
下文主要針對InnoDB存儲引擎的B+樹作為前提來闡述，不再敖述寄纵。
開篇小例子
為了能夠更好地理解后續(xù)章節(jié)的優(yōu)化介紹杨拐，我先通過一個小例子，讓大家明白一個簡單的查詢語句的執(zhí)行過程擂啥、邏輯及原理哄陶。
這里所說的執(zhí)行過程不是指：Mysql語法詞法解析器、優(yōu)化器哺壶、執(zhí)行器等宏觀的維度屋吨，而是偏向索引樹的維度。
我們依舊采用大家最熟悉的學(xué)生表(student)來舉例吧山宾，看下圖：
圖1：student表及索引說明
學(xué)生表至扰，包含：id、number（學(xué)號）资锰、name（姓名）敢课、sex（性別）、age（年齡）绷杜，并且id為主鍵直秆，其他字段分別有一個單獨索引。
類似這樣的索引設(shè)計鞭盟，在我的工作中經(jīng)常遇到圾结，當(dāng)然也隨著查詢邏輯的復(fù)雜性提升，這種單字段索引也會變得越來越多齿诉。之所以出現(xiàn)這樣的情況筝野，是對索引的理解和用法并不深入導(dǎo)致的，在茫然的時候選擇了：加單索引來解決性能問題的方法粤剧。
我猜大家可能會有這樣的兩個疑問：
- 創(chuàng)建單字段索引性能最優(yōu)歇竟？如果不是，那該如何加索引呢?
- 是不是索引越多越好抵恋？因為越多越容易命中焕议？
單索引性能最優(yōu)？
首先來看單索引性能是否最優(yōu)馋记？為了說明這個問題号坡，我還是先準(zhǔn)備一點數(shù)據(jù)幫助理解：
圖2：student表數(shù)據(jù)準(zhǔn)備
student表有很多數(shù)據(jù)懊烤，1~7條數(shù)據(jù)如上圖所示，其中第一條黃色背景的數(shù)據(jù)宽堆，是如下SQL的命中結(jié)果：
SELECT * FROM student WHERE age = 7 AND sex = '男' ;
那這條SQL語句是如何執(zhí)行的呢腌紧？前面我們給這個表加了4個非主鍵索引，既然我們用了兩個查詢條件畜隶，因此壁肋，為了提升檢索性能，mysql的優(yōu)化器會選擇其中的一個索引樹去查找籽慢。這里我們做一個假設(shè)浸遗，假設(shè)優(yōu)化器選擇idx_age這個索引,當(dāng)然選擇idx_sex這個索引，跟我們下面闡述的原理是一樣的箱亿。
我手繪了idx_age和主鍵索引的簡圖跛锌，輔助理解：
圖3：主鍵索引簡圖
圖4：idx_age索引簡圖
- 主鍵索引
主鍵索引的葉子節(jié)點17是主鍵id，它下方的R1R7是我對行記錄（也就是全字段內(nèi)容）的簡寫届惋。其中髓帽，紅色的背景R1就是查詢命中的結(jié)果。 - idx_age索引
idx_age為非聚簇索引脑豹，索引的葉子節(jié)點為年齡+主鍵Id郑藏，順便提一下，不知道你有沒有考慮為什么該索引的葉子節(jié)點不直接掛的是行記錄呢瘩欺？ 我想原因有二：1.主鍵已經(jīng)有行記錄必盖，再次存儲占用額外的空間，如果二級索引更多俱饿，存儲冗余就更大 2.mysql的存儲以page為存儲單元歌粥，如果單索引鍵空間占用更多，一個page能容納的鍵更少稍途，導(dǎo)致樹更深阁吝，檢索需要更多的IO訪問。
回過頭說下械拍，上述語句的執(zhí)行過程吧：
- 1.在age索引樹查找age=7的記錄，取得ID=1;
- 2.繼續(xù)到主鍵索引樹查找ID=1對應(yīng)的數(shù)據(jù)R1装盯，判斷R1.sex等于’男’坷虑，返回到結(jié)果集中;
- 3.在age索引樹查找下一個age=7的記錄，取得ID=2;
- 4.繼續(xù)到主鍵索引樹查找ID=2對應(yīng)的數(shù)據(jù)R2埂奈，判斷R2.sex不等于’男’迄损，丟棄;
- 5.繼續(xù)第3步和第4步，發(fā)現(xiàn)ID=3的記錄也不滿足條件
- 6.當(dāng)在age索引樹上找到age=10的記錄時账磺，不滿足條件芹敌，循環(huán)結(jié)束痊远。
你會發(fā)現(xiàn)，整個查找執(zhí)行的過程中氏捞，查詢主鍵索引樹3次碧聪，查詢主鍵索引的目的有兩個：一是為了查詢sex是否滿足條件，二是為了返回需要的全字段液茎。
單索引執(zhí)行是這樣逞姿，那我們繼續(xù)看一下，如果我們基于SQL語句的兩個查詢字段age捆等、sex建立聯(lián)合索引滞造，執(zhí)行過程是怎樣的呢？是否會提升性能呢栋烤？
同樣谒养，我手繪了一張聯(lián)合索引的簡圖如下：
圖5：idx_age_sex聯(lián)合索引簡圖
聯(lián)合索引中(5,'男')代表一個索引鍵 ，5是年齡,'男'是性別明郭。同樣葉子節(jié)點的綠色陰影部分為Id值蝴光。
聯(lián)合索引的執(zhí)行情況是這樣的：
- 1.在聯(lián)合索引樹上查找age=7并且sex=‘男’的記錄ID=1;
- 2.繼續(xù)到主鍵索引樹查找ID=1對應(yīng)的數(shù)據(jù)R1, 返回到結(jié)果集中。
你會發(fā)現(xiàn)這次執(zhí)行僅僅為了返回需要的全字段,才執(zhí)行了一次主鍵索引樹的查詢达址，比單字段索引少了2次蔑祟。少的這兩次主鍵索引樹查詢你知道意味著什么嗎？ 減少了至少兩次的IO訪問（因為mysql為了提升性能會將部分頁緩存沉唠，暫時忽略這種情況）疆虚。我們都知道IO、CPU满葛、內(nèi)存是mysql性能優(yōu)化的幾大主要影響因素和考慮點径簿。
因此，我們可以得出一個結(jié)論：使用聯(lián)合索引能提升索引命中率嘀韧，減少回表篩選帶來的IO損耗篇亭。相反單索引需要更多的回表次數(shù)。
索引越多越好锄贷？
可能有人有這樣的索引誤區(qū)译蒂，索引越多越容易命中？是不是這樣的呢谊却？首先看下增加索引會帶來什么影響呢柔昼？
1.索引需要占用存儲空間
索引是一種性能優(yōu)化的數(shù)據(jù)結(jié)構(gòu)，本身也是一種采用空間換時間的思路來提升查詢性能炎辨。因此捕透，增加索引的數(shù)量一定會導(dǎo)致對應(yīng)的增加存儲空間。2.索引更新需要更多維護(hù)成本
我們一直在討論索引的查詢，可我們不能忽略的一點是乙嘀，當(dāng)Insert末购、Update、Delete等操作也帶來索引的更新和維護(hù)虎谢，因此盟榴，索引的數(shù)量也會帶來更多的維護(hù)成本，你說呢嘉冒？
因此曹货，綜合所有該表的SQL的查詢條件，合理規(guī)劃索引的個數(shù)讳推，避免冗余索引的出現(xiàn)顶籽，有助于降低維護(hù)成本。比如下面的索引银觅，可以將左圖兩個索引優(yōu)化為右圖一個索引：
圖6：冗余索引去除
至于為什么可以這樣優(yōu)化礼饱，通過閱讀下文，你會得到答案究驴。
常用術(shù)語
圍繞著索引有很多術(shù)語镊绪，也許你經(jīng)常聽到或者在面試中被問到，但是卻沒有理解是什么意思洒忧。但是為了提升自身逼格和自我的虛榮心滿足蝴韭，那么，我們一起回顧下吧：
- 主鍵索引
以下是student表的主鍵索引熙侍，它也叫做一級索引榄鉴。有的時候也有人稱它為聚簇索引，聚簇索引是因葉子節(jié)點的id和行數(shù)據(jù)聚簇在一起而得名蛉抓，如下圖：
圖7：主鍵索引
- 輔助索引
而與主鍵索引的對應(yīng)的就是輔助索引庆尘，它也叫做二級索引。由于葉子節(jié)點上無行數(shù)據(jù)巷送，只有一個id驶忌，因此它是非聚簇索引。
圖8：輔助索引簡圖
- 覆蓋索引
覆蓋索引并不是一種索引類型笑跛，而是一種索引查詢的形式和行為付魔。覆蓋索引往往應(yīng)用于聯(lián)合索引。下圖就是一個由age堡牡、sex組成的聯(lián)合索引：
圖9：聯(lián)合索引簡圖
當(dāng)查詢條件運用了索引抒抬，并且SELECT的字段也覆蓋在該索引樹上，也就是一顆索引樹既滿足了檢索也滿足了結(jié)果晤柄，無需為了拿到需要的SELECT字段而去回表的一種方式。
細(xì)心的你會發(fā)現(xiàn)id在SELECT的字段中妖胀，也是可以走覆蓋索引的芥颈。 以下SQL就是運用了覆蓋索引的例子：
SELECT sex,age,id FROM student WHERE age=7;
- 最左匹配
建立聯(lián)合索引的時候是否要考慮字段的順序惠勒？比如idx_A_B和idx_B_A是一樣的嗎？答案是否爬坑！
idx_A_B可以滿足以下兩個SQL走索引：
SELECT * FROM t WHERE A=1 AND B=1;SELECT * FROM t WHERE A=1;
而idx_B_A可以滿足這兩個SQL走索引：
SELECT * FROM t WHERE A=1 AND B=1;SELECT * FROM t WHERE B=1;
順序不同導(dǎo)致的效果也截然不同纠屋。查詢條件只能根據(jù)索引由左到右的順序來匹配索引，而不可以跨索引字段盾计。
提示：WHERE A=1 AND B=1 或者WHERE B=1 AND A=1是沒有任何區(qū)別的售担。優(yōu)化器已經(jīng)幫助我們做好了優(yōu)化。
- 索引下推
我們來看下在下圖這個聯(lián)合索引前提下署辉，根據(jù)文章開頭給出的數(shù)據(jù)樣例族铆，看看這個SQL在不同的MYSQL版本中如何執(zhí)行的呢？
圖10：聯(lián)合索引idx_name_age
SELECT name FROM studentWHERE name like ‘小%’ AND age=7
5.6之前的版本
- 1.根據(jù)name從聯(lián)合索引查找到7條name以“小”開頭的記錄的ID哭尝；
- 2.根據(jù)ID回表到主鍵索引查找全字段哥攘，篩選age=7的記錄，返回材鹦。
圖11：5.6之前服務(wù)層與引擎層流程圖
因此逝淹，回表7次。
5.6及之后的版本
- 1.根據(jù)name從聯(lián)合索引查找到7條name以“小”開頭的記錄桶唐，由于索引上存儲了age字段栅葡，因此在該索引上就可以過濾出age=7的記錄，查找到符合條件的3條記錄的ID尤泽。
- 2.根據(jù)ID回表到主鍵索引查找全字段欣簇，返回結(jié)果集。
[圖片上傳中...(image-6ec8d0-1639963550371-2)]
圖12：5.6及以后服務(wù)層與引擎層流程圖
因此安吁，回表3次醉蚁。
以上描述的就是索引下推，你可能會有疑問鬼店，為什么叫下推网棍？不是左推、右推妇智、上推呢?其實滥玷，這個概念是相對MYSQL的層次劃分的，將MYSQL的服務(wù)層下推到存儲引擎層來過濾巍棱。索引下推少了5和8這兩步惑畴，這兩步也就是服務(wù)端參與的，將age=7在存儲引擎層完成了過濾航徙。
再談優(yōu)化
覆蓋索引如贷，減少回表
最常用的查詢操作就是Select * 操作，如果在二級索引進(jìn)行條件篩選，但為了獲取 全部字段杠袱，需要回表操作尚猿，前面提過，回表越多楣富，性能較差凿掂。因此，按需select字段纹蝴，讓where后的條件字段和select字段覆蓋索引減少回表次數(shù)庄萎，是非常重要的優(yōu)化手段。
遵循最左匹配原則
前面講到最左匹配原則塘安，涉及兩點：
假設(shè)student表上有這樣的索引：
idx_name_age糠涛。
- 字段從左向右匹配，如下：
## 能使用索引的name部分SELECT * FROM student WHERE name = '小一';## 無法使用索引SELECT * FROM student WHERE age = 7;
- 字符從左向右匹配耙旦，如下：
## 能使用索引的name部分SELECT * FROM student WHERE name like '小%';## 無法使用索引SELECT * FROM student WHERE name like '%小';
因此脱羡，我建議大家在設(shè)計索引的時候一定要考慮該原則，保證索引設(shè)計的合理性免都。不僅僅該原則涉及到索引的設(shè)計锉罐，同時也涉及到功能的設(shè)計。比如绕娘，某表數(shù)據(jù)量較大脓规，產(chǎn)品建議左右模糊匹配，出于性能考慮险领，可以建議產(chǎn)品的設(shè)計改為僅使用右模糊匹配侨舆。
聯(lián)合索引，字段順序
往往建立聯(lián)合索引绢陌，不管是idx_A_B或者idx_B_A都能滿足設(shè)計要求挨下，那么聯(lián)合索引字段的順序，怎樣設(shè)計才是最合理的脐湾？才能夠更長遠(yuǎn)呢臭笆？這里我給出兩點參考建議：
- 考慮索引的復(fù)用能力
復(fù)用能力很好理解，比如查詢有這幾種情況：A=1 AND B=1 AND C=1秤掌、A=1 AND B=1愁铺、A=1或者A>=1 再或者A=1 AND B>=1等等，這些情況下闻鉴，索引idx_A_B_C肯定是最合適茵乱，相反，idx_B_A_C或者idx_A_C_B等的復(fù)用能力要差一些孟岛。不妨瓶竭，你使用上面講到的最左原則來思考下吧督勺。
- 考慮空間的占用情況
為了說明這點，先看下面這些SQL：
SELECT * FROM student WHERE name ='小一' and age = 7;SELECT * FROM student WHERE name ='小一';SELECT * FROM student WHERE age = 7;
滿足這些SQL可以有以下兩種設(shè)計在验，你可以先思考下玷氏，你會選擇哪一種呢堵未？
[圖片上傳中...(image-c9eb39-1639963550370-1)]
圖13：索引1和索引2設(shè)計
你會發(fā)現(xiàn)索引1和索引2都可以滿足三個SQL腋舌，從復(fù)用能力上來說是等同的，那該如何抉擇呢渗蟹？答案是可以從儲存空間占用上考慮块饺。
idx_name_age和idx_age_name字段相同，空間占用沒有太大差別雌芽，而idx_age和idx_name一個為int類型授艰，一個為varchar(10)類型，int占用4字節(jié)世落，而varchar(10)占用32字節(jié)淮腾，相差了8倍，相信選擇哪一個索引屉佳，你已經(jīng)有了答案谷朝。
一個非空的varchar字段，在UTF8編碼下的長度計算公式為：3*len+2武花。
前綴索引
聯(lián)合索引字段順序圆凰，我們提及了存儲空間的考慮。不知道你沒有發(fā)現(xiàn)体箕，在郵箱字段上建立索引相同的后綴占用了重復(fù)的空間专钉。比如@xixihaha.com,那是否考慮僅使用前綴呢？
[圖片上傳中...(image-d7dbc6-1639963550370-0)]
圖14：郵箱前綴索引
就像上圖這樣去設(shè)計索引累铅，可以節(jié)省空間跃须。但是要重點考慮選擇前綴的大小，比如這個選擇5娃兽、6還是7合適呢菇民？你可以根據(jù)前綴的區(qū)分度來考慮。比如我們選擇了email(1)這樣的前綴换薄，那么區(qū)分度只有26個英文字母玉雾，顯然達(dá)不到索引設(shè)計預(yù)期的效果。
下面是添加前綴索引的語法轻要，你可以參考：
mysql> ALTER TABLE student ADD INDEX idx_email_6(email(6));
索引失效
索引往往會在某些情況下不按照我們預(yù)期的執(zhí)行方式執(zhí)行复旬，導(dǎo)致失效。我列舉了幾種常見的失效情況,SQL直白明了冲泥，請看以下示例：
- 索引字段函數(shù)操作
SELECT * FROM student WHERE left(name,1) = '小';
- 索引字段隱式類型強(qiáng)轉(zhuǎn)
ps:學(xué)號為varchar類型驹碍。
SELECT * FROM student WHERE number = 2021007;
- 索引字段運算符操作
SELECT * FROM student WHERE age+1 = 7;
- 負(fù)向查詢
包含：!=壁涎、<>、not in志秃、not like怔球、!>、!<等浮还。
SELECT * FROM student WHERE age != 7;
- 隱字符字符編碼
a表采用uft8編碼竟坛，b表采用utf8mb4編碼，當(dāng)使用字符串字段進(jìn)行join操作钧舌。
SELECT * FROM t1 a join t2 b on a.name = b.name;
大字段影響檢索性能
在設(shè)計表時担汤，我們會使用TEXT或者BLOB等類型來存儲大文本或者二進(jìn)制，而這些大字段對查詢性能的影響是比較大的洼冻。那是為什么呢崭歧？
回表查詢，需要將整行數(shù)據(jù)讀取撞牢，由于大字段占用空間較大率碾，帶來大量IO操作，影響數(shù)據(jù)讀取性能屋彪。
既然無法回避使用大字段所宰，我有兩點建議送給你吧：
覆蓋索引
使用覆蓋索引，避免回表對大字段的讀取撼班，從而避免帶來過多的IO操作歧匈。表超過10萬行，將大字段單獨放置一張表
我們在分表策略中砰嘁，有一種縱向拆分件炉，就是針對此種場景的一種分表設(shè)計思路。
GROUP BY如何優(yōu)化
不知道你有沒有使用關(guān)鍵字EXPLAIN去查看GROUP BY操作的執(zhí)行計劃矮湘，你會發(fā)現(xiàn)在EXTRA字段中出現(xiàn)類似filesort的關(guān)鍵字斟冕。這是因為默認(rèn)情況下，MySQL對所有GROUP BY col1缅阳，col2….的字段進(jìn)行排序磕蛇，類似在查詢中指定 ORDER BY col1，col2…一樣十办。因此秀撇，GROUP BY是默認(rèn)排序的。
因此向族，我們可以讓GROUP BY后的字段利用索引排序呵燕，或者你的業(yè)務(wù)場景不需要排序的情況下，可以使用以下語句禁用默認(rèn)排序：
SELECT age,count(*) FROM student GROUP BY age ORDER BY NULL;
ORDER BY如何優(yōu)化
ORDER BY是最常用的場景件相，因為很多業(yè)務(wù)都需要排序再扭，比如取排行TOP5氧苍，根據(jù)年齡排序，按照創(chuàng)建時間排序等泛范。我們知道索引樹是有序的让虐。如果ORDER BY能夠使用索引樹有序的先天特性，從而避免二次排序帶來的時間和空間的復(fù)雜度罢荡。明顯是提升排序性能的重要手段赡突。
因此，MySQL 可以使用一個索引來滿足ORDER BY子句柠傍，而不需要額外的排序麸俘。但需要遵守以下三個原則：
- WHERE 條件和 ORDER BY 使用相同的索引。
- ORDER BY 字段的順序和索引順序一致惧笛。
- ORDER BY 的字段都是升序或者都是降序。
以下這個排序語句逞泄，很好的結(jié)合了idx_age_name索引使用的三個原則：
SELECT * FROM student WHERE age = 7ORDER BY age ASC,name ASC;
分頁性能優(yōu)化
深分頁的時候患整，MYSQL查詢幾秒鐘的情況，你遇到過嗎喷众？不知道MYSQL在分頁時處于何種考慮各谚，LIMIT n,m,這個操作跳過n條數(shù)據(jù)需要進(jìn)行回表，導(dǎo)致我們下面這個SQL需要回表10萬次到千。
SELECT * FROM student where age = 10 LIMIT 100000,10
辦法總是有的昌渤，可換種思路避免這10萬次回表，來看SQL的優(yōu)化吧：
SELECT * FROM student s1INNER JOIN( SELECT id FROM student where age = 10 LIMIT 100000,10) s2 on s1.id = s2.id ;
ORDER BY再分頁BUG
工作中憔四，有人被這個BUG坑過嗎膀息？ORDER BY后分頁，相鄰兩頁存在重復(fù)數(shù)據(jù)了赵。無數(shù)次檢查SQL和代碼邏輯無誤潜支，BUG始終無法定位。 這是由于ORDER BY后的字段存在重復(fù)值的情況柿汛，比如age字段存在重復(fù)的值冗酿，導(dǎo)致分頁時，順序被打亂络断。
SELECT * FROM student ORDER BY age ASC LIMIT 1,15;
因此裁替，解決該問題的方法很簡單，基于age的排序后增加一個能確定唯一值的排序字段貌笨，比如我采用id字段再次排序：
SELECT * FROM student ORDER BY age ASC,ID ASC LIMIT 1,15;
JOIN性能優(yōu)化
JOIN也是多表關(guān)聯(lián)的常用的關(guān)鍵字弱判，有LEFT JOIN、RIGHT JOIN躁绸、JOIN等裕循。在了解JOIN性能優(yōu)化前臣嚣，需要明確：驅(qū)動表和被驅(qū)動表。
LEFT JOIN
左表是驅(qū)動表剥哑，右表是被驅(qū)動表RIGHT JOIN
右表時驅(qū)動表硅则，左表是被驅(qū)動表INNER JOIN
MYSQL會選擇數(shù)據(jù)量比較小的表作為驅(qū)動表，大表作為被驅(qū)動表
你會發(fā)現(xiàn)INNER JOIN的時候株婴，MYSQL選擇小表為驅(qū)動表怎虫，為什么呢？在弄清楚原因之前困介，我們了解JOIN的三種算法大审，我們用這個SQL來觀察三種算法的執(zhí)行過程：
SELECT t1.*,t2.* FROM table1 t1 LEFT JOIN table2 t2 on t1.a=t2.a;
假設(shè)：table1有100行數(shù)據(jù)，table2有1000行數(shù)據(jù)座哩。
- Index Nested-Loop Join（索引嵌套查詢連接）
既然是索引嵌套查詢連接徒扶，那肯定是依賴索引，我們假設(shè)這兩個表都有索引：idx_a根穷。執(zhí)行過程是這樣的：
1.從表t1中讀入一行數(shù)據(jù) R1姜骡；
2.從數(shù)據(jù)行R1中，取出a字段到表t2里去查找屿良；
3.根據(jù)idx_a索引取出表t2中滿足條件的行圈澈，跟R1組成一行，作為結(jié)果集的一部分尘惧；
4.重復(fù)執(zhí)行步驟1到3康栈，直到表t1的末尾循環(huán)結(jié)束。
你會發(fā)現(xiàn),總掃描行數(shù)為：200次喷橙，包括遍歷t1表的100次和嵌套查詢idx_a索引的100次啥么，因此，掃描次數(shù)受驅(qū)動表t1影響
圖15：索引嵌套查詢連接
- Simple Nested-Loop Join（簡單嵌套查詢連接）
依然是這個SQL重慢，如果沒有idx_a這個索引饥臂，執(zhí)行過程是什么樣的呢？
從表t1中讀入一行數(shù)據(jù) R1似踱；
從數(shù)據(jù)行R1中隅熙，取出a字段到表t2里去查找；
全表掃描取出表t2中滿足條件的行核芽，跟R組成一行囚戚， 作為結(jié)果集的一部分
重復(fù)執(zhí)行步驟1到3，直到表t1的末尾循環(huán)結(jié)束轧简。
失去了索引驰坊，形成了笛卡爾掃描，掃描次數(shù)為100100次哮独，100次的t1表的掃描和與t2表全表掃描比對拳芙，因此察藐，這個性能太差了，MYSQL并未采用此種算法舟扎。
圖16：簡單嵌套查詢連接
- Block Nested-Loop Join（分塊嵌套查詢連接）
分塊嵌套查詢鏈接是針對簡單嵌套查詢的解決方案分飞，采用Join Buffer緩存的方式，提升性能睹限。執(zhí)行過程是這樣的：
1.把表t1的數(shù)據(jù)讀入線程內(nèi)存join_buffer中
2.掃描表t2譬猫，把表t2中的每一行取出來，跟join_buffer中的數(shù)據(jù)做對比羡疗，滿足join條件的染服，作為結(jié)果集的一部分返回。
因此叨恨，盡量比對次數(shù)是10萬次柳刮，但表掃描次數(shù)為1100次，是table1和table2的數(shù)據(jù)總行數(shù)特碳。
圖17：采用JoinerBuffer嵌套查詢連接
然后诚亚，似乎我們遺漏了一個概念分塊，這里并未提及它午乓。你試想下table1數(shù)據(jù)量比較大，會把所有數(shù)據(jù)裝載到Joiner Buffer中嗎闸准？當(dāng)然會采用分而治之的方法益愈。這種方法就是分塊。
按照分塊的方式夷家，我們重新看下蒸其，假設(shè)table1的50條數(shù)據(jù)裝滿Joiner Buffer,再次看下執(zhí)行流程吧：
圖18：采用JoinerBuffer分塊嵌套查詢連接
table1將分為兩次裝載到Join Buffer與table2比對，你會發(fā)現(xiàn)掃描次數(shù)是這樣計算：table1的行數(shù)+塊數(shù)×table2的行數(shù)=100+2×1000=2100库快。而塊數(shù)是掃描次數(shù)的一個重要影響系數(shù)摸袁，而這個系數(shù)是由table1的行數(shù)決定，也就是說驅(qū)動表的行數(shù)決定义屏。
經(jīng)過三種算法的比對靠汁，你是否發(fā)現(xiàn)，掃描次數(shù)由驅(qū)動表的大小決定闽铐，這也就是為什么InnerJoiner會選擇小表作為驅(qū)動表的原因蝶怔。
那么，最后我們總結(jié)下優(yōu)化Join的手段有：
將小表作為驅(qū)動表
無論是否使用索引兄墅，小表作為驅(qū)動表都能夠減少掃描次數(shù)踢星。調(diào)整join_buffer_size大小
MYSQL該參數(shù)的默認(rèn)值大小為512k,調(diào)整該參數(shù)的大小，可以減少分塊嵌套查詢的塊數(shù)隙咸，能夠成倍的減少掃描次數(shù)沐悦。關(guān)聯(lián)時使用索引
關(guān)聯(lián)時使用索引避免掃描和笛卡爾判斷成洗，是提升join性能的絕對殺手锏！
寫在最后
SQL調(diào)優(yōu)雖然說起來理論比較多也相對來說好理解藏否，當(dāng)問題來的時候瓶殃，還可能束手無策。你可以在SQL語句上增加force index或者ignore index來強(qiáng)制或者忽略某個索引秕岛，來驗證是不是MYSQL優(yōu)化器給出了錯誤的優(yōu)化碌燕。
當(dāng)然，可以通過explainSQL語句來觀察語句的執(zhí)行過程继薛、索引的使用情況等修壕，幫助你綜合分析。explain是優(yōu)化的非常重要的技巧遏考，不妨你百度找篇文章來仔細(xì)研究一下慈鸠。
好了，今天這篇文章就分享到這里啦灌具。有什么疑問可以關(guān)注我青团，留言加我好友。
作者介紹
keaizhuzhu，公眾號面試怪圈小編，網(wǎng)站面試怪圈站長躺盛，曾就職于阿里巴巴本地生活坤候，目前就職于京東做后端開發(fā)。
編寫過《Java面試怪圈內(nèi)卷手冊》面試秘籍，全網(wǎng)閱讀量過萬次。
官網(wǎng)：http://www.msgqer.com。旨在分享前端料扰、后端、大數(shù)據(jù)焙蹭、各種中間件技術(shù)的面試資料晒杈，總訪問量數(shù)萬次。
