MySQL索引【詳解】

文章目錄

• ​​第一篇 什麼是索引？​​

• ​​1、來看一個問題​​

• ​​方案1​​
• ​​方案2​​
• ​​方案3​​
• ​​方案4​​

• ​​2、索引是什麼？​​

• ​​第二篇 MySQL索引原理詳解​​

• ​​1、背景​​
• ​​2、預備知識​​

• ​​什麼是索引？​​
• ​​磁盤中資料的存取​​
• ​​mysql中的頁​​
• ​​資料檢索過程​​

• ​​3、我們迫切的需求是什麼？（資料結構和算法）​​

• ​​3.1、循環周遊查找​​
• ​​3.2、二分法查找​​
• ​​3.3、有序數組​​
• ​​3.4、連結清單​​

• ​​單連結清單​​
• ​​雙向連結清單​​

• ​​3.5、二叉查找樹​​
• ​​3.6、平衡二叉樹（AVL樹）​​
• ​​3.7、B-樹​​
• ​​3.8、B+樹​​
• ​​3.9、Mysql的存儲引擎和索引​​
• ​​3.10、頁結構​​

• ​​資料檢索過程​​
• ​​對page的結構總結一下​​

• ​​第三篇 MySQL索引管理​​

• ​​1、索引分類​​

• ​​1.1、聚集索引​​
• ​​1.2、非聚集索引（輔助索引）​​

• ​​mysql中非聚集索引分為​​

• ​​1.3、資料檢索的過程​​

• ​​2、索引管理​​

• ​​2.1、建立索引​​
• ​​2.2、删除索引​​
• ​​2.3、檢視索引​​
• ​​2.4、索引修改​​

• ​​3、示例​​

• ​​準備200萬資料​​
• ​​無索引我們體驗一下查詢速度​​
• ​​建立索引​​
• ​​建立索引并指定長度​​
• ​​檢視表中的索引​​
• ​​删除索引​​

• ​​第四篇 如何正确使用索引​​

• ​​1、通常說的這個查詢走索引了是什麼意思？​​
• ​​2、b+樹中資料檢索過程​​

• ​​2.1、唯一記錄檢索​​
• ​​2.2、查詢某個值的所有記錄​​
• ​​2.3、範圍查找​​
• ​​2.4、模糊比對​​

• ​​查詢包含f的記錄​​

• ​​2.5、最左比對原則​​

• ​​查詢a=1的記錄​​
• ​​查詢a=1 and b=5的記錄​​
• ​​查詢b=1的記錄​​
• ​​按照c的值查詢​​
• ​​按照b和c一起查​​
• ​​按照[a,c]兩個字段查詢​​
• ​​查詢a=1 and b>=0 and c=1的記錄​​

• ​​3、索引區分度​​
• ​​4、正确使用索引​​

• ​​4.1、準備400萬測試資料​​
• ​​4.2、無索引檢索效果​​
• ​​4.3、主鍵檢索​​
• ​​4.4、between and範圍檢索​​
• ​​4.5、in的檢索​​
• ​​4.6、多個索引時查詢如何走？​​
• ​​4.7、模糊查詢​​
• ​​4.8、回表​​
• ​​4.9、索引覆寫​​
• ​​4.10、索引下推​​
• ​​4.11、數字使字元串類索引失效​​
• ​​4.12、函數使索引無效​​
• ​​4.13、運算符使索引無效​​
• ​​4.14、使用索引優化排序​​

• ​​5、總結一下使用索引的一些建議​​

第一篇 什麼是索引？

1、來看一個問題

路人在搞計算機之前，是負責小區建設規劃的，上級上司安排路人負責一個萬人小區建設規劃，并提了一個要求：可以快速通過戶主姓名找到戶主的房子；讓路人出個好的解決方案。

方案1

剛開始路人沒什麼經驗，實在想不到什麼好辦法。

路人告訴上司：你可以去敲每戶的門，然後開門之後再去詢問房主姓名，是否和需要找的人姓名一緻。

上司一聽郁悶了：我敲你的頭，1萬戶，我一個個找，找到什麼時候了？你明天不用來上班了。

這裡面涉及到的時間有：走到每戶的門口耗時、敲門等待開門耗時、詢問戶主擷取戶主姓名耗時、将戶主姓名和需要查找的姓名對比是否一緻耗時。加入要找的人剛好在最後一戶，上司豈不是要瘋掉了，需要重複1萬次上面的操作。

上面是最原始，最耗時的做法，可能要找的人根本不在這個小區，白費力的找了1萬次，豈不是要瘋掉。

方案2

路人靈機一動，想到了一個方案：

1. 給所有的戶主制定一個編号，從1-10000，戶主将戶号貼在自家的門口
2. 路人自己制作了一個戶主和戶号對應的表格，我們叫做： 戶主目錄表 ，共1萬條記錄，如下：

戶主姓名	房屋編号
劉德華	00001
張學友	00002
路人	00888
路人甲java	10000

此時上司要查找 路人甲Java 時，過程如下：

1. 按照姓名在 戶主目錄表 查找 路人甲Java ，找到對應的編号： 10000
2. 然後從第一戶房子開始找，檢視其門口戶号是否是10000，直到找到為止

路人告訴上司，這個方案比方案1有以下好處：

1. 如果要找的人不在這個小區，通過 戶主目錄表 就确定，不需要第二步了
2. 步驟2中不需要再去敲每戶的門以及詢問戶主的姓名了，隻需對比一下門口的戶号就可以了，比方

案1省了不少時間。

上司笑着說，不錯不錯，有進步，不過我找 路人甲Java 還是需要挨家挨戶看門牌号1萬次啊！。。。。。你再去想想吧，看看是否還有更好的辦法來加快查找速度。

路人下去了苦思冥想，想出了方案3。

方案3

方案2中第2步最壞的情況還是需要找1萬次。

路人去上海走了一圈，看了那邊小區搞的不錯，很多小區都是搞成一棟一棟的，每棟樓裡面有100戶，路人也決定這麼搞。

路人告訴上司：

1. 将1萬戶劃分為100棟樓，每棟樓有25層，每層有4戶人家，總共1萬戶
2. 給每棟樓一個編号，範圍是[001,100]，将棟号貼在每棟樓最顯眼的位置
3. 給每棟樓中的每層一個編号，編号範圍是[01,25]，将層号貼在每層樓最顯眼的位置
4. 戶号變為：棟号-樓層-層中編号，如 路人甲Java 戶号是：100-20-04，貼在每戶門口

戶主目錄表 還是有1萬條記錄，如下：

戶主姓名	房屋編号
劉德華	001-08-04
張學友	022-18-01
路人	088-25-04
路人甲java	100-25-04

此時上司要查找 路人甲Java 時，過程如下：

1. 按照姓名在 戶主目錄表 查找 路人甲Java ，找到對應的編号是 100-25-04 ，将編号分解，得到：棟号（100）、樓層（25）、樓号（04）
2. 從第一棟開始找，看其棟号是否是100，直到找到編号為100為止，這個過程需要找100次，然後到了第100棟樓下
3. 從100棟的第一層開始向上走，走到每層看其編号是否為25，直到走到第25層，這個過程需要比對25次
4. 在第25層依次看看戶号是否為 100-25-04 ，比對了4次，找到了 路人甲Java

此方案分析：

1. 查找 戶主目錄表 1萬次，不過這個是在表格中，不用動身走路去找，隻需要動動眼睛對比一下數字，速度還是比較快的
2. 将方案2中的第2步優化為上面的 2/3/4 步驟，上面最壞需要比對129次（棟100+層25+樓号4次），相對于方案2的1萬次好多了

上司拍拍路人的肩膀：小夥子，去過上海的人确實不一樣啊，這次方案不錯，不過第一步還是需要很多次，能否有更好的方案呢？

路人下去了又想了好幾天，突然想到了我們常用的字典，可以按照字典的方式對方案3中第一步做優化，然後提出了方案4。

方案4

姓首字母：A
姓名	戶号
阿三	010-16-01
阿郎	017-11-04
啊啊	008-08-02

