2 mysql索引優化分析

2.1 效能下降SQL慢 執行時間長 等待時間長

2.1.1 查詢語句寫的爛

2.1.2 索引失效

單值

複合

2.1.3 關聯查詢太多join(設計缺陷或不得已的需求)

2.1.4 伺服器調優及各個引數設定(緩衝\執行緒數等)

2.2 常見通用的join查詢

2.2.1 SQL執行順序

手寫

機讀

總結

2.2.2 Join圖

2.2.3 建表SQL

2.2.4 7種Join

2.3 索引簡介

2.3.1 是什麼

MySQL官方對索引的定義為：索引(Index)是幫助MySQL高校獲取資料的資料結構。 可以得到索引的本質：索引是資料結構

你可以簡單理解為"排好序的快速查詢資料結構"。

詳解(重要)

結論

資料本身之外,資料庫還維護著一個滿足特定查詢演算法的資料結構，這些資料結構以某種方式指向資料， 這樣就可以在這些資料結構的基礎上實現高階查詢演算法,這種資料結構就是索引。

一般來說索引本身也很大，不可能全部儲存在記憶體中，因此索引往往以檔案形式儲存在硬碟上

我們平時所說的索引，如果沒有特別指明，都是指B樹(多路搜尋樹，並不一定是二叉樹)結構組織的索引。其中聚集索引，次要索引，覆蓋索引， 複合索引，字首索引，唯一索引預設都是使用B+樹索引，統稱索引。當然,除了B+樹這種型別的索引之外，還有雜湊索引(hash index)等。

2.3.2 優勢

類似大學圖書館建書目索引，提高資料檢索效率，降低資料庫的IO成本

通過索引列對資料進行排序，降低資料排序成本，降低了CPU的消耗

2.3.3 劣勢

實際上索引也是一張表，該表儲存了主鍵和索引欄位，並指向實體表的記錄,所以索引列也是要佔用空間的

雖然索引大大提高了查詢速度，同時卻會降低更新表的速度,如果對錶INSERT,UPDATE和DELETE。 因為更新表時，MySQL不僅要不存資料，還要儲存一下索引檔案每次更新新增了索引列的欄位， 都會調整因為更新所帶來的鍵值變化後的索引資訊

索引只是提高效率的一個因素，如果你的MySQL有大資料量的表，就需要花時間研究建立優秀的索引，或優化查詢語句

2.3.4 mysql索引分類

單值索引

即一個索引只包含單個列，一個表可以有多個單列索引

建議一張表索引不要超過5個

優先考慮複合索引

唯一索引

索引列的值必須唯一，但允許有空值

複合索引

即一個索引包含多個列

基本語法

建立

CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));

如果是CHAR,VARCHAR型別，length可以小於欄位實際長度； 如果是BLOB和TEXT型別，必須指定length。

ALTER mytable ADD [UNIQUE] INDEX [indexName] ON(columnname(length));

刪除

DROP INDEX [indexName] ON mytable;

檢視

SHOW INDEX FROM table_name\G

使用ALTER命令

2.3.5 mysql索引結構

BTree索引

Btree索引(或Balanced Tree)，是一種很普遍的資料庫索引結構，oracle預設的索引型別（本文也主要依據oracle來講）。其特點是定位高效、利用率高、自我平衡，特別適用於高基數字段，定位單條或小範圍資料非常高效。理論上，使用Btree在億條資料與100條資料中定位記錄的花銷相同。

資料結構利用率高、定位高效

Btree索引的資料結構如下：

結構看起來Btree索引與Binary Tree相似，但在細節上有所不同，上圖中用不同顏色的標示出了Btree索引的幾個主要特點：

樹形結構：由根節(root)、分支(branches)、葉(leaves)三級節點組成，其中分支節點可以有多層。

多分支結構：與binary tree不相同的是，btree索引中單root/branch可以有多個子節點(超過2個)。

雙向連結串列：整個葉子節點部分是一個雙向連結串列(後面會描述這個設計的作用)

單個資料塊中包括多條索引記錄

這裡先把幾個特點羅列出來，後面會說到各自的作用。

結構上Btree與Binary Tree的區別，在於binary中每節點代表一個數值，而balanced中root和Btree節點中記錄了多條”值範圍”條目(如:[60-70][70-80])，這些”值範圍”條目分別指向在其範圍內的葉子節點。既root與branch可以有多個分支，而不一定是兩個，對資料塊的利用率更高。

