sql--數(shù)據(jù)庫索引

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+樹的時間復雜度對某建成的樹是固定的

?著作權歸作者所有,轉載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末承绸,一起剝皮案震驚了整個濱河市裸影,隨后出現(xiàn)的幾起案子,更是在濱河造成了極大的恐慌军熏,老刑警劉巖轩猩,帶你破解...
    沈念sama閱讀 210,978評論 6 490
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件,死亡現(xiàn)場離奇詭異羞迷,居然都是意外死亡界轩,警方通過查閱死者的電腦和手機画饥,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 89,954評論 2 384
  • 文/潘曉璐 我一進店門衔瓮,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人抖甘,你說我怎么就攤上這事热鞍。” “怎么了?”我有些...
    開封第一講書人閱讀 156,623評論 0 345
  • 文/不壞的土叔 我叫張陵薇宠,是天一觀的道長偷办。 經(jīng)常有香客問我,道長澄港,這世上最難降的妖魔是什么椒涯? 我笑而不...
    開封第一講書人閱讀 56,324評論 1 282
  • 正文 為了忘掉前任,我火速辦了婚禮回梧,結果婚禮上废岂,老公的妹妹穿的比我還像新娘。我一直安慰自己狱意,他們只是感情好湖苞,可當我...
    茶點故事閱讀 65,390評論 5 384
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著详囤,像睡著了一般财骨。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上藏姐,一...
    開封第一講書人閱讀 49,741評論 1 289
  • 那天隆箩,我揣著相機與錄音,去河邊找鬼羔杨。 笑死摘仅,一個胖子當著我的面吹牛,可吹牛的內(nèi)容都是我干的问畅。 我是一名探鬼主播娃属,決...
    沈念sama閱讀 38,892評論 3 405
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場噩夢啊……” “哼护姆!你這毒婦竟也來了矾端?” 一聲冷哼從身側響起,我...
    開封第一講書人閱讀 37,655評論 0 266
  • 序言:老撾萬榮一對情侶失蹤卵皂,失蹤者是張志新(化名)和其女友劉穎秩铆,沒想到半個月后,有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體灯变,經(jīng)...
    沈念sama閱讀 44,104評論 1 303
  • 正文 獨居荒郊野嶺守林人離奇死亡殴玛,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,451評論 2 325
  • 正文 我和宋清朗相戀三年,在試婚紗的時候發(fā)現(xiàn)自己被綠了添祸。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片滚粟。...
    茶點故事閱讀 38,569評論 1 340
  • 序言:一個原本活蹦亂跳的男人離奇死亡,死狀恐怖刃泌,靈堂內(nèi)的尸體忽然破棺而出凡壤,到底是詐尸還是另有隱情署尤,我是刑警寧澤,帶...
    沈念sama閱讀 34,254評論 4 328
  • 正文 年R本政府宣布亚侠,位于F島的核電站曹体,受9級特大地震影響,放射性物質發(fā)生泄漏硝烂。R本人自食惡果不足惜箕别,卻給世界環(huán)境...
    茶點故事閱讀 39,834評論 3 312
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望滞谢。 院中可真熱鬧究孕,春花似錦、人聲如沸爹凹。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,725評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽禾酱。三九已至微酬,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間颤陶,已是汗流浹背颗管。 一陣腳步聲響...
    開封第一講書人閱讀 31,950評論 1 264
  • 我被黑心中介騙來泰國打工, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留滓走,地道東北人垦江。 一個月前我還...
    沈念sama閱讀 46,260評論 2 360
  • 正文 我出身青樓,卻偏偏與公主長得像搅方,于是被迫代替她去往敵國和親比吭。 傳聞我的和親對象是個殘疾皇子,可洞房花燭夜當晚...
    茶點故事閱讀 43,446評論 2 348

推薦閱讀更多精彩內(nèi)容