姓首字母：L
姓名	戶号
劉德華	011-16-01
路人	057-11-04
路人甲Java	048-08-02

現在查找戶号步驟如下：

1. 通過姓名擷取姓對應的首字母
2. 在26個表格中找到對應姓的表格，如 路人甲Java ，對應 L表
3. 在L表中循環周遊，找到 路人甲Java 的戶号
4. 根據戶号按照方案3中的(2/3/4)步驟找對應的戶主

理想情況：

1萬戶主的姓氏配置設定比較均衡，那麼每個姓氏下面配置設定385戶（10000/26） ，那麼找到某個戶主，最多需要:26次+385次 = 410次，相對于1萬次少了很多。

最壞的情況：

1萬個戶主的姓氏都是一樣的，導緻這1萬個戶主資訊都位于同一個姓氏戶主表，此時查詢又變為了1萬多次。不過出現姓氏一樣的情況比較低。

如果擔心姓氏不足以均衡劃分戶主資訊，那麼也可以通過戶主姓名的筆畫數來劃分，或者其他方法，主要是将使用者資訊劃分為不同的區，可以快速過濾一些不相關的戶主。

上面幾個方案為了快速檢索到戶主，用到了一些資料結構，通過這些資料結構對戶主的資訊進行組織，進而可以快速過濾掉一些不相關的戶主，減少查找次數，快速定位到戶主的房子。

2、索引是什麼？

通過上面的示例，我們可以概況一下索引的定義：索引是依靠某些資料結構和算法來組織資料，最終引導使用者快速檢索出所需要的資料。

索引有2個特點：

1. 通過資料結構和算法來對原始的資料進行一些有效的組織
2. 通過這些有效的組織，可以引導使用者對原始資料進行快速檢索

第二篇 MySQL索引原理詳解

1、背景

使用mysql最多的就是查詢，我們迫切的希望mysql能查詢的更快一些，我們經常用到的查詢有：

1. 按照id查詢唯一一條記錄
2. 按照某些個字段查詢對應的記錄
3. 查找某個範圍的所有記錄（between and）
4. 對查詢出來的結果排序

mysql的索引的目的是使上面的各種查詢能夠更快。

2、預備知識

什麼是索引？

上一篇中有詳細的介紹，可以過去看一下：什麼是索引？

索引的本質：通過不斷地縮小想要擷取資料的範圍來篩選出最終想要的結果，同時把随機的事件變成順

序的事件，也就是說，有了這種索引機制，我們可以總是用同一種查找方式來鎖定資料。

磁盤中資料的存取

以機械硬碟來說，先了解幾個概念。

扇區：磁盤存儲的最小機關，扇區一般大小為512Byte。

磁盤塊：檔案系統與磁盤互動的的最小機關（計算機系統讀寫磁盤的最小機關），一個磁盤塊由連續幾個（2^n）扇區組成，塊一般大小一般為4KB。

磁盤讀取資料：磁盤讀取資料靠的是機械運動，每次讀取資料花費的時間可以分為尋道時間、旋轉延遲、傳輸時間三個部分，尋道時間指的是磁臂移動到指定磁道所需要的時間，主流磁盤一般在5ms以下；旋轉延遲就是我們經常聽說的磁盤轉速，比如一個磁盤7200轉，表示每分鐘能轉7200次，也就是說1秒鐘能轉120次，旋轉延遲就是1/120/2 = 4.17ms；傳輸時間指的是從磁盤讀出或将資料寫入磁盤的時間，一般在零點幾毫秒，相對于前兩個時間可以忽略不計。那麼通路一次磁盤的時間，即一次磁盤IO的時間約等于5+4.17 = 9ms左右，聽起來還挺不錯的，但要知道一台500 -MIPS的機器每秒可以執行5億條指令，因為指令依靠的是電的性質，換句話說執行一次IO的時間可以執行40萬條指令，資料庫動辄十萬百萬乃至千萬級資料，每次9毫秒的時間，顯然是個災難。

mysql中的頁

mysql中和磁盤互動的最小機關稱為頁，頁是mysql内部定義的一種資料結構，預設為16kb，相當于4個磁盤塊，也就是說mysql每次從磁盤中讀取一次資料是16KB，要麼不讀取，要讀取就是16KB，此值可以修改的。

資料檢索過程

我們對資料存儲方式不做任何優化，直接将資料庫中表的記錄存儲在磁盤中，假如某個表隻有一個字段，為int類型，int占用4個byte，每個磁盤塊可以存儲1000條記錄，100萬的記錄需要1000個磁盤塊，如果我們需要從這100萬記錄中檢索所需要的記錄，需要讀取1000個磁盤塊的資料（需要1000次io），每次io需要9ms，那麼1000次需要9000ms=9s，100條資料随便一個查詢就是9秒，這種情況我們是無法接受的，顯然是不行的。

一個磁盤塊4kb，一條記錄4byte，4kb / 4byte = 4 * 1024 byte / 4 byte = 1024 條 （1K）

3、我們迫切的需求是什麼？（資料結構和算法）

我們迫切需要這樣的資料結構和算法：

1. 需要一種資料存儲結構：當從磁盤中檢索資料的時候能，夠減少磁盤的io次數，最好能夠降低到一個穩定的常量值
2. 需要一種檢索算法：當從磁盤中讀取磁盤塊的資料之後，這些塊中可能包含多條記錄，這些記錄被加載到記憶體中，那麼需要一種算法能夠快速從記憶體多條記錄中快速檢索出目标資料

我們來找找，看是否能夠找到這樣的算法和資料結構。我們看一下常見的檢索算法和資料結構。

3.1、循環周遊查找

從一組無序的資料中查找目标資料，常見的方法是周遊查詢，n條資料，時間複雜度為O(n)，最快需要1次，最壞的情況需要n次，查詢效率不穩定。

3.2、二分法查找

二分法查找也稱為折半查找，用于在一個有序數組中快速定義某一個需要查找的資料。

原理是：

先将一組無序的資料排序（升序或者降序）之後放在數組中，此處用升序來舉例說明：用數組中間位置的資料A和需要查找的資料F對比，如果A=F，則結束查找；如果A<F，則将查找的範圍縮小至數組中A資料右邊的部分；如果A>F，則将查找範圍縮小至數組中A資料左邊的部分，繼續按照上面的方法直到找到F為止。

示例：

從下列有序數字中查找數字9，過程如下

[1,2,3,4,5,6,7,8,9]

第1次查找：[1,2,3,4,5,6,7,8,9]中間位置值為5，9>5，将查找範圍縮小至5右邊的部分：[6、7、8、9]

第2次查找：[6、7、8、9]中間值為8，9>8 ，将範圍縮小至8右邊部分：[9]

第3次查找：在[9]中查找9，找到了。

可以看到查找速度是相當快的，每次查找都會使範圍減半，如果我們采用順序查找，上面資料最快需要1次，最多需要9次，而二分法查找最多隻需要3次，耗時時間也比較穩定。

二分法查找時間複雜度是:O(logN)(N為資料量)，100萬資料查找最多隻需要20次（ =1048576 ）

二分法查找資料的優點：定位資料非常快，前提是：目标數組是有序的。

3.3、有序數組

如果我們将mysql中表的資料以有序數組的方式存儲在磁盤中，那麼我們定位資料步驟是：

1. 取出目标表的所有資料，存放在一個有序數組中
2. 如果目标表的資料量非常大，從磁盤中加載到記憶體中需要的記憶體也非常大

步驟取出所有資料耗費的io次數太多，步驟2耗費的記憶體空間太大，還有新增資料的時候，為了保證數組有序，插入資料會涉及到數組内部資料的移動，也是比較耗時的，顯然用這種方式存儲資料是不可取的。

3.4、連結清單

連結清單相當于在每個節點上增加一些指針，可以和前面或者後面的節點連接配接起來，就像一列火車一樣，每節車廂相當于一個節點，車廂内部可以存儲資料，每個車廂和下一節車廂相連。

連結清單分為單連結清單和雙向連結清單。

單連結清單

每個節點中有持有指向下一個節點的指針，隻能按照一個方向周遊連結清單，結構如下：

