...
logotyp för webbhotell

MariaDB:s frågeoptimerare förklarad inifrån: grunder, strategier och praktisk tillämpning

Jag förklarar MariaDB-optimeraren Från praktiken: hur han skapar planer, beräknar kostnader och varför han ibland har fel. Så här läser du SQL-exekveringsplanen på ett målinriktat sätt, använder index på ett meningsfullt sätt och styr optimeraren med fakta istället för magkänsla.

Centrala punkter

Till att börja med sammanfattar jag kort de viktigaste delarna, så att du lättare kan sätta in de följande avsnitten i sitt sammanhang och Översikt behåller.

  • Faser: Parsning, förberedelse, optimering och utförande utgör livscykeln för varje sökning.
  • Kostnadsmodell: Tidsbaserade värden i mikrosekunder styr indexval, genomsökningar och sammanfogningsordning.
  • Statistik: Kardinalitet och histogram avgör hur selektiviteten uppskattas.
  • Öppenhet: EXPLAIN, EXPLAIN ANALYZE och Optimizer Trace öppnar den svarta lådan.
  • Tuning: Index, omskrivning av frågor, ANALYZE TABLE och kostnadsparametrar ger snabbare prestanda.

En frågas livscykel i MariaDB

Innan en plan utarbetas genomgår en fråga fyra steg, som jag noggrant granskar i det dagliga arbetet för att Orsaker för att hitta orsaker till långsamhet. Vid parsningen omvandlar MariaDB SQL till en intern struktur; här upptäcks syntaxfel. Under förberedelsen kontrollerar motorn tabeller, kolumner och potentiella index samt utför enkla omformuleringar. Därefter följer optimeringen, där möjliga planer beräknas och utvärderas med hjälp av en kostnadsmodell. Vid exekveringen genomför servern den valda planen steg för steg: läsning, sammanfogning, filtrering, returnering.

Jag delar upp analysfelen tydligt efter fas, eftersom diagnoserna då ger snabbare resultat och Åtgärder verka målinriktat. Prestandaproblem har oftast sin grund i optimeringen: felaktiga uppskattningar, saknade index eller ogynnsamma ordningar för sammanfogningar. Parsningsfel är triviala, men förberedelsefasen kan redan innehålla finesser som upplösning av vyer eller omformuleringar av underfrågor. Under exekveringen blir ineffektiviteterna sedan uppenbara om man tidigare valt en fullständig genomsökning. Därför inleder jag varje utredning med en strukturerad genomgång av alla fyra stegen.

Hur optimeraren fattar beslut internt

MariaDB arbetar kostnadsbaserat och utvärderar alternativa lösningar genom en Kostnadsfunktion. För varje variant beräknar servern antalet lästa rader, selektiviteten hos WHERE/ON, åtkomsttyper som tabellskanning, indexskanning och intervallskanning samt tidsåtgången för enskilda operationer. Internt skiljer servern mellan join_preparation och join_optimization. I join_preparation utförs omskrivningar av frågor, förenklingar av villkor, omformuleringar av underfrågor och upplösningar av vyer. I join_optimization beräknas join-ordningar, indexkandidater kontrolleras via ref_optimizer_key_uses, rader uppskattas via intervallskanningar och villkor tilldelas specifika tabeller så tidigt som möjligt.

Denna mekanism förklarar varför ett litet filter på fel ställe kan orsaka dyra Konsekvenser har. Om ”attaching_conditions_to_tables” sker sent drar planen med sig onödigt många rader genom join-operationer. Om statistiken är föråldrad blir ”rows_estimation” och ”Selectivity” felaktiga; optimeraren väljer då tillgångsvägar som verkar fördelaktiga men som i själva verket är långsamma. Det är just dessa justeringsmöjligheter jag fokuserar på: bättre statistik, tydligare predikat, välordnade sammansatta index. Därefter förändras valet av plan ofta märkbart.

Prismodell från och med MariaDB 11.0

Aktuella versioner bedömer arbetet inte längre grovt utifrån vikter, utan med mikrosekunder för specifika lagringsoperationer. Parametrar som optimizer_disk_read_cost, optimizer_disk_read_ratio och optimizer_where_cost gör att modellen bättre återspeglar verkliga körtider. På så sätt jämför optimeraren index-range-scan med fullständig skanning utifrån verkliga tidsantaganden. LAST_QUERY_COST visar den uppskattade totalkostnaden och stämmer ofta betydligt bättre överens med verkligheten än tidigare. För dataintensiva system ger denna finare uppdelning omedelbara fördelar.

