SQL Query Optimization Techniques

Republicat de Platon

Urmaritori: 0

Tehnici de optimizare a interogărilor SQL
Imagine de autor

La nivel de începător, ne concentrăm doar pe scrierea și rularea interogărilor SQL. Nu ne deranjam cu privire la cât timp durează executarea sau dacă poate gestiona milioane de înregistrări. Dar, la nivel intermediar, oamenii se așteaptă ca interogarea dvs. să fie optimizată și să aibă nevoie de timp minim pentru execuție.

Scrierea unei interogări optimizate în aplicații mari cu milioane de înregistrări, cum ar fi platformele de comerț electronic sau sistemele bancare, este imperativă. Să presupunem că dețineți o companie de comerț electronic cu mai mult de un milion de produse și un client dorește să caute un produs. Ce se întâmplă dacă interogarea pe care ați scris-o în backend durează mai mult de un minut pentru a prelua acel produs din baza de date? Crezi că clienții cumpără produse de pe site-ul tău?

Trebuie să înțelegeți importanța optimizării interogărilor SQL. În acest tutorial, vă voi arăta câteva sfaturi și trucuri pentru a vă optimiza interogările SQL și pentru a le face să se execute mai rapid. Condiția prealabilă principală este că trebuie să aveți cunoștințe de bază despre SQL.

Pentru a verifica dacă un anumit element este prezent în tabel, utilizați EXIST() cuvânt cheie în loc de COUNT() va rula interogarea într-un mod mai optimizat.

Utilizarea COUNT(), interogarea trebuie să numere toate aparițiile acelui element special care pot fi ineficiente atunci când baza de date este extinsă. Pe de altă parte, EXIST() va verifica doar prima apariție a acelui element și apoi se va opri când va găsi prima apariție. Acest lucru economisește mult timp.

De asemenea, sunteți interesat doar să aflați dacă un anumit element este prezent sau nu. Nu vă interesează să aflați numărul de apariții. De aceea și EXIST() este mai bine.

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

Interogarea de mai sus va reveni 1 dacă cel puțin un rând de tabel conține o intrare în care o coloană numită myColumn are o valoare egală cu val. În caz contrar, se va întoarce 0.

Ambele char și varchar tipurile de date sunt folosite pentru a stoca șiruri de caractere în tabel. Dar varchar este mult mai eficient în memorie decât char.

Tipul de date char poate stoca doar șirul de caractere de lungime fixă definită. Dacă lungimea șirului este mai mică decât lungimea fixă, atunci va umple spațiile goale pentru ca lungimea sa să fie egală cu lungimea setată. Acest lucru va risipi în mod inutil memorie în umplutură. De exemplu,CHAR(100) va lua 100 de octeți de memorie chiar dacă este stocat un singur caracter.

Pe de altă parte, varchar datatype stochează șirul de caractere de lungime variabilă având o lungime mai mică decât lungimea maximă specificată. Nu completează spațiile goale și ia doar o memorie egală cu lungimea reală a șirului. De exemplu, VARCHAR(100) ia doar 1 octet de memorie atunci când stochează un singur caracter.

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

În exemplul de mai sus, un tabel myTable este creat având două coloane, charCol și varcharCol având tipuri de date char și, respectiv, varchar. charCol va lua întotdeauna 10 octeți de memorie. În contrast, varcharCol ia memorie egală cu dimensiunea reală a șirului de caractere stocat în el.

Trebuie să evităm utilizarea subinterogărilor în cadrul clauzei WHERE pentru a optimiza o interogare SQL. Deoarece subinterogările pot fi costisitoare și dificil de executat atunci când returnează un număr mare de rânduri.

În loc să utilizați subinterogarea, puteți obține același rezultat utilizând o operație de îmbinare sau scriind o subinterogare corelată. O subinterogare corelată este o subinterogare în care interogarea interioară depinde de interogarea exterioară. Și sunt foarte eficiente în comparație cu subinterogarile necorelate.

Mai jos este un exemplu pentru a înțelege diferența dintre cele două.