//單項連結清單 class Node1{
  private Object data;//存儲資料
  private Node1 nextNode;//指向下一個節點 
}      

雙向連結清單

每個節點中兩個指針，分别指向目前節點的上一個節點和下一個節點，結構如下：

//雙向連結清單 
class Node2{ 
  private Object data;//存儲資料 
  private Node1 prevNode;//指向上一個節點 
  private Node1 nextNode;//指向下一個節點 
}      

連結清單的優點：

1. 可以快速定位到上一個或者下一個節點
2. 可以快速删除資料，隻需改變指針的指向即可，這點比數組好

連結清單的缺點：

1. 無法向數組那樣，通過下标随機通路資料
2. 查找資料需從第一個節點開始周遊，不利于資料的查找，查找時間和無需資料類似，需要全周遊，最差時間是O(N)

3.5、二叉查找樹

二叉樹是每個結點最多有兩個子樹的樹結構，通常子樹被稱作“左子樹”（left subtree）和“右子樹” （right subtree）。二叉樹常被用于實作二叉查找樹和二叉堆。二叉樹有如下特性：

• 每個結點都包含一個元素以及n個子樹，這裡0≤n≤2。 2、左子樹和右子樹是有順序的，次序不能任意颠倒，左子樹的值要小于父結點，右子樹的值要大于父結點。

數組[20,10,5,15,30,25,35]使用二叉查找樹存儲如下：

每個節點上面有兩個指針（left,rigth），可以通過這2個指針快速通路左右子節點，檢索任何一個資料最多隻需要通路3個節點，相當于通路了3次資料，時間為O(logN)，和二分法查找效率一樣，查詢資料還是比較快的。

但是如果我們插入資料是有序的，如[5,10,15,20,30,25,35]，那麼結構就變成下面這樣：

二叉樹退化為了一個連結清單結構，查詢資料最差就變為了O(N)。

二叉樹的優缺點：

1. 查詢資料的效率不穩定，若樹左右比較平衡的時，最差情況為O(logN)，如果插入資料是有序的，退化為了連結清單，查詢時間變成了O(N)
2. 資料量大的情況下，會導緻樹的高度變高，如果每個節點對應磁盤的一個塊來存儲一條資料，需io次數大幅增加，顯然用此結構來存儲資料是不可取的

3.6、平衡二叉樹（AVL樹）

平衡二叉樹是一種特殊的二叉樹，是以他也滿足前面說到的二叉查找樹的兩個特性，同時還有一個特性：

• 它的左右兩個子樹的高度差的絕對值不超過1，并且左右兩個子樹都是一棵平衡二叉樹。

平衡二叉樹相對于二叉樹來說，樹的左右比較平衡，不會出現二叉樹那樣退化成連結清單的情況，不管怎麼插入資料，最終通過一些調整，都能夠保證樹左右高度相差不大于1。

這樣可以讓查詢速度比較穩定，查詢中周遊節點控制在O(logN)範圍内

如果資料都存儲在記憶體中，采用AVL樹來存儲，還是可以的，查詢效率非常高。不過我們的資料是存在磁盤中，用過采用這種結構，每個節點對應一個磁盤塊，資料量大的時候，也會和二叉樹一樣，會導緻樹的高度變高，增加了io次數，顯然用這種結構存儲資料也是不可取的。

3.7、B-樹

B杠樹 ，千萬不要讀作B減樹了，B-樹在是平衡二叉樹上進化來的，前面介紹的幾種樹，每個節點上面隻有一個元素，而B-樹節點中可以放多個元素，主要是為了降低樹的高度。

一棵m階的B-Tree有如下特性【特征描述的有點繞，看不懂的可以跳過，看後面的圖】：

1. 每個節點最多有m個孩子，m稱為b樹的階
2. 除了根節點和葉子節點外，其它每個節點至少有Ceil(m/2)個孩子
3. 若根節點不是葉子節點，則至少有2個孩子
4. 所有葉子節點都在同一層，且不包含其它關鍵字資訊
5. 每個非終端節點包含n個關鍵字（健值）資訊
6. 關鍵字的個數n滿足：ceil(m/2)-1 <= n <= m-1
7. ki(i=1,…n)為關鍵字，且關鍵字升序排序
8. Pi(i=1,…n)為指向子樹根節點的指針。P(i-1)指向的子樹的所有節點關鍵字均小于ki，但都大于k(i-1)

B-Tree結構的資料可以讓系統高效的找到資料所在的磁盤塊。為了描述B-Tree，首先定義一條記錄為一個二進制組[key, data] ，key為記錄的鍵值，對應表中的主鍵值，data為一行記錄中除主鍵外的資料。對于不同的記錄，key值互不相同。

B-Tree中的每個節點根據實際情況可以包含大量的關鍵字資訊和分支，如下圖所示為一個3階的B-Tree：

每個節點占用一個盤塊的磁盤空間，一個節點上有兩個升序排序的關鍵字和三個指向子樹根節點的指針，指針存儲的是子節點所在磁盤塊的位址。兩個鍵将資料劃分成的三個範圍域，對應三個指針指向的子樹的資料的範圍域。以根節點為例，關鍵字為17和35，P1指針指向的子樹的資料範圍為小于17，P2指針指向的子樹的資料範圍為17~35，P3指針指向的子樹的資料範圍為大于35。

模拟查找關鍵字29的過程：

1. 根據根節點找到磁盤塊1，讀入記憶體。【磁盤I/O操作第1次】
2. 比較關鍵字29在區間（17,35），找到磁盤塊1的指針P2
3. 根據P2指針找到磁盤塊3，讀入記憶體。【磁盤I/O操作第2次】
4. 比較關鍵字29在區間（26,30），找到磁盤塊3的指針P2
5. 根據P2指針找到磁盤塊8，讀入記憶體。【磁盤I/O操作第3次】
6. 在磁盤塊8中的關鍵字清單中找到關鍵字29

分析上面過程，發現需要3次磁盤I/O操作，和3次記憶體查找操作，由于記憶體中的關鍵字是一個有序表結構，可以利用二分法快速定位到目标資料，而3次磁盤I/O操作是影響整個B-Tree查找效率的決定因素。B-樹相對于avl樹，通過在節點中增加節點内部資料的個數來減少磁盤的io操作。

上面我們說過mysql是采用頁方式來讀寫資料，每頁是16KB，我們用B-樹來存儲mysql的記錄，每個節點對應mysql中的一頁（16KB），假如每行記錄加上樹節點中的1個指針占160Byte，那麼每個節點可以存儲1000（16KB/160byte）條資料，樹的高度為3的節點大概可以存儲（第一層1000+第二層10002+第三層10003）10億條記錄，是不是非常驚訝，一個高度為3個B-樹大概可以存儲10億條記錄，我們從10億記錄中查找資料隻需要3次io操作可以定位到目标資料所在的頁，而頁内部的資料又是有序的，然後将其加載到記憶體中用二分法查找，是非常快的。

可以看出使用B-樹定位某個值還是很快的(10億資料中3次io操作+記憶體中二分法)，但是也是有缺點的：

• B-不利于範圍查找，比如上圖中我們需要查找[15,36]區間的資料，需要通路7個磁盤塊（1/2/7/3/8/4/9），io次數又上去了，範圍查找也是我們經常用到的，是以]b-樹也不太适合在磁盤中存儲需要檢索的資料。

3.8、B+樹

先看個b+樹結構圖：

b+樹的特征

1. 每個結點至多有m個子女
2. 除根結點外,每個結點至少有[m/2]個子女，根結點至少有兩個子女
3. 有k個子女的結點必有k個關鍵字
4. 父節點中持有通路子節點的指針
5. 父節點的關鍵字在子節點中都存在（如上面的1/20/35在每層都存在），要麼是最小值，要麼是最大值，如果節點中關鍵字是升序的方式，父節點的關鍵字是子節點的最小值
6. 最底層的節點是葉子節點
7. 除葉子節點之外，其他節點不儲存資料，隻儲存關鍵字和指針
8. 葉子節點包含了所有資料的關鍵字以及data，葉子節點之間用連結清單連接配接起來，可以非常友善的支

