索引是什麼？
索引是一種特殊的檔案(InnoDB資料表上的索引是表空間的一個組成部分)，它們包含著對資料表裡所有記錄的引用指標。
索引是一種資料結構。資料庫索引，是資料庫管理系統中一個排序的資料結構，以協助快速查詢、更新資料庫表中資料。索引的實現通常使用B樹及其變種B+樹。更通俗的說，索引就相當於目錄。為了方便查詢書中的內容，通過對內容建立索引形成目錄。而且索引是一個檔案，它是要佔據物理空間的。
MySQL索引的建立對於MySQL的高效執行是很重要的，索引可以大大提高MySQL的檢索速度。比如我們在查字典的時候，前面都有檢索的拼音和偏旁、筆畫等，然後找到對應字典頁碼，這樣然後就開啟字典的頁數就可以知道我們要搜尋的某一個key的全部值的資訊了。
總結：索引是一種為了快速查詢資料而設計的一種資料結構。
索引有哪些優缺點？
索引的優點
- 可以大大加快資料的檢索速度，這也是建立索引的最主要的原因。
- 通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的效能。
索引的缺點
- 時間方面：建立索引和維護索引要耗費時間，具體地，當對錶中的資料進行增加、刪除和修改的時候，索引也要動態的維護，會降低增/改/刪的執行效率；
- 空間方面：索引需要佔物理空間。
MySQL有哪幾種索引型別？
- 從資料結構上來劃分：BTree索引（B-Tree或B+Tree索引）、Hash索引、full-index（全文索引）、R-Tree索引（空間索引）。這裡所描述的是索引儲存時儲存的形式。
- 從應用層次來分：普通索引，唯一索引，複合索引。
普通索引：即一個索引只包含單個列，一個表可以有多個單列索引。
唯一索引：索引列的值必須唯一，但允許有空值。
複合索引：多列值組成一個索引，專門用於組合搜尋，其效率大於索引合併。
聚簇索引(聚集索引)：並不是一種單獨的索引型別，而是一種資料儲存方式。具體細節取決於不同的實現，InnoDB的聚簇索引其實就是在同一個結構中儲存了B-Tree索引(技術上來說是B+Tree)和資料行。
非聚簇索引： 不是聚簇索引，就是非聚簇索引。 - 根據中資料的物理順序與鍵值的邏輯（索引）順序關係： 聚集索引，非聚集索引。
說一說索引的底層實現?
B+Tree索引
定義：是B-Tree的改進版本，同時也是MySQL中InnoDB儲存引擎所採用的儲存結構(是一種由B樹和索引訪問順序演變而來的索引，也就是我們常說的一種索引型別。B數決定索引的儲存結構，索引訪問順序決定了索引是按照有序排列，索引訪問屬性有ISAM演算法實現，也是MyISAM儲存引擎採用的一種索引儲存演算法)。資料都在葉子節點上，並且增加了順序訪問指標，每個葉子節點都指向相鄰的葉子節點的地址。相比B-Tree來說，進行範圍查詢時只需要查詢兩個節點，進行遍歷即可。而B-Tree需要獲取所有節點，相比之下B+Tree效率更高。
B+tree性質：
- n棵子tree的節點包含n個關鍵字，不用來儲存資料而是儲存資料的索引。
- 所有的葉子結點中包含了全部關鍵字的資訊，及指向含這些關鍵字記錄的指標，且葉子結點本身依關鍵字的大小自小而大順序連結。
- 所有的非終端結點可以看成是索引部分，結點中僅含其子樹中的最大（或最小）關鍵字。
- B+ 樹中，資料物件的插入和刪除僅在葉節點上進行。
- B+樹有2個頭指標，一個是樹的根節點，一個是最小關鍵碼的葉節點。
Hash索引
基於雜湊表實現，只有精確匹配索引所有列的查詢才有效(hash索引的查詢使用等值查詢。)，對於每一行資料，儲存引擎都會對所有的索引列計算一個雜湊碼（hash code），並且Hash索引將所有的雜湊碼儲存在索引中，同時在索引表中儲存指向每個資料行的指標。hash索引，不能人為的設定，這是MySQL內部根據索引情況，自動轉換的。
為什麼索引結構預設使用B+Tree，而不是B-Tree，Hash，二叉樹，紅黑樹？
B-tree：
從兩個方面來回答
- B+樹的磁碟讀寫代價更低：B+樹的內部節點並沒有指向關鍵字具體資訊的指標，因此其內部節點相對B(B-)樹更小，如果把所有同一內部節點的關鍵字存放在同一盤塊中，那麼盤塊所能容納的關鍵字數量也越多，一次性讀入記憶體的需要查詢的關鍵字也就越多，相對IO讀寫次數就降低了。
- 由於B+樹的資料都儲存在葉子結點中，分支結點均為索引，方便掃庫，只需要掃一遍葉子結點即可，但是B樹因為其分支結點同樣儲存著資料，我們要找到具體的資料，需要進行一次中序遍歷按序來掃，所以B+樹更加適合在區間查詢的情況，所以通常B+樹用於資料庫索引。
Hash：
- 雖然可以快速定位，但是沒有順序，IO複雜度高；
- 基於Hash表實現，只有Memory儲存引擎顯式支援雜湊索引 ；
- 適合等值查詢，如=、in()、<=>，不支援範圍查詢 ；
- 因為不是按照索引值順序儲存的，就不能像B+Tree索引一樣利用索引完排序；
- Hash索引在查詢等值時非常快 ；
- 因為Hash索引始終索引的所有列的全部內容，所以不支援部分索引列的匹配查詢 ；
- 如果有大量重複鍵值得情況下，雜湊索引的效率會很低，因為存在雜湊碰撞問題 。
二叉樹
樹的高度不均勻，不能自平衡，查詢效率跟資料有關（樹的高度），並且IO代價高。
紅黑樹
樹的高度隨著資料量增加而增加，IO代價高。
講一講聚簇索引與非聚簇索引？
在 InnoDB 裡，索引B+ Tree的葉子節點儲存了整行資料的是主鍵索引，也被稱之為聚簇索引，即將資料儲存與索引放到了一塊，找到索引也就找到了資料。
而索引B+ Tree的葉子節點儲存了主鍵的值的是非主鍵索引，也被稱之為非聚簇索引、二級索引。
聚簇索引與非聚簇索引的區別：
- 非聚集索引與聚集索引的區別在於非聚集索引的葉子節點不儲存表中的資料，而是儲存該列對應的主鍵（行號）
- 對於InnoDB來說，想要查詢資料我們還需要根據主鍵再去聚集索引中進行查詢，這個再根據聚集索引查詢資料的過程，我們稱為回表。第一次索引一般是順序IO，回表的操作屬於隨機IO。需要回表的次數越多，即隨機IO次數越多，我們就越傾向於使用全表掃描 。
- 通常情況下， 主鍵索引（聚簇索引）查詢只會查一次，而非主鍵索引（非聚簇索引）需要回表查詢多次。當然，如果是覆蓋索引的話，查一次即可
- 注意：MyISAM無論主鍵索引還是二級索引都是非聚簇索引，而InnoDB的主鍵索引是聚簇索引，二級索引是非聚簇索引。我們自己建的索引基本都是非聚簇索引。
非聚簇索引一定會回表查詢嗎？
不一定，這涉及到查詢語句所要求的欄位是否全部命中了索引，如果全部命中了索引，那麼就不必再進行回表查詢。一個索引包含（覆蓋）所有需要查詢欄位的值，被稱之為”覆蓋索引”。換而言之，如果查詢的欄位在索引中就能夠找到，就不需要在進行回表查詢。
舉個簡單的例子，假設我們在員工表的年齡上建立了索引，那麼當進行select score from student where score > 90的查詢時，在索引的葉子節點上，已經包含了score 資訊，不會再次進行回表查詢。
聯合索引是什麼？為什麼需要注意聯合索引中的順序？
MySQL可以使用多個欄位同時建立一個索引，叫做聯合索引。在聯合索引中，如果想要命中索引，需要按照建立索引時的欄位順序挨個使用，否則無法命中索引。
具體原因為:
- MySQL使用索引時需要索引有序，假設現在建立了”name，age，school”的聯合索引，那麼索引的排序為: 先按照name排序，如果name相同，則按照age排序，如果age的值也相等，則按照school進行排序。
- 當進行查詢時，此時索引僅僅按照name嚴格有序，因此必須首先使用name欄位進行等值查詢，之後對於匹配到的列而言，其按照age欄位嚴格有序，此時可以使用age欄位用做索引查詢，以此類推。因此在建立聯合索引的時候應該注意索引列的順序，一般情況下，將查詢需求頻繁或者欄位選擇性高的列放在前面。此外可以根據特例的查詢或者表結構進行單獨的調整。
講一講MySQL的最左字首原則?
最左字首原則就是最左優先，在建立多列索引時，要根據業務需求。
- 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的查詢優化器會幫你優化成索引可以識別的形式。
講一講字首索引？
因為可能我們索引的欄位非常長，這既佔記憶體空間，也不利於維護。所以我們就想，如果只把很長欄位的前面的公共部分作為一個索引，就會產生超級加倍的效果。但是，我們需要注意，order by不支援字首索引 。
流程是：
先計算完整列的選擇性 :
select count(distinct col_1)/count(1) from table_1再計算不同字首長度的選擇性 :
select count(distinct left(col_1,4))/count(1) from table_1找到最優長度之後，建立字首索引 :
create index idx_front on table_1 (col_1(4))瞭解索引下推嗎？
MySQL 5.6引入了索引下推優化。預設開啟，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以將其關閉。
有了索引下推優化，可以在減少回表次數。
在InnoDB中只針對二級索引有效。
官方文件中給的例子和解釋如下：
在 people_table中有一個二級索引(zipcode，lastname，address)，查詢是
SELECT * FROM people WHERE zipcode=’95054′ AND
lastname LIKE ‘%etrunia%’ AND
address LIKE ‘%Main Street%’;如果沒有使用索引下推技術，則MySQL會通過zipcode=’95054’從儲存引擎中查詢對應的資料，返回到MySQL服務端，然後MySQL服務端基於lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’來判斷資料是否符合條件
如果使用了索引下推技術，則MYSQL首先會返回符合zipcode=’95054’的索引，然後根據lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’來判斷索引是否符合條件。如果符合條件，則根據該索引來定位對應的資料，如果不符合，則直接reject掉。
怎麼檢視MySQL語句有沒有用到索引？
通過explain，如以下例子：
EXPLAIN SELECT * FROM employees.titles WHERE
emp_no='10001' AND title='Senior Engineer'
AND from_date='1986-06-26';***************************[ 1. row ]***************************
id | 1
select_type | SIMPLE
table | store_article
partitions | <null>
type | ALL
possible_keys | <null>
key | <null>
key_len | <null>
ref | <null>
rows | 11
filtered | 100.0
Extra | <null>- id：在⼀個⼤的查詢語句中每個SELECT關鍵字都對應⼀個唯⼀的id ，如explain select * from s1 where id = (select id from s1 where name = ‘egon1’);第一個select的id是1，第二個select的id是2。有時候會出現兩個select，但是id卻都是1，這是因為優化器把子查詢變成了連線查詢 。
- select_type：select關鍵字對應的那個查詢的型別，如SIMPLE,PRIMARY,SUBQUERY,DEPENDENT,SNION 。
- table：每個查詢對應的表名 。
- type：type 欄位比較重要, 它提供了判斷查詢是否高效的重要依據依據. 通過 type 欄位, 我們判斷此次查詢是 全表掃描 還是 索引掃描 等。如const(主鍵索引或者唯一二級索引進行等值匹配的情況下),ref(普通的⼆級索引列與常量進⾏等值匹配),index(掃描全表索引的覆蓋索引) 。
通常來說, 不同的 type 型別的效能關係如下:ALL < index < range ~ index_merge < ref < eq_ref < const < system ALL型別因為是全表掃描, 因此在相同的查詢條件下, 它是速度最慢的.
而 index 型別的查詢雖然不是全表掃描, 但是它掃描了所有的索引, 因此比 ALL 型別的稍快. - possible_key：查詢中可能用到的索引(可以把用不到的刪掉，降低優化器的優化時間) 。
- key：此欄位是 MySQL 在當前查詢時所真正使用到的索引。
- filtered：查詢器預測滿足下一次查詢條件的百分比 。
rows 也是一個重要的欄位. MySQL 查詢優化器根據統計資訊, 估算 SQL 要查詢到結果集需要掃描讀取的資料行數.
這個值非常直觀顯示 SQL 的效率好壞, 原則上 rows 越少越好。 - extra：表示額外資訊，如Using where,Start temporary,End temporary,Using temporary等。
為什麼官方建議使用自增長主鍵作為索引？
結合B+Tree的特點，自增主鍵是連續的，在插入過程中儘量減少頁分裂，即使要進行頁分裂，也只會分裂很少一部分。並且能減少資料的移動，每次插入都是插入到最後。總之就是減少分裂和移動的頻率。
如何建立索引？
建立索引有三種方式。
- 在執行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用來建立普通索引、UNIQUE索引或PRIMARY KEY索引。ALTER TABLE table_name ADD INDEX index_name (column_list);
其中table_name是要增加索引的表名，column_list指出對哪些列進行索引，多列時各列之間用逗號分隔。
索引名index_name可自己命名，預設時，MySQL將根據第一個索引列賦一個名稱。另外，ALTER TABLE允許在單個語句中更改多個表，因此可以在同時建立多個索引。 - 使用CREATE INDEX命令建立。
CREATE INDEX index_name ON table_name (column_list);
建立索引時需要注意什麼？
- 非空欄位：應該指定列為NOT NULL，除非你想儲存NULL。在mysql中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計資訊以及比較運算更加複雜。你應該用0、一個特殊的值或者一個空串代替空值；
- 取值離散大的欄位：（變數各個取值之間的差異程度）的列放到聯合索引的前面，可以通過count()函式檢視欄位的差異值，返回值越大說明欄位的唯一值越多欄位的離散程度高；
- 索引欄位越小越好：資料庫的資料儲存以頁為單位一頁儲存的資料越多一次IO操作獲取的資料越大效率越高。
建立索引的原則有哪些？
- 最左字首匹配原則，非常重要的原則，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的查詢優化器會幫你優化成索引可以識別的形式。 - 儘量選擇區分度高的列作為索引，區分度的公式是count(distinct col)/count(*)，表示欄位不重複的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別欄位可能在大資料面前區分度就是0，那可能有人會問，這個比例有什麼經驗值嗎？使用場景不同，這個值也很難確定，一般需要join的欄位我們都要求是0.1以上，即平均1條掃描10條記錄。
- 索引列不能參與計算，保持列“乾淨”，比如
from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是資料表中的欄位值，但進行檢索時，需要把所有元素都應用函式才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’)。 - 儘量的擴充套件索引，不要新建索引。比如表中已經有a的索引，現在要加(a,b)的索引，那麼只需要修改原來的索引即可。
使用索引查詢一定能提高查詢的效能嗎？
通常通過索引查詢資料比全表掃描要快。但是我們也必須注意到它的代價。
索引需要空間來儲存，也需要定期維護， 每當有記錄在表中增減或索引列被修改時，索引本身也會被修改。 這意味著每條記錄的INSERT，DELETE，UPDATE將為此多付出4，5 次的磁碟I/O。 因為索引需要額外的儲存空間和處理，那些不必要的索引反而會使查詢反應時間變慢。使用索引查詢不一定能提高查詢效能，索引範圍查詢(INDEX RANGE SCAN)適用於兩種情況:
- 基於一個範圍的檢索，一般查詢返回結果集小於表中記錄數的30%
- 基於非唯一性索引的檢索。
什麼情況下不走索引（索引失效）？
- 使用!= 或者 <> 導致索引失效。
- 型別不一致導致的索引失效。
- 函式導致的索引失效。
如：SELECT * FROMuserWHERE DATE(create_time) = '2020-09-03';
如果使用函式在索引列，這是不走索引的。 - 運算子導致的索引失效。
SELECT * FROMuserWHERE age - 1 = 20;
如果你對列進行了（+，-，*，/，!）, 那麼都將不會走索引。 - OR引起的索引失效。
SELECT * FROMuserWHEREname= '張三' OR height = '175';
OR導致索引是在特定情況下的，並不是所有的OR都是使索引失效，如果OR連線的是同一個欄位，那麼索引不會失效，反之索引失效。 - 模糊搜尋導致的索引失效。
SELECT * FROMuserWHEREnameLIKE '%冰';
當%放在匹配欄位前是不走索引的，放在後面才會走索引。 - NOT IN、NOT EXISTS導致索引失效。
個人開發的一款小程式，主要是收集一些網際網路大廠面試真題，希望能夠幫助大家早日進入大廠。
本作品採用《CC 協議》，轉載必須註明作者和本文連結