mysql索引是什麼及怎麼使用的？整理的很詳細

學習mysql時，經常會遇見索引吧，到底什麼是索引呢？之前我也只是模模糊糊的會使用，但是讓我解釋出來還真有點困難呢，所以呢趁著空閑時間就查閱了下書籍，以防以後有人問我，我就整理寫了下，有點印象總歸是好的，說不會也挺尷尬的，畢竟也接觸這些有些年頭了，廢話不說了，apache php mysql 講重點：

begin!

一、索引是什麼

1.索引簡介
索引其實是以檔案形式儲存在磁碟上的一種資料結構，索引檢索需要磁碟I/O操作。與主存不同，磁碟I/O存在機械運動耗費，因此磁碟I/O的時間消耗是巨大的。

2.IO簡介
IO在電腦中指 輸入與輸出，由於程式和運行時資料是在記憶體中駐留，由CPU這個超快的計算核心來執行，涉及到資料交換的地方，通常是磁碟、網路等，就需要IO介面。生活例子: 需要記住關鍵的事情都需要寫在筆記本上，需要的時候在拿出來看，每次去筆記本上看記錄時就是IO，如果記憶好的人會記住這件事情，直接就能讀出來，這就是緩衝(電腦裡面更加不能一直儲存)。

二、索引演算法

1.資料庫基本都是使用B+Tree演算法實現

2.資料庫索引是使用磁碟I/O次數來評價索引結構的優劣

3.B-Tree
(1) B-Tree的定義，可知檢索一次最多需要訪問h-1個節點（根節點常駐記憶體）。資料庫系統的設計者巧妙利用了磁碟預讀原理，將一個節點的大小設為等於一個頁，這樣每個節點只需要一次I/O就可以完全載入

(2) 實際實現B-Tree還需要使用如下技巧：每次建立節點時，直接申請一個頁的空間，這樣就保證一個節點物理上也儲存在一個頁裡，加之電腦儲存分配都是按頁對齊的，就實現了一個node只需一次I/O

(3) 採用B-Tree儲存結構，搜尋時I/O次數一般不會超過3次，所以用B-Tree作為索引結構效率是非常高的，但是B-tree中的節點根據實際情況可以包含大量的關鍵字資訊和分支

4.B+Tree
(1) B-Tree的搜尋複雜度為O(h)=O(logdN)，所以樹的出度d越大，深度h就越小，I/O的次數就越少。B+Tree恰恰可以增加出度d的寬度，因為每個節點大小為一個頁大小，所以出度的上限取決於節點內key和data的大小

(2) 由於B+Tree的內節點去掉了data，因此可以擁有更大的出度，從而擁有更好的效能

三、叢集索引與非叢集索引

1.聚簇索引
(1) 聚簇索引的資料的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那麼對應的資料一定也是相鄰地存放在磁碟上的。聚簇索引要比非聚簇索引查詢效率高很多

(3) 每個表只能有一個聚簇索引，因為一個表中的記錄只能以一種物理順序存放

(4) Innodb的預設索引

2.非聚簇索引

(1) 非叢集索引，類似於圖書的附錄，那個專業術語出現在哪個章節，這些專業術語是有順序的，但是出現的位置是沒有順序的。但是，一個表可以有不止一個非聚簇索引

(2) 實現原理就是使用葉子節點儲存引用行的主鍵(可以說是叢集索引)

(3) 叢集索引是非聚簇索引的一種索引，即主+輔索引的索引方式，這種主+輔索引的好處是，當發生資料行移動或者頁分裂時，輔助索引樹不需要更新，因為輔助索引樹儲存的是主索引的主鍵關鍵字，而不是資料具體的物理地址

(4)所以非聚簇索引要訪問兩次索引

四、索引的類型

1.UNIQUE(唯一索引)：不可以出現相同的值，可以有NULL值

2.INDEX(普通索引)：允許出現相同的索引內容

3.PROMARY KEY(主鍵索引)：不允許出現相同的值

4.FULLTEXT INDEX(全文索引)：可以針對值中的某個單詞，但效率很差

5.複合式索引：實質上是將多個欄位建到一個索引裡，列值的組合必須唯一

五、索引技巧

1.索引不會包含有NULL的列

(1) 只要列中包含有NULL值，都將不會被包含在索引中，複合索引中只要有一列含有NULL值，那麼這一列對於此符合索引就是無效的

2.使用短索引

(1)對串列進行索引，如果可以就應該指定一個前置長度。例如，如果有一個char（255）的列，如果在前10個或20個字元內，多數值是唯一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和I/O操作

3.索引列排序

(1) mysql查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此資料庫預設排序可以符合要求的情況下不要使用排序操作，盡量不要包含多個列的排序，如果需要最好給這些列建複合索引

4.like語句操作

(1) 一般情況下不鼓勵使用like操作，如果非使用不可，注意正確的使用方式。like ‘%aaa%’不會使用索引，而like ‘aaa%’可以使用索引

5.不要在列上進行運算

6.不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的

7.索引要建立在經常進行select操作的欄位上

(1) 這是因為，如果這些列很少用到，那麼有無索引並不能明顯改變查詢速度。相反，由於增加了索引，反而降低了系統的維護速度和增大了空間需求

8.索引要建立在值比較唯一的欄位上

9.對於那些定義為text、image和bit資料類型的列不應該增加索引。因為這些列的資料量要麼相當大，要麼取值很少

10.在where和join中出現的列需要建立索引

11.where的查詢條件裡有不等號(where column != …),mysql將無法使用索引

12.如果where字句的查詢條件裡使用了函數(如：where DAY(column)=…),mysql將無法使用索引

13.在join操作中(需要從多個資料表提取資料時)，mysql只有在主鍵和外鍵的資料類型相同時才能使用索引，否則及時建立了索引也不會使用

14.explain可以協助開發人員分析SQL問題,explain顯示了mysql如何使用索引來處理select語句以及串連表,可以協助選擇更好的索引和寫出更最佳化的查詢語句

六、索引與鎖

1.鎖用到索引就是行鎖，如果沒有用到索引就是表鎖，所以操作的資料必須用到鎖才行

(1) 如果沒有建立索引的話，在進行資料選取或者定位的時候是通過全表掃描的形式來進行的，這樣就會形成表鎖，要是有索引的話就會直接定位到指定的行，就是形成行鎖，這裡注意在更新資料時假如沒用到索引也會全表掃描

end

這樣內容大多都是我平時積攢的，有的不清楚的參考了網路和書籍，多多諒解！

相關文章：

mysql索引名稱如何使用，在什麼時候使用

什麼是索引？Mysql目前主要的幾種索引類型

相關視頻：

索引簡單介紹-六天帶你玩轉MySQL視頻教程