MySQL(database), Indeksering av tabeller?

[RipZ-]

RiPZ - tror du er på meget tynn is.

 

Den tiden du sparer på å gjøre spørringen på en indeksert tabell, det er den tiden som allerede brukt på forhånd for å indeksere tabellen. og mere der til. tabellen er indeksert for å optimalisere alle spørringer, ikke bare de radene du spør om der og da.

 

jeg kan vanskelig se for meg at dette skal lønne seg utenom helt merkelig bisarre tilfeller.

Er fullstendig klar over at det høres veldig merkelig ut. Derfor hadde det vært flott om noen kunne testet ut akkurat dette. Skal selv ta noen tester rundt dette så snart jeg får tilgang til MySQL-databasen min.

 

Men jeg er enig slik som du Sier Torbjørn, at det logisk sett burde være tregere. Derfor er det litt ekstra spennende å se det stemmer

[knatten]

For at det skal lønne seg å legge til indexer før man kjører spørring må dette tilfredsstilles:

 

I kronologisk rekkefølge:

1: Svært mange med inserts over en lang periode

2: Helt slutt på inserts

3: Bygg index

4: Rimelig mye lesing

5: Drep index

6: Loop

 

Å bygge indexen før hver eneste select kan jeg ikke på noen måte forstå at skal være fornuftig. Jeg tror ikke jeg skal forsøke å bevise det, men står bastant på det inntil det motsatte er bevist, eller har en sterk sak.

[RipZ-]

Nå var ikke det å bygge en index for hver spørring heller poenget. Men når man for eksempel bygger et statistikkscript så er det mye mer skriving enn lesing. Da kan man kanskje med fordel skru av index og heller bygge indexen når man skal lese innholdet (noe man gjør relativt sjeldent). Jeg tror nok heller ikke det lønner seg å bygge index for hver eneste spørring. Men skal man avlese en god del innhold, så kanskje?

 

Spørsmålet man kan stille seg om man har et slikt behov, er vel kanskje om man bruker rett type database.

[????????]

Nå var ikke det å bygge en index for hver spørring heller poenget. Men når man for eksempel bygger et statistikkscript så er det mye mer skriving enn lesing. Da kan man kanskje med fordel skru av index og heller bygge indexen når man skal lese innholdet (noe man gjør relativt sjeldent). Jeg tror nok heller ikke det lønner seg å bygge index for hver eneste spørring. Men skal man avlese en god del innhold, så kanskje?

 

Spørsmålet man kan stille seg om man har et slikt behov, er vel kanskje om man bruker rett type database.

Da er det mer vanlig å opprette f.eks. en tabell for hver måned, kvartal eller lignende. Etter denne tiden så behøver det ikke å være mulig å skrive til databasen, så da pakker man den og merger den sammen med resten av den gamle statistikken.

[RipZ-]

????????: Riktig det. Men så brukte jeg statistikkscript som et eksempel. Men er helt enig, som regel finnes det bedre metoder enn å legge til, for deretter fjerne indexen for hver gang.

 

Har litt lyst å trekke frem denne tråden igjen da jeg har gjort noen små tester. På en av sidene jeg er webmaster for, nuffe.net, har jeg 4 tabeller som alle deler en felles ID. Tre av tabellene er for de forskjellige tjenestene jeg tilbyr, den siste for brukeropplysninger.

 

I et script på admin-delen finner jeg automatisk de siste 30 registrerte brukerene, finner ut hvor mange som er registrert i dag, samt hvilke tjenester de bruker. For å finne ut dette må jeg joine alle tabellene inn i en spørring.

 

Først gjorde jeg en test når de tre tjeneste-tabellene mine ikke hadde indeksert kolonnen id. Scriptet brukte da hele 47 sekunder på spørringen. Da jeg indekserte tabellene, noe som tok 0,02 s, 0,02 s og 0,1 s for hver av tabellene, tok scriptet under et halvt sekund å kjøre.

 

Med andre ord ville det her vært mye å hente på å bruke metoden jeg beskrev ved å først indeksere, kjøre spørringen og deretter slette indeksen. Til opplysning inneholder hver av tabellene rundt 2000 - 3000 rader hver.

 

Bare for å gjøre det helt klart. Prøver ikke å vinne en debatt, men synes det er veldig gøy å gå litt dypere i enkelte temaer. Hadde vært morsomt om flere kunne prøvd ut denne teknikken på større databaser og se om de henter like mye på det. For det kan selvfølgelig være at dette dreier seg om et merkelig bisarr tilfelle som Torbjørn liker å kalle det

[????????]

Det er ikke et så ekstremt tilfelle, med det er to viktige ting:

 

- Tiden det det å indekstere er avhneig av databasestruktur og databasetypen.

 

- Det andre er at 2 - 3000 rader er en liten database

 

Bra at du kommer info om testinger De fleste her bygger små scripts til hjemmesidene sine og for de er dette kjekk info. Hovedregelen burde likevel alltid være å planlegge databasen nøye, og indeksere databasen før scriptet lanseres.

[Beisfjord]

For det første, hva RipZ- trolig leste var at det lønnet seg å legge på index etter(!) at man har lagt inn all dataen i en database. Altså hvis man skal flytte data fra en databae til en annen, og det er snakk om hundrevis av MB, eller GB med data. vil det være mye tid å hente på først å legge inn dataene og deretter sette opp index'er. Grunnen til dette er at da slipper man at dbms'en reindexerer unødvendig mange ganger under innlegging av data.

 

