什么是索引？

在關系數據庫中，索引是一種單獨的、物理的對數據庫表中一列或多列的值進行排序的一種存儲結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數據頁的邏輯指針清單。索引的作用相當于圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。

能實現快速定位數據的一種存儲結構，其設計思想是以空間換時間。

索引的分類

按「數據結構」分類：B+tree索引、Hash索引、Full-text索引。
按「物理存儲」分類：聚簇索引（主鍵索引）、二級索引（輔助索引）。
按「字段特性」分類：主鍵索引、唯一索引、普通索引、前綴索引。
按「字段個數」分類：單列索引、聯合索引。

MySQL如何實現的索引機制

這個話題比較大，在MySQL中有不同的存儲引擎比如像InnoDB MyISAM Memory 等等,每一種存儲引擎在其內部實現索引機制的原理也有所不同。在MySQL5.5之后默認的就是InnoDB，并且是目前使用最廣泛的MySQL數據引擎，那我們就以InnoDB為例展開講講。

?如果說我們在表中有100條數據，而我們要找出我們需要的數據，有哪些辦法？

• ? 我們是不是可以按照一種順序的方式一條一條往下去搜索，直到匹配到我們需要的數據，這是一種方案在時間復雜度上是O(N)，雖說效率差但也能用。
• ? 二分查找法也是一種常用的比較高效的查詢算法，它的搜索效率為O(log(N)),雖說查找效率是比順序查找高了不少，但是它有兩個前提條件，必須用順序存儲結構比如數組，第二個是必須按照關鍵字進行有序排序（從小到大）。
• ? 哈希查找，哈希查找的特性是能夠做到直接定址，其效率無限接近于O(1)，取決于沖突的數量。但是散列表數據是無序存儲的，排序要自己做，第二個是散列表還要擴容耗時長，遇到散列沖突性能不穩定。
• ? B樹/B+樹查找的復雜度是O(log2(N)), 那么這也是InnoDB采用的數據結構，在查找效率上的非常高的，算法具體的原理在后面介紹。

為什么InnoDB要使用B+樹作為索引結構？

InnoDB的索引和MyISAM的索引有什么區別？

首先InnoDB和MyISAM都是使用的B+樹實現的，但是InnoDB使用的是聚簇索引而MyISAM使用的是非聚簇索引，聚簇索引根據主鍵創建一顆B+樹，葉子節點則存放的是數據行記錄，也可以把葉子結點稱為數據頁。通俗點來說就是把數據和索引存在同一個塊，找到了索引也就找到了數據。

• 因為葉子結點將索引和數據放在一起，就決定了聚簇索引的唯一性，一張表里面只能有一個聚簇索引。
• InnoDB引擎默認將主鍵設置為聚簇索引，但如果沒有設置主鍵，那么InnoDB將會選擇非空的唯一索引作為代替，如果沒有這樣的索引，InnoDB將會定一個隱式主鍵作為聚簇索引。
• 因為聚簇索引特殊的物理結構所決定，葉子結點將索引和數據存放在一起，在獲取數據的速度上是比非聚簇索引快的。
• 聚簇索引數據的存儲是有序的，在進行排序查找和范圍查找的速度也是非常快的。
• ?? 也正因為有序性，在數據插入時按照主鍵的順序插入是最快的，否則就會出現頁分裂等問題，嚴重影響性能。對于InnoDB我們一般采用自增作為主鍵ID。
• 第二個問題主鍵最好不要進行更新，修改主鍵的代價非常大，為了保持有序性會導致更新的行移動，一般來說我們通常設置為主鍵不可更新。

?在這部分只介紹InnoDB和MyISAM主鍵索引的不同？輔助索引后面在說

而非聚簇索引是將索引和數據分開存儲，那么在訪問數據的時候就需要2次查找，但是和InnoDB的非聚簇部分還是有所區別。InnoDB是需要查找2次樹，先查找輔助索引樹，再查找聚簇索引樹（這個過程也叫回表）。而MyISAM的主鍵索引葉子結點的存儲的部分還是有所區別。InnoDB中存儲的是索引和聚簇索引ID，但是MyISAM中存儲的是索引和數據行的地址，只要定位就可以獲取到。

其實看到這個部分會有一個疑惑，那就是InnoDB的聚簇索引比MyISAM的主鍵快，那為什么會認為MyISAM查詢效率比InnoDB快呢？

• 第一點，對于兩者存儲引擎的的性能分析不能只看主鍵索引，我們也要看看輔助索引，前頭我們介紹過InnoDB輔助索引會存在一個回表的過程。而MyISAM的輔助索引和主鍵索引的原理是一樣的，并沒有什么區別。
• (重點) InnoDB對MVCC的支持，事物是比較影響性能的，就算你沒用但是也省不了檢查和維護，而MyISAM這塊卻沒有這方面的影響，具體MVCC詳解將在后面章節描述。

