索引是SQL優化中最重要的手段之一，本文從基礎到原理，帶你深度掌握索引。

一、索引基礎

1、什麼是索引

MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效擷取資料的資料結構，索引對于良好的性能非常關鍵，尤其是當表中的資料量越來越大時，索引對于性能的影響愈發重要。索引優化應該是對查詢性能優化最有效的手段了。索引能夠輕易将查詢性能提高好幾個數量級。

通俗來講，索引類似文章的目錄，用來提高查詢的效率。

2、索引分類

常見的索引類型有：主鍵索引、唯一索引、普通索引、全文索引、組合索引

2.1、主鍵索引

當一張表，把某個列設為主鍵的時候，則該列就是主鍵索引

create table a (  
    id int primary key auto_increment,  
    name varchar(20) not null default ''  
);

這裡id就是表的主鍵，如果當建立表時沒有指定主鍵索引，也可以在建立表之後添加：

alter table table_name add primary key (column_name);

1.2、普通索引

用表中的普通列建構的索引，沒有任何限制

create index 索引名 on table_name(column1);
alter table table_name add index 索引名(column1);

1.3、全文索引

全文索引主要針對文本檔案，比如文章，标題。在MySQL5.6之前，隻有MyISAM存儲引擎支援全文索引，MySQL5.6之後

InnoDB

存儲引擎也支援全文索引。

create table c(  
    id int primary key auto_increment ,  
    title varchar(20),  
    content text,  
    fulltext(title,content)  
) engine=myisam charset utf8;

insert into c(title,content) values  
    ('MySQL Tutorial','DBMS stands for DataBase ...'),  
    ('How To Use MySQL Well','After you went through a ...'),  
    ('Optimizing MySQL','In this tutorial we will show ...'),  
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),  
    ('MySQL vs. YourSQL','In the following database comparison ...'),  
    ('MySQL Security','When configured properly, MySQL ...');

1.4、唯一索引

見名知義，索引列中的值必須是唯一的，但是允許為空值。d表中name就是唯一索引，相比主鍵索引，主鍵字段不能為null，也不能重複

create table d(
    id int primary key auto_increment , 
    name varchar(32) unique
    )

1.5、組合索引

用多個列組合建構的索引。

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

組合索引遵循“最左字首”原則，使用時最好把最常用作為檢索或排序的列放在最左，依次遞減。組合索引相當于建立了col1，col1col2，col1col2col3 三個索引，而col2或者col3是不能使用索引的。在使用組合索引的時候可能因為列名長度過長而導緻索引的key太大，導緻效率降低，在允許的情況下，可以隻取col1和col2的前幾個字元作為索引。 ALTER TABLE ‘table_name’ ADD INDEX index_name(col1(4),col2（3))； 表示使用col1的前4個字元和col2的前3個字元作為索引

3、索引機制淺析

我們這裡先簡單剖析一下索引的機制，為接下來的深入做一些鋪墊。

3.1、索引加快查詢的原理

傳統的查詢方法，是按照表的順序周遊的，不論查詢幾條資料，MySQL需要将表的資料從頭到尾周遊一遍。

在我們添加完索引之後，MySQL一般通過BTREE算法生成一個索引檔案，在查詢資料庫時，找到索引檔案進行周遊，使用能夠大幅地查詢的效率的折半查找的方式，找到相應的鍵進而擷取資料。

3.1、索引的代價