Et annet viktig poeng å få fram er jo det at index'er først og fremst er nødvendige for å redusere antall diskoppslag for dbms'en. Det er tross alt det som tar tid. Om databasen er så liten at den får plass i minnet, så er det ikke alltid så veldig mye å hente på å indeksere den da det som ligger i minnet uansett går veldig fort.

Endret 25. oktober 2004 av Psi_^

[Torbjørn]

kan du utdype dette litt mer:

 

"[...] da slipper man at dbms'en reindexerer unødvendig mange ganger under innlegging av data."

 

hva er det som reindekseres ved inlegging av data? ved hver ny rad skal den raden indekseres, hva har dette å si mtp allerede eksisterende data?

[Beisfjord]

Må nesten inrømme at jeg synes du har et godt poeng

 

Altså poenget mitt var at det er index'ene reindex'eres etter hvert som man får mer data å basere disse på. Altså dbms'en styrer og herjer noe værre med å plassere data rett på disken, optimalisere index'er, osv.. Så da var poenget at ved å spare all den jobben til slutt så ville en spare seg mye mye bry. Hvis man kan spare 10-15 ganger med reorganisering av data som ligger på disk ved å droppe indexer ved populering, så bør det gå dertil fortere

 

Uansett, best pratice etter hva jeg har hørt er at man dropper auto-commit og index'er ved populering av databaser, for å få det til å gå fortere.

Endret 25. oktober 2004 av Psi_^

[RipZ-]

Det er ikke et så ekstremt tilfelle, med det er to viktige ting:

 

- Tiden det det å indekstere er avhneig av databasestruktur og databasetypen.

 

- Det andre er at 2 - 3000 rader er en liten database

 

Bra at du kommer info om testinger De fleste her bygger små scripts til hjemmesidene sine og for de er dette kjekk info. Hovedregelen burde likevel alltid være å planlegge databasen nøye, og indeksere databasen før scriptet lanseres.

Enig i at 2 - 3000 er relativt lite.

 

Å indeksere en av de andre tabellene mine, tok 0.0915 sec. Den bestod av 50.000 rader. Da indeksterte jeg id-kolonnen som består av 2305 forskjellige verdier. La oss si vi skal joine to tabeller av samme størrelse ved å sammenlikne ID-kolonnen. Det blir 50.000^2 forskjellige rader som må sammenliknes. Da vil jeg absolutt tro det vil lønne seg å legge til indeks, lese data og deretter fjerne indeksen. Dette trenger fortsatt ikke være den beste måten å gjøre det på

 

Noen som vet sånn ca hvor mye man sparer på å inserte uten indeksering i forhold til en tabell med indeksering? Dette kommer jo selvfølgelig an på hvor mange rader som er indeksert osv. Men hadde vært litt morsomt å sett hvor mye man egentlig sparer av tid på å utelate indekseringen ved inserts.

[Beisfjord]

Nå glemmer du det faktum at for å indeksere en tabell så må du søke gjennom alle dataene for den tabellen, og i tilfelle tabellen er for stor til å ha i minnet så må du da først lese alle dataene inn i minnet for å konstruere index'en. Deretter optimalierer dbms'en blant annet utfra hvilke kolonner som er index'ert for så å gå ut på disken og hente de dataene som etterspørs.

 

Indekser skal IKKE lages, og droppes mellom hver spørring. Selv om det i ditt tilfelle kun tok 0.0915 sekunder å indeksere tabellene så betyr ikke det annet enn at spørringene dine tar minst 0.0915 sekunder ekstra å kjøre

[RipZ-]

Nå var poenget det at man tjener inn de 0.0915 sekundene veldig mange ganger. Les min første post og hvor mye jeg tjente på å legge inn + droppe spørringen. Grunnen til at vi dropper spørringen er for at insert-spørringene skal bruke mindre tid.

 

Dette er selvfølgelig på INGEN måte en god idè om man leser fra databasen ofte. Jeg vet heller ikke hvordan denne teknikken fungerer med databaser som er for store for minnet.

 

Over til spørsmålet fra min forrige post. Noen som vet hvor mye man tjener av tid på insert-spørringer uten indeks i forhold til insert-spørringer som må indekseres?

[Beisfjord]

Over til spørsmålet fra min forrige post. Noen som vet hvor mye man tjener av tid på insert-spørringer uten indeks i forhold til insert-spørringer som må indekseres?

Her kommer man her tilbake til det evinnelige spørsmålet om diskaksess, og hvor index'er finnes, og hvordan de er optimalisert av dbms'en. Altså om røtter i trær pinnes i minnet, hvilket index typer som brukes, hvilke rader som er indeksert, osv... Umulig å gi et entydig svar, ut over det at det er raskere å sette inn rader i tabeller som ikke er indeksert. Hvor mye raskere kommer ann på dbms'en og hvilke felter som er indeksert.

 

Men det er klart i databaser som stort sett står og overvåker noe, som resulterer i hundrevis (les mange) av inserts i sekundet så kan det nok lønne seg å droppe indekser på disse feltene sånn at man slipper at databasen kneler av den grunn

Endret 26. oktober 2004 av Psi_^