如果一個表沒有主鍵索引那還會創建B+樹嗎？

答案是會的！！！

InnoDB是MySQL中的一種存儲引擎，它會為每個表創建一個主鍵索引。如果表沒有明確的主鍵索引，InnoDB會使用一個隱藏的、自動生成的主鍵來創建索引。這個隱藏的主鍵索引使用的就是B+樹結構。因此，在InnoDB中，即使表沒有明確的主鍵索引，也會創建一個B+樹索引。

索引的優缺點是什么？

數據是存儲在磁盤上的，操作系統讀取磁盤的最小單位是塊，如果沒有索引，會加載所有的數據到內存，依次進行檢索，加載的總數據會很多，磁盤IO多。

如果有了索引，會以學號為key創建索引，MySQL采用B+樹結構存儲，一方面加載的數據只有學號和主鍵ID，另一方便采用了多叉平衡樹，定位到指定學號會很快，根據關聯的ID可以快速定位到對應行的數據，所以檢索的速度會很快，因為加載的總數據很少，磁盤IO少。

可見，索引可以大大減少檢索數據的范圍、減少磁盤IO，使查詢速度很快，因為磁盤IO是很慢的，是由它的硬件結構決定的。

? 優點

• 索引能夠提高數據檢索的效率，降低數據庫的IO成本。
• 通過創建唯一性索引，可以保證數據庫表中每一行數據的唯一性，創建唯一索引
• 在使用分組和排序子句進行數據檢索時，同樣可以顯著減少查詢中分組和排序的時間
• 加速兩個表之間的連接，一般是在外鍵上創建索引

? 缺點

• 需要占用物理空間,建立的索引越多需要的空間越大
• 創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加
• 會降低表的增刪改的效率，因為每次增刪改索引需要進行動態維護，導致時間變長

使用索引一定能提升效率嗎？（什么時候適合創建索引，什么時候不適合創建索引？）

答案是不一定，任何事物我們都應該辯證的看，知道其運行邏輯從而利用其優點，盡量避開它的缺點。在上面我們已經和大家介紹了過了索引帶來的優缺點，那接下來就和大家分享幾個建索引的提示。

• 對于查詢中使用的少的字段盡量不要創建索引，創建索引是有成本的，空間占用、創建和維護成本、增刪改效率降低。
• 對于數據密度小的列也不建議創建索引，因為InnoDB中索引的B+樹所決定的，你能帶來的效率提升非常有限。（但是也有例外，舉個例子枚舉值（1，2，3），頭兩個占比百分之1%，第三個占比99%，并且頭兩個搜索占比比第三個高很多，那么是可以建議加索引的）。InnoDB的輔助索引是存在回表的，如果數據密度過小，那么性能可能還不如全表掃。像上面這種場景具有特殊性，也說明一個道理，在大多數場景下建議可能適用，但是也有不適用的時候，我們不要把這種建議當作鐵律。

如何查看一個表的索引？

?? 上代碼 ??

show index from table_name (表名)

有哪些情況會導致索引失效？

這個問題要分版本回答！！！版本不同可能會導致索引失效的場景也不同，直接給答案的都是耍流氓！！！

這里回答基于最新MySQL8版本，MySQL8失效的以前版本也失效，MySQL8不失效的，以前可能會失效。

• 使用like并且是左邊帶%, 右邊可以帶會走索引（但是并不絕對，詳細解釋看下面like專題分析）
• 隱式類型轉換，索引字段與條件或關聯字段的類型不一致。（比如你的字段是int，你用字符串方式去查詢會導致索引失效）。
• 在where條件里面對索引列使用運算或者使用函數。
• 使用OR且存在非索引列
• 在where條件中兩列做比較會導致索引失效
• 使用IN可能不會走索引（MySQL環境變量eq_range_index_dive_limit的值對IN語法有很大影響，該參數表示使用索引情況下IN中參數的最大數量。MySQL 5.7.3以及之前的版本中，eq_range_index_dive_limit的默認值為10，之后的版本默認值為200。我們拿MySQL8.0.19舉例，eq_range_index_dive_limit=200表示當IN (...)中的值 >200個時，該查詢一定不會走索引。<=200則可能用到索引。）
• 使用非主鍵范圍條件查詢時，部分情況索引失效 。
• 使用order by可能會導致索引失效
• is null is not null ≠ 可能會導致索引失效

如果表中有字段為NULL 索引是否會失效？

