[TOC] 本文轉載至：https://github.com/Snailclimb/JavaGuide/blob/master/docs/database/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B4%A2%E5%BC%95.md ## 什么是索引? **索引是一種用于快速查詢和檢索數據的數據結構。常見的索引結構有: B 樹， B+樹和 Hash。** 索引的作用就相當于目錄的作用。打個比方: 我們在查字典的時候，如果沒有目錄，那我們就只能一頁一頁的去找我們需要查的那個字，速度很慢。如果有目錄了，我們只需要先去目錄里查找字的位置，然后直接翻到那一頁就行了。 ## 為什么要用索引?索引的優缺點分析 ### 索引的優點 **可以大大加快 數據的檢索速度（大大減少的檢索的數據量）, 這也是創建索引的最主要的原因。畢竟大部分系統的讀請求總是大于寫請求的。** 另外，通過創建唯一性索引，可以保證數據庫表中每一行數據的唯一性。 ### 索引的缺點 1. **創建索引和維護索引需要耗費許多時間**：當對表中的數據進行增刪改的時候，如果數據有索引，那么索引也需要動態的修改，會降低 SQL 執行效率。 2. **占用物理存儲空間** ：索引需要使用物理文件存儲，也會耗費一定空間。 ## B 樹和 B+樹區別 - B 樹的所有節點既存放 鍵(key) 也存放 數據(data);而 B+樹只有葉子節點存放 key 和 data，其他內節點只存放 key。 - B 樹的葉子節點都是獨立的;B+樹的葉子節點有一條引用鏈指向與它相鄰的葉子節點。 - B 樹的檢索的過程相當于對范圍內的每個節點的關鍵字做二分查找，可能還沒有到達葉子節點，檢索就結束了。而 B+樹的檢索效率就很穩定了，任何查找都是從根節點到葉子節點的過程，葉子節點的順序檢索很明顯。  ## Hash 索引和 B+樹索引優劣分析 **Hash 索引定位快** Hash 索引指的就是 Hash 表，最大的優點就是能夠在很短的時間內，根據 Hash 函數定位到數據所在的位置，這是 B+樹所不能比的。 **Hash 沖突問題** 知道 HashMap 或 HashTable 的同學，相信都知道它們最大的缺點就是 Hash 沖突了。不過對于數據庫來說這還不算最大的缺點。 **Hash 索引不支持順序和范圍查詢(Hash 索引不支持順序和范圍查詢是它最大的缺點。** 試想一種情況: ```text SELECT * FROM tb1 WHERE id < 500; ``` B+樹是有序的，在這種范圍查詢中，優勢非常大，直接遍歷比 500 小的葉子節點就夠了。而 Hash 索引是根據 hash 算法來定位的，難不成還要把 1 - 499 的數據，每個都進行一次 hash 計算來定位嗎?這就是 Hash 最大的缺點了。 --- ## 索引類型 ### 主鍵索引(Primary Key) **數據表的主鍵列使用的就是主鍵索引。** **一張數據表有只能有一個主鍵，并且主鍵不能為 null，不能重復。** **在 mysql 的 InnoDB 的表中，當沒有顯示的指定表的主鍵時，InnoDB 會自動先檢查表中是否有唯一索引的字段，如果有，則選擇該字段為默認的主鍵，否則 InnoDB 將會自動創建一個 6Byte 的自增主鍵。** ### 二級索引(輔助索引) **二級索引又稱為輔助索引，是因為二級索引的葉子節點存儲的數據是主鍵。也就是說，通過二級索引，可以定位主鍵的位置。** 唯一索引，普通索引，前綴索引等索引屬于二級索引。 **PS:不懂的同學可以暫存疑，慢慢往下看，后面會有答案的，也可以自行搜索。** 1. **唯一索引(Unique Key)** ：唯一索引也是一種約束。**唯一索引的屬性列不能出現重復的數據，但是允許數據為 NULL，一張表允許創建多個唯一索引。** 建立唯一索引的目的大部分時候都是為了該屬性列的數據的唯一性，而不是為了查詢效率。 2. **普通索引(Index)** ：**普通索引的唯一作用就是為了快速查詢數據，一張表允許創建多個普通索引，并允許數據重復和 NULL。** 3. **前綴索引(Prefix)** ：前綴索引只適用于字符串類型的數據。前綴索引是對文本的前幾個字符創建索引，相比普通索引建立的數據更小， 因為只取前幾個字符。 4. **全文索引(Full Text)** ：全文索引主要是為了檢索大文本數據中的關鍵字的信息，是目前搜索引擎數據庫使用的一種技術。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。 二級索引: .png>) ## 聚集索引與非聚集索引 ### 聚集索引 **聚集索引即索引結構和數據一起存放的索引。主鍵索引屬于聚集索引。** 在 Mysql 中，InnoDB 引擎的表的 `.ibd`文件就包含了該表的索引和數據，對于 InnoDB 引擎表來說，該表的索引(B+樹)的每個非葉子節點存儲索引，葉子節點存儲索引和索引對應的數據。 #### 聚集索引的優點 聚集索引的查詢速度非常的快，因為整個 B+樹本身就是一顆多叉平衡樹，葉子節點也都是有序的，定位到索引的節點，就相當于定位到了數據。 #### 聚集索引的缺點 1. **依賴于有序的數據** ：因為 B+樹是多路平衡樹，如果索引的數據不是有序的，那么就需要在插入時排序，如果數據是整型還好，否則類似于字符串或 UUID 這種又長又難比較的數據，插入或查找的速度肯定比較慢。 2. **更新代價大** ： 如果對索引列的數據被修改時，那么對應的索引也將會被修改， 而且況聚集索引的葉子節點還存放著數據，修改代價肯定是較大的， 所以對于主鍵索引來說，主鍵一般都是不可被修改的。 ### 非聚集索引 **非聚集索引即索引結構和數據分開存放的索引。** **二級索引屬于非聚集索引。** > MYISAM 引擎的表的.MYI 文件包含了表的索引， > 該表的索引(B+樹)的每個葉子非葉子節點存儲索引， > 葉子節點存儲索引和索引對應數據的指針，指向.MYD 文件的數據。 > > **非聚集索引的葉子節點并不一定存放數據的指針， > 因為二級索引的葉子節點就存放的是主鍵，根據主鍵再回表查數據。** #### 非聚集索引的優點 **更新代價比聚集索引要小** 。非聚集索引的更新代價就沒有聚集索引那么大了，非聚集索引的葉子節點是不存放數據的 #### 非聚集索引的缺點 1. 跟聚集索引一樣，非聚集索引也依賴于有序的數據 2. **可能會二次查詢(回表)** :這應該是非聚集索引最大的缺點了。 當查到索引對應的指針或主鍵后，可能還需要根據指針或主鍵再到數據文件或表中查詢。 這是 Mysql 的表的文件截圖:  聚集索引和非聚集索引:  ### 非聚集索引一定回表查詢嗎(覆蓋索引)? **非聚集索引不一定回表查詢。** > 試想一種情況，用戶準備使用 SQL 查詢用戶名，而用戶名字段正好建立了索引。 ```text SELECT name FROM table WHERE name='guang19'; ``` > 那么這個索引的 key 本身就是 name，查到對應的 name 直接返回就行了，無需回表查詢。 **即使是 MYISAM 也是這樣，雖然 MYISAM 的主鍵索引確實需要回表， 因為它的主鍵索引的葉子節點存放的是指針。但是如果 SQL 查的就是主鍵呢?** ```text SELECT id FROM table WHERE id=1; ``` 主鍵索引本身的 key 就是主鍵，查到返回就行了。這種情況就稱之為覆蓋索引了。 ## 覆蓋索引 如果一個索引包含（或者說覆蓋）所有需要查詢的字段的值，我們就稱之為“覆蓋索引”。我們知道在 InnoDB 存儲引擎中，如果不是主鍵索引，葉子節點存儲的是主鍵+列值。最終還是要“回表”，也就是要通過主鍵再查找一次。這樣就會比較慢覆蓋索引就是把要查詢出的列和索引是對應的，不做回表操作！ **覆蓋索引即需要查詢的字段正好是索引的字段，那么直接根據該索引，就可以查到數據了， 而無需回表查詢。** > 如主鍵索引，如果一條 SQL 需要查詢主鍵，那么正好根據主鍵索引就可以查到主鍵。 > > 再如普通索引，如果一條 SQL 需要查詢 name，name 字段正好有索引， > 那么直接根據這個索引就可以查到數據，也無需回表。 覆蓋索引:  --- ## 索引創建原則 ### 單列索引 單列索引即由一列屬性組成的索引。 ### 聯合索引(多列索引) 聯合索引即由多列屬性組成索引。 ### 最左前綴原則 假設創建的聯合索引由三個字段組成: ```text ALTER TABLE table ADD INDEX index_name (num,name,age) ``` 那么當查詢的條件有為:num / (num AND name) / (num AND name AND age)時，索引才生效。所以在創建聯合索引時，盡量把查詢最頻繁的那個字段作為最左(第一個)字段。查詢的時候也盡量以這個字段為第一條件。 > 但可能由于版本原因(我的 mysql 版本為 8.0.x),我創建的聯合索引，相當于在聯合索引的每個字段上都創建了相同的索引: .png>) 無論是否符合最左前綴原則，每個字段的索引都生效:  ## 索引創建注意點 ### 最左前綴原則 雖然我目前的 Mysql 版本較高，好像不遵守最左前綴原則，索引也會生效。 但是我們仍應遵守最左前綴原則，以免版本更迭帶來的麻煩。 ### 選擇合適的字段 #### 1.不為 NULL 的字段 索引字段的數據應該盡量不為 NULL，因為對于數據為 NULL 的字段，數據庫較難優化。如果字段頻繁被查詢，但又避免不了為 NULL，建議使用 0,1,true,false 這樣語義較為清晰的短值或短字符作為替代。 #### 2.被頻繁查詢的字段 我們創建索引的字段應該是查詢操作非常頻繁的字段。 #### 3.被作為條件查詢的字段 被作為 WHERE 條件查詢的字段，應該被考慮建立索引。 #### 4.被經常頻繁用于連接的字段 經常用于連接的字段可能是一些外鍵列，對于外鍵列并不一定要建立外鍵，只是說該列涉及到表與表的關系。對于頻繁被連接查詢的字段，可以考慮建立索引，提高多表連接查詢的效率。 ### 不合適創建索引的字段 #### 1.被頻繁更新的字段應該慎重建立索引 雖然索引能帶來查詢上的效率，但是維護索引的成本也是不小的。 如果一個字段不被經常查詢，反而被經常修改，那么就更不應該在這種字段上建立索引了。 #### 2.不被經常查詢的字段沒有必要建立索引 #### 3.盡可能的考慮建立聯合索引而不是單列索引 因為索引是需要占用磁盤空間的，可以簡單理解為每個索引都對應著一顆 B+樹。如果一個表的字段過多，索引過多，那么當這個表的數據達到一個體量后，索引占用的空間也是很多的，且修改索引時，耗費的時間也是較多的。如果是聯合索引，多個字段在一個索引上，那么將會節約很大磁盤空間，且修改數據的操作效率也會提升。 #### 4.注意避免冗余索引 冗余索引指的是索引的功能相同，能夠命中 就肯定能命中 ，那么 就是冗余索引如（name,city ）和（name ）這兩個索引就是冗余索引，能夠命中后者的查詢肯定是能夠命中前者的 在大多數情況下，都應該盡量擴展已有的索引而不是創建新索引。 #### 5.考慮在字符串類型的字段上使用前綴索引代替普通索引 前綴索引僅限于字符串類型，較普通索引會占用更小的空間，所以可以考慮使用前綴索引帶替普通索引。 ### 使用索引一定能提高查詢性能嗎? 大多數情況下，索引查詢都是比全表掃描要快的。但是如果數據庫的數據量不大，那么使用索引也不一定能夠帶來很大提升。