索引

概念

索引其實是一種數據結構，能夠幫助我們快速的檢索數據庫中的數據

---

一、索引的分類

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

1、普通索引

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

2、唯一索引

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

3、主鍵索引

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

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

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

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

5、全文索引

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

---

二、索引的優缺點

優點

• 提高數據檢索的效率，降低數據庫IO成本。

• 通過索引對數據進行排序，降低數據的排序成本，降低CPU的消耗。

缺點

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

---

三、索引創建

1、何時要創建索引

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

2、何時不要創建索引

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

---

四、索引使用的注意事項

• 模糊查詢

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

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

• or 會引起全表掃描

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

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

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

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

• 不要在列上進行運算，這將導致索引失效而進行全表掃描

• 使用短索引

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

---

五、索引結構方式

Hash索引

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

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

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

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

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

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

B-TREE

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

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

---

六、聚族非聚族索引

聚族索引

1、定義

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

2、舉例

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

3、誤區

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

非聚族索引

1、定義

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

2、舉例

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

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

區別

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

索引與主鍵的區別

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

使用場景

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