Gegevensbanken 2010 Indexstructuren Bettina Berendt www cs kuleuven

Gegevensbanken 2010 Indexstructuren Bettina Berendt www. cs. kuleuven. be/~berendt

Indexstructuren: Motivatie & Samenvatting 2

Waar zijn we? Les Nr. wie ED ED KV KV KV wat intro, ER EER relational model mapping EER 2 relational algebra, relational calculus SQL vervolg SQL demo Access, QBE, JDBC functional dependencies and normalisation 10 KV 11 BB 12 BB functional dependencies and normalisation file structures and hashing indexing I 13 BB indexing II and higher-dimensional structures 14 15 16 17 18 query processing transaction query security Data warehousing and mining XML, oodb, multimedia db 1 2 3 4 5 6 7 8 9 BB BB ED Fysisch model / vragen 3

Herhaling: Bestandsorganisatie • Vraag: Waar/hoe gegevens plaatsen? • Antwoord: Zo plaatsen dat de kost van operaties geminimaliseerd wordt (zoeken, invoegen, verwijderen, . . . ) • Randvoorwaarde: fysica - alles moet ergens zijn, en alles is op een plaats. Door de juiste bestandsorganisatie wordt efficiënt zoeken volgens de „primaire“, fysische structuur mogelijk 4

Dit was ook een goed idee voor het Web zoeken 1994: . . . maar nu willen we toch een beetje anders zoeken. . . lineair of binair 5

Hoe werkt dat? Indexstructuren (hier: volledig geïnverteerde bestanden) Door indexstructuren wordt efficiënt zoeken ook volgens andere criteria (velden) mogelijk 6

Gegevens zijn als … een goed boek (1) Bestandsorganisatie Primaire index 7

Gegevens zijn als een goed boek (2) Secundaire index 1 Secundaire index 2 8

Gegevens zijn als een goed boek (3) Multi-niveau index 9

PS: Dit is ook een index (of: we moeten nog dingen van de vorige les vervolledigen) * als bruin: bucket 1 * als groen: bucket 2 10

[Nog iets over les #11] Herhaling: Belangrijke parameters m. b. t. bestandsverwerking • Bestandsactiviteit (file activity) # records dat gebruikt wordt door een toepassing / totaal # records van het bestand • Bestandsveranderingsgraad (file volatility) # records dat in een bepaalde periode een verandering ondergaat / totaal # records van het bestand • Bestandsverloop of vervangingsgraad (file turnover) # records dat in een bepaalde periode vervangen wordt door nieuwe records / totaal # records van het bestand • Bestandsgroei (file growth) toename van # records gedurende een bepaalde periode / het oorspronkelijk totaal # records 11

Oefening: Vervolledig de tabel! hoge bestands. . . Bestandsorganisatie activiteit veranderingsgraad vervangingsgraad groei ongeordend direct 12

Agenda Indexen: definitie Soorten indexen Indexen met meerdere niveaus Boomstructuren als indexen Indexen op meerdere velden 13

Agenda Indexen: definitie Soorten indexen Indexen met meerdere niveaus Boomstructuren als indexen Indexen op meerdere velden 14

Indexstructuren • Definitie: – een index op een bestand = een gegevensstructuur die de toegang op dat bestand via een bepaald veld (of een groep velden) efficiënter maakt • d. w. z. : laat efficiënt zoeken naar een bepaalde waarde van dat veld toe • vgl. : woordenlijst achteraan boek, fichebak in bibliotheek, . . . • Index kan opgeslagen zijn: – in centraal geheugen (enkel redelijk kleine indexen) – in een bestand in het externe geheugen 15

Vb. : Op welke velden zouden het nuttig zijn om een index te plaatsen? 16

Agenda Indexen: definitie Soorten indexen Indexen met meerdere niveaus Boomstructuren als indexen Indexen op meerdere velden 17

Soorten indexen • primaire index : index op veld dat – de ordening van het bestand bepaalt – records uniek geïdentificeerd (d. w. z. elke waarde voor het veld is uniek) • clusterindex : index op veld dat – de ordening van het bestand bepaalt – niet noodzakelijk unieke waarden Sluiten elkaar uit • secundaire index : index op een ander veld dan dat wat de ordening bepaalt 18

Primaire indexen: voorbeeld } 1 blok 19