建立索引是為産生索引檔案的，占用磁盤空間。索引檔案是一個二叉樹類型的檔案，可想而知我們的DML操作((資料操作語言,對表記錄的(增、删、改)操作)同樣也會對索引檔案進行修改，是以性能會相應的有所下降。

二、索引存儲資料結構

上面已經說到，索引實際上是資料庫中

滿足特定查找算法的資料結構

，這些資料結構以某種方式引用（指向）資料，這樣就可以

在這些資料結構上實作進階查找算法

。

可能我們都知道，MySQL索引是

B+樹

資料結構，當然，實際上索引還有

哈希表

、

有序數組

等常見的資料結構。

1、哈希表

哈希表是一種以鍵-值（key-value）存儲資料的結構，我們隻要輸入待查找的值即key，就可以找到其對應的值即Value。哈希的思路很簡單，把值放在數組裡，用一個哈希函數把key換算成一個确定的位置，然後把value放在數組的這個位置。

不可避免地，多個key值經過哈希函數的換算，會出現同一個值的情況。處理這種情況的一種方法是，拉出一個連結清單。

是以，需要注意，哈希表後的連結清單并不是有序的，區間查詢的話需要掃描連結清單，是以哈希表這種結構适用于隻有等值查詢的場景，比如Memcached及其他一些NoSQL引擎。

2、有序數組

另外一個大家比較熟悉的數組結構，有序數組在等值查詢和範圍查詢場景中的性能都非常優秀。

如果僅僅看查詢效率，有序數組是非常棒的資料結構。但是，在需要更新資料的時候就麻煩了，你往中間插入一個記錄就必須得挪動後面所有的記錄，成本太高。

是以，有序數組索引隻适用于靜态存儲引擎，比如你要儲存的是2017年某個城市的所有人口資訊，這類不會再修改的資料。

這兩種都不是最主要的索引，常見的索引使用的資料結構是樹結構，樹是資料結構裡相對複雜一些的資料結構，我們來一步步認識索引的樹結構。

3、二分查找

二分查找也稱折半查找（Binary Search），它是一種效率較高的查找方法。但是，折半查找要求線性表必須采用順序存儲結構，而且表中元素按關鍵字有序排列。

查找方法：首先，假設表中元素是按升序排列，将表中間位置記錄的關鍵字與查找關鍵字比較，如果兩者相等，則查找成功；否則利用中間位置記錄将表分成前、後兩個子表，如果中間位置記錄的關鍵字大于查找關鍵字，則進一步查找前一子表，否則進一步查找後一子表。重複以上過程，直到找到滿足條件的記錄，使查找成功，或直到子表不存在為止，此時查找不成功。

上面提到的有序數組的等值查詢和比較查詢效率非常高，但是更新資料存在問題。

為了支援頻繁的修改，比如插入資料，我們需要采用連結清單。連結清單的話，如果是單連結清單，它的查找效率還是不夠高。

是以，有沒有可以使用二分查找的連結清單呢?

為了解決這個問題，BST(Binary Search Tree)也就是我們所說的二叉查找樹誕生了。

4、二叉查找樹

二叉樹具有以下性質：左子樹的鍵值小于根的鍵值，右子樹的鍵值大于根的鍵值。

如下圖所示就是一棵二叉查找樹，

在這種比較平衡的狀态下查找時間複雜度是O(log(n))。

但是二叉查找樹存在一個問題：在某些極端情況下會退化成連結清單。

同樣是2,3,4,6,7,8這六個數字，如果我們插入的資料剛好是有序的，那它就變成這樣👇

這個時候，二叉查找樹查找的時間複雜度就和連結清單一樣，是O(n)。

造成它“叉劈”的原因是什麼呢? 因為左右子樹深度差太大，這棵樹的左子樹根本沒有節點——也就是它不夠平衡。

是以，我們有沒有左右子樹深度相差不是那麼大，更加平衡的樹呢? ——那就就是平衡二叉樹，叫做 Balanced binary search trees，或者 AVL 樹。

5、AVL 樹

AVL Trees (Balanced binary search trees) 平衡二叉樹的定義:左右子樹深度差絕對值不能超過 1。

例如左子樹的深度是 2，右子樹的深度隻能是 1 或者 3。這個時候我們再按順序插入 2,3,4,6,7,8，就不會“叉劈”👇

AVL樹的平衡是怎麼做到的呢？主要用到了兩個操作

左旋

右旋

插入 1、2、3。

當我們插入了 1、2 之後，如果按照二叉查找樹的定義，3 肯定是要在 2 的右邊的，這個時候根節點 1 的右節點深度會變成 2，但是左節點的深度是 0，因為它沒有子節點，是以就會違反平衡二叉樹的定義。

那應該怎麼辦呢?因為它是右節點下面接一個右節點，右–右型，是以這個時候我們要把 2 提上去，這個操作叫做
左旋

同樣的，如果我們插入3、2、1，這個時候會變成左左型，就會發生 右旋 操作，把 2提上去。

既然平衡二叉樹能保持平衡，不會退化，那麼我們用平衡二叉樹存儲索引可以嗎？——可以的。

當我們用樹的結構來存儲索引的時候，通路一個節點就要跟磁盤之間發生一次 IO。 InnoDB 操作磁盤的最小的機關是一頁(或者叫一個磁盤塊)。與主存不同，磁盤I/O存在機械運動耗費，是以磁盤I/O的時間消耗是巨大的。

是以如果每個節點存儲的資料太少，從索引中找到我們需要的資料，就要通路更多的節點，意味着跟磁盤互動次數就會過多。

那麼解決方案是什麼？

讓每個節點存儲更多的資料。
讓節點上有更多的關鍵字。

節點上的關鍵字的數量越多，我們的指針數也越多，也就是意味着可以有更多的分叉(我們把它叫做“路數”)。

因為分叉數越多，樹的深度就會減少(根節點是 0)。這樣，樹就從瘦高變成了矮胖。

這個時候，我們的樹就不再是二叉了，而是多叉，或者叫做

多路

6、多路平衡查找樹（B-Tree）

接下來看一下多路平衡查找樹，也就是B樹。

B樹是一種多叉平衡查找樹，如下圖主要特點：

B樹的節點中存儲着多個元素，每個内節點有多個分叉。
節點中的元素包含鍵值和資料，節點中的鍵值從大到小排列。也就是說，在所有的節點都儲存資料。
父節點當中的元素不會出現在子節點中。
所有的葉子結點都位于同一層，葉節點具有相同的深度，葉節點之間沒有指針連接配接。

以上圖為例，我們來簡單看幾個查詢：

如果查找key<17，就走左邊子節點；
如果查找17<key<35，就走中間子節點；
如果查找key>35，就走右邊子節點；
如果查找key=17，直接命中；
如果查找key=35，直接命中；

B樹看起來很完美，到這就結束了嗎？并沒有。

B樹不支援範圍查詢的快速查找，你想想這麼一個情況如果我們想要查找10和35之間的資料，查找到15之後，需要回到根節點重新周遊查找，需要從根節點進行多次周遊，查詢效率有待提高。

如果data存儲的是行記錄，行的大小随着列數的增多，所占空間會變大。這時，一個頁中可存儲的資料量就會變少，樹相應就會變高，磁盤IO次數就會變大

是以接下來就引入我們的終極資料結構——B+樹。

7、加強版多路平衡查找樹(B+Tree)

B+樹，作為B樹的更新版，在B樹基礎上，MySQL在B樹的基礎上繼續改造，使用B+樹建構索引。B+樹和B樹最主要的差別在于非葉子節點是否存儲資料的問題

B樹：非葉子節點和葉子節點都會存儲資料。

B+樹：隻有葉子節點才會存儲資料，非葉子節點至存儲鍵值。葉子節點之間使用雙向指針連接配接，最底層的葉子節點形成了一個雙向有序連結清單。

來看一下InnoDB裡的B+樹的具體存儲結構：

來說一下這張圖的重點：

最外面的方塊，的塊我們稱之為一個磁盤塊，可以看到每個磁盤塊包含幾個資料項（粉色所示）和指針（黃色/灰色所示），如根節點磁盤包含資料項17和35，包含指針P1、P2、P3，P1表示小于17的磁盤塊，P2表示在17和35之間的磁盤塊，P3表示大于35的磁盤塊。真實的資料存在于葉子節點即3、4、5……、65。非葉子節點隻不存儲真實的資料，隻存儲指引搜尋方向的資料項，如17、35并不真實存在于資料表中。
葉子節點之間使用雙向指針連接配接，最底層的葉子節點形成了一個雙向有序連結清單。

7.1、存儲容量

舉個例子:假設一條記錄是 1K，一個葉子節點(一頁)可以存儲 16 條記錄。非葉子節點可以存儲多少個指針?

假設索引字段是 bigint 類型，長度為 8 位元組。指針大小在 InnoDB 源碼中設定為 6 位元組，這樣一共 14 位元組。非葉子節點(一頁)可以存儲 16384/14=1170 個這樣的單元(鍵值+指針)，代表有 1170 個指針。

樹深度為 2 的時候，有 1170^2 個葉子節點，可以存儲的資料為 1170*1170*16=21902400。

在查找資料時一次頁的查找代表一次 IO，也就是說，一張 2000 萬左右的表，查詢資料最多需要通路 3 次磁盤。

是以在 InnoDB 中 B+ 樹深度一般為 1-3 層，它就能滿足千萬級的資料存儲。

7.2、查詢效率

我們來看一下 B+Tree 的資料搜尋過程:

1. 例如我們要查找 35，在根節點就找到了鍵值，但是因為它不是頁子節點，是以會繼續往下搜尋，25 是[17,35)的左閉右開的區間的臨界值，是以會走中間的子節點，然後繼續搜尋，它又是[28,34)的左閉右開的區間的臨界值，是以會走左邊的子節點，最後在葉子節點上找到了需要的資料。
2. 如果是範圍查詢，比如要查詢從 22 到 60 的資料，當找到 22 之後，隻需要順着節點和指針順序周遊就可以一次性通路到所有的資料節點，這樣就極大地提高了區間查詢效率(不需要傳回上層父節點重複周遊查找)。

3）添加了指向相鄰葉節點的指針**，形成了帶有順序通路指針的B+Tree，這樣做是為了**提高區間查找的效率，隻要找到第一個值那麼就可以順序的查找後面的值。

7.3、B+樹特點總結

總結一下，InnoDB 中的 B+Tree 的特點:

1. 它是 B Tree 的變種，B Tree 能解決的問題，它都能解決。B Tree 解決的兩大問題是什麼?(每個節點存儲更多關鍵字；路數更多)

2)掃庫、掃表能力更強(如果我們要對表進行全表掃描，隻需要周遊葉子節點就可以了，不需要周遊整棵 B+Tree 拿到所有的資料)

