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索引所有知識點

Tags: