1、MySql數(shù)據(jù)庫(kù)索引數(shù)據(jù)庫(kù)索引課件索引基礎(chǔ) 索引是提高查詢速度的最重要的工具。當(dāng)然還有其它的一些技術(shù)可供使用，但是一般來說引起最大性能差異的都是索引的正確使用與否。為了滿足應(yīng)用的高效數(shù)據(jù)查詢需要，選擇合適的表類型以及數(shù)據(jù)類型也至關(guān)重要。建表-表類型MySQL中表的類型表現(xiàn)為不同的存儲(chǔ)引擎，同一種存儲(chǔ)引擎在不同的應(yīng)用中表現(xiàn)出的性能差異也是不容忽略的重要因素。因此，在建表之初應(yīng)當(dāng)正確選擇表的類型。MyISAM和InnoDB兩種使用比較多的存儲(chǔ)引擎，MyISAM類型(不支持事務(wù)，不支持外鍵，對(duì)訪問完整性無要求）在select操作多的應(yīng)用中優(yōu)勢(shì)明顯；InnoDB在insert、update操作多的應(yīng)用

2、中優(yōu)勢(shì)明顯。數(shù)據(jù)庫(kù)索引課件索引基礎(chǔ)建表-數(shù)據(jù)類型高效的查詢也要求在合適的表字段上使用好的索引策略，所以創(chuàng)建表的字段類型選擇對(duì)于快速查詢也有很重要的影響。 選擇數(shù)據(jù)類型來幫助提高查詢運(yùn)行速度。 把數(shù)據(jù)列定義成不能為空（NOT NULL）。這會(huì)使處理速度更快，需要的存儲(chǔ)更少。它有時(shí)候還簡(jiǎn)化了查詢，因?yàn)樵谀承┣闆r下不需要檢查值的NULL屬性。 考慮使用ENUM數(shù)據(jù)列。如果某個(gè)數(shù)據(jù)列的基數(shù)很低（包含的不同的值數(shù)量有限），那么可以考慮把它轉(zhuǎn)換為ENUM列。ENUM值可以被更快地處理，因?yàn)樗鼈冊(cè)趦?nèi)部表現(xiàn)為數(shù)值。數(shù)據(jù)庫(kù)索引課件索引基礎(chǔ) 以下是幾種常用數(shù)據(jù)類型在MySQL中的性能順序（從高到低）INT CHA

3、R VARCHAR TEXTENUM介于INT與CHAR性能行之間；在相同數(shù)據(jù)類型的情況下，長(zhǎng)度短的性能更高，因此在建表時(shí)要充分考慮每個(gè)字段最合適的類型及長(zhǎng)度。數(shù)據(jù)庫(kù)索引課件索引類型 B-Tree索引一般來說，MySQL 中的 B-Tree 索引的物理文件大多是以 Balance Tree 的結(jié)構(gòu)來存儲(chǔ)的，也就是所有實(shí)際需要的數(shù)據(jù)都存放于 Tree 的Leaf Node，而且到任何一個(gè) Leaf Node的最短路徑的長(zhǎng)度都是完全相同的，所以把它稱之為 B-Tree 索引。不過，可能各種數(shù)據(jù)庫(kù)（或 MySQL 的各種存儲(chǔ)引擎）在存放自己的 B-Tree 索引的時(shí)候會(huì)對(duì)存儲(chǔ)結(jié)構(gòu)稍作改造。如 Inn

4、oDB 存儲(chǔ)引擎的 B-Tree 索引使用的存儲(chǔ)結(jié)構(gòu)實(shí)際上是 B+Tree，在 B-Tree 數(shù)據(jù)結(jié)構(gòu)的基礎(chǔ)上做了很小的改造，在每一個(gè) Leaf Node 上面除了存放索引鍵的相關(guān)信息之外，還存儲(chǔ)了指向與該 Leaf Node 相鄰的后一個(gè) Leaf Node 的指針信息，這主要是為了加快檢索多個(gè)相鄰 Leaf Node 的效率。數(shù)據(jù)庫(kù)索引課件索引類型在 InnoDB 存儲(chǔ)引擎中，存在兩種不同形式的索引，一種是 Cluster 形式的主鍵索引（Primary Key），另外一種則是和其他存儲(chǔ)引擎（如MyISAM存儲(chǔ)引擎）存放形式基本相同的普通 B-Tree 索引，這種索引在 InnoDB 存儲(chǔ)