1. B+Tree 的磁盤讀寫能力相對于 B Tree 來說更強(根節點和枝節點不儲存資料區，是以一個節點可以儲存更多的關鍵字，一次磁盤加載的關鍵字更多)
2. 排序能力更強(因為葉子節點上有下一個資料區的指針，資料形成了連結清單)
3. 效率更加穩定(B+Tree 永遠是在葉子節點拿到資料，是以 IO 次數是穩定的)

三、聚簇索引和非聚簇索引

MySQL中最常見的兩種存儲引擎分别是MyISAM和InnoDB，分别實作了

非聚簇索引

和

聚簇索引

首先要介紹幾個概念，在索引的分類中，我們可以按照索引的鍵是否為主鍵來分為“

主鍵索引

”和“

輔助索引

”，使用主鍵鍵值建立的索引稱為“

主鍵索引

”，其它的稱為“

輔助索引

”。是以

主鍵索引

隻能有一個，輔助索引可以有很多個。

1、MyISAM——非聚簇索引

MyISAM存儲引擎采用的是非聚簇索引，非聚簇索引的主鍵索引和輔助索引

基本上是相同的

，隻是主鍵索引不允許重複，不允許空值，他們的葉子結點的key都存儲指向鍵值對應的資料的實體位址。

非聚簇索引的資料表和索引表是分開存儲的。

