...
webhosting-logo

MySQL-histogrammer – Bedre forespørgselsplaner uden indeks

MySQL-histogrammer giver optimeringsmodulet reelle fordelingsdata, så den kan estimere selektiviteter korrekt og udarbejde hurtigere forespørgselsplaner – ofte endda uden et ekstra indeks. Jeg viser, hvordan jeg opretter og kontrollerer histogrammer i MySQL 8+ med ANALYZE TABLE og bruger dem til at træffe bedre beslutninger ved sammenføjninger, filtreringer og scanninger.

Centrale punkter

Kort fokus: De følgende punkter viser, hvad jeg lægger særlig vægt på, når jeg bruger histogrammer.

  • Selektivitet I stedet for mavefornemmelse: mere realistiske skøn over kardinalitet
  • Uden indeks hurtigere: bedre valg af plan ved skæve fordelinger
  • typer Forstå: Målrettet brug af Singleton og Equi-Height
  • Spande skat: Afvejning af afvikling mod omkostninger til metadata
  • Pleje I fokus: Opdater, kontroller og slet om nødvendigt

Hvorfor histogrammer uden indeks virker

Jeg bruger Histogrammer, fordi optimeringsmodulet ellers ofte antager en jævn fordeling og dermed vælger dårlige planer. Et histogram viser Værdifordeling den nærmer sig en kolonne og leverer dermed realistiske selektivitetsestimater for prædikater som =, >, BETWEEN, IN eller IS NULL. Optimeringsmodulet beslutter derefter, om en indeks-range-scanning, en tabelscanning eller en sammenføjningsstrategi med indlejrede sløjfer er mest fordelagtig. Hvis en betingelse f.eks. kun rammer 0,1 % af rækkerne, foretrækker jeg en målrettet adgang frem for en bred scanning. Hvis et filter derimod dækker næsten alle rækker, undgår jeg dyre indeksadgange, der ikke giver nogen fordel, og øger dermed Effektivitet hver plan.

Histogramtyper i MySQL 8.0

Jeg skelner mellem to typer: Singleton og Equi-Height. Singleton-histogrammer samler hyppigt forekommende enkeltværdier i separate kategorier – ideelt til kolonner med få dominerende kategorier som „aktiv“, „inaktiv“ eller „arkiveret“. Equi-Height-histogrammer opdeler værdiområdet på en sådan måde, at hver kategori indeholder omtrent det samme antal Linjer indeholder; dette egner sig til kontinuerlige eller uregelmæssige fordelinger såsom priser, tidsstempler eller „hullet“ ID-intervaller. Begge varianter giver optimeringsværktøjet mere præcise hitprocenter for filtre. Jeg vælger altid typen ud fra dataegenskaberne, ikke ud fra personlig præference.

Tekniske grundlag: Styring af typevalg i MySQL

MySQL bestemmer den konkrete Histogramvariant automatisk på baggrund af datadistributionen. I praksis betyder det: Hvis antallet af forskellige værdier (NDV) er lille nok i forhold til antallet af buckets, opstår der reelt et singleton-histogram; ellers genereres der et equi-height-histogram. Jeg „vælger“ derfor typen indirekte, ved at fastlægge den relevante kolonne og et passende antal kategorier. For kolonner med meget få, men stærkt dominerende kategorier indstiller jeg bevidst få buckets for at opnå singleton-lignende præcision for disse værdier. Ved fint spredte, kontinuerlige data øger jeg antallet af buckets trinvist, indtil EXPLAIN viser den ønskede Selektivitet afspejler.

Vigtigt: Histogrammer er i én kolonne. Afhængigheder mellem kolonner (f.eks. status og country) kan ikke afbildes direkte. I sådanne tilfælde kan det være en hjælp at oprette et histogram for den mest selektive kolonne og tilpasse sammenkædningsrækkefølgen i overensstemmelse hermed.

Sådan vælger du de rigtige spande

MySQL bruger som standard 100 Spande, men tillader 1 til 1024 via WITH N BUCKETS. Flere buckets øger opløsningen, men medfører også en stigning i metadata og analyseindsatsen. Jeg starter som regel konservativt, måler effekten på EXPLAIN og øger antallet gradvist, hvis planen fortsat virker uhensigtsmæssig. Ved stærkt koncentrerede værdier (f.eks. 90 % i én status) er få buckets ofte tilstrækkelige; ved spredte priser eller tidsstempler er det en fordel med flere buckets. Målet er en fornuftig Granularitet, hvilket har reduceret fejlvurderingerne mærkbart uden at øge den administrative byrde unødigt.

