Programmering

SQL frigivet: 17 måder at fremskynde dine SQL-forespørgsler på

SQL-udviklere på hver platform kæmper, tilsyneladende fast i en GØRE IMENS løkke, der får dem til at gentage de samme fejl igen og igen. Det skyldes, at databasefeltet stadig er relativt umodent. Sikker på, leverandører gør nogle fremskridt, men de fortsætter med at kæmpe med de større problemer. Samtidighed, ressourcehåndtering, pladsadministration og hastighed plager stadig SQL-udviklere, uanset om de koder på SQL Server, Oracle, DB2, Sybase, MySQL eller enhver anden relationel platform.

En del af problemet er, at der ikke er nogen magisk kugle, og for næsten enhver bedste praksis kan jeg vise dig mindst en undtagelse. Typisk finder en udvikler sine egne yndlingsmetoder - selvom de normalt ikke indeholder nogen konstruktioner til ydeevne eller samtidighed - og gider ikke at udforske andre muligheder. Måske er det et symptom på manglende uddannelse, eller udviklerne er bare for tæt på processen til at genkende, når de laver noget forkert. Måske kører forespørgslen godt på et lokalt sæt testdata, men mislykkes meget i produktionssystemet.

Jeg forventer ikke, at SQL-udviklere bliver administratorer, men de skal tage produktionsproblemer i betragtning, når de skriver deres kode. Hvis de ikke gør det under den første udvikling, får DBA'erne bare dem til at gå tilbage og gøre det senere - og brugerne lider i mellemtiden.

Der er en grund til, at vi siger, at indstilling af en database er både en kunst og en videnskab. Det skyldes, at der kun findes meget få hårde og hurtige regler, der gælder over hele linjen. De problemer, du har løst på et system, er ikke problemer med et andet og omvendt. Der er ikke noget rigtigt svar, når det gælder indstilling af forespørgsler, men det betyder ikke, at du skal give op.

Der er nogle gode principper, du kan følge, som skal give resultater i en eller anden kombination. Jeg har indkapslet dem på en liste over SQL-doser og don'ts, der ofte overses eller er svære at få øje på. Disse teknikker skal give dig lidt mere indsigt i dine DBAs sind såvel som evnen til at begynde at tænke på processer på en produktionsorienteret måde.

1. Brug ikke OPDATER i stedet for SAG

Dette problem er meget almindeligt, og selvom det ikke er svært at få øje på, overser mange udviklere det ofte, fordi de bruger OPDATER har en naturlig ow, der virker logisk.

Tag for eksempel dette scenarie: Du indsætter data i en temp-tabel og har brug for det til at vise en bestemt værdi, hvis der findes en anden værdi. Måske trækker du fra kundetabellen, og du vil have, at alle med mere end $ 100.000 i ordrer skal mærkes som "Foretrukne". Således indsætter du dataene i tabellen og kører en OPDATER erklæring for at indstille CustomerRank-kolonnen til "Foretrukket" for alle, der har mere end $ 100.000 i ordrer. Problemet er, at OPDATER erklæring er logget, hvilket betyder, at den skal skrive to gange for hver enkelt skrivning til tabellen. Vejen omkring dette er selvfølgelig at bruge en inline SAG i selve SQL-forespørgslen. Dette tester hver række for betingelsen om ordrestørrelse og indstiller etiketten "Foretrukken", før den skrives til tabellen. Ydelsesforøgelsen kan være forbløffende.

2. Genbrug ikke koden blindt

Dette spørgsmål er også meget almindeligt. Det er meget let at kopiere en andens kode, fordi du ved, at den trækker de data, du har brug for. Problemet er, at det ofte trækker meget mere data, end du har brug for, og udviklere gider sjældent at trimme det ned, så de ender med et stort supersæt af data. Dette kommer normalt i form af en ekstra ydre sammenføjning eller en ekstra tilstand i HVOR klausul. Du kan få enorme præstationsgevinster, hvis du trimmer genbrugt kode til dine nøjagtige behov.

