Etter testing ble en betinget spørring utført på en tabell med mer enn 4 millioner poster, og spørringstiden var så høy som 40 sekunder. Derfor er det svært viktig hvordan man kan forbedre effektiviteten til SQL-setningsspørsmål. Følgende er flere metoder for optimalisering av spørringssetninger som er mye utbredt på Internett:
Først og fremst, når datavolumet er stort, bør du prøve å unngå å skanne hele tabellen, og heller vurdere å bygge indekser på kolonnene som er involvert i hvor og sortere etter, noe som kan fremskynde gjenfinningen av data betydelig. Det finnes imidlertid noen situasjoner hvor indeksering ikke fungerer:
1. Prøv å unngå å bruke != eller <> operatorer i where-klausulen, ellers vil motoren slutte å bruke indekser og utføre full tabellskanning.
2. Prøv å unngå nullverdivurdering på felt i where-klausulen, ellers vil motoren droppe bruken av indekser og utføre full tabellskanning, som for eksempel:
Velg id fra t hvor num er null
Du kan sette standardverdien 0 på num, sørge for at det ikke finnes nullverdi i num-kolonnen i tabellen, og så spørre slik:
velg id fra t hvor num=0
3. Prøv å unngå å bruke OR i where-klausulen for å joine betingelser, ellers vil motoren slutte å bruke indeksen og utføre en full tabellskanning, for eksempel:
Velg ID fra t hvor num=10 eller num=20
Du kan spørre slik:
Velg ID fra t hvor num=10
Forening alle
Velg ID fra t hvor num=20
4. Følgende spørring vil også resultere i en full tabellskanning:
Velg ID fra t hvor navn som '%abc%'
For å øke effektiviteten, vurder fulltekstsøk.
5. In og ikke in bør også brukes med forsiktighet, ellers vil det føre til full bordskanning, for eksempel:
Velg ID fra t hvor num in(1,2,3)
For kontinuerlige verdier, hvis du kan bruke mellom, ikke bruk i:
Velg ID fra T der Num er mellom 1 og 3
6. Hvis du bruker parameteren i where-klausulen, vil det også føre til at hele tabellen blir skannet. Fordi SQL kun løser lokale variabler under kjøring, men optimalisatoren kan ikke utsette valget av tilgangsplaner til kjøretid; Den må velges ved kompilering. Men hvis en tilgangsplan etableres ved kompilering, er verdien til variabelen fortsatt ukjent og kan derfor ikke brukes som et inngangselement for indeksvalg. Følgende erklæringer vil bli skannet i sin helhet:
Velg ID fra t der num=@num
Du kan tvinge spørringen til å bruke en indeks i stedet:
Velg ID fra t med(indeks(indeks(indeksnavn)) hvor num=@num
7. Prøv å unngå å uttrykke felt i where-klausulen, da vil motoren slutte å bruke indeksen og utføre full tabellskanning. For eksempel:
Velg ID fra t der num/2=100
bør endres til:
Velg ID fra T hvor num=100*2
8. Prøv å unngå å utføre funksjonsoperasjoner på felt i where-klausulen, noe som vil føre til at motoren slutter å bruke indekser og gjør full tabellskanning. For eksempel:
Velg ID fra t hvor substring(name,1,3)='abc' – navn-id som starter med abc
Velg ID fra t hvor datediff(day,createdate,'2005-11-30′)=0–'2005-11-30' generert id
bør endres til:
Velg ID fra t hvor navn som 'abc%'
Velg ID fra T der CreateDate>='2005-11-30′ og CreateDate<'2005-12-1′
9. Ikke utfør funksjoner, aritmetiske operasjoner eller andre uttrykksoperasjoner til venstre for "=" i where-klausulen, ellers kan systemet kanskje ikke bruke indeksen korrekt.
10. Når man bruker et indeksfelt som betingelse, hvis indeksen er en sammensatt indeks, må det første feltet i indeksen brukes som betingelse for å sikre at systemet bruker indeksen, ellers vil ikke indeksen bli brukt, og rekkefølgen på feltene bør være så konsistent med indeksrekkefølgen som mulig.
11. Ikke skriv meningsløse spørringer, som å generere en tom tabellstruktur:
Velg kol1,kol2 inn i #t fra t hvor 1=0
Denne typen kode returnerer ikke noe resultatsett, men den bruker systemressurser, så den bør endres til noe slik:
Opprett tabell #t(...)
12. Ofte er det et godt valg å bruke eksisterende i stedet for i:
Velg num fra a hvor num inn (velg num fra b)
Erstatt ut med følgende utsagn:
Velg Num fra A hvor finnes (velg 1 fra B hvor Num=A.Num)
Ting du bør være oppmerksom på når du bygger en indeks:
1. Ikke alle indekser er gyldige for spørringer, SQL er basert på dataene i tabellen for å optimalisere spørringen, når indekskolonnen har mye dataduplisering, kan SQL-spørringer ikke bruke indeksen, for eksempel at en tabell har felt kjønn, mann, kvinne nesten halvparten hver, så selv om indeksen er bygget på kjønn, vil den ikke spille noen rolle i spørringseffektiviteten.
2. Jo flere indekser ikke er, desto bedre, indeksen kan absolutt forbedre effektiviteten til den tilsvarende select, men den reduserer også effektiviteten ved innsetting og oppdatering, fordi indeksen kan bygges opp igjen ved innsetting eller oppdatering, så hvordan man bygger en indeks må vurderes nøye, avhengig av den spesifikke situasjonen. Det er best å ikke ha mer enn 6 indekser i en tabell, og hvis det er for mange, vurder om det er nødvendig å bygge indekser på noen sjeldent brukte kolonner.
3. Unngå å oppdatere kolonner for klyngede indeksdata så mye som mulig, fordi rekkefølgen på klyngede indekserte datakolonner er den fysiske lagringsrekkefølgen til tabellpostene, og når kolonneverdien endres, vil det føre til justering av rekkefølgen på hele tabellpostene, noe som vil bruke betydelige ressurser. Hvis applikasjonssystemet må oppdatere de klyngede indekskolonnene ofte, må det vurdere om indeksen bør bygges som en klynget indeks.
Andre punkter å merke seg:
1. Prøv å bruke numeriske felt, og prøv å unngå å designe felt som kun inneholder numerisk informasjon som tegn, noe som vil redusere ytelsen til spørringer og tilkoblinger, og øke lagringsoverhead. Dette er fordi motoren sammenligner hvert tegn i strengen én etter én når den behandler forespørsler og joins, mens for numeriske typer trenger den bare å sammenlignes én gang.
2. Ikke bruk velg * fra t noe sted, bytt ut "*" med en spesifikk feltliste, og ikke returner felt som ikke er brukt.
3. Prøv å bruke tabellvariabler i stedet for midlertidige tabeller. Hvis tabellvariabelen inneholder store mengder data, merk at indeksen er svært begrenset (kun primærnøkkelindeksen).
4. Unngå hyppig opprettelse og sletting av midlertidige tabeller for å redusere forbruket av systemtabellressurser.
5. Midlertidige tabeller er ikke ubrukelige, og å bruke dem riktig kan gjøre visse rutiner mer effektive, for eksempel når du må referere til en stor tabell eller et datasett gjentatte ganger i en vanlig tabell. For engangshendelser er det imidlertid best å bruke en eksporttabell.
6. Når du oppretter en midlertidig tabell, hvis mengden data som legges inn samtidig er stor, kan du bruke velg i i stedet for å opprette tabell for å unngå å forårsake et stort antall logger for å forbedre hastigheten; Hvis datamengden ikke er stor, bør du for å lette ressursene i systemtabellen først opprette tabellen og deretter sette inn.
7. Hvis en midlertidig tabell brukes, sørg for å eksplisitt slette alle midlertidige tabeller på slutten av den lagrede prosedyren, kort tabellen først, og deretter fjerne tabellen, for å unngå en lang lås av systemtabellen.
8. Prøv å unngå å bruke markøren, fordi effektiviteten til markøren er dårlig, hvis dataene som kjøres av markøren overstiger 10 000 linjer, bør du vurdere å skrive om den.
9. Før du bruker den markørbaserte metoden eller den midlertidige tabellmetoden, bør du først se etter mengdebaserte løsninger for å løse problemet, og den mengdebaserte metoden er vanligvis mer effektiv.
10. Som med midlertidige tabeller er ikke markøren ubrukelig. Å bruke FAST_FORWARD markører for små datasett er ofte bedre enn andre rad-for-rad-prosesseringsmetoder, spesielt hvis du må referere til flere tabeller for å hente dataene du trenger. Rutiner som inkluderer "total" i resultatsettet er vanligvis raskere enn de som utføres med markøren. Hvis utviklingstiden tillater det, kan både markørbaserte og mengdebaserte metoder prøves for å se hvilken som fungerer best.
11. Sett SETT NOCOUNT PÅ i starten av alle lagrede prosedyrer og triggere, og sett SET NOCOUNT OFF på slutten. Det er ikke nødvendig å sende DONE_IN_PROC meldinger til klienten etter at hver setning i den lagrede prosedyren og triggeren er utført.
12. Prøv å unngå å returnere store data til klienten; hvis datavolumet er for stort, bør du vurdere om den tilsvarende etterspørselen er rimelig.
13. Prøv å unngå store transaksjonsoperasjoner og forbedre systemets samtidighetsmulighet.
|
Publisert på 11.05.2018 13:57:06
|
|
|
Publisert på 17.05.2018 10:12:27