首先講答案不一定。即使我們使用is null 或者is not null 它其實都是會走索引的。那為什么會有這樣的言論呢？這里首先就得來講講NULL值是怎么在記錄中存儲的，又是怎么在B+樹中存儲的呢。

那么在InnoDB中分為聚簇索引和非聚簇索引兩種，聚簇索引本身是不允許記錄為空的，所以可以不不用考慮，那么就剩下非聚簇索引也就是我們的輔助索引。

那既然IS NULL、IS NOT NULL、!=這些條件都可能使用到索引，那到底什么時候索引，什么時候采用全表掃描呢？

首先我們得知道兩個東西，第一個在InnoDB引擎是如何存儲NULL值的，第二個問題是索引是如何存儲NULL值的，這樣我們才能從根上理解NULL在什么場景走索引，在什么場景不走索引。

1?? 在InnoDB引擎是如何存儲NULL值的？

InnoDB引擎通過使用一個特殊的值來表示null，這個值通常被稱為"null bitmap"。null bitmap是一個二進制位序列，用來標記表中每一個列是否為null。當null bitmap中對應的位為1時，表示對應的列為null；當null bitmap中對應的位為0時，表示對應的列不為null。在實際存儲時，InnoDB引擎會將null bitmap作為行記錄的一部分，存儲在行記錄的開頭，這樣可以在讀取行記錄時快速判斷每個列是否為null。

從頭開始說理解起來會比較容易，理解了獨占表空間文件就更容易理解行格式了，接著往下看：

當我們創建表的時候默認會創建一個*.idb 文件，這個文件又稱為獨占表空間文件，它是由段、區、頁、行組成。InnoDB存儲引擎獨占表空間大致如下圖；

Segment(表空間) 是由各個段（segment）組成的，段是由多個區（extent）組成的。段一般分為數據段、索引段和回滾段等。

• 數據段 存放 B + 樹的葉子節點的區的集合
• 索引段 存放 B + 樹的非葉子節點的區的集合
• 回滾段 存放的是回滾數據的區的集合， MVCC就是利用了回滾段實現了多版本查詢數據

Extent(區) 在表中數據量大的時候，為某個索引分配空間的時候就不再按照頁為單位分配了，而是按照區（extent）為單位分配。每個區的大小為 1MB，對于 16KB 的頁來說，連續的 64 個頁會被劃為一個區，這樣就使得鏈表中相鄰的頁的物理位置也相鄰，就能使用順序 I/O 了 。

（我們知道 InnoDB 存儲引擎是用 B+ 樹來組織數據的。B+ 樹中每一層都是通過雙向鏈表連接起來的，如果是以頁為單位來分配存儲空間，那么鏈表中相鄰的兩個頁之間的物理位置并不是連續的，可能離得非常遠，那么磁盤查詢時就會有大量的隨機I/O，隨機 I/O 是非常慢的。解決這個問題也很簡單，就是讓鏈表中相鄰的頁的物理位置也相鄰，這樣就可以使用順序 I/O 了，那么在范圍查詢（掃描葉子節點）的時候性能就會很高。）

Page(頁) 記錄是按照行來存儲的，但是數據庫的讀取并不以「行」為單位，否則一次讀取（也就是一次 I/O 操作）只能處理一行數據，效率會非常低。

因此，InnoDB 的數據是按「頁」為單位來讀寫的，也就是說，當需要讀一條記錄的時候，并不是將這個行記錄從磁盤讀出來，而是以頁為單位，將其整體讀入內存。

默認每個頁的大小為 16KB，也就是最多能保證 16KB 的連續存儲空間。

頁是 InnoDB 存儲引擎磁盤管理的最小單元，意味著數據庫每次讀寫都是以 16KB 為單位的，一次最少從磁盤中讀取 16K 的內容到內存中，一次最少把內存中的 16K 內容刷新到磁盤中。

頁的類型有很多，常見的有數據頁、undo 日志頁、溢出頁等等。數據表中的行記錄是用「數據頁」來管理的，數據頁的結構這里我就不講細說了，總之知道表中的記錄存儲在「數據頁」里面就行。

Row(行) 數據庫表中的記錄都是按行（row）進行存放的，每行記錄根據不同的行格式，有不同的存儲結構。

重點來了！！！

InnoDB 提供了 4 種行格式，分別是 Redundant、Compact、Dynamic和 Compressed 行格式。

• Redundant 是很古老的行格式了， MySQL 5.0 版本之前用的行格式，現在基本沒人用了，那就不展開詳講了。
• MySQL 5.0 之后引入了 Compact 行記錄存儲方式，由于 Redundant 不是一種緊湊的行格式，而采用更為緊湊的Compact ，設計的初衷就是為了讓一個數據頁中可以存放更多的行記錄，從 MySQL 5.1 版本之后，行格式默認設置成 Compact。
• Dynamic 和 Compressed 兩個都是緊湊的行格式，它們的行格式都和 Compact 差不多，因為都是基于 Compact 改進一點東西。從 MySQL5.7 版本之后，默認使用 Dynamic 行格式。