非聚簇索引中的資料是根據資料的插入順序儲存。是以非聚簇索引更适合單個資料的查詢。插入順序不受鍵值影響。

思考：既然非聚簇索引的主鍵索引索引和輔助索引指向相同的内容，為什麼還要輔助索引呢？索引不就是用來查詢的嗎，用在哪些地方呢？不就是WHERE和ORDER BY 語句後面嗎，那麼如果查詢的條件不是主鍵怎麼辦呢，這個時候就需要輔助索引了。

2、InnoDB——聚簇索引

聚簇索引的主鍵索引的葉子結點存儲的是鍵值對應的資料本身，輔助索引的葉子結點存儲的是鍵值對應的資料的主鍵鍵值。是以主鍵的值長度越小越好，類型越簡單越好。

聚簇索引的資料和主鍵索引存儲在一起。

從上圖中可以看到輔助索引的葉子節點的data存儲的是主鍵的值，主鍵索引的葉子節點的data存儲的是資料本身，也就是說資料和索引存儲在一起，并且索引查詢到的地方就是資料（data）本身，那麼索引的順序和資料本身的順序就是相同的。

因為聚簇輔助索引存儲的是主鍵的鍵值，是以可以在資料行移動或者頁分裂的時候降低成本，因為這時不用維護輔助索引。但是由于主鍵索引存儲的是資料本身，是以聚簇索引會占用更多的空間。