持範圍查找

b+樹與b-樹的幾點不同：

1. b+樹中一個節點如果有k個關鍵字，最多可以包含k個子節點（k個關鍵字對應k個指針）；而b-樹對應k+1個子節點（多了一個指向子節點的指針）
2. b+樹除葉子節點之外其他節點值存儲關鍵字和指向子節點的指針，而b-樹還存儲了資料，這樣同樣大小情況下，b+樹可以存儲更多的關鍵字
3. b+樹葉子節點中存儲了所有關鍵字及data，并且多個節點用連結清單連接配接，從上圖中看子節點中資料從左向右是有序的，這樣快速可以支撐範圍查找（先定位範圍的最大值和最小值，然後子節點中依靠連結清單周遊範圍資料）

B-Tree和B+Tree該如何選擇？

1. B-Tree因為非葉子結點也儲存具體資料，是以在查找某個關鍵字的時候找到即可傳回。而B+Tree所有的資料都在葉子結點，每次查找都得到葉子結點。是以在同樣高度的B-Tree和B+Tree中，BTree查找某個關鍵字的效率更高
2. 由于B+Tree所有的資料都在葉子結點，并且結點之間有指針連接配接，在找大于某個關鍵字或者小于某個關鍵字的資料的時候，B+Tree隻需要找到該關鍵字然後沿着連結清單周遊就可以了，而B-Tree還需要周遊該關鍵字結點的根結點去搜尋。
3. 由于B-Tree的每個結點（這裡的結點可以了解為一個資料頁）都存儲主鍵+實際資料，而B+Tree非葉子結點隻存儲關鍵字資訊，而每個頁的大小有限是有限的，是以同一頁能存儲的B-Tree的資料會比B+Tree存儲的更少。這樣同樣總量的資料，B-Tree的深度會更大，增大查詢時的磁盤I/O次數，進而影響查詢效率。

3.9、Mysql的存儲引擎和索引

mysql内部索引是由不同的引擎實作的，主要說一下InnoDB和MyISAM這兩種引擎中的索引，這兩種引擎中的索引都是使用b+樹的結構來存儲的。

InnoDB中的索引：

Innodb中有2種索引：主鍵索引（聚集索引）、輔助索引（非聚集索引）。

• 主鍵索引：每個表隻有一個主鍵索引，b+樹結構，葉子節點同時儲存了主鍵的值也資料記錄，其他節點隻存儲主鍵的值。
• 輔助索引：每個表可以有多個，b+樹結構，葉子節點儲存了索引字段的值以及主鍵的值，其他節點隻存儲索引指端的值。

MyISAM引擎中的索引：

B+樹結構，MyISM使用的是非聚簇索引，非聚簇索引的兩棵B+樹看上去沒什麼不同，節點的結構完全一緻隻是存儲的内容不同而已，主鍵索引B+樹的節點存儲了主鍵，輔助鍵索引B+樹存儲了輔助鍵。表資料存儲在獨立的地方，這兩顆B+樹的葉子節點都使用一個位址指向真正的表資料，對于表資料來說，這兩個鍵沒有任何差别。由于索引樹是獨立的，通過輔助鍵檢索無需通路主鍵的索引樹。

如下圖：為了更形象說明這兩種索引的差別，我們假想一個表存儲了4行資料。其中Id作為主索引，Name作為輔助索引，圖中清晰的顯示了聚簇索引和非聚簇索引的差異。

我們看一下上圖中資料檢索過程。

InnoDB資料檢索過程：

如果需要查詢id=14的資料，隻需要在左邊的主鍵索引中檢索就可以了。

如果需要搜尋name='Ellison’的資料，需要2步：

1. 先在輔助索引中檢索到name='Ellison’的資料，擷取id為14
2. 再到主鍵索引中檢索id為14的記錄

輔助索引這個查詢過程在mysql中叫做回表。

MyISAM資料檢索過程：

1. 在索引中找到對應的關鍵字，擷取關鍵字對應的記錄的位址
2. 通過記錄的位址查找到對應的資料記錄

我們用的最多的是innodb存儲引擎，是以此處主要說一下innodb索引的情況，innodb中最好是采用主鍵查詢，這樣隻需要一次索引，如果使用輔助索引檢索，涉及到回表操作，比主鍵查詢要耗時一些。

innodb中輔助索引為什麼不像myisam那樣存儲記錄的位址？

表中的資料發生變更的時候，會影響其他記錄位址的變化，如果輔助索引中記錄資料的位址，此時會受影響，而主鍵的值一般是很少更新的，當頁中的記錄發生位址變更的時候，對輔助索引是沒有影響的。我們來看一下mysql中頁的結構，頁是真正存儲記錄的地方，對應B+樹中的一個節點，也是mysql中讀寫資料的最小機關，頁的結構設計也是相當有水準的，能夠加快資料的查詢。

3.10、頁結構

mysql中頁是innodb中存儲資料的基本機關，也是mysql中管理資料的最小機關，和磁盤互動的時候都是以頁來進行的，預設是16kb，mysql中采用b+樹存儲資料，頁相當于b+樹中的一個節點。

頁的結構如下圖：

每個Page都有通用的頭和尾，但是中部的内容根據Page的類型不同而發生變化。Page的頭部裡有我們關心的一些資料，下圖把Page的頭部詳細資訊顯示出來：

我們重點關注和資料組織結構相關的字段：Page的頭部儲存了兩個指針，分别指向前一個Page和後一個Page，根據這兩個指針我們很容易想象出Page連結起來就是一個雙向連結清單的結構，如下圖：

再看看Page的主體内容，我們主要關注行資料和索引的存儲，他們都位于Page的User Records部分，User Records占據Page的大部分空間，User Records由一條一條的Record組成。在一個Page内部，單連結清單的頭尾由固定内容的兩條記錄來表示，字元串形式的"Infimum"代表開頭，"Supremum"代表結尾，這兩個用來代表開頭結尾的Record存儲在System Records的，Infinum、Supremum和User Records組成了一個單向連結清單結構。最初資料是按照插入的先後順序排列的，但是随着新資料的插入和舊資料的删除，資料實體順序會變得混亂，但他們依然通過連結清單的方式保持着邏輯上的先後順序，如下圖：

把User Record的組織形式和若幹Page組合起來，就看到了稍微完整的形式。

innodb為了快速查找記錄，在頁中定義了一個稱之為page directory的目錄槽（slots）,每個槽位占用兩個位元組（用于儲存指向記錄的位址），page directory中的多個slot組成了一個有序數組（可用于二分法快速定位記錄，向下看），行記錄被Page Directory邏輯的分成了多個塊，塊與塊之間是有序的，能夠加速記錄的查找，如下圖：

看上圖，每個行記錄的都有一個n_owned的區域（圖中粉色區域），n_owned辨別所屬的slot這個這個塊有多少條資料，僞記錄Infimum的n_owned值總是1，記錄Supremum的n_owned的取值範圍為[1,8]，其他使用者記錄n_owned的取值範圍[4,8]，并且隻有每個塊中最大的那條記錄的n_owned才會有值，其他的使用者記錄的n_owned為0。

資料檢索過程

在page中查詢資料的時候，先通過b+樹中查詢方法定位到資料所在的頁，然後将頁内整體加載到記憶體中，通過二分法在page directory中檢索資料，縮小範圍，比如需要檢索7，通過二分法查找到7位于slot2和slot3所指向的記錄中間，然後從slot3指向的記錄5開始向後向後一個個找，可以找到記錄7，如果裡面沒有7，走到slot2向的記錄8結束。

n_owned範圍控制在[4,8]内，能保證每個slot管轄的範圍内資料量控制在[4,8]個，能夠加速目标資料的查找，當有資料插入的時候，page directory為了控制每個slot對應塊中記錄的個數（[4,8]），此時page directory中會對slot的數量進行調整。

對page的結構總結一下

1. b+樹中葉子頁之間用雙向連結清單連接配接的，能夠實作範圍查找
2. 頁内部的記錄之間是采用單向連結清單連接配接的，友善通路下一條記錄
3. 為了加快頁内部記錄的查詢，對頁内記錄上加了個有序的稀疏索引，叫頁目錄（page directory）

整體上來說mysql中的索引用到了b+樹，連結清單，二分法查找，做到了快速定位目标資料，快速範圍查找。

第三篇 MySQL索引管理

1、索引分類

分為聚集索引和非聚集索引。

1.1、聚集索引

每個表有且一定會有一個聚集索引，整個表的資料存儲在聚集索引中，mysql索引是采用B+樹結構儲存在檔案中，葉子節點存儲主鍵的值以及對應記錄的資料，非葉子節點不存儲記錄的資料，隻存儲主鍵的值。當表中未指定主鍵時，mysql内部會自動給每條記錄添加一個隐藏的rowid字段（預設4個位元組）作為主鍵，用rowid建構聚集索引。

聚集索引在mysql中又叫主鍵索引。

1.2、非聚集索引（輔助索引）

也是b+樹結構，不過有一點和聚集索引不同，非聚集索引葉子節點存儲字段（索引字段）的值以及對應記錄主鍵的值，其他節點隻存儲字段的值（索引字段）。

每個表可以有多個非聚集索引。

mysql中非聚集索引分為

單列索引：即一個索引隻包含一個列。

多列索引（又稱複合索引）： 即一個索引包含多個列。

唯一索引： 索引列的值必須唯一，允許有一個空值。

1.3、資料檢索的過程

看一張圖：

上面的表中有2個索引：id作為主鍵索引，name作為輔助索引。

innodb我們用的最多，我們隻看圖中左邊的innodb中資料檢索過程：

如果需要查詢id=14的資料，隻需要在左邊的主鍵索引中檢索就可以了。

如果需要搜尋name='Ellison’的資料，需要2步：

1. 先在輔助索引中檢索到name='Ellison’的資料，擷取id為14
2. 再到主鍵索引中檢索id為14的記錄

輔助索引相對于主鍵索引多了第二步。

2、索引管理

2.1、建立索引

方式1：

create [unique] index 索引名稱 on 表名(列名[(length)]);      

方式2：

alter 表名 add [unique] index 索引名稱 (列名[(length)]);      

方式3：建立聯合索引(示例)

alter table 表名 add [unique] index 索引名稱 (列名1,列名2);      

如果字段是char、varchar類型，length可以小于字段實際長度，如果是blog、text等長文本類型，必須指定length。

[unique]：中括号代表可以省略，如果加上了unique，表示建立唯一索引。

如果table後面隻寫一個字段，就是單列索引，如果寫多個字段，就是複合索引，多個字段之間用逗号隔開。

2.2、删除索引

drop index 索引名稱 on 表名;

alter table 表名 drop index 索引名稱;

#删除主鍵索引
alter table 表名 drop primary key;      

2.3、檢視索引

檢視某個表中所有的索引資訊如下：

show index from 表名;      

2.4、索引修改

可以先删除索引，再重建索引。

3、示例

準備200萬資料

/*建庫javacode2018*/
DROP DATABASE IF EXISTS javacode2018;
CREATE DATABASE javacode2018;
USE javacode2018;

/*建表test1*/
DROP TABLE IF EXISTS test1;
CREATE TABLE test1 (
  id     INT NOT NULL COMMENT '編号',
  name   VARCHAR(20) NOT NULL COMMENT '姓名',
  sex TINYINT NOT NULL COMMENT '性别,1：男，2：女',
  email  VARCHAR(50)
);

/*準備資料*/
DROP PROCEDURE IF EXISTS proc1;
DELIMITER $
CREATE PROCEDURE proc1()
  BEGIN
    DECLARE i INT DEFAULT 1;
    START TRANSACTION;
    WHILE i <= 2000000 DO
      INSERT INTO test1 (id, name, sex, email) VALUES (i,concat('javacode',i),if(mod(i,2),1,2),concat('javacode',i,'@163.com'));
      SET i = i + 1;
      if i%10000=0 THEN
        COMMIT;
        START TRANSACTION;
      END IF;
    END WHILE;
    COMMIT;
  END $

DELIMITER ;
CALL proc1();
SELECT count(*) FROM test1;      

上圖中使用存儲過程循環插入了200萬記錄，表中有4個字段，除了sex列，其他列的值都是沒有重複的，表中還未建索引。

插入的200萬資料中，id，name，email的值都是沒有重複的。

無索引我們體驗一下查詢速度

mysql> select * from test1 a where a.id = 1;
+----+-----------+-----+-------------------+
| id | name      | sex | email             |
+----+-----------+-----+-------------------+
|  1 | javacode1 |   1 | [email protected] |
+----+-----------+-----+-------------------+
1 row in set (0.77 sec)      

上面我們按id查詢了一條記錄耗時770毫秒，我們在id上面建立個索引感受一下速度。

建立索引

我們在id上面建立一個索引，感受一下：

mysql> create index idx1 on test1 (id);
Query OK, 0 rows affected (2.82 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test1 a where a.id = 1;
+----+-----------+-----+-------------------+
| id | name      | sex | email             |
+----+-----------+-----+-------------------+
|  1 | javacode1 |   1 | [email protected] |
+----+-----------+-----+-------------------+
1 row in set (0.00 sec)      

上面的查詢是不是非常快，耗時1毫秒都不到。

我們在name上也建立個索引，感受一下查詢的神速，如下：

mysql> create unique index idx2 on test1(name);
Query OK, 0 rows affected (9.67 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test1 where name = 'javacode1';
+----+-----------+-----+-------------------+
| id | name      | sex | email             |
+----+-----------+-----+-------------------+
|  1 | javacode1 |   1 | [email protected] |
+----+-----------+-----+-------------------+
1 row in set (0.00 sec)      

查詢快如閃電，有沒有，索引是如此的神奇。

建立索引并指定長度

通過email檢索一下資料

mysql> select * from test1 a where a.email = '[email protected]';
+---------+-----------------+-----+-------------------------+
| id      | name            | sex | email                   |
+---------+-----------------+-----+-------------------------+
| 1000085 | javacode1000085 |   1 | [email protected] |
+---------+-----------------+-----+-------------------------+
1 row in set (1.28 sec)      

耗時1秒多，回頭去看一下插入資料的sql，我們可以看到所有的email記錄，每條記錄的前面15個字元是不一樣的，結尾是一樣的（都是@163.com），通過前面15個字元就可以定位一個email了，那麼我們可以對email建立索引的時候指定一個長度為15，這樣相對于整個email字段更短一些，查詢效果是一樣的，這樣一個頁中可以存儲更多的索引記錄，指令如下：

mysql> create index idx3 on test1 (email(15));
Query OK, 0 rows affected (7.67 sec)
Records: 0  Duplicates: 0  Warnings: 0      

然後看一下查詢效果：

mysql> select * from test1 a where a.email = '[email protected]';
+---------+-----------------+-----+-------------------------+
| id      | name            | sex | email                   |
+---------+-----------------+-----+-------------------------+
| 1000085 | javacode1000085 |   1 | [email protected] |
+---------+-----------------+-----+-------------------------+
1 row in set (0.00 sec)      

耗時不到1毫秒，神速。

檢視表中的索引

我們看一下test1表中的所有索引，如下

mysql> show index from test1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test1 |          0 | idx2     |            1 | name        | A         |     1992727 |     NULL | NULL   |      | BTREE      |         |               |
| test1 |          1 | idx1     |            1 | id          | A         |     1992727 |     NULL | NULL   |      | BTREE      |         |               |
| test1 |          1 | idx3     |            1 | email       | A         |     1992727 |       15 | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)      

可以看到test1表中3個索引的詳細資訊(索引名稱、類型，字段)。

删除索引

我們删除idx1，然後再列出test1表所有索引，如下：

mysql> drop index idx1 on test1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test1 |          0 | idx2     |            1 | name        | A         |     1992727 |     NULL | NULL   |      | BTREE      |         |               |
| test1 |          1 | idx3     |            1 | email       | A         |     1992727 |       15 | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)      

本篇主要是mysql中索引管理相關一些操作，屬于基礎知識，必須掌握。

第四篇 如何正确使用索引

