mysql四種索引類型，mysql常見索引類型

大家好，今天來為大家解答mysql四種索引類型這個問題的一些問題點(diǎn)，包括mysql常見索引類型也一樣很多人還不知道，因此呢，今天就來為大家分析分析，現(xiàn)在讓我們一起來看看吧！如果解決了您的問題，還望您關(guān)注下本站哦，謝謝~

MySQL用什么索引

mysql索引有：

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

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

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

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

5、空間索引：空間索引是對空間數(shù)據(jù)類型的字段建立的索引。MYSQL使用SPATIAL關(guān)鍵字進(jìn)行擴(kuò)展，使得能夠用于創(chuàng)建正規(guī)索引類型的語法創(chuàng)建空間索引。

mysql什么是二級索引

mysql中每個表都有一個聚簇索引（clusteredindex），除此之外的表上的每個非聚簇索引都是二級索引，又叫輔助索引（secondaryindexes）。以InnoDB來說，每個InnoDB表具有一個特殊的索引稱為聚集索引。如果您的表上定義有主鍵，該主鍵索引是聚集索引。

如果你不定義為您的表的主鍵時，MySQL取第一個唯一索引（unique）而且只含非空列（NOTNULL）作為主鍵，InnoDB使用它作為聚集索引。

如果沒有這樣的列，InnoDB就自己產(chǎn)生一個這樣的ID值，它有六個字節(jié)，而且是隱藏的，使其作為聚簇索引。

Mysql中哪些場景下會導(dǎo)致使用了索引但索引失效，導(dǎo)致性能變差

以Mysql為例，其中索引BTree類型。以下幾種SQL設(shè)計會導(dǎo)致雖然使用了索引，但是索引不會生效，即引擎放棄使用索引而進(jìn)行全表掃描：

WHERE子句中使用!=或<>操作符。WHERE子句中對索引列使用%前綴模糊查詢。WHERE子句中對索引列使用OR來連接條件。WHERE子句中對索引列使用NOTIN。WHERE子句中對索引列使用計算、函數(shù)、類型轉(zhuǎn)換等操作。WHERE子句中對索引列使用參數(shù)。

MySQL是如何利用索引的

什么是索引？

索引是數(shù)據(jù)庫快速找到記錄行的一種數(shù)據(jù)結(jié)構(gòu)，類似我們看書時的目錄，它是良好性能的關(guān)鍵因素。尤其是表中的數(shù)據(jù)量越來越大時，如果索引使用不當(dāng)，會嚴(yán)重影響性能。索引也是最常見的數(shù)據(jù)庫優(yōu)化手段，它能輕易的將查詢性能提高好幾個量級。

MySQL索引類型？

mysql索引數(shù)據(jù)是存儲在存儲引擎中的，所以不同存儲引擎中索引的工作方式并不一樣。

B-Tree索引：基于B+樹（一種多叉搜索數(shù)樹）來實現(xiàn)的索引類型，一般也是使用的最多的索引類型，之所以選擇B+樹而不是其他數(shù)據(jù)結(jié)構(gòu)，是因為B+樹在查詢時間復(fù)雜度可以維持在O(logn)的級別上，由于B+的矮胖（從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)的距離可以維持在較小范圍）特性減少磁盤IO次數(shù)、數(shù)據(jù)只存在葉子節(jié)點(diǎn)中并且按順序存儲也可以支持快速的范圍查詢，這是其他結(jié)構(gòu)無法滿足的！

B+索引中值是按順序存儲的，葉子節(jié)點(diǎn)到根節(jié)點(diǎn)的距離都相同，從B+樹的根節(jié)點(diǎn)開始往下查找，節(jié)點(diǎn)存儲了指向葉子節(jié)點(diǎn)的指針，通過將要查找的值和每個節(jié)點(diǎn)值比較后，一層層定位到最終的葉子結(jié)點(diǎn)上，葉子節(jié)點(diǎn)存儲的就是行數(shù)據(jù)、指針或主鍵。

假如我們索引列是：

key（lastname（姓）,firstname（名）,born），可以使用B+樹索引的查詢類型包括：全鍵值、鍵值范圍、鍵前綴查找，其中鍵前綴只適用于最左前綴查找：

全值匹配：指的是和索引中所有的列進(jìn)行匹配，如可以找到姓名為：Cuba（名）Allen(姓)、生于1988-10-04的人，如wherelastname=‘Allen’andfirstname=‘Cuba’andborn='1988-10-04'匹配最左前綴：可以查找姓為Allen的人，如wherelastname=‘Allen’匹配列前綴：也可以匹配某一列的值的開頭部分，如wherelastnamelike‘A%’或者wherefirstnamelike‘M%’匹配范圍：可以匹配姓在Allen和Bill之間的人精確匹配某一列并匹配另外一列：查找所有姓為Allen、并且名字是以M開頭的人，如wherelastname=‘Allen’andfirstnamelike‘M%’訪問索引數(shù)據(jù)：這種查詢只需要訪問索引本身就行了，不需要訪問數(shù)據(jù)行，也就是常說的索引覆蓋，舉個例子：如果只需要找到姓為Allen的人的名稱，而不需要這個人其他的信息，名稱就存在與索引中，不需要再去數(shù)據(jù)行中查找數(shù)據(jù)了。