那么我們來看看Compact里面長什么樣，先混個臉熟。

這里簡單介紹一下，Compact行格式其他內容后面單獨出一個章節介紹。

• NULL值列表（本問題介紹重點）

• 表中的某些列可能會存儲 NULL 值，如果把這些 NULL 值都放到記錄的真實數據中會比較浪費空間，所以 Compact 行格式把這些值為 NULL 的列存儲到 NULL值列表中。如果存在允許 NULL 值的列，則每個列對應一個二進制位（bit），二進制位按照列的順序逆序排列。
• 二進制位的值為1時，代表該列的值為NULL。二進制位的值為0時，代表該列的值不為NULL。另外，NULL 值列表必須用整數個字節的位表示（1字節8位），如果使用的二進制位個數不足整數個字節，則在字節的高位補 0。
• 當然NULL 值列表也不是必須的。當數據表的字段都定義成 NOT NULL 的時候，這時候表里的行格式就不會有 NULL 值列表了。所以在設計數據庫表的時候，通常都是建議將字段設置為 NOT NULL，這樣可以節省 1 字節的空間（NULL 值列表占用 1 字節空間）。
• 「NULL 值列表」的空間不是固定 1 字節的。當一條記錄有 9 個字段值都是 NULL，那么就會創建 2 字節空間的「NULL 值列表」，以此類推。

2?? 索引是如何存儲NULL值的？

我們知道InnoDB引擎中按照物理存儲的不同分為聚簇索引和非聚簇索引，聚簇索引也就是主鍵索引，那么是不允許為空的。那就不再我們本問題的討論范圍，我們重點來看看非聚簇索引，非聚簇索引是允許值為空的。

在InnoDB中非聚簇索引是通過B+樹的方式進行存儲的

從圖中可以看出，對于s1表的二級索引idx_key1來說，值為NULL的二級索引記錄都被放在了B+樹的最左邊，這是因為設計InnoDB的大叔有這樣的規定：

We define the SQL null to be the smallest possible value of a field.

也就是說他們把SQL中的NULL值認為是列中最小的值。在通過二級索引idx_key1對應的B+樹快速定位到葉子節點中符合條件的最左邊的那條記錄后，也就是本例中id值為521的那條記錄之后，就可以順著每條記錄都有的next_record屬性沿著由記錄組成的單向鏈表去獲取記錄了，直到某條記錄的key1列不為NULL。

3?? 我們了解了上面的兩個問題之后，我們就可以來看看，使不使用索引的依據是什么了

實際上來說我們用is null is not null ≠ 這些條件都是能走索引的，那什么時候走索引什么時候走全表掃描呢？

總結起來就是兩個字：成本！！！

如何去度量成本計算使用某個索引執行查詢的成本就非常復雜了，展開講這個話題就停不下來了，后面考慮單獨列一個篇幅去講。

這里總結性講講：第一個，讀取二級索引記錄的成本，第二，將二級索引記錄執行回表操作，也就是到聚簇索引中找到完整的用戶記錄操作所付出的成本。

要掃描的二級索引記錄條數越多，那么需要執行的回表操作的次數也就越多，達到了某個比例時，使用二級索引執行查詢的成本也就超過了全表掃描的成本（舉一個極端的例子，比方說要掃描的全部的二級索引記錄，那就要對每條記錄執行一遍回表操作，自然不如直接掃描聚簇索引來的快）

所以MySQL優化器在真正執行查詢之前，對于每個可能使用到的索引來說，都會預先計算一下需要掃描的二級索引記錄的數量，比方說對于下邊這個查詢：

SELECT * FROM s1 WHERE key1 IS NULL;

優化器會分析出此查詢只需要查找key1值為NULL的記錄，然后訪問一下二級索引idx_key1，看一下值為NULL的記錄有多少（如果符合條件的二級索引記錄數量較少，那么統計結果是精確的，如果太多的話，會采用一定的手段計算一個模糊的值，當然算法也比較麻煩，我們就不展開說了），這種在查詢真正執行前優化器就率先訪問索引來計算需要掃描的索引記錄數量的方式稱之為index dive。當然，對于某些查詢，比方說WHERE子句中有IN條件，并且IN條件中包含許多參數的話，比方說這樣：

SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c', ... , 'zzzzzzz');

這樣的話需要統計的key1值所在的區間就太多了，這樣就不能采用index dive的方式去真正的訪問二級索引idx_key1，而是需要采用之前在背地里產生的一些統計數據去估算匹配的二級索引記錄有多少條（很顯然根據統計數據去估算記錄條數比index dive的方式精確性差了很多）。

