|
|
Geplaatst op 11-05-2018 13:57:06
|
|
|
Na testen werd een voorwaardelijke query uitgevoerd op een tabel met meer dan 4 miljoen records, en de querytijd bedroeg wel 40 seconden. Daarom is het erg belangrijk hoe de efficiëntie van SQL-statement-query's te verbeteren. De volgende zijn verschillende methoden voor optimalisatie van querystatements die wijdverspreid zijn op het internet:
Allereerst, wanneer het datavolume groot is, moet je proberen de volledige tabel niet te scannen en indexen bouwen op de kolommen die betrokken zijn bij waar en volgorde op, wat het ophalen van data aanzienlijk kan versnellen. Er zijn echter situaties waarin indexeren niet werkt:
1. Probeer het gebruik van != of <> operatoren in de where-clausule te vermijden, anders stopt de engine met het gebruik van indexen en voert volledige tabelscanning uit.
2. Probeer null value judgment te vermijden op velden in de where-clausule, anders stopt de engine met het gebruik van indexen en voert volledige tabelscanning uit, zoals:
Selecteer ID uit T waar Num null is
Je kunt de standaardwaarde 0 instellen op het num, ervoor zorgen dat er geen nullwaarde in de numkolom in de tabel staat, en dan zo'n query doen:
Selecteer ID uit t waarbij num=0
3. Probeer het gebruik van OR in de where-clausule te vermijden om condities te joinen, anders stopt de engine met het gebruik van de index en voert een volledige tabelscan uit, zoals:
Selecteer ID uit T waarbij num=10 of num=20
Je kunt zo'n vraag stellen:
Selecteer ID uit T waar num=10
Unie ALL
Selecteer ID uit t waarbij num=20
4. De volgende query resulteert ook in een volledige tabelscan:
Selecteer ID uit T waar naam zoals '%abc%'
Om de efficiëntie te verbeteren, overweeg full-text search.
5. In en niet in moet ook met voorzichtigheid worden gebruikt, anders leidt dit tot volledige tafelscanning, zoals:
Selecteer ID uit t waar num in(1,2,3)
Voor continue waarden, als je tussen kunt gebruiken, gebruik dan niet in:
Selecteer ID uit T waar Num tussen 1 en 3 ligt
6. Als je de parameter in de where-clausule gebruikt, wordt de volledige tabel ook gescand. Omdat SQL alleen lokale variabelen tijdens runtime oplost, maar de optimizer de selectie van toegangsplannen niet naar runtime kan uitstellen; Het moet bij het compileren worden geselecteerd. Als er echter een toegangsplan wordt opgesteld tijdens het compileren tijd, is de waarde van de variabele nog steeds onbekend en kan daarom niet als invoeritem voor indexselectie worden gebruikt. De volgende verklaringen worden volledig gescand:
Selecteer ID uit T waar num=@num
Je kunt de query dwingen om in plaats daarvan een index te gebruiken:
Selecteer ID uit T met(index(index(indexnaam)) waarbij num=@num
7. Probeer velden niet uit te drukken in de where-clausule, wat ertoe leidt dat de engine stopt met de index en volledig tabelscanning uitvoert. Bijvoorbeeld:
Selecteer ID uit T waar num/2=100
moet worden gewijzigd in:
Selecteer ID uit T waar num=100*2
8. Probeer het uitvoeren van functiebewerkingen op velden in de where-clausule te vermijden, wat ertoe leidt dat de engine het gebruik van indexen stopt en volledige tabelscanning uitvoert. Bijvoorbeeld:
Selecteer ID uit t waarbij substring(name,1,3)='abc' – naam-id dat begint met abc
Selecteer ID uit T waar datediff(day,createdate,'2005-11-30′)=0–'2005-11-30' gegenereerde id
moet worden gewijzigd in:
Selecteer ID uit T waar naam zoals 'abc%'
Selecteer ID uit T waar CreateDate>='2005-11-30' en CreateDate<'2005-12-1′
9. Voer geen functies, rekenkundige bewerkingen of andere expressiebewerkingen uit links van de "=" in de where-clausule, anders kan het systeem de index mogelijk niet correct gebruiken.
10. Bij het gebruik van een indexveld als voorwaarde, als de index een samengestelde index is, moet het eerste veld in de index als voorwaarde worden gebruikt om ervoor te zorgen dat het systeem de index gebruikt, anders wordt de index niet gebruikt, en moet de volgorde van de velden zoveel mogelijk consistent zijn met de indexvolgorde.
11. Schrijf geen betekenisloze queries, zoals het genereren van een lege tabelstructuur:
Selecteer col1,col2 in #t uit t waarbij 1=0
Dit type code geeft geen enkele resultaatset terug, maar verbruikt wel systeembronnen, dus het zou moeten worden aangepast naar iets als dit:
maak een tabel aan #t(...)
12. Vaak is het een goede keuze om 'exist' te gebruiken in plaats van in:
Selecteer Num uit A Waar Num In (Selecteer Num uit B)
Vervang door de volgende uitspraak:
Selecteer Num uit A waar bestaat (selecteer 1 uit B waar Num=A.Num)
Dingen om op te letten bij het samenstellen van een index:
1. Niet alle indexen zijn geldig voor queries, SQL is gebaseerd op de data in de tabel om de query te optimaliseren; wanneer de indexkolom veel dataduplicatie bevat, gebruiken SQL-queries mogelijk geen index, bijvoorbeeld een tabel met velden geslacht, man, vrouw, elk bijna de helft, dan speelt de index geen rol in de query-efficiëntie.
2. Hoe meer indexen niet zijn, hoe beter, de index kan zeker de efficiëntie van de bijbehorende selectie verbeteren, maar het vermindert ook de efficiëntie van inserten en updaten, omdat de index kan worden herbouwd bij het invoegen of updaten, dus hoe een index gebouwd moet worden zorgvuldig overwogen, afhankelijk van de specifieke situatie. Het is het beste om niet meer dan 6 indexen in een tabel te hebben, en als er te veel zijn, overweeg dan of het nodig is indexen te bouwen op enkele zelden gebruikte kolommen.
3. Vermijd het zo veel mogelijk bij te werken van geclusterde indexdatakolommen, omdat de volgorde van geclusterde geïndexeerde datakolommen de fysieke opslagvolgorde van tabelrecords is, en zodra de kolomwaarde verandert, leidt dit tot aanpassing van de volgorde van de volledige tabelrecords, wat aanzienlijke middelen zal kosten. Als het applicatiesysteem de geclusterde indexkolommen vaak moet bijwerken, moet het overwegen of de index als een geclusterde index moet worden opgebouwd.
Andere punten om op te merken:
1. Probeer numerieke velden te gebruiken en probeer geen velden te ontwerpen die alleen numerieke informatie als tekens bevatten, wat de prestaties van queries en verbindingen vermindert en de opslagoverhead verhoogt. Dit komt doordat de engine elk teken in de string één voor één vergelijkt bij het verwerken van queries en joins, terwijl het voor numerieke types slechts één keer vergeleken hoeft te worden.
2. Gebruik nergens selecteer * uit t, vervang "*" door een specifieke veldlijst en geef geen velden terug die niet zijn gebruikt.
3. Probeer tabelvariabelen te gebruiken in plaats van tijdelijke tabellen. Als de tabelvariabele een grote hoeveelheid data bevat, let dan op dat de index zeer beperkt is (alleen de primaire sleutelindex).
4. Vermijd het vaak aanmaken en verwijderen van tijdelijke tabellen om het verbruik van systeemtabelbronnen te verminderen.
5. Tijdelijke tabellen zijn niet onbruikbaar, en het correct gebruiken ervan kan bepaalde routines effectiever maken, bijvoorbeeld wanneer je herhaaldelijk moet verwijzen naar een grote tabel of een dataset in een veelgebruikte tabel. Voor eenmalige gebeurtenissen is het echter het beste om een exporttabel te gebruiken.
6. Bij het aanmaken van een tijdelijke tabel, als de hoeveelheid data die tegelijk is ingevoegd groot is, kun je selecteren gebruiken in plaats van een tabel aanmaken om te voorkomen dat er veel logs worden veroorzaakt en de snelheid verbetert; Als de hoeveelheid data niet groot is, moet je om de middelen van de systeemtabel te vergemakkelijken eerst een tabel aanmaken en dan invoegen.
7. Als een tijdelijke tabel wordt gebruikt, zorg er dan voor dat alle tijdelijke tabellen expliciet aan het einde van de opgeslagen procedure worden verwijderd, eerst de tabel worden afgekapt en vervolgens de tabel worden verwijderd, om een lange vergrendeling van de systeemtabel te voorkomen.
8. Probeer het gebruik van de cursor te vermijden, want de efficiëntie van de cursor is slecht; als de data die door de cursor wordt gebruikt meer dan 10.000 regels bedraagt, moet je overwegen om te herschrijven.
9. Voordat je de cursor-gebaseerde methode of de tijdelijke tabelmethode gebruikt, moet je eerst zoeken naar verzamelingsgebaseerde oplossingen om het probleem op te lossen, en de verzamelingsgebaseerde methode is meestal effectiever.
10. Net als tijdelijke tabellen is de cursor niet onbruikbaar. Het gebruik van FAST_FORWARD cursors voor kleine datasets is vaak beter dan andere rij-voor-rij verwerkingsmethoden, vooral als je meerdere tabellen moet raadplegen om de benodigde data te krijgen. Routines die "totaal" in de resultaatset bevatten, zijn meestal sneller dan die welke met de cursor worden uitgevoerd. Als de ontwikkeltijd het toelaat, kunnen zowel cursor-gebaseerde als set-gebaseerde methoden worden geprobeerd om te zien welke beter werkt.
11. SET NOCOUNT ON aan het begin van alle opgeslagen procedures en triggers, en stel SET NOCOUNT OFF aan het einde. Het is niet nodig om DONE_IN_PROC berichten naar de client te sturen nadat elke instructie van de opgeslagen procedure en trigger is uitgevoerd.
12. Probeer te voorkomen dat grote data aan de klant wordt teruggegeven; als het datavolume te groot is, moet je overwegen of de bijbehorende vraag redelijk is.
13. Probeer grote transacties te vermijden en verbeter de gelijktijdigheid van het systeem.
|
Vorig:Gebruik van IFNULL, NULLIF en ISNULLVolgend:Foutcode: 2013. Verbinding met MySQL-server verloren tijdens query
|
Geplaatst op 17-05-2018 10:12:27