Primaire indexen • Primaire index: – bestand • met vaste lengte records • fysisch geordend volgens de sleutelwaarden – index: bevat 1 record per blok in het gegevensbestand: • sleutel van "ankerrecord" van het blok (= eerste of laatste record in het blok) • adres van het blok – Gegeven een sleutelwaarde, kan adres van blok waar overeenkomstig record zit, gevonden worden door zoeken in index i. p. v. gegevensbestand • d. i. dankzij de ordening in het bestand 20

Eigenschappen en voordelen • index bevat kleinere records dan gegevensbestand – enkel sleutel + adres, geen andere info • index bevat meestal minder records dan gegevensbestand – is een niet-dichte of ijle (nondense, sparse) index + index is kleiner dan gegevensbestand doorlopen van index gaat sneller dan doorlopen van gegevensbestand veel minder toegang tot schijf nodig 21

Probleem en oplossing - • Toevoegen / weglaten van gegevens: nu ingewikkelder! – Naast gegevensbestand ook index aanpassen • ankerrecords kunnen veranderen – Oplossing: • voor toevoegen: overloopgebieden • voor weglaten: markeren van weggelaten records • na een tijdje: reorganisatie 22

Berekening van performantie van indexen • Berekening van tijdswinst door index – Toegang tot hoeveel blokken is nodig? • Gegevensbestand gekenmerkt door – – – # records r # blokken b = (r / bfr) recordlengte R bloklengte B blocking factor bfr = B / R (hoeveel records in één blok? ) • Indexbestand : analoog ri, bi, Ri, Bi, bfri – meestal Bi = B – met ankerrecords: ri = b – dichte index: ri = r 23

Voorbeeld 1 (1): de gegevens • Geordend gegevensbestand: – r = 30 000 – R = 100 bytes, B = 1 024 bytes bfr = 1024 / 100 = 10 – b = r / bfr = 30 000 / 10 = 3 000 • Binair zoeken op gegevensbestand: – # schijftoegangen = log 2 b = log 2 3 000 = 12 24

Voorbeeld 1 (2): primaire index • Indexbestand: – vb. sleutel = 9 bytes, blokadres = 6 bytes – Ri = 9 + 6 = 15 bytes bfri = 1024 / 15 = 68 – ri = b = 3 000 bi = ri / bfri = 3 000 / 68 = 45 • binair zoeken op indexbestand: – # schijftoegangen = log 2 bi = log 2 45 = 6 • Uiteindelijk ook nog blok met gegevens inlezen: – 1 extra blok 7 in totaal (i. p. v. 12) 25

Clusterindex • Gegevensbestand – fysisch geordend volgens veld dat niet uniek is • dat veld is dus geen sleutel • wel "clusterveld" genoemd (records met zelfde waarde voor dat veld zitten gegroepeerd) • Clusterindex: – per waarde van clusterveld 1 wijzer naar blok waar eerste record met die waarde voorkomt ijle index 26

Clusterindexen: voorbeeld 27

Probleem en oplossing • Toevoegen – records schuiven op verandering blokadressen in index – kan opgelost worden door aparte blokken te gebruiken voor de verschillende waarden 28

Secundaire index • Index op een ander veld dan het veld dat de ordening bepaalt – index zelf is wel geordend volgens dat veld – veld kan al dan niet een sleutel zijn • Indien dit een secundair-sleutel-veld is: – 1 record in index per record in gegevensbestand • geen ordening enkel ankerrecords is onvoldoende dichte index + – nog steeds kleiner dan gegevensbestand omdat records zelf kleiner zijn (maar minder spectaculair) 29

Secundaire index op sleutelveld: voorbeeld 30

Voordelen + • Hoewel index zeer groot kan zijn: toch grote tijdswinst + • Eens blok gevonden: enkel nog lineair zoeken binnen blok – index is geordend binair zoeken mogelijk – vs. gegevensbestand: lineair zoeken nodig! – in intern geheugen gaat snel – bfr meestal relatief klein verwaarloosbaar vs. inlezen van blokken 31

