Virkningen av å velge riktige datatyper i SQL database
Databaser er hjertet i mange applikasjoner og systemer, og deres ytelse og effektivitet er avgjørende for suksess. En av de mest effektive metodene for å optimalisere en database er å velge de riktige datatypene for kolonnene i tabellene dine. Ved å velge optimale datatyper kan du redusere lagringskravene, forbedre spørringsytelsen og opprettholde dataintegriteten.
Forståelse av forskjellige datatyper
Når man skal velge riktig datatyper er det viktig å kjenne til karakteristikkene til de forskjellige datatypene. Forskjellige typer bruker ulike antall bytes per karakter, de støtter forskjellige tegn og forskjellige verdier. Ved å velge riktig datatype for kolonnene dine har du et godt utgangspunkt for en robust og effektiv database.
Numeriske datatyper
Heltall-typer
Desimal-typer
* Hvis 1<=n<=24 tar float opp 4 bytes, hvis 25<=n<=53 tar n 8 bytes. n sin normalverdi er 53 om den ikke spesifiseres. For n < 24 har man 7 desimalers presisjon, om 24 < n <= 53 har man 15 desimalers presisjon.
Dato-typer
* DateTimeOffset er den eneste datotypen som tar hensyn til tidssone. ** Om man velger presisjon på under 3 desimaler brukes 6 bytes, 3-4 desimaler tar 7 bytes og alt over 4 desimaler bruker 8 bytes.
Tekst-typer
* Om man har UTF-8 collation vil char og varchar bruke alt fra 1–4 bytes per karakter.
Hvilke datatyper skal jeg velge?
Når man skal velge datatyper er det viktig å se på hva man skal lagre og hvilke datatyper som støtter de kriteriene vi har for dataen. For å spare lagringsplass og ha best mulig ytelse på databasespørringene burde man alltid gå for den datatypen som bruker minst plass, samtidig som den støtter all dataen vi vil lagre i kolonnen.
Skal man f.eks. lagre alderen til en person burde man gå for TinyInt da denne støtter alle kjente aldre på personer (0 til 255 år) og bruker minst data. Om vi sammenligner med SmallInt vil man bruke halvparten av lagringsplassen og det går ikke på bekostning av gyldighetsområdet vi trenger da det er ytterst få som lever til å bli eldre enn 255.
Skal du lagre tekst? Da må du vurdere om det har statisk eller variabel lengde, om du har bruk for UNICODE karakterer og skal lagre tekst på forskjellige språk. Om du vet hvilken lengde det er på teksten du skal lagre, f.eks. et telefonnummer og systemet bare jobber i et land, kan det være lurt å velge char(n) da dette er bedre for dataintegritet samtidig som det tar halv lagringsplass av nchar(n).
Når man velger verdien n i en databasekolonne, er det viktig å forstå at n representerer antall bytes som kan lagres, ikke nødvendigvis antall karakterer. I tilfeller hvor det brukes single-byte encoding, vil n både definere antall bytes og antall karakterer, men ved bruk av multibyte encoding, kan en enkelt karakter kreve mer enn én byte, og det faktiske antallet karakterer som kan lagres vil derfor være mindre enn n.
For eksempel, i en char(10) kolonne med single-byte encoding kan man lagre 10 karakterer. Men i samme kolonne med multibyte encoding, vil det maksimale antallet karakterer være mindre enn 10.
Når det gjelder nchar og nvarchar datatyper, representerer n antall byte-par som kan lagres. Hvis alle karakterene som lagres kun bruker ett byte-par, kan man lagre det samme antallet karakterer som n. Men hvis man lagrer karakterer fra et høyere Unicode-område som krever to byte-par, vil antallet karakterer som kan lagres være lavere enn n.
For tid og dato må man se på hva man skal bruke datoen til, skal man f.eks. bare lagre fødselsdagen til en person holder det med Date da klokkeslett er irrelevant for hvilken dag det er. Skal man vite når et fly lander kan man velge å gå for DateTime2 (med lav presisjon, 3 desimaler) eller velge å dele det opp i Date og Time. Om man går for Date og Time vil det totalt bruke like mye lagringsplass som DataTime så det er jo et alternativ, men man har andre muligheter når det kommer til indeksering da man kan velge å indeksere på begge eller bare en av dem. Man må også vurdere om man har behov for tidssone eller ikke og om man vil lagre det ved siden av i en egen kolonne, eller sammen med Dato/tid. Om man ønsker alt i samme kolonne må man lagre det som DatetimeOffset. Om ikke kan man velge enten Datetime eller Datetime2 å lagre det som UTC og lagre tidssonen i en annen kolonne som char eller varchar.
Hvorfor påvirker datastørrelse hvilken ytelse spørringer har?
Grunnen til at mindre datatyper øker ytelsen er at man må lese, prosesserer og overføre mindre data fra server og til den som spør.
Om vi ser på hvordan en SQL database er bygd opp så har vi Buffer Cachen (temporart storage i minnet) og harddisker . Å lese fra minne er mye raskere enn å lese fra disk, så vi ønsker at mest mulig data som blir jevnlig brukt ligger i minnet. Buffer cachen består av buffer pages hvor hver page har en størrelse på 8KB. Dette kan enten være data eller "free space". SQL Serveren vil ikke fordele rader i cachen over to pages, men vil heller opprette en ny page for å få plass. Det betyr at om vi har to rader som hver utgjør 4.2KB vil denne dataen caches over to pages og serveren må lete gjennom begge for å finne dataen den er ute etter. Om man hadde fått radene ned til å bruke mindre enn 4KB ville disse bli cachet på samme page og dermed halvert antall pages serveren må lese gjennom.
Når man analyserer cache-bruk ønsker man at over 90% av dataen på serveren i minnet og at det leses mindre enn 90 pages per sekund. Det samme gjelder for writes. Om antallet overstiger 90 burde man se på databruk og indeksering.
Ved å indeksere riktig kan man senke tidsbruk per spørring betraktelig da serveren kun trenger å lete i minnet for å finne dataen man etterspør.
Endring av datatyper i eksisterende databaser
Om man allerede har en database og man ønsker å optimalisere den ved å endre datatyper er det et par ting å tenke på:
Endring av datatyper kan føre til tap av data, det kan hende systemene som snakker med databasen ikke støtter alle datatyper og det vil kunne ha en påvirkning på eksisterende indekser.
For å sørge for at man ikke mister data er det viktig å se an hva slags data som ligger i kolonnen og se om den er kompatibel med en annen datatype. Her må både lengde på innholdet og hva slags karakterer som lagres tas i betraktning.
Når det kommer til kompatibilitet mellom SQL serveren og systemene som snakker med den er dette noe man må sjekke med de som lager systemene eller eventuelt endre om man sitter med både database og systemet som snakker med databasen.
Det siste man må sørge for er at man bygger opp indeksene på nytt etter å ha endret datatype for å være sikker på at de er optimalisert for de nye datatypene.
Konklusjon
Å optimalisere databasen din ved å velge riktige datatyper for kolonene er en kraftig teknikk som kan føre til betydelige ytelsesforbedringer og lagringsbesparelser. Ved nøye analyse av dataene dine, valg av de mest passende datatypene og grundig teste endringer, kan du forbedre effektiviteten og påliteligheten til dine databasesystemer. Det å regelmessig gå gjennom databasen og optimalisere datatypene burde være en del av vedlikeholdsrutinene av databasen.
NB: Husk å alltid gjøre endringer i et staging-miljø før man begynner å tukle med produksjonsdata!
Ensō er spesialister på utvikling, arkitektur og teknologiledelse. Med initiativ, engasjement og en ukuelig vilje til å levere, sørger vi for at våre kunder får størst mulig verdi av vår erfaring og kompetanse. Ta kontakt her for en uforpliktende prat.