Jag kalibrerar modellen noggrant när hårdvaruegenskaper strider mot standardantagandena och därmed Planval förvränga. NVMe-SSD:er, distribuerat lagringsutrymme eller särskilda cacher kan märkbart påverka diskförhållandet och lästiderna. Små justeringar av optimizer_costs gör att MariaDB prioriterar lämpliga sökvägar. Jag dokumenterar varje ändring och kontrollerar därefter EXPLAIN ANALYZE för att mäta effekten. Utan mätningar förblir optimeringen ett lotteri.

Selektivitet, statistik och histogram

Bra uppskattningar börjar med noggranna kardinalitet och tillförlitlig selektivitet. MariaDB för statistik över olika värden per kolumn och kan valfritt använda histogram för fördelningar. Särskilt ojämnt fördelade data – hotspots, Zipf-fördelningar, säsongsmönster – drar nytta av histogram. Efter stora dataändringar kör jag ANALYZE TABLE så att optimeringen återigen baseras på aktuella fakta. Den som glömmer detta riskerar fullskanningar som objektivt sett är felaktiga.

Jag planerar att köra ANALYZE som ett återkommande jobb, anpassat efter Förändringar i datavolym och på kritiska tabeller. Vid kraftigt skeva kolumnfördelningar hjälper histogram till att på ett realistiskt sätt fånga selektiviteten hos enstaka värden. Detta minskar risken för felbedömningar vid intervallsökningar och sammanslagningsstrategier. I kombination med lämpliga sammansatta index förbättras träffnoggrannheten avsevärt. Resultat: kortare körtider och mindre I/O.

EXPLAIN och läsa exekveringsplaner

För att synliggöra beslut använder jag EXPLAIN, EXPLAIN EXTENDED och FORMAT=JSON. De klassiska kolumnerna ger en snabb översikt: id, select_type, table, type, possible_keys, key, key_len, ref, rows och eventuellt filtered. Ett type=ALL indikerar en fullständig genomsökning, vilket sällan är önskvärt. FORMAT=JSON visar i detalj hur villkor har flyttats och vilka sökvägar optimeraren har utvärderat. I samband med webbhotell rekommenderar jag guiden till Körningsplaner inom webbhotell, för att koppla samman planeringsinformation med effekter på infrastrukturen.

För att snabbt kunna tolka resultaten använder jag en liten tabell som kortfattat sammanställer typiska värden och därmed Felaktiga tolkningar förhindras.

EXPLAIN-fält Typiskt värde Betydelse i praktiken
typ ALL, range, ref, eq_ref, const Ju längre till höger, desto mer selektivt; ALL indikerar fullskanning.
possible_keys Indexlista Index som teoretiskt sett passar; om det saknas kandidater här, saknas det struktur.
nyckel Indexnamn Index som faktiskt används; tomt fält innebär att indexet inte används.
rader Antal Uppskattat antal lästa rader; avviker kraftigt från verkligheten = dålig statistik.
filtrerad Procent Hur mycket som släpps igenom efter filtret; en låg nivå är ofta bra.

Varför optimeringsverktyget ibland har fel

Ingen kostnadsmodell passar alla situationer, därför justerar jag Misstag målmedvetet. Föråldrad statistik leder till felaktiga uppskattningar av rader och olämpliga sammanfogningsordningar. Felaktigt uppbyggda sammansatta index förhindrar indexanvändning vid filter med flera kolumner. Mycket invecklade underfrågor försvårar effektiva omskrivningar och blockerar materialisering. Saknade eller missvisande filter tvingar motorn att flytta många rader innan användbara predikat träder i kraft.

Jag kontrollerar först om frågeställningen uppfyller Index som verkligen fungerar: vänsterprefixregel, lämplig sorteringsordning, undvikande av funktioner på kolumner i WHERE. Därefter tittar jag i EXPLAIN ANALYZE för att se om verkligheten stöder uppskattningen. Om inte, följer ANALYZE TABLE och vid behov en omskrivning. Först i sista hand använder jag FORCE INDEX eller hinting, eftersom det kan begränsa framtida optimeringar.

Använda Optimizer Trace på ett målinriktat sätt

Om EXPLAIN inte räcker till aktiverar jag Optimizer Trace och följer Beslut i JSON-loggen. Där kan jag se vilka planer som har övervägts, förkastats eller godkänts. Jag förstår varför ett villkor träder i kraft för sent eller varför ett index inte kom med i urvalet. Loggen visar också hur villkoren har omordnats. Denna översikt fördjupar förståelsen och ger konkreta verktyg för nästa optimering.

