今天整理了一下 MySQL 的索引，一般人只知道3~4個，但我經(jīng)常聽到有10幾個之多，如下圖：

索引

是不是很多都聽過，但是只是想不起來。我大概歸了一下類：

索引分類

密度

按照密度分，可分為稠密索引和稀疏索引，我們常見的MySQL Innodb 引擎使用的是稠密索引。一般列式數(shù)據(jù)庫采用的是稀疏索引，如 Clickhouse。

稠密索引

稠密索引（Dense Index）也稱為完全索引，是一種將每個記錄（行）都映射到一個索引項（Index Entry）的索引方式。這種索引方式對于每一條記錄都會創(chuàng)建一個索引項，因此它可以支持高效的查詢和排序操作。但是，由于每個記錄都需要一個索引項，因此它會占用更多的存儲空間，特別是在大型表中使用時，可能會導致索引文件的過大，從而降低查詢性能。

稀疏索引

稀疏索引（Sparse Index）也稱為部分索引，是一種僅為部分記錄創(chuàng)建索引項的索引方式。它僅為某些值創(chuàng)建索引項，而其他值則沒有索引項。這種索引方式可以減少索引文件的大小，并且可以提高插入和更新的性能。但是，由于它需要進行更多的磁盤訪問來執(zhí)行查詢操作，因此查詢性能可能會受到影響。

稠密索引和稀疏索引

存儲

按照存儲分類，可以分為聚簇索引和非聚簇索引。

聚簇索引

聚簇索引（Clustered Index）是一種索引方式，它將數(shù)據(jù)存儲在磁盤上，并且按照索引的順序進行排序。它可以將相鄰的行存儲在相鄰的磁盤頁上，從而提高查詢的性能。聚簇索引只能為表創(chuàng)建一個索引，因為每個表只能以一種方式進行排序。聚簇索引對于經(jīng)常需要根據(jù)特定列進行查詢的表非常有用，因為它們可以快速定位數(shù)據(jù)。

非聚簇索引

非聚簇索引（Non-Clustered Index）是一種索引方式，它將索引數(shù)據(jù)存儲在單獨的數(shù)據(jù)結(jié)構(gòu)中，而不是存儲在表的磁盤上。它包含了指向表中每行的指針，并按照索引列的順序進行排序。這種索引方式可以為表創(chuàng)建多個索引，并且可以根據(jù)多個列進行排序。非聚簇索引對于經(jīng)常需要根據(jù)不同的列進行查詢的表非常有用，因為它們可以快速定位數(shù)據(jù)。

在使用聚簇索引時，需要注意以下幾點：

• 每個表只能有一個聚簇索引。
• 聚簇索引的排序方式對查詢性能有很大的影響，因此需要仔細選擇聚簇索引的列。
• 聚簇索引的創(chuàng)建和維護可能會占用較多的磁盤空間和時間。

在使用非聚簇索引時，需要注意以下幾點：

• 非聚簇索引可以為表創(chuàng)建多個索引，因此可以根據(jù)多個列進行排序。
• 非聚簇索引可以減少查詢時間，但是需要額外的空間存儲索引數(shù)據(jù)結(jié)構(gòu)。
• 對于經(jīng)常進行插入、更新和刪除操作的表，非聚簇索引需要頻繁更新，因此可能會影響性能。

算法

按照算法分類，可以分為B+樹索引和哈希索引。

B+樹索引

B+樹索引是一種常用的數(shù)據(jù)庫索引結(jié)構(gòu)，它是基于B樹的一種變體。B+樹索引與B樹的區(qū)別在于，它的非葉子節(jié)點不存儲數(shù)據(jù)，只存儲索引，而所有數(shù)據(jù)都存儲在葉子節(jié)點中。這種結(jié)構(gòu)使得B+樹索引的查詢速度更快，因為在進行范圍查詢時，只需要遍歷葉子節(jié)點即可。

B+樹索引的特點如下：

