什么是索引？

在關(guān)系數(shù)據(jù)庫中，索引是一種單獨的、物理的對數(shù)據(jù)庫表中一列或多列的值進行排序的一種存儲結(jié)構(gòu)，它是某個表中一列或若干列值的集合和相應(yīng)的指向表中物理標(biāo)識這些值的數(shù)據(jù)頁的邏輯指針清單。索引的作用相當(dāng)于圖書的目錄，可以根據(jù)目錄中的頁碼快速找到所需的內(nèi)容。

能實現(xiàn)快速定位數(shù)據(jù)的一種存儲結(jié)構(gòu)，其設(shè)計思想是以空間換時間。

索引的分類

按「數(shù)據(jù)結(jié)構(gòu)」分類：B+tree索引、Hash索引、Full-text索引。
按「物理存儲」分類：聚簇索引（主鍵索引）、二級索引（輔助索引）。
按「字段特性」分類：主鍵索引、唯一索引、普通索引、前綴索引。
按「字段個數(shù)」分類：單列索引、聯(lián)合索引。

MySQL如何實現(xiàn)的索引機制

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

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

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

為什么InnoDB要使用B+樹作為索引結(jié)構(gòu)？

InnoDB的索引和MyISAM的索引有什么區(qū)別？

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

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

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

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

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

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

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

答案是會的?。?！

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

索引的優(yōu)缺點是什么？

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

如果有了索引，會以學(xué)號為key創(chuàng)建索引，MySQL采用B+樹結(jié)構(gòu)存儲，一方面加載的數(shù)據(jù)只有學(xué)號和主鍵ID，另一方便采用了多叉平衡樹，定位到指定學(xué)號會很快，根據(jù)關(guān)聯(lián)的ID可以快速定位到對應(yīng)行的數(shù)據(jù)，所以檢索的速度會很快，因為加載的總數(shù)據(jù)很少，磁盤IO少。

可見，索引可以大大減少檢索數(shù)據(jù)的范圍、減少磁盤IO，使查詢速度很快，因為磁盤IO是很慢的，是由它的硬件結(jié)構(gòu)決定的。

? 優(yōu)點

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

? 缺點

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

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

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

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

如何查看一個表的索引？

?? 上代碼 ??

show index from table_name (表名)

有哪些情況會導(dǎo)致索引失效？

這個問題要分版本回答?。?！版本不同可能會導(dǎo)致索引失效的場景也不同，直接給答案的都是耍流氓?。。?/p>

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

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

如果表中有字段為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是一個二進制位序列，用來標(biāo)記表中每一個列是否為null。當(dāng)null bitmap中對應(yīng)的位為1時，表示對應(yīng)的列為null；當(dāng)null bitmap中對應(yīng)的位為0時，表示對應(yīng)的列不為null。在實際存儲時，InnoDB引擎會將null bitmap作為行記錄的一部分，存儲在行記錄的開頭，這樣可以在讀取行記錄時快速判斷每個列是否為null。

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

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

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

• 數(shù)據(jù)段 存放 B + 樹的葉子節(jié)點的區(qū)的集合
• 索引段 存放 B + 樹的非葉子節(jié)點的區(qū)的集合
• 回滾段 存放的是回滾數(shù)據(jù)的區(qū)的集合， MVCC就是利用了回滾段實現(xiàn)了多版本查詢數(shù)據(jù)

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

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

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

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

默認(rèn)每個頁的大小為 16KB，也就是最多能保證 16KB 的連續(xù)存儲空間。

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

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

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

重點來了！??！

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

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

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

這里簡單介紹一下，Compact行格式其他內(nèi)容后面單獨出一個章節(jié)介紹。

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

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

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

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

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

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

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

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

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

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

總結(jié)起來就是兩個字：成本！?。?/p>

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

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

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

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

SELECT * FROM s1 WHERE key1 IS NULL;

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

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

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

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

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

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

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

首先看看B+樹是如何查找數(shù)據(jù)的：

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

比如說我有下面這條SQL：

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

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

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

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

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 這兩個字段是不是都在我們的輔助索引中，葉子節(jié)點是不是存的索引值和主鍵值，所以我們只要查輔助索引就可以直接拿到我們的需要的結(jié)果了，那么這個叫做索引覆蓋。我們觀察執(zhí)行計劃會發(fā)現(xiàn)它的查詢級別是index ，其實也是全表遍歷了輔助索引。

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

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

什么是索引覆蓋？

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

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

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

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

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

什么是聚簇索引？

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

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

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

聚簇索引

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

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

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

聚簇索引與非聚簇索引b+樹實現(xiàn)有什么區(qū)別？

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

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

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

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

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

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

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

什么是Hash索引？

哈希索引（hash index）基于哈希表實現(xiàn)。哈希索引通過Hash算法將數(shù)據(jù)庫的索引列數(shù)據(jù)轉(zhuǎn)換成定長的哈希碼作為key，將這條數(shù)據(jù)的行的地址作為value一并存入Hash表的對應(yīng)位置。

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

哈希索引還有如下特點：

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

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

對于InnoDB的哈希索引，確切的應(yīng)該這么說：

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

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

1、自適應(yīng)即我們不需要自己處理，當(dāng)InnoDB引擎根據(jù)查詢統(tǒng)計發(fā)現(xiàn)某一查詢滿足hash索引的數(shù)據(jù)結(jié)構(gòu)特點，就會給其建立一個hash索引；

2、hash索引底層的數(shù)據(jù)結(jié)構(gòu)是散列表（Hash表），其數(shù)據(jù)特點就是比較適合在內(nèi)存中使用，自適應(yīng)Hash索引存在于InnoDB架構(gòu)中的緩存中（不存在于磁盤架構(gòu)中）.

什么是索引下推？

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

在講這個技術(shù)之前你得對mysql架構(gòu)有一個簡單的認(rèn)識，見下圖

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

索引下推的使用條件

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

索引下推相關(guān)語句

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

什么是唯一索引？

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

• 最大的所用就是確保寫入數(shù)據(jù)庫的數(shù)據(jù)是唯一值。

什么時候應(yīng)該使用唯一索引呢？

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

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

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

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

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

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

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

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

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

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

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

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

在內(nèi)存中

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

不在內(nèi)存中

• 普通索引 將更新記錄在change buffer → 結(jié)束
• 唯一索引 將數(shù)據(jù)頁讀入內(nèi)存→當(dāng)判斷到?jīng)]有沖突→插入值→結(jié)束

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

什么是聯(lián)合索引，組合索引，復(fù)合索引？

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

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

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

真相往往不那么簡單，首先我們得承認(rèn)我們的業(yè)務(wù)千變?nèi)f化，我們的查詢語句條件肯定是非常多的。

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

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

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

// 假設(shè)給username，age創(chuàng)建了組合索引

// 這兩種情況是會走索引的
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;

復(fù)合索引創(chuàng)建時字段順序不一樣使用效果一樣嗎？

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

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

我們知道在很多場景下會導(dǎo)致索引失效，比如說沒有遵循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

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

通過索引排序內(nèi)部流程是什么呢？

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

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

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

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

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

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

我們來聊聊row_id排序

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

// 默認(rèn)max_length_for_sort_data的大小為4096字節(jié)
show variables like 'max_length_for_sort_data';

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

舉個例子：

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

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

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

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