索引

概念

索引其實是一種數(shù)據(jù)結(jié)構(gòu)，能夠幫助我們快速的檢索數(shù)據(jù)庫中的數(shù)據(jù)

---

一、索引的分類

索引主要分為：普通索引、唯一索引、主鍵索引、組合索引、全文索引

1、普通索引

是最基本的索引，它沒有任何限制。

2、唯一索引

索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一

3、主鍵索引

它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候指定了主鍵，就會創(chuàng)建主鍵索引， CREATE INDEX不能用來創(chuàng)建主鍵索引，使用 ALTER TABLE來代替。

4、組合索引(復(fù)合索引)

一個索引包含多個列，實際開發(fā)中推薦使用復(fù)合索引。

注：如果我們創(chuàng)建了(name, age,xb)的復(fù)合索引，那么其實相當(dāng)于創(chuàng)建了(name, age,xb)、(name, age)、(name)三個索引，這被稱為最佳左前綴
特性。因此我們在創(chuàng)建復(fù)合索引時應(yīng)該將最常用作限制條件的列放在最左邊，依次遞減。

5、全文索引

FULLTEXT索引用于全文搜索。用于搜索很長一篇文章的時候，效果最好。用在比較短的文本，如果就一兩行字的，普通的 INDEX 也可以。
只有InnoDB和 MyISAM存儲引擎支持 FULLTEXT索引和僅適用于 CHAR， VARCHAR和 TEXT列。

---

二、索引的優(yōu)缺點

優(yōu)點

• 提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫IO成本。

• 通過索引對數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)的排序成本，降低CPU的消耗。

缺點

