1. 首頁 > 生活百科 > >

4 MySQL學習---MySQL索引( 六 )

生活百科


  • 將查詢得到的輔助索引鍵值存放于一個緩存中 ， 這時緩存中的數據是根據輔助索引鍵值排序的 。
  • 將緩存中的鍵值根據RowID進行排序 。
  • 根據RowID的排序順序來訪問實際的數據文件 。
Index Condition PushDown（ICP）優化和Muiti-Range Read一樣，Index Condition PushDown同樣是MySQL5.6開始支持的一種根據索引進行查詢的優化方式 。它能減少在使用輔助索引過濾where條件時的回表次數和減少MySQL Server層和引擎層的交互次數 。在索引組織表中，使用二級索引進行回表的代價相比堆表中是要高一些的 。相關文檔地址：http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html
之前的MySQL數據庫版本不支持Index Condition Pusddown，當進行索引查詢時，首先根據索引來查找記錄 ， 然后再根據where條件來過濾記錄 。在支持index Condition Pushdown后，MySQL數據庫會在取出索引的同時 ， 判斷是否可以進行where條件的過濾 ， 也就是將where的部分過濾操作放在了存儲引擎層 。在某些查詢下 ， 可以大大減少上層SQL層對記錄的索?。╢etch），從而提高數據庫的整體性能 ， 優化支持range、ref、eq_ref、ref_or_null類型的查詢，選擇Index Condition Pushdown優化時，可在執行計劃的列Extra看到Using index condition提示 。
注意事項
  • 索引最用于一個范圍列，如果查詢條件中有兩個或以上的范圍列則無法全用到索引 。范圍條件有：>、<、between、like等 。
  • 建立索引的列不能包含null值 。
  • 查詢的時候，不要在索引列上做任何操作（如計算、函數、手動/自動類型轉換等），會導致索引失效而轉向全表掃描 。（MySQL認為這些操作會改變索引順序）
  • 使用auto_increment關鍵字的列必須有索引 。（只要有索引就行）
  • 業務上具有唯一特性的字段 ， 即使是多個字段的組合，也必須建成唯一索引 。
常見問題主鍵索引就是聚簇索引嗎？答案是否定的 。
注意：聚簇索引決定了數據庫的物理存儲結構 ， 而主鍵只是確定了表格邏輯組織方式 。這二者不可混為一談 。
對于InnoDB存儲引擎而言，主鍵毫無疑問是一個聚簇索引 。但是當一個表沒有主鍵 ， 或者沒有一個索引，Innodb存儲引擎會如何處理？有如下規則：
  • 如果一個主鍵被定義了，那么這個主鍵就是作為聚簇索引，有且僅有一個 。
  • 如果沒有主鍵被定義，那么該表的第一個唯一非空索引被作為聚簇索引 。
  • 如果沒有主鍵也沒有唯一索引，那么innodb內部會生成一個隱藏的主鍵作為聚集索引，這個隱藏的主鍵是一個6個字節的列，改列的值會隨著數據的插入自增 。
  • 自增主鍵會把數據自動向后插入 ， 避免了插入過程中的聚簇索引排序問題 。聚簇索引的排序 ， 必然會帶來大范圍的數據的物理移動，這里面帶來的磁盤IO性能損耗是非常大的 。而如果聚集索引上的值可以改動的話，那么也會觸發物理磁盤上的移動，于是就可能出現page分裂，表碎片橫生 。所以不應該修改聚簇索引 。
為什么InnoDB只在主鍵索引樹的葉子節點存儲了具體數據？為了節省存儲空間，一個表中可能有多個索引，InnoDB都會給每個加了索引的字段生成索引樹 ， 如果每個字段的索引樹多存儲了具體數據，那么這個表的索引數據文件就變得非常巨大 。（數據極度冗余）
為什么MySQL不推薦使用uuid作為主鍵？
  • 使用自增id的內部結構
自增的主鍵的值是順序的，索引InnoDB把每一條記錄都存儲在上一條記錄的后面 。當達到頁面的最大填充因子時候（InnoDB默認的最大填充因子是頁的15/16，會留出1/16的空間留作以后的修改），這樣做有幾個好處：
（1）下一條流就會寫入新的頁中，一旦數據按照這種順序的方式加載 ， 主鍵頁就會近乎于順序的記錄填滿，提升了頁面的最大填充率，不會有頁的浪費 。
（2）新插入的行一定會在原有的最大數據行下一行 ， MySQL定位和尋址很快，不會為計算新行的位置而作出額外的消耗 。
（3）減少了頁分裂和碎片的產生 。
  • 使用uuid的索引內部結構
因為uuid相對順序的自增id來說時毫無規律可言的 ， 新行的值不一定要比之前的主鍵的值要大，所以InnoDb無法做到總是把新行插入到索引的最后 ， 而是需要為新尋找新的合適的位置從而來分配新的空間 。
這個過程需要做很多額外的操作，數據的毫無順序會導致數據分布散亂，將會導致以下問題：

推薦閱讀