反正不論采用index dive還是依據統計數據估算，最終要得到一個需要掃描的二級索引記錄條數，如果這個條數占整個記錄條數的比例特別大，那么就趨向于使用全表掃描執行查詢，否則趨向于使用這個索引執行查詢。

理解了這個也就好理解為什么在WHERE子句中出現IS NULL、IS NOT NULL、!=這些條件仍然可以使用索引，本質上都是優化器去計算一下對應的二級索引數量占所有記錄數量的比值而已。

大家可以看到，MySQL中決定使不使用某個索引執行查詢的依據很簡單：就是成本夠不夠小。而不是是否在WHERE子句中用了IS NULL、IS NOT NULL、!=這些條件。大家以后也多多辟謠吧，沒那么復雜，只是一個成本而已。

為什么LIKE以%開頭索引會失效？

首先看看B+樹是如何查找數據的：

查找數據時，MySQL會從根節點開始，按照從左到右的順序比較查詢條件和節點中的鍵值。如果查詢條件小于節點中的鍵值，則跳到該節點的左子節點繼續查找；如果查詢條件大于節點中的鍵值，則跳到該節點的右子節點繼續查找；如果查詢條件等于節點中的鍵值，則繼續查找該節點的下一個節點。

比如說我有下面這條SQL：

select * from `user` where nickname like '%冥';

如果數據庫中存在南冥 北冥 西冥 東冥 ，那么在B+樹中搜索的效率和全表掃描還有什么區別呢？

我走聚簇索引全表掃描還不用回表。

最后在擴展講一個點，其實不一定會導致索引失效。舉個例子：

create table `user`(
  id int primary key auto_increment,
  name varchar(20),
  index idx_name(name),
);

// 那么這種情況是會走索引的。
select id,name from `user` where name like '%冥';

為什么說上面的例子會走索引呢？

首先我們需要查詢的id name 這兩個字段是不是都在我們的輔助索引中，葉子節點是不是存的索引值和主鍵值，所以我們只要查輔助索引就可以直接拿到我們的需要的結果了，那么這個叫做索引覆蓋。我們觀察執行計劃會發現它的查詢級別是index ，其實也是全表遍歷了輔助索引。

第二個問題來了，那為什么就要走輔助索引而不是走全表掃描呢？

因為輔助索引中記錄的東西比主鍵索引少了很多，只有索引值和主鍵值，但是主鍵索引中就包含了，其他值、事物ID、MVCC的回流指針等等。再加上索引覆蓋不用回表，優化器就認為直接遍歷輔助索引的效率高于主鍵索引。

什么是索引覆蓋？

索引覆蓋（Index Covering）是指通過在索引中包含所有查詢語句中所需的列，可以避免對表中的數據進行額外的訪問，從而提高查詢效率。(避免了回表操作)

例如，對于一個查詢語句：

SELECT col1, col2, col3 FROM table WHERE col1 = x AND col2 = y

如果在table表中建立了一個索引，包含col1、col2和col3三列，那么MySQL可以通過索引定位到符合條件的數據，并在索引中提取col1、col2和col3列的值，無需對表中的數據進行額外的訪問。這種方式就叫做索引覆蓋。

索引覆蓋能夠顯著提高查詢效率，因此在建立索引時應盡量考慮包含查詢語句中所需的所有列。

什么是聚簇索引？

聚簇索引是一種特殊的索引，它將數據存儲在索引樹的葉子節點上。這種索引方式的優點是，在查詢數據時可以減少一次查詢，因為查詢索引樹的同時就能獲取到數據。聚簇索引的缺點是，因為數據存儲在索引樹中，所以對數據進行修改或刪除操作時需要更新索引樹，這會增加系統的開銷。

聚簇索引與非聚集索引的特點是什么？

在InnoDB中聚簇索引和非聚簇索引實際上是物理空間存儲方式的一個不同。

聚簇索引

1. 聚簇索引將數據存儲在索引樹的葉子節點上。
2. 聚簇索引可以減少一次查詢，因為查詢索引樹的同時就能獲取到數據。
3. 聚簇索引的缺點是，對數據進行修改或刪除操作時需要更新索引樹，會增加系統的開銷。
4. 聚簇索引通常用于數據庫系統中，主要用于提高查詢效率。

非聚簇索引（又稱二級索引 / 輔助索引）