1. 所有數(shù)據(jù)都存儲在葉子節(jié)點中，非葉子節(jié)點只存儲索引，因此可以存儲更多的索引數(shù)據(jù)。
2. 葉子節(jié)點之間形成一個有序的鏈表，方便進行范圍查詢。
3. B+樹索引的深度較淺，因此查詢速度快，同時也減少了磁盤I/O操作的次數(shù)，提高了性能。

B+樹

Hash索引

Hash索引是一種基于哈希表實現(xiàn)的數(shù)據(jù)庫索引結(jié)構(gòu)。在Hash索引中，每個索引項包含兩部分，一個是關(guān)鍵字的哈希值，另一個是指向存儲該關(guān)鍵字的數(shù)據(jù)塊的指針。

Hash索引的查詢速度非常快，因為它通過哈希函數(shù)將關(guān)鍵字轉(zhuǎn)換為固定長度的哈希值，然后根據(jù)哈希值直接訪問索引項。由于哈希值是唯一的，因此可以直接找到存儲數(shù)據(jù)的位置，不需要進行比較操作。

Hash索引的優(yōu)點包括：

1. 查詢速度快，查詢性能穩(wěn)定。
2. 索引構(gòu)建速度快，適合處理大量數(shù)據(jù)。
3. 支持等值查詢，適用于一些需要高速查詢的場景。

Hash索引的缺點包括：

1. 不支持范圍查詢、模糊查詢和排序操作。
2. 哈希函數(shù)的選擇和設(shè)計比較關(guān)鍵，不合適的哈希函數(shù)可能導致哈希沖突，進而影響查詢效率。
3. Hash索引在內(nèi)存中存儲，如果數(shù)據(jù)過大，可能會導致內(nèi)存不足的問題。

Hash索引的使用場景包括：

1. 需要快速查找具有唯一性的數(shù)據(jù)的表，例如用戶表、商品表等。
2. 數(shù)據(jù)量較大、查詢操作較多、更新操作較少的表，例如日志表、統(tǒng)計表等。

Hash索引

優(yōu)化

從優(yōu)化的角度分類，可分為前綴索引、復合索引、覆蓋索引。

前綴索引

前綴索引是一種基于字符串前綴的數(shù)據(jù)庫索引結(jié)構(gòu)。在前綴索引中，對于字符串類型的列，可以只對其前幾個字符建立索引，而不是對整個字符串進行索引。這樣可以大大減小索引的存儲空間，同時也可以提高查詢效率。

例如，對于一個名字列，如果需要進行模糊查詢，可以只對名字的前幾個字符建立索引，這樣可以有效減小索引的存儲空間，同時也可以提高查詢效率。一般來說，前綴索引的長度越短，索引的存儲空間就越小，但是查詢效率也會受到影響。

前綴索引的優(yōu)點包括：

1. 可以減小索引的存儲空間，適用于字符串類型的列。
2. 可以提高查詢效率，特別是在模糊查詢等操作中。

前綴索引的缺點包括：

1. 可能會出現(xiàn)重復的索引項，進而影響查詢效率。
2. 前綴長度的選擇比較關(guān)鍵，如果長度太短，可能會出現(xiàn)大量的哈希沖突，如果長度太長，可能會導致索引的存儲空間變大。

前綴索引的使用場景包括：

1. 需要對字符串類型的列進行模糊查詢、前綴匹配等操作的表。
2. 數(shù)據(jù)量較大、查詢操作較多、更新操作較少的表，例如日志表、統(tǒng)計表等。

前綴索引是一種非常實用的數(shù)據(jù)庫索引結(jié)構(gòu)，可以大大提高查詢效率，減小索引的存儲空間。但是需要注意的是，在選擇前綴長度時需要根據(jù)具體的業(yè)務(wù)需求進行選擇，不合適的前綴長度可能會影響查詢效率。

復合索引

復合索引是一種將多個列組合在一起來創(chuàng)建的索引，用于優(yōu)化查詢中涉及到多個列的查詢效率。相對于單列索引，復合索引可以更有效地支持多列的查詢，提高查詢效率。

在創(chuàng)建復合索引時，可以選擇多個列，并按照順序依次排列。這樣可以使得查詢更加高效，因為復合索引的葉子節(jié)點存儲的是多列的值，可以直接滿足多列查詢的需求，避免了查詢時多次進行索引掃描的開銷。

