一制妄、mysq索引分類

按照數(shù)據(jù)結(jié)構(gòu)可以分為分為聚集索引和?聚集索引兩大類滑凉。

聚集索引

? ? ? ?每個表有且?定會有?個聚集索引番舆，整個表的數(shù)據(jù)存儲在聚集索引中，mysql索引是采?B+樹結(jié)構(gòu)保存在?件中捍歪，葉?節(jié)點(diǎn)存儲主鍵的值以及對應(yīng)記錄的數(shù)據(jù)户辱，?葉?節(jié)點(diǎn)不存儲記錄的數(shù)據(jù)，只存儲主鍵的值糙臼。當(dāng)表中未指定主鍵時焕妙，mysql內(nèi)部會?動給每條記錄添加?個隱藏的rowid字段（默認(rèn)4個字節(jié)）作為主鍵，?rowid構(gòu)建聚集索引弓摘。聚集索引在mysql中又叫主鍵索引。

非聚集索引（輔助索引）

? ? ? 也是b+樹結(jié)構(gòu)痕届，不過有?點(diǎn)和聚集索引不同韧献，?聚集索引葉?節(jié)點(diǎn)存儲字段（索引字段）的值以及對應(yīng)記錄主鍵的值，其他節(jié)點(diǎn)只存儲字段的值（索引字段）研叫。每個表可以有多個?聚集索引锤窑，?聚集索引又可以分為普通索引、唯一索引嚷炉、全文索引渊啰、空間索引、組合索引

Normal 普通索引

? ? ? 表示普通索引申屹，大多數(shù)情況下都可以使用

Unique 唯一索引

? ? ? 表示唯一的绘证，不允許重復(fù)的索引，如果該字段信息保證不會重復(fù)例如身份證號用作索引時哗讥，可設(shè)置為unique約束唯一標(biāo)識數(shù)據(jù)庫表中的每一條記錄嚷那，即在單表中不能用每條記錄是唯一的（例如身份證就是唯一的），Unique(要求列唯一)和Primary Key(primary key = unique + not null 列唯一)約束均為列或列集合中提供了唯一性的保證杆煞，Primary Key是擁有自動定義的Unique約束魏宽，但是每個表中可以有多個Unique約束腐泻，但是只能有一個Primary Key約束。

Full Text 全文索引

? ? ? ? 表示全文收索队询，在檢索長文本的時候派桩，效果最好，短文本建議使用Index,但是在檢索的時候數(shù)據(jù)量比較大的時候蚌斩，現(xiàn)將數(shù)據(jù)放入一個沒有全局索引的表中铆惑，然后在用Create Index創(chuàng)建的Full Text索引，要比先為一張表建立Full Text然后在寫入數(shù)據(jù)要快的很多凳寺。FULLTEXT 用于搜索很長一篇文章的時候鸭津，效果最好。用在比較短的文本肠缨，如果就一兩行字的逆趋，普通的 INDEX 也可以。

SPATIAL 空間索引

? ? ? ?空間索引是對空間數(shù)據(jù)類型的字段建立的索引晒奕，MYSQL中的空間數(shù)據(jù)類型有4種闻书，分別是GEOMETRY、POINT脑慧、LINESTRING魄眉、POLYGON。MYSQL使用SPATIAL關(guān)鍵字進(jìn)行擴(kuò)展闷袒，使得能夠用于創(chuàng)建正規(guī)索引類型的語法創(chuàng)建空間索引坑律。創(chuàng)建空間索引的列，必須將其聲明為NOT NULL囊骤，空間索引只能在存儲引擎為MYISAM的表中創(chuàng)建

二晃择、索引方法btree和hash的區(qū)別

1、BTREE（B樹（可以是多叉樹）） {主流使用}

2也物、HASH（key,value） 這種方式對范圍查詢支持得不是很好宫屠。hash 索引結(jié)構(gòu)的特殊性，其檢索效率非常高滑蚯，索引的檢索可以一次定位浪蹂，不像B-Tree 索引需要從根節(jié)點(diǎn)到枝節(jié)點(diǎn)，最后才能訪問到頁節(jié)點(diǎn)這樣多次的IO訪問告材，所以 Hash 索引的查詢效率要遠(yuǎn)高于 B-Tree 索引坤次。

? ? ? ?既然 Hash 索引的效率要比 B-Tree 高很多，為什么大家不都用 Hash 索引而還要使用 B-Tree 索引呢斥赋？任何事物都是有兩面性的浙踢，Hash 索引也一樣，雖然 Hash 索引效率高灿渴，但是 Hash 索引本身由于其特殊性也帶來了很多限制和弊端洛波，主要有以下這些胰舆。

（1）Hash 索引僅僅能滿足”=”,”IN”和”<=>”查詢，不能使用范圍查詢蹬挤。

由于 Hash 索引比較的是進(jìn)行 Hash 運(yùn)算之后的 Hash 值缚窿，所以它只能用于等值的過濾，不能用于基于范圍的過濾焰扳，因?yàn)榻?jīng)過相應(yīng)的 Hash 算法處理之后的 Hash 值的大小關(guān)系倦零，并不能保證和Hash運(yùn)算前完全一樣。

（2）Hash 索引無法被用來避免數(shù)據(jù)的排序操作吨悍。

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

（3）Hash 索引不能利用部分索引鍵查詢葫隙。

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

（4）Hash 索引在任何時候都不能避免表掃描糟描。

前面已經(jīng)知道，Hash 索引是將索引鍵通過 Hash 運(yùn)算之后书妻，將 Hash運(yùn)算結(jié)果的 Hash 值和所對應(yīng)的行指針信息存放于一個 Hash 表中船响，由于不同索引鍵存在相同 Hash 值，所以即使取滿足某個 Hash 鍵值的數(shù)據(jù)的記錄條數(shù)躲履，也無法從 Hash 索引中直接完成查詢灿意，還是要通過訪問表中的實(shí)際數(shù)據(jù)進(jìn)行相應(yīng)的比較，并得到相應(yīng)的結(jié)果崇呵。

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

對于選擇性比較低的索引鍵馅袁，如果創(chuàng)建 Hash 索引域慷，那么將會存在大量記錄指針信息存于同一個 Hash 值相關(guān)聯(lián)。這樣要定位某一條記錄時就會非常麻煩汗销，會浪費(fèi)多次表數(shù)據(jù)的訪問犹褒，而造成整體性能低下。

三弛针、在實(shí)際操作過程中叠骑，應(yīng)該選取表中哪些字段作為索引？有9大原則：

1．選擇唯一性索引

2．為經(jīng)常需要排序削茁、分組和聯(lián)合操作的字段建立索引

3．為常作為查詢條件的字段建立索引

4．限制索引的數(shù)目

5．盡量使用數(shù)據(jù)量少的索引

6．盡量使用前綴來索引

7．刪除不再使用或者很少使用的索引

8. 經(jīng)常更新修改的字段不要建立索引

9宙枷、不推薦在同一列建多個索引