在Leaf節點中，同樣也是存放了多條索引記錄，這些記錄就是具體的索引列值，和與其對應的rowid。另外，在葉節點層上，所有的節點在組成了一個雙向連結串列。

瞭解基本結構後，下圖展示定位數值82的過程：

演算如下：

讀取root節點，判斷82大於在0-120之間，走左邊分支。

讀取左邊branch節點，判斷82大於80且小於等於120，走右邊分支。

讀取右邊leaf節點，在該節點中找到資料82及對應的rowid

使用rowid去物理表中讀取記錄資料塊(如果是count或者只select rowid，則最後一次讀取不需要)

在整個索引定位過程中，資料塊的讀取只有3次。既三次I/O後定位到rowid。

而由於Btree索引對結構的利用率很高，定位高效。當1千萬條資料時，Btree索引也是三層結構(依稀記得億級資料才是3層與4層的分水嶺)。定位記錄仍只需要三次I/O，這便是開頭所說的，100條資料和1千萬條資料的定位，在btree索引中的花銷是一樣的。

平衡擴張

除了利用率高、定位高效外，Btree的另一個特點是能夠永遠保持平衡，這與它的擴張方式有關。(unbalanced和hotspot是兩類問題，之前我一直混在一起)，先描述下Btree索引的擴張方式：

新建一個索引，索引上只會有一個leaf節點，取名為Node A，不斷的向這個leaf節點中插入資料後，直到這個節點滿，這個過程如下圖（綠色表示新建/空閒狀態，紅色表示節點沒有空餘空間）：

​

當Node A滿之後，我們再向表中插入一條記錄，此時索引就需要做拆分處理：會新分配兩個資料塊NodeB & C，如果新插入的值，大於當前最大值，則將Node A中的值全部插入Node B中，將新插入的值放到Node C中；否則按照5-5比例，將已有資料分別插入到NodeB與C中。

無論採用哪種分割方式，之前的leaf節點A，將變成一個root節點，儲存兩個範圍條目，指向B與C，結構如下圖（按第一種拆分形式）：

​

當Node C滿之後，此時 Node A仍有空餘空間存放條目，所以不需要再拆分，而只是新分配一個資料塊Node D，將在Node A中建立指定到Node D的條目：

如果當根節點Node A也滿了，則需要進一步拆分：新建Node E&F&G，將Node A中範圍條目拆分到E&F兩個節點中，並建立E&F到BCD節點的關聯，向Node G插入索引值。此時E&F為branch節點，G為leaf節點，A為Root節點：

在整個擴張過程中，Btree自身總能保持平衡，Leaf節點的深度能一直保持一致。

實際應用中的一些問題

前面說完了Btree索引的結構與擴張邏輯，接下來講一些Btree索引在應用中的一些問題：

單一方向擴充套件引起的索引競爭(Index Contention)

若索引列使用sequence或者timestamp這類只增不減的資料型別。這種情況下Btree索引的增長方向總是不變的，不斷的向右邊擴充套件，因為新插入的值永遠是最大的。

當一個最大值插入到leaf block中後，leaf block要向上傳播，通知上層節點更新所對應的“值範圍”條目中的最大值，因此所有靠右邊的block(從leaf 到branch甚至root)都需要做更新操作，並且可能因為塊寫滿後執行塊拆分。

如果併發插入多個最大值，則最右邊索引資料塊的的更新與拆分都會存在爭搶，影響效率。在AWR報告中可以通過檢測enq: TX – index contention事件的時間來評估爭搶的影響。解決此類問題可以使用Reverse Index解決，不過會帶來新的問題。

Index Browning 索引枯萎(不知道該怎麼翻譯這個名詞，就是指leaves節點”死”了，樹枯萎了)

其實oracle針對這個問題有優化機制，但優化的不徹底，所以還是要拿出來的說。

我們知道當表中的資料刪除後，索引上對應的索引值是不會刪除的，特別是在一性次刪除大批量資料後，會造成大量的dead leaf掛到索引樹上。考慮以下示例，如果表100以上的資料會部被刪除了，但這些記錄仍在索引中存在，此時若對該列取max()：

通過與之前相同演算，找到了索引樹上最大的資料塊，按照記錄最大的值應該在這裡，但發現這資料塊裡的資料已經被清空了，與是利用Btree索引的另一個特點：leaves節點是一個雙向列表，若資料沒有找到就去臨近的一個資料塊中看看，在這個資料塊中發現了最大值99。