例如，對于一個包含姓名和年齡的表，可以創(chuàng)建一個復合索引，將這三列按照順序依次排列。這樣，當需要查詢姓名、性別和年齡都滿足一定條件的記錄時，可以直接使用復合索引來完成查詢，避免了多次索引掃描的開銷，從而提高了查詢效率。

復合索引的優(yōu)點包括：

1. 可以提高多列查詢的效率，避免多次索引掃描的開銷。
2. 可以減小索引的存儲空間，適用于需要對多個列進行查詢的表。

復合索引的缺點包括：

1. 復合索引的維護成本比單列索引高，因為需要對多個列進行維護。
2. 對于一些單列查詢，復合索引可能并不適用，因為需要掃描復合索引的葉子節(jié)點，而單列索引可以直接定位到需要的記錄。

復合索引的使用場景包括：

1. 需要對多列進行查詢的表，例如包含姓名和年齡等多個列的表。
2. 查詢操作較多，更新操作較少的表。

復合索引

復合索引是一種非常實用的數(shù)據(jù)庫索引結(jié)構(gòu)，可以提高多列查詢的效率，減小索引的存儲空間。但是需要注意的是，在創(chuàng)建復合索引時需要根據(jù)具體的業(yè)務(wù)需求進行選擇，不合適的復合索引可能會影響查詢效率。

覆蓋索引

覆蓋索引是一種特殊的索引，它包含了所有需要查詢的列的數(shù)據(jù)，而不需要進一步的查找操作就可以直接返回查詢結(jié)果。這種索引也被稱為索引覆蓋或索引包含查詢。

覆蓋索引的原理是在索引結(jié)構(gòu)中包含了查詢所需要的列，因此數(shù)據(jù)庫不需要再到數(shù)據(jù)表中查找所需的列。這樣可以減少磁盤I/O操作，從而提高查詢效率，特別是對于大型的數(shù)據(jù)表和頻繁的查詢操作。

例如，如果有一個包含姓名和年齡的表，并且需要查詢所有男性的姓名和年齡?？梢詣?chuàng)建一個復合索引，按照性別、姓名和年齡的順序依次排列。這樣，查詢時可以直接使用這個復合索引，并且由于該索引包含了查詢所需的姓名和年齡信息，不需要進一步的查找操作就可以直接返回查詢結(jié)果，從而提高了查詢效率。

覆蓋索引的優(yōu)點包括：

1. 可以減少磁盤I/O操作，從而提高查詢效率。
2. 可以減少CPU和內(nèi)存的開銷，特別是對于大型數(shù)據(jù)表和頻繁的查詢操作。

覆蓋索引的缺點包括：

1. 只有當需要查詢的列都在索引中時，才能使用覆蓋索引。否則，仍然需要到數(shù)據(jù)表中查找所需的列。
2. 創(chuàng)建覆蓋索引需要占用更多的磁盤空間。

覆蓋索引的使用場景包括：

1. 需要頻繁進行查詢操作的表。
2. 查詢操作需要使用多個列的信息。

覆蓋索引

覆蓋索引是一種特殊的復合索引，可以減少磁盤I/O操作，提高查詢效率。但是需要注意的是，只有當需要查詢的列都在索引中時才能使用覆蓋索引，否則仍然需要到數(shù)據(jù)表中查找所需的列。因此，在創(chuàng)建覆蓋索引時需要根據(jù)具體的業(yè)務(wù)需求進行選擇。

功能

按照功能的角度劃分，可以分為普通索引、唯一索引、全文索引。

普通索引

普通索引是數(shù)據(jù)庫中最基本的索引結(jié)構(gòu)，也被稱為單列索引或簡單索引。它只包含一個列的值和指向該行的指針，用于加速對該列的單列查詢?？梢詫Ρ淼娜我饬袆?chuàng)建普通索引，但通常建議對經(jīng)常進行查詢和排序的列創(chuàng)建索引，例如主鍵列和外鍵列等。