Voorbeeld 1 (3): dichte secundaire index • Uit het vorige voorbeeld: – r = 30 000, R = 100 bytes, B = 1 024 bytes, b = 3 000 • Lineair zoeken in dit bestand: – gemiddeld b / 2 = 1 500 blokken inlezen • Met (dichte) secundaire index: – – stel veld 9 bytes, adres 6 bytes Ri=15 ri = r = 30 000, bfri = 68 bi = 30 000 / 68 = 442 binair zoeken : log 2 bi = log 2 442 = 9 blokken • opmerking: controle of record voorkomt, bij dichte index, kan zonder gegevens zelf in te lezen – + 1 voor gegevens zelf : 10 blokken lezen (i. p. v. 1500) 32

Secundaire index op niet-sleutel veld • dichte index – elke waarde komt even vaak in index voor als in gegevensbestand • index met variabele lengte records – per waarde een lijst wijzers naar blokken • index met verwijzingen naar blok recordwijzers – m. a. w. 1 adres per waarde – adres wijst naar blok (evt. lijst van blokken) met wijzers naar blokken in gegevensbestand 1 indirectie meer 33

Secundaire index op nietsleutelveld: voorbeeld 34

Voor- en nadelen van 2 -niveau indexen + • toevoegen / weglaten is veel gemakkelijker - • 1 extra blok te lezen 35

Overzicht indexen Anker : blok hangt vast aan sleutel (bemoeilijkt toevoegen / weglaten) Indexveld Indextype # index records is (op veldtype) ordenend veld nietordenend veld dicht of ijl Blokanker op Gegevensbestand (GB) ? primair (sleutel) # blokken in GB ijl ja clustering (niet-sleutel) # verschillende indexveld-waarden ijl ja / neen secundair (sleutel) # records in GB dicht neen # records in GB of # verschillende indexveld-waarden dicht of ijl neen secundair (niet-sleutel) 36

Agenda Indexen: definitie Soorten indexen Indexen met meerdere niveaus Boomstructuren als indexen Indexen op meerdere velden 37

Indexen met meerdere niveaus • Principe: – Gewone index op gegevensbestand kan nog steeds groot zijn – opnieuw een index bouwen bovenop deze index • laat toe waarden sneller terug te vinden in deze index • = niveau 2 index – eventueel hierbovenop nog een index, enz. – tot top-index maar 1 blok groot is • Blocking factor bfri even groot voor alle indexen = "fan-out" (fo) 38

39

Hoeveel blokken op welk niveau? – 1 e niveau: • r 1 = r records r 1 / fo blokken – 2 e niveau: • r 2 = r 1 / fo records r 2 / fo r 1 / fo 2 blokken – ke niveau: • rk r 1 / fok blokken – hoogste niveau • 1 blok – aantal niveaus t logfo (r 1) • vgl. met log 2 (r 1) voor binair zoeken • hoe groter fo, hoe minder blokken te lezen 40

Voorbeeld 1 (4): als multi-niveau index • Stel dat de dichte secundaire index uit voorbeeld 2 nu een multi-niveau index is – fo = bfri = 68 – 1 e niveau: • 442 blokken (zie eerder) – 2 e niveau: • b 1 / fo = 442 / 68 = 7 blokken – 3 e niveau: • 7 / 68 = 1 blok topniveau – Controleren of een waarde voorkomt: • 3 blokken lezen – Ophalen van gegevens zelf: • 4 blokken (vgl. 10 met binair zoeken, 1 500 zonder index) 41

Algoritme voor zoeken in ijle primaire multiniveau-index naar record met sleutel K p : = adres van top-blok van index; voor j : = t tot 1: lees blok met adres p (op niveau j in index); zoek in p een record i zodat Kj(i) <= Kj(i+1); p : = pj(i); lees het blok gegevens met adres p; zoek in p naar het record met sleutel K 42

Praktijkvoorbeeld: ISAM • IBM's ISAM = "Indexed Sequential Access Method" • is een speciaal geval van een multi-niveau indexstructuur • 2 -niveau indexstructuur: – 1 e niveau: cilinderindex • sleutel van ankerrecord voor die cilinder + wijzer naar spoorindex van die cilinder – 2 e niveau: spoorindex • sleutel van ankerrecord voor spoor + wijzer naar spoor (nu vervangen door VSAM, “virtual storage access method”) 43