1. 非聚簇索引不將數據存儲在索引樹的葉子節點上，而是存儲在數據頁中。
2. 非聚簇索引在查詢數據時需要兩次查詢，一次查詢索引樹，獲取數據頁的地址，再通過數據頁的地址查詢數據（通常情況下來說是的，但如果索引覆蓋的話實際上是不用回表的）。
3. 非聚簇索引的優點是，對數據進行修改或刪除操作時不需要更新索引樹，減少了系統的開銷。
4. 非聚簇索引通常用于數據庫系統中，主要用于提高數據更新和刪除操作的效率。

聚簇索引與非聚簇索引b+樹實現有什么區別？

結合“聚簇索引與非聚集索引的特點是什么？”加上下圖就明白了

一個表中可以有多個（非）聚簇索引嗎？

可以，這題容易混淆聚簇和非聚簇，聚簇只能有一個，但是非聚簇可以有很多，因為聚簇是和數據存放在一起的，但是非聚簇是單獨的。（同時這題可以結合上面兩個問題回答）

非聚簇索引為什么不存數據地址值而存儲主鍵？

我們知道在MyISAM引擎中是沒有聚簇索引，都是存的輔助索引。但是和InnoDB不同的是存儲的，它是存儲索引值和數據地址，而我們InnoDB中存儲的是主鍵ID。

我們要記住知道一個點，數據是會不斷變動的，那么它的一個地址也是會跟著不斷變動，如果直接存儲地址，下次找到的數據可能就不是原來的數據了。如果要解決這個問題的話，成本是非常高的。每次數據變動都需要進行調整。

一個b+樹中大概能存放多少條索引記錄？

什么是Hash索引？

哈希索引（hash index）基于哈希表實現。哈希索引通過Hash算法將數據庫的索引列數據轉換成定長的哈希碼作為key，將這條數據的行的地址作為value一并存入Hash表的對應位置。

在MySQL中，只有Memeory引擎顯式的支持哈希索引，這也是Memory引擎表的默認索引結構，Memeory同時也支持B-Tree索引。并且，Memory引擎支持非唯一哈希索引，如果多個列的哈希值相同（或者發生了Hash碰撞），索引會在對應Hash鍵下以鏈表形式存儲多個記錄地址。

哈希索引還有如下特點：

• 哈希索引不支持部分索引列的匹配查找，因為哈希索引始終是使用索引列的全部內容來計算哈希值的。例如，在數據列（A，B）上建立哈希索引，如果查詢只有數據列A，則無法使用該索引。
• 哈希索引具有哈希表的特性，因此只有精確匹配所有列的查詢對于哈希索引才有效，比如=、<>、IN(，因為數據的存儲是無序的)，且無法使用任何范圍查詢。
• 因為數據的存儲是無序的，哈希索引還無法用于排序。
• 對于精確查詢，則哈希索引效率很高，時間復雜度為O(1)，除非有很多哈希沖突（不同的索引列有相同的哈希值），如果發生哈希沖突，則存儲引擎必須遍歷鏈表中的所有數據指針，逐行比較，直到找到所有符合條件的行。哈希沖突越多，代價就越大！

InnoDB到底支不支持哈希索引？

對于InnoDB的哈希索引，確切的應該這么說：

• InnoDB用戶無法手動創建哈希索引，這一層上說，InnoDB確實不支持哈希索引;
• InnoDB會自調優(self-tuning)，如果判定建立自適應哈希索引(Adaptive Hash Index, AHI)，能夠提升查詢效率，InnoDB自己會建立相關哈希索引，這一層上說，InnoDB又是支持哈希索引的;

那什么是自適應哈希索引(Adaptive Hash Index, AHI)呢?

1、自適應即我們不需要自己處理，當InnoDB引擎根據查詢統計發現某一查詢滿足hash索引的數據結構特點，就會給其建立一個hash索引；

2、hash索引底層的數據結構是散列表（Hash表），其數據特點就是比較適合在內存中使用，自適應Hash索引存在于InnoDB架構中的緩存中（不存在于磁盤架構中）.

什么是索引下推？

索引下推（INDEX CONDITION PUSHDOWN，簡稱 ICP）是在 MySQL 5.6 針對掃描二級索引的一項優化改進。總的來說是通過把索引過濾條件下推到存儲引擎，來減少 MySQL 存儲引擎訪問基表的次數以及 MySQL 服務層訪問存儲引擎的次數。ICP 適用于 MYISAM 和 INNODB，本篇的內容只基于 INNODB。

在講這個技術之前你得對mysql架構有一個簡單的認識，見下圖

• MySQL 服務層：也就是 SERVER 層，用來解析 SQL 的語法、語義、生成查詢計劃、接管從 MySQL 存儲引擎層上推的數據進行二次過濾等等。
• MySQL 存儲引擎層：按照 MySQL 服務層下發的請求，通過索引或者全表掃描等方式把數據上傳到 MySQL 服務層。
• MySQL 索引掃描：根據指定索引過濾條件，遍歷索引找到索引鍵對應的主鍵值后回表過濾剩余過濾條件。
• MySQL 索引過濾：通過索引掃描并且基于索引進行二次條件過濾后再回表。
• 使用索引下推實現

