這樣總結MySQL索引分類才好

文章簡介

本文將大致介紹索引的類型、InnoDB的索引分類、如何創(chuàng)建索引、使用索引的注意事項等幾個方面記錄索引。由于側重點的不同，本文不會全面介紹索引的知識點，例如二叉樹、平衡二叉樹、B Tree和B + Tree等，后面會單獨針對這幾種數(shù)據(jù)結構，進行深度的分享。導圖連接(點擊底部閱讀原文，就可以打開文檔連接了。)

問題引入

詳細很多程序員在面試的時候，都會被問到這樣一個問題“MySQL中的索引都有哪些”？或許你很自信的啪啪啪答了一大堆，什么唯一索引、普通索引、主鍵索引、聯(lián)合索引等等索引名稱，你還以為自己答得非常完美。此時，你看面試官臉上帶著一臉嫌棄的樣子，你心里瞬間跌入了萬丈深淵似的。

不是因為你沒有答對，而是因為你回答的方式不對。該文將梳理如何回答該問題。

文章提綱

下面的截圖也是文章的大致題綱，寫作的思路也是圍繞該題綱進行。

索引定義

什么是索引，想必大家都很熟悉了。使用的字典目錄這個案例也是非常之多了。這里個人羅列一下自己對為什么使用索引多一個定義吧(不一定準確，屬于個人理解)。

索引是利用數(shù)據(jù)結構的特點，實現(xiàn)一種為了快速檢索數(shù)據(jù)的存儲方式。

存儲引擎

本文的話題是索引，為什么會提出存儲引擎這個東西呢？因為不同的存儲引擎支持的索引也不同。下面羅列出不同的存儲引擎之間的區(qū)別：

hash索引是不需要我們手動設置的，InnoDB存儲引擎的表會自動根據(jù)使用情況，調整為hash索引。其他的索引類型，使用的少，暫時也沒過多的了解，后期針對該文更新時彌補上。

索引分類

索引從數(shù)據(jù)結構上主要分為下面四種索引類型。其中B+Tree索引使用情況也是最多的。后面文章重點也是總結該索引類型。

1. B+Tree 類型索引
2. hash索引
3. 空間索引
4. 全文索引

B+Tree索引

B+Tree是一種多路搜索的數(shù)據(jù)結構類型。它就像一棵樹一樣，有根節(jié)點、葉子結點、枝干等元素。一般在提交到索引的時候，都會提及到二叉樹、平衡二叉樹、B Tree這幾種數(shù)據(jù)結構，至于為什么會提及呢？主要是B+Tree是根據(jù)這幾種數(shù)據(jù)結構演變過來的，因為前面的數(shù)據(jù)結構存在這種種不足。

這里簡單提及一下幾種數(shù)據(jù)結構，后面單獨出一篇文章來總結這幾種數(shù)據(jù)結構。

索引分類

從 B+Tree數(shù)據(jù)結構分類，InnoDB中的索引類型分為聚集索引和非聚集索引。聚集索引包含了主鍵索引，而非聚集索引包含了唯一索引、聯(lián)合索引、前綴索引、復合索引、普通索引。

hash索引

hah索引是一種等值類型的數(shù)據(jù)類型。通過將鍵值進行hash計算，檢索時通過過相同的hash方式進行等值查找的一種存儲策略方式。

這種索引類型在等值上檢索快(不需要像B+tree進行逐級查找，只需要進行一次的hash計算，就等定位到數(shù)據(jù)，檢索快。)，但是也存在諸多不足之處。例如：

1. 不能進行范圍檢索。
2. 不能進行大小比較。
3. 不能進行排序檢索。

使用該索引也不需要手動去定義，InnoDB存儲引擎會根據(jù)表的使用情況，自動生成hash索引，不能通過人為的干預生成hash索引。

定義索引

主鍵索引

定義

由一個或者多個字段組成的索引列，該索引列是唯一的、自增的、不能為null的。