在計算最大值的過程中，這次的定位多載入了一個資料塊，再極端的情況下，大批量的資料被刪除，就會造成大量訪問這些dead leaves。

針對這個問題的一般解決辦法是重建索引，但記住! 重建索引並不是最優方案，詳細原因可以看看這。使用coalesce語句來整理這些dead leaves到freelist中，就可以避免這些問題。理論上oracle中這步操作是可以自動完成的，但在實際中一次性大量刪除資料後，oracle在短時間內是反應不過來的。

Hash索引

瞭解

full-text全文索引

瞭解

R-Tree索引

瞭解

2.3.6 哪些情況需要建立索引

1.主鍵自動建立唯一索引

2.頻繁作為查詢的條件的欄位應該建立索引

3.查詢中與其他表關聯的欄位，外來鍵關係建立索引

4.頻繁更新的欄位不適合建立索引

因為每次更新不單單是更新了記錄還會更新索引，加重IO負擔

5.Where條件裡用不到的欄位不建立索引

6.單間/組合索引的選擇問題，who？（在高併發下傾向建立組合索引）

7.查詢中排序的欄位，排序欄位若通過索引去訪問將大大提高排序的速度

8.查詢中統計或者分組欄位

2.3.7 哪些情況不要建立索引

1.表記錄太少

2.經常增刪改的表

3.資料重複且分佈平均的表欄位，因此應該只為經常查詢和經常排序的資料列建立索引。 注意，如果某個資料列包含許多重複的內容，為它建立索引就沒有太大的實際效果。

2.4 效能分析

2.4.1 MySQL Query Optimizer

2.4.2 MySQL常見瓶頸

CPU:CPU在飽和的時候一般發生在資料裝入在記憶體或從磁碟上讀取資料時候

IO:磁碟I/O瓶頸發生在裝入資料遠大於記憶體容量時

伺服器硬體的效能瓶頸：top,free,iostat和vmstat來檢視系統的效能狀態

2.4.3 Explain

是什麼（檢視執行計劃）

使用EXPLAIN關鍵字可以模擬優化器執行SQL語句，從而知道MySQL是 如何處理你的SQL語句的。分析你的查詢語句或是結構的效能瓶頸

官網介紹

能幹嘛

表的讀取順序

資料讀取操作的操作型別

哪些索引可以使用

哪些索引被實際使用

表之間的引用

每張表有多少行被優化器查詢

怎麼玩

Explain+SQL語句

執行計劃包含的資訊

各個欄位解釋

id

select查詢的序列號，包含一組數字，表示查詢中執行select子句或操作表的順序

三種情況

id相同，執行順序由上至下

id不同，如果是子查詢，id的序號會遞增，id值越大優先順序越高，越先被執行

id相同不同，同時存在

select_type

有哪些

查詢的型別，主要用於區別 普通查詢、聯合查詢、子查詢等的複雜查詢

1.SIMPLE

簡單的select查詢，查詢中不包含子查詢或者UNION

2.PRIMARY

查詢中若包含任何複雜的子部分，最外層查詢則被標記為

3.SUBQUERY

在SELECT或者WHERE列表中包含了子查詢

4.DERIVED

在FROM列表中包含的子查詢被標記為DERIVED（衍生） MySQL會遞迴執行這些子查詢，把結果放在臨時表裡。

5.UNION

若第二個SELECT出現在UNION之後，則被標記為UNION; 若UNION包含在FROM子句的子查詢中，外層SELECT將被標記為：DERIVED

6.UNION RESULT

從UNION表獲取結果的SELECT

table

顯示這一行的資料是關於哪張表的

type

Subtopic

訪問型別排列

顯示查詢使用了何種型別 從最好到最差依次是： system>const>eq_ref>ref>range>index>ALL

system

表只有一行記錄（等於系統表），這是const型別的特例，平時不會出現，這個也可以忽略不計

const

表示通過索引一次就找到了，const用於比較primary key或者unique索引。因為只匹配一行資料，所以很快。如將主鍵至於where列表中，MySQL就能將該查詢轉換為一個常量

eq_ref

唯一性索引，對於每個索引鍵，表中只有一條記錄與之匹配，常見於主鍵或唯一索引掃描

ref

非唯一索引掃描，返回匹配某個單獨值的所有行。 本質上也是一種索引訪問，它返回所有匹配某個單獨值的行，然而， 它可能會找到多個符合條件的行，所以他應該屬於查詢和掃描的混合體

range