5、引擎中被稱為 Secondary Index。 數(shù)據(jù)庫(kù)索引課件索引分類主鍵索引與第二索引數(shù)據(jù)庫(kù)索引課件索引類型使用B-Tree索引的查詢類型B-Tree索引能夠很好的用于全鍵值，鍵值范圍以及鍵前綴的索引查找，它只有在查找時(shí)候使用了最左前綴的時(shí)候還有用，有效的查詢類型如下：匹配全名：全鍵值匹配指和索引中的所有列匹配。匹配最左前綴：按照索引順序，只匹配前幾列。匹配列前綴：按照這種索引順序，只匹配索引第一列中以某字符串開頭的所有結(jié)果。匹配范圍值：可以查找大于某一個(gè)值且小于某一個(gè)值的集合，但是只能用于第一列。數(shù)據(jù)庫(kù)索引課件索引類型精確匹配一部分，并且部分匹配另一部分：查找索引前幾個(gè)字段精確匹配，后面一

6、個(gè)字段的模糊匹配。只訪問索引的查詢：B-Tree索引通常能支持只訪問索引的查詢，它不需要訪問數(shù)據(jù)行（覆蓋索引）。上面的查找方式適用于查找，同樣也適用于order by排序。B-Tree索引的局限：1.沒有從索引列的最左邊開始將不起作用。2.不能跳過索引中的列，不然只有索引前一部分的索引生效。3.不能優(yōu)化第一個(gè)范圍條件右邊的列。數(shù)據(jù)庫(kù)索引課件索引類型哈希索引 對(duì)于每一行，存儲(chǔ)引擎根據(jù)被索引的列計(jì)算出哈希碼，它是一個(gè)很小的值，并且有可能和其它行的哈希碼不同，它把哈希碼保存到索引中，并且保存了一個(gè)哈希表中每一行的一個(gè)指針。 在哈希索引的數(shù)據(jù)結(jié)構(gòu)中，索引的哈希值是有序的，但是指針指向的數(shù)據(jù)行不是有序的

7、。 哈希索引哈希值的長(zhǎng)度不會(huì)依賴于索引列，TINNYINT的索引和大型字符串的索引大小是一樣的。所以查找速度還是很快的。但是也有一些缺點(diǎn)：1.只存取了哈希碼和指向行的指針，不是值本身，因此不能避免讀取行。2.哈希索引不能進(jìn)行排序，因?yàn)樗粫?huì)按序保存行。3.不支持索引的部分匹配，因?yàn)楣４a是所有索引列計(jì)算出來的。4.碰撞率很高時(shí)，索引維護(hù)操作很慢，因?yàn)椴坏米x取每一行進(jìn)行匹配。數(shù)據(jù)庫(kù)索引課件索引類型 Innodb存儲(chǔ)引擎可實(shí)現(xiàn)自適應(yīng)的哈希索引，當(dāng)Innodb注意到一些索引值被頻繁訪問的時(shí)候，它會(huì)在B-Tree頂端建立起內(nèi)存中的索引。這使它能快速的哈希查找。這個(gè)過程是全自動(dòng)的，不能控制也不能配置?？?/p>