3. Træk kun det antal kolonner, du har brug for

Dette nummer svarer til nummer nr. 2, men det er specifikt for kolonner. Det er alt for let at kode alle dine forespørgsler med VÆLG * i stedet for at liste kolonnerne individuelt. Problemet er igen, at det trækker flere data, end du har brug for. Jeg har set denne fejl snesevis af og dusinvis af gange. En udvikler gør en VÆLG * forespørgsel mod et bord med 120 kolonner og millioner af rækker, men vind op med kun tre til fem af dem. På det tidspunkt behandler du så meget mere data, end du har brug for, det er et under, forespørgslen overhovedet returnerer. Du behandler ikke kun flere data, end du har brug for, men du tager også ressourcer væk fra andre processer.

4. Dobbeltdyp ikke

Her er en anden, jeg har set flere gange, end jeg skulle have: En lagret procedure er skrevet for at hente data fra en tabel med hundreder af millioner af rækker. Udvikleren har brug for kunder, der bor i Californien og har en indkomst på mere end $ 40.000. Så han spørger til kunder, der bor i Californien og lægger resultaterne i en temp-tabel; så spørger han efter kunder med indkomster over $ 40.000 og sætter disse resultater i en anden temp-tabel. Endelig slutter han sig til begge borde for at få det endelige produkt.

Laver du sjov med mig? Dette skal gøres i en enkelt forespørgsel; I stedet dobbeltdypper du et stort bord. Vær ikke en idiot: Spørg kun store tabeller, når det er muligt - du finder ud af, hvor meget bedre dine procedurer udfører.

Et lidt andet scenarie er, når der er behov for en undersæt af en stor tabel i flere trin i en proces, hvilket får den store tabel til at blive forespurgt hver gang. Undgå dette ved at spørge til delsættet og vedligeholde det andetsteds og derefter pege de efterfølgende trin på dit mindre datasæt.

6. Udfør data på forhånd

Dette er et af mine yndlingsemner, fordi det er en gammel teknik, der ofte overses. Hvis du har en rapport eller en procedure (eller bedre endnu, et sæt af dem), der gør lignende sammenføjninger til store tabeller, kan det være en fordel for dig at pre-fase dataene ved at slutte dig til tabellerne på forhånd og vedvare dem ind i et bord. Nu kan rapporterne køre mod den forudindstillede tabel og undgå den store sammenføjning.

Du er ikke altid i stand til at bruge denne teknik, men når du kan, finder du, at det er en glimrende måde at spare serverressourcer på.

Bemærk, at mange udviklere omgår dette sammenkædningsproblem ved at koncentrere sig om selve forespørgslen og oprette en visning kun omkring sammenføjningen, så de ikke behøver at indtaste tilslutningsbetingelserne igen og igen. Men problemet med denne tilgang er, at forespørgslen stadig kører for hver rapport, der har brug for den. Ved at pre-iscenesætte dataene kører du sammenføjningen kun en gang (for eksempel 10 minutter før rapporterne), og alle andre undgår den store sammenføjning. Jeg kan ikke fortælle dig, hvor meget jeg elsker denne teknik; i de fleste miljøer er der populære tabeller, der bliver tilsluttet hele tiden, så der er ingen grund til, at de ikke kan forudindstilles.

7. Slet og opdater i batches

Her er en anden let teknik, der bliver overset meget. Sletning eller opdatering af store mængder data fra store tabeller kan være et mareridt, hvis du ikke gør det rigtigt. Problemet er, at begge disse udsagn kører som en enkelt transaktion, og hvis du har brug for at dræbe dem, eller hvis der sker noget med systemet, mens de arbejder, skal systemet rulle hele transaktionen tilbage. Dette kan tage meget lang tid. Disse operationer kan også blokere for andre transaktioner, så længe de er flaskehalsede i systemet.

