Forskjell mellom Index og Primær

[TheClown]

Hei, jeg har en database med mange tables. I ommetrent alle har hver row en ID som er unik. Det er denne IDen jeg som oftest bruker i WHERE spørringer.

 

Burde jeg sette dem som Index eller Primær (eller Unique), og hva er forskjellen?

 

Akkurat dette har jeg aldri forstått og jeg klarer ikke å lese meg til det på internett.

[blackbrrd]

Les denne siden og kom med spørsmål hvis det er noe du fortsatt lurer på:

http://en.wikipedia.org/wiki/Unique_key

[kaffenils]

Her er noen av forskjellene:

Du kan kun ha en primærnøkkel pr tabell. Dens kolonner kan ikke være null. En unique constraint kan inneholde null verdier. primærnøkler og unqiue constrains trenger ikke å håndheves ved å bruke en unqiue index, men det er den vanligste (og eneste jeg kjenner til) måten å gjøre det på.

 

Nullability er den viktigste forskjellen på primary og unique constraint. En annen viktig forskjell, iallefall på SQL Server, er at du ikke får komprimert (rebuild) en unique constraint/primary key (selv om det er i "bakgrunnen" er en unique index), slik du kan med en index (vha ALTER INDEX {index_name} on <object> REBUILD + options).

 

Derfor bruker jeg alltid unique indexes istedet for unique constraints. I tillegg til primary key constraints.

[qualbeen]

Du bruker helt klart indeksen sin som om det var primærnøkkel. Og det er ingenting galt i det. Streng tatt, er det nok det som er vanligst.

 

Hvis databasen også vet at dette er primærnøkkel vil den hjelpe deg ved innsetting av nye rader: Du får rett og slett ikke lov til å ha to rader med samme index, det blir istendefor generert en feilmld.

 

Grunnen til at man kan angi primærnøkkel som noe annet enn en index, er at i visse tilfeller er ikke en unik teller god nok identifikator. Hvis jeg skulle lage en oversikt over adresseboka mi, ville jeg kanskje brukt telefonnummeret som primærnøkkel, istendenfor en teller. (Vil dog feile om to eller flere personer er registret på samme nummer)

[kaffenils]

Dette her skjønte ikke jeg mye av. Blander du index med identity/autincrement?

[qualbeen]

ja, det gjør jeg. Tenkte på manuell nøkkel, inkrementert i kode ikke i db. Index brukes kanskje mest mot oppslag? Enlighten me!

[TheClown]

Me too

 

Superbumper denne.

 

Har mange tables med en rad for ID, og resten er andre ting. IDen er det jeg bruker for å hente informasjon i spørringer.

 

I de situasjonene hvor ID er auto_inc, er det riktig å ha PRIMARY KEY på den?

Og i de situasjonene hvor ID er brukerID er det riktig å ha INDEX på den?

[quantum]

Har mange tables med en rad for ID, og resten er andre ting. IDen er det jeg bruker for å hente informasjon i spørringer.

 

I de situasjonene hvor ID er auto_inc, er det riktig å ha PRIMARY KEY på den?

Og i de situasjonene hvor ID er brukerID er det riktig å ha INDEX på den?

 

Å ha en primærnøkkel er vel omtrent det mest grunnleggende kravet til en fornuftig tabell i en database. En primærnøkkel kan bestå av ett eller flere felt i tabellen, og det er et krav at disse har unik verdi. Dersom nøkkelen er sammensatt er kravet at kombinasjonen av verdier er unik.

 

Om databasen støtter autoincrement og om du skal bruke dette på (deler av) primærnøkkelen er helt opp til deg. Om du har auto-increment på en kolonne vil den kanskje være egnet som pk, men det er ingenting som sier at den skal være det. Det som er viktig er å ha en pk.

 

Som nevnt av en annen ovenfor er index den vanligste (og også for meg den eneste kjente) måten å implementere unik-constraint på en pk, indeksen opprettes som regel av seg selv når du spesifiserer hva tabellen har som pk.

 

Når det gjelder primærnøkler er det etter min mening greiest å ha en egen kolonne kalt f.eks. ID, som i tilfellet her, som primærnøkkel, og å benytte en sekvens til å generere verdier, dersom basen ikke støtter autoincrement. Dette synes jeg fordi det er ryddig og greit å programmere mot, og fordi det blir litt enklere å bruke ymse verktøy sammen med databasen, alle tabellene kan da konfigureres «likt» mot evt. verktøy, pk er alltid en kolonne med heltallsverdi og navn ID.

 

Alternativt kan man ha reelle «domenedata» som pk, f.eks. kan man bruke personnummer i en persontabell, telefonnr. i en telefontabell, eller en kombinasjon av flere kolonner dersom det er nødvendig for å kunne identifisere en rad unikt. Dette kan på noen måter være en bedre tilnærming, det kan f.eks. være et tegn på at datamodellen ikke er «god» dersom det er vanskelig å vite hva som bør være pk.

 

Det jeg først og fremst vil fraråde er å blande strategiene i en og samme database.

[TheClown]

Har PK på alle tablene mine nå, på kolonnen ID. Men kan jeg ha den som index også? Eller har det ingen hensikt?

[kaffenils]

En annen viktig egenskap med indexer og primary keys er om de er clustered eller non-clustered.

En tabell kan kun ha en index eller primary key som er clustered. Alle kolonner lagres sammen med en eventuell clustered index, og data er lagret fysisk i sortert rekkefølge ihht den clustrede indexen.

 

Indexer som ikke er clustered lagres i et separat index tre (f.eks. b-tre som er det vanligste) og inneholder i tilegg til kolonnene som indexen består av, også kolonnene som den clustrede indexen består av.

 

En clustered index vil påvirke ytelsen både positivt og negativt. En insert eller update av verdiene i den clustrede indexen vil kunne medføre page splits, som krever ekstra disk I/O, siden radene er fysisk lagret i rekkefølgen til den clustrede indexen. På den positive siden så vil f.eks. range scans på kolonnene til den clustrede indexen kunne gi en ytelseforbedring i forhold til en non-clustered index, spesielt hvis spørringen også skal returnere kolonner som ikke er inkludert i den ikke-clustrede indexen.

 

Det er derfor viktig å velge riktige kolonner for en evetuell clustered index. Hva som er den beste kandidaten avgjøres som regel av hvilke spørringer du ser for deg vil være vanlige. Har du f.eks. en tabell som logger temperaturer for dato og tidspunkter, og den vanligste spørringen henter ut data for et tidsintervall, så er date/tid kolonnen en god kandidat for den clustrede indexen.

 

Kolonnene i en clustered index trenger ikke å være unike, men databasemotoren vil da som regel legge til en skjult numerisk kolonne som gjør verdien unik.

 

Har PK på alle tablene mine nå, på kolonnen ID. Men kan jeg ha den som index også? Eller har det ingen hensikt?

De har ingen hensikt siden databsemotoren håndhever PK ved å opprette en unique index.

[quantum]

En annen viktig egenskap med indexer og primary keys er om de er clustered eller non-clustered.

... se der lærte jeg noe nytt i dag også :-)