Efter testning blev en betinget forespørgsel udført på en tabel med mere end 4 millioner poster, og forespørgselstiden var op til 40 sekunder. Derfor er det meget vigtigt, hvordan man forbedrer effektiviteten af SQL-sætningsforespørgsler. Følgende er flere metoder til optimering af forespørgselssætninger, som er bredt udbredt på internettet:
Først og fremmest, når datavolumen er stor, bør du prøve at undgå at scanne hele tabellen og overveje at bygge indekser på kolonnerne i 'hvor og ordna efter', hvilket kan fremskynde datahentningen betydeligt. Dog er der nogle situationer, hvor indeksering ikke virker:
1. Prøv at undgå at bruge != eller <> operatorer i where-klausulen, ellers opgiver motoren brugen af indekser og udfører fuld tabelscanning.
2. Prøv at undgå nullværdidom på felter i where-klausulen, ellers vil motoren opgive brugen af indekser og udføre fuld tabelscanning, såsom:
Vælg id fra t hvor num er null
Du kan sætte standardværdien 0 på num, sikre dig, at der ikke er nulværdi i num-kolonnen i tabellen, og så forespørge sådan her:
Vælg id fra t hvor num=0
3. Prøv at undgå at bruge OR i where-klausulen til at joine betingelser, ellers vil motoren opgive brugen af indekset og udføre en fuld tabelscanning, såsom:
Vælg ID fra T, hvor num=10 eller num=20
Du kan forestille sådan her:
Vælg id fra t hvor num=10
Forening alle
Vælg id fra t hvor num=20
4. Følgende forespørgsel vil også resultere i en fuld tabelscanning:
Vælg ID fra T, hvor navn som '%abc%'
For at forbedre effektiviteten kan du overveje fuldtekstsøgning.
5. In og ikke in bør også bruges med forsigtighed, ellers vil det føre til fuld bordscanning, såsom:
Vælg ID fra t hvor num in(1,2,3)
For kontinuerte værdier, hvis du kan bruge mellem, så brug ikke i:
Vælg ID fra T, hvor Num er mellem 1 og 3
6. Hvis du bruger parameteren i where-klausulen, vil det også få hele tabellen til at blive scannet. Fordi SQL kun løser lokale variabler under kørsel, men optimereren ikke kan udsætte valget af adgangsplaner til kørsel; Den skal vælges ved kompilering. Hvis der dog oprettes en adgangsplan ved kompilering, er værdien af variablen stadig ukendt og kan derfor ikke bruges som inputelement til indeksvalg. Følgende udsagn vil blive scannet i sin helhed:
Vælg id fra t hvor num=@num
Du kan tvinge forespørgslen til at bruge et indeks i stedet:
Vælg ID fra t med(indeks(indeks(indeksnavn)) hvor num=@num
7. Prøv at undgå at udtrykke felter i where-klausulen, da det vil få motoren til at opgive at bruge indekset og udføre fuld tabelscanning. For eksempel:
Vælg ID fra t hvor num/2=100
bør ændres til:
Vælg id fra t hvor num=100*2
8. Prøv at undgå at udføre funktionsoperationer på felter i where-klausulen, da det vil få motoren til at opgive at bruge indekser og udføre fuld tabelscanning. For eksempel:
Vælg id fra t hvor substring(name,1,3)='abc' – navn-id, der starter med abc
Vælg ID fra T hvor datediff(dag, oprettelsesdato,'2005-11-30′)=0–'2005-11-30′ genereret id
bør ændres til:
Vælg ID fra T, hvor navn som 'ABC%'
Vælg ID fra T hvor CreateDate>='2005-11-30′ og CreateDate<'2005-12-1′
9. Udfør ikke funktioner, aritmetiske operationer eller andre udtryksoperationer til venstre for "=" i where-klausulen, ellers kan systemet muligvis ikke bruge indekset korrekt.
10. Når man bruger et indeksfelt som betingelse, hvis indekset er et sammensat indeks, skal det første felt i indekset bruges som betingelse for at sikre, at systemet bruger indekset, ellers vil indekset ikke blive brugt, og rækkefølgen af felterne bør være så konsistent som muligt med indeksrækkefølgen.
11. Skriv ikke meningsløse forespørgsler, såsom at generere en tom tabelstruktur:
Vælg kol1,kol2 ind i #t fra t hvor 1=0
Denne type kode returnerer ikke noget resultatsæt, men den bruger systemressourcer, så den bør ændres til noget i retning af dette:
Opret tabel #t(...)
12. Ofte er det et godt valg at bruge exists i stedet for i:
Vælg num fra a, hvor num er ind (vælg num fra b)
Erstat med følgende udsagn:
Vælg Num fra A, hvor der findes (vælg 1 fra B, hvor num=A.Num)
Ting du skal være opmærksom på, når du bygger et indeks:
1. Ikke alle indekser er gyldige for forespørgsler, SQL er baseret på dataene i tabellen for at optimere forespørgslen; når indekskolonnen har en stor mængde dataduplikation, kan SQL-forespørgsler ikke bruge indekset, for eksempel har en tabel felter køn, han, kvinde næsten halvdelen hver, og selv hvis indekset er bygget på køn, vil det ikke spille nogen rolle i forespørgselseffektiviteten.
2. Jo flere indeks ikke er, desto bedre, indekset kan bestemt forbedre effektiviteten af den tilsvarende select, men det reducerer også effektiviteten af indsættelse og opdatering, fordi indekset kan genopbygges ved indsættelse eller opdatering, så hvordan man opbygger et indeks skal overvejes nøje afhængigt af den specifikke situation. Det er bedst ikke at have mere end 6 indekser i en tabel, og hvis der er for mange, bør man overveje, om det er nødvendigt at bygge indekser på nogle sjældent brugte kolonner.
3. Undgå at opdatere klyngede indeksdatakolonner så meget som muligt, fordi rækkefølgen af klyngede indekserede datakolonner er den fysiske lagringsrækkefølge for tabelposter, og når kolonneværdien ændres, vil det føre til justering af rækkefølgen af hele tabelposterne, hvilket vil forbruge betydelige ressourcer. Hvis applikationssystemet ofte skal opdatere de klyngede indekskolonner, skal det overveje, om indekset skal bygges som et klyngeindeks.
Andre punkter at bemærke:
1. Prøv at bruge numeriske felter, og prøv ikke at designe felter, der kun indeholder numerisk information som tegn, hvilket vil reducere ydeevnen af forespørgsler og forbindelser og øge lageroverhead. Dette skyldes, at motoren sammenligner hvert tegn i strengen én for én, når forespørgsler og joins behandles, mens den for numeriske typer kun behøver at blive sammenlignet én gang.
2. Brug ikke vælg * fra t nogen steder, erstat "*" med en specifik feltliste, og returner ikke felter, der ikke er brugt.
3. Prøv at bruge tabelvariabler i stedet for midlertidige tabeller. Hvis tabelvariablen indeholder en stor mængde data, skal man bemærke, at indekset er meget begrænset (kun primærnøgleindekset).
4. Undgå hyppig oprettelse og sletning af midlertidige tabeller for at reducere forbruget af systemtabelressourcer.
5. Midlertidige tabeller er ikke ubrugelige, og brug af dem korrekt kan gøre visse rutiner mere effektive, for eksempel når du gentagne gange skal referere til en stor tabel eller et datasæt i en almindeligt brugt tabel. Men til engangsbegivenheder er det bedst at bruge en eksporttabel.
6. Når du opretter en midlertidig tabel, hvis mængden af data indsat på én gang er stor, kan du bruge vælg i i stedet for at oprette tabel for at undgå at forårsage et stort antal logs for at forbedre hastigheden; Hvis datamængden ikke er stor, bør du for at lette ressourcerne i systemtabellen først oprette tabellen og derefter indsætte den.
7. Hvis en midlertidig tabel bruges, skal du sørge for eksplicit at slette alle midlertidige tabeller i slutningen af den lagrede procedure, afkorte tabellen først, og derefter fjerne tabellen for at undgå en lang låsning af systemtabellen.
8. Prøv at undgå at bruge markøren, fordi markørens effektivitet er dårlig; hvis dataene, som markøren opererer, overstiger 10.000 linjer, bør du overveje at omskrive.
9. Før du bruger den cursorbaserede metode eller den midlertidige tabelmetode, bør du først lede efter mængdebaserede løsninger til at løse problemet, og den mængdebaserede metode er som regel mere effektiv.
10. Ligesom midlertidige tabeller er markøren ikke ubrugelig. At bruge FAST_FORWARD cursors til små datasæt er ofte bedre end andre række-for-række-behandlingsmetoder, især hvis du skal referere til flere tabeller for at få de data, du har brug for. Rutiner, der inkluderer "total" i resultatsættet, er som regel hurtigere end dem, der udføres med markøren. Hvis udviklingstiden tillader det, kan både cursorbaserede og mængdebaserede metoder prøves for at se, hvilken der fungerer bedst.
11. Sæt SET NOCOUNT ON i starten af alle lagrede procedurer og triggere, og sæt SET NOCOUNT OFF til sidst. Der er ikke behov for at sende DONE_IN_PROC beskeder til klienten efter at have udført hver sætning i den lagrede procedure og trigger.
12. Prøv at undgå at returnere store data til klienten; hvis datamængden er for stor, bør du overveje, om den tilsvarende efterspørgsel er rimelig.
13. Forsøg at undgå store transaktionsoperationer og forbedre systemets samtidighedsevne.
|
Opslået på 11/05/2018 13.57.06
|
|
|
Opslået på 17/05/2018 10.12.27