索引下推的使用條件

• ICP目標是減少全行記錄讀取，從而減少IO 操作，只能用于非聚簇索引。聚簇索引本身包含的表數據，也就不存在下推一說。
• 只能用于range、 ref、 eq_ref、ref_or_null訪問方法；
• where 條件中是用 and 而非 or 的時候。
• ICP適用于分區表。
• ICP不支持基于虛擬列上建立的索引，比如說函數索引
• ICP不支持引用子查詢作為條件。
• ICP不支持存儲函數作為條件，因為存儲引擎無法調用存儲函數。

索引下推相關語句

# 查看索引下推是否開啟
select @@optimizer_switch
# 開啟索引下推
set optimizer_switch="index_condition_pushdown=on";
# 關閉索引下推
set optimizer_switch="index_condition_pushdown=off";

什么是唯一索引？

講起來非常簡單，其實和 "普通索引"類似，不同的就是：索引列的值必須唯一，但允許有空值。 可以是單列唯一索引，也可以是聯合唯一索引。

• 最大的所用就是確保寫入數據庫的數據是唯一值。

什么時候應該使用唯一索引呢？

我們前面講了唯一索引最大的好處就是能保證唯一性。看似沒什么太大的價值，可能就會有同學說，我業務層做一個重復檢查不就好了。問題就在這個地方，“業務是無法確保唯一性的”，除非你說你的代碼沒有BUG。很多時候業務場景需要保證唯一性，如果不在數據庫加限制的話，總有一天會出現臟數據。

那又有同學就說了，既然你不想重復你可以使用主鍵索引。這個回答也很有意思。

• 我們確實可以通過主鍵索引來保證唯一，但是，如果你的數據不能保證有序插入。比如說身份證字段，你如果用身份證字段作為主鍵的話，會導致查詢效率降低。
• 唯一索引還有一個好處就是可以為空，真實的業務場景肯定是可以保證身份證為空的，如果沒有綁定身份證就不讓注冊好像也有點說不過去。

聚簇索引的原理就不在這里細講了，會有一個單獨的章節來介紹。

唯一索引是否會影響性能呢？

我們通過和普通索引來做一個對比，有查詢和插入兩個場景。

首先第一個數據查詢，一般情況下來說索引是通過B+樹從根節點開始層序遍歷到葉子結點，數據頁內部通過二分搜索。

• 普通索引 查到滿足條件的第一條記錄，繼續查找下一條記錄，直到找到不滿足條件的記錄
• 唯一索引 查到第一個滿足條件的記錄，就停止搜索。

InnoDB 它是以數據頁為單位進行讀寫的，我們讀一條記錄，并不是從磁盤加載一條記錄，而是以頁為單位整體讀到內存里面來的。

普通索引比唯一索引就多了一次查找和判斷下一條記錄的操作，也就是一次指針尋找數據和一次計算。當然還有一種特殊情況，讀取到的這條數據正好是數據頁的最后一條，但是這種概率也是非常低，幾乎可以忽略不計。

整體看下來看上去性能差距并不大對吧。

來看第二個更新的性能，我們按照上面圖上的例子在2和6之間插入一個3。

在內存中

• 普通索引 找到2和6之間的位置 →插入值→ 結束
• 唯一索引 找到2和6之間的位置 →**當判斷有沒有沖突**→ 插入值→ 結束

不在內存中

• 普通索引 將更新記錄在change buffer → 結束
• 唯一索引 將數據頁讀入內存→當判斷到沒有沖突→插入值→結束

數據讀取到內存涉及了隨機IO訪問，這是在數據庫里面成本最高的操作之一，而change buffer 就可以減少這種隨機磁盤訪問，所以性能提示比較明顯。所以在這一塊來說，如果兩者在業務場景下都能滿足時可以優先考慮使用普通索引。

什么是聯合索引，組合索引，復合索引？

我們在索引回顧的時候和大家對索引做了一個分類對吧，按照字段個數來分的話，就分為了單列索引和組合索引對吧。那么他們之間的特點是什么呢？我們來看

• 單列索引 一個索引只包含了一個列，一個表里面可以有多個單列索引，但是這不叫組合索引。
• 組合索引（聯合索引 & 復合索引）一個索引包含多個列。

看上去感覺這組合索引并沒有太大作用是吧，我一個列已經有一個索引了，我還要這組合索引干嘛？

