關于資料庫索引，必須掌握的知識點-有解無憂

MySQL的索引是資料庫非常重要的知識點，這些知識點你都掌握了嗎？如果有幫到你可以點贊收藏呦，

推薦閱讀：這些必會的計算機網路知識點你都掌握了嗎

什么是索引？
索引的優缺點？
索引的資料結構？
Hash索引和B+樹的區別？
索引的型別有哪些?
索引的種類有哪些？
B樹和B+樹的區別？
資料庫為什么使用B+樹而不是B樹？
什么是聚簇索引，什么是非聚簇索引？
非聚簇索引一定會進行回表查詢嗎？
索引的使用場景有哪些？
索引的設計原則？
如何對索引進行優化？
如何創建/洗掉索引？
使用索引查詢時性能一定會提升嗎？
什么是前綴索引？
什么是最左匹配原則？
索引在什么情況下會失效？

什么是索引？

百度百科的解釋：索引是對資料庫表的一列或者多列的值進行排序一種結構，使用索引可以快速訪問資料表中的特定資訊，

索引的優缺點？

優點：

大大加快資料檢索的速度，
將隨機I/O變成順序I/O(因為B+樹的葉子節點是連接在一起的)
加速表與表之間的連接

缺點：

從空間角度考慮，建立索引需要占用物理空間
從時間角度考慮，創建和維護索引都需要花費時間，例如對資料進行增刪改的時候都需要維護索引，

索引的資料結構？

索引的資料結構主要有B+樹和哈希表，對應的索引分別為B+樹索引和哈希索引，InnoDB引擎的索引型別有B+樹索引和哈希索引，默認的索引型別為B+樹索引，

B+樹索引

熟悉資料結構的同學都知道，B+樹、平衡二叉樹、紅黑樹都是經典的資料結構，在B+樹中，所有的記錄節點都是按照鍵值大小的順序放在葉子節點上，如下圖，

在這里插入圖片描述

從上圖可以看出，因為B+樹具有有序性，并且所有的資料都存放在葉子節點，所以查找的效率非常高，并且支持排序和范圍查找，

B+樹的索引又可以分為主索引和輔助索引，其中主索引為聚簇索引，輔助索引為非聚簇索引，聚簇索引是以主鍵作為B+ 樹索引的鍵值所構成的B+樹索引，聚簇索引的葉子節點存盤著完整的資料記錄；非聚簇索引是以非主鍵的列作為B+樹索引的鍵值所構成的B+樹索引，非聚簇索引的葉子節點存盤著主鍵值，所以使用非聚簇索引進行查詢時，會先找到主鍵值，然后到根據聚簇索引找到主鍵對應的資料域，上圖中葉子節點存盤的是資料記錄，為聚簇索引的結構圖，非聚簇索引的結構圖如下：

在這里插入圖片描述

上圖中的字母為資料的非主鍵的列值，假設要查詢該列值為B的資訊，則需先找到主鍵7，在到聚簇索引中查詢主鍵7所對應的資料域，

哈希索引

哈希索引是基于哈希表實作的，對于每一行資料，存盤引擎會對索引列通過哈希演算法進行哈希計算得到哈希碼，并且哈希演算法要盡量保證不同的列值計算出的哈希碼值是不同的，將哈希碼的值作為哈希表的key值，將指向資料行的指標作為哈希表的value值，這樣查找一個資料的時間復雜度就是o(1)，一般多用于精確查找，

Hash索引和B+樹的區別？

因為兩者資料結構上的差異導致它們的使用場景也不同，哈希索引一般多用于精確的等值查找，B+索引則多用于除了精確的等值查找外的其他查找，在大多數情況下，會選擇使用B+樹索引，

哈希索引不支持排序，因為哈希表是無序的，
哈希索引不支持范圍查找，
哈希索引不支持模糊查詢及多列索引的最左前綴匹配，
因為哈希表中會存在哈希沖突，所以哈希索引的性能是不穩定的，而B+樹索引的性能是相對穩定的，每次查詢都是從根節點到葉子節點

索引的型別有哪些?

MySQL主要的索引型別主要有FULLTEXT，HASH，BTREE，RTREE，

FULLTEXT

FULLTEXT即全文索引，MyISAM存盤引擎和InnoDB存盤引擎在MySQL5.6.4以上版本支持全文索引，一般用于查找文本中的關鍵字，而不是直接比較是否相等，多在CHAR，VARCHAR，TAXT等資料型別上創建全文索引，全文索引主要是用來解決WHERE name LIKE "%zhang%"等針對文本的模糊查詢效率低的問題，
HASH

HASH即哈希索引，哈希索引多用于等值查詢，時間復雜夫為o(1)，效率非常高，但不支持排序、范圍查詢及模糊查詢等，
BTREE

BTREE即B+樹索引，INnoDB存盤引擎默認的索引，支持排序、分組、范圍查詢、模糊查詢等，并且性能穩定，
RTREE

RTREE即空間資料索引，多用于地理資料的存盤，相比于其他索引，空間資料索引的優勢在于范圍查找