聚簇索引在插入新資料的時候比非聚簇索引慢很多，因為插入新資料時需要檢測主鍵是否重複，這需要周遊主索引的所有葉節點，而非聚簇索引的葉節點儲存的是資料位址，占用空間少，是以分布集中，查詢的時候I/O更少，但聚簇索引的主索引中存儲的是資料本身，資料占用空間大，分布範圍更大，可能占用好多的扇區，是以需要更多次I/O才能周遊完畢。

四、索引使用原則

1、列的離散度

第一個叫做列的離散度，我們先來看一下列的離散度的公式:

count(distinct(column_name)) : count(*)

列的全部不同值和所有資料行的比例。資料行數相同的情況下，分子越大，列的離散度就越高。

mysql> SELECT * FROM `test`.`user` ORDER BY `id` LIMIT 10 OFFSET 0;
+----+-----------+--------+-------------+
| id | name      | gender | phone       |
+----+-----------+--------+-------------+
|  1 | 秦啭      |      0 | 13601722591 |
|  2 | 李镒榘    |      0 | 15204160836 |
|  3 | 陳艮      |      0 | 13601994087 |
|  4 | 沈夷旌    |      0 | 15507785988 |
|  5 | 朱桐泰    |      1 | 13201268193 |
|  6 | 周韬蕊    |      1 | 15705478612 |
|  7 | 馮叻加    |      0 | 13705834063 |
|  8 | 王焓      |      1 | 15006956358 |
|  9 | 黃芪      |      0 | 15108012536 |
| 10 | 吳笄遊    |      0 | 15301860708 |
+----+-----------+--------+-------------+
10 rows in set (0.00 sec)

簡單來說，如果列的重複值越多，離散度就越低，重複值越少，離散度就越高。

了解了離散度的概念之後，我們再來思考一個問題，我們在 name 上面建立索引和在 gender 上面建立索引有什麼差別。

當我們用在 gender 上建立的索引去檢索資料的時候，由于重複值太多，需要掃描的行數就更多。例如，我們現在在 gender 列上面建立一個索引，然後看一下執行計劃。

ALTER TABLE user ADD INDEX idx_user_gender (gender); -- 耗時比較久 
EXPLAIN SELECT * FROM `user` WHERE gender = 0;

+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+---------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys   | key        | key_len | ref   | rows    | filtered | Extra |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+---------+----------+-------+
|  1 | SIMPLE      | user      | NULL       | ref  | idx_user_gender | idx_user_gender | 2       | const | 2492574 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