1、通常說的這個查詢走索引了是什麼意思？

當我們對某個字段的值進行某種檢索的時候，如果這個檢索過程中，我們能夠快速定位到目标資料所在的頁，有效的降低頁的io操作，而不需要去掃描所有的資料頁的時候，我們認為這種情況能夠有效的利用索引，也稱這個檢索可以走索引，如果這個過程中不能夠确定資料在那些頁中，我們認為這種情況下索引對這個查詢是無效的，此查詢不走索引。

2、b+樹中資料檢索過程

2.1、唯一記錄檢索

如上圖，所有的資料都是唯一的，查詢105的記錄，過程如下：

1. 将P1頁加載到記憶體
2. 在記憶體中采用二分法查找，可以确定105位于[100,150)中間，是以我們需要去加載100關聯P4頁
3. 将P4加載到記憶體中，采用二分法找到105的記錄後退出

2.2、查詢某個值的所有記錄

如上圖，查詢105的所有記錄，過程如下：

1. 将P1頁加載到記憶體
2. 在記憶體中采用二分法查找，可以确定105位于[100,150)中間，100關聯P4頁
3. 将P4加載到記憶體中，采用二分法找到最有一個小于105的記錄，即100，然後通過連結清單從100開始向後通路，找到所有的105記錄，直到遇到第一個大于100的值為止

2.3、範圍查找

資料如上圖，查詢[55,150]所有記錄，由于頁和頁之間是雙向連結清單升序結構，頁内部的資料是單項升序連結清單結構，是以隻用找到範圍的起始值所在的位置，然後通過依靠連結清單通路兩個位置之間所有的資料即可，過程如下：

1. 将P1頁加載到記憶體
2. 記憶體中采用二分法找到55位于50關聯的P3頁中，150位于P5頁中
3. 将P3加載到記憶體中，采用二分法找到第一個55的記錄，然後通過連結清單結構繼續向後通路P3中的60、67，當P3通路完畢之後，通過P3的nextpage指針通路下一頁P4中所有記錄，繼續周遊P4中的所有記錄，直到通路到P5中的150為止。

2.4、模糊比對

資料如上圖。

查詢以 f 開頭的所有記錄

過程如下：

1. 将P1資料加載到記憶體中
2. 在P1頁的記錄中采用二分法找到最後一個小于等于f的值，這個值是f，以及第一個大于f的，這個值是z，f指向葉節點P3，z指向葉節點P6，此時可以斷定以f開頭的記錄可能存在于[P3,P6)這個範圍的頁内，即P3、P4、P5這三個頁中
3. 加載P3這個頁，在内部以二分法找到第一條f開頭的記錄，然後以連結清單方式繼續向後通路P4、P5中的記錄，即可以找到所有已f開頭的資料

查詢包含f的記錄

包含的查詢在sql中的寫法是%f%，通過索引我們還可以快速定位所在的頁麼？

可以看一下上面的資料，f在每個頁中都存在，我們通過P1頁中的記錄是無法判斷包含f的記錄在那些頁的，隻能通過io的方式加載所有葉子節點，并且周遊所有記錄進行過濾，才可以找到包含f的記錄。

是以如果使用了%值%這種方式，索引對查詢是無效的。

2.5、最左比對原則

當b+樹的資料項是複合的資料結構，比如(name,age,sex)的時候，b+樹是按照從左到右的順序來建立搜尋樹的， 比如當(張三,20,F)這樣的資料來檢索的時候，b+樹會優先比較name來确定下一步的所搜方向，如果name相同再依次比較age和sex，最後得到檢索的資料；但當(20,F)這樣的沒有name的資料來的時候，b+樹就不知道下一步該查哪個節點，因為建立搜尋樹的時候name就是第一個比較因子，必須要先根據name來搜尋才能知道下一步去哪裡查詢。比如當(張三,F)這樣的資料來檢索時，b+樹可以用name來指定搜尋方向，但下一個字段age的缺失，是以隻能把名字等于張三的資料都找到，然後再比對性别是F的資料了， 這個是非常重要的性質，即索引的最左比對特性。

來一些示例我們體驗一下。

下圖中是3個字段(a,b,c)的聯合索引，索引中資料的順序是以 a asc,b asc,c asc 這種排序方式存儲在節點中的，索引先以a字段升序，如果a相同的時候，以b字段升序，b相同的時候，以c字段升序，節點中每個資料認真看一下。

查詢a=1的記錄

由于頁中的記錄是以a asc,b asc,c asc這種排序方式存儲的，是以a字段是有序的，可以通過二分法快速檢索到，過程如下：

1. 将P1加載到記憶體中
2. 在記憶體中對P1中的記錄采用二分法找，可以确定a=1的記錄位于{1,1,1}和{1,5,1}關聯的範圍内，這兩個值子節點分别是P2、P4
3. 加載葉子節點P2，在P2中采用二分法快速找到第一條a=1的記錄，然後通過連結清單向下一條及下一頁開始檢索，直到在P4中找到第一個不滿足a=1的記錄為止

查詢a=1 and b=5的記錄

方法和上面的一樣，可以确定a=1 and b=5的記錄位于{1,1,1}和{1,5,1}關聯的範圍内，查找過程和a=1查找步驟類似。

查詢b=1的記錄

這種情況通過P1頁中的記錄，是無法判斷b=1的記錄在那些頁中的，隻能加鎖索引樹所有葉子節點，對所有記錄進行周遊，然後進行過濾，此時索引是無效的。

按照c的值查詢

這種情況和查詢b=1也一樣，也隻能掃描所有葉子節點，此時索引也無效了。

按照b和c一起查

這種也是無法利用索引的，也隻能對所有資料進行掃描，一條條判斷了，此時索引無效。

按照[a,c]兩個字段查詢

這種隻能利用到索引中的a字段了，通過a确定索引範圍，然後加載a關聯的所有記錄，再對c的值進行過濾。

查詢a=1 and b>=0 and c=1的記錄

這種情況隻能先确定a=1 and b>=0所在頁的範圍，然後對這個範圍的所有頁進行周遊，c字段在這個查詢的過程中，是無法确定c的資料在哪些頁的，此時我們稱c是不走索引的，隻有a、b能夠有效的确定索引頁的範圍。

類似這種的還有>、<、between and，多字段索引的情況下，mysql會一直向右比對直到遇到範圍查詢(>、<、between、like)就停止比對。

上面說的各種情況，大家都多看一下圖中資料，認真分析一下查詢的過程，基本上都可以了解了。

上面這種查詢叫做最左比對原則。

3、索引區分度

我們看2個有序數組

[1,2,3,4,5,6,7,8,8,9,10]

[1,1,1,1,1,8,8,8,8,8]

上面2個數組是有序的，都是10條記錄，如果我需要檢索值為8的所有記錄，那個更快一些？

咱們使用二分法查找包含8的所有記錄過程如下：先使用二分法找到最後一個小于8的記錄，然後沿着這條記錄向後擷取下一個記錄，和8對比，知道遇到第一個大于8的數字結束，或者到達數組末尾結束。

采用上面這種方法找到8的記錄，第一個數組中更快的一些。因為第二個數組中含有8的比例更多的，需要通路以及比對的次數更多一些。

這裡就涉及到資料的區分度問題：

索引區分度 = count(distint 記錄) / count(記錄)。

當索引區分度高的時候，檢索資料更快一些，索引區分度太低，說明重複的資料比較多，檢索的時候需要通路更多的記錄才能夠找到所有目标資料。

當索引區分度非常小的時候，基本上接近于全索引資料的掃描了，此時查詢速度是比較慢的。

第一個數組索引區分度為1，第二個區分度為0.2，是以第一個檢索更快的一些。

是以我們建立索引的時候，盡量選擇區分度高的列作為索引。

4、正确使用索引

4.1、準備400萬測試資料

/*建庫javacode2018*/
DROP DATABASE IF EXISTS javacode2018;
CREATE DATABASE javacode2018;
USE javacode2018;
/*建表test1*/
DROP TABLE IF EXISTS test1;
CREATE TABLE test1 (
  id     INT NOT NULL COMMENT '編号',
  name   VARCHAR(20) NOT NULL COMMENT '姓名',
  sex TINYINT NOT NULL COMMENT '性别,1：男，2：女',
  email  VARCHAR(50)
);

