Hoppa till innehåll 
MySQL-histogram ger optimeraren verkliga fördelningsdata så att den kan uppskatta selektiviteten korrekt och ta fram snabbare frågeplaner – ofta till och med utan ytterligare index. Jag visar hur jag skapar och kontrollerar histogram i MySQL 8+ med ANALYZE TABLE och använder dem för att fatta bättre beslut vid sammanfogningar, filtreringar och genomsökningar.
Centrala punkter
Kortfokus: Följande punkter visar vad jag lägger särskilt stor vikt vid när jag använder histogram.
- Selektivitet I stället för magkänsla: mer realistiska uppskattningar av kardinalitet
- Utan index snabbare: bättre val av plan vid skeva fördelningar
- typer Förstå: Att använda singleton och equi-height på ett målmedvetet sätt
- Hinkar skatter: Avväg upplösning mot kostnader för metadata
- Vård I fokus: Uppdatera, kontrollera, radera vid behov
Varför histogram utan index ger intryck
Jag använder Histogram, eftersom optimeraren annars ofta utgår från en jämn fördelning och därmed väljer dåliga planer. Ett histogram visar Värdefördelning den utgår från en kolumn och ger därmed realistiska selektivitetsuppskattningar för predikat som =, >, BETWEEN, IN eller IS NULL. Optimeraren avgör därefter om en index-range-scan, en tabellskanning eller en sammanfogningsstrategi med Nested Loops är mest fördelaktig. Om ett villkor till exempel endast träffar 0,1 % av raderna föredrar jag en målinriktad åtkomst istället för en bred skanning. Om ett filter däremot omfattar nästan alla rader avstår jag från kostsamma indexåtkomster som inte ger någon fördel och ökar därmed Effektivitet varje plan.
Typer av histogram i MySQL 8.0
Jag skiljer mellan två typer: Singleton och Equi-Height. Singleton-histogram sammanför vanligt förekommande enskilda värden i separata kategorier – perfekt för kolumner med få dominerande kategorier som „aktiv“, „inaktiv“ eller „arkiverad“. Equi-Height-histogram delar upp värdeintervallet så att varje bucket innehåller ungefär lika många Linjer innehåller; detta lämpar sig för kontinuerliga eller ojämna fördelningar, såsom priser, tidsstämplar eller „glesa“ ID-intervall. Båda varianterna ger optimeringsverktyget mer exakta träffprocent för filter. Jag väljer alltid typen utifrån dataegenskaperna, inte utifrån personlig preferens.
Tekniska grunder: Styra typvalet i MySQL
MySQL bestämmer den konkreta Histogramvariant automatiskt utifrån datadistributionen. I praktiken innebär det följande: Om antalet olika värden (NDV) är tillräckligt litet i förhållande till antalet bucketar, uppstår i praktiken ett singleton-histogram; i annat fall genereras ett equi-height-histogram. Jag „väljer“ därför typen indirekt, genom att ange lämplig kolumn och ett passande antal buckets. För kolumner med mycket få, men starkt dominerande kategorier, använder jag medvetet få buckets för att uppnå singleton-liknande precision för dessa värden. Vid fint spridda, kontinuerliga data ökar jag antalet buckets stegvis tills EXPLAIN ger den önskade Selektivitet återspeglar.
Viktigt: Histogram är enkolumnig. De kan inte direkt återge beroenden mellan kolumner (t.ex. status och country). I sådana fall kan det vara till hjälp att skapa ett histogram för den mest selektiva kolumnen och anpassa sammanfogningsordningen därefter.
Att välja rätt hinkar
MySQL använder som standard 100 Hinkar, men tillåter 1 till 1024 med WITH N BUCKETS. Fler buckets ökar upplösningen, men medför också större mängder metadata och ökad analysinsats. Jag brukar börja försiktigt, mäta effekten på EXPLAIN och öka stegvis om planen fortfarande verkar olämplig. Vid starkt koncentrerade värden (t.ex. 90 % i en status) räcker det ofta med få buckets; vid finfördelade priser eller tidsstämplar lönar det sig med fler buckets. Målet är en meningsfull Granularitet, vilket märkbart minskar antalet felbedömningar utan att onödigt öka den administrativa bördan.
Praktisk tillämpning: Arbetsflöde med ANALYZE TABLE
Jag följer en tydlig Arbetsflöde: Först identifierar jag kolumner som ofta förekommer i WHERE- eller JOIN-villkor och som uppvisar tydligt skeva fördelningar. Sedan skapar jag ett histogram med ANALYZE TABLE tbl UPDATE HISTOGRAM ON col WITH N BUCKETS; och kontrollerar det via INFORMATION_SCHEMA.COLUMN_STATISTICS. Efter dataförflyttningar uppdaterar jag på nytt med ANALYZE TABLE. Om en statistik inte stämmer tar jag bort den med ANALYZE TABLE tbl DROP HISTOGRAM ON col;. För att utvärdera planens effekt läser jag Tolka EXPLAIN ANALYZE och jämföra uppskattningar med faktiska siffror Linjer från.
Konkreta order och kontroll
Jag arbetar på ett reproducerbart sätt med få, tydliga steg och granskar den JSON-statistik som genereras.
-- Skapa histogram för enskilda kolumner
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS;
ANALYZE TABLE orders UPDATE HISTOGRAM ON created_at WITH 128 BUCKETS;
-- Flera kolumner i ett körningspass med samma antal bucketar
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, payment_method WITH 64 BUCKETS;
-- Ta bort histogram på ett målinriktat sätt
ANALYZE TABLE orders DROP HISTOGRAM ON status;
-- Visuell granskning av statistiken
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');
Jag utvärderar effekten direkt med EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE status = 'canceled'
AND created_at >= NOW() - INTERVAL 7 DAY;
Förbättras skattningen rader Om det märks en förändring och planen t.ex. byter från fullskanning till index-range-skanning eller ändrar ordningen på sammanfogningarna, har åtgärden varit framgångsrik. Om avvikelsen fortfarande är stor ökar eller minskar jag antalet buckets och jämför på nytt.
Exempel: Orderstatus och ovanliga värden
I en ordertabell dominerar ofta statusen „completed“, medan „pending“ förekommer ganska ofta och „canceled“ är mycket sällsynt; detta obalans utan histogram leder lätt till felaktiga selektiviteter. Om ett API söker efter „canceled“ kan optimeraren felaktigt välja en fullständig tabellgenomsökning, trots att en snäv indexåtkomst skulle räcka. Med ett singleton-histogram upptäcker MySQL att „canceled“ endast utgör en mycket liten andel och byter till index-range-scan eller optimerar sammanfogningsordningen. På så sätt minskar latensen, och jag behöver inget extra index för varje Variant i ett filter. I instrumentpaneler med strikta SLO:er ger denna justering ofta märkbara fördelar när det gäller reaktionstiden.
Tidsserier och tidsstämplar
När det gäller tidsserier finns det många Tillträden baserat på färska data; äldre tidsfönster förblir oftast inaktiva. Ett Equi-Height-histogram baserat på created_at eller updated_at skiljer tidsperioder med hög aktivitet från sådana som sällan används. Optimeraren bedömer då korrekt om en Range-Scan är lämplig eller om en Table-Scan leder snabbare till målet. Särskilt vid partiella tidsfilter på stora tabeller märker jag tydliga planändringar och lägre I/O-kostnader. Jag anser att Statistik här uppdateras oftare, eftersom tyngdpunkten förskjuts i takt med den löpande verksamheten.
Partitioner, datatyper och sorteringsregler
Jag undersöker datafördelningen i partitionerade tabeller över alla partitioner. Stora skillnader (t.ex. mellan månader) kan jämna ut globala histogram. Om enskilda partitioner är extremt selektiva eller extremt breda testar jag dessutom med partition-pruning-filter i WHERE-klausulen för att se om planeringskvaliteten ändå är tillräcklig. Generellt sett ser jag till att formulera filter så att MySQL kan hantera partitionerna tidigt utesluta kan.
Histogram fungerar bäst med skalära, jämförbara datatyper (tal, datum- och tidsvärden, VARCHAR/CHAR med lämplig sorteringsordning). Vid LOB-/JSON-data jag satsar hellre på Genererade kolumner med extraherade, typiserade värden och kompletterar dessa vid behov med histogram eller index. För strängar bestämmer Sortering Jämförelselogiken; beroende på sorteringsordning kan värden sammanfalla (t.ex. stor- och småbokstäver). Jag ser till att sorteringsordningen är konsekvent i frågorna för att få realistiska selektiviteter.
Gränser och misstag
Histogram visar framför allt enskilda kolumner med Konstanter Bra; de återger dock endast i begränsad utsträckning beroenden mellan flera kolumner. Vid starkt korrelerade kolumner eller dynamiska parametrar (t.ex. sådana som fylls i av användaren) når de sina gränser. Booleska fält eller kolumner med nästan jämn fördelning drar sällan nytta av ytterligare statistik. För många kategorier och överdriven underhåll kan i sin tur öka tiden för administration och analys. Jag använder därför histogram på ett målinriktat sätt och kontrollerar regelbundet Effekt på verkliga utföranden.
Kontroll och uppdatering av Optimizer
Jag kontrollerar den Användning från histogram via ANALYZE TABLE och relevanta optimeringsalternativ, så att planeraren kan utnyttja statistiken på ett meningsfullt sätt. I system med hög belastning planerar jag uppdateringen under lugna tidsfönster eller i batcher efter större datainläsningar. Före och efter jämför jag utdata från EXPLAIN och EXPLAIN ANALYZE för att utvärdera ändrade join-sekvenser, filtersteg och kostnadsmodeller. Vid negativa effekter reagerar jag omedelbart och återställer en statistik. För vidare styrning av Optimeringalternativ ser jag till att beroenden med andra statistiska uppgifter inte obemärkt leder till felaktiga Antaganden skapa.
Övervakning, skydd mot regression och handbok
Jag bygger en lätt Spelguide för drift:
- Fastställa basvärden: Innan ändringar görs, kör EXPLAIN ANALYZE och notera körtid, „rows examined“ samt handlarräknaren.
- Skapa/ändra histogram: riktat mot filterkolumnerna, konservativa intervall.
- Mät direkt efteråt: plan, beräknade kontra faktiska rader; en avvikelse med en faktor >10 är för mig en varningssignal.
- Finjustering: Flytta upp/ned buckets; justera vid behov filterordningen i frågan.
- Ha en återställning redo: DROP HISTOGRAM om fördröjningarna ökar.
- Automatisering: ANALYZE efter ETL-inläsningar eller större DML-vågor under underhållsfönstren.
För att analysera orsakerna använder jag Optimeringsspår och EXPLAIN ANALYZE för att se om planeraren, utifrån histogrammen, lyfter fram rätt selektiv tabell „i förgrunden“. För A/B-tester fastställer jag testvis sammanfogningsordningen (STRAIGHT_JOIN) eller tvingar fram/förhindrar enskilda index för att isolerat utvärdera effekten av statistiken.
Ur organisatorisk synvinkel har det visat sig vara en bra idé med en kort Förändringslogg Per tabell: kolumn, antal buckets, tidpunkt, mätvärden före/efter. Detta underlättar senare korrigeringar och förhindrar oklara interaktioner.
Operativa aspekter: spärrning, kostnader, portabilitet
ANALYZE TABLE tar en Metadataspärr i tabellen, men blockerar inte vanliga läs- och skrivoperationer permanent. För mycket stora tabeller planerar jag in tillräckligt med tid; genereringen av histogrammet bygger på stickprov och är minnesbegränsad (nyckelord: internt arbetsminne för beräkningen). Utrymmesbehovet för själva statistiken förblir måttligt: några dussin till några hundra kilobyte per kolumn med 100–256 buckets är en realistisk riktlinje. Totalt sett räknar jag ändå på det, eftersom många kolumner gånger många tabeller ger synliga metadata.
Med Logiska dumpningar (mysqldump) överförs inte histogrammen som data; efter en återställning skapar jag dem medvetet på nytt. Vid en in-place-uppgradering behålls de. På serversidan behöver jag tillräckliga behörigheter för ANALYZE TABLE på respektive objekt; i strängt reglerade miljöer integrerar jag underhållet i underhållspipelines.
När histogram inte är till någon nytta
Jag struntar i Histogram på kolumner som innehåller mycket få värden och som ändå kan uppskattas väl. Även där ett bra index redan täcker minimala träffmängder ger ett histogram sällan någon ytterligare nytta. Jämna fördelningar kräver ingen omfattande detaljnivå. I mycket dynamiska, skrivintensiva system kan underhållet skapa onödig belastning om jag utför det för ofta. I sådana situationer använder jag Energi hellre inom indexstrategier, utformning av frågor och cachelagring.
Översikt i tabellform
Jag använder följande Översikt för snabba beslut: Vilken typ av histogram passar, hur ställer jag in buckets och vilka kostnader uppstår. Tabellen fungerar som en minneshjälp vid granskning av problematiska frågor. Jag uppdaterar den utifrån lärdomar från EXPLAIN ANALYZE och produktionsstatistik. Då tar jag hänsyn till att datafördelningar förändras och att tidigare antaganden blir inaktuella. Det avgörande är fortfarande att Planens kvalitet att bekräfta med verkliga mätningar.
| Aspekt | Rekommendation | Förmån | avvägning | Exempel |
|---|---|---|---|---|
| Typ | Singleton vid ett fåtal dominerande värden | Exakta träffprocent för vanliga kategorier | Inte särskilt användbart vid sammanhängande områden | order_status |
| Typ | Equi-Height vid skeva, kontinuerliga data | Bättre skattning över hela värdeintervallet | Mer metadata vid många buckets | created_at, pris |
| Hinkar | Börja på 100, justera sedan | Balanserad upplösning | Högre analys- och lagringsbelastning vid 512–1024 | MED 100 SKOPOR |
| Vård | Efter större dataändringar: ANALYZE | Aktuella selektiviteter | Planera in underhållsfönster | ANALYZE TABLE … UPDATE HISTOGRAM |
| Kontroll | Kontrollera via COLUMN_STATISTICS | Öppenhet och revision | JSON-tolkning krävs | INFORMATION_SCHEMA.COLUMN_STATISTICS |
Plats i den övergripande bilden av tuning
Jag behandlar Histogram som en byggsten vid sidan av index, frågedesign, caching och hårdvaruparametrar. Ofta ändrar ett bra histogram ordningen på sammanfogningarna, minskar I/O och säkerställer konstanta svarstider. Trots detta ersätter jag inte väl genomtänkta indexstrategier eller ett effektivt schema med det. Den som granskar planeringsbesluten närmare drar nytta av Att förstå exekveringsplaner och jämför kostnadsmodeller med faktiska löptider. Jag kontrollerar regelbundet om Arbetsbelastning om de fortfarande stämmer överens med statistiken eller om det behövs justeringar.
Avancerade sammanfogningsscenarier
Histogram är särskilt användbara när flera tabeller med filter ingår. Exempel:
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;
Utan histogram kan optimeringsverktyget eventuellt underskatta selektiviteten för o.status=’canceled‘ eller överskatta andelen tyska användare. Med ett histogram på u.land och o.status (ev. även på o.created_at) inser planeraren oftast att kombinationen är extremt selektiv. I praktiken ser jag då att MySQL först bestämmer den mindre delmängden (t.ex. via index på users(country) eller orders(status, created_at)) och först därefter utför sammanfogningen – istället för att skanna den stora tabellen. Detta sparar I/O, buffertutrymme och CPU-resurser samt stabiliserar latensen även under hög belastning.
Eftersom histogram endast enkolumnig är, förblir indexstrategier viktiga: Ett sammansatt index på (status, created_at) kan ytterligare påskynda intervallsökningen. Histogrammet ser här framför allt till att optimeraren Strategi överhuvudtaget anser vara förmånligt.
Sammanfattning för praktiken
Jag ställer in MySQL-Histogram när optimeraren missbedömer situationen med standardstatistik och skeva fördelningar ger upphov till felaktiga planer. Med ANALYZE TABLE skapar, uppdaterar och tar jag bort statistik på de kolumner som dominerar i filter och sammanfogningar. Jag väljer mellan Singleton och Equi-Height utifrån data, och kalibrerar antalet bucket med hjälp av mätningar. Med EXPLAIN ANALYZE kontrollerar jag om join-sekvenser, filterpositioner och skanningar ändras som önskat. På så sätt uppnår jag med liten Overhead märkbart snabbare sökningar – ofta utan ytterligare index.