• 當(dāng)對表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時候，索引也要動態(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。
• 索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大

---

三、索引創(chuàng)建

1、何時要創(chuàng)建索引

• 主鍵自動創(chuàng)建唯一索引
• 作為條件進(jìn)行較頻繁的查詢的字段
• 查詢中排序的字段，查詢中統(tǒng)計或者分組的字段?！?/li>

2、何時不要創(chuàng)建索引

• 表記錄字段太少
• 頻繁進(jìn)行增刪改的字段
• 唯一性太差的字段，不適合單獨創(chuàng)建索引。即使頻繁作為查詢條件 比如性別，民族，政治面貌（可能總共就是那么幾個或幾十個值重復(fù)使用的字段）

---

四、索引使用的注意事項

• 模糊查詢

  盡量少使用模糊查詢，如果要使用那么，通配符%可以出現(xiàn)在結(jié)尾，不能在開頭。

  • name like ‘張%’ ，索引有效
  • name like ‘%張’ ,索引無效，全表查詢

• or 會引起全表掃描

• 不要使用NOT、!=、NOT IN、NOT LIKE等

• 盡量少使用select *，而是根據(jù)需求來選擇需要顯示的字段

• 索引不會包含有null值的列

  只要列中包含有null值都將不會被包含在索引中，復(fù)合索引中只要有一列含有null值，那么這一列對于此復(fù)合索引就是無效的。所以我們在數(shù)據(jù)庫設(shè)計時不要讓字段的默認(rèn)值為null。

• 不要在列上進(jìn)行運算，這將導(dǎo)致索引失效而進(jìn)行全表掃描

• 使用短索引

  對串列進(jìn)行索引，如果可能應(yīng)該指定一個前綴長度。例如，如果有一個char(255)的列，如果在前10個或20個字符內(nèi)，多數(shù)值是惟一的，那么就不要對整個列進(jìn)行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作。

---

五、索引結(jié)構(gòu)方式

Hash索引

所謂Hash索引，當(dāng)我們要給某張表某列增加索引時，將這張表的這一列進(jìn)行哈希算法計算，得到哈希值，排序在哈希數(shù)組上。所以Hash索引可以一次定位，其效率很高。

• Hash索引僅僅能滿足=,IN和<=>查詢，不能使用范圍查詢。
  由于 Hash 索引比較的是進(jìn)行 Hash 運算之后的 Hash值，所以它只能用于等值的過濾，不能用于基于范圍的過濾，因為經(jīng)過相應(yīng)的 Hash算法處理之后的 Hash 值的大小關(guān)系，并不能保證和Hash運算前完全一樣。

• Hash索引無法被用來避免數(shù)據(jù)的排序操作。
  由于 Hash 索引中存放的是經(jīng)過 Hash 計算之后的 Hash值，而且Hash值的大小關(guān)系并不一定和 Hash運算前的鍵值完全一樣，所以數(shù)據(jù)庫無法利用索引的數(shù)據(jù)來避免任何排序運算

• 對于組合索引Hash索引不能利用部分索引鍵查詢。
  對于組合索引，Hash 索引在計算 Hash 值的時候是組合索引鍵合并后再一起計算 Hash 值，而不是單獨計算 Hash值，所以通過組合索引的前面一個或幾個索引鍵進(jìn)行查詢的時候，Hash 索引也無法被利用。

• Hash索引在任何時候都不能避免表掃描。
  Hash索引是將索引鍵通過 Hash 運算之后，將 Hash運算結(jié)果的 Hash值和所對應(yīng)的行指針信息存放于一個 Hash 表中，由于不同索引鍵存在相同 Hash 值，所以即使取滿足某個 Hash 鍵值的數(shù)據(jù)的記錄條數(shù)，也無法從 Hash索引中直接完成查詢，還是要通過訪問表中的實際數(shù)據(jù)進(jìn)行相應(yīng)的比較，并得到相應(yīng)的結(jié)果

• Hash索引遇到大量Hash值相等的情況后性能并不一定就會比B-Tree索引高。

B-TREE

B-Tree 索引是 MySQL 數(shù)據(jù)庫中使用最為頻繁的索引類型。簡單理解，它就像一棵樹，B-Tree索引需要從根節(jié)點到枝節(jié)點，才能訪問到頁節(jié)點的具體數(shù)據(jù)。
B-Tree索引能夠加快訪問數(shù)據(jù)的速度，因為存儲引擎不再需要進(jìn)行全表掃描來獲取需要的數(shù)據(jù)，取而代之的是從索引的根節(jié)點開始進(jìn)行搜索，根節(jié)點的槽中存放了指向子節(jié)點的指針，存儲引擎根據(jù)這些指針向下層查找,通過比較節(jié)點頁的值和要查找的值可以找到合適的指針進(jìn)入下一層子節(jié)點，這些指針實際上定義了子節(jié)點頁中值的上限和下限，最終存儲引擎要么是找到對應(yīng)的值，要么是該記錄不存在。

B-tree 索引可以用于使用 =, >, >=, <, <= 或者 BETWEEN 運算符的列比較。如果 LIKE 的參數(shù)是一個沒有以通配符起始的常量字符串的話也可以使用這種索引。

---

六、聚族非聚族索引

聚族索引

1、定義

聚集索引，來源于生活嘗試。這中索引可以說是按照數(shù)據(jù)的物理存儲進(jìn)行劃分的。對于一堆記錄來說，使用聚集索引就是對這堆記錄 進(jìn)行 堆劃分。即主要描述的是物理上的存儲

2、舉例

比如圖書館新進(jìn)了一批書。那么這些書需要放到圖書館內(nèi)。書如何放呢？一般都有一個規(guī)則，雜志類的放到101房間，文學(xué)類的放到102房間，理工類的放到103房間等等。這些存儲的規(guī)則決定了每本書應(yīng)該放到哪里。而這個例子中聚集索引為書的類別。
正式因為這種存儲規(guī)則，才導(dǎo)致 聚集索引的唯一性。

3、誤區(qū)

有的人認(rèn)為，聚聚族引的字段是唯一的。這是因為sql server 中添加主鍵的時候，自動給主鍵所在的字段生成一個聚集索引。所以人們會認(rèn)為聚集索引所加的字段是唯一的。
思考一下上面這個問題。雜志類的書放到101房間。那么如果雜志類的書太多，一個101房間存放不下。那么可能101，201兩個房間來存放雜志類的書籍。如果這樣分析的話，那么一個雜志類對應(yīng)多個房間。放到表存儲的話，那么這個類別字段 就不是唯一的了

非聚族索引

1、定義

非聚族索引，也可以從生活中找到映射。非聚族索引強調(diào)的是邏輯分類?？梢哉f是定義了一套存儲規(guī)則，而需要有一塊控件來維護(hù)這個規(guī)則，這個被稱之為索引表

2、舉例

同學(xué)如果想去圖書館找一本書，而不知道這本書在哪里？那么這個同學(xué)首先應(yīng)該找的就是 檢索室吧。對于要查找一本書來說，在檢索室查是一個非?？旖莸牡耐緩搅税?。但是，在檢索室中你查到了該書在XX室XX書架的信息。你的查詢結(jié)束了嗎？沒有吧。你僅僅找到了目的書的位置信息，你還要去該位置去取書
對于這種方式來說，你需要兩個步驟：

• 查詢該記錄所在的位置。
• 通過該位置去取要找的記錄

區(qū)別

• 聚族索引：可以幫助把很大的范圍，迅速減小范圍。但是查找該記錄，就要從這個小范圍中Scan了。
• 非聚族索引：把一個很大的范圍，轉(zhuǎn)換成一個小的地圖。你需要在這個小地圖中找你要尋找的信息的位置。然后通過這個位置，再去找你所需要的記錄。

索引與主鍵的區(qū)別

• 主鍵：主鍵是唯一的，用于快速定位一條記錄。
• 聚族索引：聚族索引也是唯一的。（因為聚集索引的劃分依據(jù)是物理存儲）。而聚集索引的主要是為了快速的縮小查找范圍，即記錄數(shù)目未定。
  主鍵和索引沒有關(guān)系。他們的用途相近。如果聚集索引加上唯一性約束之后，他們的作用就一樣了。

使用場景

動作描述	使用聚族索引	使用非聚族索引
列經(jīng)常分組排序	T	T
返回某范圍內(nèi)的數(shù)據(jù)	T	F
很少的不同值	F	F
小數(shù)目不同值	T	F
大數(shù)目不同值	F	T
頻繁更新的列	F	T
主鍵列	T	T
外鍵列	T	T
頻繁修改索引列	T	T