如何在mysql中的數(shù)據(jù)建立倒排索引 mysql建索引的方法

索引對(duì)查詢(xún)的速度有著至關(guān)重要的影響，理解索引也是進(jìn)行數(shù)據(jù)庫(kù)性能調(diào)優(yōu)的起點(diǎn)�？紤]如下情況，假設(shè)數(shù)據(jù)庫(kù)中一個(gè)表有10^6條記錄，DBMS的頁(yè)面大小為4K，并存儲(chǔ)100條記錄。如果沒(méi)有索引，查詢(xún)將對(duì)整個(gè)表進(jìn)行掃描，最壞的情況下，如果所有數(shù)據(jù)頁(yè)都不在內(nèi)存，需要讀取10^4個(gè)頁(yè)面，如果這10^4個(gè)頁(yè)面在磁盤(pán)上隨機(jī)分布，需要進(jìn)行10^4次I/O，假設(shè)磁盤(pán)每次I/O時(shí)間為10ms(忽略數(shù)據(jù)傳輸時(shí)間)，則總共需要100s(但實(shí)際上要好很多很多)。如果對(duì)之建立B-Tree索引，則只需要進(jìn)行l(wèi)og100(10^6)=3次頁(yè)面讀取，最壞情況下耗時(shí)30ms。這就是索引帶來(lái)的效果，很多時(shí)候，當(dāng)你的應(yīng)用程序進(jìn)行SQL查詢(xún)速度很慢時(shí)，應(yīng)該想想是否可以建索引。

從數(shù)據(jù)結(jié)構(gòu)角度

1、B+樹(shù)索引(O(log(n)))：關(guān)于B+樹(shù)索引，可以參考 MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理

2、hash索引：
a 僅僅能滿(mǎn)足"=","IN"和"<=>"查詢(xún)，不能使用范圍查詢(xún)
b 其檢索效率非常高，索引的檢索可以一次定位，不像B-Tree 索引需要從根節(jié)點(diǎn)到枝節(jié)點(diǎn)，最后才能訪(fǎng)問(wèn)到頁(yè)節(jié)點(diǎn)這樣多次的IO訪(fǎng)問(wèn)，所以 Hash 索引的查詢(xún)效率要遠(yuǎn)高于 B-Tree 索引
c 只有Memory存儲(chǔ)引擎顯示支持hash索引

3、FULLTEXT索引（現(xiàn)在MyISAM和InnoDB引擎都支持了）

4、R-Tree索引（用于對(duì)GIS數(shù)據(jù)類(lèi)型創(chuàng)建SPATIAL索引）

從物理存儲(chǔ)角度

1、聚集索引（clustered index）

2、非聚集索引（non-clustered index）

從邏輯角度

1、主鍵索引：主鍵索引是一種特殊的唯一索引，不允許有空值

2、普通索引或者單列索引

3、多列索引（復(fù)合索引）：復(fù)合索引指多個(gè)字段上創(chuàng)建的索引，只有在查詢(xún)條件中使用了創(chuàng)建索引時(shí)的第一個(gè)字段，索引才會(huì)被使用。使用復(fù)合索引時(shí)遵循最左前綴集合

4、唯一索引或者非唯一索引

5、空間索引：空間索引是對(duì)空間數(shù)據(jù)類(lèi)型的字段建立的索引，MYSQL中的空間數(shù)據(jù)類(lèi)型有4種，分別是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL關(guān)鍵字進(jìn)行擴(kuò)展，使得能夠用于創(chuàng)建正規(guī)索引類(lèi)型的語(yǔ)法創(chuàng)建空間索引。創(chuàng)建空間索引的列，必須將其聲明為NOT NULL，空間索引只能在存儲(chǔ)引擎為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為同義詞，兩者作用相同，用來(lái)指定創(chuàng)建索引

3、col_name為需要?jiǎng)?chuàng)建索引的字段列，該列必須從數(shù)據(jù)表中該定義的多個(gè)列中選擇；

4、index_name指定索引的名稱(chēng)，為可選參數(shù)，如果不指定，MYSQL默認(rèn)col_name為索引值；

5、length為可選參數(shù)，表示索引的長(zhǎng)度，只有字符串類(lèi)型的字段才能指定索引長(zhǎng)度；

6、asc或desc指定升序或降序的索引值存儲(chǔ)