索引的種類有哪些？

主鍵索引：資料列不允許重復，不能為NULL，一個表只能有一個主鍵索引
組合索引：由多個列值組成的索引，
唯一索引：資料列不允許重復，可以為NULL，索引列的值必須唯一的，如果是組合索引，則列值的組合必須唯一，
全文索引：對文本的內容進行搜索，
普通索引：基本的索引型別，可以為NULL

B樹和B+樹的區別？

B樹和B+樹最主要的區別主要有兩點：

B樹中的內部節點和葉子節點均存放鍵和值，而B+樹的內部節點只有鍵沒有值，葉子節點存放所有的鍵和值，
B＋樹的葉子節點是通過相連在一起的，方便順序檢索，

兩者的結構圖如下，

資料庫為什么使用B+樹而不是B樹？

B樹適用于隨機檢索，而B+樹適用于隨機檢索和順序檢索
B+樹的空間利用率更高，因為B樹每個節點要存盤鍵和值，而B+樹的內部節點只存盤鍵，這樣B+樹的一個節點就可以存盤更多的索引，從而使樹的高度變低，減少了I/O次數，使得資料檢索速度更快，
B+樹的葉子節點都是連接在一起的，所以范圍查找，順序查找更加方便
B+樹的性能更加穩定，因為在B+樹中，每次查詢都是從根節點到葉子節點，而在B樹中，要查詢的值可能不在葉子節點，在內部節點就已經找到，

那在什么情況適合使用B樹呢，因為B樹的內部節點也可以存盤值，所以可以把一些頻繁訪問的值放在距離根節點比較近的地方，這樣就可以提高查詢效率，綜上所述，B+樹的性能更加適合作為資料庫的索引，

什么是聚簇索引，什么是非聚簇索引？

聚簇索引和非聚簇索引最主要的區別是資料和索引是否分開存盤，

聚簇索引：將資料和索引放到一起存盤，索引結構的葉子節點保留了資料行，
非聚簇索引：將資料進和索引分開存盤，索引葉子節點存盤的是指向資料行的地址，

在InnoDB存盤引擎中，默認的索引為B+樹索引，利用主鍵創建的索引為主索引，也是聚簇索引，在主索引之上創建的索引為輔助索引，也是非聚簇索引，為什么說輔助索引是在主索引之上創建的呢，因為輔助索引中的葉子節點存盤的是主鍵，

在MyISAM存盤引擎中，默認的索引也是B+樹索引，但主索引和輔助索引都是非聚簇索引，也就是說索引結構的葉子節點存盤的都是一個指向資料行的地址，并且使用輔助索引檢索無需訪問主鍵的索引，

可以從非常經典的兩張圖看看它們的區別(圖片來源于網路)：

在這里插入圖片描述

非聚簇索引一定會進行回表查詢嗎？

上面是說了非聚簇索引的葉子節點存盤的是主鍵，也就是說要先通過非聚簇索引找到主鍵，再通過聚簇索引找到主鍵所對應的資料，后面這個再通過聚簇索引找到主鍵對應的資料的程序就是回表查詢，那么非聚簇索引就一定會進行回表查詢嗎？

答案是不一定的，這里涉及到一個索引覆寫的問題，如果查詢的資料再輔助索引上完全能獲取到便不需要回表查詢，例如有一張表存盤著個人資訊包括id、name、age等欄位，假設聚簇索引是以ID為鍵值構建的索引，非聚簇索引是以name為鍵值構建的索引，select id,name from user where name = 'zhangsan';這個查詢便不需要進行回表查詢因為，通過非聚簇索引已經能全部檢索出資料，這就是索引覆寫的情況，如果查詢陳述句是這樣，select id,name,age from user where name = 'zhangsan';則需要進行回表查詢，因為通過非聚簇索引不能檢索出age的值，那應該如何解決那呢？只需要將索引覆寫即可，建立age和name的聯合索引再使用select id,name,age from user where name = 'zhangsan';進行查詢即可，

所以通過索引覆寫能解決非聚簇索引回表查詢的問題，

索引的使用場景有哪些？

對于中大型表建立索引非常有效，對于非常小的表，一般全部表掃描速度更快些，
對于超大型的表，建立和維護索引的代價也會變高，這時可以考慮磁區技術，
如何表的增刪改非常多，而查詢需求非常少的話，那就沒有必要建立索引了，因為維護索引也是需要代價的，
一般不會出現再where條件中的欄位就沒有必要建立索引了，
多個欄位經常被查詢的話可以考慮聯合索引，
欄位多且欄位值沒有重復的時候考慮唯一索引，
欄位多且有重復的時候考慮普通索引，

索引的設計原則？

