SQL Query Optimization Techniques

Återutgiven av Platon

anhängare: 0

SQL-frågeoptimeringstekniker
Bild av författare

På nybörjarnivå fokuserar vi bara på att bara skriva och köra SQL-frågorna. Vi bryr oss inte om hur mycket tid det tar att utföra eller om det kan hantera miljontals poster. Men på mellannivå förväntar sig folk att din fråga är optimerad och tar minimal tid att köra.

Att skriva en optimerad fråga i stora applikationer med miljontals uppgifter, som e-handelsplattformar eller banksystem, är absolut nödvändigt. Anta att du äger ett e-handelsföretag med mer än en miljon produkter och en kund vill söka efter en produkt. Vad händer om frågan du skrev i backend tar mer än en minut att hämta den produkten från databasen? Kommer du att tro att kunderna köper produkter från din webbplats?

Du måste förstå vikten av SQL-frågeoptimering. I den här handledningen kommer jag att visa dig några tips och tricks för att optimera dina SQL-frågor och få dem att köras snabbare. Den primära förutsättningen är att du måste ha grundläggande kunskaper i SQL.

För att kontrollera om ett specifikt element finns i tabellen, använd EXIST() nyckelord istället för COUNT() kommer att köra frågan på ett mer optimerat sätt.

Använda COUNT(), måste frågan räkna alla förekomster av det specifika elementet som kan vara ineffektivt när databasen är omfattande. Å andra sidan, EXIST() kontrollerar endast den första förekomsten av det elementet och slutar sedan när den hittar den första förekomsten. Detta sparar mycket tid.

Dessutom är du bara intresserad av att ta reda på om ett visst element är närvarande eller inte. Du är inte intresserad av att ta reda på antalet förekomster. Det är därför också EXIST() är bättre.

SELECT EXISTS( SELECT * FROM table WHERE myColumn = 'val' );

Ovanstående fråga kommer tillbaka 1 om minst en tabellrad innehåller en post där en kolumn heter myColumn har ett värde lika med Val. Annars kommer det tillbaka 0.

Både char och varchar datatyper används för att lagra teckensträngar i tabellen. Men varchar är mycket mer minneseffektiv än char.

Teckningsdatatypen kan endast lagra teckensträngen med fast längd som definierats. Om längden på strängen är mindre än den fasta längden, kommer den att fylla de tomma utrymmena för att göra dess längd lika med den inställda längden. Detta kommer att slösa minne i onödan på stoppning. Till exempel,CHAR(100) kommer att ta 100 byte minne även om ett enstaka tecken är lagrat.

Å andra sidan lagrar varchar datatype teckensträngen med variabel längd som har en längd som är mindre än den angivna maximala längden. Den fyller inte ut de tomma utrymmena och tar bara minnet lika med strängens faktiska längd. Till exempel, VARCHAR(100) tar bara 1 byte minne när du lagrar ett enstaka tecken.

CREATE TABLE myTable ( id INT PRIMARY KEY, charCol CHAR(10), varcharCol VARCHAR(10)
);

I exemplet ovan, en tabell myTable skapas med två kolumner, charCol och varcharCol som har char- respektive varchar-datatyper. charCol kommer alltid att ta 10 byte minne. I kontrast, varcharCol tar minne lika med den faktiska storleken på teckensträngen lagrad i den.

Vi måste undvika att använda underfrågor i WHERE-satsen för att optimera en SQL-fråga. Eftersom underfrågorna kan vara dyra och svåra att utföra när de returnerar ett stort antal rader.

Istället för att använda underfrågan kan du få samma resultat genom att använda en join-operation eller skriva en korrelerad underfråga. En korrelerad underfråga är en underfråga där den inre frågan beror på den yttre frågan. Och de är mycket effektiva jämfört med icke-korrelerade subquery.

Nedan är ett exempel för att förstå skillnaden mellan de två.

# Using a subquery
SELECT * FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE country = 'INDIA' ); # Using a join operation
SELECT orders.* FROM orders JOIN customers ON orders.customer_id = customers.id WHERE customers.country = 'INDIA';