語法

alter table table_name add primary key(column_name)

唯一索引

定義

表中的當前列的值不能重復，但可以為null。

語法

alter table table_name add unique(column_name)

前綴索引

定義

為值的前幾個字符創(chuàng)建索引的一種策略。

語法

alter table add key(column_name(inde_length))

該類索引適用于text，blob，varchar等字符類型，但是建議減少該索引類型的使用。

優(yōu)缺點

1. 便于快速檢索數(shù)據(jù)。
2. 不能使用在order by情況中。
3. 不能使用在group by的情況中。
4. 不能使用在覆蓋索引的情況中。
5. 創(chuàng)建的索引長度，最好是根據(jù)column_name對應的長度來確定。

普通索引

語法

alter table table_name add index index_name(column_name)

聯(lián)合索引

定義

是表中兩個或者兩個以上的索引組成的一個新索引。

語法

create index index_name on table_name(index_name_1,index_name_2)

優(yōu)缺點

1. 減少數(shù)據(jù)檢索范圍。
2. 必須遵循前綴索引原則，則索引會生效。

覆蓋索引

定義

一個索引包含(覆蓋)所有查詢字段的值。

優(yōu)點

1. 減少回表查詢查詢額外的字段值。為什么覆蓋索引會檢索快呢？
2. 索引檢索的數(shù)據(jù)量始終是小于數(shù)據(jù)表的數(shù)據(jù)量。
3. 索引是按照順序檢索的，避免了直接檢索表的隨機IO讀取。
4. 減少系統(tǒng)層面的調用，部分存儲引擎讀取數(shù)據(jù)還需要調用系統(tǒng)層面。
5. 在InnoDB的聚集索引中，可以減少二次索引的開銷。

查看索引

show語法

mysql [email protected]:demo> show index from `user`\G;
2 rows in set
Time: 0.003s
***************************[ 1. row ]***************************
Table         | user
Non_unique    | 0
Key_name      | PRIMARY
Seq_in_index  | 1
Column_name   | id
Collation     | A
Cardinality   | 7
Sub_part      | <null>
Packed        | <null>
Null          |
Index_type    | BTREE
Comment       |
Index_comment |
***************************[ 2. row ]***************************
Table         | user
Non_unique    | 0
Key_name      | idx_mobiel
Seq_in_index  | 1
Column_name   | mobile
Collation     | A
Cardinality   | 7
Sub_part      | <null>
Packed        | <null>
Null          | YES
Index_type    | BTREE
Comment       |
Index_comment |

explain語法

explain語句可以分析出SQL語句是否使用了索引、檢索的類型等情況。

mysql [email protected]:demo> explain select * from `user` where id = 1\G;
1 row in set
Time: 0.003s
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | user
partitions    | <null>
type          | const
possible_keys | PRIMARY
key           | PRIMARY
key_len       | 4
ref           | const
rows          | 1
filtered      | 100.0
Extra         | <null>

注意事項

創(chuàng)建索引

1. 選擇性低的字段不要創(chuàng)建字段。
2. 很少查詢的列不要創(chuàng)建索引。
3. 大數(shù)據(jù)類型的字段不要創(chuàng)建索引。
4. 盡量避免列不要使用null，盡可能的設置為not null。盡可能使用空值來代替這種情況。

使用索引

1. 通過索引掃描的行記錄數(shù)數(shù)超過全表的30%，優(yōu)化器不會走索引，而且會變成全表掃描。
2. 聯(lián)合索引中，第一個查詢條件不是最左索引列不會走索引。
3. 模糊查詢條件列最左以"%"開始的。
4. 兩個單列索引，一個用于檢索，一個用于排序。這種情況是只能使用一個索引，因為SQL查詢語句中最多只能使用一個索引，推薦使用聯(lián)合索引來替代。
5. 查詢列上面使用了聚合函數(shù)，也不會走索引。