普通索引的原理是將所需要查詢的列作為索引列，按照索引列的值建立索引。當查詢該列時，數(shù)據(jù)庫系統(tǒng)會先在索引結(jié)構(gòu)中進行查找，然后根據(jù)索引中的指針到數(shù)據(jù)表中找到對應(yīng)的行。由于普通索引只包含一個列的值和指向該行的指針，因此查詢時需要在數(shù)據(jù)表中找到其他所需的列的值。

普通索引的優(yōu)點包括：

1. 可以加速單列查詢的速度，特別是對于大型數(shù)據(jù)表和頻繁的查詢操作。
2. 可以提高數(shù)據(jù)的訪問效率，從而加快數(shù)據(jù)的處理速度。

普通索引的缺點包括：

1. 當需要查詢的列不在索引列中時，需要進行額外的查找操作，從而降低查詢效率。
2. 創(chuàng)建普通索引需要占用額外的磁盤空間和內(nèi)存空間，可能會對寫入操作的性能產(chǎn)生一定的影響。

普通索引的使用場景包括：

1. 經(jīng)常進行查詢和排序操作的列。
2. 需要經(jīng)常進行連接操作的表的外鍵列。
3. 數(shù)據(jù)表中需要保證唯一性的列，如主鍵列等。

普通索引是數(shù)據(jù)庫中最基本的索引結(jié)構(gòu)，用于加速對單列查詢的速度，提高數(shù)據(jù)的訪問效率。在創(chuàng)建普通索引時需要根據(jù)具體的業(yè)務(wù)需求進行選擇，避免對寫入操作的性能產(chǎn)生過大的影響。

唯一索引

唯一索引是一種限制數(shù)據(jù)庫表中列值唯一性的索引，用于保證在指定列上沒有重復的數(shù)據(jù)。與普通索引不同，唯一索引在索引列中的每個值都是唯一的，且不允許插入重復值，包括 NULL 值。

唯一索引的原理與普通索引類似，只是對于唯一索引而言，如果插入或更新操作的列值已經(jīng)存在，數(shù)據(jù)庫系統(tǒng)會拋出一個唯一性沖突的錯誤。唯一索引可以用于加速唯一性約束條件的驗證，從而提高數(shù)據(jù)的訪問效率。

唯一索引的優(yōu)點包括：

1. 可以保證數(shù)據(jù)表中的列值唯一性，避免重復數(shù)據(jù)的插入。
2. 可以提高數(shù)據(jù)的訪問效率，加速唯一性約束條件的驗證。

唯一索引的缺點包括：

1. 創(chuàng)建唯一索引需要占用額外的磁盤空間和內(nèi)存空間，可能會對寫入操作的性能產(chǎn)生一定的影響。
2. 如果需要對多個列進行唯一性約束，需要創(chuàng)建多個唯一索引，會占用更多的磁盤空間和內(nèi)存空間。

唯一索引的使用場景包括：

1. 需要對數(shù)據(jù)表中的某一列或多個列進行唯一性約束的情況。
2. 經(jīng)常進行查詢和排序操作的列需要保證唯一性。

唯一索引是一種用于限制數(shù)據(jù)庫表中列值唯一性的索引，可以保證數(shù)據(jù)表中的列值唯一性，提高數(shù)據(jù)的訪問效率。在創(chuàng)建唯一索引時需要根據(jù)具體的業(yè)務(wù)需求進行選擇，避免對寫入操作的性能產(chǎn)生過大的影響。

全文索引

全文索引是一種基于文本內(nèi)容的索引技術(shù)，可以快速地檢索出包含指定關(guān)鍵詞或短語的文檔或記錄。相比于傳統(tǒng)的索引技術(shù)，全文索引更加適用于文本數(shù)據(jù)的搜索和查詢。

全文索引通常使用倒排索引（Inverted Index）的數(shù)據(jù)結(jié)構(gòu)，將每個單詞或短語出現(xiàn)的位置作為索引項，以便進行快速的查找和匹配。倒排索引可以理解為是一張單詞詞表，每個單詞都指向包含該單詞的文檔或記錄的位置。通過倒排索引，可以快速地找到包含指定單詞的文檔或記錄，以及它們出現(xiàn)的位置。