/*準備資料*/
DROP PROCEDURE IF EXISTS proc1;
DELIMITER $
CREATE PROCEDURE proc1()
  BEGIN
    DECLARE i INT DEFAULT 1;
    START TRANSACTION;
    WHILE i <= 4000000 DO
      INSERT INTO test1 (id, name, sex, email) VALUES (i,concat('javacode',i),if(mod(i,2),1,2),concat('javacode',i,'@163.com'));
      SET i = i + 1;
      if i%10000=0 THEN
        COMMIT;
        START TRANSACTION;
      END IF;
    END WHILE;
    COMMIT;
  END $

DELIMITER ;
CALL proc1();      

上面插入的400萬資料，除了sex列，其他列的值都是沒有重複的。

4.2、無索引檢索效果

400萬資料，我們随便查詢幾個記錄看一下效果。

按照id查詢記錄

mysql> select * from test1 where id = 1;
+----+-----------+-----+-------------------+
| id | name      | sex | email             |
+----+-----------+-----+-------------------+
|  1 | javacode1 |   1 | [email protected] |
+----+-----------+-----+-------------------+
1 row in set (1.91 sec)      

id=1的資料，表中隻有一行，耗時近2秒，由于id列無索引，隻能對400萬資料進行全表掃描。

4.3、主鍵檢索

test1表中沒有明确的指定主鍵，我們将id設定為主鍵：

mysql> alter table test1 modify id int not null primary key;
Query OK, 0 rows affected (10.93 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test1 |          0 | PRIMARY  |            1 | id          | A         |     3980477 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)      

id被置為主鍵之後，會在id上建立聚集索引，随便檢索一條我們看一下效果：

mysql> select * from test1 where id = 1000000;
+---------+-----------------+-----+-------------------------+
| id      | name            | sex | email                   |
+---------+-----------------+-----+-------------------------+
| 1000000 | javacode1000000 |   2 | [email protected] |
+---------+-----------------+-----+-------------------------+
1 row in set (0.00 sec)      

這個速度很快，這個走的是上面介紹的唯一記錄檢索。

4.4、between and範圍檢索

mysql> select count(*) from test1 where id between 100 and 110;
+----------+
| count(*) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)      

速度也很快，id上有主鍵索引，這個采用的上面介紹的範圍查找可以快速定位目标資料。

但是如果範圍太大，跨度的page也太多，速度也會比較慢，如下：

mysql> select count(*) from test1 where id between 1 and 2000000;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (1.17 sec)      

上面id的值跨度太大，1所在的頁和200萬所在頁中間有很多頁需要讀取，是以比較慢。

是以使用between and的時候，區間跨度不要太大。

4.5、in的檢索

in方式檢索資料，我們還是經常用的。

平時我們做項目的時候，建議少用表連接配接，比如電商中需要查詢訂單的資訊和訂單中商品的名稱，可以先查詢查詢訂單表，然後訂單表中取出商品的id清單，采用in的方式到商品表檢索商品資訊，由于商品id是商品表的主鍵，是以檢索速度還是比較快的。

通過id在400萬資料中檢索100條資料，看看效果：

mysql> select * from test1 a where a.id in (100000, 100001, 100002, 100003, 100004, 100005, 100006, 100007, 100008, 100009, 100010, 100011, 100012, 100013, 100014, 100015, 100016, 100017, 100018, 100019, 100020, 100021, 100022, 100023, 100024, 100025, 100026, 100027, 100028, 100029, 100030, 100031, 100032, 100033, 100034, 100035, 100036, 100037, 100038, 100039, 100040, 100041, 100042, 100043, 100044, 100045, 100046, 100047, 100048, 100049, 100050, 100051, 100052, 100053, 100054, 100055, 100056, 100057, 100058, 100059, 100060, 100061, 100062, 100063, 100064, 100065, 100066, 100067, 100068, 100069, 100070, 100071, 100072, 100073, 100074, 100075, 100076, 100077, 100078, 100079, 100080, 100081, 100082, 100083, 100084, 100085, 100086, 100087, 100088, 100089, 100090, 100091, 100092, 100093, 100094, 100095, 100096, 100097, 100098, 100099);
+--------+----------------+-----+------------------------+
| id     | name           | sex | email                  |
+--------+----------------+-----+------------------------+
| 100000 | javacode100000 |   2 | [email protected] |
| 100001 | javacode100001 |   1 | [email protected] |
| 100002 | javacode100002 |   2 | [email protected] |
.......
| 100099 | javacode100099 |   1 | [email protected] |
+--------+----------------+-----+------------------------+
100 rows in set (0.00 sec)      

耗時不到1毫秒，還是相當快的。

這個相當于多個分解為多個唯一記錄檢索，然後将記錄合并。

4.6、多個索引時查詢如何走？

我們在name、sex兩個字段上分别建個索引

mysql> create index idx1 on test1(name);
Query OK, 0 rows affected (13.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx2 on test1(sex);
Query OK, 0 rows affected (6.77 sec)
Records: 0  Duplicates: 0  Warnings: 0      

看一下查詢：

mysql> select * from test1 where name='javacode3500000' and sex=2;
+---------+-----------------+-----+-------------------------+
| id      | name            | sex | email                   |
+---------+-----------------+-----+-------------------------+
| 3500000 | javacode3500000 |   2 | [email protected] |
+---------+-----------------+-----+-------------------------+
1 row in set (0.00 sec)      

上面查詢速度很快，name和sex上各有一個索引，覺得上面走哪個索引？

有人說name位于where第一個，是以走的是name字段所在的索引，過程可以解釋為這樣：

1. 走name所在的索引找到javacode3500000對應的所有記錄
2. 周遊記錄過濾出sex=2的值

我們看一下name='javacode3500000’檢索速度，确實很快，如下：

mysql> select * from test1 where name='javacode3500000';
+---------+-----------------+-----+-------------------------+
| id      | name            | sex | email                   |
+---------+-----------------+-----+-------------------------+
| 3500000 | javacode3500000 |   2 | [email protected] |
+---------+-----------------+-----+-------------------------+
1 row in set (0.00 sec)      

走name索引，然後再過濾，确實可以，速度也很快，果真和where後字段順序有關麼？我們把name和sex的順序對調一下，如下：

mysql> select * from test1 where sex=2 and name='javacode3500000';
+---------+-----------------+-----+-------------------------+
| id      | name            | sex | email                   |
+---------+-----------------+-----+-------------------------+
| 3500000 | javacode3500000 |   2 | [email protected] |
+---------+-----------------+-----+-------------------------+
1 row in set (0.00 sec)      

速度還是很快，這次是不是先走sex索引檢索出資料，然後再過濾name呢？我們先來看一下sex=2查詢速度：

mysql> select count(id) from test1 where sex=2;
+-----------+
| count(id) |
+-----------+
|   2000000 |
+-----------+
1 row in set (0.36 sec)      

看上面，查詢耗時360毫秒，200萬資料，如果走sex肯定是不行的。

我們使用explain來看一下：

mysql> explain select * from test1 where sex=2 and name='javacode3500000';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test1 | NULL       | ref  | idx1,idx2     | idx1 | 62      | const |    1 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)      

possible_keys：列出了這個查詢可能會走兩個索引（idx1、idx2）

實際上走的卻是idx1（key列：實際走的索引）。

當多個條件中有索引的時候，并且關系是and的時候，會走索引區分度高的，顯然name字段重複度很低，走name查詢會更快一些。

4.7、模糊查詢

看兩個查詢

mysql> select count(*) from test1 a where a.name like 'javacode1000%';
+----------+
| count(*) |
+----------+
|     1111 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from test1 a where a.name like '%javacode1000%';
+----------+
| count(*) |
+----------+
|     1111 |
+----------+
1 row in set (1.78 sec)      

上面第一個查詢可以利用到name字段上面的索引，下面的查詢是無法确定需要查找的值所在的範圍的，隻能全表掃描，無法利用索引，是以速度比較慢，這個過程上面有說過。