Løsningen er at udføre sletninger eller opdateringer i mindre batcher. Dette løser dit problem på et par måder. For det første, hvis transaktionen bliver dræbt af en eller anden grund, har den kun et lille antal rækker at rulle tilbage, så databasen vender tilbage meget hurtigere online. For det andet, mens de mindre batcher forpligter sig til disken, kan andre snige sig ind og udføre noget arbejde, så samtidig forbedres i høj grad.

På denne linje har mange udviklere det fast i hovedet, at disse sletnings- og opdateringsoperationer skal være afsluttet samme dag. Det er ikke altid sandt, især hvis du arkiverer. Du kan strække den operation ud, så længe du har brug for det, og de mindre batcher hjælper med at opnå det. Hvis det kan tage længere tid at udføre disse intensive operationer, skal du bruge den ekstra tid og ikke bringe dit system ned.

8. Brug midlertidigtabeller til at forbedre markørens ydeevne

Jeg håber, at vi alle ved nu, at det er bedst at holde sig væk fra markører, hvis det overhovedet er muligt. Markører lider ikke kun af hastighedsproblemer, hvilket i sig selv kan være et problem med mange operationer, men de kan også få din operation til at blokere andre operationer meget længere end nødvendigt. Dette mindsker i høj grad samtidigheden i dit system.

Du kan dog ikke altid undgå at bruge markører, og når disse tidspunkter opstår, kan du muligvis komme væk fra markørinducerede ydeevneproblemer ved at udføre markørhandlingerne mod en temp-tabel i stedet. Tag for eksempel en markør, der går gennem en tabel og opdaterer et par kolonner baseret på nogle sammenligningsresultater. I stedet for at foretage sammenligningen med live-tabellen kan du muligvis placere disse data i en temp-tabel og i stedet sammenligne med den. Så har du en single OPDATER udsagn mod livebordet, der er meget mindre og kun holder låse i kort tid.

At skære dine datamodifikationer som denne kan i høj grad øge samtidigheden. Jeg slutter med at sige, at du næsten aldrig behøver at bruge en markør. Der er næsten altid en sætbaseret løsning; du skal lære at se det.

9. Nest ikke udsigter

Visninger kan være praktiske, men du skal være forsigtig, når du bruger dem. Mens visninger kan hjælpe med at skjule store forespørgsler fra brugere og til at standardisere dataadgang, kan du nemt befinde dig i en situation, hvor du har visninger, der kalder visninger, der kalder visninger, der kalder visninger. Dette kaldes indlejrede udsigter, og det kan forårsage alvorlige ydeevneproblemer, især på to måder:

  • For det første vil du sandsynligvis have meget flere data, der kommer tilbage, end du har brug for.
  • For det andet vil forespørgselsoptimering give op og returnere en dårlig forespørgselsplan.

Jeg havde engang en klient, der elskede indlejrede synspunkter. Klienten havde en visning, den brugte til næsten alt, fordi den havde to vigtige sammenføjninger. Problemet var, at visningen returnerede en kolonne med 2MB dokumenter i. Nogle af dokumenterne var endnu større. Klienten skubbede mindst 2 MB ekstra over hele netværket for hver enkelt række i næsten hver eneste forespørgsel, den kørte. Naturligvis var forespørgslens ydeevne uhyggelig.

Og ingen af ​​forespørgslerne brugte faktisk den kolonne! Naturligvis blev søjlen begravet syv visninger dybt, så selv at finde det var svært. Da jeg fjernede dokumentkolonnen fra visningen, gik tiden for den største forespørgsel fra 2,5 timer til 10 minutter. Da jeg endelig afslørede de indlejrede synspunkter, som havde flere unødvendige sammenføjninger og kolonner, og skrev en almindelig forespørgsel, faldt tiden til den samme forespørgsel til undersekunder.