深入淺出Mysql索引優(yōu)化專題分享|面試怪圈

文章綱要

該文章結(jié)合18張手繪圖例，21個SQL經(jīng)典案例、近10000字，將Mysql索引優(yōu)化經(jīng)驗予以總結(jié)，你可以根據(jù)綱要來決定是否繼續(xù)閱讀，完成這篇文章大概需要25-30分鐘，相信你的堅持是不負時光的：

  • 前言
  • 開篇小例子
    單索引性能最優(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)來舉例吧，看下圖：

image

圖1：student表及索引說明

學(xué)生表，包含：id、number（學(xué)號）、name（姓名）、sex（性別）、age（年齡），并且id為主鍵，其他字段分別有一個單獨索引。

類似這樣的索引設(shè)計，在我的工作中經(jīng)常遇到，當然也隨著查詢邏輯的復(fù)雜性提升，這種單字段索引也會變得越來越多。之所以出現(xiàn)這樣的情況，是對索引的理解和用法并不深入導(dǎo)致的，在茫然的時候選擇了：加單索引來解決性能問題的方法。

我猜大家可能會有這樣的兩個疑問：

  • 創(chuàng)建單字段索引性能最優(yōu)？如果不是，那該如何加索引呢?
  • 是不是索引越多越好？因為越多越容易命中？

單索引性能最優(yōu)？

首先來看單索引性能是否最優(yōu)？為了說明這個問題，我還是先準備一點數(shù)據(jù)幫助理解：

image

圖2：student表數(shù)據(jù)準備
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這個索引,當然選擇idx_sex這個索引，跟我們下面闡述的原理是一樣的。

我手繪了idx_age和主鍵索引的簡圖，輔助理解：

image

圖3：主鍵索引簡圖

image

圖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.當在age索引樹上找到age=10的記錄時，不滿足條件，循環(huán)結(jié)束。

你會發(fā)現(xiàn)，整個查找執(zhí)行的過程中，查詢主鍵索引樹3次，查詢主鍵索引的目的有兩個：一是為了查詢sex是否滿足條件，二是為了返回需要的全字段。

單索引執(zhí)行是這樣，那我們繼續(xù)看一下，如果我們基于SQL語句的兩個查詢字段age、sex建立聯(lián)合索引，執(zhí)行過程是怎樣的呢？是否會提升性能呢？

同樣，我手繪了一張聯(lián)合索引的簡圖如下：

image

圖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.索引更新需要更多維護成本
    我們一直在討論索引的查詢，可我們不能忽略的一點是，當Insert、Update、Delete等操作也帶來索引的更新和維護，因此，索引的數(shù)量也會帶來更多的維護成本，你說呢？

因此，綜合所有該表的SQL的查詢條件，合理規(guī)劃索引的個數(shù)，避免冗余索引的出現(xiàn)，有助于降低維護成本。比如下面的索引，可以將左圖兩個索引優(yōu)化為右圖一個索引：

image

圖6：冗余索引去除

至于為什么可以這樣優(yōu)化，通過閱讀下文，你會得到答案。

常用術(shù)語

圍繞著索引有很多術(shù)語，也許你經(jīng)常聽到或者在面試中被問到，但是卻沒有理解是什么意思。但是為了提升自身逼格和自我的虛榮心滿足，那么，我們一起回顧下吧：

  • 主鍵索引

以下是student表的主鍵索引，它也叫做一級索引。有的時候也有人稱它為聚簇索引，聚簇索引是因葉子節(jié)點的id和行數(shù)據(jù)聚簇在一起而得名，如下圖：

image

圖7：主鍵索引

  • 輔助索引

而與主鍵索引的對應(yīng)的就是輔助索引，它也叫做二級索引。由于葉子節(jié)點上無行數(shù)據(jù)，只有一個id，因此它是非聚簇索引。

image

圖8：輔助索引簡圖

  • 覆蓋索引

覆蓋索引并不是一種索引類型，而是一種索引查詢的形式和行為。覆蓋索引往往應(yīng)用于聯(lián)合索引。下圖就是一個由age、sex組成的聯(lián)合索引：

image

圖9：聯(lián)合索引簡圖

當查詢條件運用了索引，并且SELECT的字段也覆蓋在該索引樹上，也就是一顆索引樹既滿足了檢索也滿足了結(jié)果，無需為了拿到需要的SELECT字段而去回表的一種方式。

細心的你會發(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í)行的呢？

image

圖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的記錄，返回。
image

