|
|
Publicerad på 2018-05-11 13:57:06
|
|
|
Efter testning utfördes en villkorad förfrågan på en tabell med mer än 4 miljoner poster, och frågetiden var så lång som 40 sekunder. Därför är det mycket viktigt hur man förbättrar effektiviteten i SQL-satsfrågor. Följande är flera metoder för optimering av frågesatser som sprids brett på Internet:
För det första, när datavolymen är stor, bör du försöka undvika att skanna hela tabellen och överväga att bygga index på kolumnerna i var och ordna efter, vilket kan påskynda datahämtningen avsevärt. Det finns dock vissa situationer där indexering inte fungerar:
1. Försök undvika att använda != eller <> operatorer i where-klausulen, annars överger motorn användningen av index och utför fullständig tabellskanning.
2. Försök undvika nullvärdesbedömning på fält i where-klausulen, annars kommer motorn att överge användningen av index och utföra fullständig tabellskanning, såsom:
Välj ID från t där num är null
Du kan sätta standardvärdet 0 på num, se till att det inte finns något nullvärde i num-kolumnen i tabellen, och sedan fråga så här:
Välj id från t där num=0
3. Försök undvika att använda OR i where-klausulen för att joina villkor, annars kommer motorn att sluta använda indexet och istället utföra en fullständig tabellskanning, till exempel:
Välj ID från t där num=10 eller num=20
Du kan fråga så här:
Välj id från t där num=10
Förening alla
Välj ID från t där num=20
4. Följande fråga kommer också att resultera i en fullständig tabellsskanning:
Välj ID från t där namn som '%abc%'
För att förbättra effektiviteten, överväg fulltextsökning.
5. In och inte in bör också användas med försiktighet, annars leder det till fullständig bordsscanning, såsom:
Välj ID från t där num in(1,2,3)
För kontinuerliga värden, om du kan använda mellan, använd inte i:
Välj ID från t där numet ligger mellan 1 och 3
6. Om du använder parametern i where-klausulen kommer det också att leda till att hela tabellen skannas. Eftersom SQL endast löser lokala variabler vid körning, men optimeraren inte kan skjuta upp valet av åtkomstplaner till körtid; Den måste väljas vid kompilering. Om dock en åtkomstplan upprättas vid kompileringstillfället är variabelns värde fortfarande okänt och kan därför inte användas som en indata för indexval. Följande uttalanden kommer att skannas i sin helhet:
Välj ID från t där num=@num
Du kan tvinga frågan att använda ett index istället:
Välj ID från t med(index(index(index(indexnamn)) där num=@num
7. Försök undvika att uttrycka fält i where-klausulen, vilket gör att motorn slutar använda indexet och istället utför fullständig tabellskanning. Till exempel:
Välj ID från t där num/2=100
bör ändras till:
Välj id från t där num=100*2
8. Försök undvika att utföra funktionsoperationer på fält i where-klausulen, vilket gör att motorn slutar använda index och istället utför fullständig tabellskanning. Till exempel:
Välj id från t där substring(name,1,3)='abc' – namn-id som börjar med abc
Välj ID från t där datediff(day,createdate,'2005-11-30′)=0–'2005-11-30' genererat id
bör ändras till:
Välj ID från T där namn som 'abc%'
Välj ID från t där CreateDate>='2005-11-30′ och CreateDate<'2005-12-1′
9. Utför inte funktioner, aritmetiska operationer eller andra uttrycksoperationer till vänster om "=" i where-klausulen, annars kan systemet kanske inte använda indexet korrekt.
10. När ett indexfält används som villkor, om indexet är ett sammansatt index, måste det första fältet i indexet användas som villkor för att säkerställa att systemet använder indexet, annars används inte indexet och fältordningen bör vara så konsekvent som möjligt med indexordningen.
11. Skriv inte meningslösa frågor, som att generera en tom tabellstruktur:
Välj kol1,kol2 i #t från t där 1=0
Denna typ av kod returnerar ingen resultatuppsättning, men den förbrukar systemresurser, så den bör ändras till något i stil med detta:
skapa tabell #t(...)
12. Många gånger är det ett bra val att använda existens istället för i:
Välj Num från A där Num In (Välj Num från B)
Ersätt med följande påstående:
Välj Num från A där finns (välj 1 från B där Num=A.Num)
Saker att vara uppmärksam på när du bygger ett index:
1. Alla index är inte giltiga för frågor, SQL baseras på data i tabellen för att optimera frågan, när indexkolumnen har mycket datadubblering kan SQL-frågor inte använda indexet, till exempel har en tabell fält kön, man, kvinna nästan hälften varje, och även om indexet byggs på kön spelar det ingen roll i frågeeffektiviteten.
2. Ju fler index som inte är bättre, indexet kan visserligen förbättra effektiviteten hos motsvarande select, men det minskar också effektiviteten vid insättning och uppdatering, eftersom indexet kan byggas om vid insättning eller uppdatering, så hur man bygger ett index måste noggrant övervägas beroende på den specifika situationen. Det är bäst att inte ha fler än 6 index i en tabell, och om det finns för många, överväg om det är nödvändigt att bygga index på några sällan använda kolumner.
3. Undvik att uppdatera klustrade indexdatakolumner så mycket som möjligt, eftersom ordningen för klustrade indexerade datakolumner är den fysiska lagringsordningen för tabellposter, och när kolumnvärdet ändras leder det till justering av ordningen på hela tabellposten, vilket kommer att förbruka betydande resurser. Om applikationssystemet behöver uppdatera de klustrade indexkolumnerna ofta måste det överväga om indexet bör byggas som ett klustrat index.
Andra punkter att notera:
1. Försök använda numeriska fält och försök att inte designa fält som endast innehåller numerisk information som tecken, vilket minskar prestandan för frågor och anslutningar och ökar lagringsöverhead. Detta beror på att motorn jämför varje tecken i strängen en efter en vid bearbetning av frågor och joins, medan den för numeriska typer bara behöver jämföras en gång.
2. Använd inte select * från t någonstans, ersätt "*" med en specifik fältlista och returnera inga fält som inte används.
3. Försök använda tabellvariabler istället för temporära tabeller. Om tabellvariabeln innehåller en stor mängd data, observera att indexet är mycket begränsat (endast primärnyckelindexet).
4. Undvik att ofta skapa och ta bort tillfälliga tabeller för att minska förbrukningen av systemtabellresurser.
5. Tillfälliga tabeller är inte oanvändbara, och att använda dem på rätt sätt kan göra vissa rutiner mer effektiva, till exempel när du behöver referera till en stor tabell eller en datamängd i en vanligt använd tabell upprepade gånger. Men för engångshändelser är det bäst att använda en exporttabell.
6. När du skapar en tillfällig tabell, om mängden data som infogas samtidigt är stor, kan du använda select in istället för create table för att undvika att orsaka ett stort antal loggar för att förbättra hastigheten; Om datamängden inte är stor, för att underlätta resurserna i systemtabellen, bör du först skapa en tabell och sedan infoga.
7. Om en tillfällig tabell används, se till att uttryckligen ta bort alla tillfälliga tabeller i slutet av den lagrade proceduren, trunkera tabellen först och sedan ta bort tabellen, för att undvika en lång låsning av systemtabellen.
8. Försök undvika att använda markören, eftersom markörens effektivitet är dålig, om datan som styrs av markören överstiger 10 000 rader bör du överväga att skriva om.
9. Innan du använder den kursorbaserade metoden eller den temporära tabellmetoden bör du först leta efter mängdbaserade lösningar för att lösa problemet, och den mängdbaserade metoden är vanligtvis mer effektiv.
10. Precis som temporära tabeller är markören inte oanvändbar. Att använda FAST_FORWARD markörer för små datamängder är ofta bättre än andra rad-för-rad-bearbetningsmetoder, särskilt om du måste referera till flera tabeller för att få den data du behöver. Rutiner som inkluderar "total" i resultatuppsättningen är vanligtvis snabbare än de som körs med markören. Om utvecklingstiden tillåter kan både kursorbaserade och mängdbaserade metoder testas för att se vilken som fungerar bäst.
11. Sätt SET NOCOUNT ON i början av alla lagrade procedurer och triggers, och sätt SET NOCOUNT OFF i slutet. Det finns inget behov av att skicka DONE_IN_PROC meddelanden till klienten efter att varje sats i den lagrade proceduren och triggern har exekverats.
12. Försök undvika att returnera stora data till klienten, om datavolymen är för stor bör du överväga om motsvarande efterfrågan är rimlig.
13. Försök undvika stora transaktionsoperationer och förbättra systemets samtidighetsförmåga.
|
Föregående:IFNULL, NULLIF och ISNULL användningNästa:Felkod: 2013. Tappade anslutningen till MySQL-servern under en fråga
|
Publicerad på 2018-05-17 10:12:27