MySQL索引由淺入深

三分惡發表於2021-03-03
MySql索引

索引是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演算法生成一個索引檔案，在查詢資料庫時，找到索引檔案進行遍歷，使用能夠大幅地查詢的效率的折半查詢的方式，找到相應的鍵從而獲取資料。

image-20210302210641120

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樹

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

  • 插入 1、2、3。

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

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

左旋

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

右旋

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

二叉樹儲存索引

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

IO

所以如果每個節點儲存的資料太少，從索引中找到我們需要的資料，就要訪問更多的節點，意味著跟磁碟互動次數就會過多。

那麼解決方案是什麼？

  • 讓每個節點儲存更多的資料。

  • 讓節點上有更多的關鍵字。

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

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

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

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

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

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+樹的具體儲存結構：

B+樹索引

來說一下這張圖的重點：

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

7.1、儲存容量

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

B+樹儲存容量

假設索引欄位是 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)的左閉右開的區間的臨界值，所以會走左邊的子節點，最後在葉子節點上找到了需要的資料。
    1. 如果是範圍查詢，比如要查詢從 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 來說更強(根節點和枝節點不儲存資料區， 所以一個節點可以儲存更多的關鍵字，一次磁碟載入的關鍵字更多)
    1. 排序能力更強(因為葉子節點上有下一個資料區的指標，資料形成了連結串列)
    1. 效率更加穩定(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)，也不是基於語義。怎麼樣開銷小就怎麼來。



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


參考：

【1】：《高效能MySQL》

【2】：MySQL索引原理及慢查詢優化

【3】：極客時間 《MySQL45講》

【4】：MySQL索引背後的資料結構及演算法原理

【5】：MySQL索引原理，一篇從頭到尾講清楚

【6】：Mysql 四種常見的索引

【7】：MySQL探祕(三)MySQL索引原理

【8】：一口氣搞懂MySQL索引所有知識點

相關文章