2021年最新最全的MySQL索引面試知識點

一、什么是索引？

索引是一種特殊的文件(InnoDB數據表上的索引是表空間的一個組成部分)，它們包含著對數據表里所有記錄的引用指針。

索引是一種數據結構。數據庫索引，是數據庫管理系統中一個排序的數據結構，以協助快速查詢、更新數據庫表中數據；索引的實現通常使用B樹及其變種B+樹。

更通俗的說，索引就相當于目錄。為了方便查找書中的內容，通過對內容建立索引形成目錄。索引是一個文件，它是要占據物理空間的。

二、索引有哪些優缺點？

索引的優點

1.可以大大加快數據的檢索速度，這也是創建索引的最主要的原因。

2.通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。

索引的缺點

1.時間方面：創建索引和維護索引要耗費時間，具體地，當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，會降低增/改/刪的執行效率；

2.空間方面：索引需要占物理空間。

三、索引使用場景（重點）

Where

上圖中，根據id查詢記錄，因為ID字段僅建立了主鍵索引，因此此SQL執行可選的索引只有主鍵索引，如果有多個，最終會選一個較優的作為檢索的依據。

--?增加一個沒有建立索引的字段

alter?table?innodb1?add?sex?char(1);

--?按sex檢索時可選的索引為null

EXPLAIN?SELECT?*?from?innodb1?where?sex='男';

可以嘗試在一個字段未建立索引時，根據該字段查詢的效率，然后對該字段建立索引（alter table 表名 add index 字段名），同樣的SQL執行的效率，你會發現查詢效率會有明顯的提升（數據量越大越明顯）。

ORDER BY

當我們使用order by將查詢結果按照某個字段排序時，如果該字段沒有建立索引，那么執行計劃會將查詢出的所有數據使用外部排序（將數據從硬盤分批讀取到內存使用內部排序，最后合并排序結果），這個操作是很影響性能的，因為需要將查詢涉及到的所有數據從磁盤中讀到內存（如果單條數據過大或者數據量過多都會降低效率），更無論讀到內存之后的排序了。

但是如果我們對該字段建立索引alter table 表名 add index(字段名)，那么由于索引本身是有序的，因此直接按照索引的順序和映射關系逐條取出數據即可。而且如果分頁的，那么只用取出索引表某個范圍內的索引對應的數據，而不用像上述那取出所有數據進行排序再返回某個范圍內的數據。（從磁盤取數據是最影響性能的）

Join

對Join語句匹配關系（on）涉及的字段建立索引能夠提高效率

索引覆蓋

這里值得注意的是不要想著為每個字段建立索引，因為優先使用索引的優勢就在于其體積小。

四、索引有哪幾種類型？

1.主鍵索引: 數據列不允許重復，不允許為NULL，一個表只能有一個主鍵。

2.唯一索引: 數據列不允許重復，允許為NULL值，一個表允許多個列創建唯一索引。

可以通過 ALTER TABLE table_name ADD UNIQUE (column);?創建普通索引。

可以通過 ALTER TABLE table_name ADD UNIQUE (column1,column2)， 創建唯一組合索引。

3.普通索引: 基本的索引類型，沒有唯一性的限制，允許為NULL值。

可以通過ALTER TABLE table_name ADD INDEX index_name (column)，創建普通索引。

可以通過ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3)，創建組合索引。

4.全文索引：是目前搜索引擎使用的一種關鍵技術。

可以通過ALTER TABLE table_name ADD FULLTEXT (column)，創建全文索引。

五、索引的數據結構（B樹，Hash）

索引的數據結構和具體存儲引擎的實現有關，在MySQL中使用較多的索引有Hash索引，B+樹索引等，而我們經常使用的InnoDB存儲引擎的默認索引實現為：B+樹索引。

對于哈希索引來說，底層的數據結構就是哈希表，因此在絕大多數需求為單條記錄查詢的時候，可以選擇哈希索引，查詢性能最快；其余大部分場景，建議選擇BTree索引。

1.B樹索引

