目前大部分?jǐn)?shù)據(jù)庫(kù)系統(tǒng)及文件系統(tǒng)都采用B-Tree(B樹(shù))或其變種B Tree(B 樹(shù))作為索引結(jié)構(gòu)。B Tree是數(shù)據(jù)庫(kù)系統(tǒng)實(shí)現(xiàn)索引的首選數(shù)據(jù)結(jié)構(gòu)。在MySQL中,索引屬于存儲(chǔ)引擎級(jí)別的概念,不同存儲(chǔ)引擎對(duì)索引的實(shí)現(xiàn)方式是不同的。今天主要簡(jiǎn)單講下mysql的幾個(gè)索引類型，下面一起看看吧~
mysql里目前只支持4種索引，分別是：full-text，b-tree，hash，r-tree
b-tree索引應(yīng)該是mysql里最廣泛的索引了，除了archive基本所有的存儲(chǔ)引擎都支持它.
1、full-text索引
full-text在mysql里僅有myisam支持它，而且支持full-text的字段只有char、varchar、text數(shù)據(jù)類型。
full-text主要是用來(lái)代替like '%***%'效率低下的問(wèn)題。
強(qiáng)烈注意：MySql自帶的全文索引只能用于數(shù)據(jù)庫(kù)引擎為MYISAM的數(shù)據(jù)表，如果是其他數(shù)據(jù)引擎，則全文索引不會(huì)生效。此外，MySql自帶的全文索引只能對(duì)英文進(jìn)行全文檢索，目前無(wú)法對(duì)中文進(jìn)行全文檢索。如果需要對(duì)包含中文在內(nèi)的文本數(shù)據(jù)進(jìn)行全文檢索，我們需要采用Sphinx（斯芬克斯）/Coreseek技術(shù)來(lái)處理中文。
ps：目前，使用MySql自帶的全文索引時(shí)，如果查詢字符串的長(zhǎng)度過(guò)短將無(wú)法得到期望的搜索結(jié)果。MySql全文索引所能找到的詞默認(rèn)最小長(zhǎng)度為4個(gè)字符。另外，如果查詢的字符串包含停止詞，那么該停止詞將會(huì)被忽略。
如果可能，請(qǐng)盡量先創(chuàng)建表并插入所有數(shù)據(jù)后再創(chuàng)建全文索引，而不要在創(chuàng)建表時(shí)就直接創(chuàng)建全文索引，因?yàn)榍罢弑群笳叩娜乃饕室摺?/p>
2、b-tree索引
b-tree在myisam里的形式和innodb稍有不同
在 innodb里，有兩種形態(tài)：一是primary key形態(tài)，其leaf node里存放的是數(shù)據(jù)，而且不僅存放了索引鍵的數(shù)據(jù)，還存放了其他字段的數(shù)據(jù)。二是secondary index，其leaf node和普通的b-tree差不多，只是還存放了指向主鍵的信息.
采用InnoDB引擎的數(shù)據(jù)存儲(chǔ)文件有兩個(gè)，一個(gè)定義文件，一個(gè)數(shù)據(jù)文件。若建索引的字段不是主鍵ID，則對(duì)該字段建索引，然后在葉子節(jié)點(diǎn)中存儲(chǔ)的是該記錄的主鍵，然后通過(guò)主鍵索引來(lái)找到對(duì)應(yīng)的記錄
innodb
而在myisam里，主鍵和其他的并沒(méi)有太大區(qū)別。不過(guò)和innodb不太一樣的地方是在myisam里，leaf node里存放的不是主鍵的信息，而是指向數(shù)據(jù)文件里的對(duì)應(yīng)數(shù)據(jù)行的信息。MyIsam中索引和數(shù)據(jù)分別存放在不同的文件，所以在索引樹(shù)中的葉子節(jié)點(diǎn)中存放的數(shù)據(jù)是該索引對(duì)應(yīng)的數(shù)據(jù)記錄的地址，由于數(shù)據(jù)與索引不在一起，所以MyIsam是非聚簇索引。
3、hash索引
目前應(yīng)該只有memory和ndb cluster支持這種索引。hash索引由于其結(jié)構(gòu)，所以在每次查詢的時(shí)候直接一次到位，不像b-tree那樣一點(diǎn)點(diǎn)的前進(jìn)。所以hash索引的效率高于b-tree，簡(jiǎn)單地說(shuō)，哈希索引就是采用一定的哈希算法，把鍵值換算成新的哈希值，檢索時(shí)不需要類似B 樹(shù)那樣從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)逐級(jí)查找，只需一次哈希算法即可立刻定位到相應(yīng)的位置，速度非?？?。
但hash也有缺點(diǎn)，主要如下：
(1)由于存放的是hash值，所以僅支持<=>以及in操作.
(2)hash索引無(wú)法通過(guò)操作索引來(lái)排序，這是因?yàn)榇娣诺臅r(shí)候經(jīng)過(guò)hash計(jì)算，但是計(jì)算的hash值和存放的不一定相等，所以無(wú)法排序.
(3)在組合所以里，無(wú)法對(duì)部分使用索引.
(4)不能避免全表掃描，只是由于在memory表里支持非唯一值hash索引，就是不同的索引鍵，可能存在相同的hash值.
(5)當(dāng)存在大量相同hash值得時(shí)候，hash索引的效率會(huì)變低。
4、r-tree索引
myisam支持空間索引，可以用作地理數(shù)據(jù)存儲(chǔ)，R-tree無(wú)須前綴索引?？臻g索引會(huì)從所有維度來(lái)索引數(shù)據(jù)。查詢時(shí)，可以有效地使用任意維度來(lái)組合查詢。
不過(guò)r-tree在mysql很少使用，僅支持geometry數(shù)據(jù)類型，支持該類型的存儲(chǔ)引擎只有myisam、bdb、innodb、ndb、archive幾種。
相對(duì)于b-tree，r-tree的優(yōu)勢(shì)在于范圍查找。
1. myisam里所有鍵的長(zhǎng)度僅支持1000字節(jié)，innodb是767
2. blob和text字段僅支持前綴索引
3. 使用!=以及<>不等于的時(shí)候，mysql不使用索引
4. 當(dāng)在字段時(shí)候函數(shù)的時(shí)候，mysql無(wú)法使用索引；在join時(shí)條件字段類型不一致的時(shí)候，mysql無(wú)法使用索引；在組合索引里使用非第一個(gè)索引時(shí)也不使用索引.
5. 在使用like的時(shí)候，以%開(kāi)頭，即'%***'的時(shí)候無(wú)法使用索引；在使用or的時(shí)候，要求or前后字段都有索引
有時(shí)候mysql query optimizer會(huì)認(rèn)為使用索引并不是最優(yōu)計(jì)劃,所以不使用索引?？梢栽趕ql語(yǔ)句里可以用use,force index,當(dāng)然有時(shí)候使用也不會(huì)比不用快,所以需要忽略掉index方法是ignore index.
1. 強(qiáng)制連接順序： STRAIGHT_JOIN
由上面的SQL語(yǔ)句可知，通過(guò)STRAIGHT_JOIN強(qiáng)迫MySQL按TABLE1、TABLE2的順序連接表。如果你認(rèn)為按自己的順序比MySQL推薦的順序進(jìn)行連接的效率高的話，就可以通過(guò)STRAIGHT_JOIN來(lái)確定連接順序。
2. 強(qiáng)制使用臨時(shí)表： SQL_BUFFER_RESULT
SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …
當(dāng)我們查詢的結(jié)果集中的數(shù)據(jù)比較多時(shí)，可以通過(guò)SQL_BUFFER_RESULT，選項(xiàng)強(qiáng)制將結(jié)果集放到臨時(shí)表中，這樣就可以很快地釋放MySQL的表鎖（這樣其它的SQL語(yǔ)句就可以對(duì)這些記錄進(jìn)行查詢了），并且可以長(zhǎng)時(shí)間地為客戶端提供大記錄集。
3. 分組使用臨時(shí)表 SQL_BIG_RESULT和SQL_SMALL_RESULT
一般用于分組或DISTINCT關(guān)鍵字，這個(gè)選項(xiàng)通知MySQL，如果有必要，就將查詢結(jié)果放到臨時(shí)表中，甚至在臨時(shí)表中進(jìn)行排序。SQL_SMALL_RESULT比起SQL_BIG_RESULT差不多，很少使用。
聯(lián)系客服