Jag sparar relevanta delar av spårningen tillsammans med frågehash och Parametrarvärdera. På så sätt kan jag senare jämföra vilken ändring som gav vilken effekt. Dokumentationen för MariaDB Server och olika föredrag inom ekosystemet beskriver fälten ingående (källa: MariaDB Server-dokumentation om Query Optimizer och Optimizer Trace). Med det här verktyget hittar jag felaktiga antaganden snabbare än med trial-and-error. Jag sparar framför allt tid vid komplexa sammanfogningar.

Praktisk guide: Databasoptimering steg för steg

Jag inleder varje optimering med en tydlig Mätning. Jag identifierar problem genom övervakning och det Långsam frågelogg. Därefter jämför jag EXPLAIN med EXPLAIN ANALYZE för att ställa plan och verklighet mot varandra. Jag anpassar indexstrategin efter WHERE, JOIN och ORDER BY; sammansatta index riktar jag in mot de vanligaste åtkomstpunkterna. Jag använder FORCE INDEX endast om optimeraren väljer fel kandidat trots korrekta statistikuppgifter.

Varje steg innefattar skötsel av Statistik: ANALYZE TABLE på tabeller med hög aktivitet, histogram för skeva fördelningar. Jag förenklar onödiga underfrågor, materialiserar delresultat vid behov och rensar bort gamla tillfälliga lösningar. Vid användning av specialhårdvara kontrollerar jag optimizer_costs för att säkerställa att mikrosekundmodellen stämmer. Jag dokumenterar varje ändring med före- och eftervärden så att effekten förblir spårbar på lång sikt.

Vanliga problem med optimeringsverktyg och lösningar

Om EXPLAIN visar type=ALL trots att possible_keys är ifyllt, tittar jag först på Selektivitet. Ofta stämmer inte kolumnordningen i det sammansatta indexet, eller så hindrar en funktion att indexet kan användas. Då byter jag ordningen, tar bort störande funktioner eller delar upp predikat. Om join-ordningen är felaktig kontrollerar jag om det går att filtrera tidigt, till exempel genom att flytta fram den mer selektiva tabellen. Subfrågor omvandlar jag, där det är lämpligt, till join-operationer eller TEMPORARY-tabeller.

Jag känner igen felaktiga beslut även när de avviker kraftigt från rader mellan plan och verklighet. Då kan kommandot ANALYZE TABLE eller ett histogram för den berörda kolumnen vara till hjälp. Om inte ens korrekta statistikvärden leder till önskat resultat överväger jag att använda explicita hintar. Innan dess säkerställer jag kontrollmätningar och mätvärden, så att senare versioner av optimeraren inte hämmas av lagrade inställningar. Disciplin när det gäller dokumentation lönar sig här.

Webbhotellssammanhang och driftsaspekter

Sökningens kvalitet och infrastrukturen måste stämma överens, annars går applikationen till spillo Potentiell. Snabba SSD-enheter, konsekventa cacher och en ren konfiguration utgör grunden för att optimeringsverktyget ska kunna fatta bra beslut. Hög trafik tål inga fullständiga genomsökningar; några få dåliga sökfrågor kan bromsa upp hela system. För MySQL/MariaDB-miljöer i produktiv drift ger praktiska tips som MySQL-optimeraren Användbara tankeställare om kombinationen av plan och plattform. Den som tar hänsyn till denna nivå kan förebygga flaskhalsar innan de eskalerar.

Jag kopplar alltid plananalysen till nyckeltal för I/O, latens och parallellitet. Om värdena inte stämmer överens med den antagna kostnadsmodellen kontrollerar jag parametrarna. Därefter tittar jag på buffertstorlekar, parallella arbetsbelastningar och fördelningen av hotsets. Med detta tillvägagångssätt lyckas jag driva sökningar och resurser på ett harmoniskt sätt och hålla toppbelastningarna under kontroll.

Join- och åtkomstvägar i praktiken

Jag reder ut många missförstånd genom att Åtkomsttyper väger noggrant mot varandra. Ett intervall- eller ref-Åtkomst fungerar nästan alltid ALL. Vid logiska kopplingar på unika nycklar (eq_ref) är planerna särskilt stabila. Jag kontrollerar dessutom om en Täckningsindex som helt täcker frågan: Om alla nödvändiga kolumner finns i indexet sparar MariaDB kostsamma tabellåtkomster. Nedpressning i indexerat tillstånd (ICP) hjälper till att redan i indexet kontrollera ytterligare WHERE-villkor – detta minskar antalet returnerade rader och I/O.