MySQL通過存儲引擎取數據，基本上90%的人用的就是InnoDB了，按照實現方式分，InnoDB的索引類型目前只有兩種：BTree（B樹）索引和Hash索引。B樹索引是MySql數據庫中使用最頻繁的索引類型，基本所有存儲引擎都支持BTree索引。通常我們說的索引不出意外指的就是（B樹）索引（實際是用B+樹實現的，因為在查看表索引時，MySQL一律打印BTree，所以簡稱為B樹索引）

查詢方式：

主鍵索引區：PI(關聯保存的數據的地址)按主鍵查詢,

普通索引區：SI(關聯的ID的地址，然后再到達上面的地址)。所以按主鍵查詢,速度最快

B+tree性質：

1）n棵子tree的節點包含n個關鍵字，不用來保存數據而是保存數據的索引。

2）所有的葉子結點中包含了全部關鍵字的信息，及指向含這些關鍵字記錄的指針，且葉子結點本身依關鍵字的大小自小而大順序鏈接。

3）所有的非終端結點可以看成是索引部分，結點中僅含其子樹中的最大（或最小）關鍵字。

4）B+ 樹中，數據對象的插入和刪除僅在葉節點上進行。

5）B+樹有2個頭指針，一個是樹的根節點，一個是最小關鍵碼的葉節點。

2.哈希索引

簡要說下，類似于數據結構中簡單實現的Hash表（散列表）一樣，當我們在MySQL中用哈希索引時，主要就是通過Hash算法（常見的Hash算法有直接定址法、平方取中法、折疊法、除數取余法、隨機數法），將數據庫字段數據轉換成定的Hash值，與這條數據的行指針一并存入Hash表的對應位置；如果發生Hash碰撞（兩個不同關鍵字的Hash值相同），則在對應Hash鍵下以鏈表形式存儲。當然這只是簡略模擬圖。

六、索引的基本原理

索引用來快速地尋找那些具有特定值的記錄。如果沒有索引，一般來說執行查詢時遍歷整張表。

索引的原理很簡單，就是把無序的數據變成有序的查詢

1.把創建了索引的列的內容進行排序

2.對排序結果生成倒排表

3.在倒排表內容上拼上數據地址鏈

4.在查詢的時候，先拿到倒排表內容，再取出數據地址鏈，從而拿到具體數據

七、索引算法有哪些？

索引算法有 BTree算法和Hash算法

BTree算法

BTree是最常用的MySQL數據庫索引算法，也是MySQL默認的算法。因為它不僅可以被用在=,>,>=,<,<=和between這些比較操作符上，而且還可以用于LIKE操作符，只要它的查詢條件是一個不以通配符開頭的常量， 例如：

--?只要它的查詢條件是一個不以通配符開頭的常量

select?*?from?user?where?name?like?'jack%';

--?如果統一配符開頭，或者沒有使用常量，則不會使用索引，例如：

select?*?from?user?where?name?like?'%jack';

Hash算法

Hash索引只能用于對等比較，例如=,<=>（相當于=）操作符。由于是一次定位數據，不像BTree索引需要從根節點到枝節點，最后才能訪問到頁節點這樣多次IO訪問，所以檢索效率遠高于BTree索引。

八、索引設計的原則？

1.適合索引的列是出現在Where子句中的列，或者連接子句中指定的列。

2.基數較小的類，索引效果較差，沒有必要在此列建立索引。

4.不要過度索引，索引需要額外的磁盤空間，并降低寫操作的性能。在修改表內容的時候，索引會進行更新甚至重構，索引列越多，這個時間就會越長。所以只保持需要的索引有利于查詢即可。

九、創建索引的原則（重中之重）

索引雖好，但也不是無限制的使用，最好符合以下幾個原則

1.最左前綴匹配原則，組合索引非常重要的原則，MySQL會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。

2.較頻繁作為查詢條件的字段才去創建索引。

3.更新頻繁字段不適合創建索引。

4.若是不能有效區分數據的列不適合做索引列(如性別，男女未知，最多也就三種，區分度實在太低)。

5.盡量的擴展索引，不要新建索引。比如表中已經有a的索引，現在要加(a,b)的索引，那么只需要修改原來的索引即可。

