---

MySQL中，索引是儲存在引擎層而不是伺服器層實現的。

B-Tree索引，它使用B-Tree資料結構來儲存資料。
而MySQL的B-Tree索引雖然名字是BTree，但是InnoDB使用的是B+Tree資料結構實現的。

索引的優點

1. 索引大大減少了伺服器需要掃描的資料量
2. 索引可以幫助伺服器避免排序和臨時表
3. 索引可以將隨機I/O變為順序I/O

索引是最好的解決方案麼？

索引並不總是最好的解決方案。只有當索引幫助儲存引擎快速查詢到記錄帶來的好處大於其額外的工作時，索引才有效的。

1. 對於非常小的表，大部分情況下簡單的全表掃描更高效
2. 對於中到大型的表，索引就非常有效
3. 但對於特大型的表，建立和使用索引的代價將隨之增長，這種情況下，需要一種技術可以直接區分出查詢需要的一組資料，而不是一條記錄一條記錄的匹配。例如可以使用分割區技術。對於TB級別的資料，定位單條記錄意義不大，所以經常會使用塊級別後設資料技術來代替索引

https://juejin.im/post/6844903845554814983

高效能的索引策略

正確的建立和使用索引是實現高效能查詢的基礎。

獨立的列

如果查詢中的列不是獨立的，則MySQL不會使用索引。獨立的列指索引列不能是表示式的一部分，也不能是函數的引數。我們應該簡化WHERE條件，始終將索引列單獨放在比較符號的一側

多列索引

在多個列上建立獨立的單列索引大部分情況下不能提高MySQL的查詢效能。MySQL5.0和更新版本引入了一種叫「索引合併」的策略，一定程度上可以使用表上的多個單列索引來定位指定的行。

即在MySQL5.0後，MySQL並不是一次查詢只能使用一個索引了，可以同時使用多個索引

MySQL會使用這類技術優化複雜查詢，但實際上更多時候說明了表上的索引建得很糟糕：

1. 當出現伺服器對多個索引做相交操作時（通常有多個AND條件），通常意味著需要一個包含所有相關列的多列索引，而不是多個獨立的單列索引
2. 當伺服器需要對多個索引做聯合操作（通常有多個OR條件），通常需要耗費大量CPU和記憶體資源在演演算法的快取、排序和合並操作上。特別是當其中有些索引的選擇性不高，需要合併掃描返回的大量資料的時候
3. 更重要的是，優化器不會吧這些計算到查詢成本中，優化器只關心隨機頁面讀取。這會使得查詢的成本被「低估「，導致該執行計劃還不如走全表掃描。這樣做不但會消耗更多的CPU和記憶體資源，還可能會影響查詢的並行性。

選擇合適的索引列順序

正確的順序依賴於使用該索引的查詢，並且同時需要考慮如何更好的滿足排序和分組的需要。

在一個多列索引中，索引列的順序意味著索引首先按照最左列進行排序，其次是第二列。。。

對於如何選擇索引的列順序有一個經驗法則：將選擇性最高的列放到索引最前列

聚簇索引

聚簇索引並不是一種單獨的索引型別，而是一種資料儲存方式。具體的細節依賴於其實現方式，但InnoDB的聚簇索引實際上在同一個結構中儲存了B-Tree索引和資料行。

InnoDB將通過主鍵聚集資料。
如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替。如果沒有這樣的索引，InnoDB會隱式定義一個主鍵來作為聚簇索引。
InnoDB只聚集在同一個頁面中的記錄。

聚集的資料有一些重要的優點：

• 可以把相關資料儲存在一起，
• 資料存取更快。使用覆蓋索引掃描的查詢可以直接使用頁節點中的主鍵值

同時，聚簇索引也有一些缺點：

• 聚簇資料最大限度地提高了I/O密集型應用的效能，但如果資料全部都放在記憶體中，則存取的順序就沒那麼重要了，聚簇索引也就沒什麼優勢了。
• 插入速度嚴重依賴於插敘順序。按照主鍵的順序插入是載入資料到InnoDB表中速度最快的方式。但如果不是按照主鍵順序載入資料，那麼在載入完成後最好使用OPTIMIZE TABLE命令重新組織一下表。
• 更新聚簇索引列的代價很高，因為會強制InnoDB將每個被更新的行移動到新的位置
• 基於聚簇索引的表在插入新行，或者主鍵被更新導致需要移動行的時候，可能面臨「頁分裂」的問題。當行的主鍵值要求必須將這一行插入到某一個已滿的頁中時，儲存引擎會將該頁分裂成兩個頁面來容納該行，這就是一次頁分裂操作。頁分裂會導致表佔用更多的磁碟空間。
• 聚簇索引可能導致全表掃描變慢，尤其是行比較稀疏，或者由於頁分裂導致資料儲存不連續的時候。
• 二級索引（非聚簇索引）可能比想象的要更大，因為在二級索引的葉子節點包含了參照行的主鍵列。
• 二級索引存取需要兩次索引查詢，而不是一次。因為二級索引葉子節點儲存的不是指向行的物理位置的指標，而是行的主鍵值。所以二級索引查詢行，儲存引擎需要找到二級索引的葉子節點獲得對應的主鍵值，然後根據這個值去聚簇索引中查詢到對應的行。

InnoDB的二級索引和聚簇索引很不相同。
InnoDB二級索引的葉子節點中儲存的不是「行指標」，而是主鍵值，並以此作為指向行的「指標」。
這樣策略減少了當行移動或者資料頁分裂時二級索引的維護工作。只用主鍵值當做指標會讓二級索引佔用更多的空間，換來的好處是，InnoDB在移動行時無須更新二級索引中的這個「指標」

在InnoDB表中不按主鍵順序插入行的缺點

新行的主鍵值不一定比之前插入的大，所以InnoDB無法簡單的總是把新行插入到索引的最後，而是需要為新的行尋找合適的位置——通常是已有資料的中間位置——並且分配空間。這會增加很多額外的工作，並導致資料分佈不夠優化。下面是總結的一些缺點：

• 寫入的目標頁可能已經刷到磁碟上並從快取中移除，或者是還沒有被載入到快取中，InnoDB在插入之前不得不先找到並從磁碟讀取目標頁到記憶體中。這將導致大量的隨機I/O。
• 因為寫入是亂序的，InnoDB不得不頻繁的做頁分裂操作，以便為新的行分配空間。頁的分裂會導致大量資料移動，一次插入最少需要修改三個頁而不是一個頁
• 由於頻繁的頁分裂，頁會變得稀疏並被不規則地填充，所以最終資料會有碎片。

在把這些隨機值載入到聚簇索引以後，也許需要做一次OPTIMIZE TABLE來重建表並優化頁的填充

順序的主鍵什麼時候造成更壞的結果

對於高並行工作負載，在InnoDB中按主鍵順序插入可能會造成明顯的爭用。主鍵的上界會成為「熱點」。因為所有的插入都發生在這裡，所以並行插入可能導致間隙鎖競爭。另一個熱點可能是AUTO_INCREMENT鎖機制；如果遇到這個問題，則可能需要重新考慮設計表或者應用，或者更改innodb_autoinc_lock_mode設定。

冗餘和重複索引

MySQL允許在相同列上建立多個索引，無論是有意的還是無意的。MySQL需要單獨維護重複索引，並且優化器在優化查詢的時候也需要逐個的進行考慮，這會影響效能。

重複索引指的是在相同的列上按照相同的順序建立的相同型別的索引。應該避免這樣建立重複索引，發現後也應該立即移除

如果索引型別不同，不算是重複索引。比如KEY和FULLTEXT KEY