Operaties in multi-niveau indexen • Weglaten: – door te markeren • Toevoegen: – m. b. v. overloopgebieden • Na een tijdje: reorganisatie – heel het bestand wordt sequentieel doorlopen en herschreven naar nieuw bestand – overloop en markeringen worden opgeruimd – nieuwe index wordt gebouwd op nieuw bestand • Voordelen: + – snelle toegang tot bestand, toevoegingen en weglatingen tamelijk efficiënt • Nadelen: - – overloop werkt vertragend, verkwisting van ruimte, geregelde reorganisatie vraagt tijd 44

Statische en dynamische structuren • Problemen met toevoegen / weglaten van records – doordat elk niveau van de indexboom fysisch geordend is – hele boom van indexen moet aangepast worden • Meer dynamische structuren kunnen oplossing bieden: Bbomen, B+-bomen 45

Agenda Indexen: definitie Soorten indexen Indexen met meerdere niveaus Boomstructuren als indexen Indexen op meerdere velden 46

Boomstructuren als indexen • Binaire zoekboom is geordend: – 1 waarde in knoop – in linkerdeelboom enkel kleinere waarden – in rechterdeelboom enkel grotere waarden • Opzoeken van waarde vraagt tijd evenredig met hoogte h van boom – "gewoonlijk" : + • h log 2 n • met n = # waarden in de boom – dus: zoeken is efficiënt 47

. . . maar. . . 48

Evenwichtigheid • Aanpassen van boom (toevoegen, weglaten): ook tijdscomplexiteit evenredig met h – gemiddeld dus ook efficiënt • MAAR: aanname van "evenwichtigheid" van bomen wordt gemaakt! – Niet onmogelijk dat h n i. p. v. log 2 n – vb. bij eenvoudig toevoeg-algoritme dat waarden reeds in volgorde krijgt • concept van evenwichtige zoekbomen – toevoegen, weglaten worden zo geïmplementeerd dat evenwicht steeds bewaard blijft 49

Zoekbomen • Een zoekboom (niet noodzakelijk binaire) – heeft in elke knoop een aantal waarden • v 1 < v 2 <. . . < vm - 1 (m kan variëren van knoop tot knoop) – heeft in een knoop met m - 1 waarden • m kinderen b 1, . . . , bm – voor alle waarden v die voorkomen in bi geldt: • vi - 1 < vi (v 0 = - , vm = + ) • Consistent met binaire zoekbomen (overal m = 2) 50

Zoekbomen: abstract 51

Zoekbomen: voorbeeld (een zoekboom van orde 3) 52

B-bomen • B-boom van orde m (m > 2) is zoekboom waarvoor : – elke inwendige knoop heeft hoogstens m kinderen – de wortel heeft minstens 2 kinderen, elke andere knoop minstens m / 2 – alle bladeren zitten even diep – "waarde" in B-boom = sleutel + adres – speciale gevallen: 2 - 3 bomen, 3 - 5 bomen, . . . – beperkingen i. v. m. min en max aantal kinderen garanderen • redelijke gebalanceerdheid • beperkte verspilling van geheugen 53

Adressen in knopen van B-bomen • Adres is een blokadres of recordadres – recordadres = blokadres + positie van record in blok • voor niet-sleutelveld: – adres van blok met wijzers naar adressen (cfr. eerdere voorbeelden) extra indirectie 54

Maximale hoogte van B-bomen • orde p minstens d = p / 2 deelbomen per knoop • op niveau 1 (onder wortel) – minstens 2 knopen, • op niveau i – minstens 2 di-1 knopen 2 di-1 (d-1) waarden h logd ( (n + 1) / 2 ) 55

B-bomen: abstract en voorbeeld 56

Voorbeeld 2 (1): Berekening orde B-boom • Stel: – grootte van veld waarop gezocht wordt V = 9 bytes – B = 512 bytes – recordadres Pr = 7 bytes, blokadres P = 6 bytes • 1 knoop van B-boom moet in 1 blok passen – max aantal deelbomen p van een knoop: • • p * P + (p - 1) * (Pr + V) B 6 p + 16 (p - 1) 512 p 24 meestal nog wat extra (administratieve) info in blok kies p = 23 57

