Databaser —Design och programmering
Fysisk design av databasen
- att ta hänsyn till implementationsaspekter:
minnesteknik
filstrukturer
indexering
Databas
• En databas är en samling data som:
–Har med varandra att göra
–Representerar (modellerar) en del av verkligheten för ett specifikt ändamål –Är beständig (finns kvar)
–Har en strikt struktur som beskrivs i ett schema
–Inte innehåller motsägelser –Lagras på dator
2
Programdesign, databasdesign
3
Implementation
Databasdesign Applikationsdesign
Konceptuell design
Logisk design
Fysisk design Konceptuell datamodell
Implementationsmodell
Fysisk datamodell
Transaktionsdesign Kravspecifikation
Lagra data - minnesteknik
• Primärminne/Arbetsminne (kretsteknik) – Flyktigt
– Snabbt (direktaccess, nanosekunder) – Dyrt
• Sekundärminne (Olika tekniker: hårddisk, diskett, magnetband, CD, DVD, SSD mm)
– Permanent
– Långsamt (olika typer av access, milli-mikro sekunder)
– Billigt
4
Utvecklingen
0,01 0,1 1 10 100 1000
2008 2009 2010 2011 2012 2017 2018 2019
Primärminne SSD
Hårddisk DVD
Pris per GB (kr/GB)
5
• magnetiserbar beläggning på ytan
• packningstäthet
• läs/skrivhuvud
• spår, söktid
• rotations- hastighet
• Överföring till PM
• Block
Hårddisk (skivbaserad)
6
Hårddisk, forts
• Block – minsta överföringsmängd till PM
• 1/2 till 4kB data
• Accesstid = sök+rotation+läs
• Accesstid = 5-10 millisekunder (5-10*10-3).
• Att läsa flera block i samma spår går lite snabbare
7
SSD
• Kretsteknik, transistorer lagrar 1/0 (NAND)
• Organiserade i matriser så att information läses ut radvis eller ett antal rader åt
gången, kallas block.
• Block (ofta 4kB) mängd som överförs till PM
• Läsning snabb, skrivning ofta lite
långsammare, varierar: 200-2500 MB/s
• Accesstid: 2-20 mikrosekunder/block (2-20*10-6)
8
Block
Databasen lagras i filer
• Lagras som en samling poster i filer
• en tupel lagras som en post
• ett attributvärde lagras som ett fält i en post
• Datatyp (tecken, heltal osv) - utrymme för respektive attribut
• Viktiga operationer på poster/
rader: sätta in ny, ta bort, söka specifik
9
Block
Filerna lagras på hårddisk
Filerna lagras på hårddisk
• Posterna i en fil lagras i ett antal fysiska block.
• Block i samma fil läggs efter
varandra i samma spår så långt det går - snabbar upp
• Information om vilka block som ingår i en fil lagras, kallas
filhuvud.
• Efter raderingar och insättningar:
fragmentering - längre tid
11
… eller ssd
• Posterna i en fil lagras i ett antal fysiska block.
• Information om vilka block som ingår i en fil lagras
• SSD slits - gammal tar längre tid pga försöker igen
12
www.liu.se
Slut Del 1
www.liu.se
Databaser - Fysiska databasen Del 2:
Filstrukturer
Databasen på hårddisk eller ssd
• Oavsett teknik har vi:
– Accesstid - den tid det tar att hämta data till primärminnet
– Block - den mängd som hämtas varje gång
• Hårddisk
– Accesstid 5-10ms, blockstorlek 0,5-4kB
• SSD
– Accesstid 2-20µs, blockstorlek 4kB
• Stora filer tar tid att hantera! Hur lång?
15
Operationer i databasen
• INSERT INTO Person VALUES
(”190503-1234”, ”Stina”, ”Pettersson”)
• SELECT * FROM Person
WHERE pnr = ”190503-1234”;
• DELETE FROM Person
WHERE pnr = ”190503-1234”;
16
Blockningsfaktor, bfr
17
• R = postens storlek, B = blockstorleken bfr = blockningsfaktor = ⎣B/R⎦
• en fil med r stycken poster kräver b = ⎡r/bfr⎤ stycken block
• Vilka block som ingår lagras i filhuvudet (ibland i respektive block)
Filorgansation
1. Hög (heap) (osorterat)
2. Ordnad sekvens (sorterat) 3. Hashtabell
4. Indexerad sekvens
18
Hög (heap)
Oordnad sekvens av poster (antal block=b)
• Ny post - Adressen till sista blocket finns i
filhuvudet. Läs in den, lägg till nya posten, skriv ut.
Antal accesser: 2.
• Sökning - sekvensiell genomgång av filen tills man hittar den sökta posten.
Antal acesser: Medel=b/2, Max=b.
• Borttagning - sök rätt block, som läses in. Ta bort
posten. Skriv tillbaks det reviderade blocket (som nu är delvis tomt).
Antal accesser: Medel=b/2 +1
19
Sökning i hög, räkne-exempel
Fil: Poststorlek, R = 100 byte filstorlek, r = 30 000 st poster
Hårddisk: blockstorlek, B = 512 byte tid för blocköverföring: 10 ms
Blockningsfaktorn blir: bfr=⎣B/R⎦=
=⎣512/100⎦= 5 poster per block
Filen tar upp b=⎡r/bfr⎤=⎡30000/5⎤=6000 st block
● För att hitta en viss post krävs i genomsnitt b/2= 3000 blocköverföringar = 30 sekunder.
20
Hög: fördelar och nackdelar
+ Insättning går mycket fort.
- Många borttagningar leder till tomrum på skivan.
(Kräver periodisk omorganisering av filen.) - Sökning långsam
- Om posterna har variabel längd kan ändring i en
sådan post få blocket att svämma över - posten måste då tas bort och sättas in på nytt.
21
Ordnad sekvens (sorterat)
Posterna i filen sorteras enligt värdet på något fält i posterna.
• Ny post - hitta rätt ställe (rätt block), skapa plats åt
den nya posten, sätt in den. Skapa plats = flytta ”resten av posterna” ett steg framåt (=skriv om resten av filen?
Eller länka in nytt block).
• Sökning - binärsökning, om listan av block sparas i filhuvudet.
• Borttagning - hitta rätt post, flytta resten bakåt?. Eller markera posten som borttagen.
22
Binärsökning
• Gissa mitt i
intervallet varje gång
• max antal gissningar:
log2(antal)
• Sökning i fil:
gissning = access
• dvs log2(antal block)
23
0 100
ok: 34
Tänk på
ett tal mellan 0 och 100
50
25 37 3431
Sökning i ordnad sekvens, exempel
Samma fil som tidigare:
blockfaktor, bfr = 5 poster per block antal block, b = 6000 block.
tid för blocköverföring = 10 ms
Binärsökning ger antal blocköverföringar:
n=⎡log2(b)⎤ = ⎡log2(6000)⎤=13
● För att hitta en viss post krävs
13 blocköverföringar = 0.13 sekunder.
24
Ordnad sekvens, fördelar och nackdelar
- Insättning av ny post tar tid om plats skapas genom att förskjuta övriga poster i filen
- Borttagning eventuellt likaså.
+ snabbare sökning än hög
25
Hashtabell
Bara användbar för filer med nyckel.
Posterna sprids över en ”hylla med fack” med hjälp av en hashfunktion.
Varje hylla=ett block på disken
26
0 post post post 1 post post
…
9 post
Hashtabell
• Logiskt
• Fysiskt
27
Filhuvud 0
1 ..
9
Hashvärde = f(nyckel) Block
Hashvärde = f(nyckel)
Block
Hashtabell, forts
Varje hylla=ett block på disken
Kollisionshantering vid fullt block: spill-block
28
0 post post post
1 post post
…
9 post
Hashtabell, forts
• Ny post - beräkna hashvärde, om plats finns i blocket, sätt in. Om inte länka in nytt block.
Accesser = 1-2
• Sökning - beräkna hashvärde, läs. Om inte rätt block följ länken till nästa tills funnen.
Accesser = 1-?
• Borttagning – hitta som ovan, ta bort posten ur blocket och skriv tillbaks det ändrade blocket.
Accesser = sökning + 1
29
Hashstruktur, fördelar och nackdelar
+ snabb sökning (fåtal blockaccesser) - avancerad algoritm för insättning och borttagning
- tar viss extra plats (länkfältet samt “luft”)
- kräver hela nyckeln vid utsökning, kan ej läsas sekvensiellt
30
www.liu.se
Slut Del 2
www.liu.se
Databaser - Fysiska databasen Del 3:
Index, Summering
Indexerad sekvens
Jfr innehållsförteckningen till en bok - lista med pekare till rätt plats
Indexpost pekar ut var i huvudfilen posten finns
• Primärindex, sekundärindex, multipelindex, klustrat index
• Glest index vs tätt index Indexfilen sorteras alltid.
Indexfilen tar plats!
33
Indexerad sekvens, forts
• Ny post - rätt block söks fram, läses in, ändras och skrivs tillbaks. Vid översvämning i blocket skapas ett nytt block och en ny indexpost läggs in i indexfilen.
Accesser: sökning + 1-2
• Sökning – sök nyckeln i indexfilen (som vid sökning i sorterad fil). Hämta aktuellt block från huvudfilen).
Accesser: binärsökning i indexfilen + 1
• Borttagning - som ny post. Oftast lämnas tomrum i block istället för att organisera om varje gång.
Accesser sökning + 1
34
Primärindex
• Huvudfilen sorterad
• i ett antal block på disken.
• En indexpost per block i huvudfilen
• består av sorterings- värdet och en adress till blocket.
• Glest index
35
Huvudfil 0
1 3 4 5 6
.. ..
.. ..
.. ..
index 0
4 ..
Sökning med primärindex, exempel
• Samma huvudfil som tidigare:
antal block, b = 6000 block.
tid för blocköverföring = 10 ms
• Antag att nyckelfältet är 9 bytes långt och adressen till block tar 6 bytes.
• Indexfilen är en sorterad fil som söks som sådan:
– Beräkna hur många block indexfilen tar upp – genom att beräkna indexfilens poststorlek,
blockningsfaktor och antal poster (en per block i huvudfilen)
36
Sökning med primärindex, forts.
Indexfilens poststorlek iR blir 15 bytes
Indexfilens blockfaktor ibfr =⎣512/15⎦= 34
Indexfilen har en post per block i huvudfilen, dvs ir=6000 poster
Indexfilen tar alltså upp ib = ⎡6000/34⎤=177 block Hitta en viss indexpost (binärsökning i indexfilen):
⎡log2(177)⎤ blocköverföringar =8 st.
Sedan ytterligare en för att läsa ur huvudfilen.
● Totalt 9 blocköverföringar = 0.09 sekunder.
37
Sekundärindex
index på unikt fält som
filen inte är sorterad efter!
Är ett tätt index (en indexpost per post i huvudfilen).
Sekundärindexet får lika många poster som
datafilen har men är ändå mindre.
38
Huvudfil 0 E
1 B 3 K 4 A 5 D 6 T
. .
..
. .
..
. .
..
index A
B C D E ..
..
Sökning på sekundärindex, exempel
• samma fil som tidigare och indexposter som tidigare, men med tätt index:
Indexfilens poststorlek iR = 15 bytes
Indexfilens blockfaktor ibfr =⎣512/15⎦= 34
• Indexfilen tar ib =⎡30000/34⎤= 883 block
• Hitta en indexpost, binärsökning i indexfilen:
⎡log2(883)⎤ blocköverföringar = 10 st sedan en blocköverföring ur huvudfilen.
• Totalt 11 blockaccesser, 0.11 sekunder.
39
Klustrat index
Indexering på icke-unikt fält – går det?
Sortera filen på
indexfältet och gör index för första posten av varje indexvärde.
40
Huvudfil 0
0 1 1 4 4
.. ..
.. ..
.. ..
index 0
1 4 5 ..
..
Multipla index (indexnivåer)
Index till indexfilen.
• För varje block i indexfilen en post i index-indexfilen...
Kan ha godtyckligt många nivåer...
41
Huvudfil
0 1 3 4 5 6
.. ..
.. ..
.. ..
inde 0 4 ..
41
index 0
..
Sökning med multipelindex på primärindex
Samma huvudfil och samma indexfil:
Indexfilens blockfaktor ibfr = 34 Indexfilen tar upp ib = 177 block
Index på indexfilen behöver i2r= 177 poster.
Den tar 6 block.
Hitta index till indexfilen (⎡log2(6)⎤=3).
Hitta index till huvudfilen (1 överföring).
Läsa in det eftersökta blocket ur huvudfilen.
● Totalt 5 blockaccesser, 0.05 sekunder.
42
Indexstruktur, fördelar och nackdelar
- indexfilen tar extra plats i databasen.
- insättning och borttagning kräver även uppdatering av indexfilen.
+ snabb sökning
+ relativt enkla algoritmer för insättning och borttagning
43
Sammanfattning
Typ Ny post Sökning Borttagning Extra platsHög snabb
2 långsam
b/2 långsam
b/2 +1 övergivna poster*
Ordnad långsam log2(b)+1
snabb
log2(b) långsam
log2(b)+1 tomma poster*
Hashstrukt snabb
2-3 snabb
1-2** snabb
2-3 länkvärde + reserv
Indexerad snabb
(og2(ib)+2 snabb
log2(ib)+1 snabb
log2(ib)+2 indexfilen
Värdet anger antal accesser, b=antal block i filen, ib=antal block i indexfilen.
*=Om ingen omorganisation sker. **= sökning på nyckelvärde, annan sökning som för Hög.
44
Filorganisationer
• Hög
• Ordnad sekvens
• Hashstruktur
• Indexstruktur – primärindex – sekundärindex – klusterindex – multipla nivåer
45
Fysisk design
• designa databasen på fysisk nivå:
• avvägning mellan utrymme och snabbhet.
• Ta med i beräkningen hur ofta tabellen används och hur (sökning/matchning mot vilket fält).
• Åtgärder:
– Ordna datafilen - osorterad hög eller sorterad? På vad? Används alltid nyckel? Hashtabell!
– Skapa index – vilka behövs? - de som söks på!
46