Tags:    databaser mysql

Introduktion

[url=http://www.mysql.com/]MySQL[/url] er blot én ud mange database servere på markedet. MySQL kan fås med en [url=http://en.wikipedia.org/wiki/Gpl]GPL[/url] licens hvorfor det er en populær database server blandt open source folk (selvom produktet er udviklet af et Svensk "for-profit" firma som også tilbyder en kommerciel licens).

MySQL har i mange versioner været kendtegnet ved at været en meget hurtig database server, samtidig med at produktet dog har manglet mange af de funktioner som de komercielle konkurrenter såsom [url=http://www.oracle.com/technology/database/index.html]Oracle[/url] og [url=http://www.microsoft.com/sql/default.mspx]Microsoft SQL Server[/url] har kunnet prale af på deres annonceblade, især hvad angår data warehousing.

Men fra [url=http://dev.mysql.com/doc/refman/5.0/en/index.html]version 5[/url] af, hvor så vigtige ting som cursors, views, triggers og stored procedures blev tilføjet, har mange udenfor OSS verdenen fået øjnene op for MySQL, og dets popularitet er nærmest eksploderet de senere år.

MySQL er typisk brugt i LAMP/MAMP/WAMP setups, men kan med fordel også anvendes i et Java setup og endda med .NET (som forfattern gør i sit arbejde).

Denne artikel vil berøre nogle af de ting som er nødvendig at have kendskab til for at lave et effektivt database design. Sandheden er at på mange små og mellemstore projekter vil en eller flere programmørere også fungere som de facto database administrator, hvorfor viden om effektivt design er vigitgt.

Artiklen forudsætter at læseren kender til SQL samt ved hvordan man får tabellerne i tredje normal form. Denne artikel vil så se på hvilke muligheder MySQL tilbyder for at få et design der passer optimalt til det pågældende projekt ud fra et effektivitets synspunkt. Artiklen indeholder ikke megen SQL kode, men er en gennemgang af hvordan man opnår et effektivt database design.

Tabeller og views

Når man skal vælge sin tabeltype (eng. storage engine) i MySQL kan man godt blive lidt overvældet af de mange muligheder, men der vil i de fleste tilfælde reelt kun være et valg mellem 2-3 forskellige tabeltyper. De resterende typer er entent helt uinteresserante eller kun egnet til meget specielle situationer. Vi vil nu kort kigge på de forskellige tabeltyper og lægge vægt på i hvilket situationer de er bedst egnede.

Når man har fundet den ønskede tabel type, angiver man den ved CREATE TABLE ligesom her hvor tabeltypen MEMORY er valgt:

CREATE TABLE department_messages
  (
   ... (kolonne specifikationerne er her) ...
  )
  ENGINE = MEMORY;

MyISAM
MyISAM er den mest populære tabeltype og med god grund. Den burde altid være dit første valg, men det er absolut essentielt at være bevidst om dens ulemper for i en del tilfælde vil den være aldeles uacceptabel.

MyISAM er hurtig. Rigtig hurtig. Hvis du har brug for fart, og kan gå på kompromis med data integritet så vælg MyISAM. Tabellen understøtter ikke transaktioner eller relations krav (eng. constraints) såsom fremmed nøgle integritet. Derudover låser MyISAM hele tabellen under en UPDATE.

Udfra ovenstående kan vi konkludere at hvis du laver rigtig, rigtig mange UPDATEs og INSERTs og processerer vigtig transaktionsdata så vælg ikke MyISAM, men hvis du vil ha' en hurtig og fleksibel tabel er MyISAM et glimrende valg.

Nu lyder det måske som om man ikke kan bruge MyISAM medmindre man gemmer nærmest ligegyldig information. Dette er absolut ikke tilfældet, men jeg vil ikke bruge MyISAM til f.eks. at gemme finansdata og lignende vigtig information. Derimod er MyISAM perfekt til stort set alle andre formål. Og den er hurtig; rigtig hurtig hvorfor den er velegnet til databasebaseret websites.

InnoDB
I de tilfælde hvor MyISAM ikke slår til vil InnoDB typisk være løsningen. InnoDB understøtter transaktioner og relations krav såsom fremmed nøgle integritet. Så i de situationer hvor data integritet er af yderste vigitghed er InnoDB løsningen. Derudover låser den ikke hele tabellen ved UPDATEs men kun den enkelte række.

Tilgengæld er den meget langsomere en MyISAM. Her skal det dog understreges at InnoDB hastighedsmæssigt er sammenlignelig med tabeltyperne i mange af de kommercielle database servere; med andre ord er det mere tilfældet at MyISAM er ekstrem hurtigt og ikke at InnoDB er langsom (den er bare "som de andre" og tit hurtigere end "de andre") En del er faktisk gået helt væk fra brugen af MyISAM og bruge InnoDB i stedet for da den er hurtig nok og er bedre til at sikre data integritet end MyISAM.

Merge
Hvis du har brug for at håndtere store data mængder kan Merge tabeller være løsningen. Merge er fakisk ikke en tabel som sådan, men blot en samling af ens MyISAM tabeller der så kan bruges som var de én stor tabel.

Det er vigtigt her at understrege at MyISAM tabellerne der udgør Merge specifikationen skal være 100% identisk. Hvis blot nogle indeks er specificeret i forskellig rækkefølge i CREATE TABLE virke Merge ikke efter hensigten.

Når du har oprettet en Merge tabel kan du både lave SELECT, UPDATE, INSERT og DELETE på selve merge tabellen såvel som de enkelte MyISAM tabeller. Et sådanne setup kan give nogle markante effektivitets fordele.

Du kan f.eks. opnå meget effektive søgninger. Hvis du ved præcist hvad du søger efter kan du søge i en af de enkelte MyISAM tabeller, og ellers søge i Merge tabellen.

Et andet eksempel på god brug af en Merge tabel er ved log information. Du kan gemme information fra forskellige måneder i hver sin tabel, komprimere dem med myisampack og så håndtere dem som én tabel med Merge.

Hvis du har en stor tabel du kun læser fra, er Merge en god måde hvormed du kan få fart på din database. I et sådanne tilfælde kan du opdele tabellen i flere tabeller og lægger hver tabel på forskellige diske.

Merge tabller er dog ikke kun fryd og gammen. Der er visse begrænsninger og ulemper forbundet med dem.

Den største hæmsko er nok at du kun kan man bruge MyISAM tabeller i en Merge tabel. Derudover kan du heller ikke anvende FULLTEXT indeks på en Merge tabel.

Vi kan altså konkludere at Merge tabeller er fremragende hvis man skal behandle rigtig store datamængder og derfor har brug for lidt ekstra "performance".

Andre tabeltyper
De tre ovennævnte tabeltyper - MyISAM, InnodB, og Merge - er dem du vil komme til at bruge i de fleste tilfælde. De andre tabeltyper bruges kun i meget sjældne tilfælde, og nogle af dem kommer du nok aldrig til at bruge.

Las os kigge lidt på dem.

I tilfælde hvor du har brug for en ekstrem hurtig tabel hvis data er af en midlertidig natur er tabeltypen Memory perfekt. Der er her tale om en tabel der ikke holdes på en disk men i computerens hukommelse så længe MySQL serveren kører. På plussiden giver det som sagt en ekstrem hurtig tabel. Ulempen ved denne tabel er ganske indlysende; hvis database serveren eller server maskinen går ned mister man al data. Derfor skal man kun bruge sådan en tabel til at holde information som man ikke har brug for at gemme i særlig lang tid. Endvidere, for at sikre at tabellen ikke bliver alt for stor skal man sørge for jævnligt at "tømme" den.

ISAM tabellen er forgængeren til MyISAM, og er en forkortelse for Indexed Sequential Access Method. For at være helt korrekt er ISAM en metode for datalagring i databaser som blev udviklet af IBM. MySQL implementerede så i tidernes morgen denne metode og kaldte derfor logisk nok tabellen for ISAM. Denne tabeltype var altså den der oprindeligt kom med MySQL. Den blev så senere udvidet og blev derfor til MyISAM. I MySQL er ISAM tabeltypen derfor nu anset for at være en "legacy" teknologi, hvorfor du aldrig skal bruge den i nye projekter.

Tabeltypen CSV er yderst velegnet til dataeksport. Den gemmer data i et komma opdelt data format (CSV er en forkortelse for comma separated values). Tabellen understøtter kun SELECT og INSERT så den skal udelukkende bruges til eksportformål.

Hvis du lider af pladsmangel, eller har virkeligt store datamængder der skal arkiveres kommer tabeltypen Archive til din undsætning. Den gemmer data i et komprimeret format, understøtter kun SELECT og INSERT og du kan ikke søge via et indeks. Brug derfor kun denne tabeltype hvis du har enorme mængder af data som du dog har brug for at kunne søge i (bemærk dog at da indekssøgning ikke understøttes vil søgningen være langsom). Med nutidens priser på diske kan jeg ikke forestille mig at skulle komme til at bruge Archive medmindre jeg skulle lave en folketælling i Kina og det ser jeg ikke ske for mig.

BerkeleyDB (BDB) tabeller tilbyder ganske avanceret transaktions muligheder. Og på det punkt er de at sammenligne med InnoDB (BDB var faktisk en del af MySQL produktet før InnoDB). Men så hører ligheden også op. BDB er faktisk en lidt sær tabeltype, og er ikke ligeså populær som MyISAM og InnoDB. BDB er for tiden ejet af Oracle Corporation. Fra og med MySQL version 5.1 er BDB ikke længere understøttet i MySQL hvorfor man som MySQL bruger ikke burde bruge BDB i nye projekter.

Hvis du har brug for clustering af din MySQL database så skal du bruge tabeltypen NDB. Tabeltypen Federated bruges hvor du har et "distributed computing" opsætning. Hvis en tabel angives som værende Federated betyder det at den rent faktisk eksisterer på en anden database server. Clustering, distributed database computing og herunder NDB og Federated er uden for denne artikels spændevidde da de er store emner i sig selv.

Views
Views kan bruges til mange ting. Oftest bruges views til at skabe flere forskellige præsentationer af en tabels data for at forenkle brugernes forespørgelser. Views bruges også ofte til at segmentere forskellige brugergruppers adgang til tabeldata ud fra et sikkerhedshensyn. Men views kan også bruges til at opnå god effektivitet i din database, især hvis brugerne har adgang til databasen gennem kanaler der er udenfor din kontrol - det kan f.eks. være andre utrænede udviklere hvis SQL formået endnu ikke er perfekt, eller via tredje parts business software.

Hvis for eksempel du ved at nogle brugere af en tabel kun har brug for en lille del af tabellens indhold så kan du nøjes med at give dem adgang til et view af det ønskede data og så lade dem operere på den. På dem måde kan du undgå resurse krævende forespørgelser. Sådanne effektivitets skabende views kan indeholde enten en delmænge af kolonnerne eller rækkerne, eller bege dele, alt efter situationen. Dette er en god og sikker måde at undgå en masse unødvendige SELECT * forespørgelser på din hovedtabel.

En anden effektiv brug af views er at slippe for en masse resursekrævende SELECT med JOINs fra utrænede udviklere eller business software. Lav i stedet et optimeret view med den ønskede struktur og SELECT fra den i stedet. MySQL kan endda cache et view såfremt det bruges flere gange efter hindanden.

Pakning af indeks
Hvis du ønsker at spare på diskpladsen og/eller ønsker høj læsehastighed på bekostning af skrivehastigheden så kan du i tilfælde med MyISAM tabeller pakke indeksne. Dine indekses pladskrav vil være meget mindre end før.

Tabelstruktur

Når du har bestemt dig for hvilke tabeltyper du ønsker at bruge for de forskellige tabeller i din database, er næste trin at overveje den enkelte tabelsstruktur. Også her er der nogle ting du skal være vidende omkring hvis du ønsker en effektiv database.

Kolonnetype
Valget af datatyper for dine kolonner kan have en stor betydning for basens effektivitet. Igen tilbyder MySQL mange forskellige typer, som kan virke lidt overvældende især hvad angår tekst og tal.

Hvad tekststrenge angår er det næsten altid bedst at vælge CHAR i modsætning til VARCHAR. CHAR har en bedre "performance" hvor VARCHAR sparer diskplads; med dagens diskpriser er valget af CHARs bedre performance næsten altid at foretrække. Bemærk dog at valget af rækkeformat (se nedenfor) også spiller ind her. Der er også en evig diskussion omkring at gemme TEXT (og BLOB) i selve databasen mod blot gemme en sti i databasen som peger på resursens lokation i filsystemet. I mange tilfælde er valget foretage på forhånd. Hvis for eksempel du ønsker at kunne søge i dataene er du tvunget til at gemme det i databasen. Hvis indholdet af TEXT/BLOB kolonnen derimod er sekundær til tabellens funktion kan du spare din database den øgede processeringspris som mange af sådanne kolonner koster ved at gemme indholet på filsystemet istedet.

Valget af kolonnetype for taldata burde ikke udgøre en vanskelighed. Den generelle regel er at du skal vælge den mindste datatyper der kan holde din kolonnes data. Lad være med at vælge INT for alt hvis TINYINT, SMALLINT eller MEDIUMINT kan gøre det! Hvis du har mange rækker i din tabel sparer du ikke blot meget plads, men mange SELECTs og JOINs er også mere effektiv såfremt du bruger en mindre datatype. For kolonner der holder finansieldata (dvs. penge, løn osv.) brug DECIMAL. DECIMAL datatypen gemmer talværdien som en streng så der sker intet præcisionstab ved lagring - ved beregning med disse numeriske værdier bruges dobbelt-præcisions operationer som var de DOUBLE værdier (såfremt MySQL udfører beregningerne; hvis du udfører beregningerne i et programmeringssprog er disse selvfølgelig underlagt det pågældende sprogs begrænsninger, som i visse tilfælde endda kan give mere præcise resultater).

Jeg vil også lige nævne analyse() funktionen. Denne funktion analyserer din tabels data og kan fortælle dig hvilke datatyper der er mest optimal for hver kolonne baseret på tabellens nuværende indhold. Så efter databasen har været oppe at køre i noget tid og indeholder rigtig og reelt data så prøv at køre en analyse() på tabellen - der er gode chancer for at du vil kunne brug resultatet til at ændre lidt på kolonnetyperne så basen bliver endnu mere effektiv.

MyISAM Tabelformatter
Når du opretter en MyISAM tabel kan du angive dens format som enten FIXED eller DYNAMIC.

I en FIXED tabel har rækkerne en konstant størrelse hvilket gør MySQL serverens processering af tabbelen meget mere effektivt end hvis rækker har varierende størrelser. Hvis en tabel erklæres som værende FIXED vil alle VARCHARs automatisk blive dannet som CHARs. Der er så store effektivitets fordele ved FIXED formatet at MySQL grundlæggende vil oprette tabellerne i dette format såfremt der ikke er nogle TEXT eller BLOB kolonner. FIXED formatet er altså klart at foretrække. Du kan ikke bruge FIXED formatet hvis dine tabel indeholder TEXT eller BLOB kolonner (du kan godt angive det ved CREATE TABLE men det vil blive ignoreret).

Hvis du opretter tabeller med TEXT eller BLOB kolonner vil MySQL automatisk bruge formatet DYNAMIC. Hvis du har en tabel uden TEXT eller BLOB kolonner, og ønsker at bruge DYNAMIC formatet skal du angive det explicit ved CREATE TABLE. Som navnet antyder er en DYNAMIC tabel en tabel hvis rækker varierer i størrelse; dette giver som sagt en dårligere effektivitet da sådanne rækker er besværlige at håndtere for database serveren.

DYNAMIC tabeller er også mindre sikre en FIXED tabeller. Hvis computeren går ned under skrivning til tabellen kan MySQL serveren efterfølgende (via myisamchk) nemt finde ud af hvor hver tække starter og begynder. Dette er ikke tilfældet med DYNAMIC tabeller.

Hvis du har store DYNAMIC tabeller kan MySQL konstruere indeks mere effektivt hvis du gør brug af variablerne AVG_ROW_LENGTH og MAX_ROWS ved CREATE TABLE. Med de to variabler kan du fortælle MySQL hvor stor du forventer tabellerne at blive - den information gør som sagt indeksarbejdet en del letter for MySQL.

Relationskrav

Brug af relationskrav (eng. constraints) kan også være en god effektivitets øger: logikken finder sted på database serveren og behøver altså ikke at være en del af hver klients kodebase - dette gør også udvikleren mere produktivt.

PRIMARY KEY/UNIQUE
Disse to relationskrav identificerer hver række helt unikt. Forskellen mellem de to er kun at en PRIMARY KEY kan omfatte flere kolonner hvor UNIQUE kravet kun kan omfatte en enkelt kolonne. Dette relationskrav er selvsagt enormt vigtig da det sikrer databasens integritet.

FOREIGN KEY
Dette relationskrav understøttes kun i InnoDB tabeltypen; for at være helt korrekt så kan du selvfølgelig godt have fremmede nøgler i en MyISAM tabel, men brugen af dem som et relationskrav, understøttes kun i InnoDB tabeller - for eksempel vil en InnoDB tabel checke at den opgivne fremmede nøgle rent faktisk peger på en række i den fremmede tabel. Det bruges til at angive relationer tabellerne imellem. Da det antages at du kender til SQL kender du også til brugen af fremmede nøgler til at sikre data integritet. Korrekt bruge af fremmede nøgler er ganske vigtig i et godt databasedesign.

NOT NULL
NOT NULL kravet betyder at en kolonne ikke kan indeholde NULL men skal indeholde data. NOT NULL sikrer altså at visse kolonner ikke kan være tomme. NOT NULL bruges ofte sammen med DEFAULT.

DEFAULT
Dette krav forsyner en værdi for hver kolonne med data såfremt databaseserven ikke bliver forsynet med data for kolonnen. Hvis din tabel har kolonner der tit har den samme værdi, kan du bruge DEFAULT relationskravet. Resultatet er at du ikke behøver at indtaste en værdi på klienten og der skal sendes det mindre fra klienten til databaseserveren.

ENUM
Ved at bruge ENUM i en tabelkolonne kan du håndhæve at kolonnen kun indeholder data fra værdier specificeret i din ENUM. Det er ideelt til situationer hvor kolonnen kun må indeholde én mulighed ud af mange muligheder.

Et eksempel kan være en kolonne der indeholder hårfarve. Du kunne selvfølgelig bare bruge en VARCHAR (eller CHAR) og så lade folk selv udfylde hårfarven. Det åbner dog porten for en masse irriterende situationer hvor brugeren måske staver forkert og skriver 'srot' istedet for 'sort', et andet problem er at brugerne vil indtaste alle mulige forskellige hårfarver så dine kolonne ville kundeholder 117 forskellige hårfarver. Disse problemer kan være uoverkommelige især såfremt du ønsker at lave søgninger/analyse at kolonnens indehold - og det er jo tit formålet med en database.

I stedet bruger vi en ENUM:

ENUM('Black','Brown','Blond','Red','GrayWhite')

Ved brug af ovenstående kan vores kolonne kun indeholder én mulig hårfarve ud af de fem mulige.

SET
SET er beslægtede med ENUM, men en kolonne der har datatypen SET kan indeholde én eller flere af mulighederne.

Igen handler det om at begrænse hvad kolonnen kan indeholde til nogle få godkendte muligheder, men i SETs tilfælde gerne flere muligheder og ikke kun én som med ENUM.

Hvis for eksempel jeg har en profil på en dating site kan det være sitet giver mig mulighed for at specificere hvilke religiøse overbevisninger jeg kunne tænke mig min mulighede emner kunne have. Med mindre jeg er meget intolerant vil jeg nok kunne acceptere mere end én mulighed hvorfor denne information passende kunne gemmes i en kolonne med datatypen SET, som for eksempel:

SET('Buddhism','Christian','Hinduism','Islam','Irreligious','Judaism','Pagan','Spiritism')

Hvad synes du om denne artikel? Giv din mening til kende ved at stemme via pilene til venstre og/eller lægge en kommentar herunder.

Del også gerne artiklen med dine Facebook venner:  

Karma barometer (30 dage)

IT jobs | Kontakt | Om Udvikleren.dk | Om karma | IT nyheds arkiv | Annoncering

X