Voorbeeld 2 (2): Aantal blokken en diepte – empirisch onderzoek toont: B-boom gemiddeld 69% vol • dus: – Gemiddeld 0. 69 p = 0. 69 x 23 = 16 wijzers • gemiddelde fan-out fo = 16 • gemiddeld aantal waarden per knoop = 15 – wortel : 15 sleutels – 1 niveau onder wortel : • 16 knopen 16 * 15 = 240 sleutels – 2 niveaus diep: • 162 = 256 knopen, 3 840 sleutels – 3 niveaus diep: • 163 = 4 096 knopen, 61 440 sleutels – totaal voor 3 niveaus: • 61 440 + 3 840 + 240 + 15 = 65 535 58

Wanneer B-bomen gebruiken? • Gebruik van B-bomen als primaire bestandsorganisatie - – dus niet voor index op bestand, maar bestand zelf – 1 waarde in knoop = sleutel + het hele record • Enkel goed bruikbaar indien – klein aantal records – kleine recordgrootte • Anders fo te klein # niveaus van boom te groot inefficiënt 59

Operaties en hun kost/efficiëntie + + - • Opzoeken : O (logd n) • Toevoegen, weglaten: – eerst positie opzoeken – wijziging aanbrengen en doorvoeren – alles in O (logd n) tijd • Sequentiele verwerking: – boom doorlopen in in-orde (links, knoop, rechts) – interne knopen vaak opnieuw gelezen, tenzij ze in centraal geheugen onthouden worden – kan beter : met B+-bomen 60

B+-bomen • Bij B-bomen: – sommige record-wijzers in interne knopen, andere in bladeren • Bij B+-bomen: – interne knopen bevatten enkel sleutels, geen adressen van records • recordadressen enkel in de bladeren • interne knopen bevatten enkel "wegwijzers" • orde pi van interne knopen is nu groter betere prestaties; orde pb van bladeren ongeveer even groot • extra: – aan het eind van een blad wijzer naar volgend blad • maakt sequentieel doorlopen eenvoudiger 61

B+-bomen: abstract 62

Voorbeeld 3 (1): Berekening orde B+-boom • Gegeven: – V = 9 bytes, B = 512 bytes, Pr = 7 bytes, P = 6 bytes • orde van interne knopen: – pi * P + (pi - 1) * V B – 6 pi + 9 (pi - 1) 512 – pi = 34 (cfr. 23 voor B-boom) • orde van bladeren: – – pb * (Pr + V) + P B pb * ( 7 + 9 ) + P B 16 pb + 6 512 pb = 31 63

Voorbeeld 3 (2): Aantal sleutels en diepte – Stel 69% vol – dan: • 0. 69 * 34 = 23 wijzers per knoop (22 waarden) • in blad: 0. 69 * pb = 0. 69 * 31 = 21 recordwijzers – gemiddeld aantal sleutels op elk niveau: • • wortel: 1 knoop, niveau 1: 23 knopen, niveau 2: 529 knopen, bladeren: 12 167 knopen, 22 sleutels 506 sleutels 11 638 sleutels 255 507 recordwijzers – Vgl. met 65 536 recordwijzers voor B-boom 64

Algoritmes • Algoritmes voor – zoeken in B+-boom en – voor aanpassing van B+-boom bij toevoegen / weglaten van gegevens – Gedetailleerde algoritmes in boek 65

B+-boom: Opzoeken van een sleutelwaarde { K = gezochte sleutel } n : = blok dat wortel van B+-boom bevat; lees blok n; zolang n geen blad is: q : = #deelbomen van n; v 0=- , v 1. . vq-1 waarden in knoop, vq=+ kies i zo dat vi < K <= vi+1; n : = bi; lees blok n; zoek in n een koppel (vi, Pri) met vi=K; indien gevonden: lees record met adres Pri anders: meld 'niet gevonden' 66

B+-boom: Toevoegen van een record met sleutel K • zoek blad waar sleutel hoort • indien niet vol: voeg sleutel gewoon toe • indien blad al vol: splits blad – – 1 e helft blijft, 2 e helft naar nieuw blad voeg sleutel toe aan juiste blad pas ook bladwijzers aan voeg laatste waarde van blad 1 in ouderknoop toe • herhaal zolang ouderknoop overvol is: – splits knoop : helft van waarden naar nieuwe knoop; verhuis laatste waarde van 1 e knoop naar ouder 67