8、間索引： 使用地理空間數(shù)據(jù)類型，不要求where語(yǔ)句使用最左前綴，全方位的索引數(shù)據(jù)查找。全文索引：它主要是從文本中查找關(guān)鍵字不是直接和索引中的值比較。它是基于數(shù)據(jù)關(guān)聯(lián)性的搜索。數(shù)據(jù)庫(kù)索引課件索引高效使用隔離列 如果你在一個(gè)查詢中不隔離已經(jīng)索引的列，一般來說MySQL就不會(huì)使用這個(gè)列上的索引，除非在查詢中隔離這個(gè)列?！案綦x”列的意思就是，這個(gè)列不能是一個(gè)表達(dá)式的一部分或者被放在一個(gè)函數(shù)的內(nèi)部。例：SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;前綴索引和索引選擇性對(duì)很長(zhǎng)的varchar列，BLOG,TEXT使用索引，就會(huì)使索引變大

9、且使查詢很慢，可以只索引開始的幾個(gè)字符，使索引空間減少。但是前綴索引可能帶來索引選擇性比較低。需要對(duì)真實(shí)的數(shù)據(jù)，逐一遞增索引列的長(zhǎng)度，直到選擇出的選擇性與全部索引選擇性相近時(shí)，使用此前綴長(zhǎng)度就可以。而對(duì)于數(shù)據(jù)不均的情況下，不能看平均情況，看到達(dá)到某一前綴長(zhǎng)度，選擇率不會(huì)明顯升高時(shí)，使用選取的索引前綴長(zhǎng)度即可。數(shù)據(jù)庫(kù)索引課件索引高效使用聚集索引它不是一種單獨(dú)的索引類型，而是一種存儲(chǔ)數(shù)據(jù)方式。Innodb聚集索引實(shí)際上是在同樣的數(shù)據(jù)結(jié)構(gòu)中保存了索引和數(shù)據(jù)行。當(dāng)表有聚集索引時(shí)候，數(shù)據(jù)行是保存在索引的葉子節(jié)點(diǎn)中。但節(jié)點(diǎn)頁(yè)只包含被索引的列。MySql存儲(chǔ)引擎目前不能選擇聚集的列，Innodb目前是按照主

10、鍵進(jìn)行聚集。如果沒有主鍵，Innodb會(huì)使用非空的唯一索引，否則會(huì)構(gòu)造隱藏的主鍵。聚集索引優(yōu)點(diǎn)：1.可以將相關(guān)數(shù)據(jù)保存在一起，按照某一字段聚集，可以只提取幾個(gè)頁(yè)面就獲取所有需要的信息。2.數(shù)據(jù)訪問快，因?yàn)樗饕蛿?shù)據(jù)都保存在同一顆B-Tree中。聚集索引缺點(diǎn)：1.維護(hù)聚集索引時(shí)昂貴的，插入速度嚴(yán)重依賴插入數(shù)據(jù)順序，更新聚集索引時(shí)候，強(qiáng)制更新行到新的位置。數(shù)據(jù)庫(kù)索引課件索引高效使用2.建立在聚集索引上的表在插入新行，而行被移動(dòng)到的數(shù)據(jù)頁(yè)已經(jīng)滿了的情況，就會(huì)分頁(yè)，分頁(yè)導(dǎo)致需要分配更多的存儲(chǔ)空間。3.聚集表可能比全表掃描慢，尤其當(dāng)表數(shù)據(jù)存儲(chǔ)比較稀疏或者因?yàn)榉猪?yè)等造成數(shù)據(jù)的多次隨機(jī)讀取。4.第二索引（非

11、聚集）索引需要兩次索引查找，因?yàn)榈诙饕~子節(jié)點(diǎn)包含了被引用行的主鍵列。下圖展示了聚集索引（Innodb）與非聚集索引（MyISAM)的索引布局：數(shù)據(jù)庫(kù)索引課件索引高效使用2.在聚集索引的表中插入新行，當(dāng)行必須被移動(dòng)的時(shí)候，會(huì)進(jìn)行分頁(yè)。分頁(yè)會(huì)占用更多的存儲(chǔ)空間。3.第二索引會(huì)變大，因?yàn)樗鼈內(nèi)~子節(jié)點(diǎn)要包含被引用行的主鍵列。這樣第二索引的查找需要兩次，首先找到引用行的主鍵列，然后查找行數(shù)據(jù)。數(shù)據(jù)庫(kù)索引課件索引高效使用覆蓋索引：索引是找到高效的行，但如果索引葉子節(jié)點(diǎn)包含了索引的數(shù)據(jù)，就不需要讀取行，這樣能夠滿足查詢需要數(shù)據(jù)的索引，叫做覆蓋索引。覆蓋索引的優(yōu)點(diǎn)：1.索引通常都遠(yuǎn)小于全行大小，只讀取索引

