關於mysql建立索引 複合索引 索引類型

標籤：位置   create   post   arch   目的   har   進入   索引尋找   依據   

這兩天有個非常強烈的感覺就是自己在一些特別的情況下還是hold不住，腦子easy放空或者說一下子不知道怎麼去分析問題了，比方，問“hash和btree索引的差別”，這非常難嗎。僅僅要掌握了這兩種資料結構稍加分析就能得出答案，結果是一下子不知道從何說起。進入正題吧。這兩者有啥差別。

1. hash索引尋找資料基本上能一次定位元據。當然有大量碰撞的話效能也會下降。

而btree索引就得在節點上挨著尋找了，非常明顯在資料精確尋找方面hash索引的效率是要高於btree的。

2. 那麼不精確尋找呢，也非常明顯，由於hash演算法是基於等值計算的。所以對於“like”等範圍尋找hash索引無效，不支援；

3. 對於btree支援的聯合索引的最優首碼。hash也是無法支援的。聯合索引中的欄位要麼全用要麼全不用。提起最優首碼竟然都泛起迷糊了，看來有時候放空得太厲害。

4. hash不支援索引排序。索引值和計算出來的hash值大小並不一定一致。

勤動腦，少放空。

索引是在資料庫表或者視圖上建立的對象，目的是為了加快對錶或視圖的查詢的速度。

依照儲存方式分為：聚集與非叢集索引
依照維護與管理索引角度分為：唯一索引、複合索引和系統自己主動建立的索引。

索引的結構是由：根節點--->非分葉節點--->非分葉節點--->分葉節點

 

1、叢集索引:表中儲存的資料依照索引的順序儲存,檢索效率比普通索引高,但對資料新增/改動/刪除的影響比較大。

邏輯順序決定了表中對應行的物理順序。

特點：
　 (1) 一個表能夠最多能夠建立249個索引
　 (2) 先建叢集索引才幹建立非叢集索引
     (3) 非叢集索引資料與索引不同序
     (4) 資料與索引在不同位置
     (5) 索引在分葉節點上儲存,在分葉節點上有一個＂指標＂直接指向要查詢的資料區域
     (6) 資料不會依據索引鍵的順序又一次排列資料

     (7)假設在該欄位上進行範圍查詢，或者該表非常少做增刪改

　建立叢集索引的文法：
   create NONCLUSTERED INDEX idximpID ON EMP(empID)
２、非叢集索引:不影響表中的資料存放區順序,檢索效率比叢集索引低,對資料新增/改動/刪除的影響非常少
。

是通過二叉樹的資料結構來描寫敘述的，邏輯順序，特點：
   (1) 無索引。資料無序
   (2) 有索引。資料與索引同序 
   (3) 資料會依據索引鍵的順序又一次排列資料
   (4) 一個表僅僅能有一個索引
   (5) 分葉節點的指標指向的資料也在同一位置儲存
文法：
create CLUSTERED INDEX idxempID on emp(empID)
３、惟一索引：惟一索引能夠確保索引列不包括反覆的值.
能夠用多個列，可是索引能夠確保索引列中每一個值組合都是唯一的
姓   名
李   二
張   三
王   五
文法： create unique index idxempid on emp(姓，名)

4、複合索引：假設在兩上以上的列上建立一個索引，則稱為複合索引。
那麼。不可能有兩行的姓和名是反覆的
文法：
create index indxfullname on addressbook(firstname,lastname)

注意：假設把複合的叢集索引欄位分開查詢。
帶著這個問題。我們來看一下下面的查詢速度（結果集都是25萬條資料）：（日期列fariqi首先排在複合叢集索引的起始列。usernameneibuyonghu排在後列）：
    （1）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>‘‘2004-5-5‘‘
    查詢速度：2513毫秒
    （2）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>‘‘2004-5-5‘‘ and neibuyonghu=‘‘辦公室‘‘
    查詢速度：2516毫秒
    （3）select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu=‘‘辦公室‘‘
    查詢速度：60280毫秒
 　從以上實驗中，我們能夠看到假設僅用叢集索引的起始列作為查詢條件和同一時候用到複合叢集索引的所有列的查詢速度是差點兒一樣的。甚至比用上所有的複合索引列還要略快（在查詢結果集數目一樣的情況下）；而假設僅用複合叢集索引的非起始列作為查詢條件的話，這個索引是不起不論什麼作用的。當然，語句1、2的查詢速度一樣是由於查詢的條目數一樣。假設複合索引的所有列都用上，並且查詢結果少的話，這樣就會形成“索引覆蓋”。因而效能能夠達到最優。同一時候，請記住：不管您是否常常使用彙總索引的其它列，但其前置列一定要是使用最頻繁的列。

5、系統自建的索引:在使用T_sql語句建立表的時候使用PRIMARY KEY或UNIQUE約束時。會在表上自己主動建立一個惟一索引
自己主動建立的索引是無法刪除的
文法：
create table ABc
( empID int primary key,
   firstname varchar(50)UNIQUE,
   lastname   varchar(50)UNIQUE,
)
這種結果就出來了三個索引，但僅僅有一個叢集索引哦


６、建立索引的方法：
1、企業管理器中
(1)右擊某個表，全部任務---管理索引，開啟管理索引。單擊“建立”就能夠建立索引
(2)在設計表中進行設計表，管理索引/鍵
(3)在關係圖中，加入表後右擊關係圖中的某個表，就有“索引/鍵”
(4)通過嚮導,資料庫---建立索引嚮導
(5)通過T-SQL語句
2、能過“索引最佳化嚮導”來最佳化索引的嚮導。通過它能夠決定選擇哪些列做為索引列
　
 二、何時使用叢集索引或非叢集索引

以下的表總結了何時使用叢集索引或非叢集索引（非常重要）：

 

動作描寫敘述	使用叢集索引	使用非叢集索引
列常常被分組排序	應	應
返回某範圍內的資料	應	不應
一個或極少不同值	不應	不應
小數目的不同值	應	不應
大數目的不同值	不應	應
頻繁更新的列	不應	應
外鍵列	應	應
主鍵列	應	應
頻繁改動索引列	不應	應

關於mysql建立索引 複合索引 索引類型