6.定義有外鍵的數據列一定要建立索引。

7.對于那些查詢中很少涉及的列，重復值比較多的列不要建立索引。

8.對于定義為text、image和bit的數據類型的列不要建立索引。

十、創建索引的三種方式，刪除索引

第一種方式：在執行CREATE TABLE時創建索引

CREATE?TABLE?user_index2?(

id?INT?auto_increment?PRIMARY?KEY,

first_name?VARCHAR?(16),

last_name?VARCHAR?(16),

id_card?VARCHAR?(18),

information?text,

KEY?name?(first_name,?last_name),

FULLTEXT?KEY?(information),

UNIQUE?KEY?(id_card)

);

第二種方式：使用ALTER TABLE命令去增加索引

ALTER?TABLE?table_name?ADD?INDEX?index_name?(column_list);

ALTER TABLE用來創建普通索引、UNIQUE索引或PRIMARY KEY索引。

其中table_name是要增加索引的表名，column_list指出對哪些列進行索引，多列時各列之間用逗號分隔。

索引名index_name可自己命名，缺省時，MySQL將根據第一個索引列賦一個名稱。另外，ALTER TABLE允許在單個語句中更改多個表，因此可以在同時創建多個索引。

第三種方式：使用CREATE INDEX命令創建

CREATE?INDEX?index_name?ON?table_name?(column_list);

CREATE INDEX可對表增加普通索引或UNIQUE索引。（但是，不能創建PRIMARY KEY索引）

刪除索引

根據索引名刪除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名

alter?table?user_index?drop?KEY?name;

alter?table?user_index?drop?KEY?id_card;

alter?table?user_index?drop?KEY?information;

刪除主鍵索引：alter table 表名 drop primary key（因為主鍵只有一個）。這里值得注意的是，如果主鍵自增長，那么不能直接執行此操作（自增長依賴于主鍵索引）：

需要取消自增長再行刪除：

alter?table?user_index

--?重新定義字段

MODIFY?id?int,

drop?PRIMARY?KEY

但通常不會刪除主鍵，因為設計主鍵一定與業務邏輯無關。

十一、創建索引時需要注意什么？

1.非空字段：應該指定列為NOT NULL，除非你想存儲NULL。在MySQL中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計信息以及比較運算更加復雜。你應該用0、一個特殊的值或者一個空串代替空值；

2.取值離散大的字段：（變量各個取值之間的差異程度）的列放到聯合索引的前面，可以通過count()函數查看字段的差異值，返回值越大說明字段的唯一值越多字段的離散程度高；

3.索引字段越小越好：數據庫的數據存儲以頁為單位一頁存儲的數據越多一次IO操作獲取的數據越大效率越高。

十二、使用索引查詢一定能提高查詢的性能嗎？為什么？

通常，通過索引查詢數據比全表掃描要快，但是我們也必須注意到它的代價。

索引需要空間來存儲，也需要定期維護， 每當有記錄在表中增減或索引列被修改時，索引本身也會被修改。這意味著每條記錄的INSERT，DELETE，UPDATE將為此多付出4，5 次的磁盤I/O。因為索引需要額外的存儲空間和處理，那些不必要的索引反而會使查詢反應時間變慢。使用索引查詢不一定能提高查詢性能，索引范圍查詢(INDEX RANGE SCAN)適用于兩種情況:

基于一個范圍的檢索，一般查詢返回結果集小于表中記錄數的30%

基于非唯索引的檢索

十三、百萬級別或以上的數據如何刪除

關于索引：由于索引需要額外的維護成本，因為索引文件是單獨存在的文件,所以當我們對數據的增加,修改,刪除,都會產生額外的對索引文件的操作,這些操作需要消耗額外的IO，會降低增/改/刪的執行效率。

所以，在我們刪除數據庫百萬級別數據的時候，查詢MySQL官方手冊得知刪除數據的速度和創建的索引數量是成正比的。

1.所以我們想要刪除百萬數據的時候可以先刪除索引（此時大概耗時三分多鐘）。