4.8、回表

當需要查詢的資料在索引樹中不存在的時候，需要再次到聚集索引中去擷取，這個過程叫做回表，如查詢：

mysql> select * from test1 where name='javacode3500000';
+---------+-----------------+-----+-------------------------+
| id      | name            | sex | email                   |
+---------+-----------------+-----+-------------------------+
| 3500000 | javacode3500000 |   2 | [email protected] |
+---------+-----------------+-----+-------------------------+
1 row in set (0.00 sec)      

上面查詢是*，由于name列所在的索引中隻有name、id兩個列的值，不包含sex、email，是以上面過程如下：

1. 走name索引檢索javacode3500000對應的記錄，取出id為3500000
2. 在主鍵索引中檢索出id=3500000的記錄，擷取所有字段的值

4.9、索引覆寫

查詢中采用的索引樹中包含了查詢所需要的所有字段的值，不需要再去聚集索引檢索資料，這種叫索引覆寫。

我們來看一個查詢：

select id,name from test1 where name='javacode3500000';      

name對應idx1索引，id為主鍵，是以idx1索引樹葉子節點中包含了name、id的值，這個查詢隻用走idx1這一個索引就可以了，如果select後面使用*，還需要一次回表擷取sex、email的值。

是以寫sql的時候，盡量避免使用*，*可能會多一次回表操作，需要看一下是否可以使用索引覆寫來實作，效率更高一些。

【可參考這篇部落格（後續整理）

4.10、索引下推

簡稱ICP，Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一種在存儲引擎層使用索引過濾資料的一種優化方式，ICP可以減少存儲引擎通路基表的次數以及MySQL伺服器通路存儲引擎的次數。

舉個例子來說一下：

我們需要查詢name以javacode35開頭的，性别為1的記錄數，sql如下：

mysql> select count(id) from test1 a where name like 'javacode35%' and sex = 1;
+-----------+
| count(id) |
+-----------+
|     55556 |
+-----------+
1 row in set (0.19 sec)      

過程：

1. 走name索引檢索出以javacode35的第一條記錄，得到記錄的id
2. 利用id去主鍵索引中查詢出這條記錄R1
3. 判斷R1中的sex是否為1，然後重複上面的操作，直到找到所有記錄為止。

上面的過程中需要走name索引以及需要回表操作。

如果采用ICP的方式，我們可以這麼做，建立一個(name,sex)的組合索引，查詢過程如下：

1. 走(name,sex)索引檢索出以javacode35的第一條記錄，可以得到(name,sex,id)，記做R1
2. 判斷R1.sex是否為1，然後重複上面的操作，知道找到所有記錄為止

這個過程中不需要回表操作了，通過索引的資料就可以完成整個條件的過濾，速度比上面的更快一些。

更詳細的索引下推内容可看：

4.11、數字使字元串類索引失效

mysql> insert into test1 (id,name,sex,email) values (4000001,'1',1,'[email protected]');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1 where name = '1';
+---------+------+-----+----------------------+
| id      | name | sex | email                |
+---------+------+-----+----------------------+
| 4000001 | 1    |   1 | [email protected] |
+---------+------+-----+----------------------+
1 row in set (0.00 sec)

mysql> select * from test1 where name = 1;
+---------+------+-----+----------------------+
| id      | name | sex | email                |
+---------+------+-----+----------------------+
| 4000001 | 1    |   1 | [email protected] |
+---------+------+-----+----------------------+
1 row in set, 65535 warnings (3.30 sec)      

上面3條sql，我們插入了一條記錄。

第二條查詢很快，第三條用name和1比較，name上有索引，name是字元串類型，字元串和數字比較的時候，會将字元串強制轉換為數字，然後進行比較，是以第二個查詢變成了全表掃描，隻能取出每條資料，将name轉換為數字和1進行比較。

數字字段和字元串比較什麼效果呢？如下：

mysql> select * from test1 where id = '4000000';
+---------+-----------------+-----+-------------------------+
| id      | name            | sex | email                   |
+---------+-----------------+-----+-------------------------+
| 4000000 | javacode4000000 |   2 | [email protected] |
+---------+-----------------+-----+-------------------------+
1 row in set (0.00 sec)

mysql> select * from test1 where id = 4000000;
+---------+-----------------+-----+-------------------------+
| id      | name            | sex | email                   |
+---------+-----------------+-----+-------------------------+
| 4000000 | javacode4000000 |   2 | [email protected] |
+---------+-----------------+-----+-------------------------+
1 row in set (0.00 sec)      

id上面有主鍵索引，id是int類型的，可以看到，上面兩個查詢都非常快，都可以正常利用索引快速檢索，是以如果字段是數組類型的，查詢的值是字元串還是數組都會走索引。

4.12、函數使索引無效

mysql> select a.name+1 from test1 a where a.name = 'javacode1';
+----------+
| a.name+1 |
+----------+
|        1 |
+----------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from test1 a where concat(a.name,'1') = 'javacode11';
+----+-----------+-----+-------------------+
| id | name      | sex | email             |
+----+-----------+-----+-------------------+
|  1 | javacode1 |   1 | [email protected] |
+----+-----------+-----+-------------------+
1 row in set (2.88 sec)      

name上有索引，上面查詢，第一個走索引，第二個不走索引，第二個使用了函數之後，name所在的索引樹是無法快速定位需要查找的資料所在的頁的，隻能将所有頁的記錄加載到記憶體中，然後對每條資料使用函數進行計算之後再進行條件判斷，此時索引無效了，變成了全表資料掃描。

結論：索引字段使用函數查詢使索引無效。

4.13、運算符使索引無效

mysql> select * from test1 a where id = 2 - 1;
+----+-----------+-----+-------------------+
| id | name      | sex | email             |
+----+-----------+-----+-------------------+
|  1 | javacode1 |   1 | [email protected] |
+----+-----------+-----+-------------------+
1 row in set (0.00 sec)

mysql> select * from test1 a where id+1 = 2;
+----+-----------+-----+-------------------+
| id | name      | sex | email             |
+----+-----------+-----+-------------------+
|  1 | javacode1 |   1 | [email protected] |
+----+-----------+-----+-------------------+
1 row in set (2.41 sec)      

id上有主鍵索引，上面查詢，第一個走索引，第二個不走索引，第二個使用運算符，id所在的索引樹是無法快速定位需要查找的資料所在的頁的，隻能将所有頁的記錄加載到記憶體中，然後對每條資料的id進行計算之後再判斷是否等于1，此時索引無效了，變成了全表資料掃描。

結論：索引字段使用了函數将使索引無效。

4.14、使用索引優化排序

我們有個訂單表t_order(id,user_id,addtime,price)，經常會查詢某個使用者的訂單，并且按照addtime升序排序，應該怎麼建立索引呢？我們來分析一下。

在user_id上建立索引，我們分析一下這種情況，資料檢索的過程：

1. 走user_id索引，找到記錄的的id
2. 通過id在主鍵索引中回表檢索出整條資料
3. 重複上面的操作，擷取所有目标記錄
4. 在記憶體中對目标記錄按照addtime進行排序

5、總結一下使用索引的一些建議

1. 在區分度高的字段上面建立索引可以有效的使用索引，區分度太低，無法有效的利用索引，可能需要掃描所有資料頁，此時和不使用索引差不多
2. 聯合索引注意最左比對原則：必須按照從左到右的順序比對，mysql會一直向右比對直到遇到範圍查詢(>、<、between、like)就停止比對，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整
3. 查詢記錄的時候，少使用*，盡量去利用索引覆寫，可以減少回表操作，提升效率
4. 有些查詢可以采用聯合索引，進而使用到索引下推（IPC），也可以減少回表操作，提升效率
5. 禁止對索引字段使用函數、運算符操作，會使索引失效
6. 字元串字段和數字比較的時候會使索引無效
7. 模糊查詢’%值%'會使索引無效，變為全表掃描，但是’值%'這種可以有效利用索引
8. 排序中盡量使用到索引字段，這樣可以減少排序，提升查詢效率