Praksis: Workflow med ANALYZE TABLE

Jeg følger en klar Arbejdsgang: Først identificerer jeg kolonner, der ofte forekommer i WHERE- eller JOIN-betingelser og som tydeligvis har skæve fordelinger. Derefter genererer jeg et histogram med ANALYZE TABLE tbl UPDATE HISTOGRAM ON col WITH N BUCKETS; og kontrollerer det via INFORMATION_SCHEMA.COLUMN_STATISTICS. Efter dataflytninger opdaterer jeg igen med ANALYZE TABLE. Hvis en statistik ikke passer, fjerner jeg den med ANALYZE TABLE tbl DROP HISTOGRAM ON col;. For at vurdere planens effekt læser jeg Fortolk EXPLAIN ANALYZE og sammenligning af skøn med de faktiske tal Linjer fra.

Konkrete ordrer og kontrol

Jeg arbejder på en reproducerbar måde med få, klare trin og tjekker de genererede JSON-statistikker.

-- Oprette histogrammer på enkelte kolonner
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS;
ANALYZE TABLE orders UPDATE HISTOGRAM ON created_at WITH 128 BUCKETS;

-- Flere kolonner i én kørsel med samme antal buckets
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, payment_method WITH 64 BUCKETS;

-- Målrettet sletning af histogrammer
ANALYZE TABLE orders DROP HISTOGRAM ON status;

-- Visuel gennemgang af statistikken
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');

Jeg vurderer effekten direkte med EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE status = 'canceled'
  AND created_at >= NOW() - INTERVAL 7 DAY;

Bliver skønnet bedre rækker Hvis der er en mærkbar forskel, og planen f.eks. skifter fra en fuld scanning til en indeks-range-scanning eller ændrer rækkefølgen af sammenkædninger, har foranstaltningen været en succes. Hvis afvigelsen fortsat er stor, øger eller reducerer jeg antallet af buckets og sammenligner igen.

Eksempel: Ordrestatus og sjældne værdier

I en ordretabel er status „completed“ ofte den mest udbredte, mens „pending“ forekommer med middelhyppighed og „canceled“ er meget sjælden; denne Ubalance fører uden et histogram let til forkerte selektiviteter. Hvis en API forespørger på „canceled“, kan optimeringsmodulet fejlagtigt vælge en fuld tabelscanning, selvom en præcis indeksadgang er tilstrækkelig. Med et singleton-histogram genkender MySQL, at „canceled“ kun udgør en ubetydelig andel, og skifter til en indeks-range-scan eller optimerer sammenføjningsrækkefølgen. På den måde falder ventetiden, og jeg behøver ikke et ekstra indeks for hver Variant i et filter. I dashboards med strenge SLO’er giver denne justering ofte mærkbare fordele i reaktionstiden.

Tidsserier og tidsstempler

Når det gælder tidsserier, er der mange Adgange baseret på nye data; ældre tidsvinduer forbliver som regel uaktiverede. Et Equi-Height-histogram baseret på created_at eller updated_at skelner mellem tidsintervaller med høj aktivitet og sjældent anvendte tidsintervaller. Optimizeren vurderer derefter korrekt, om en range-scan er hensigtsmæssig, eller om en table-scan fører hurtigere til målet. Især ved partielle tidsfiltre på store tabeller bemærker jeg tydelige ændringer i udførelsesplanen og lavere I/O-omkostninger. Jeg anser Statistik her opdateres oftere, fordi fokus flytter sig i takt med den daglige drift.

Partitioner, datatyper og sorteringsregler

På partitionerede tabeller undersøger jeg datafordelingen på alle partitioner. Store udsving (f.eks. på månedsbasis) kan udjævne globale histogrammer. Hvis enkelte partitioner er ekstremt selektive eller ekstremt brede, tester jeg desuden med partition-pruning-filtre i WHERE-sætningen, om planens kvalitet alligevel er tilfredsstillende. Generelt sørger jeg for at formulere filtre på en sådan måde, at MySQL tidligt udelukke kan.

Histogrammer fungerer bedst med skalære, sammenlignelige datatyper (tal, dato-/tidsværdier, VARCHAR/CHAR med passende sorteringsregler). Ved LOB-/JSON-data så satser jeg hellere på Genererede kolonner med udtrukne, typebestemte værdier og tilføj om nødvendigt histogrammer eller indekser. For strenge bestemmer Sortering sammenligningslogikken; afhængigt af sorteringsordenen kan værdier være identiske (f.eks. store og små bogstaver). Jeg sørger for, at sorteringsordenen er i overensstemmelse med forespørgslerne for at opnå realistiske selektiviteter.

