索引是什麼意思

“

上一篇我們認識了下什麼是索引，今天我們來聊聊索引的使用和最佳化

”

索引種類

聚簇索引

「主鍵索引」

（PRIMARY KEY）：主鍵索引一般都是在建立表的時候指定，不指定的話會預設生成一個（row_id）

「一個表只有一個主鍵索引」

，特點是

「唯一、非空」

。

「不為空的唯一索引」:

如果存在非空的唯一索引，不會生成row_id

非聚簇索引（二級索引）

「唯一索引」

（UNIQUE）：唯一索引具有的特點就是唯一性，可以在建立表的時候指定，也可以在建立表後建立。

「普通索引」

（INDEX）：普通索引唯一的作用就是加快查詢。

「組合索引」

（ INDEX）：組合索引是建立一個

「多個欄位的索引」

，這個概念是相對於上上面的單列索引而言，組合索引查詢遵循

「最左字首原則」

。

全文索引

底層的資料結構

：全文索引底層使用倒排索引來實現，倒排索引和B+樹索引一樣，都是一種索引結構。這種索引結構會建立一個輔助表，這個“輔助表”裡面儲存單詞和單詞所在的一個或者多個文件之間的對映。這個輔助表通常使用關聯陣列來實現。

比如我們建立一張表

列

中文描述

id

序號

自增主鍵

age

年齡

name

名稱

主鍵索引

：完整的資料在葉子節點上，非葉子節點只有主鍵和頁號等相關資訊

用name 建立

二級索引

：葉子節點包括name和id，非葉子節點

只有name和頁號等相關資訊

用name 和age 建立

組合索引

：葉子節點包括name，age和id，非葉子節點

只有name、age和頁號等相關資訊

關於回表

什麼是回表查詢呢？回表查詢簡單來說

「透過二級索引查詢資料，得不到完整的資料行，需要再次查詢主鍵索引來獲得資料行」

。

例如來查詢：

select * from t where name =‘aa’；

我們用 index_name 這個二級索引查詢有這麼兩個特點：

會使用到

兩個B+樹索引

，一個二級索引，一個聚簇索引。

訪問二級索引使用

順序I/O

，訪問聚簇索引使用

隨機I/O

。

需要回表的記錄越多，使用二級索引的效能就越低，甚至讓某些查詢寧願使用全表掃描也不使用二級索引。比方說name值在Asa～Barlow之間的使用者記錄數量佔全部記錄數量90%以上，那麼如果使用idx_name索引的話，有90%多的id值需要回表，這不是吃力不討好麼，還不如直接去掃描聚簇索引

（也就是

全表掃描

）。

索引最佳化

使用聯合索引

這也是減少回表，回表會增加隨機IO，如上面的例子，我們查詢id和name 就使用name 的二級索引，就不會回表了。因為葉子節點已經有我們想要的資料。

最左匹配原則

如果我們使用 name age 的聯合索引，當where 條件中只有age 的時候，索引是不起作用的。必須有name。

匹配列字首

為某個列建立索引的意思其實就是在對應的B+樹的記錄中使用該列的值進行排序，字串也是排序的。

不在索引上使用表示式

這樣的話用不到建立的索引儲存引擎會依次遍歷所有的記錄，計算這個表示式的值

比如：select * from t where age/2>22 就無法用 index_age

索引列儘量小

‍能用char 不用varchar，能用INT 不用BIGINT，有兩方面的考慮

：

資料型別越小，在查詢時進行的比較操作越快（這是CPU層次的東東）

資料型別越小，索引佔用的儲存空間就越少，在一個數據頁內就可以放下更多的記錄，從而減少磁碟I/O帶來的效能損耗，也就意味著可以把更多的資料頁快取在記憶體中，從而加快讀寫效率。

索引列的區分度要高

因為所有值都一樣就無法排序，無法進行快速查找了，而且如果某個建立了二級索引的列的重複值特別多，那麼使用這個二級索引查出的記錄還可能要做回表操作，這樣效能損耗就更大了。

歡迎關注、點贊、留言、拍磚