MySQL優化學習筆記之索引

gogooh發表於2018-10-22
MySql優化筆記索引

索引是什麼

  • MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取資料的資料結構。那麼索引的本質就是：索引是資料結構。
  • 在資料之外，資料庫系統還維護著滿足特定查詢演算法的資料結構，這些資料結構以某種方式引用（指向）資料，這樣就可以在這些資料結構上實現高階查詢演算法。這種資料結構，就是索引。
  • 索引的目的在於提高查詢效率，可以類比字典。可以簡單理解為“排好序的快速查詢資料結構”。
  • 一般來說索引本身也很大，不可能全部儲存在記憶體中，因此索引往往以索引檔案的形式儲存的磁碟上。
  • 我們平常所說的索引，如果沒有特別指明，都是指B+樹（多路搜尋樹，並不一定是二叉樹）結構組織的索引。其中聚集索引，次要索引，複合索引，字首索引，唯一索引預設都是使用B+樹索引，統稱索引。當然，除了B+樹這種型別的索引之外，還有雜湊索引(hash index)等。

索引的優點

  • 類似大學圖書館建書索引，提高資料檢索的效率，降低資料庫的IO成本
  • 通過索引列對資料進行排序，降低資料排序的成本，降低了CPU的消耗

索引的缺點

  • 實際上索引也是一張表，該表儲存了主鍵與索引欄位，並指向實體表的記錄，所以索引列也是要佔用空間的
  • 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不進要儲存資料，還要儲存一下索引檔案每次更新新增了索引列的欄位，都會調整因為更新所帶來的鍵值變化後的索引資訊
  • 索引只是提高效率的一個因素，如果MySQL有大資料量的表，就需要花時間研究建立最優秀的索引，或優化查詢語句。

索引分類

  • 單值索引，即一個索引只包含單個列，一個表可以有多個單列索引
  • 唯一索引，索引列的值必須唯一，但允許有空值
  • 複合索引，即一個索引包含多個列

索引的基本語法

#建立索引
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(lenght));
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length));

#刪除
DROP INDEX [indexName] ON mytable;

#檢視
SHOW INDEX FROM table_name;

#新增資料表索引的幾種方式：

##該語句新增一個主鍵，這意味著索引值必須是唯一的，且不能為NULL
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

##這條語句建立索引的值必須是唯一的（除了NULL外，NULL可能會出現多次）
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)

##新增普通索引，索引值可出現多次
ALTER TABLE tbl_name ADD INDEX index_name (column_list)

##該語句指定了索引為FULLTEXT，用於全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

複製程式碼

MySQL索引結構

  • BTree索引
  • Hash索引
  • full-text全文索引
  • R-Tree索引

哪些情況適合建立索引

  • 主鍵自動建立唯一索引
  • 頻繁作為查詢條件的欄位應該建立索引
  • 查詢中與其他表關聯的欄位，外來鍵關係建立索引
  • 單鍵/組合索引的選擇問題，在高併發下傾向建立組合索引
  • 查詢中排序的欄位，排序欄位若通過索引去訪問將大大提高排序速度
  • 查詢中統計或者分組欄位

哪些情況不適合建立索引

  • 表記錄太少
  • 頻繁更新的欄位不適合建立索引，因為每次更新不單單是更新了記錄還會更新索引
  • where條件裡用不到的欄位不建立索引
  • 經常增刪改的表，新增索引雖然會提高查詢速度，但是同時也會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為在更新表時，MySQL不進會儲存資料，還會儲存索引檔案。
  • 資料重複且分佈平均的表欄位，因此應該只為最經常查詢和最經常排序的資料列建立索引。注意，如果某個資料列包含許多重複的內容，為它建立索引就沒有太大的實際效果。

相關文章