Om Indexsammanslagning MariaDB kan kombinera flera index (skärningsmängd/förening). Detta är användbart vid OR-predikat eller flera selektiva villkor, men ofta långsammare än ett välvalt sammansatt index. Jag utvärderar dessutom MRR (läsning över flera intervall) och BKA (Batched Key Access). MRR sorterar primärnycklar som ska läsas för att jämna ut slumpmässig I/O; BKA sammanför join-uppslag och ger störst vinst vid join-operationer utan överlappning. I praktiken testar jag BKA/MRR via optimizer_switch och kontrollerar med EXPLAIN ANALYZE om I/O-mönstren minskar. Om MariaDB däremot använder Block med inbäddade slingor (BNL) lönar det oftast att använda en större join-buffert (join_buffer_size) – eller en omskrivning som möjliggör riktiga index-joins.

-- Exempel: Sammansatt index för join + filter + sortering
CREATE INDEX ix_orders_cust_status_created
  ON orders (customer_id, status, created_at);

-- Typisk åtkomst
SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'open' AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;

Med ovanstående index kan optimeraren välja den mest selektiva ordningen, utvärdera filter tidigt och ofta utföra sorteringen utan ytterligare filsortering.

ORDER BY, GROUP BY, fil sortering och tillfälliga tabeller

Sortering och sammanställning tar tid. Jag ser till att ORDER BY och GRUPPERA EFTER kan köras i indexordningen. Det fungerar om prefixet och riktningen stämmer exakt. Annars träder en Filsortering med sorteringsbuffert (sort_buffer_size) och eventuellt en tillfällig tabell. Om resultatmängden innehåller breda TEXT-/BLOB-kolumner märks MariaDB snabbare på disk TEMP-tabeller (Aria). Jag förebygger detta genom att endast välja de kolumner som behövs, ladda stora fält först i slutet eller använda prefix med begränsad längd.

Vid aggregeringar använder jag, där det är möjligt, Skanning av lös index (t.ex. GROUP BY på den ledande indexdelen) och välj sammansatta index längs grupperingen. När mellanresultaten blir stora skalar en materialisering med lämpliga nycklar bättre än en enda megasammanfogning. Jag mäter regelbundet handlarmetriker och Created_tmp_*-räknare för att upptäcka hotspots i sortering och temporära tabeller.

Underfrågor, semi-join och materialisering

Många underfrågor kan omformuleras på ett effektivt sätt redan under förberedelsefasen. IN/EXISTS-konstruktioner kan användas som Semi-join köras, med strategier som materialisering eller LooseScan. Jag kontrollerar om optimeraren är en derived_merge kunde genomföra: Om en härledd tabell (eller en WITH-CTE) införlivas i den yttre planen, blir dess index direkt tillgängliga. Om detta inte lyckas hamnar underfrågan i en tillfällig tabell – jag ger den då, om möjligt, en nyckel (t.ex. genom SELECT DISTINCT/ORDER BY på nyckelkolumner), så att sammanfogningar inte hamnar i ingenmanland.

-- Exempel: EXISTS istället för IN och en sammanfogningsbar härledd tabell
SELECT o.id
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM payments p
  WHERE p.order_id = o.id AND p.state = 'captured'
);

-- Härledning med entydiga nycklar
WITH paid_orders AS (
  SELECT DISTINCT order_id
  FROM payments
  WHERE state = 'captured'
)
SELECT o.*
FROM orders o
JOIN paid_orders po ON po.order_id = o.id;

Jag kontrollerar med EXPLAIN FORMAT=JSON om materialiserad eller . beroende underfråga valdes och om det finns villkor (villkorsnedskjutning) vidta åtgärder i god tid.

Partitionering och beskärning

Partitionering ersätter inte index, men kan Datamängd per åtkomst minska drastiskt. Optimizern utför endast en korrekt beskärning om predikatet uppfyller Partitionsnyckel träffar entydigt och inte döljs av funktioner. Jag undviker därför uttryck som DATE(created_at) i WHERE-satsen för partitionerade tabeller och arbetar istället med intervallgränser. EXPLAIN visar vilka partitioner som läses; breda intervall tyder på dålig pruning.

