索引概述:

索引(也就是mysql中常說的鍵值key)是存儲引擎用戶快速找到記錄的一種數據結構,這是索引的基本功能,索引對于良好的性能非常關鍵,尤其是當表中數據量

越來越大的時候,索引對性能的影響就越重要,在數據量較小且負載較低時,不恰當的索引對性能的影響可能不明顯,但當數據量逐漸增大的時候,性能就會急劇下降.

理解索引最簡單的方法:就是去看看一本書的"索引"部分,如果想在一本書中找到某個特定主題,一般會先看書的“索引”,根據“索引”找到對應的頁碼.在mysql中,存儲

引擎用類似的方法使用索引,先在索引中找到對應值,然后根據匹配的索引記錄找到對應的值,然后根據匹配的索引記錄找到對應的數據行.

索引的類型:

一.B-Tree索引

在mysql中,索引是在存儲引擎層而不是服務器層實現的,所以并沒有統一的索引標準;不同存儲引擎的索引的工作方式并不一樣,也不是所有的存儲引擎都支持所

有類型的索引,即使多個存儲引擎支持同一種類型的索引,其底層的實現也可能不同.

如果沒有特別指明類型,那多半說的是B-Tree索引,它使用B-Tree數據結構來存儲數據,大多數mysql引擎都支持這種索引,存儲引擎以不同的方式使用B-Tree索引,性能

也各不相同,各有優劣,例如,myisam使用前綴壓縮技術使得索引更小,但InnoDB則按照原數據格式進行存儲.再如MyISAM索引通過數據的物理位置引用被索引的行,而

InnoDB則根據主鍵引用被索引的行.

B-Tree通常意味著所有的值都是按照順序存儲的,并且每一個葉子頁到根的距離相同,B-Tree索引能夠加快訪問數據的速度,因為存儲引擎不再需要進行全表掃描來獲取

需要的數據,取而代之的是從索引的根節點開始進行搜索,根節點的槽中存放了指向子節點的指針,存儲引擎根據這些指針向下層查找.通過比較節點頁的值和要查找的值可以

找到合適的指針進入下層子節點,這些指針實際上定義了子節點頁中值的上限與下限,最終存儲引擎要么是找到對應的值,要么該記錄不存在.

葉子節點比較特別,它們的指針指向的是被索引的數據,而不是其他的節點頁(不同引擎的“指針”類型不同),根節點與葉子節點之間可能存在很多層節點頁,樹的深度與表的大小

直接相關.B-Tree對索引列是順序組織存儲的,所以很合適查找范圍數據.例如,在一個基于文本域的索引樹上,按字母順序傳遞連續的值進行查詢是非常合適的,所以像“找出所有以

I到K開頭的名字”這樣的查找效率會非常高.

可以使用B-Tree索引的查詢類型,B-Tree索引適用于全鍵值、鍵值范圍或者鍵前綴查找,其中鍵前綴查找只適用于根據最左前綴的查找.

B-Tree對以下類型的查詢有效:

1.全值匹配

2.匹配最左前綴(第一列)

3.匹配列前綴(第一列)

4.匹配范圍值(第一列)

5.精確匹配某一列并范圍匹配另外一列

6.只訪問索引的查詢

關于B-Tree索引的限制:

1.如果不是按照索引的最左列開始查找,則無法使用索引.

2.不能跳過索引列

3.如果查詢中有某個列的范圍查詢,則其右邊所有列都無法使用索引優化查找,例如有查詢條件where last_name='smith' and first_name like 'j%' and dob='1976-12-23',

這個查詢只能使用索引的前兩列,因為這里的like是一個范圍條件(但是服務器可以把其余列用于其他目的).如果范圍查詢列值的數量有限,那么可以通過使用多個等于

條件來代替范圍條件.

二.哈希索引

哈希索引基于哈希表實現,只有精確匹配索引所有列的查詢才有效,對于每一行數據,存儲引擎都會對所有的索引列計算一個哈希碼,哈希碼是一個較小的值,并且不同的鍵值

的行計算出來的哈希碼也不一樣,哈希索引將所有的哈希碼存儲在索引中,同時在哈希表中保存指向每個數據行的指針,在MySQL中,只有Memory引擎顯式支持哈希索引.這

