2018-10-08
數(shù)據(jù)庫索引
索引的優(yōu)點:
通過創(chuàng)建唯一索引,可以保證數(shù)據(jù)庫表中每行數(shù)據(jù)的唯一性
可以加快查詢速度
在實現(xiàn)數(shù)據(jù)的參考完整性方面,可以加速表和表之間的連接
在使用分組和排序子句進行數(shù)據(jù)檢索時鸟款,同樣可以顯著減少查詢中分組和排序的時間
通過使用索引,可以在查詢中使用優(yōu)化隱藏器哩治,提高系統(tǒng)的性能
索引缺點:
創(chuàng)建和維護索引要耗費時間猿规,并其隨著數(shù)據(jù)量的增加耗費時間也增加
索引占用空間內(nèi)存
在對表中數(shù)據(jù)進行增加刪除和修改的時候,索引也需要動態(tài)維護尤揣,降低了數(shù)據(jù)維護速度
索引分類:
普通索引
唯一索引
復合索引:多個列上建立索引搔啊,叫做復合索引(組合索引)
聚集索引:表中行的物理順序與邏輯順序相同,一個表只能包含一個聚集索引
非聚集索引
索引失效:
WHERE字句的查詢條件里有不等于號北戏,mysql將無法索引
WHERE字句的查詢條件里使用了函數(shù)
在JOIN操作中负芋,mysql只有在主鍵和外鍵的數(shù)據(jù)類型相同時才能使用索引
如果WHERE子句的查詢條件里使用了比較操作符LIKE和REGEXP,mysql只有在搜索模板的第一個字符不是通配符的情況下才能使用索引
在ORDER BY操作中嗜愈,MYSQL只有在排序條件不是一個查詢條件表達式的情況下才使用索引旧蛾。盡管如此莽龟,在涉及多個數(shù)據(jù)表的查詢里,即使有索引可用锨天,那些索引在加快ORDER BY操作方面也沒什么作用毯盈。
如果某個數(shù)據(jù)列里包含著許多重復的值,就算為它建立了索引也不會有很好的效果病袄。比如說搂赋,如果某個數(shù)據(jù)列里包含了凈是些諸如“0/1”或“Y/N”等值,就沒有必要為它創(chuàng)建一個索引益缠。
如果條件中有or(并且其中有or的條件是不帶索引的)脑奠,即使其中有條件帶索引也不會使用(這也是為什么盡量少用or的原因)。注意:要想使用or左刽,又想讓索引生效捺信,只能將or條件中的每個列都加上索引。
如果列類型是字符串欠痴,那一定要在條件中將數(shù)據(jù)使用引號引用起來,否則不使用索引迄靠。
如果mysql估計使用全表掃描要比使用索引快,則不使用索引。
什么情況下適合建立索引:
經(jīng)常用作查詢的字段
在經(jīng)常用作表連接的屬性上喇辽,加快連接速度
在經(jīng)常使用where子句中的列上創(chuàng)建索引掌挚,加快條件的判斷速度
在經(jīng)常需要排序的列上創(chuàng)建索引
在經(jīng)常需要根據(jù)范圍進行搜索的列上創(chuàng)建索引
考慮使用索引覆蓋,對數(shù)據(jù)很少被更新的表菩咨,如果用戶經(jīng)常只查詢其中的幾個字段吠式,可以考慮在這幾個字段上建立索引
索引的實現(xiàn):
InnoDB:B+Tree
數(shù)據(jù)文件本身就是索引文件
聚集索引:表數(shù)據(jù)文件本身就是按B+Tree組織的一個索引結構,這棵樹的葉節(jié)點data域保存了完整的數(shù)據(jù)記錄
必須有主鍵抽米,如果沒有顯式指定特占,系統(tǒng)會自動選擇一個可以作為唯一標識數(shù)據(jù)
所有的輔助索引都引用主鍵作為data域
MyISAM:B+Tree
葉子節(jié)點的data域存放的數(shù)據(jù)記錄的地址
索引算法為按照B+Tree搜索算法搜索索引,如果指定的key存在云茸,則取出data域的值是目,然后以data域的值為地址,讀取相應的數(shù)據(jù)記錄
主索引和輔助索引的存儲結構沒有任何區(qū)別
索引文件和數(shù)據(jù)文件是分開的标捺,索引文件僅保存數(shù)據(jù)記錄的地址
memory:適用于快速訪問數(shù)據(jù)的場景懊纳。內(nèi)部基于哈希表數(shù)據(jù)結構的實現(xiàn),只包含哈希值和行指針亡容。為了解決多個hash沖突問題嗤疯,采用了鏈地址法來解決沖突問題
B樹和B+樹:
B樹:
B樹中每個結點包含了鍵值和鍵值對于數(shù)據(jù)對象存放地址的指針,所以成功搜索一個對象可以不用到達樹的葉節(jié)點
在B樹中查找給定關鍵字的方法:首先把根節(jié)點取出闺兢,在根節(jié)點所包含的關鍵字k1....kj查找給定的關鍵字茂缚,若找到則查找成功,若未找到,則確定要查找的關鍵字在某個k1或ki+1之間脚囊,于是取pi所指的下一層索引結點繼續(xù)查找帖汞,直到找到或者直到為空
B+樹:
非葉節(jié)點中存放的關鍵碼并不指示數(shù)據(jù)對象的地址指針,非葉節(jié)點指示索引部分凑术,所有葉節(jié)點在同一層上翩蘸,包含全部關鍵碼和相應數(shù)據(jù)對象的存放地址指針,且葉節(jié)點按關鍵碼從小到大順序連接
有兩個指針淮逊,一個是樹的根節(jié)點催首,一個是最小關鍵碼的葉節(jié)點
有兩種搜索方式:
按葉節(jié)點進行鏈表的順序搜索
從根節(jié)點開始搜索,和B樹類似泄鹏,無論搜索成功與否郎任,都將走完樹的所有層
數(shù)據(jù)對象的插入和刪除僅僅在葉節(jié)點上進行
區(qū)別:
B樹中同一鍵值不會出現(xiàn)多次,并且它有可能出現(xiàn)在葉結點备籽,也有可能出現(xiàn)在非葉結點中舶治。而B+樹的鍵一定會出現(xiàn)在葉節(jié)點中,并且有可能在非葉節(jié)點中也有可能重復出現(xiàn)车猬,以維持B+樹的平衡
因為B樹鍵位置不定霉猛,且在整個樹結構中只出現(xiàn)一次,雖然可以節(jié)省存儲空間珠闰,但使得在插入惜浅、刪除操作復雜度明顯增加。B+樹相比來說是一種較好的折中
B樹的查詢效率與鍵在數(shù)中的位置有關伏嗜,最大時間復雜度與B+樹相同(在葉節(jié)點的時候)坛悉,最小時間復雜度為1(在根節(jié)點的時候)。而B+樹的時間復雜度對某建成的樹是固定的