Ga naar de inhoud 
MySQL-histogrammen voorzien de optimizer van echte distributiegegevens, zodat hij de selectiviteit correct kan inschatten en snellere queryplannen kan genereren – vaak zelfs zonder extra index. Ik laat zien hoe ik in MySQL 8+ histogrammen opzet met ANALYZE TABLE, deze controleer en gebruik voor betere beslissingen bij joins, filters en scans.
Centrale punten
Korte focus: De volgende punten geven aan waar ik bij het gebruik van histogrammen extra op let.
- Selectiviteit in plaats van een onderbuikgevoel: realistischere schattingen van de kardinaliteit
- Zonder index sneller: betere keuze van het plan bij scheve verdelingen
- Typen begrijpen: Singleton versus Equi-Height doelgericht inzetten
- Emmers belasting: afweging tussen afschrijving en kosten voor metadata
- Zorg In het oog: bijwerken, controleren, indien nodig verwijderen
Waarom histogrammen zonder index effect sorteren
Ik gebruik Histogrammen, omdat de optimizer anders vaak uitgaat van een gelijkmatige verdeling en daardoor slechte plannen kiest. Een histogram geeft de Verdeling van waarden begint bij een kolom en levert daarmee realistische selectiviteitsschattingen voor predikaten zoals =, >, BETWEEN, IN of IS NULL. De optimizer beslist vervolgens of een index-range-scan, een table-scan of een join-strategie met nested loops voordeliger is. Als een voorwaarde bijvoorbeeld slechts 0,1 % van de rijen treft, geef ik de voorkeur aan een gerichte toegang in plaats van een brede scan. Als een filter daarentegen bijna alle rijen selecteert, zie ik af van dure indextoegangen die geen voordeel opleveren en verhoog ik zo de Efficiëntie elk plan.
Soorten histogrammen in MySQL 8.0
Ik maak onderscheid tussen twee Typen: Singleton en Equi-Height. Singleton-histogrammen groeperen veelvoorkomende afzonderlijke waarden in aparte buckets – ideaal voor kolommen met een klein aantal dominante categorieën zoals „actief“, „inactief“ of „gearchiveerd“. Equi-Height-histogrammen verdelen het waardenbereik zodanig dat elke bucket ongeveer evenveel Lijnen bevat; dit is geschikt voor continue of onregelmatige verdelingen, zoals prijzen, tijdstempels of ID-reeksen met „gaten“. Beide varianten leveren de optimizer nauwkeurigere trefpercentages voor filters op. Ik kies het type altijd op basis van de eigenschappen van de gegevens, niet op basis van persoonlijke voorkeur.
Technische basisprincipes: het selecteren van typen in MySQL beheren
MySQL bepaalt de concrete Histogramvariant automatisch op basis van de gegevensverdeling. In de praktijk betekent dit: als het aantal verschillende waarden (NDV) klein genoeg is in verhouding tot het aantal buckets, ontstaat er in feite een singleton-histogram; anders wordt er een equi-height-histogram gegenereerd. Ik „kies“ daarom het type indirect, door de juiste kolom en een passend aantal buckets te kiezen. Voor kolommen met zeer weinig, maar sterk dominante categorieën stel ik bewust weinig buckets in om een singleton-achtige precisie voor deze waarden te behouden. Bij fijn gespreide, continue gegevens verhoog ik het aantal buckets stapsgewijs totdat EXPLAIN de gewenste Selectiviteit weerspiegelt.
Belangrijk: histogrammen zijn in één kolom. Afhankelijkheden tussen kolommen (bijvoorbeeld status en country) kunt u niet rechtstreeks weergeven. In dergelijke gevallen helpt het om de meest selectieve kolom van een histogram te voorzien en de volgorde van de joins daarop af te stemmen.
De juiste emmers kiezen
MySQL gebruikt standaard 100 Emmers, maar staat 1 tot 1024 toe via WITH N BUCKETS. Meer buckets verhogen de resolutie, maar zorgen ook voor meer metadata en meer analysewerk. Ik begin meestal voorzichtig, meet het effect op EXPLAIN en verhoog het aantal stapsgewijs als het plan nog steeds niet geschikt lijkt. Bij sterk geconcentreerde waarden (bijv. 90 % in één status) volstaan vaak weinig buckets; bij fijn verspreide prijzen of tijdstempels loont het om meer buckets te gebruiken. Het doel is een zinvolle Granulariteit, waardoor het aantal verkeerde inschattingen aanzienlijk is verminderd, zonder dat de administratieve lasten onnodig toenemen.
Praktijk: Workflow met ANALYZE TABLE
Ik volg een duidelijke Werkstroom: Eerst zoek ik kolommen die vaak in WHERE- of JOIN-voorwaarden voorkomen en die duidelijk scheve verdelingen vertonen. Vervolgens genereer ik een histogram met ANALYZE TABLE tbl UPDATE HISTOGRAM ON col WITH N BUCKETS; en controleer ik dit via INFORMATION_SCHEMA.COLUMN_STATISTICS. Na gegevensverplaatsingen werk ik de statistieken opnieuw bij met ANALYZE TABLE. Als een statistiek niet klopt, verwijder ik deze met ANALYZE TABLE tbl DROP HISTOGRAM ON col;. Om de effectiviteit van het uitvoeringsplan te beoordelen, bekijk ik EXPLAIN ANALYZE interpreteren en dezelfde ramingen vergeleken met de werkelijke cijfers Lijnen van.
Concrete opdrachten en controle
Ik werk op een reproduceerbare manier met een klein aantal duidelijke stappen en controleer de gegenereerde JSON-statistieken.
-- Histogrammen aanmaken voor afzonderlijke kolommen
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS;
ANALYZE TABLE orders UPDATE HISTOGRAM ON created_at WITH 128 BUCKETS;
-- Meerdere kolommen in één bewerking met hetzelfde aantal buckets
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, payment_method WITH 64 BUCKETS;
-- Histogrammen gericht verwijderen
ANALYZE TABLE orders DROP HISTOGRAM ON status;
-- Visuele controle van de statistieken
SELECT
SCHEMA_NAME, TABLE_NAME, COLUMN_NAME,
JSON_PRETTY(HISTOGRAM) AS histogram
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE SCHEMA_NAME = DATABASE()
AND TABLE_NAME = 'orders'
AND COLUMN_NAME IN ('status','created_at');
Ik evalueer het effect direct met EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE status = 'canceled'
AND created_at >= NOW() - INTERVAL 7 DAY;
Wordt de schatting beter? rijen Als er een merkbaar verschil is en het plan bijvoorbeeld overschakelt van een volledige scan naar een index-range-scan of de volgorde van de joins wijzigt, is de maatregel geslaagd. Blijft de afwijking groot, dan verhoog of verlaag ik het aantal buckets en vergelijk ik opnieuw.
Voorbeeld: orderstatus en ongebruikelijke waarden
In een tabel met bestellingen komt de status „completed“ vaak voor, terwijl „pending“ redelijk vaak voorkomt en „canceled“ zeer zelden; deze onevenwicht leidt zonder histogram gemakkelijk tot onjuiste selectiviteiten. Als een API naar „canceled“ zoekt, kan de optimizer ten onrechte kiezen voor een volledige tabelscan, terwijl een beperkte indextoegang volstaat. Met een singleton-histogram herkent MySQL dat „canceled“ slechts een minuscuul deel uitmaakt en schakelt het over op een index-range-scan of optimaliseert het de volgorde van de joins. Zo daalt de latentie en heb ik geen extra index nodig voor elke Variant van een filter. In dashboards met strenge SLO’s levert deze aanpassing vaak merkbare voordelen op wat betreft de reactietijd.
Tijdreeksen en tijdstempels
Bij tijdreeksen zijn er veel Toegang tot op basis van recente gegevens; oudere tijdsvensters blijven meestal ongebruikt. Een Equi-Height-histogram op `created_at` of `updated_at` maakt een onderscheid tussen drukbezochte tijdsperioden en zelden gebruikte. De optimizer schat vervolgens correct in of een range-scan zinvol is of dat een table-scan sneller tot het gewenste resultaat leidt. Vooral bij gedeeltelijke tijdsfilters op grote tabellen zie ik duidelijke wijzigingen in de uitvoeringsplan en lagere I/O-kosten. Ik beschouw de Statistieken hier vaker bijgewerkt, omdat de focus verschuift naar de dagelijkse gang van zaken.
Partities, gegevenstypen en collaties
Ik bekijk de gegevensverdeling in gepartitioneerde tabellen over alle partities. Grote verschillen (bijvoorbeeld per maand) kunnen globale histogrammen afvlakken. Als afzonderlijke partities extreem selectief of extreem breed zijn, test ik bovendien met ‘partition pruning’-filters in de WHERE-clausule of de plankwaliteit toch nog in orde is. Over het algemeen let ik erop dat ik filters zo formuleer dat MySQL partities vroeg uitsluiten kan.
Histogrammen werken het beste bij scalaire, vergelijkbare gegevenstypen (getallen, datum-/tijdwaarden, VARCHAR/CHAR met de juiste collatie). Bij LOB-/JSON-gegevens ik geef de voorkeur aan Gegenereerde kolommen met geëxtraheerde, getypeerde waarden en voorzie deze indien nodig van histogrammen of indexen. Bij strings bepaalt de Verzameling de vergelijkingslogica; afhankelijk van de collatie kunnen waarden overeenkomen (bijv. hoofdletters/kleine letters). Ik houd de collatie consistent met de query’s om realistische selectiviteiten te verkrijgen.
Grenzen en misstappen
Histogrammen geven vooral een beeld van afzonderlijke kolommen met Constanten Goed; ze geven afhankelijkheid tussen meerdere kolommen slechts in beperkte mate weer. Bij sterk gecorreleerde kolommen of dynamische parameters (bijvoorbeeld door de gebruiker ingevuld) stoten ze op hun grenzen. Booleaanse velden of kolommen met een vrijwel gelijkmatige verdeling hebben zelden baat bij aanvullende statistieken. Te veel buckets en buitensporig onderhoud kunnen op hun beurt de beheer- en analysetijd verlengen. Daarom gebruik ik histogrammen doelgericht en controleer ik regelmatig de Effect op daadwerkelijke uitvoeringen.
Controle en update van de Optimizer
Ik controleer de Gebruik van histogrammen via ANALYZE TABLE en relevante optimizer-opties, zodat de planner de statistieken op een zinvolle manier kan gebruiken. In drukke systemen plan ik de update in rustige tijdsvensters of in batches na grotere loads. Voor en na vergelijk ik de uitvoer van EXPLAIN en EXPLAIN ANALYZE om gewijzigde join-volgordes, filterstappen en kostenmodellen te evalueren. Bij negatieve effecten reageer ik onmiddellijk en draai ik een statistiek terug. Voor verdere controle van de Optimalisatie-opties let ik erop dat afhankelijkheden met andere statistieken niet onopgemerkt tot onjuiste Veronderstellingen produceren.
Monitoring, bescherming tegen regressie en draaiboek
Ik bouw een lichtgewicht Playbook voor de productieve omgeving:
- Een basislijn vaststellen: vóór het doorvoeren van wijzigingen EXPLAIN ANALYZE uitvoeren, de looptijd, het aantal „rows examined“ en de handler-teller noteren.
- Histogram aanmaken/wijzigen: gericht op de filterkolommen, conservatieve buckets.
- Direct daarna meten: plan, geschatte versus werkelijke regels; een afwijking met een factor >10 is voor mij een waarschuwingssignaal.
- Fijnafstelling: buckets omhoog/omlaag; pas indien nodig de volgorde van de filters in de query aan.
- Zorg dat je een rollback achter de hand hebt: DROP HISTOGRAM, mochten de vertragingen toenemen.
- Automatisering: ANALYZE na ETL-loads of grotere DML-golven tijdens onderhoudsvensters.
Voor de oorzaakanalyse maak ik gebruik van Optimizer-traces en EXPLAIN ANALYZE, om te zien of de planner op basis van de histogrammen de juiste selectieve tabel „naar voren“ haalt. Voor A/B-tests leg ik bij wijze van test de volgorde van de joins vast (STRAIGHT_JOIN) of dwing ik het gebruik van bepaalde indexen af of verbied ik deze, om het effect van de statistiek geïsoleerd te kunnen beoordelen.
Op organisatorisch vlak heeft een korte Veranderingslogboek per tabel: kolom, aantal buckets, tijdstip, meetwaarden voor en na. Dit vergemakkelijkt latere correcties en voorkomt onduidelijke interacties.
Operationele aspecten: blokkeringen, kosten, overdraagbaarheid
ANALYZE TABLE voert een Metadata-blokkering in de tabel, maar blokkeert de gebruikelijke lees- en schrijfbewerkingen niet permanent. Bij zeer grote tabellen houd ik rekening met voldoende tijd; het genereren van histogrammen werkt met steekproeven en is beperkt door het geheugen (trefwoord: intern werkgeheugen voor de berekening). De benodigde ruimte voor de statistiek zelf blijft beperkt: enkele tientallen tot een paar honderd kilobyte per kolom met 100–256 buckets is een realistische richtwaarde. In totaal maak ik toch een berekening, want veel kolommen maal veel tabellen leveren zichtbare metagegevens.
Op Logische dumps (mysqldump) worden histogrammen niet als gegevens meegenomen; na een restore maak ik ze gericht opnieuw aan. Bij een in-place-upgrade blijven ze behouden. Aan de gebruikerszijde heb ik voldoende rechten nodig voor ANALYZE TABLE op de betreffende objecten; in streng gereguleerde omgevingen integreer ik het onderhoud in onderhoudspijplijnen.
Wanneer histogrammen geen zin hebben
Ik sla het over Histogrammen voor kolommen die maar heel weinig waarden bevatten en toch al goed kunnen worden ingeschat. Ook wanneer een goede index al minimale sets met treffers dekt, levert een histogram zelden extra voordeel op. Gelijkmatige verdelingen vereisen geen uitgebreide fijnmazigheid. In zeer dynamische, schrijfintensieve systemen kan het onderhoud onnodige belasting veroorzaken als ik het te vaak start. In dergelijke situaties gebruik ik de Energie liever in indexstrategieën, query-ontwerp en caching.
Spiekbriefje in tabelvorm
Ik gebruik het volgende Overzicht voor snelle beslissingen: welk type histogram is geschikt, hoe stel ik buckets in en welke kosten zijn hieraan verbonden. De tabel dient als geheugensteuntje bij het beoordelen van problematische query's. Ik werk deze bij op basis van inzichten uit EXPLAIN ANALYZE en productiemetrics. Daarbij houd ik er rekening mee dat gegevensverdelingen veranderen en historische aannames achterhaald raken. Cruciaal blijft dat de Kwaliteit van het plan met behulp van echte metingen te bevestigen.
| Aspect | Aanbeveling | Voordeel | afweging | Voorbeeld |
|---|---|---|---|---|
| Type | Singleton bij een klein aantal dominante waarden | Nauwkeurige trefpercentages voor veelvoorkomende categorieën | Niet erg nuttig bij aaneengesloten gebieden | order_status |
| Type | Equi-Height bij scheve, continue gegevens | Betere schatting over het gehele waardenbereik | Meer metagegevens bij veel buckets | created_at, price |
| Emmers | Begin bij 100, en pas het daarna aan | Evenwichtige resolutie | Hogere analyse- en opslagbelasting bij 512–1024 | MET 100 EMMERS |
| Zorg | Na ingrijpende wijzigingen in de gegevens: ANALYZE | Huidige selectiviteiten | Onderhoudsperiode inplannen | ANALYZE TABLE … UPDATE HISTOGRAM |
| Controle | Controleren via COLUMN_STATISTICS | Transparantie en audit | JSON-interpretatie vereist | INFORMATION_SCHEMA.COLUMN_STATISTICS |
Plaats binnen het totale beeld van de tuning
Ik behandel Histogrammen als bouwsteen naast indexen, query-ontwerp, caching en hardwareparameters. Vaak zorgt een goed histogram ervoor dat de volgorde van de joins wordt aangepast, dat de I/O-belasting daalt en dat de responstijden constant blijven. Toch is het geen vervanging voor een goede indexstrategie en een efficiënt schema. Wie dieper ingaat op planningsbeslissingen, profiteert van Uitvoeringsplannen begrijpen en vergelijkt kostenmodellen met de werkelijke looptijden. Ik controleer regelmatig of de Werklasten nog wel overeenkomen met de statistieken of dat er aanpassingen nodig zijn.
Geavanceerde join-scenario’s
Histogrammen zijn vooral nuttig wanneer er meerdere tabellen met filters bij betrokken zijn. Voorbeeld:
SELECT o.id, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'DE'
AND o.status = 'canceled'
AND o.created_at >= NOW() - INTERVAL 30 DAY;
Zonder histogrammen kan de optimizer de selectiviteit van o.status=’canceled‘ onderschatten of het aandeel Duitse gebruikers overschatten. Met een histogram op u.land en o.status (eventueel ook op o.created_at) merkt de ontwerper meestal dat de combinatie uiterst selectief is. In de praktijk zie ik dan dat MySQL eerst de kleinere subset bepaalt (bijvoorbeeld via een index op users(country) of orders(status, created_at)) en pas daarna de join uitvoert – in plaats van de grote tabel te scannen. Dat bespaart I/O, bufferruimte en CPU-capaciteit en stabiliseert de latentie, ook onder belasting.
Omdat histogrammen alleen in één kolom blijven indexstrategieën belangrijk: een samengestelde index op (status, created_at) kan de range-scan verder versnellen. Het histogram zorgt er hier vooral voor dat de optimizer deze Strategie over het algemeen als voordelig beschouwt.
Samenvatting voor de praktijk
Ik stel MySQL-histogrammen als de optimizer er met standaardstatistieken naast zit en scheve verdelingen verkeerde plannen opleveren. Met ANALYZE TABLE bouw ik statistieken op, werk ik ze bij en verwijder ik ze gericht voor de kolommen die in filters en joins de boventoon voeren. De keuze tussen Singleton en Equi-Height maak ik op basis van de gegevens; het aantal buckets kalibreer ik aan de hand van metingen. Met EXPLAIN ANALYZE controleer ik of de volgorde van joins, de posities van filters en scans zoals gewenst veranderen. Zo bereik ik met weinig Overhead aanzienlijk snellere zoekopdrachten – vaak zonder extra indexen.
