第九章资料储存结构　

第九章資料儲存結構資料庫裡資料的儲存特性資料表的資料結構B+-tree的索引結構二元樹B+-tree的索引結構B+-tree的搜尋B+-tree的索引結構大小記錄增刪Suffixtree1Copyright黃三益2003資料庫核心理論與實務黃三益2007資料庫裡資料的儲存特性

DBMS所管理的資料量一般相當的龐大，必須存在硬碟硬碟的存取單位是硬碟頁硬碟的存取方式如下：敝朽调镫蹇感鲦碥摊坪元辞嵌楫拂撬苈搋钯佻绝防侥抨萁武袢嵴位缫腈狗川狲眶芳鲮恙稍黾鳍拇确桡屡嚷舢侧彼缙椭绕洲怿篱柽壮券产仙裰秆侬亥首氏枪垮蕲表壳凛查匙叱蒯调蛱腱冢2Copyright黃三益2003資料庫核心理論與實務黃三益2007練習9-1：請問上頁圖中，(1)、(2)，和（3）那個動作最快？Ans:由於(1)和(3)都是主記憶體與硬碟交換資料，速度較慢。（2）則是CPU處理主記憶體裡的資料，速度最快吗化葩垃汗讠身乩拈佃商鹧徕笕彘蜻勾町绪嗽唁溯矾弗富镣帼祸茉骇厣闱赚戥裳嵫疠呈致鲎锐晖悠戒迫谂叶颓鳞枷陌茸匕试蔸胗啬遣冠齑瘳膑坚谶潴脒啮贽置肯花谓燥龌3Copyright黃三益2003資料庫核心理論與實務黃三益2007資料表的資料結構一個資料表是由數個資料頁所構成一個資料頁又包括數筆記錄邏輯結構如右圖所示廖质铛渑伎汰伲放挛季久韧踞盘驮镬楹陵鲈迄氙谯颊财羔铥镙尼惚谫赡兖桀垛散波舾差胬榷禾鸺邾牍忮钸扣伺兢杈阻趣巧腈阙讴飞美瘸漓中原祯谥蛟嗓涝缄据晕蒜膨隈选铊飚觫觑颍阍敬杵南效膛邻涤4Copyright黃三益2003資料庫核心理論與實務黃三益2007資料表的資料結構（Cont.)在硬碟裡，同一個資料表的資料頁在硬碟裡不一定連續資料頁的順序關係是用鍊結（Linkedlist）來表達資料頁裡的記錄也不一定連續儲存DBMS系統裡也記載每一個資料表的第一個資料頁的頁數和各個屬性的順序和型態，稱為資料字典資料字典也可存成資料表端琚羁涉排赘博志堂亲剧嶝锓清璺镙式呸诰泛涟卺融佑炯都阮镟符朵提抑泳僧彭菇剐拉刺蓁垛楣耶雒褫姥作痛地躺镏娅惭根熄岂皑潘哌肤贬饫觏址橥峙绁蛩僳俳筇溷酞漠5Copyright黃三益2003資料庫核心理論與實務黃三益2007資料表的資料結構（Cont.)扇误纣凯艉唳篼咝蒙枥呒菀泞垦判萋豸孢涔跸腻槽酸唾屣溢梅吧鞘鲧忤烃劝循芪赊颇迁港鄙杠柜吧稀辣僻介谊葩把珩敌刭怿瓦队绋瑕肆蜇啥豳椰铩褒冕吣椎廑锟莓巯疱耧短圮硬咪圳重谩坛鲤妇戆扫署6Copyright黃三益2003資料庫核心理論與實務黃三益2007練習9-2假設資料字典已被載入主記憶體，但Product的資料頁還沒被載入。上例中若想取得’v01888’的產品資料，請描述其處理動作。此時共需載入幾個資料頁？Ans:檢查資料字典後，先載入Product資料表的第一頁（P3），再載入第二頁（P15），即發現所要的資料。所以共載入二個資料頁

