SQL-indeksointiExplained

31 of 31 rows

Käsite↕	Selitys↕	Vinkki↕
Indeksityypit
⤢B-tree	Tasapainotettu puu yhtäsuuruus-, haarukka- ja lajitteluhakuihin. Oletus useimmissa tietokannoissa.	Käytä operaattoreihin =, <, >, BETWEEN, ORDER BY. Älä täystekstihakuun.
⤢Clustered	Indeksi, jonka lehtitaso on itse taulu. Rivit tallennetaan avainjärjestyksessä. Yksi per taulu.	InnoDB klusteroi ensisijaisen avaimen mukaan. SQL Server -taulut ilman sitä ovat kekoja (heap).
⤢Nonclustered	Erillinen rakenne, joka sisältää avaimet ja osoittimen perustauluun. Monta per taulu.	Keossa se tallentaa rivitunnuksen. Klusteroidussa taulussa se tallentaa klusterointiavaimen.
⤢Hash	Hajautustaulu vain yhtäsuuruushakuihin. Tarkka osuma vakioajassa.	Käytä vain = -operaatioihin; haarukat ja järjestys vaativat B-treen. SQL Serverin muistiin optimoitu hash tarvitsee bucket count -arvon lähelle rivimäärää.
⤢GIN	Generalized Inverted Index. Kuvaa arvot riveihin. Tehty taulukoille ja täystekstille.	Käytä jsonb:hen, taulukoihin, tsvectoriin ja trigramihakuun. Hitaampi rakentaa kuin B-tree.
⤢GiST	Generalized Search Tree. Laajennettavissa ei-skaalareille tyypeille ja päällekkäisyyskyselyille.	Käytä geometrisiin haarukoihin, kNN-hakuihin ja poissulkurajoitteisiin. pg_trgm nopeuttaa LIKEä.
⤢SP-GiST	Tilaosiottava GiST. Jakaa datan päällekkäättömiin alueisiin radix- tai quadpuilla.	Käytä pisteisiin, IP-etuliitteisiin ja etuliitehakuihin. Ei päällekkäisyyksiin; käytä GiSTiä.
⤢BRIN	Block Range Index. Tallentaa min/max-lohkoalueittain. Hyvin pieni jalanjälki.	Käytä suurissa fyysisesti lajitelluissa tauluissa. Halpa rakentaa, heikko selektiivisyys.
⤢Columnstore	Indeksi, joka on tallennettu sarakesegmentteinä rivien sijaan. Korkea pakkausteho, eräsuoritus.	Käytä analyyttisiin luentoihin monen rivin ja harvan sarakkeen yli. Yksittäisten rivien haut ovat hitaita.
⤢Full-text	Erillinen käänteinen sanaindeksi MySQL:ssä ja SQL Serverissä. Rakennetaan tekstisaraketta kohden.	Käytä MATCH AGAINST- tai CONTAINS-lauseita. Postgresin täysteksti käyttää sen sijaan GINiä tsvectorilla.
⤢Bitmap	Oraclen indeksi, joka tallentaa yhden bitmapin kutakin erillistä arvoa kohden. Jokainen bitti merkitsee yhtä riviä.	Käytä matalakardinaliteettisissa sarakkeissa lukupainotteisissa tietovarastoissa. Samanaikaiset kirjoitukset sarjoittuvat bitmapia kohden.
⤢Descending	DESC-määritteinen indeksisarake. Kyseisen sarakkeen avaimet tallennetaan käänteisessä järjestyksessä.	Tarvitaan sekalajitteluun kuten (a ASC, b DESC). Pelkät DESC-lajittelut voivat myös lukea tavallisen indeksin takaperin.
⤢Invisible	MySQL 8 -indeksi, jonka optimoija ohittaa. Ylläpidetään silti jokaisessa kirjoituksessa.	Piilota indeksi ennen poistoa vaikutuksen testaamiseksi. Tee siitä uudelleen näkyvä ilman uudelleenrakennusta.
Suunnittelu
⤢Composite	Monisarakkeinen indeksi. Johtavien sarakkeiden on vastattava kyselyn suodatinjärjestystä.	Laita yhtäsuuruus ensin, sitten haarukka. Kaksi yksittäistä indeksiä ei korvaa yhtä yhdistelmäindeksiä.
⤢Partial	Indeksi WHERE-ehtoon. SQL Server kutsuu sitä suodatetuksi indeksiksi (filtered index). Kattaa vain sopivat rivit.	Indeksoi vain active='t' -rivit. Puolittaa koon ja kirjoituskustannuksen.
⤢Covering (INCLUDE)	B-tree ylimääräisillä ei-avainsarakkeilla. Vain indeksiin perustuvat luennot välttävät kekoluennot.	Lisää usein haetut sarakkeet INCLUDE-listaan. Pitää indeksin lajiteltavana.
⤢Unique	Indeksi, joka kieltää päällekkäiset arvot. Hylkää törmäävät lisäykset.	Käytä luonnollisille avaimille ja yhteen yhteen -rajoitteille. UNIQUE NULL sallii monta NULLia.
⤢Expression	Indeksi, joka on rakennettu sarakkeiden funktiolle eikä sarakkeille itselleen.	Indeksoi lower(email) tai date_trunc('day', created_at). Kyselyn on toistettava täsmälleen sama lauseke.
⤢Opclass	Operaattoriluokka. Määrittää, miten indeksi vertaa ja tallentaa yhden sarakkeen tyypin.	Aseta varchar_pattern_ops LIKE 'abc%' -hakuun ilman kirjainkoon muunnosta. Oletukset sopivat yhtäsuuruuteen ja haarukoihin.
⤢Index-only scan	Suunnitelma, joka vastaa kyselyyn pelkillä indeksisivuilla. Kekkoa ei koskaan lueta.	Vaatii jokaisen haetun sarakkeen indeksiin tai INCLUDE-listaan. Vacuum pitää näkyvyyskartan ajan tasalla.
⤢Foreign keys	Viiteavainsarakkeet eivät saa automaattista indeksiä viittaavaan tauluun.	Indeksoi jokainen viiteavainsarake, jolla liität tai poistat. Kaskadipoistot skannaavat lapsitaulun ilman sitä.
Ylläpito
⤢EXPLAIN	Suunnitelmatarkastaja. Näyttää peräkkäisskannaukset, indeksivalinnat ja kustannusarviot.	Käytä EXPLAIN (ANALYZE, BUFFERS) -muotoa. Seq Scan 10 miljoonan rivin taulussa tarkoittaa puuttuvaa indeksiä.
⤢ANALYZE	Näytteistää taulun sarakkeet suunnittelijatilastojen rakentamiseksi. Histogrammit ohjaavat indeksivalintaa.	Aja massalatausten jälkeen. Vanhentuneet tilastot aiheuttavat peräkkäisskannauksia indeksoiduissa sarakkeissa.
⤢Over-indexing	Jokainen indeksi lisää kirjoituskustannusta. Lisäykset, päivitykset ja poistot maksavat jokaisesta indeksistä.	Poista käyttämättömät indeksit. Seuraa käyttöä näkymästä pg_stat_user_indexes.
⤢pg_stat_user_indexes	Postgresin näkymä indeksikohtaiseen käyttöön. Laskee skannaukset ja luetut tuplet tilastojen nollauksen jälkeen.	Suodata idx_scan = 0 löytääksesi käyttämättömät indeksit. Nollaa laskurit funktiolla pg_stat_reset().
⤢CREATE INDEX CONCURRENTLY	Rakentaa indeksin estämättä lukuja tai kirjoituksia. Tarvitsee kaksi taulun skannausta.	Ei voi ajaa transaktion sisällä. Epäonnistunut rakennus jättää INVALID-indeksin poistettavaksi.
⤢DROP INDEX	Poistaa indeksin ja vapauttaa sen tilan. Ottaa oletuksena yksinomaisen lukon.	Käytä DROP INDEX CONCURRENTLY -muotoa ruuhkaisissa tauluissa. Tarkista käyttötilastot ennen poistoa.
⤢REINDEX	Rakentaa turvonneen indeksin uudelleen. Palauttaa tilaa kuolleista tupleista.	Käytä REINDEX CONCURRENTLY -muotoa tuotannossa. Ilman sitä lukot estävät kirjoitukset.
⤢Bloat	Kuolleet tuplet ja sivuvälit, jotka kertyvät MVCC-tauluihin ja niiden indekseihin.	Mittaa pgstattuple-laajennoksella. Aja Vacuumia usein; REINDEX kun kuollut tila hallitsee.
⤢Fillfactor	Sivun täyttöprosentti kirjoitushetkellä. Vapaa tila imee tulevat päivitykset.	Laske 70-90:een kuumasti päivittyvissä tauluissa. Täydet sivut pakottavat sivujakoon (page split).
⤢CLUSTER	Kirjoittaa taulun uudelleen fyysisesti indeksin järjestykseen. Kertaalleen suoritettava toimenpide.	Sopii yhteen BRINin kanssa lajitelluissa lohkoissa. Järjestys ei säily myöhemmissä kirjoituksissa.