Oefening: toevoegen van. . . 8, 5, 1, 7, 3, 12, 9, 6 68

69

70

Alternatieve oplossing 71

B+-boom: Verwijderen van een sleutel K uit gegevens • zoek blad met sleutel, verwijder sleutel daaruit • indien sleutel ergens in interne knopen voorkomt: – vervang door waarde net links ervan • indien onderloop (te weinig waarden in blad): – steel enkele waarden van naburig blad (en pas bovenliggende knoop aan) – indien nog niet voldoende: voeg 2 bladeren samen – verwijder 1 wegwijzer uit bovenliggende knoop • indien onderloop in interne knoop: – herverdeel of voeg samen (met evt. verwijdering van 1 waarde uit bovenliggende knoop. . . ) 72

Oefening: verwijderen van. . . 5, 12, 9 73

74

75

B*-bomen • elke knoop tenminste 2/3 gevuld (i. p. v. ½) – splits slechts wanneer 2 naburige knopen vol zijn 76

Agenda Indexen: definitie Soorten indexen Indexen met meerdere niveaus Boomstructuren als indexen Indexen op meerdere velden 82

Indexen op meerdere velden • vb. index op combinatie van leeftijd en departement – "geef alle werknemers van dept. 5 met leeftijd 60" – met aparte indexen: eerst verz. werknemers van dept. 5 (A), dan verz. met leeftijd 60 (B), vervolgens doorsnede nemen niet zo efficiënt • resultaat waarschijnlijk veel kleinere verzameling dan A en B – Samengestelde index veel efficiënter: • levert direct het goede resultaat • Hoe indexeren op meerdere velden? 83

Hoe de velden combineren? (1) • Meerdere velden samen als 1 veld beschouwen – Samengestelde waarden: ordening? • lexicografisch : cf. alfabetische ordening • (p 1, q 1) < (p 2, q 2) p 1 < p 2 OF (p 1 = p 2 en q 1< q 2) • enz. voor meer componenten • Hashing op samengestelde velden: "partitioned hashing" – resultaat hashfunctie is combinatie van resultaten van aparte hashfuncties op componenten – bv. • 5 101; • 60 10110 (5, 60) 10110110 – geen aparte toegangsstructuren voor componenten nodig (maar: enkel voor "="-tests) 84

Hoe de velden combineren? (2) • i. p. v. rij met adressen : matrix met adressen – indices voor matrix = waarden van componenten • evt. code voor interval van waarden – vb. matrix M: M 5, 5 bevat wijzer naar cel met wijzers naar records waarvoor dept = 5 en leeftijd > 50 85

Fysische en logische indexen • Naast bomen ook hashing of andere datastructuren mogelijk voor index • In onze bespreking: fysische indexen – steeds fysische adressen gebruikt – indien deze veel wijzigen: probleem – logische index verhelpt dit: • i. p. v. fysisch adres, wordt sleutel voor primaire bestandsorganisatie teruggegeven • dan zoeken volgens primaire structuur – nadeel van logische index: 1 extra indirectie 86

Indexen: samenvatting • Grootste voordeel van indexen: – kunnen in principe gebruikt worden met eender welke primaire bestandsorganisatie – bemerk dat primaire organisatie enkel efficiënt zoeken op 1 veld mogelijk maakt • Indexen o. a. handig voor opleggen van uniciteit van velden (naast efficiënt opzoeken van waarden) • Bestand met secundaire index op elk veld = "volledig geïnverteerd bestand" (fully inverted file) 87

Vooruitblik Indexen: definitie Soorten indexen Indexen met meerdere niveaus Boomstructuren als indexen Indexen op meerdere velden Meerdimensionale structuren 88

Bronnen • Deze slides zijn gebaseerd op Henk Olivié‘s slides voor Gegevensbanken 2009 en op Elmasri & Navathe, Fundamentals of Database Systems, Addison Wesley / Pearson, 5 e editie 2007. • Alle kopieën zonder bronspecificatie: Elmasri & Navathe, Fundamentals of Database Systems, Addison Wesley / Pearson, 5 e editie 2007. • Verdere figuren: bronnen zie “Powerpoint comments field” • Bedankt iedereen! 89