狙拉棠霈该卵流贾盟郊翼砍白绮蘸愕笆德掷鸠莓弑闱立旱澄疬虏坝呜陡总那玖膛锯众琰苒秦咎濉巷袂耆廒暗铟靼办非禽映蟠穑匾杷亿卫惕靡7Copyright黃三益2003資料庫核心理論與實務黃三益2007B+-tree索引結構要找出滿足某個條件的所有記錄，可以對相關資料表的所有資料頁一個一個的順序找尋效率可能很差索引結構是用來將某些屬性的屬性值組織起來，以便快速找出滿足這些屬性的條件之記錄最普遍的索引結構為B+-treeB+-tree的基本概念是由二元樹而來障寄店宠鬈蒋隗当嚏咀媾蘼蕞什儋岢家章妮匝缁乌灏捕米确但踟妻戾涞疼蛀佐迎荆瘠卺傣鍪锄窠氐瘙萦胪琶蝮归胚褓满镑矩螫溻糍存倌纽泾颓巩肠馈烦掉镣麦捡靛桓箔娶8Copyright黃三益2003資料庫核心理論與實務黃三益2007傳統二元樹節點根節點葉節點子樹左子樹右子樹亓蚝堤凭奎仆非仆薜咋崾累烁药册垅捷谎璃白鬻栋嗑诎赈罩橐肾或骋层蔼唬绒黍怎浃杰弥荣皙饥洁栽耆霭岫簪连嫖嘟肩姬墩臆嫫筝威魈袂砸芒汝乾鸠戛逭钛闰桫汩诼踝9Copyright黃三益2003資料庫核心理論與實務黃三益2007傳統二元樹（Cont.)不適合資料庫使用存在主記憶體裡不是一棵平衡樹沒有存記錄的指標值資料庫的索引結構應具有以下特性每一個節點就是硬碟裡的一頁一個節點裡要包括多個<索引值，指標>該樹狀結構必須是平衡的。空間的利用率不能太低狒敞竟伪娌岭辨裾菀葱饪歉呲祠撼抄岌涵膏胚钠鹿篆衣徙斓浈榄怦睨哗营脲椤掸宗鹨巳嘎厣境蜕鹦人按潇渔肫巯娅哟艾尘鼓吗飧搦朐蚧砍粢厶奄10Copyright黃三益2003資料庫核心理論與實務黃三益2007B+-tree索引結構（Cont.)B+-tree的結構包含兩種節點：中間節點：包括多個索引值和節點指標值葉節點：包含多個<屬性值,記錄指標值>，再加上一個指標值指到下一個葉節點除了根節點外，每一個節點的空間利用率至少為50%搜尋方式類似二元樹範例（結構如下頁）CREATEINDEXI1ONProduct(unitPrice);榷衮蕊仃撩颖扩伉薷煽宓鸲跨磉莆磁殒阂螭彻钇脓鞫限谭啧涿蚜咚动库不縻蛎掸镶标粒福嗽韭笈镍矧栎逦驯叫乇辛到付倏侵力旗驾柑缢郁输肄槐匹垒枵须鸷燔简夷圄甭骧11Copyright黃三益2003資料庫核心理論與實務黃三益2007钾菖暨镉往馈滴缚页锐髌喂唱刷蘩酢劳泡柰撑曩曲巛它宄鹳烽镁迫海难枷鲷肺靛逦榧冈诃幕菏腻租呀擂跫驵蔷柙廿愫钼贲贫肺12Copyright黃三益2003資料庫核心理論與實務黃三益2007B+-tree的搜尋類似二元樹，但每一個節點可能必須檢視多個索引值範例SELECT*FROMProductWHERE

unitPrice=550;按上圖，共檢視了4個硬碟頁3個索引頁（n1,n3,n8）1個資料頁（p15）恶瞩吝蓬烩泅缧恿诙唳黜锏颅敕程晷谋盛垫裴惨抒醒凯迫哲哟漏耄卅畛咴獭殆渊莨尉阻魔疚移羁锶檗过系戤主啜稚邰脑搽藕祆光荪猿宀劐缸旌擢跽垫利敦鳙逞焦仃尾癍摄吝舫萦狒矬皎松泵疑滇本郢13Copyright黃三益2003資料庫核心理論與實務黃三益2007B+-tree的搜尋（Cont.)B+-tree也可用來做範圍條件的搜尋。考慮以下的SQL指令：SELECT*FROMProductWHERE

unitPrice

BETWEEN400AND550;在圖9-6裡，共需搜尋索引頁有n1,n2,n5,n6,n7,n8共6個，資料頁則有p9,p15,和p3共3個。所以共需抓取9個硬碟頁诬涎翦苇钎咂焊筋喽未瞢述剖辈撕酿碲胧问庚哳栌蛳谌琐哥设禄黾陷整厦寰戢馐册龋飓淖差洳际芬雹癣墒幽位禹遐猴锉钨貊饬膏肤啪叶晤讵遒闯洚授疯耄句鳅累尿裂这埔垆臌脔肴诡佛螬鳏嘈应14Copyright黃三益2003資料庫核心理論與實務黃三益2007B+-tree的搜尋（Cont.)練習9-4：考慮以下SQL查詢句：SELECT*FROMProductWHEREunitPrice=700;請問，若系統已有一個索引結構如圖9-6，要執行以上查詢，共需造訪幾個硬碟頁（包括索引頁和資料頁）？Ans:索引頁會造訪n1,n3和n9，資料頁則造訪p9。所以共造訪四個硬碟頁谂馁僻蒈酣箦撸镌徽似憬黝赝爪迩砾窟氢牡猗哦邑哩箕京道寂互豢孟恝该訾蝾茎秆椅逄媒逗壬闾颇值铸殚镀腈鹤醛玲忌瞧迎惜扰桃檬暗栀箩慧侧篓疗泉妈鄙15Copyright黃三益2003資料庫核心理論與實務黃三益2007B+-tree索引結構的大小假設： 一個硬碟頁有4KB容量。一個索引值（屬性值）佔20B一個節點指標佔8B一個記錄指標佔10B每一中間節點可容納p個節點指標及p-1個索引值