也是Memory引擎表默認的索引類型,Memory引擎同時也支持B-Tree索引. 值得一提的是,Memory引擎是支持非唯一哈希索引的,這在數據庫世界里面是比較與眾不同的,如果

多個列的哈希值相同,索引會以鏈表的方式存放多個記錄指針到同一哈希條目中.

假如有下列表:

create table testhash(

fname varchar(50) not null,

lname varchar(50) not null,

key using hash(fname)

) engine=memory;

表中包含如下數據:

select * from testhash;

fname? ?lname

Arjen? ? ?Lentz

Baron? ? Schwartz

Peter? ? ?Zaitsev

Vadim? ? Tkachenko

假設索引使用假想的哈希函數f(),它返回下面的值(都是演示數據)

f('Arjen')=2323

f('Baron')=7437

f('Peter')=8784

f('Vadim')=2458

則哈希索引的數據結構如下:

槽? ? ? ? ?值

2323? ? ?指向第一行的指針

2458? ? ?指向第四行的指針

7437? ? ?指向第二行的指針

8784? ? ?指向第三行的指針

注意每個槽的編號是順序的,但是數據行不是.現在我們講解一下下面查詢語句的查詢過程

select lname from testhash where fname='Peter';

mysql先計算“Peter”的哈希值,并使用該值尋找對應的記錄指針,因為f('Peter')=8784,所以mysql在索引中查找8784,可以找到指向第3行的指針,

最后一步是比較第三行的值是否為‘Peter’,以確保就是要查找的行.

因為索引自身只需存儲對應的哈希值,所以索引的結構十分緊湊,這也讓哈希索引查找的速度非常快.然而,哈希索引也有他的限制,如下:

1.哈希索引只包含哈希值與行指針,而不存儲字段值,所以不能使用索引中的值來避免讀取行,不過,訪問內存中的行的速度很快,所以大部分情況下這一點對性能的

影響并不明顯.

2.哈希索引數據并不是按照索引值順序存儲的,所以也就無法用于排序.

3.哈希索引也不支持部分索引列匹配查找,因為哈希索引始終是使用索引列的全部內容計算哈希值的

4.哈希索引只支持等值比較查詢,包括=,IN(),<=>,也不支持任何范圍查詢,

5.訪問哈希索引的數據非常快,除非有很多哈希沖突(不同的索引列值卻有相同的哈希值),當出現哈希沖突的時候,存儲引擎必須遍歷鏈表中所有的行指針,逐行進行比較,

直到找到所有符合條件的行

6.如果哈希沖突很多的話,一些索引維護操作的代價也會很高,例如,如果在某個選擇性很低(哈希沖突很多)的列上建立哈希索引,那么當從表中刪除一行的時候,存儲引擎需要遍歷

對應哈希值的鏈表的每一行,找到并刪除對應行的應用,沖突越多,代價越大

因為以上的這些限制,所以哈希索引只適用于某些特定的場合,而一旦適合哈希索引,則它帶來的性能提升將非常顯著.

InnoDB引擎有一個特殊的功能叫做“自適應哈希索引”,當InnoDB注意到某些索引值被使用得非常頻繁的時候,它會在內存中基于B-Tree索引之上再創建一個哈希索引,這樣就讓B-Tree索引也

具有哈希索引的一些優點,比如快速的哈希查找.這是一個完全自動的、內部的行為,用戶無法控制或者配置,不過如果有必要,完全可以關閉該功能.

三.創建自定義哈希索引

如果存儲引擎不支持哈希索引,則可以模擬像InnoDB一樣創建哈希索引,這樣可以享受一些哈希索引的便利,例如只需要很小的索引就可以為超長的鍵創建索引.

思路很簡單:在B-Tree基礎上創建一個偽哈希索引,這和真正的哈希索引不是一回事,因為還是使用B-Tree進行查找,但是它使用哈希值而不是鍵本身進行索引查找,你需要做的就是

在查詢的where子句中手動指定使用哈希函數

下面通過一個例子進行講解,例如需要存儲大量的URL,并且需根據URL進行搜索查找,如果使用B-Tree來存儲URL,存儲的內容就會很大,因為URL本身都很長,正常情況下會有以下查詢:

select id from url where url=''

總結

以上是生活随笔為你收集整理的mysql第四项_mysql数据库的基本介绍与操作(第四篇-mysql索引篇)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。