SQL Query Optimization Techniques

Publisert av Platon

Følgere: 0

Optimaliseringsteknikker for SQL-spørring
Bilde av forfatter

På nybegynnernivå fokuserer vi kun på å skrive og kjøre SQL-spørringene. Vi bryr oss ikke om hvor mye tid det tar å utføre eller om det kan håndtere millioner av poster. Men på mellomnivå forventer folk at søket ditt er optimalisert og tar minimum tid å utføre.

Å skrive en optimalisert spørring i store applikasjoner med millioner av poster, som e-handelsplattformer eller banksystemer, er avgjørende. Tenk deg at du eier et e-handelsselskap med mer enn en million produkter, og en kunde ønsker å søke etter et produkt. Hva om spørringen du skrev i backend tar mer enn ett minutt å hente det produktet fra databasen? Vil du tro at kundene kjøper produkter fra nettstedet ditt?

Du må forstå viktigheten av SQL-spørringsoptimalisering. I denne opplæringen vil jeg vise deg noen tips og triks for å optimalisere SQL-spørringene dine og få dem til å utføres raskere. Den primære forutsetningen er at du må ha grunnleggende kunnskap om SQL.

For å sjekke om et spesifikt element er til stede i tabellen, bruk EXIST() søkeord i stedet for COUNT() vil kjøre spørringen på en mer optimalisert måte.

Ved hjelp av COUNT(), må spørringen telle alle forekomster av det bestemte elementet som kan være ineffektivt når databasen er omfattende. På den andre siden, EXIST() vil bare sjekke den første forekomsten av det elementet og deretter stoppe når den finner den første forekomsten. Dette sparer mye tid.

Dessuten er du bare interessert i å finne ut om et bestemt element er til stede eller ikke. Du er ikke interessert i å finne antall forekomster. Det er derfor også EXIST() er bedre.

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

Spørsmålet ovenfor vil returnere 1 hvis minst én tabellrad inneholder en oppføring hvor en kolonne heter myColumn har en verdi lik val. Ellers kommer den tilbake 0.

Begge char og varchar datatyper brukes til å lagre tegnstrenger i tabellen. Men varchar er mye mer minneeffektiv enn char.

Tegndatatypen kan bare lagre tegnstrengen med fast lengde som er definert. Hvis lengden på strengen er mindre enn den faste lengden, vil den fylle de tomme feltene for å gjøre lengden lik den angitte lengden. Dette vil unødvendig sløse med minne i polstring. For eksempel,CHAR(100) vil ta 100 byte minne selv om et enkelt tegn er lagret.

På den annen side lagrer varchar datatype tegnstrengen med variabel lengde som har en lengde som er mindre enn den maksimale lengden som er spesifisert. Den fyller ikke mellomrom og tar bare minnet lik strengens faktiske lengde. For eksempel, VARCHAR(100) tar bare 1 byte med minne når du lagrer et enkelt tegn.

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

I eksemplet ovenfor, en tabell myTable er opprettet med to kolonner, charCol og varcharCol har henholdsvis char og varchar datatyper. charCol vil alltid ta 10 byte minne. I motsetning, varcharCol tar minne lik den faktiske størrelsen på tegnstrengen som er lagret i den.

Vi må unngå å bruke underspørringer inne i WHERE-klausulen for å optimalisere en SQL-spørring. Siden underspørringene kan være dyre og vanskelige å utføre når de returnerer et stort antall rader.

I stedet for å bruke underspørringen, kan du få det samme resultatet ved å bruke en join-operasjon eller skrive en korrelert underspørring. En korrelert underspørring er en underspørring der den indre spørringen avhenger av den ytre spørringen. Og de er veldig effektive sammenlignet med ikke-korrelerte underspørringer.

Nedenfor er et eksempel for å forstå forskjellen mellom de to.

# 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ørste eksempelet samler underspørringen først alle kunde-ID-ene som tilhører INDIA, og deretter vil den ytre spørringen få alle bestillingene til de valgte kunde-ID-ene. Og i det andre eksemplet har vi oppnådd samme resultat ved å bli med i customers og orders tabeller og deretter velge kun bestillinger hvor kundene hører hjemme fra INDIA.

På denne måten kan vi optimalisere spørringen ved å unngå bruk av underspørringer inne i WHERE-klausulen og gjøre dem lettere å lese og forstå.

Bruk av JOIN operasjon fra en større tabell til en mindre tabell er en vanlig SQL-optimaliseringsteknikk. Fordi å bli med fra en større tabell til en mindre tabell vil få spørringen til å utføres raskere. Hvis vi bruker en JOIN operasjon fra en mindre tabell til en større tabell, må vår SQL-motor søke i en større tabell for samsvarende rader. Dette er mer ressurskrevende og tidkrevende. Men på den annen side, hvis JOIN brukes fra en større tabell til en mindre tabell, så må SQL-motoren søke i en mindre tabell for samsvarende rader.

Her er et eksempel for bedre forstå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

I motsetning til LIKE klausul, regexp_like brukes også til mønstersøking. De LIKE klausul er en grunnleggende mønstertilpasningsoperatør som bare kan utføre grunnleggende operasjoner som _ or %, som brukes til å matche et enkelt tegn eller et hvilket som helst antall tegn. De LIKE klausulen må skanne hele databasen for å finne det spesielle mønsteret, som er tregt for store tabeller.

På den annen side, regexp_like er en mer effektiv, optimalisert og kraftig mønstersøketeknikk. Den bruker mer komplekse regulære uttrykk for å finne spesifikke mønstre i en tegnstreng. Disse regulære uttrykkene er mer spesifikke enn enkle jokertegn-matching fordi de lar deg søke etter det nøyaktige mønsteret vi finner. På grunn av dette reduseres mengden data som må søkes, og spørringen utføres raskere.

Vær oppmerksom på at regexp_like finnes kanskje ikke i alle databasebehandlingssystemer. Syntaksen og funksjonaliteten kan variere i andre systemer.

Her er et eksempel for bedre forstå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].*');

Spørringene ovenfor brukes til å finne elementene som navnet starter med A eller B. I det første eksemplet, LIKE brukes til å søke etter alle navnene som begynner med A eller B. A% betyr at det første tegnet er A; etter det kan et hvilket som helst antall tegn være til stede. I det andre eksemplet, regexp_like benyttes. Innsiden ^[AB], ^ representerer at symbolet vil matche i begynnelsen av strengen, [AB] representerer at starttegnet kan være A eller B, og .* representerer alle tegnene etter det.

Ved hjelp av regexp_like, kan databasen raskt filtrere ut radene som ikke samsvarer med mønsteret, noe som forbedrer ytelsen og reduserer ressursbruken.

I denne artikkelen har vi diskutert ulike metoder og tips for å optimalisere SQL-spørringen. Denne artikkelen gir deg en klar forståelse av hvordan du skriver effektive SQL-spørringer og viktigheten av å optimalisere dem. Det er mange flere måter å optimalisere spørringene på, som å foretrekke bruken av heltallsverdier i stedet for tegn eller bruke Union All i stedet for Union når tabellen ikke inneholder duplikater, osv.

Ariske Garg er en B.Tech. Elektroingeniørstudent, går for tiden siste året av undergraden. Hans interesse ligger innen webutvikling og maskinlæring. Han har fulgt denne interessen og er ivrig etter å jobbe mer i disse retningene.