文章目錄
- 索引的優點
- 索引是最好的解決方案么？
- 高性能的索引策略
- 獨立的列
- 多列索引
- 選擇合適的索引列順序
- 聚簇索引
- 在InnoDB表中不按主鍵順序插入行的缺點
- 順序的主鍵什么時候造成更壞的結果
- 冗余和重復索引
MySQL中，索引是存盤在引擎層而不是服務器層實作的，
B-Tree索引，它使用B-Tree資料結構來存盤資料，
而MySQL的B-Tree索引雖然名字是BTree，但是InnoDB使用的是B+Tree資料結構實作的，
索引的優點
- 索引大大減少了服務器需要掃描的資料量
- 索引可以幫助服務器避免排序和臨時表
- 索引可以將隨機I/O變為順序I/O
索引是最好的解決方案么？
索引并不總是最好的解決方案，只有當索引幫助存盤引擎快速查找到記錄帶來的好處大于其額外的作業時，索引才有效的，
- 對于非常小的表，大部分情況下簡單的全表掃描更高效
- 對于中到大型的表，索引就非常有效
- 但對于特大型的表，建立和使用索引的代價將隨之增長，這種情況下，需要一種技術可以直接區分出查詢需要的一組資料，而不是一條記錄一條記錄的匹配，例如可以使用磁區技術，對于TB級別的資料，定位單條記錄意義不大，所以經常會使用塊級別元資料技術來代替索引
https://juejin.im/post/6844903845554814983
高性能的索引策略
正確的創建和使用索引是實作高性能查詢的基礎，
獨立的列
如果查詢中的列不是獨立的，則MySQL不會使用索引，獨立的列指索引列不能是運算式的一部分，也不能是函式的引數，我們應該簡化WHERE條件，始終將索引列單獨放在比較符號的一側
多列索引
在多個列上建立獨立的單列索引大部分情況下不能提高MySQL的查詢性能，MySQL5.0和更新版本引入了一種叫“索引合并”的策略，一定程度上可以使用表上的多個單列索引來定位指定的行，
即在MySQL5.0后，MySQL并不是一次查詢只能使用一個索引了，可以同時使用多個索引
MySQL會使用這類技術優化復雜查詢，但實際上更多時候說明了表上的索引建得很糟糕：
- 當出現服務器對多個索引做相交操作時（通常有多個
AND條件），通常意味著需要一個包含所有相關列的多列索引，而不是多個獨立的單列索引 - 當服務器需要對多個索引做聯合操作（通常有多個
OR條件），通常需要耗費大量CPU和記憶體資源在演算法的快取、排序和合并操作上，特別是當其中有些索引的選擇性不高，需要合并掃描回傳的大量資料的時候 - 更重要的是，優化器不會吧這些計算到
查詢成本中，優化器只關心隨機頁面讀取，這會使得查詢的成本被“低估“，導致該執行計劃還不如走全表掃描，這樣做不但會消耗更多的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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/158526.html
標籤：其他
上一篇：記一次排序規則utf8_genera_ci與utf8mb4_bin的區別引發的資料丟失問題
下一篇：Mysql查詢練習