索引是數據的目錄，所謂的存儲引擎，說白了就是如何存儲數據、如何為存儲的數據建立索引和如何更新、查詢數據等技術的實現方法。
索引的分類#
按照四個角度來分類索引。
- 按「數據結構」分類：B+tree 索引、Hash 索引、Full-text 索引。
- 按「物理存儲」分類：聚簇索引（主鍵索引）、二級索引（輔助索引）。
- 按「字段特性」分類：主鍵索引、唯一索引、普通索引、前綴索引。
- 按「字段個數」分類：單列索引、聯合索引。
按數據結構分類#
從數據結構的角度來看，MySQL 常見索引有 B+Tree 索引、HASH 索引、Full-Text 索引。
B+Tree 索引#
B+Tree 索引類型也是 MySQL 存儲引擎採用最多的索引類型。
在創建表時，InnoDB 存儲引擎會根據不同的場景選擇不同的列作為索引：
- 如果有主鍵，默認會使用主鍵作為聚簇索引的索引鍵（key）；
- 如果沒有主鍵，就選擇第一個不包含 NULL 值的唯一列作為聚簇索引的索引鍵（key）；
- 在上面兩個都沒有的情況下，InnoDB 將自動生成一個隱式自增 id 列作為聚簇索引的索引鍵（key）
創建的主鍵索引和二級索引默認使用的是 B+Tree 索引。
B+Tree 是一種多叉樹，葉子節點才存放數據，非葉子節點只存放索引，而且每個節點裡的數據是按主鍵順序存放的，並且每一個葉子節點都有兩個指針，分別指向下一個葉子節點和上一個葉子節點，形成一個雙向鏈表。
B+Tree 存儲千萬級的數據只需要3-4 層高度就可以滿足，這意味著從千萬級的表查詢目標數據最多需要3-4 次磁碟 I/O，所以 B+Tree 相比於 B 樹和二叉樹來說，最大的優勢在於查詢效率很高，因為即使在數據量很大的情況，查詢一個數據的磁碟 I/O 依然維持在 3-4 次。
為什麼 MySQL InnoDB 選擇 B+tree 作為索引的數據結構？
1、B+Tree vs B Tree
B+Tree 只在葉子節點存儲數據，而 B 樹 的非葉子節點也要存儲數據，所以 B+Tree 的單個節點的數據量更小，在相同的磁碟 I/O 次數下，就能查詢更多的節點。
另外，B+Tree 葉子節點採用的是雙鏈表連接，適合 MySQL 中常見的基於範圍的順序查找，而 B 樹無法做到這一點。
2、B+Tree vs Hash
Hash 在做等值查詢的時候效率非常快，搜索複雜度為 O (1)。
但是 Hash 表不適合做範圍查詢，它更適合做等值的查詢，這也是 B+Tree 索引要比 Hash 表索引有著更廣泛的適用場景的原因。
按物理存儲分類#
分為聚簇索引（主鍵索引）、二級索引（輔助索引）
這兩個區別：
- 主鍵索引的 B+Tree 的葉子節點存放的是實際數據，所有完整的用戶記錄都存放在主鍵索引的 B+Tree 的葉子節點裡；
- 二級索引的 B+Tree 的葉子節點存放的是主鍵值，而不是實際數據
覆蓋索引： 所以，在查詢時使用了二級索引，如果查詢的數據能在二級索引裡查詢的到，那麼就不需要回表，這個過程就是覆蓋索引。
回表： 如果查詢的數據不在二級索引裡，就會先檢索二級索引，找到對應的葉子節點，獲取到主鍵值後，然後再檢索主鍵索引，就能查詢到數據了，這個過程就是回表。
按字段特性分類#
從字段特性的角度來看，索引分為主鍵索引、唯一索引、普通索引、前綴索引。
1. 主鍵索引
主鍵索引就是建立在主鍵字段上的索引，通常在創建表的時候一起創建，一張表最多只有一個主鍵索引，索引列的值不允許有空值。
2. 唯一索引
唯一索引建立在 UNIQUE 字段上的索引，一張表可以有多個唯一索引，索引列的值必須唯一，但是允許有空值。
3. 普通索引
普通索引就是建立在普通字段上的索引，既不要求字段為主鍵，也不要求字段為 UNIQUE。
4. 前綴索引
前綴索引是指對字符類型字段的前幾個字符建立的索引，而不是在整個字段上建立的索引，前綴索引可以建立在字段類型為 char、 varchar、binary、varbinary 的列上。
使用前綴索引的目的是為了減少索引佔用的存儲空間，提升查詢效率。
按字段個數分類#
從字段個數的角度來看，索引分為單列索引、聯合索引（複合索引）。
- 建立在單列上的索引稱為單列索引，比如主鍵索引；
- 建立在多列上的索引稱為聯合索引；
聯合索引
通過將多個字段組合成一個索引，該索引就被稱為聯合索引。
比如，將商品表中的 product_no 和 name 字段組合成聯合索引 (product_no, name)，創建聯合索引的方式如下：
CREATE INDEX index_product_no_name ON product(product_no, name);
可以看到，聯合索引的非葉子節點用兩個字段的值作為 B+Tree 的 key 值。當在聯合索引查詢數據時，先按 product_no 字段比較，在 product_no 相同的情況下再按 name 字段比較。
也就是說，聯合索引查詢的 B+Tree 是先按 product_no 進行排序，然後再 product_no 相同的情況下再按 name 字段排序。
因此，使用聯合索引時，存在最左匹配原則，也就是按照最左優先的方式進行索引的匹配。在使用聯合索引進行查詢的時候，如果不遵循「最左匹配原則」，聯合索引會失效，這樣就無法利用到索引快速查詢的特性了。
聯合索引裡的範圍查詢#
聯合索引的最左匹配原則會一直向右匹配直到遇到「範圍查詢」就會停止匹配。也就是範圍查詢的字段可以用到聯合索引，但是在範圍查詢字段的後面的字段無法用到聯合索引。
聯合索引的最左匹配原則，在遇到範圍查詢（如 >、<) 的時候，就會停止匹配，也就是範圍查詢的字段可以用到聯合索引，但是在範圍查詢字段的後面的字段無法用到聯合索引。注意，對於 >=、<=、BETWEEN、like 前綴匹配的範圍查詢，並不會停止匹配，前面我也用了四個例子說明了。
索引下推#
索引下推優化（index condition pushdown)， 可以在聯合索引遍歷過程中，對聯合索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。
對於聯合索引（a, b），在執行 select * from table where a > 1 and b = 2語句的時候，只有 a 字段能用到索引，那在聯合索引的 B+Tree 找到第一個滿足條件的主鍵值（ID 為 2）後，判斷 b 是否 = 2，如果不滿足條件直接過濾。
索引區分度#
越靠前的字段被用於索引過濾的概率越高，實際開發工作中建立聯合索引時，要把區分度大的字段排在前面，這樣區分度大的字段越有可能被更多的 SQL 使用到。
區分度就是某個字段 column 不同值的個數「除以」表的總行數，計算公式如下：
比如，性別的區分度就很小，不適合建立索引或不適合排在聯合索引列的靠前的位置，而 UUID 這類字段就比較適合做索引或排在聯合索引列的靠前的位置。
聯合索引進行排序#
針對下面這條 SQL，怎麼通過索引來提高查詢效率呢？
select * from order where status = 1 order by create_time asc
更好的方式給 status 和 create_time 列建立一個聯合索引，因為這樣可以避免 MySQL 資料庫發生文件排序。
因為在查詢時，如果只用到 status 的索引，但是這條語句還要對 create_time 排序，這時就要用文件排序 filesort，也就是在 SQL 執行計劃中，Extra 列會出現 Using filesort。
所以，要利用索引的有序性，在 status 和 create_time 列建立聯合索引，這樣根據 status 篩選後的數據就是按照 create_time 排好序的，避免在文件排序，提高了查詢效率。
有什麼優化索引的方法？#
什麼時候需要 / 不需要創建索引？#
索引最大的好處是提高查詢速度，但是索引也是有缺點的，比如：
- 需要佔用物理空間，數量越大，佔用空間越大；
- 創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增大；
- 會降低表的增刪改的效率，因為每次增刪改索引，B+ 樹為了維護索引有序性，都需要進行動態維護。
什麼時候適用索引？#
- 字段有唯一性限制的，比如商品編碼；
- 經常用於 WHERE 查詢條件的字段，這樣能夠提高整個表的查詢速度，如果查詢條件不是一個字段，可以建立聯合索引。
- 經常用於 GROUP BY 和 ORDER BY 的字段，這樣在查詢的時候就不需要再去做一次排序了，因為我們都已經知道了建立索引之後在 B+Tree 中的記錄都是排序好的。
什麼時候不需要創建索引？#
- WHERE 條件，GROUP BY，ORDER BY 裡用不到的字段，索引的價值是快速定位，如果起不到定位的字段通常是不需要創建索引的，因為索引是會佔用物理空間的。
- 字段中存在大量重複數據，不需要創建索引，比如性別字段，只有男女，如果資料庫表中，男女的記錄分佈均勻，那麼無論搜索哪個值都可能得到一半的數據。在這些情況下，還不如不要索引，因為 MySQL 還有一個查詢優化器，查詢優化器發現某個值出現在表的數據行中的百分比很高的時候，它一般會忽略索引，進行全表掃描。
- 表數據太少的時候，不需要創建索引；
- 經常更新的字段不用創建索引，比如不要對電商項目的用戶餘額建立索引，因為索引字段頻繁修改，由於要維護 B+Tree 的有序性，那麼就需要頻繁的重建索引，這個過程是會影響資料庫性能的。
有什麼優化索引的方法？#
前綴索引優化#
前綴索引顧名思義就是使用某個字段中字符串的前幾個字符建立索引，那我們為什麼需要使用前綴來建立索引呢？
使用前綴索引是為了減小索引字段大小，可以增加一個索引頁中存儲的索引值，有效提高索引的查詢速度。在一些大字符串的字段作為索引時，使用前綴索引可以幫助我們減小索引項的大小。
不過，前綴索引有一定的局限性，例如：
- order by 就無法使用前綴索引；
- 無法把前綴索引用作覆蓋索引；
覆蓋索引優化#
假設我們只需要查詢商品的名稱、價格，有什麼方式可以避免回表呢？
我們可以建立一個聯合索引，即「商品 ID、名稱、價格」作為一個聯合索引。如果索引中存在這些數據，查詢將不會再次檢索主鍵索引，從而避免回表。
主鍵索引最好是自增的#
如果我們使用自增主鍵，那麼每次插入的新數據就會按順序添加到當前索引節點的位置，不需要移動已有的數據，當頁面寫滿，就會自動開闢一個新頁面。因為每次插入一條新記錄，都是追加操作，不需要重新移動數據，因此這種插入數據的方法效率非常高。
如果我們使用非自增主鍵，由於每次插入主鍵的索引值都是隨機的，因此每次插入新的數據時，就可能會插入到現有數據頁中間的某個位置，這將不得不移動其它數據來滿足新數據的插入，甚至需要從一個頁面複製數據到另外一個頁面，我們通常將這種情況稱為頁分裂。頁分裂還有可能會造成大量的內存碎片，導致索引結構不緊湊，從而影響查詢效率。
索引最好設置為 NOT NULL#
第一原因：索引列存在 NULL 就會導致優化器在做索引選擇的時候更加複雜，更加難以優化，因為可為 NULL 的列會使索引、索引統計和值比較都更複雜，比如進行索引統計時，count 會省略值為 NULL 的行。
第二個原因：NULL 值是一個沒意義的值，但是它會佔用物理空間，所以會帶來的存儲空間的問題，因為 InnoDB 存儲記錄的時候，如果表中存在允許為 NULL 的字段，那麼行格式中至少會用 1 字節空間存儲 NULL 值列表。
防止索引失效#
發生索引失效的情況：
- 當我們使用左或者左右模糊匹配的時候，也就是 like % xx 或者 like % xx% 這兩種方式都會造成索引失效；
- 當我們在查詢條件中對索引列做了計算、函數、類型轉換操作，這些情況下都會造成索引失效；
- 聯合索引要能正確使用需要遵循最左匹配原則，也就是按照最左優先的方式進行索引的匹配，否則就會導致索引失效。
- 在 WHERE 子句中，如果在 OR 前的條件列是索引列，而在 OR 後的條件列不是索引列，那麼索引會失效。
哪種 count 性能最好？#
COUNT (*) = COUNT (1) < COUNT (字段)（存在二級索引）< COUNT (主鍵字段)（僅存在主鍵索引）< COUNT（非主鍵字段）（不存在二級索引）
count 是什麼？#
count () 是一個聚合函數，函數的參數不僅可以是字段名，也可以是其他任意表達式，該函數作用是統計符合查詢條件的記錄中，函數指定的參數不為 NULL 的記錄有多少個。
count (主鍵字段) 執行過程是怎樣的？#
在通過 count 函數統計有多少個記錄時，MySQL 的 server 層會維護一個名叫 count 的變量。
server 層會循環向 InnoDB 讀取一條記錄，如果 count 函數指定的參數不為 NULL，那麼就會將變量 count 加 1，直到符合查詢的全部記錄被讀完，就退出循環。最後將 count 變量的值發送給客戶端。
InnoDB 是通過 B+ 樹來保存記錄的，根據索引的類型又分為聚簇索引和二級索引，它們區別在於，聚簇索引的葉子節點存放的是實際數據，而二級索引的葉子節點存放的是主鍵值，而不是實際數據。
如果表裡只有主鍵索引，沒有二級索引時，那麼，InnoDB 循環遍歷聚簇索引，將讀取到的記錄返回給 server 層，然後讀取記錄中的 id 值，就會 id 值判斷是否為 NULL，如果不為 NULL，就將 count 變量加 1。
但是，如果表裡有二級索引時，InnoDB 循環遍歷的對象就不是聚簇索引，而是二級索引。
這是因為相同數量的二級索引記錄可以比聚簇索引記錄佔用更少的存儲空間，所以二級索引樹比聚簇索引樹小，這樣遍歷二級索引的 I/O 成本比遍歷聚簇索引的 I/O 成本小，因此「優化器」優先選擇的是二級索引。
count (1) 和 count (*) 執行過程是怎樣的#
count (1) 和 count ( * ) 執行過程基本一致，因為 count ( * ) = count (0)。
InnoDB 循環遍歷聚簇索引（主鍵索引），將讀取到的記錄返回給 server 層，但是不會讀取記錄中的任何字段的值，因為 count 函數的參數是 1，不是字段，所以不需要讀取記錄中的字段值。參數 1 很明顯並不是 NULL，因此 server 層每從 InnoDB 讀取到一條記錄，就將 count 變量加 1。
可以看到，count (1) 相比 count (主鍵字段) 少一個步驟，就是不需要讀取記錄中的字段值，所以通常會說 count (1) 執行效率會比 count (主鍵字段) 高一點。
count (字段) 執行過程是怎樣的#
count (字段) 的執行效率是最差的因為要全表掃描
MYISAM 和 innoDB 的區別
使用 MyISAM 引擎時，執行 count 函數只需要 O (1) 複雜度，這是因為每張 MyISAM 的數據表都有一個 meta 信息有存儲了 row_count 值，由表級鎖保證一致性，所以直接讀取 row_count 值就是 count 函數的執行結果。
如何優化 count (*)？#
面對大表的記錄統計，count 的執行效率很差，比如表 t_order 共有 1200+ 萬條記錄，也創建了二級索引，但是執行一次 select count (*) from t_order 要花費差不多 5 秒！
如何提高效率呢？
第一種，近似值
如果你的業務對於統計個數不需要很精確，比如搜索引擎在搜索關鍵詞的時候，給出的搜索結果條數是一個大概值。
這時，我們就可以使用 show table status 或者 explain 命令來表進行估算。
第二種，額外表保存計數值
如果是想精確的獲取表的記錄總數，我們可以將這個計數值保存到單獨的一張計數表中。
當我們在數據表插入一條記錄的同時，將計數表中的計數字段 + 1。也就是說，在新增和刪除操作時，我們需要額外維護這個計數表。