För många små partitioner ökar planeringsbördan. Jag väljer därför en lämplig granularitet (t.ex. månadsvis istället för dagligen), håller statistiken per partition uppdaterad (ANALYZE PARTITION) och kontrollerar om viktiga index finns lokalt i partitionerna. Vid migreringsprojekt tar jag hänsyn till påverkan på replikering och säkerhetskopiering – båda dessa faktorer påverkar hur aggressivt jag partitionerar.

Sargability och omskrivningsmönster

Den enklaste metoden är fortfarande Sargability – Villkor som gör det möjligt att använda index. Jag undviker funktioner på kolumner i WHERE-satsen, reducerar konstanterna till kolumnnivå och delar upp OR-villkor vid behov i UNION ALL. För LIKE-sökningar utan inledande ankare ("%foo") är ett BTREE-index inte lämpligt; här planerar jag att använda fulltextsökning eller en lämplig söktjänst. Vid beräkningar använder jag indexerade genererade kolumner, så att optimeraren kan återfinna logiken i indexet.

-- Anti-mönster: Funktion på kolumn
WHERE DATE(created_at) = '2026-08-01'
-- Bättre: Intervall baserat på råvärde
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02'

-- Anti-mönster: OR förhindrar indexering
WHERE status = 'open' OR customer_id = 42
-- Bättre: två sökningar med UNION ALL och varsitt eget index
(SELECT ... WHERE status = 'open')
UNION ALL
(SELECT ... WHERE customer_id = 42');

När det gäller sammansatta index anser jag att vänsterprefixregeln Följ detta strikt, ordna kolumnerna efter selektivitet och efter den sortering som kommer att behövas senare. Om jag behöver en ORDER BY i fallande ordning tar jag hänsyn till detta i indexets uppbyggnad – på så sätt slipper jag fil-sorteringen.

Optimeringsknapp och finjustering av kostnader

Innan jag börjar granska frågorna kontrollerar jag optimizer_switch och minnesbuffert. Funktioner som mrr, batched_key_access, index_merge, semijoin, derived_merge eller . condition_pushdown_for_derived kan anpassas per session. Jag aktiverar kandidater specifikt för en testsession, mäter med EXPLAIN ANALYZE och återställer om effekten uteblir. Join-vägen drar nytta av tillräcklig join_buffer_size; stora sorter av sortera_buffer_storlek. Samtidigt håller jag koll på buffertarna i förhållande till samtidigheten, så att servern inte hamnar i swap-läge under parallell belastning.

När det gäller kostnaderna justerar jag, om det behövs, de redan nämnda optimeringskostnader i mikrosekunder. Min riktlinje: små, reversibla steg med dokumenterade mätpunkter. Jag använder LAST_QUERY_COST för att kontrollera rimligheten och upprepa mätningarna med realistiska parametervärden, eftersom planer i hög grad kan bero på konkreta värden.

Planstabilitet, regressioner och teamets arbetsflöde

Även en bra plan kan påverkas av datamängdens ökning eller versionsbyten tippa. Därför samlar jag in information om planeringen: query-hashar, EXPLAIN-JSON, utdrag ur optimeringsspårningen och körtider för EXPLAIN ANALYZE. Ändringar av index och omskrivningar sker hos mig som pull-förfrågningar med före- och efterdokumentation. I CI/CD-miljöer testar jag kritiska frågor automatiskt mot representativa dataställningar. På så sätt upptäcker jag Regressionsplaner tidigt.

I känsliga fall anser jag att Tips (FORCE INDEX, STRAIGHT_JOIN, optimizer_switch per fråga) finns som sista utväg, men använd dem sparsamt och med en tidsbegränsning. Det är bättre att åtgärda orsakerna – statistik, index, formulering. I team ser en lättviktig vägledning för skalbarhet, indexdesign och mätdisciplin till att nya funktioner inte obemärkt inför prestandaproblem.

Kort sammanfattning: Från plan till resultat

Vem kan använda Planera förstår och styr prestandan. Faserna Parsing, Preparing, Optimizing och Executing visar var tid går förlorad. Den tidsbaserade kostnadsmodellen från version 11.0 och välunderhållna statistiska uppgifter och histogram gör uppskattningarna tillförlitliga. EXPLAIN, EXPLAIN ANALYZE och Optimizer Trace ger insyn, vilket jag omvandlar till konkreta åtgärder. Med en väl genomtänkt indexstrategi, tydlig utformning av frågor och lämplig infrastruktur levererar MariaDB-frågor genomgående snabba svar.

Aktuella artiklar