而 name 的離散度更高，比如“陳艮”的這名字，隻需要掃描一行。

ALTER TABLE user ADD INDEX idx_user_name (name); 

EXPLAIN SELECT * FROM `user` WHERE name = '陳艮';

+----+-------------+-------------+------------+------+----------------------------+----------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+----------------------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user      | NULL       | ref  | idx_name | idx_name | 1023    | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+----------------------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

檢視表上的索引，Cardinality [kɑ:dɪ’nælɪtɪ]代表基數，代表預估的不重複的值的數量。索引的基數與表總行數越接近，列的離散度就越高。

mysql> show indexes from user;
+-------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY           |            1 | id          | A         |     4985145 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | idx_name          |            1 | name        | A         |     2605146 |     NULL | NULL   | YES  | BTREE      |         |               |
| user  |          1 | idx_user_gender   |            1 | gender      | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| user  |          1 | comidx_name_phone |            1 | name        | A         |     2595718 |     NULL | NULL   | YES  | BTREE      |         |               |
| user  |          1 | comidx_name_phone |            2 | phone       | A         |     4972647 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

如果在索引 B+Tree 結構裡面的重複值太多，MySQL 的優化器發現走索引跟使用全表掃描差不了多少的時候，就算建了索引，也不一定會走索引。

2、組合索引最左比對

前面我們說的都是針對單列建立的索引，但有的時候我們的多條件查詢的時候，也會建立組合索引。單列索引可以看成是特殊的組合索引。

比如我們在 user 表上面，給 name 和 phone 建立了一個組合索引。

ALTER TABLE user add INDEX comidx_name_phone (name,phone);

組合索引在 B+Tree 中是複合的資料結構，它是按照從左到右的順序來建立搜尋樹的 (name 在左邊，phone 在右邊)。

從這張圖可以看出來，name 是有序的，phone 是無序的。當 name 相等的時候， phone 才是有序的。

這個時候我們使用 where name= ‘wangwu‘ and phone = ‘139xx ‘去查詢資料的時候， B+Tree 會優先比較 name 來确定下一步應該搜尋的方向，往左還是往右。如果 name 相同的時候再比較 phone。但是如果查詢條件沒有 name，就不知道第一步應該查哪個節點，因為建立搜尋樹的時候 name 是第一個比較因子，是以用不到索引。

2.1、什麼時候用到組合索引

是以，我們在建立組合索引的時候，一定要把最常用的列放在最左邊。比如下面的三條語句，能用到組合索引嗎?

1)使用兩個字段，可以用到組合索引:

mysql> EXPLAIN SELECT * FROM user WHERE name= '陳艮' AND phone = '13601994087';
+----+-------------+-------------+------------+------+----------------------------+-------------------+---------+-------------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys    | key               | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------------+------------+------+----------------------------+-------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | user_innodb | NULL       | ref  | comidx_name_phone | comidx_name_phone | 1070    | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+----------------------------+-------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

2)使用左邊的 name 字段，可以用到組合索引:

mysql> EXPLAIN SELECT * FROM user WHERE name= '陳艮';
+----+-------------+-------------+------------+------+----------------------------+----------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys     | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+----------------------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user_innodb | NULL       | ref  | comidx_name_phone | idx_name | 1023    | const |   19 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+----------------------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

3)使用右邊的 phone 字段，無法使用索引，全表掃描:

mysql> EXPLAIN SELECT * FROM user WHERE  phone = '13601994087';
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4985148 |    10.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

2.2、如何建立組合索引

當建立(a,b,c)聯合索引時，相當于建立了(a)單列索引，(a,b)組合索引以及(a,b,c)組合索引，想要索引生效的話,隻能使用 a和a,b和a,b,c三種組合；當然，b,a也是好使的，因為sql會對它優化。

