Wat betekent de kardinaliteit van een index?
Auteur: Redactie Domeny.ForSale Gepubliceerd
Kort samengevat: De kardinaliteit van een index is het aantal verschillende sleutelwaarden in die index. De database geeft dit vaak als schatting weer. Dit helpt de optimizer te bepalen hoeveel rijen aan een queryvoorwaarde kunnen voldoen en of het loont om de index te gebruiken. Een hoge kardinaliteit helpt doorgaans bij het zoeken naar specifieke records, maar garandeert op zichzelf geen betere prestaties.
Wat indexkardinaliteit precies betekent
Een index is een aanvullende gegevensstructuur waarmee rijen kunnen worden gevonden zonder de hele tabel te doorzoeken. De sleutel bestaat uit de waarde van één kolom of een combinatie van waarden uit meerdere kolommen. Kardinaliteit beschrijft hoeveel verschillende sleutels in de gegevens voorkomen, niet hoeveel vermeldingen de index in totaal bevat.
Stel dat de tabel klanten 100 000 rijen bevat. Als elke klant een andere identificatiecode heeft, is de kardinaliteit van de index op de kolom id 100 000. Als de kolom status alleen de waarden actief, inactief en geblokkeerd bevat, is de kardinaliteit daarvan 3.
Het getal dat beheertools tonen, is niet altijd het exacte resultaat van een telling. De database-engine kan het bepalen op basis van een steekproef en opslaan in statistieken: informatie over de inhoud van tabellen die wordt gebruikt om query’s te plannen. Raadpleeg de documentatie van de betreffende engine voor de interpretatie van de waarde NULL en de precieze manier waarop kardinaliteit wordt weergegeven.
Kardinaliteit en de selectiviteit van een query
Kardinaliteit is een eigenschap van de gegevens in de index. De selectiviteit van een voorwaarde beschrijft daarentegen hoe sterk die voorwaarde de resultaatset beperkt. In de praktijk wordt een voorwaarde die slechts een klein deel van de tabel selecteert zeer selectief genoemd. Controleer bij het vergelijken van numerieke maatstaven wel de definitie, want de gebruikte conventies kunnen verschillen.
Bij het zoeken naar één unieke identificatiecode kan de database één rij vinden. Voor de voorwaarde status = 'actief' kan de database duizenden of tienduizenden rijen moeten ophalen. In dat tweede geval kan het lezen van de index en vervolgens het ophalen van de records uit de tabel meer kosten dan het volledig scannen van de tabel.
Het aantal verschillende waarden zegt echter niet hoe vaak elke waarde voorkomt. Als 99 000 klanten actief zijn en slechts 20 geblokkeerd, kan een index voor twee voorwaarden op dezelfde kolom heel verschillend van nut zijn. De kardinaliteit blijft 3, maar de gegevens zijn ongelijk verdeeld.
Daarom kan de optimizer — het onderdeel van de engine dat bepaalt hoe een query wordt uitgevoerd — ook andere statistieken gebruiken, zoals histogrammen die de verdeling van waarden beschrijven. Of deze beschikbaar zijn en hoe ze worden gebruikt, hangt af van de database.
De kardinaliteit van een samengestelde index begrijpen
Een samengestelde index omvat meer dan één kolom. Bij een index op (land, stad) gaat het om het aantal verschillende paren, niet alleen om het aantal landen of steden afzonderlijk.
Een tabel kan bijvoorbeeld 10 landen en 500 verschillende land-stadparen bevatten. De kardinaliteit van het eerste deel van de sleutel is dan 10 en die van de volledige sleutel 500. Vermenigvuldig het aantal verschillende landen niet zomaar met het aantal verschillende steden: niet elke mogelijke combinatie hoeft in de tabel voor te komen.
Sommige tools tonen afzonderlijke schattingen voor opeenvolgende begindelen van de index. In MySQL moet je het veld Cardinality in het resultaat van SHOW INDEX lezen in samenhang met de kolompositie die Seq_in_index aangeeft. De schatting geldt voor het indexprefix dat op die positie eindigt.
Ook de volgorde van de kolommen bepaalt waarvoor de index bruikbaar is. Bij een gebruikelijke B-tree-index, een index met een geordende boomstructuur, ondersteunt (land, stad) van nature zoekopdrachten op land en op de combinatie van land en stad. Alleen op stad zoeken kan een andere toegangsmethode vereisen; de beschikbare optimalisaties hangen af van de engine.
Waarom een hoge kardinaliteit niet genoeg is
Een index op een kolom met vrijwel uitsluitend unieke waarden kan een goede keuze zijn voor een vergelijking met één specifieke waarde, maar minder nuttig zijn voor een breed bereik. Een query die op basis van een datum de meeste records ophaalt, gebruikt mogelijk geen index, ook al bevatten de datums veel verschillende waarden.
Ook de formulering van de voorwaarde is van belang. Een functie toepassen op een geïndexeerde kolom, bijvoorbeeld om uit een datum alleen het jaar te halen, kan het gebruik van een gewone index bemoeilijken. Controleer voor de betreffende engine of een functionele index mogelijk is of dat de query zo kan worden herschreven dat de index wel bruikbaar is.
Een index kan daarnaast helpen bij het sorteren, het koppelen van tabellen of het lezen van gegevens zonder de tabel te raadplegen, als de index alle benodigde kolommen bevat. Een lage kardinaliteit is dus op zichzelf geen reden om een index te verwijderen. Houd ook rekening met de kosten: de ingenomen opslagruimte en het extra werk bij het schrijven en bijwerken van gegevens.
Controleren of een index helpt
Beoordeel een index aan de hand van een echte query, niet alleen op basis van het getal in het beheerpaneel:
- Kies een gebruikelijke query en representatieve parameterwaarden, waaronder zowel veelvoorkomende als zeldzame waarden.
- Bekijk het uitvoeringsplan met
EXPLAINof het equivalent daarvan in je engine. - Let op de gekozen index, het geschatte aantal rijen en de manier waarop de tabel wordt benaderd.
- Vergelijk de schattingen met de daadwerkelijke uitvoering als dat veilig kan. Houd er rekening mee dat opdrachten die de uitvoering analyseren de query ook kunnen uitvoeren.
- Controleer bij grote afwijkingen of de statistieken actueel zijn en raadpleeg de databasedocumentatie voor de regels waarmee ze worden vernieuwd.
Na een grootschalige import, het verwijderen van records of een verandering in de verdeling van waarden weerspiegelen de statistieken mogelijk niet meer de actuele gegevens. Bekijk voordat je nog een index toevoegt het plan van de problematische query en de toestand van de statistieken. Zo kun je onderscheid maken tussen het ontbreken van een geschikte index en een onjuiste kostenschatting.
Zie ook: Wat is FireFox?
Lees ook
