SQL-indexering uitgelegd

31 of 31 rows


Indextypen
	Gebalanceerde boom voor gelijkheid, ranges en gesorteerde scans. Standaard in de meeste databases.	Gebruik voor =, <, >, BETWEEN, ORDER BY. Niet voor full-text search.
	Index waarvan het bladniveau de tabel zelf is. Rijen worden in sleutelvolgorde opgeslagen. Één per tabel.	InnoDB clustert op de primaire sleutel. SQL Server-tabellen zonder een dergelijke index zijn heaps.
	Aparte structuur met sleutels plus een verwijzing terug naar de basistabel. Vele per tabel mogelijk.	Op een heap slaat hij een row-ID op. Op een geclusteerde tabel slaat hij de clustersleutel op.
	Hashtabel uitsluitend voor gelijkheidslookups. Exacte match in constante tijd.	Gebruik alleen voor =-operaties; ranges en sorteren vereisen een B-tree. De hash-index voor memory-geoptimaliseerde SQL Server-tabellen heeft een bucket count nodig dicht bij het rijaantal.
	Generalized Inverted Index. Beeldt waarden af op rijen. Gebouwd voor arrays en full-text.	Gebruik voor jsonb, arrays, tsvector en trigram-zoekopdrachten. Langzamer te bouwen dan B-tree.
	Generalized Search Tree. Uitbreidbaar voor niet-scalaire typen en overlapquery's.	Gebruik voor geometrische ranges, kNN en exclusion constraints. pg_trgm versnelt LIKE.
	Space-partitioned GiST. Splitst data in niet-overlappende regio's via radix- of quadbomen.	Gebruik voor punten, IP-prefixen en prefix-matching. Niet voor overlapquery's; gebruik GiST.
	Block Range Index. Slaat min/max per blokbereik op. Zeer kleine voetafdruk.	Gebruik op grote, fysiek gesorteerde tabellen. Goedkoop om te bouwen, zwakke selectiviteit.
	Index opgeslagen als kolomsegmenten in plaats van rijen. Hoge compressie, batch-uitvoering.	Gebruik voor analytische scans over veel rijen en weinig kolommen. Lookups van één enkele rij zijn traag.
	Toegewijde omgekeerde woordindex in MySQL en SQL Server. Per tekstkolom gebouwd.	Gebruik MATCH AGAINST of CONTAINS. Postgres full-text gebruikt in plaats daarvan GIN op tsvector.
	Oracle-index die één bitmap per distincte waarde opslaat. Elke bit markeert één rij.	Gebruik op kolommen met lage kardinaliteit in lees-intensieve datawarehouses. Gelijktijdige schrijfacties serialiseren per bitmap.
	Indexkolom gedeclareerd als DESC. Sleutels voor die kolom worden in omgekeerde volgorde opgeslagen.	Nodig voor gemengde sorteringen zoals (a ASC, b DESC). Gewone DESC-sorteerbewerkingen kunnen ook een normale index achterstevoren lezen.
	MySQL 8-index die de optimizer negeert. Wordt nog steeds bij elke schrijfactie onderhouden.	Verberg een index voordat je hem dropt om de impact te testen. Maak hem zonder rebuild weer zichtbaar.
Ontwerp
	Index met meerdere kolommen. De leidende kolommen moeten overeenkomen met de filtervolgorde van de query.	Zet gelijkheidskolommen eerst, dan rangekolommen. Twee losse indexen kunnen één samengestelde index niet vervangen.
	Index op een WHERE-predicaat. SQL Server noemt het een gefilterde index. Dekt alleen overeenkomende rijen.	Indexeer alleen rijen met active='t'. Halveert omvang en schrijkosten.
	B-tree met extra niet-sleutelkolommen toegevoegd. Index-only scans vermijden heap-lookups.	Voeg vaak opgehaalde kolommen toe via INCLUDE. Houdt de index sorteerbaar.
	Index die duplicaatwaarden uitsluit. Weigert inserts die botsen.	Gebruik voor natuurlijke sleutels en één-op-één-constraints. UNIQUE met NULL staat vele NULL's toe.
	Index gebouwd op een functie van kolommen in plaats van op de kolommen zelf.	Indexeer lower(email) of date_trunc('day', created_at). De query moet exact dezelfde expressie herhalen.
	Operatorclass. Bepaalt hoe een index het type van één kolom vergelijkt en opslaat.	Stel varchar_pattern_ops in voor LIKE 'abc%' zonder case-folding. De standaardwaarden passen bij gelijkheid en ranges.
	Plan dat een query alleen uit indexpagina's beantwoordt. De heap wordt nooit gelezen.	Vereist elke opgehaalde kolom in de index of de INCLUDE-lijst. Vacuum houdt de visibility map actueel.
	Foreign-key-kolommen krijgen geen automatische index op de verwijzende tabel.	Indexeer elke FK-kolom waar je door joint of door verwijdert. Cascade-verwijderingen scannen zonder index de gehele childtabel.
Onderhoud
	Planinspecteur. Toont sequentiële scans, indexkeuzes en kostenramingen.	Gebruik EXPLAIN (ANALYZE, BUFFERS). Een Seq Scan op een tabel van 10 miljoen rijen betekent een ontbrekende index.
	Bemonstert tabelkolommen om plannerstatistieken te bouwen. Histogrammen sturen de indexkeuze.	Draai na bulk loads. Verouderde statistiek veroorzaakt seq scans op geïndexeerde kolommen.
	Elke index voegt schrijf overhead toe. Inserts, updates en deletes betalen per index.	Drop ongebruikte indexen. Volg het gebruik in pg_stat_user_indexes.
	Postgres-view met gebruik per index. Telt scans en gelezen tuples sinds de laatste stats reset.	Filter op idx_scan = 0 om ongebruikte indexen te vinden. Reset tellers met pg_stat_reset().
	Bouwt een index zonder lees- of schrijfacties te blokkeren. Vereist twee table scans.	Kan niet binnen een transactie draaien. Een mislukte build laat een INVALID index achter die je moet droppen.
	Verwijdert een index en geeft de opslag vrij. Neemt standaard een exclusief lock.	Gebruik DROP INDEX CONCURRENTLY op drukke tabellen. Controleer de gebruiksstatistiek vóór verwijderen.
	Bouwt een opgeblazen index opnieuw. Wint ruimte terug van dode tuples.	Gebruik REINDEX CONCURRENTLY in productie. Zonder dit blokkeren locks alle schrijfacties.
	Dode tuples en paginagaten die zich opstapelen in MVCC-tabellen en hun indexen.	Meet met pgstattuple. Vacuum vaak; REINDEX wanneer dode ruimte domineert.
	Percentage van een pagina dat bij het schrijven wordt volgepakt. Vrije ruimte absorbeert toekomstige updates.	Verlaag hem naar 70-90 op tabellen met veel updates. Volle pagina's forceren page splits.
	Herschrijft de tabel fysiek gesorteerd op een index. Eenmalige operatie.	Combineer met BRIN voor gesorteerde blokken. De volgorde blijft niet behouden bij toekomstige schrijfacties.