最適合索引的列是在where后面出現的列或者連接句子中指定的列，而不是出現在SELECT關鍵字后面的選擇串列中的列，
索引列的基數越大，索引的效果越好，換句話說就是索引列的區分度越高，索引的效果越好，比如使用性別這種區分度很低的列作為索引，效果就會很差，因為列的基數最多也就是三種，大多不是男性就是女性，
盡量使用短索引，對于較長的字串進行索引時應該指定一個較短的前綴長度，因為較小的索引涉及到的磁盤I/O較少，并且索引高速快取中的塊可以容納更多的鍵值，會使得查詢速度更快，
盡量利用最左前綴，
不要過度索引，每個索引都需要額外的物理空間，維護也需要花費時間，所以索引不是越多越好，

如何對索引進行優化？

對索引的優化其實最關鍵的就是要符合索引的設計原則和應用場景，將不符合要求的索引優化成符合索引設計原則和應用場景的索引，

除了索引的設計原則和應用場景那幾點外，還可以從以下兩方面考慮，

在進行查詢時，索引列不能是運算式的一部分，也不能是函式的引數，因為這樣無法使用索引，例如select * from table_name where a + 1 = 2
將區分度最高的索引放在前面
盡量少使用select*

索引的使用場景、索引的設計原則和如何對索引進行優化可以看成一個問題，

如何創建/洗掉索引？

創建索引：

使用CREATE INDEX 陳述句

CREATE INDEX index_name ON table_name (column_list);

在CREATE TABLE時創建

	CREATE TABLE user(
	id INT PRIMARY KEY,
	information text,
	FULLTEXT KEY (information)
);

使用ALTER TABLE創建索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

洗掉索引：

洗掉主鍵索引

alter table 表名 drop primary key
洗掉其他索引

alter table 表名 drop key 索引名

使用索引查詢時性能一定會提升嗎？

不一定，前面在索引的使用場景和索引的設計原則中已經提到了如何合理地使用索引，因為創建和維護索引需要花費空間和時間上的代價，如果不合理地使用索引反而會使查詢性能下降，

什么是前綴索引？

前綴索引是指對文本或者字串的前幾個字符建立索引，這樣索引的長度更短，查詢速度更快，

使用場景：前綴的區分度比較高的情況下，

建立前綴索引的方式

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

這里面有個prefix_length引數很難確定，這個引數就是前綴長度的意思，通常可以使用以下方法進行確定，先計算全列的區分度

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

然后在計算前綴長度為多少時和全列的區分度最相似，

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

不斷地調整prefix_length的值，直到和全列計算出區分度相近，

什么是最左匹配原則？

最左匹配原則：從最左邊為起點開始連續匹配，遇到范圍查詢（<、>、between、like）會停止匹配，

例如建立索引(a,b,c)，大家可以猜測以下幾種情況是否用到了索引，

第一種
```
select * from table_name where a = 1 and b = 2 and c = 3 
select * from table_name where b = 2 and a = 1 and c = 3
```
上面兩次查詢程序中所有值都用到了索引，where后面欄位調換不會影響查詢結果，因為MySQL中的優化器會自動優化查詢順序，

第二種

select * from table_name where a = 1
select * from table_name where a = 1 and b = 2  
select * from table_name where a = 1 and b = 2 and c = 3

答案是三個查詢陳述句都用到了索引，因為三個陳述句都是從最左開始匹配的，

第三種
```
select * from table_name where  b = 1 
select * from table_name where  b = 1 and c = 2 
```
答案是這兩個查詢陳述句都沒有用到索引，因為不是從最左邊開始匹配的
第四種
```
select * from table_name where a = 1 and c = 2 
```
這個查詢陳述句只有a列用到了索引，c列沒有用到索引，因為中間跳過了b列，不是從最左開始連續匹配的，
第五種
```
select * from table_name where  a = 1 and b < 3 and c < 1
```
這個查詢中只有a列和b列使用到了索引，而c列沒有使用索引，因為根據最左匹配查詢原則，遇到范圍查詢會停止，

第六種

select * from table_name where a like 'ab%'; 
select * from table_name where  a like '%ab'
select * from table_name where  a like '%ab%'

對于列為字串的情況，只有前綴匹配可以使用索引，中綴匹配和后綴匹配只能進行全表掃描，

索引在什么情況下會失效？

在上面介紹了幾種不符合最左匹配原則的情況會導致索引失效，除此之外，以下這幾種情況也會導致索引失效，

條件中有or，例如select * from table_name where a = 1 or b = 3
在索引上進行計算會導致索引失效，例如select * from table_name where a + 1 = 2
在索引的型別上進行資料型別的隱形轉換，會導致索引失效，例如字串一定要加引號，假設 select * from table_name where a = '1' 會使用到索引，如果寫成select * from table_name where a = 1 則會導致索引失效，
在索引中使用函式會導致索引失效，例如select * from table_name where abs(a) = 1
在使用like查詢時以%開頭會導致索引失效
索引上使用！、=、<>進行判斷時會導致索引失效，例如select * from table_name where a != 1
索引欄位上使用 is null/is not null判斷時會導致索引失效，例如select * from table_name where a is null

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/242302.html

標籤：MySQL

上一篇：淺談安卓逆向協議- 抖音 - 設備注冊

下一篇：SQL學習筆記系列（一）SQL概述