這里需要注意的是葉子節(jié)點(diǎn)存什么類型數(shù)據(jù)不同的存儲引擎還不一樣，在MyISAM中葉子節(jié)點(diǎn)存儲的是數(shù)據(jù)物理位置（指針），而InnoDB使用B+結(jié)構(gòu)存儲的是原始數(shù)據(jù)或主鍵，也就是我們常說的聚簇索引，它存儲的是原始全量數(shù)據(jù)、鍵值，聚簇索引指的是一種數(shù)據(jù)索引組織形式，它將數(shù)據(jù)和索引聚集在一起所以叫聚簇，它本身并不是一種索引類型。

一般InnoDB查找過程為從輔助索引上開始查找到數(shù)據(jù)主鍵，然后在主鍵索引中用主鍵再次查找，最后再找到數(shù)據(jù)，雖然多了一次查找過程，但更新數(shù)據(jù)不會導(dǎo)致聚簇索引頻繁變化。而在MyISAM中不需要2次索引查找，因為葉子節(jié)點(diǎn)存儲的是數(shù)據(jù)的物理地址可以直接定位，雖然查詢看似簡單了，但是物理地址會因為數(shù)據(jù)頻繁變更而發(fā)生變化。

假設(shè)有以下數(shù)據(jù)：

InnoDB（聚簇索引）數(shù)據(jù)查找過程：

MyISAM（非聚簇索引結(jié)構(gòu)）數(shù)據(jù)查找過程：

哈希索引：基于哈希表來實現(xiàn)的索引類型，如果存在哈希沖突，索引會使用鏈表來存放多個記錄到一個哈希桶中。舉個例子：如果存在以下索引keyUSINGHASH（firstname），哈希索引會使用哈希函數(shù)計算出firstname列的哈希值作為key，并將行指針作為value存儲，當(dāng)使用=、IN（）、<=>操作時，先計算出sql語句操作查找值的哈希值，并使用其來查找哈希表對應(yīng)的行指針，從而返回數(shù)據(jù)。

這里需要注意是：

哈希索引只存儲哈希值和行指針，索引索引本身沒有行數(shù)據(jù)，也就沒有所謂的索引覆蓋。哈希索引沒有按哈希值的順序排列，所有不支持排序操作。不支持部分索引列的匹配，哈希索引使用你指定的全部列來計算哈希值，列入（A，B）如果查詢只有列A，則索引無發(fā)匹配。哈希索引只支持等值比較（=、in(),<=>）。哈希沖突較高時，查找效率就變成了鏈表，復(fù)雜度從O（1）變?yōu)镺（n）。

空間數(shù)據(jù)索引：MyISAM支持空間索引可以用來存儲地理數(shù)據(jù)。必須使用GIS相關(guān)函數(shù)如MBRCONUNTAINS（）來維護(hù)數(shù)據(jù)，因為本身mysql對GIS的支持下不完善，這中特性使用很少。

全文索引：這是一種特殊類型的索引，他查找的是索引列中文本的關(guān)鍵詞，而不是比較索引值，全文索引的使用要注意列的文本大小和數(shù)據(jù)量，它的匹配方式類似于搜索引擎。

索引的優(yōu)缺點(diǎn)？大大減少了服務(wù)器掃描表的數(shù)據(jù)量。避免不必要的排序和臨時表。將隨機(jī)IO變?yōu)轫樞騃O。對于非常小的表，全表掃描可能比索引更快，對于中型數(shù)據(jù)量表，索引將會非常有效，對于TB級別的表來說，索引的維護(hù)和效果可能沒有我們想象的那樣好，這是可以使用表分區(qū)、業(yè)務(wù)拆分表和分庫等技術(shù)。常見的索引優(yōu)化方式及注意事項？不要把索引的列納入表達(dá)式，也不能是函數(shù)參數(shù)，如whereaid+1=5、whereto_days（col）<=10.選擇重復(fù)性較低的列建索引，重復(fù)性較高會導(dǎo)致索引失效，全表掃描。多列索引中很多常見的錯誤是，喜歡為每個列創(chuàng)建獨(dú)立索引，實際上這是錯誤的！要選擇合適的順序和列來合并索引，來看個簡單例子：

表數(shù)據(jù)為：

分別建2個獨(dú)立索引：inx_name,inx_company：

現(xiàn)在執(zhí)行以下語句：

SELECT*fromtuserwhere`name`='22'orcompany='bb'

結(jié)果顯示并沒有使用索引來查詢數(shù)據(jù)：

現(xiàn)在加一個多列索引：inx_name_company

執(zhí)行同樣的sql顯示使用了多列索引：

不要在大文本字段建全量索引，這會然導(dǎo)致索引數(shù)據(jù)較大，查詢較慢，可以建一個前綴索引，例如

//在city列上取前7個字符作為索引mysql>altertabledemoaddkey(city(7))

這是一種使索引更小，更快的方法，但缺點(diǎn)是無法使用綴索引orderby或groupby