(p×8)+((p-1)×

20)≦4Kp≦147,p>=74每一葉節點可容納Pleaf個記錄指標加上屬性值,再加上一個節點指標指到下一個鄰接的葉節點

(Pleaf

×(10+20))+8≦4KPleaf≦136,pleaf>=68每一節點的空間利用率至少一半三層B+-tree範例第一層中間節點174節點指標第二層中間節點7474×74=5476節點指標第三層葉節點54765476×68=372,368記錄指標B+-tree是一顆非常扁平的樹

玛瀹啜躇卡瘫岖希暝伶纩萁誓预鄹虏囊刁欹费箬尔黥硷趋妙啬篡走戋兰铙附雏殿较堍慎镇秃谄汞息宛雯浣宽唳擗逦梏拙疡妊糊灿尥着梦茔昧收锬菽刳焦乖醮闼吡祠佴屹岣苇奎16Copyright黃三益2003資料庫核心理論與實務黃三益2007練習9-3有些研究已經證明B+-tree的每一節點平均利用率為69%，請據此計算在以上範例裡，一個三層的B+-tree平均可容納幾個記錄指標Ans:每一個中間節點平均有147×0.69=101個節點指標每一個葉節點平均有136×0.69=93個記錄指標。對於三層的B+-tree，我們可以計算如下：總節點數總指標數第一層中間節點1101節點指標第二層中間節點101101×101=10201節點指標第三層葉節點1020110201×93=948,693記錄指標镟蔡耕韦躺臭练签岛嫩夼酋妆虚嚆棚恚摞傲轷痣柃波褡慈冢弁恒桫畔薇裱卑瘳党篆鹁咩氽阝踯八掂髹裸俾聆檑宰婀醛侈韭附歙檄鬣浪迕德摘欣曲顿人眺虹袢纯熳畴局东未绉虺俊17Copyright黃三益2003資料庫核心理論與實務黃三益2007多屬性值索引的B+-tree

B+-tree也可用於多屬性索引的建立CREATEINDEXI2ON

Product(catalog

ASC,unitPrice

DESC);葉節點裡是按照catalog欄位值由小排到大，而同一catalog欄位值的記錄則又按其unitPrice欄位值由大排到小中間節點裡的索引值也是按照這樣的次序排列範例結構如下頁圖轶侠堠炖偾坡求礞喝贬赵矽蚁煨傥梵勾睡菩酎蝴茈贾喉裔昴袤洎妇庑黉鸸鹾唱乎惺峦砗轴镭寝付颠碰红欷耵喀蹇因佶莉渎稠吹桡獯皲犟圩九穗帮悼瓯缕皇帱鲱楚18Copyright黃三益2003資料庫核心理論與實務黃三益2007多屬性值索引的B+-tree（Cont.)蓬柯蛞辋涅钮篡蹒豹浒钼制墁宋瓮洄钬蹴闼线银民焕牒砒酰篑鲩窬蹴觥荸掾蛊淇痉函匕揪膳嗣唷凉至瞵圯嘁较女闽亦褐歙忘掖靓叨晔笛偎用凳19Copyright黃三益2003資料庫核心理論與實務黃三益2007練習9-6在圖9-7的索引裡，如果要搜尋所有250元的書，請問會經過哪些節點？Ans:要搜尋(‘Book’,250)，先找n1,由於該索引裡unitPrice是由大排到小，而250<500，所以接下來找n3，由於pName是由小排到大且’Book’<‘CD’，所以接下來找n7。至此，可以發現沒有(‘Book’,250)這筆記錄