Grænser og fejltrin

Histogrammer viser især enkelte kolonner med Konstanter Godt; de kan dog kun i begrænset omfang afspejle afhængigheder mellem flere kolonner. Ved stærkt korrelerede kolonner eller dynamiske parametre (f.eks. udfyldt af applikationen) støder de på begrænsninger. Booleske felter eller kolonner med en næsten jævn fordeling drager sjældent fordel af yderligere statistik. For mange kategorier og overdreven vedligeholdelse kan til gengæld øge administrations- og analysetiden. Derfor bruger jeg histogrammer målrettet og kontrollerer regelmæssigt Effekt på virkelige udførelser.

Kontrol og opdatering af Optimizer

Jeg tjekker den Brug fra histogrammer via ANALYZE TABLE og relevante optimeringsindstillinger, så planlæggeren kan udnytte statistikkerne på en fornuftig måde. I travle systemer planlægger jeg opdateringen i rolige tidsvinduer eller i batcher efter større indlæsninger. Før og efter sammenligner jeg EXPLAIN- og EXPLAIN ANALYZE-udskrifter for at vurdere ændrede sammenkædningsrækkefølger, filtreringstrin og omkostningsmodeller. Ved negative effekter reagerer jeg straks og ruller en statistik tilbage. For yderligere styring af Optimeringsindstillinger sørger jeg for, at afhængigheder med andre statistikker ikke ubemærket fører til forkerte Antagelser producerer.

Overvågning, beskyttelse mod tilbagegang og playbook

Jeg bygger mig en letvægts Playbook til produktionsdrift:

  • Fastlægge baseline: Før ændringerne foretages, skal man køre EXPLAIN ANALYZE og notere køretid, „rows examined“ og handler-tælleren.
  • Opret/rediger histogram: målrettet mod filterkolonnerne, konservative kategorier.
  • Mål straks derefter: Plan, estimerede kontra faktiske linjer; en afvigelse på mere end 10 gange er for mig et advarselssignal.
  • Finjustering: Flyt buckets op/ned; juster om nødvendigt filterrækkefølgen i forespørgslen.
  • Vær klar til at foretage en rollback: DROP HISTOGRAM, hvis forsinkelserne stiger.
  • Automatisering: Kør ANALYZE i vedligeholdelsesvinduer efter ETL-indlæsninger eller større DML-bølger.

Til årsagsanalysen bruger jeg Optimizer-traces og EXPLAIN ANALYZE for at se, om planlæggeren på baggrund af histogrammerne trækker den rigtige selektive tabel „frem“. Til A/B-tests fastlægger jeg som test den rækkefølge, hvori sammenføjningerne skal foregå (STRAIGHT_JOIN), eller jeg tvinger/forhindrer brugen af enkelte indekser for at kunne vurdere statistikkens effekt isoleret.

Organisatorisk set har en kort Ændringslog pr. tabel: kolonne, antal buckets, tidspunkt, måleværdier før/efter. Det gør det lettere at foretage senere korrektioner og forhindrer uklare interaktioner.

Operationelle aspekter: Spærring, omkostninger, portabilitet

ANALYZE TABLE udfører en Metadatasperre i tabellen, men blokerer ikke de sædvanlige læse-/skriveoperationer permanent. Ved meget store tabeller afsætter jeg tilstrækkelig tid; generering af histogrammer foregår ved hjælp af stikprøver og er begrænset af hukommelsen (nøgleord: intern arbejdshukommelse til beregningen). Selve statistikkens pladsbehov forbliver moderat: Et par dusin til nogle få hundrede kilobyte pr. kolonne med 100–256 buckets er et realistisk vejledende tal. Samlet set regner jeg alligevel med det, for mange kolonner gange mange tabeller giver synlige metadata.

Med Logiske dumps (mysqldump) overføres histogrammer ikke som data; efter en gendannelse opretter jeg dem målrettet på ny. Ved en in-place-opgradering bevares de. På brugersiden har jeg brug for tilstrækkelige rettigheder til at udføre ANALYZE TABLE på de pågældende objekter; i strengt regulerede miljøer integrerer jeg vedligeholdelsen i vedligeholdelsespipelines.

Hvornår histogrammer ikke er til nogen nytte