用 where b=? 和 where b=? and c=? 和 where a=? and c=?是不能使用到索引。不能不用第一個字段，不能中斷。

這裡就是 MySQL 組合索引的最左比對原則。

3、覆寫索引

3.1、回表

在聚簇索引裡，通過輔助索引查找資料，先通過索引找到主鍵索引的鍵值，再通過主鍵值查出索引裡面沒有的資料，它比基于主鍵索引的查詢多掃描了一棵索引樹，這個過程就叫回表。

例如:select * from user where name = ‘lisi’;

3.2、覆寫索引

在輔助索引裡面，不管是單列索引還是聯合索引，如果 select 的資料列隻用從索引中就能夠取得，不必從資料區中讀取，這時候使用的索引就叫做覆寫索引，這樣就避免了回表。

我們先來建立一個聯合索引:

-- 建立聯合索引
ALTER TABLE user add INDEX 'comixd_name_phone' ('name','phone');

這三個查詢語句都用到了覆寫索引:

EXPLAIN SELECT name,phone FROM user WHERE name= '陳艮' AND phone = '13601994087';
EXPLAIN SELECT name FROM user WHERE name= '陳艮' AND phone = '13601994087';
EXPLAIN SELECT phone FROM user WHERE name= '陳艮' AND phone = '13601994087';

Extra 裡面值為“Using index”代表使用了覆寫索引。

mysql> EXPLAIN SELECT name FROM user_innodb WHERE name= '陳艮' AND phone = '13601994087';
+----+-------------+-------------+------------+------+----------------------------+-------------------+---------+-------------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys              | key               | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+----------------------------+-------------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | user_innodb | NULL       | ref  | idx_name,comidx_name_phone | comidx_name_phone | 1070    | const,const |    1 |   100.00 | Using index |
+----+-------------+-------------+------------+------+----------------------------+-------------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

select * ，用不到覆寫索引。

很明顯，因為覆寫索引減少了 IO 次數，減少了資料的通路量，可以大大地提升查詢效率。

4、索引條件下推(ICP)

“索引條件下推”，稱為 Index Condition Pushdown (ICP)，這是MySQL提供的用某一個索引對一個特定的表從表中擷取元組”，注意我們這裡特意強調了“一個”，這是因為這樣的索引優化不是用于多表連接配接而是用于單表掃描，确切地說，是單表利用索引進行掃描以擷取資料的一種方式。它的作用如下

一是說明減少完整記錄（一條完整元組）讀取的個數；
二是說明對于InnoDB聚集索引無效，隻能是對SECOND INDEX這樣的非聚簇索引有效。

關閉 ICP:

set optimizer_switch='index_condition_pushdown=off';

檢視參數:

show variables like 'optimizer_switch';

現在我們要查詢所有名字為陳艮，并且手機号碼後四位為4087這個人。查詢的 SQL:

SELECT * FROM user WHERE name= '陳艮'  and phone LIKE '%4087' ;

這條 SQL 有兩種執行方式:

1、根據組合索引查出所有名字是’陳艮’的二級索引資料，然後回表，到主鍵索引上查詢全部符合條件的資料(19 條資料)。然後傳回給 Server 層，在 Server 層過濾出手機号碼後四位為4087這個人。
2、根據組合索引查出所有名字是’陳艮’的二級索引資料(19 個索引)，然後從二級索引中篩選出手機号碼後四位為4087的索引(1 個索引)，然後再回表，到主鍵索引上查詢全部符合條件的資料(1 條資料)，傳回給 Server 層。

很明顯，第二種方式到主鍵索引上查詢的資料更少。

注意，索引的比較是在存儲引擎進行的，資料記錄的比較，是在 Server 層進行的。而當 phone 的條件不能用于索引過濾時，Server 層不會把 phone 的條件傳遞給存儲引擎，是以讀取了兩條沒有必要的記錄。

這時候，如果滿足 name=’陳艮’的記錄有 100000 條，就會有 99999 條沒有必要讀取的記錄。