全文索引的優(yōu)點包括：

1. 可以對文本數(shù)據(jù)進行高效的搜索和查詢，提高數(shù)據(jù)的訪問效率。
2. 可以支持模糊搜索和短語搜索等功能，增強搜索的靈活性和準確性。

全文索引的缺點包括：

1. 創(chuàng)建全文索引需要占用大量的磁盤空間和內(nèi)存空間，可能會對系統(tǒng)的性能產(chǎn)生影響。
2. 全文索引的更新和維護成本較高，需要花費較多的時間和資源。

全文索引的使用場景包括：

1. 需要對大量文本數(shù)據(jù)進行搜索和查詢的情況，如新聞、博客、社交媒體等應(yīng)用。
2. 需要支持模糊搜索和短語搜索等高級搜索功能的情況。

全文索引是一種基于文本內(nèi)容的索引技術(shù)，可以高效地對文本數(shù)據(jù)進行搜索和查詢，支持模糊搜索和短語搜索等高級搜索功能。在使用全文索引時需要考慮到其占用的磁盤空間和內(nèi)存空間，以及更新和維護的成本。

關(guān)系

按照關(guān)系分類，可分為主鍵索引和輔助索引。

主鍵索引

主鍵索引是一種基于數(shù)據(jù)庫表中主鍵的索引技術(shù)，主要用于加速對數(shù)據(jù)庫表中某個記錄的查找和訪問。主鍵是一種唯一標識數(shù)據(jù)庫表中每個記錄的字段或一組字段，每個記錄都應(yīng)該具有不同的主鍵值。

主鍵索引通常使用B+樹等數(shù)據(jù)結(jié)構(gòu)進行實現(xiàn)，以便能夠快速地查找和定位某個記錄。主鍵索引的特點包括：

1. 主鍵索引是一種唯一索引，要求每個記錄的主鍵值都是唯一的。
2. 主鍵索引通常是表中的主鍵字段上創(chuàng)建的索引，可以加快對該字段的查找和訪問速度。
3. 主鍵索引可以用于加速數(shù)據(jù)庫表的連接操作和數(shù)據(jù)的排序操作。

主鍵索引的優(yōu)點包括：

1. 提高了對數(shù)據(jù)庫表中某個記錄的查找和訪問速度，降低了訪問數(shù)據(jù)庫表的成本。
2. 由于主鍵值是唯一的，可以保證每個記錄都能被唯一地標識和訪問。
3. 可以用于加速數(shù)據(jù)庫表的連接操作和數(shù)據(jù)的排序操作，提高了查詢和分析數(shù)據(jù)的效率。

主鍵索引的缺點包括：

1. 主鍵索引只適用于基于主鍵的查詢，對于其他類型的查詢可能會不夠高效。
2. 在插入新記錄或更新主鍵值時，需要重新維護主鍵索引，可能會對數(shù)據(jù)庫性能產(chǎn)生影響。
3. 如果主鍵值不夠唯一或者數(shù)據(jù)量較大，主鍵索引可能會占用較多的磁盤空間和內(nèi)存空間。

主鍵索引是一種基于數(shù)據(jù)庫表中主鍵的索引技術(shù)，可以提高對數(shù)據(jù)庫表中某個記錄的查找和訪問速度，用于加速數(shù)據(jù)庫表的連接操作和數(shù)據(jù)的排序操作。在使用主鍵索引時需要考慮到其對于其他類型的查詢可能不夠高效，以及在插入新記錄或更新主鍵值時需要重新維護主鍵索引的成本。MySQL 主鍵索引還是聚簇索引。

輔助索引

輔助索引，也稱為非聚簇索引，是一種基于數(shù)據(jù)庫表中某個字段或多個字段的索引技術(shù)，用于加速對數(shù)據(jù)庫表的查詢操作。輔助索引與聚簇索引不同，輔助索引不會改變表中數(shù)據(jù)的物理存儲方式。因此除主鍵索引外，其余索引均為輔助索引。