# 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';

În primul exemplu, subinterogarea colectează mai întâi toate ID-urile clienților care aparțin INDIA, iar apoi interogarea exterioară va obține toate comenzile ID-urilor clientului selectate. Și în al 1-lea exemplu, am obținut același rezultat prin aderarea la customers și orders tabele și apoi selectând numai comenzile de care aparțin clienții din INDIA.

În acest fel, putem optimiza interogarea evitând utilizarea subinterogărilor în cadrul clauzei WHERE și făcându-le mai ușor de citit și de înțeles.

Aplicarea JOIN operarea de la un tabel mai mare la un tabel mai mic este o tehnică comună de optimizare SQL. Deoarece unirea de la un tabel mai mare la un tabel mai mic va face ca interogarea să se execute mai rapid. Dacă aplicăm a JOIN de la un tabel mai mic la un tabel mai mare, motorul nostru SQL trebuie să caute într-un tabel mai mare rândurile care se potrivesc. Acest lucru necesită mai mult resurse și consumă mai mult timp. Dar, pe de altă parte, dacă JOIN se aplică de la un tabel mai mare la un tabel mai mic, apoi motorul SQL trebuie să caute într-un tabel mai mic rândurile care se potrivesc.

Iată un exemplu pentru o mai bună înțelegere.

# 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

Spre deosebire de LIKE clauză, regexp_like este folosit și pentru căutarea modelelor. The LIKE clauza este un operator de bază de potrivire a modelelor care poate efectua numai operațiuni de bază, cum ar fi _ or %, care sunt folosite pentru a potrivi un singur caracter sau, respectiv, orice număr de caractere. The LIKE clauza trebuie să scaneze întreaga bază de date pentru a găsi modelul particular, care este lent pentru tabele mari.

Pe de altă parte, regexp_like este o tehnică de căutare a modelelor mai eficientă, optimizată și mai puternică. Folosește expresii regulate mai complexe pentru a găsi modele specifice într-un șir de caractere. Aceste expresii regulate sunt mai specifice decât potrivirea cu caractere joker, deoarece vă permit să căutați modelul exact pe care îl găsim. Datorită acestui fapt, cantitatea de date care trebuie căutată este redusă, iar interogarea se execută mai rapid.

Vă rugăm să reţineţi că regexp_like este posibil să nu fie prezent în toate sistemele de gestionare a bazelor de date. Sintaxa și funcționalitatea sa pot varia în alte sisteme.

Iată un exemplu pentru o mai bună înțelegere.

# 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].*');

Interogările de mai sus sunt folosite pentru a găsi elementele a căror denumire începe cu A sau B. În primul exemplu, LIKE este folosit pentru a căuta toate numele care încep cu A sau B. A% înseamnă că primul caracter este A; după aceea, poate fi prezent orice număr de caractere. În al doilea exemplu, regexp_like este folosit. Interior ^[AB], ^ reprezintă faptul că simbolul se va potrivi la începutul șirului, [AB] reprezintă faptul că caracterul de început poate fi A sau B și .* reprezintă toate personajele de după aceea.

Utilizarea regexp_like, baza de date poate filtra rapid rândurile care nu se potrivesc cu modelul, îmbunătățind performanța și reducând utilizarea resurselor.

În acest articol, am discutat despre diferite metode și sfaturi pentru a optimiza interogarea SQL. Acest articol vă oferă o înțelegere clară a modului de a scrie interogări SQL eficiente și a importanței optimizării acestora. Există multe mai multe modalități de optimizare a interogărilor, cum ar fi preferarea utilizării valorilor întregi mai degrabă decât a caracterelor sau utilizarea Union All în loc de Union atunci când tabelul dvs. nu conține duplicate etc.

Arian Garg este un B.Tech. Student în inginerie electrică, în prezent în ultimul an de licență. Interesul său se află în domeniul dezvoltării web și al învățării automate. El a urmărit acest interes și sunt dornic să lucreze mai mult în aceste direcții.