den dårligt formulerede SQL-forespørgsler Dette er en af de hyppigste årsager til, at en applikation kører langsomt, når man arbejder med store relationsdatabaser som MySQL, PostgreSQL, SQL Server, Oracle eller DB2. Selvom vi nu har kraftfulde servere og elastiske clouds, vil ineffektive forespørgsler i sidste ende koste dig dyrt. højere infrastrukturomkostninger, højere latenstid og en dårligere brugeroplevelse.
Optimering af SQL-forespørgsler i store databaser går langt ud over blot at "tilføje et indeks, og det er det". Det involverer Forståelse af, hvordan forespørgselsoptimereren tænkerHvordan data lagres, hvilke adgangsmønstre din applikation bruger, og hvilke kombinerede teknikker giver dig mulighed for at reducere I/O-, CPU- og hukommelsesforbrug. I de følgende afsnit vil vi gennemgå, i detaljer og med eksempler, De mest effektive strategier til at få mest muligt ud af dine relationsdatabaser.
Hvad er SQL-forespørgselsoptimering egentlig, og hvorfor er det vigtigt?
Optimer en SQL-forespørgsel Det betyder, at den omskrives (og justeres i kontekst: indekser, statistik, design), så motoren returnerer det samme resultat, samtidig med at den bruger færre ressourcer og på kortere tid. SQL-syntaks tillader mange måder at udtrykke den samme ting på, men ikke alle udføres lige hurtigt, især når der er millioner af rækker eller komplekse joins.
Når en udvikler forstår, hvordan det fungerer forespørgselsplanlægger Med din engine (PostgreSQL, MySQL, SQL Server, Oracle, DB2 osv.) kan du skrive forespørgsler, der bedre udnytter indekser, reducerer unødvendige læsninger og minimerer dyre operationer såsom sortering, sekventielle scanninger eller gentagne korrelerede underforespørgsler.
Det er dog vigtigt at være klar over, at Forespørgselsoptimering er ikke den eneste præstationsfaktorSkemadesignet (normalisering, primære og fremmednøgler, datatyper), arkitekturen (replikaer, partitioner, cacher) og selve infrastrukturen har en betydelig indflydelse. Men selv med en ordentlig arkitektur kan en enkelt dårligt optimeret forespørgsel være et stort problem. brutal flaskehals.
Blandt fordelene ved at arbejde i konsultationer skiller følgende sig ud: samlet forbedring af præstationen (flere anmodninger håndteres på kortere tid), reduktion af cloud-omkostninger (mindre CPU og disk, mindre instansstørrelser) og en glattere brugeroplevelse ved at reducere ventetider i lister, søgninger og rapporter. Derudover er klare og velstrukturerede forespørgsler nemmere at vedligeholde og fejlfinde, noget der værdsættes meget, når projektet vokser.
I applikationer, der virkelig sigter mod at skalere, bliver kontinuerlig forespørgselsoptimering en tilbagevendende opgave: overvåge, detektere, måle, justere og genmåleDet er ikke en engangshandling, men en proces.
Praktisk eksempel: samme forespørgsel, meget forskellig ydeevne
For at bringe dine ideer ned på jorden, forestil dig et bord ordrer med mere end 20 millioner poster På en e-handelsside ønsker vi at hente en kundes gennemførte ordrer fra de sidste 30 dage, og uden megen overvejelse kunne vi skrive noget i retning af dette:
SELECT * FROM pedidos
WHERE cliente_id = 456
AND LOWER(estado) = 'completado'
AND fecha_creacion BETWEEN NOW() - INTERVAL '30 days' AND NOW();
Denne forespørgsel returnerer det, vi ønsker, men fra et ydeevnesynspunkt er det lidt af et rod: den bruger VÆLG *, anvender en funktion (LOWER) på en filterkolonne og kombinerer datoer med udtryk, der kan forstyrre brugen af indekser. Hvis der desuden ikke findes passende indekser på klient_id, status eller oprettelsesdato, vil motoren blive tvunget til at scanne en stor del af bordet.
De praktiske konsekvenser er tydelige: Mere data overført end nødvendigtMere arbejde med backend-mappning af ubrugte kolonner, en masse disklæsning og udførelsestid, der i meget store tabeller kan stige til flere sekunder, hvilket påvirker hele systemet, når det startes mange gange.
Det samme spørgsmål, formuleret mere intelligent, kunne se sådan ud:
SELECT id, fecha_creacion, total
FROM pedidos
WHERE cliente_id = 456
AND estado = 'Completado'
AND fecha_creacion >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY fecha_creacion DESC
LIMIT 100;
Her er vi kun at vælge de nødvendige kolonnerundgå funktioner på statuskolonnen, forenkle datobetingelsen og begrænse antallet af rækker. Med veldesignede indeks (f.eks. INDEX(cliente_id, fecha_creacion) og en om estado (hvis den har en høj kardinalitet), kan motoren bruge indeksscanninger og løse forespørgslen i millisekunder i stedet for sekunder.
Denne kontrast illustrerer en central idé: Det er ikke nok, at forespørgslen "virker"Du skal bekymre dig om, hvordan det kører, når tabellen ikke længere har hundredvis af rækker, men millioner.
Indekser: den vigtigste løftestang til at accelerere søgninger
masse Indekser er det mest effektive værktøj til at fremskynde forespørgsler i store databaser. I stedet for at gennemgå hele tabellen række for række (sekventiel scanning eller Seq Scan), bruger motoren hjælpestrukturer (normalt B-træer, R-træer eller hashes, afhængigt af datatypen og motoren), der tillader direkte hop til kandidatrækker.
I MySQL er de mest almindelige strukturer for eksempel træer B for typeindekser PRIMARY KEY, UNIQUE, INDEX y FULLTEXT, mens rumlige indekser bruger R-træer og tabeller i hukommelsen kan trække fra indekser baseret på hashHver enkelt er optimeret til et specifikt adgangsmønster.
Det handler dog ikke om at sætte et indeks på alting. Hvert yderligere indeks Det optager diskplads og forsinker indsættelser, opdateringer og sletninger.fordi motoren skal holde strukturen synkroniseret. Tricket er at finde balance mellem antal indeks og responstidmed fokus på kritiske læsespørgsmål.
Blandt de mest almindelige typer af indekser i relationelle motorer finder vi dem af primær nøgle (identificer hver række unikt og tillad ikke nullværdier), dem fra fremmednøgle (henvis til PK'en for en anden tabel), den unikke indekser (garanterer unikhed, men tillader null-tegn) og sammensatte indekser på flere kolonner, meget nyttigt ved filtrering eller sortering efter mere end ét felt ad gangen.
Der er også scenarier, hvor det er nyttigt at bruge indekser med gentagne værdier (for at fremskynde søgninger i ikke-unikke kolonner) eller fuldtekstindekser (FULLTEXT i MySQL, for eksempel) for at forbedre søgninger i lange tekstfelter. Siden MySQL 8.0.13 kan disse oprettes funktionelle indekserDet vil sige, på resultatet af et udtryk eller en funktion (for eksempel, YEAR(fecha_pago)), hvilket åbner døren for avancerede optimeringer.
Vi kan oprette indekser i MySQL med forskellige statements: CREATE INDEXog tilføjer dem senere; ALTER TABLEat ændre en eksisterende tabel; eller direkte i definitionen med CREATE TABLEI alle tre tilfælde er simple, sammensatte, unikke og præfiksindekser tilladt (kun de første N tegn i en VARCHAR) o FULLTEXT, afhængigt af hvilket design vi har brug for.
Anvendelsen af præfiksindekser Dette er nyttigt, når vi har lange strenge, men et relativt lille antal tegn er nok til at skelne stort set alle værdierne. På denne måde reducerer vi størrelsen på indekserne uden at miste for meget selektivitet, hvilket er meget nyttigt i kolonner som kundenavne, hvor vi f.eks. kan indeksere de første 25 tegn i stedet for hele feltet.
Vælg kun de kolonner, du har brug for
Misbrug VÆLG * Det er en af de mest almindelige dårlige vaner i SQL. Det er praktisk under udvikling, men i produktion bliver det en byrde: Hver ekstra kolonne betyder, at flere bytes rejser fra databasen op til din applikation, mere hukommelse på klienten og mere deserialiseringsarbejde.
Når en tabel indeholder store kolonner (BLOB'er, store JSON-filer, enorme tekstfiler, binære avatarer osv.), øger inkluderingen af dem unødvendigt I/O- og RAM-forbruget. Desuden giver begrænsning af antallet af kolonner i motorer som PostgreSQL bedre ydeevne. Kun indeksscanninghvor databasen reagerer fra indekset uden at gå til heapen, men dette virker kun, hvis alle de kolonner, du anmoder om, er i indekset.
Et klassisk eksempel: et bord users med kolonner som id, e-mail, adgangskode, avatar, oprettet_på, sidste_loginHvis du kaster SELECT * FROM users WHERE email = 'juan@example.com';Du får adgangskoden hash og binær avatar, selvom du kun vil vise e-mailadressen og den seneste login-dato. Det er meget bedre bare at bede om det. id, email, last_login.
Arbejd altid med eksplicitte kolonnelister Det gør dine forespørgsler tydeligere, beskytter dig mod skemaændringer (tilføjelse af en kolonne ødelægger ikke noget) og reducerer ressourceforbruget dramatisk i store tabeller eller paginerede lister, hvilket hjælper med at håndtere store mængder data.
JOIN'er, underforespørgsler og CTE'er: hvordan man strukturerer komplekse forespørgsler korrekt
den korrelerede underforespørgsler (Dem, der udføres én gang for hver række i den ydre forespørgsel) kan virke elegante på papiret, men i praksis bliver de en flaskehals i ydeevnen, efterhånden som tabellerne vokser. Hver række i hovedtabellen udløser en yderligere udførelse af underforespørgslen, hvilket resulterer i et astronomisk antal operationer.
Når det er muligt, er det at foretrække at omdanne disse underforespørgsler til velindekserede JOIN'er eller CTE'er (fælles tabeludtryk) der opdeler logikken i klare trin. Optimeringsværktøjet håndterer normalt en kombination af tabeller meget bedre end en rede af komplekse underforespørgsler.
For eksempel, for at få produkter sammen med deres kategorinavn, i stedet for at lave en underforespørgsel i SELECT Det er mere effektivt at bruge en JOIN mod kategoritabellen. Hvis join-kolonnerne er indekseret (f.eks. productos.categoria_id y categorias.id), kan motoren løse samlingen med meget lave omkostninger, selv på store borde.
den CTE'er (WITH ... AS (...)Disse er især nyttige til rapportering af forespørgsler, komplekse aggregeringer og trinvis logik. Selvom de ikke altid forbedrer ydeevnen i sig selv, hjælper de planlæggeren og forbedrer frem for alt læsbarheden, hvilket letter yderligere optimeringer, såsom tilføjelse af specifikke indeks eller materialisering af mellemresultater.
Paginering og LIMIT til at tæmme store mængder
I virkelige applikationer giver det næsten aldrig mening at returnere tusindvis af rækker på én gang fra et brugeroplevelsesperspektiv. En produktliste, ordrehistorik eller hændelseslog læses typisk side for side, så begræns antallet af returnerede rækker Det er et grundlæggende krav for klatring.
Den klassiske tilgang bruger LIMIT y OFFSET (f.eks. LIMIT 10 OFFSET 20 (for at gå til den "tredje" side). Det er nemt at implementere og forstå, men det har et alvorligt problem: motoren skal gennemløbe alle rækker før FORSKYDNINGEN på samme måde.selvom den kun returnerer de sidste 10. I meget store tabeller resulterer høje OFFSET-værdier i stadig dårligere svartider.
Når man arbejder med hundredtusindvis eller millioner af rækker, er det normalt bedre at Tastesæt-paginering eller søgebaseret pagineringI denne tilgang, i stedet for at fortælle databasen "spring 1000 rækker over", fortæller du den "returner de næste N poster startende fra denne sorterede nøgleværdi" ved hjælp af betingelser af typen WHERE fecha_creacion < <última_fecha_vista> med en ORDER BY konsekvent.
Denne teknik gør det muligt for motoren at udnytte et direkte indeks på den sorterede kolonne (for eksempel, fecha_creacion o id), hvilket undgår omkostningerne ved at gennemgå mellemliggende sider. Desuden gør det paginering stabil mod indsættelser eller sletninger mellem sider, noget som OFFSET ikke garanterer.
Til gengæld har nøglesæt-pagination den ulempe, at Det er ikke nemt at hoppe til side 37 Uden yderligere information, da den arbejder fremad fra en logisk markør (det sidste ID eller den seneste hentede dato). Derfor kombinerer mange systemer begge tilgange afhængigt af funktionelle behov.
Undgå funktioner i filtrerede kolonner og udnyt WHERE-klausulen godt
En meget almindelig kilde til ydeevnetab er påføring funktioner på kolonner, der deltager i filtreUdtryk som LOWER(nombre), DATE(fecha) o CAST(campo AS ...) inden for klausulen WHERE De forhindrer normalt optimeringsværktøjet i at bruge indekset for den pågældende kolonne.
I stedet er det bedre normalisere dataene ved indsættelse eller opdatering (for eksempel at gemme e-mails med små bogstaver, statusser med en homogen kodning) og transformere inputværdierne, så de matcher det format, i stedet for at anvende funktionen på kolonnen i hver sammenligning.
Det er også værd at være opmærksom på selve bestemmelsen. WHERE for at gøre det så selektivt som muligt. Selvom rækkefølgen af betingelserne ikke altid har en direkte indflydelse (optimeringsværktøjet ændrer normalt rækkefølgen på dem), hjælper det at have velindekserede prædikater og simple sammenligninger i stedet for dyre mønstre som LIKE '%texto'som normalt fremtvinger en fuld scanning.
Når du skal fjerne dubletter, skal du overveje, om en DISTINCT eller om forespørgslen kunne redesignes med JOINs mere præcise eller unikke begrænsninger i modellen. Begge DISTINCT som UNION involverer normalt sorterings- eller grupperingsoperationersom er blandt de dyreste i implementeringsplanen.
Vedligeholdelse af indekser og statistikker for at hjælpe optimeringsværktøjet
Moderne databasemotorer er afhængige af interne statistikker At estimere, hvor mange rækker der opfylder hver betingelse, hvilke indekser der er mest passende, og i hvilken rækkefølge tabeller skal forbindes. Hvis disse statistikker er forældede, kan planlæggeren træffe meget dårlige beslutninger og generere ineffektive udførelsesplaner.
Derfor er det vigtigt at køre kommandoer som f.eks. med jævne mellemrum ANALYZE (eller deres specifikke varianter i hver motor) for Opdater statistik efter massive indlæsningermigrationer eller store mængder af INSERT, UPDATE y DELETEI PostgreSQL håndteres autovacuum for eksempel normalt automatisk, men efter en stor import kan det være nyttigt at køre en ANALYZE brugervejledning.
I MySQL har vi sætninger som ANALYZE TABLE, som analyserer og lagrer nøglefordelingen for at hjælpe optimeringsværktøjet med at bestemme rækkefølgen og brugen af indekser i JOINsDerudover, OPTIMIZE TABLE tillade defragmentér tabeller, omarrangér og opdater indekser, noget der anbefales i tabeller, der har gennemgået mange ændringer.
For at kontrollere om motoren bruger indekserne som forventet, er der intet som at trække fra EXPLAIN o EXPLAIN ANALYZEDisse værktøjer viser os den estimerede plan (og i nogle søgemaskiner også den faktiske plan med aflæste tider og rækker) og angiver, om der udføres en sekventiel scanning (ALL i MySQL, for eksempel) eller hvis en Index Scanhvor mange rækker der forventes, og hvor mange der rent faktisk spilles.
At lære at læse disse planer er måske en af de mest værdifulde færdigheder for alle, der ønsker at optimere databaser: Det giver dig mulighed for at opdage flaskehalse, ubrugelige indekser, dårligt selektive filtre og dårligt ordnede joins. længe før problemet når produktionen.
Fuldtekstindekser, regulære udtryk og særlige scenarier
når du arbejder med store tekstfelter (beskrivelser, rigt HTML-indhold, kommentarer osv.), søger med LIKE '%palabra%' Disse bliver hurtigt upraktiske for store tabeller. I disse tilfælde tilbyder systemer som MySQL indeks af typen FULLTEXT og operatører som f.eks. MATCH() AGAINST()hvilket muliggør langt mere effektive og relevante søgninger.
med FULLTEXT Du kan vælge mellem forskellige tilstande: naturligt sprog, boolean (med operatorer) +, -, *(anførselstegn for præcise sætninger osv.) eller forespørgselsudvidelse for at udvide relaterede resultater. Dette giver dig mulighed for at bygge ret effektive interne søgemaskiner uden at skulle forlade databasen.
Der findes mere avancerede scenarier, hvor teksten f.eks. indeholder indlejrede HTML-tags. I så fald kan det være nødvendigt at kombinere et indeks. FULLTEXT med funktioner som REGEXP_REPLACE at rydde op i etiketter, når man sammenligner præcise sætninger. En typisk strategi er filtrer først ved hjælp af fuldtekstindekset og derefter anvende det regulære udtryk i en anden betingelse for at indsnævre resultatet til det nøjagtige beløb uden at scanne hele tabellen.
Andre motorer, såsom Oracle, tillader brugen af regulære tabeludtryk Disse funktioner hjælper optimeringsværktøjet med at indsætte prædikater i visninger og reducere den mellemliggende datamængde så hurtigt som muligt. Denne tilgang er meget nyttig, når man arbejder med mange indbyggede visninger eller komplekse definitioner i samarbejdsmiljøer.
Yderligere bedste fremgangsmåder: parametre, materialiserede visninger og opdeling af forespørgsler
Ud over indekser og implementeringsplaner er der en række god tværgående praksis som bidrager til både ydeevne og sikkerhed. En af de vigtigste er brug parameteriserede forespørgsler I stedet for at sammenkæde strenge for at opbygge dynamisk SQL, reducerer dette risikoen for SQL-injektion og giver databasen mulighed for at genbruge udførelsesplaner for forespørgsler med samme struktur.
I systemer med meget tunge og gentagne forespørgsler (dashboards, ledelsesrapporter, samlede beregninger), materialiserede synspunkter De er en god allieret. I modsætning til en normal visning gemmer de fysisk forespørgselsresultatet og bliver til en slags forudberegnet tabel, der kan indekseres og forespørges meget hurtigt.
PostgreSQL, Oracle og SQL Server (med deres indekserede visninger) understøtter materialiserede visninger native med forskellige opdateringsmuligheder (manuel, planlagt og endda automatisk i nogle tilfælde). Da der i MySQL ikke er nogen direkte understøttelse, emuleres denne adfærd normalt med tabeller og processer, der periodisk regenererer dataene, ofte via triggere eller planlagte opgaver.
Når en forespørgsel forbinder for mange tabeller eller er afhængig af en kompleks mosaik af visninger, er en anden gyldig strategi opdel forespørgslen i flere trinDette betyder, at man skal køre en indledende forespørgsel for at indhente et mindre sæt (f.eks. de relevante ID'er) og derefter køre yderligere forespørgsler for at fuldføre informationen. Denne tilgang bør anvendes med omtanke, da den kan øge antallet af databaseadgange, men i nogle tilfælde reducerer den drastisk planens kompleksitet og størrelsen af de mellemliggende sæt.
Gennem hele denne proces anvendes overvågningsværktøjer som f.eks. pg_stat_statements, PgHero, PMM, Query Store, New Relic eller Datadog De kan hjælpe dig med hurtigt at identificere, hvilke forespørgsler der er langsommere eller kører oftere, så du kan prioritere optimeringsindsatsen, hvor det virkelig betyder noget.
Optimer SQL-forespørgsler ved hjælp af AI
I de senere år er der dukket op værktøjer baseret på kunstig intelligens der analyserer dine forespørgsler og databaseskemaet for at foreslå forbedringer: indeksforslag, omskrivning af forespørgsler, ændringer i tabelstrukturen osv. Navne som EverSQL, DBScoop, PGAnalyzer eller Redshift Advisor er blevet populære i professionelle miljøer.
Disse løsninger kan gennemgå store mængder forespørgselslogfiler, krydsreferere dem med statistikker, udførelsesplaner og præstationsmålinger, og derfra opdage ineffektive mønstre eller flaskehalse hvilket ville undgå os ved første øjekast. De hjælper også med at vurdere den hypotetiske effekt af at oprette eller eliminere bestemte indeks.
Det er dog vigtigt at forstå dem som en støtte, ikke som en erstatning Det afhænger af din SQL-viden og forståelse af din applikation. Du modtager muligvis et indeksforslag, der i teorien fremskynder en specifik forespørgsel, men forværrer skrivninger til et kritisk modul betydeligt. Uden forretningskontekst ved værktøjet ikke, hvad der betyder mest.
Den ideelle kombination er et team, der mestrer optimeringsprincipper (planer, indekser, normalisering, adgangsmønstre) og bruger AI til at accelerere analyse og validere hypoteserikke at træffe blinde beslutninger.
Når man internaliserer hele dette sæt af teknikker – omhyggeligt indeksdesign, minimal kolonneudvælgelse, intelligent brug af JOIN'er og CTE'er, effektiv paginering, regelmæssig vedligeholdelse af statistikker, udnyttelse af materialiserede visninger og endda understøttelse fra AI-værktøjer – Store databaser er ikke længere et ukontrollerbart monster og de bliver en forudsigelig og skalerbar komponent i din arkitektur, der er i stand til at vokse med din virksomhed uden at ødelægge brugeroplevelsen eller infrastrukturbudgettet.