2.然后刪除其中無用數據（此過程需要不到兩分鐘）。

4.與之前的直接刪除絕對是要快速很多，更別說萬一刪除中斷,一切刪除會回滾。那更是坑了。

十四、前綴索引

語法：index(field(10))，使用字段值的前10個字符建立索引，默認是使用字段的全部內容建立索引。

前提：前綴的標識度高。比如密碼就適合建立前綴索引，因為密碼幾乎各不相同。

實操的難度：在于前綴截取的長度。

我們可以利用select count(*)/count(distinct left(password,prefixLen));，通過從調整prefixLen的值（從1自增）查看不同前綴長度的一個平均匹配度，接近1時就可以了（表示一個密碼的前prefixLen個字符幾乎能確定唯一一條記錄）

十五、什么是最左前綴原則？什么是最左匹配原則

顧名思義，就是最左優先，在創建多列索引時，要根據業務需求，where子句中使用最頻繁的一列放在最左邊。

最左前綴匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。

=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，MySQL的查詢優化器會幫你優化成索引可以識別的形式

十六、B樹和B+樹的區別

在B樹中，你可以將鍵和值存放在內部節點和葉子節點；但在B+樹中，內部節點都是鍵，沒有值，葉子節點同時存放鍵和值。

B+樹的葉子節點有一條鏈相連，而B樹的葉子節點各自獨立。

十七、使用B樹的好處

B樹可以在內部節點同時存儲鍵和值，因此，把頻繁訪問的數據放在靠近根節點的地方將會大大提高熱點數據的查詢效率。這種特性使得B樹在特定數據重復多次查詢的場景中更加高效。

十八、使用B+樹的好處

由于B+樹的內部節點只存放鍵，不存放值，因此，一次讀取，可以在內存頁中獲取更多的鍵，有利于更快地縮小查找范圍。B+樹的葉節點由一條鏈相連，因此，當需要進行一次全數據遍歷的時候，B+樹只需要使用O(logN)時間找到最小的一個節點，然后通過鏈進行O(N)的順序遍歷即可。而B樹則需要對樹的每一層進行遍歷，這會需要更多的內存置換次數，因此也就需要花費更多的時間

十九、Hash索引和B+樹所有有什么區別或者說優劣呢?

首先要知道Hash索引和B+樹索引的底層實現原理：

Hash索引底層就是Hash表，進行查找時，調用一次Hash函數就可以獲取到相應的鍵值，之后進行回表查詢獲得實際數據。B+樹底層實現是多路平衡查找樹。對于每一次的查詢都是從根節點出發，查找到葉子節點方可以獲得所查鍵值，然后根據查詢判斷是否需要回表查詢數據。

那么可以看出他們有以下的不同：

Hash索引進行等值查詢更快(一般情況下)，但是卻無法進行范圍查詢。因為在Hash索引中經過Hash函數建立索引之后，索引的順序與原順序無法保持一致，不能支持范圍查詢。而B+樹的的所有節點皆遵循(左節點小于父節點，右節點大于父節點，多叉樹也類似)，天然支持范圍。

Hash索引不支持使用索引進行排序，原理同上。

Hash索引不支持模糊查詢以及多列索引的最左前綴匹配。原理也是因為hash函數的不可預測。AAAA和AAAAB的索引沒有相關性。

Hash索引任何時候都避免不了回表查詢數據，而B+樹在符合某些條件(聚簇索引，覆蓋索引等)的時候可以只通過索引完成查詢。

Hash索引雖然在等值查詢上較快，但是不穩定。性能不可預測，當某個鍵值存在大量重復的時候，發生Hash碰撞，此時效率可能極差。而B+樹的查詢效率比較穩定，對于所有的查詢都是從根節點到葉子節點，且樹的高度較低。

因此，在大多數情況下，直接選擇B+樹索引可以獲得穩定且較好的查詢速度。而不需要使用Hash索引。

二十、數據庫為什么使用B+樹而不是B樹

B樹只適合隨機檢索，而B+樹同時支持隨機檢索和順序檢索；