執行以下 SQL，Using where:

mysql> EXPLAIN SELECT * FROM user WHERE name= '陳艮' AND phone LIKE '%4087';
+----+-------------+-------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user_innodb | NULL       | ref  | comidx_name_phone | comidx_name_phone | 1023    | const |   19 |    11.11 | Using where |
+----+-------------+-------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Using Where 代表從存儲引擎取回的資料不全部滿足條件，需要在 Server 層過濾。

先用 name條件進行索引範圍掃描，讀取資料表記錄，然後進行比較，檢查是否符合 phone LIKE ‘%4087’ 的條件。此時 19 條中隻有 1 條符合條件。

五、索引建立使用總結

因為索引對于改善查詢性能的作用是巨大的，是以我們的目标是盡量使用索引。

5.1. 索引的建立

根據上一節的分析，我們總結出索引建立的一些注意點：

1、在用于 where 判斷 order 排序和 join 的(on)字段上建立索引
2、索引的個數不要過多。——浪費空間，更新變慢。
3、區分度低的字段，例如性别，不要建索引。——離散度太低，導緻掃描行數過多。
4、頻繁更新的值，不要作為主鍵或者索引。 ——頁分裂
5、組合索引把散列性高(區分度高)的值放在前面。——最左字首比對原則
6、建立複合索引，而不是修改單列索引。——組合索引代替多個單列索引（由于MySQL中每次隻能使用一個索引，是以經常使用多個條件查詢時更适合使用組合索引）
7、過長的字段，怎麼建立索引?——使用短索引。

當字段值比較長的時候，建立索引會消耗很多的空間，搜尋起來也會很慢。我們可以通過截取字段的前面一部分内容建立索引，這個就叫字首索引。

create table shop(address varchar(120) not null); 
alter table shop add key (address(12));

8、不建議用無序的值(例如身份證、UUID )作為索引——當主鍵具有不确定性，會造成葉子節點頻繁分裂，出現磁盤存儲的碎片化

5.2. 什麼時候會用不到索引

1、索引列上使用函數(replace\SUBSTR\CONCAT\sum count avg)、表達式、計算(+ – * /):

explain SELECT * FROM 't2' where id+1 = 4;

2、字元串不加引号，出現隐式轉換

explain SELECT * FROM 'user' where name = 136; 

explain SELECT * FROM 'user' where name = '136';

3、like 條件中前面帶%

where 條件中 like abc%，like %2673%，like %888 都用不到索引嗎?為什麼?

explain select * from user where name like 'wang%'; 

explain select * from user where name like '%wang';

過濾的開銷太大，是以無法使用索引。這個時候可以用全文索引。

4、負向查詢

NOT LIKE 不能:

explain select *from employees where last_name not like 'wang'

!= (<>)和 NOT IN 在某些情況下可以:

explain select * from user where id not in (1) 
explain select * from user where id <> 1

5.索引不會包含有NULL值的列

隻要列中包含有NULL值都将不會被包含在索引中，複合索引中隻要有一列含有NULL值，那麼這一列對于此複合索引就是無效的。是以我們在資料庫設計時不要讓字段的預設值為NULL。

6，排序的索引問題

MySQL查詢隻使用一個索引，是以如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。是以資料庫預設排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列建立複合索引。

注意一個 SQL 語句是否使用索引，跟資料庫版本、資料量、資料選擇度都有關系。

其實，用不用索引，最終都是優化器說了算。

優化器是基于什麼的優化器?

基于 cost 開銷(Cost Base Optimizer)，它不是基于規則(Rule-Based Optimizer)，也不是基于語義。怎麼樣開銷小就怎麼來。

以上是我對索引相關知識的整理，希望你能有所收獲，參考如下！

MySQL索引從基礎到原理，看這一篇就夠了一、索引基礎二、索引存儲資料結構三、聚簇索引和非聚簇索引四、索引使用原則五、 索引建立使用總結