12、，可以減少數(shù)據(jù)的訪問量。2.索引時(shí)按照索引值有序的，要比從磁盤中隨機(jī)讀取某一行的速度要快。3.覆蓋索引對(duì)于Innodb特別有用，Innodb聚集緩存，Innodb在第二索引葉子節(jié)點(diǎn)上保存了行的主鍵值，因此覆蓋了查詢的第二索引在主鍵中避免了另外一次索引查找。Innodb在葉子節(jié)點(diǎn)保存了主鍵值的第二索引，也就意味著Innodb第二索引獲取覆蓋查詢的額外列主鍵列。數(shù)據(jù)庫(kù)索引課件索引高效使用如下在innodb上查詢，在last_name上有索引，即使actor_id列不是該索引一部分，但是也可以覆蓋取得主鍵actor_id的查詢。Select actor_id,last_name from actor

13、 where last_name=zhang;排序索引掃描：MySql產(chǎn)生排序結(jié)果有兩種方式，一種是文件排序，另外一種是掃描索引。使用EXPLAIN輸出type值為index，表明MySql會(huì)掃描索引。注意：1.如果沒有使用索引覆蓋查詢，就不得查找在索引中發(fā)現(xiàn)的每一行，這是隨機(jī)I/O的，這要比順序掃描表慢的多。2.按照索引進(jìn)行排序，當(dāng)索引的順序和oder by字句字段的順序一樣時(shí)，并且排序方向一致時(shí)，才能使用索引排序。排序使用索引一樣要使用最左前綴。3.order by不使用最左前綴規(guī)則一種情況是前導(dǎo)列為常量。如下查詢：查看解釋器不會(huì)看到。數(shù)據(jù)庫(kù)索引課件索引高效使用rental表在（renta

14、l_date,inventory_id,customer_id)上有索引，查詢語(yǔ)句：select rental_id,staff_id from rental where rental_date = 2012-05-25 order by inventory_id, customer_id;多余和重復(fù)索引Mysql允許在同一個(gè)列上創(chuàng)建多個(gè)索引，但是Mysql不得單獨(dú)維護(hù)每一個(gè)索引，查詢優(yōu)化器在查詢的時(shí)候會(huì)逐個(gè)考慮它們，這樣會(huì)影響性能。重復(fù)索引是指類型相同，以同樣順序在同樣列上創(chuàng)建的索引。應(yīng)避免重復(fù)索引，并將多余的重復(fù)索引刪除。如下創(chuàng)建表語(yǔ)句：Create table testID INT NO

15、T NULL PRIMARY KEY,UNIQUE(ID),INDEX(ID);數(shù)據(jù)庫(kù)索引課件索引高效使用多余索引和重復(fù)索引是不同的，比如已有（a,b)上的索引，而在列（a)上的索引就是多余的，因?yàn)椋╝,b)索引能當(dāng)成（a）上索引使用。但是（b)上的索引不是多余的，因?yàn)樽钭笄熬Y規(guī)則。注意項(xiàng)：1.多余索引發(fā)生在已有索引基礎(chǔ)上，添加索引，導(dǎo)致如（a)上有索引，又添加一個(gè)（a,b）的單獨(dú)索引。這種情況，應(yīng)該是擴(kuò)展已有索引而不是新增。 2.有的情況下，處于性能考慮，需要多余索引，比如在一個(gè)整數(shù)列上有索引，將把它擴(kuò)展到一個(gè)很長(zhǎng)的varchara列時(shí)，原來只使用第一個(gè)整數(shù)列的索引會(huì)變得很慢。示例:1.select count(*) from userinfo where state_id =