B+樹空間利用率更高，可減少I/O次數，磁盤讀寫代價更低。一般來說，索引本身也很大，不可能全部存儲在內存中，因此索引往往以索引文件的形式存儲的磁盤上。這樣的話，索引查找過程中就要產生磁盤I/O消耗。B+樹的內部結點并沒有指向關鍵字具體信息的指針，只是作為索引使用，其內部結點比B樹小，盤塊能容納的結點中關鍵字數量更多，一次性讀入內存中可以查找的關鍵字也就越多，相對的，IO讀寫次數也就降低了。而IO讀寫次數是影響索引檢索效率的最大因素；

B+樹的查詢效率更加穩定。B樹搜索有可能會在非葉子結點結束，越靠近根節點的記錄查找時間越短，只要找到關鍵字即可確定記錄的存在，其性能等價于在關鍵字全集內做一次二分查找。而在B+樹中，順序檢索比較明顯，隨機檢索時，任何關鍵字的查找都必須走一條從根節點到葉節點的路，所有關鍵字的查找路徑長度相同，導致每一個關鍵字的查詢效率相當。

增刪文件（節點）時，效率更高。因為B+樹的葉子節點包含所有關鍵字，并以有序的鏈表結構存儲，這樣可很好提高增刪效率。

二十一、B+樹在滿足聚簇索引和覆蓋索引的時候不需要回表查詢數據

在B+樹的索引中，葉子節點可能存儲了當前的Key值，也可能存儲了當前的Key值以及整行的數據，這就是聚簇索引和非聚簇索引。在InnoDB中，只有主鍵索引是聚簇索引，如果沒有主鍵，則挑選一個唯一鍵建立聚簇索引。如果沒有唯一鍵，則隱式的生成一個鍵來建立聚簇索引。

當查詢使用聚簇索引時，在對應的葉子節點，可以獲取到整行數據，因此不用再次進行回表查詢。

二十二、什么是聚簇索引？何時使用聚簇索引與非聚簇索引

聚簇索引：將數據存儲與索引放到了一塊，找到索引也就找到了數據

非聚簇索引：將數據存儲于索引分開結構，索引結構的葉子節點指向了數據的對應行，myisam通過key_buffer把索引先緩存到內存中，當需要訪問數據時（通過索引訪問數據），在內存中直接搜索索引，然后通過索引找到磁盤相應數據，這也就是為什么索引不在key buffer命中時，速度慢的原因。

澄清一個概念：Innodb中，在聚簇索引之上創建的索引稱之為輔助索引，輔助索引訪問數據總是需要二次查找，非聚簇索引都是輔助索引，像復合索引、前綴索引、唯一索引，輔助索引子節點存儲的不再是行的物理位置，而是主鍵值

何時使用聚簇索引與非聚簇索引

二十三、非聚簇索引一定會回表查詢嗎？

不一定，這涉及到查詢語句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再進行回表查詢。

舉個簡單的例子，假設我們在員工表的年齡上建立了索引，那么當進行select age from employee where age < 20的查詢時，在索引的葉子節點上，已經包含了age信息，不會再次進行回表查詢。

二十四、聯合索引是什么？為什么需要注意聯合索引中的順序？

MySQL可以使用多個字段同時建立一個索引，叫做聯合索引。在聯合索引中，如果想要命中索引，需要按照建立索引時的字段順序挨個使用，否則無法命中索引。

具體原因為：

MySQL使用索引時需要索引有序，假設現在建立了"name，age，school"的聯合索引，那么索引的排序為: 先按照name排序，如果name相同，則按照age排序，如果age的值也相等，則按照school進行排序。

當進行查詢時，此時索引僅僅按照name嚴格有序，因此必須首先使用name字段進行等值查詢，之后對于匹配到的列而言，其按照age字段嚴格有序，此時可以使用age字段用做索引查找，以此類推。因此在建立聯合索引的時候應該注意索引列的順序，一般情況下，將查詢需求頻繁或者字段選擇性高的列放在前面。此外可以根據特例的查詢或者表結構進行單獨的調整。

MySQL 數據結構

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。