索引

概念

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

---

一、索引的分類(lèi)

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

1、普通索引

是最基本的索引，它沒(méi)有任何限制。

2、唯一索引

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

3、主鍵索引

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

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

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

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

5、全文索引

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

---

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

優(yōu)點(diǎn)

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

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

缺點(diǎn)

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

---

三、索引創(chuàng)建

1、何時(shí)要?jiǎng)?chuàng)建索引

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

2、何時(shí)不要?jiǎng)?chuàng)建索引

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

---

四、索引使用的注意事項(xiàng)

• 模糊查詢(xún)

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

  • name like ‘張%’ ，索引有效
  • name like ‘%張’ ,索引無(wú)效，全表查詢(xún)

• or 會(huì)引起全表掃描

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

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

• 索引不會(huì)包含有null值的列

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

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

• 使用短索引

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

---

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

Hash索引

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

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

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

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

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

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

B-TREE

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

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

---

六、聚族非聚族索引

聚族索引

1、定義

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

2、舉例

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

3、誤區(qū)

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

非聚族索引

1、定義

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

2、舉例

同學(xué)如果想去圖書(shū)館找一本書(shū)，而不知道這本書(shū)在哪里？那么這個(gè)同學(xué)首先應(yīng)該找的就是 檢索室吧。對(duì)于要查找一本書(shū)來(lái)說(shuō)，在檢索室查是一個(gè)非常快捷的的途徑了吧。但是，在檢索室中你查到了該書(shū)在XX室XX書(shū)架的信息。你的查詢(xún)結(jié)束了嗎？沒(méi)有吧。你僅僅找到了目的書(shū)的位置信息，你還要去該位置去取書(shū)
對(duì)于這種方式來(lái)說(shuō)，你需要兩個(gè)步驟：

• 查詢(xún)?cè)撚涗浰诘奈恢谩?/li>
• 通過(guò)該位置去取要找的記錄

區(qū)別

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

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

• 主鍵：主鍵是唯一的，用于快速定位一條記錄。
• 聚族索引：聚族索引也是唯一的。（因?yàn)榫奂饕膭澐忠罁?jù)是物理存儲(chǔ)）。而聚集索引的主要是為了快速的縮小查找范圍，即記錄數(shù)目未定。
  主鍵和索引沒(méi)有關(guān)系。他們的用途相近。如果聚集索引加上唯一性約束之后，他們的作用就一樣了。

使用場(chǎng)景

動(dòng)作描述	使用聚族索引	使用非聚族索引
列經(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