Jeg sparer mig selv for Histogrammer på kolonner, der indeholder meget få værdier og alligevel kan estimeres godt. Også der, hvor et godt indeks allerede dækker minimale sæt af resultater, giver et histogram sjældent yderligere gevinst. Jævne fordelinger kræver ikke omfattende detaljeringsgrad. I meget dynamiske, skriveintensive systemer kan vedligeholdelsen skabe unødvendig belastning, hvis jeg udfører den for ofte. I sådanne situationer anvender jeg Energi hellere i indeksstrategier, query-design og caching.

Oversigt i tabelform

Jeg bruger følgende Oversigt til hurtige beslutninger: Hvilken histogramtype passer bedst, hvordan indstiller jeg buckets, og hvilke omkostninger er der forbundet med det. Tabellen fungerer som en huskeliste ved gennemgang af problematiske forespørgsler. Jeg opdaterer den på baggrund af erfaringer fra EXPLAIN ANALYZE og produktionsmetrikker. Her tager jeg højde for, at datadistributioner ændrer sig, og at historiske antagelser bliver forældede. Det afgørende er stadig at Planens kvalitet at bekræfte ved hjælp af reelle målinger.

Aspekt Anbefaling Fordel kompromis Eksempel
Type Singleton ved få, dominerende værdier Præcise rammeprocenter for almindelige kategorier Ikke særlig nyttigt ved sammenhængende områder ordrestatus
Type Equi-Height ved skæve, kontinuerlige data Bedre skøn langs værdiområdet Flere metadata ved mange buckets oprettet_dato, pris
Spande Start med 100, og juster derefter Afbalanceret opløsning Større analyse- og lagerbelastning ved 512–1024 MED 100 SPANDE
Pleje Efter større ændringer i dataene: ANALYZE Aktuelle selektiviteter Planlægge vedligeholdelsesvindue ANALYZE TABLE … UPDATE HISTOGRAM
Kontrol Kontroller via COLUMN_STATISTICS Gennemsigtighed og revision JSON-fortolkning påkrævet INFORMATION_SCHEMA.COLUMN_STATISTICS

Indpasning i det samlede billede af tuning

Jeg behandler Histogrammer som en byggesten ved siden af indekser, forespørgselsdesign, caching og hardwareparametre. Ofte ændrer et godt histogram rækkefølgen af sammenføjninger, reducerer I/O og sikrer konstante svartider. Alligevel kan det ikke erstatte velgennemtænkte indeksstrategier og et effektivt skema. Den, der ser nærmere på planlægningsbeslutninger, drager fordel af At forstå udførelsesplaner og sammenligner omkostningsmodeller med de faktiske løbetider. Jeg kontrollerer regelmæssigt, om Arbejdsbyrder om de stadig stemmer overens med statistikkerne, eller om der er behov for justeringer.

Avancerede sammenføjningsscenarier

Histogrammer er især nyttige, når der er flere tabeller med filtre involveret. Eksempel:

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;

Uden histogrammer kan optimeringsværktøjet i visse tilfælde undervurdere selektiviteten af o.status=’canceled‘ eller overvurdere andelen af tyske brugere. Med et histogram på u.land og o.status (eventuelt også på o.created_at) indser planlæggeren som regel, at kombinationen er ekstremt selektiv. I praksis ser jeg så, at MySQL først bestemmer den mindre delmængde (f.eks. via indeks på users(country) eller orders(status, created_at)) og først derefter udfører sammenføjningen – i stedet for at scanne den store tabel. Det sparer I/O, bufferplads og CPU-ressourcer og stabiliserer latenstiden, selv under belastning.

Fordi histogrammer kun i én kolonne er, forbliver indeksstrategier vigtige: Et sammensat indeks på (status, created_at) kan yderligere fremskynde range-scanningen. Histogrammet sikrer her først og fremmest, at optimeringsmodulet denne Strategi overhovedet anser for at være billig.

Resumé til brug i praksis

Jeg sætter MySQL-Histogrammer, når optimeringsmodulet tager fejl med standardstatistikker, og skæve fordelinger genererer forkerte planer. Med ANALYZE TABLE opretter, opdaterer og fjerner jeg målrettet statistikker på de kolonner, der dominerer i filtre og sammenkædninger. Jeg vælger mellem Singleton og Equi-Height ud fra dataene, og jeg kalibrerer antallet af buckets ved hjælp af målinger. Med EXPLAIN ANALYZE kontrollerer jeg, om sammenkoblingsrækkefølger, filterpositioner og scanninger ændres som ønsket. På den måde opnår jeg med få Overhead Mærkbart hurtigere forespørgsler – ofte uden yderligere indekser.

Aktuelle artikler