索引對查詢的速度有著至關(guān)重要的影響,理解索引也是進行數(shù)據(jù)庫性能調(diào)優(yōu)的起點�?紤]如下情況,假設(shè)數(shù)據(jù)庫中一個表有10^6條記錄,DBMS的頁面大小為4K,并存儲100條記錄。如果沒有索引,查詢將對整個表進行掃描,最壞的情況下,如果所有數(shù)據(jù)頁都不在內(nèi)存,需要讀取10^4個頁面,如果這10^4個頁面在磁盤上隨機分布,需要進行10^4次I/O,假設(shè)磁盤每次I/O時間為10ms(忽略數(shù)據(jù)傳輸時間),則總共需要100s(但實際上要好很多很多)。如果對之建立B-Tree索引,則只需要進行l(wèi)og100(10^6)=3次頁面讀取,最壞情況下耗時30ms。這就是索引帶來的效果,很多時候,當你的應用程序進行SQL查詢速度很慢時,應該想想是否可以建索引。
從數(shù)據(jù)結(jié)構(gòu)角度
1、B+樹索引(O(log(n))):關(guān)于B+樹索引,可以參考 MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理
2、hash索引:
a 僅僅能滿足"=","IN"和"<=>"查詢,不能使用范圍查詢
b 其檢索效率非常高,索引的檢索可以一次定位,不像B-Tree 索引需要從根節(jié)點到枝節(jié)點,最后才能訪問到頁節(jié)點這樣多次的IO訪問,所以 Hash 索引的查詢效率要遠高于 B-Tree 索引
c 只有Memory存儲引擎顯示支持hash索引
3、FULLTEXT索引(現(xiàn)在MyISAM和InnoDB引擎都支持了)
4、R-Tree索引(用于對GIS數(shù)據(jù)類型創(chuàng)建SPATIAL索引)
從物理存儲角度
1、聚集索引(clustered index)
2、非聚集索引(non-clustered index)
從邏輯角度
1、主鍵索引:主鍵索引是一種特殊的唯一索引,不允許有空值
2、普通索引或者單列索引
3、多列索引(復合索引):復合索引指多個字段上創(chuàng)建的索引,只有在查詢條件中使用了創(chuàng)建索引時的第一個字段,索引才會被使用。使用復合索引時遵循最左前綴集合
4、唯一索引或者非唯一索引
5、空間索引:空間索引是對空間數(shù)據(jù)類型的字段建立的索引,MYSQL中的空間數(shù)據(jù)類型有4種,分別是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL關(guān)鍵字進行擴展,使得能夠用于創(chuàng)建正規(guī)索引類型的語法創(chuàng)建空間索引。創(chuàng)建空間索引的列,必須將其聲明為NOT NULL,空間索引只能在存儲引擎為MYISAM的表中創(chuàng)建
CREATE TABLE table_name[col_name data type]
[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]
1、unique|fulltext|spatial為可選參數(shù),分別表示唯一索引、全文索引和空間索引;
2、index和key為同義詞,兩者作用相同,用來指定創(chuàng)建索引
3、col_name為需要創(chuàng)建索引的字段列,該列必須從數(shù)據(jù)表中該定義的多個列中選擇;
4、index_name指定索引的名稱,為可選參數(shù),如果不指定,MYSQL默認col_name為索引值;
5、length為可選參數(shù),表示索引的長度,只有字符串類型的字段才能指定索引長度;
6、asc或desc指定升序或降序的索引值存儲