I det första exemplet samlar underfrågan först in alla kund-ID som tillhör INDIEN, och sedan kommer den yttre frågan att få alla beställningar av de valda kund-ID:n. Och i det andra exemplet har vi uppnått samma resultat genom att gå med i customers och orders tabeller och sedan välja endast beställningar där kunderna hör hemma från INDIEN.

På så sätt kan vi optimera frågan genom att undvika användningen av underfrågor i WHERE-satsen och göra dem lättare att läsa och förstå.

Applicera JOIN operation från en större tabell till en mindre tabell är en vanlig SQL-optimeringsteknik. Eftersom att gå från en större tabell till en mindre tabell kommer att få din fråga att köras snabbare. Om vi tillämpar en JOIN operation från en mindre tabell till en större tabell, måste vår SQL-motor söka i en större tabell efter matchande rader. Detta är mer resurskrävande och tidskrävande. Men å andra sidan, om JOIN tillämpas från en större tabell till en mindre tabell, då måste SQL-motorn söka i en mindre tabell efter matchande rader.

Här är ett exempel för din bättre förståelse.

# Order table is larger than the Customer table # Join from a larger table to a smaller table
SELECT * FROM Order JOIN Customer ON Customer.id = Order.id # Join from a smaller table to a larger table
SELECT * FROM Customer JOIN Order ON Customer.id = Order.id

Till skillnad från LIKE klausul, regexp_like används också för mönstersökning. De LIKE klausul är en grundläggande mönstermatchningsoperator som endast kan utföra grundläggande operationer som _ or %, som används för att matcha ett enstaka tecken eller valfritt antal tecken. De LIKE klausul måste skanna hela databasen för att hitta det specifika mönstret, vilket är långsamt för stora tabeller.

Å andra sidan, regexp_like är en mer effektiv, optimerad och kraftfull mönstersökningsteknik. Den använder mer komplexa reguljära uttryck för att hitta specifika mönster i en teckensträng. Dessa reguljära uttryck är mer specifika än enkel jokerteckenmatchning eftersom de låter dig söka efter det exakta mönstret som vi hittar. På grund av detta minskar mängden data som behöver sökas, och frågan körs snabbare.

Observera att regexp_like kanske inte finns i alla databashanteringssystem. Dess syntax och funktionalitet kan variera i andra system.

Här är ett exempel för din bättre förståelse.

# Query using the LIKE clause
SELECT * FROM mytable WHERE ( name LIKE 'A%' OR name LIKE 'B%' ); # Query using regexp_like clause
SELECT * FROM mytable WHERE regexp_like(name, '^[AB].*');

Ovanstående frågor används för att hitta de element som namnet börjar med A eller B. I det första exemplet, LIKE används för att söka efter alla namn som börjar med A eller B. A% betyder att det första tecknet är A; efter det kan valfritt antal tecken vara närvarande. I det andra exemplet, regexp_like är använd. Inuti ^[AB], ^ representerar att symbolen kommer att matcha i början av strängen, [AB] representerar att det första tecknet kan vara A eller B, och .* representerar alla tecken efter det.

Använda regexp_like, kan databasen snabbt filtrera bort de rader som inte matchar mönstret, vilket förbättrar prestandan och minskar resursanvändningen.

I den här artikeln har vi diskuterat olika metoder och tips för att optimera SQL-frågan. Den här artikeln ger dig en tydlig förståelse för hur du skriver effektiva SQL-frågor och vikten av att optimera dem. Det finns många fler sätt att optimera frågorna, som att föredra användningen av heltalsvärden snarare än tecken eller använda Union All istället för Union när din tabell inte innehåller dubbletter, etc.

Ariska Garg är en B.Tech. Elektroteknikstudent, går för närvarande sista året av sin grundexamen. Hans intresse ligger inom området webbutveckling och maskininlärning. Han har följt detta intresse och är angelägen om att arbeta mer i dessa riktningar.