mysql索引總結

NewHongjay發表於2018-09-17
MySql索引

最近一直在看mysql相關的知識，恰好索引有事資料庫基本知識中比較重要的一個，看了一些部落格書籍，現在大概對索引有一個比較清晰的瞭解，所以綜合整理了一下來分享給大家。

開頭

  • mysql的Innodb引擎目前支援以下幾種常見的索引
    • B+樹索引
    • 全文索引
    • 雜湊索引
  • 雜湊索引看名字就知道是基於雜湊演算法，Innodb引擎支援的雜湊索引是自適應的，就是說Innodb引擎會根據資料表的使用情況自動的為表建立雜湊索引，所以是不需要我們去人為操作的，所以不是講解的重點。本文主要著重講解B+樹索引。
  • B+樹索引並不能直接找到一個給定鍵值的具體行，而是找到資料行所在的頁，然後找到的頁讀進記憶體，在記憶體中進行比較最終找到確定的那一行。

B+樹的簡單介紹

  • B+數是一種平衡查詢樹（平衡可對比二叉平衡樹）具體構造如下圖

image.png

  • B+樹的非葉子節點不儲存資料，葉子節點才會儲存資料，每一個葉子節點都表示一個頁，一頁裡面有多行資料，所以很好理解上面所說的B+樹索引並不能直接找到一個給定鍵值的具體行，而是找到資料行所在的頁，因為B+樹查詢只能定位到葉子節點，葉子節點內的比較就需要讀進記憶體，在記憶體中進行比較最終找到確定的那一行。
  • 同時B+樹因為需要維持平衡，所以增刪葉子節點的資料時需要根據情況來旋轉或者分裂節點等操作來進行B+樹的平衡維持操作（具體怎麼操作這裡不做具體講解有興趣可以看這篇部落格https://zhuanlan.zhihu.com/p/27700617）。

索引提高查詢速度的原理

  • 其實就是將無序的資料變成有序(相對)：
    image.png
  • 要找到id為8的記錄簡要步驟：
    image.png
  • 很明顯的是：沒有用索引我們是需要遍歷雙向連結串列來定位對應的頁，現在通過**“目錄”**就可以很快地定位到對應的頁上了！

聚集索引

  • 當一張表存在主鍵時就以主鍵構造一顆B+樹建立聚集索引
  • 一張表只能有一個聚集索引，因為實際的資料頁只能按照一顆B+樹來進行排序
  • 聚焦索引頁上存放的是完整的每行的記錄，在非聚集索引的B+樹上面存放的是指向主鍵頁的地址和列值。
  • 聚集索引的儲存並不是物理連續的，而是邏輯連續的因為聚集索引的頁與頁之間是雙向連結串列連線，頁按照主鍵的順序排序，每個頁裡面的記錄也是通過雙鏈錶連結，所以物理儲存上可以不按照主鍵順序排序。
  • 聚集索引對主鍵的排序查詢和範圍查詢速度很快，並且頁上就是完整的行資料，不需要進行二次查詢。對聚集主鍵的範圍查詢只需要通過葉子節點的上層節點就可以找到頁的範圍，直接取頁資料即可。

輔助索引（非聚集索引、二級索引）

  • 上面說了非聚集索引的頁上面只儲存了主鍵地址和單獨的列值，索引當根據非聚集索引查詢的時候如果返回欄位涉及到該列以外的其他列就會在根據主鍵的指標定位到主鍵索引所在的頁查詢查詢其他列資料（所以到主鍵查詢這個過程會有一定的查詢效能損失，這個過程也叫做回表）。
  • 一個資料表只有一個聚集索引，但是可以有很多個非聚集索引。（但是過多的不必要的索引會影響資料庫效能）

什時候應該新增索引

  • 不是所有的查詢條件出現的列都需要新增索引。對於什麼時候新增B+樹索引。一般的經驗是，在訪問表中很少一部分時使用B+樹索引才有意義。對於性別欄位、地區欄位、型別欄位，他們可取值範圍很小，稱為低選擇性。如
SELECT * FROM student WHERE sex='M'
複製程式碼

按性別進行查詢時，可取值一般只有M、F。因此SQL語句得到的結果可能是該表50%的資料(假如男女比例1:1)這時新增B+樹索引是完全沒有必要的。相反，如果某個欄位的取值範圍很廣，幾乎沒有重複，屬於高選擇性。則此時使用B+樹的索引是最合適的。例如對於姓名欄位，基本上在一個應用中不允許重名的出現

  • 怎樣檢視索引是否有高選擇性？通過SHOW INDEX結果中的列Cardinality來觀察。非常關鍵，表示所以中不重複記錄的預估值，需要注意的是Cardinality是一個預估值，而不是一個準確值基本上使用者也不可能得到一個準確的值
  • 可以參考cardinality值來決定是否應該刪除該索引（如果cardinality值很小可以考慮刪除該索引）。

覆蓋索引

  • 覆蓋索引就是在從輔助索引中就可以得到查詢記錄不需要查詢聚集索引（查詢的列和建立索引的列是對應的）

索引的最左匹配原則

  • 在說索引最左匹配原則之前，需要了解一個東西就是聯合索引，就是有一個列a，一個列b，可以對a,b兩列建一個索引（a，b）(當然兩個以上也是可以的，在索引的內部就會根據（a,b）來排序，一般來說是先根據a排序，然後再根據b排序）
  • 當根據a的值來做條件查詢的時候可以使用使用(a,b)索引(因為(a,b)索引是先根據a索引排序再根據b索引排序)，但是單獨使用b做條件查詢的時候就不能使用(a,b)索引（原因同上，只有在a條件確定的情況下才開始使用b）
  • 同時索引只能用於查詢key是否存在（相等），遇到範圍查詢(>、<、between、like左匹配)等就不能進一步匹配了，後續退化為線性查詢。（比如有（a,b,c,d）聯合索引當查詢條件為a=1 ,b=2,c>8，d=9）這個時候根據索引建立順序就會依次匹配到a,b到c的時候就不會繼續往下匹配，即使d不是範圍查詢，匹配玩a，b索引後就退化為線性查詢。

索引總結

  • 最左字首匹配原則，MySQL會一直向右匹配直到遇到範圍查詢（>,<,BETWEEN,LIKE）就停止匹配。

  • 儘量選擇區分度高的列作為索引，區分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示欄位不重複的比率，比率越大我們掃描的記錄數就越少。

  • 索引列不能參與計算，儘量保持列“乾淨”。比如，FROM_UNIXTIME(create_time) = '2016-06-06' 就不能使用索引，原因很簡單，B+樹中儲存的都是資料表中的欄位值，但是進行檢索時，需要把所有元素都應用函式才能比較，顯然這樣的代價太大。所以語句要寫成 ： create_time = UNIX_TIMESTAMP('2016-06-06')。

  • 儘可能的擴充套件索引，不要新建立索引。比如表中已經有了a的索引，現在要加（a,b）的索引，那麼只需要修改原來的索引即可。

  • 單個多列組合索引和多個單列索引的檢索查詢效果不同，因為在執行SQL時，MySQL只能使用一個索引，會從多個單列索引中選擇一個限制最為嚴格的索引。

  • 參考資料：

相關文章