圖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 * 操作，如果在二級索引進行條件篩選，但為了獲取 全部字段，需要回表操作，前面提過，回表越多，性能較差。因此，按需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è)計才是最合理的？才能夠更長遠呢？這里我給出兩點參考建議：

  • 考慮索引的復(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個英文字母，顯然達不到索引設(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) = '小';
  • 索引字段隱式類型強轉(zhuǎn)
    ps:學(xué)號為varchar類型。
SELECT * FROM student WHERE number = 2021007;
  • 索引字段運算符操作
SELECT * FROM student WHERE age+1 = 7;
  • 負向查詢
    包含：!=、<>、not in、not like、!>、!<等。
SELECT * FROM student WHERE age != 7;
  • 隱字符字符編碼
    a表采用uft8編碼，b表采用utf8mb4編碼，當使用字符串字段進行join操作。
SELECT * FROM t1 a join t2 b on a.name = b.name;

大字段影響檢索性能

在設(shè)計表時，我們會使用TEXT或者BLOB等類型來存儲大文本或者二進制，而這些大字段對查詢性能的影響是比較大的。那是為什么呢？

回表查詢，需要將整行數(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)鍵字。這是因為默認情況下，MySQL對所有GROUP BY col1，col2….的字段進行排序，類似在查詢中指定 ORDER BY col1，col2…一樣。因此，GROUP BY是默認排序的。

因此，我們可以讓GROUP BY后的字段利用索引排序，或者你的業(yè)務(wù)場景不需要排序的情況下，可以使用以下語句禁用默認排序：

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ù)需要進行回表，導(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影響

image

圖15：索引嵌套查詢連接

  • Simple Nested-Loop Join（簡單嵌套查詢連接）

依然是這個SQL，如果沒有idx_a這個索引，執(zhí)行過程是什么樣的呢？

  1. 從表t1中讀入一行數(shù)據(jù) R1；

  2. 從數(shù)據(jù)行R1中，取出a字段到表t2里去查找；

  3. 全表掃描取出表t2中滿足條件的行，跟R組成一行， 作為結(jié)果集的一部分

  4. 重復(fù)執(zhí)行步驟1到3，直到表t1的末尾循環(huán)結(jié)束。

失去了索引，形成了笛卡爾掃描，掃描次數(shù)為100100次，100次的t1表的掃描和與t2表全表掃描比對，因此，這個性能太差了，MYSQL并未采用此種算法。

image

圖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ù)。

image

圖17：采用JoinerBuffer嵌套查詢連接

然后，似乎我們遺漏了一個概念分塊，這里并未提及它。你試想下table1數(shù)據(jù)量比較大，會把所有數(shù)據(jù)裝載到Joiner Buffer中嗎？當然會采用分而治之的方法。這種方法就是分塊。

按照分塊的方式，我們重新看下，假設(shè)table1的50條數(shù)據(jù)裝滿Joiner Buffer,再次看下執(zhí)行流程吧：

image

圖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ù)的默認值大小為512k,調(diào)整該參數(shù)的大小，可以減少分塊嵌套查詢的塊數(shù)，能夠成倍的減少掃描次數(shù)。

  • 關(guān)聯(lián)時使用索引
    關(guān)聯(lián)時使用索引避免掃描和笛卡爾判斷，是提升join性能的絕對殺手锏！

寫在最后

SQL調(diào)優(yōu)雖然說起來理論比較多也相對來說好理解，當問題來的時候，還可能束手無策。你可以在SQL語句上增加force index或者ignore index來強制或者忽略某個索引，來驗證是不是MYSQL優(yōu)化器給出了錯誤的優(yōu)化。

當然，可以通過explainSQL語句來觀察語句的執(zhí)行過程、索引的使用情況等，幫助你綜合分析。explain是優(yōu)化的非常重要的技巧，不妨你百度找篇文章來仔細研究一下。

好了，今天這篇文章就分享到這里啦。有什么疑問可以關(guān)注我，留言加我好友。

作者介紹

keaizhuzhu，公眾號面試怪圈小編，網(wǎng)站面試怪圈站長，曾就職于阿里巴巴本地生活，目前就職于京東做后端開發(fā)。

編寫過《Java面試怪圈內(nèi)卷手冊》面試秘籍，全網(wǎng)閱讀量過萬次。

官網(wǎng)：http://www.msgqer.com。旨在分享前端、后端、大數(shù)據(jù)、各種中間件技術(shù)的面試資料，總訪問量數(shù)萬次。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容