真相往往不那么簡單，首先我們得承認我們的業務千變萬化，我們的查詢語句條件肯定是非常多的。

• 高效率 如果說只有單列索引，那就會涉及多次二級索引樹查找，再加上回表，性能相對于聯合索引來說是比較低的。
• 減少開銷 我們要記得創建索引是存在空間開銷的，對于大數據量的表，使用聯合索引會降低空間開銷。
• 索引覆蓋 如果組合索引索引值已經滿足了我們的查詢條件，那么就不會進行回表，直接返回。

但是我們按照我們的查詢條件去創建一個聯合索引的話，就避免了上面的問題。那么聯合索引是怎么工作的呢？

這里涉及到了一個重點，叫做最左前綴，簡單理解就是只會從最左邊開始組合，組合索引的第一個字段必須出現在查詢組句中，還不能跳躍，只有這樣才能讓索引生效，比如說我查詢條件里面有組合索引里面的第二個字段，那么也是不會走組合索引的。舉個例子

// 假設給username，age創建了組合索引

// 這兩種情況是會走索引的
select username,age from user where username = '張三' and age = 18;
select * from user where username = '張三';

// 這種是不會走索引的
select * from user where age = 18;
select * from user where city = '北京' and age = 18;

復合索引創建時字段順序不一樣使用效果一樣嗎？

// 特殊情況，這種也是會走索引的，雖然我的age在前面，username在后面。
// 剛剛不是手最左前綴匹配嗎，為什么放到第二位也可以呢？
// 雖說順序不一致，但是在SQL執行過程中，根據查詢條件命中索引，
// 無論我username在不在前面，都會按照username去進行索引查找。
select * from user where age = 18 and username = '張三';

使用Order By時能否通過索引排序？

我們知道在很多場景下會導致索引失效，比如說沒有遵循B+樹的最左匹配原則，但是也有一些情況是遵循了最左匹配原則但是還是沒有走索引，這里我們使用order by進行排序的時候就有不走索引的情況，那么帶大家來分析一下

drop table if exists `user`;
drop table if exists `user_example`;
create table `user`(
    `id` int primary key comment '主鍵ID',
    `card_id` int comment '身份證',
    `nickname` varchar(10) comment '昵稱',
    `age` int not null comment '年齡',
    key  `card_id` (`card_id`)
) engine=InnoDB default charset=utf8mb4;

// 這里我們明明對card_id建好了單列索引，那為什么不走索引呢？
select * from `user` order by card_id

• 如果索引覆蓋是可以走索引的
• 如果帶上索引條件是可以走索引的

通過索引排序內部流程是什么呢？

explain select nickname,card_id,age from user order by card_id;

我們在了解mysql底層是怎么排序的之前，我們先來了解一下一個概念 sort buffer .

首先mysql會為每一個線程都分配一個固定大小的sort buffer 用于排序。它是一個具有邏輯概念的內存區域，我們可以通過sort_buffer_size參數來控制，默認值是256kb 。

// 輸入查看最，小可以設置為 32K，最大可以設置為 4G。
show variables like 'sort_buffer_size';

由于sort buffer 大小是一個固定的，但是我們待排序的數據量它不是，所以根據它們之間的一個差值呢，就分為了內部排序和外部排序

• 當待排序的數據量小于等于sort buffer 時，那我們的sort buffer就能夠容納，MySQL就可以直接在內存里面排序就行了，內部排序使用的排序算法是快排
• 當待排序的數據量大于sort buffer 時，那我們的sort buffer 就不夠用了對吧。這個時候MySQL就得要借助外部文件來進行排序了。將待排序數據拆成多個小文件，對各個小文件進行排序，最后再匯總成一個有序的文件，外部排序使用的算法時歸并排序

我們來聊聊row_id排序

和大家說一個這個參數max_length_for_sort_data ，在我們MySQL中專門控制用戶排序的行數據長度參數。默認是4096，也就是說如果超過了這個長度MySQL就會自動升級成row_id算法。

// 默認max_length_for_sort_data的大小為4096字節
show variables like 'max_length_for_sort_data';

row_id排序的思想就是把不需要的數據不放到sort_buffer中，讓sort_buffer中只存放需要排序的字段。

舉個例子：

explain select nickname,card_id,age from user order by card_id;

我們前面說到了sort buffer，在sort buffer里面進行排序的數據是我們select的全部字段，所以當我們查詢的字段越多，那么sort buffer能容納的數據量也就越小。而通過row_id排序就只會存放row_id 字段和排序相關的字段。其余的字段等排序完成之后通過主鍵ID進行回表拿。

group by 分組和 order by 在索引使用上有什么不同嗎？

沒什么太大的差異group by實際是先進行排序，再進行分組。所以遵循order by的索引機制。