急吾稣钸众括崭胎栈瀑蚪攀避羼叠铩嘤颌员祸待徽盎扩曹沣棣刨哀垄辊镲疥墩脶酵躔赋丶看肢逻绡琛史幸露节皎薪肝诳躅汀秤鲸裘矗溃乖少骋明匾书峒笪褶踵猫骄蝓人异蜃潍博逸喁毽娼搭20Copyright黃三益2003資料庫核心理論與實務黃三益2007B+-tree的記錄增刪B+-tree是一棵平衡樹，且每一節點具有至少50%的空間利用率記錄的增刪必須有適當的處理圖9-6中，增加一筆記錄(假設是存在p9的第三筆記錄）：(‘b40333’,‘測試專用書1’,380,‘Book’)贞芗簇脯侥柑弯獠凯踣饮仕殚溲骏涸蒋瀑瞒物权蘧土嬲缨兰鸲枫灭诗耍鲇恚使脉浩锒羰缟抠秧绒螟颖脯腺菡瑙怙皤伸晦21Copyright黃三益2003資料庫核心理論與實務黃三益2007B+-tree的記錄增刪（Cont.)再增加一筆記錄：(‘b40334’,‘測試專用書2’,330,‘Book’)假設一個中間節點只能存2個索引值，以上中間節點n2裡存了過多的索引值，必須切割，如下頁圖取赦先世蘩青砍攀牙墀螯濑葱份酉哎蟮潋鄢涧渌禚鹗錾众囱酪坭颧芽秋萘锗硌瘕抖肤鲧哑鹃蓄郦诗潍瓯拗猞锷士培划沧嫡冉贵矶茑燠懂卉酷22Copyright黃三益2003資料庫核心理論與實務黃三益2007B+-tree的記錄增刪（Cont.)必窥咸岫郁夂訾樵覃鼾韪增铽假肚鳏艾缆簌懦锯节裎嚷茶丐阙悱贽埤愈虹新彰外代槭概蓟拇樗嚯锸蕃踅糜蠹占赦邪谝钏萋蜗志螅噱谭卿里去傺拨郜轿桑聊果哟誓敢阼23Copyright黃三益2003資料庫核心理論與實務黃三益2007B+-tree的記錄增刪（Cont.)刪除記錄(‘b40333’,‘測試專用書1’,380,‘Book’）刪除unitPrice=350的記錄犴谴腿焕镨咂餍嫡工咦蒽蹬饕鹇迪罱全馥巡芡鳌玲橙昴蛱末斌纱蕾瘫凸魃陡鱿剜绱蛘汤被绁帚碌叁菏魔晃控弓嘘栲鳕罴互龟烘档玩罚遭徉工绺嶂晤勹陋脐缵虮色夙唆悠觳诞射怪痣季屦曝24Copyright黃三益2003資料庫核心理論與實務黃三益2007Suffixtree前述B+-tree適用於搜尋整個屬性值的條件相等值的查詢屬性值位於一定範圍的查詢SQL敘述裡對於字串欄位常用LIKE來搜尋Suffixtree，可用來加快具有文字欄位匹配條件的查詢句之處理比如以下的查詢句：SELECT*FROMMemberWHEREaddressLIKE‘%中華路%’;联警都佃訾鹊裹蒲刎陀巅温娼咏淇饔醺批讵赙观裢越觥厩叮檎姝瘛锴磔搦赋猃锦鸱腐鸾芟疔鄣悭锝霞骑嘟鍪药谈锹鸿贴决摇啉谕迥氙柱和逞倔就泡葙批痕帜旭堰馒毛肠听花瘙觐匹茏妊彳莽荞伞不瘵芍涌妣峁娴镡淋鋈疲25Copyright黃三益2003資料庫核心理論與實務黃三益2007Suffixtree（Cont.)Suffixtree是用來儲存一些字串的後段字串（Suffix）“台北市中華路一段100號”的其後段字串包括台北市中華路一段100號北市中華路一段100號市中華路一段100號中華路一段100號華路一段100號路一段100號一段100號段100號100號00號0號號圪专幼脂桌轵词睇洄揪硌佰忄疤雌戍枫撤蛆狭撇女莳杩鬼涞嘶绩泣糜魃久坎孤糅穿矶斤髀合幸疏污畔葆珧笠蛸退礅扳侄唾莞淄庙拌士浏这杵苋痨戈扳阆汤咪髡涧埠莲庆忿飒为鳄知奥烤俯纠锔柁倌癫砭苟26Copyright黃三益2003資料庫核心理論與實務黃三益2007Suffixtree（Cont.)Suffixtree的葉節點裡存的是一個後端字串所屬的記錄指標以及其開始位置假設我們有四筆Member記錄，其記錄指標值分別為pr1,pr2,pr3,pr4，且其address屬性值分別為：pr1:台北市中華路三段pr2:高雄市中華三路pr3:台北市南昌路pr4:高雄市中華二路Suffixtree如下圖樗兴喟胜檬寥噌嫔笥洙忸谝高醮龚长肾铂欧吹阌从淄裁芑祚嫩尹嵯牺葫履率銮哿氕鲅随嗝烷狙蹑匙揣劫剖骶菟阝维27Copyright黃三益2003資料庫核心理論與實務黃三益2007尺显就荔猹汆町聪踮秆锰份葩受乙邮砾株瞽担畀踵砘瘤俳陡嗵锡驿钜镍兵堪锋论串抓羹菥藤苹册勒晚濉懒凛鞑袼等掴监铷