只檢索給定範圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引 一般就是在你的where語句中出現了between、<、>、in等的查詢 這種範圍掃描索引掃描比全表掃描要好，因為他只需要開始索引的某一點，而結束語另一點，不用掃描全部索引

index

Full Index Scan,index與ALL區別為index型別只遍歷索引樹。這通常比ALL快，因為索引檔案通常比資料檔案小。 （也就是說雖然all和index都是讀全表，但index是從索引中讀取的，而all是從硬碟中讀的）

all

FullTable Scan,將遍歷全表以找到匹配的行

備註：

一般來說，得保證查詢只是達到range級別，最好達到ref

possible_keys

顯示可能應用在這張表中的索引,一個或多個。 查詢涉及的欄位上若存在索引，則該索引將被列出，但不一定被查詢實際使用

key

實際使用的索引。如果為null則沒有使用索引

查詢中若使用了覆蓋索引，則索引和查詢的select欄位重疊

參閱: 3.USING index

key_len

表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度。在不損失精確性的情況下，長度越短越好

key_len顯示的值為索引最大可能長度，並非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的

ref

顯示索引那一列被使用了，如果可能的話，是一個常數。那些列或常量被用於查詢索引列上的值

rows

根據表統計資訊及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數

越少越好

Extra

包含不適合在其他列中顯示但十分重要的額外資訊

1.Using filesort

說明mysql會對資料使用一個外部的索引排序，而不是按照表內的索引順序進行讀取。 MySQL中無法利用索引完成排序操作成為“檔案排序”

2.Using temporary

使用了臨時表儲存中間結果，MySQL在對查詢結果排序時使用臨時表。常見於排序order by 和分組查詢 group by

3.USING index

表示相應的select操作中使用了覆蓋索引（Coveing Index）,避免訪問了表的資料行，效率不錯！ 如果同時出現using where，表明索引被用來執行索引鍵值的查詢； 如果沒有同時出現using where，表面索引用來讀取資料而非執行查詢動作。

覆蓋索引（Covering Index）

4.Using where

表面使用了where過濾

5.using join buffer

使用了連線快取

6.impossible where

where子句的值總是false，不能用來獲取任何元組

7.select tables optimized away

在沒有GROUPBY子句的情況下，基於索引優化MIN/MAX操作或者 對於MyISAM儲存引擎優化COUNT(*)操作，不必等到執行階段再進行計算， 查詢執行計劃生成的階段即完成優化。

8.distinct

優化distinct，在找到第一匹配的元組後即停止找同樣值的工作

熱身Case

2.5 索引優化

2.5.1 索引分析

單表

建表SQL

案例

兩表

建表SQL

案例

三表

建表SQL

案例

2.5.2 索引失效（應該避免）

建表SQL

案例（索引失效）

1.全值匹配我最愛

2.最佳左字首法則

如果索引了多例，要遵守最左字首法則。指的是查詢從索引的最左前列開始並且不跳過索引中的列。

3.不在索引列上做任何操作（計算、函式、（自動or手動）型別轉換），會導致索引失效而轉向全表掃描

4.儲存引擎不能使用索引中範圍條件右邊的列

5.儘量使用覆蓋索引（只訪問索引的查詢（索引列和查詢列一致）），減少select*

6.mysql在使用不等於（！=或者<>）的時候無法使用索引會導致全表掃描

7.is null,is not null 也無法使用索引

8.like以萬用字元開頭（'$abc...'）mysql索引失效會變成全表掃描操作

問題：解決like'%字串%'索引不被使用的方法？？

1、可以使用主鍵索引

2、使用覆蓋索引，查詢欄位必須是建立覆蓋索引欄位

3、當覆蓋索引指向的欄位是varchar(380)及380以上的欄位時，覆蓋索引會失效！

9.字串不加單引號索引失效

10.少用or,用它連線時會索引失效

11.小總結

like KK%相當於=常量 %KK和%KK% 相當於範圍

定值、範圍還是排序，一般order by是給個範圍

group by 基本上都需要進行排序，會有臨時表產生

2.5.3 一般性建議

對於單鍵索引，儘量選擇針對當前query過濾性更好的索引

在選擇組合索引的時候，當前Query中過濾性最好的欄位在索引欄位順序中，位置越靠前越好。

在選擇組合索引的時候，儘量選擇可以能包含當前query中的where子句中更多欄位的索引

儘可能通過分析統計資訊和調整query的寫法來達到選擇合適索引的目的