Tecniche di ottimizzazione delle query SQL

Ripubblicato da Platone

Seguaci: 0

Tecniche di ottimizzazione delle query SQL
Immagine dell'autore

A livello principiante, ci concentriamo solo sulla scrittura e l'esecuzione delle query SQL. Non ci preoccupiamo di quanto tempo ci vuole per l'esecuzione o se può gestire milioni di record. Ma a livello intermedio, le persone si aspettano che la tua query sia ottimizzata e richieda un tempo minimo per l'esecuzione.

Scrivere una query ottimizzata in applicazioni di grandi dimensioni con milioni di record, come piattaforme di e-commerce o sistemi bancari, è fondamentale. Supponiamo che possiedi un'azienda di e-commerce con più di un milione di prodotti e che un cliente desideri cercare un prodotto. Cosa succede se la query che hai scritto nel backend impiega più di un minuto per recuperare quel prodotto dal database? Pensi che i clienti acquisteranno prodotti dal tuo sito web?

Devi capire l'importanza dell'ottimizzazione delle query SQL. In questo tutorial, ti mostrerò alcuni suggerimenti e trucchi per ottimizzare le tue query SQL e farle eseguire più velocemente. Il prerequisito principale è che devi avere una conoscenza di base di SQL.

Per verificare se un elemento specifico è presente nella tabella, utilizzare il EXIST() parola chiave invece di COUNT() eseguirà la query in modo più ottimizzato.

utilizzando COUNT(), la query deve contare tutte le occorrenze di quel particolare elemento che potrebbe essere inefficiente quando il database è esteso. D'altra parte, EXIST() controllerà solo la prima occorrenza di quell'elemento e poi si fermerà quando trova la prima occorrenza. Ciò consente di risparmiare molto tempo.

Inoltre, ti interessa solo scoprire se un particolare elemento è presente o meno. Non sei interessato a trovare il numero di occorrenze. Anche per questo EXIST() è migliore.

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

La query precedente restituirà 1 se almeno una riga della tabella contiene una voce in cui una colonna denominata myColumn ha un valore pari a val. In caso contrario, tornerà 0.

Entrambi char ed varchar i tipi di dati vengono utilizzati per memorizzare stringhe di caratteri nella tabella. Ma varchar è molto più efficiente in termini di memoria rispetto a char.

Il tipo di dati char può memorizzare solo la stringa di caratteri di lunghezza fissa definita. Se la lunghezza della stringa è inferiore alla lunghezza fissa, riempirà gli spazi vuoti per rendere la sua lunghezza uguale alla lunghezza impostata. Ciò sprecherà inutilmente memoria nel riempimento. Per esempio,CHAR(100) occuperà 100 byte di memoria anche se viene memorizzato un singolo carattere.

D'altra parte, il tipo di dati varchar memorizza la stringa di caratteri di lunghezza variabile con una lunghezza inferiore alla lunghezza massima specificata. Non riempie gli spazi vuoti e occupa solo la memoria pari alla lunghezza effettiva della stringa. Per esempio, VARCHAR(100) occupa solo 1 byte di memoria quando memorizza un singolo carattere.

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

Nell'esempio precedente, una tabella myTable viene creato con due colonne, charCol ed varcharCol con tipi di dati char e varchar rispettivamente. charCol richiederà sempre 10 byte di memoria. In contrasto, varcharCol richiede memoria pari alla dimensione effettiva della stringa di caratteri in esso memorizzata.

Dobbiamo evitare di utilizzare subquery all'interno della clausola WHERE per ottimizzare una query SQL. Poiché le sottoquery possono essere costose e difficili da eseguire quando restituiscono un numero elevato di righe.

Invece di utilizzare la sottoquery, è possibile ottenere lo stesso risultato utilizzando un'operazione di join o scrivendo una sottoquery correlata. Una sottoquery correlata è una sottoquery in cui la query interna dipende dalla query esterna. E sono molto efficienti rispetto alle sottoquery non correlate.

Di seguito è riportato un esempio per comprendere la differenza tra i due.

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

Nel primo esempio, la sottoquery raccoglie prima tutti gli ID cliente che appartengono a INDIA, quindi la query esterna otterrà tutti gli ordini degli ID cliente selezionati. E nel secondo esempio, abbiamo ottenuto lo stesso risultato unendo il file customers ed orders tabelle e quindi selezionando solo gli ordini in cui i clienti appartengono dall'INDIA.

In questo modo possiamo ottimizzare la query evitando l'uso di subquery all'interno della clausola WHERE e rendendole più facili da leggere e comprendere.

Applicare il JOIN operazione da una tabella più grande a una tabella più piccola è una tecnica di ottimizzazione SQL comune. Perché l'unione da una tabella più grande a una tabella più piccola renderà la tua query più veloce. Se applichiamo a JOIN operazione da una tabella più piccola a una tabella più grande, il nostro motore SQL deve cercare in una tabella più grande le righe corrispondenti. Questo è più dispendioso in termini di risorse e richiede tempo. Ma d'altra parte, se il JOIN viene applicato da una tabella più grande a una tabella più piccola, il motore SQL deve cercare in una tabella più piccola le righe corrispondenti.

Ecco un esempio per una migliore comprensione.

# 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

Diversamente dal LIKE clausola, regexp_like viene utilizzato anche per la ricerca di modelli. IL LIKE clausola è un operatore di base di corrispondenza del modello che può eseguire solo operazioni di base come _ or %, che vengono utilizzati per corrispondere rispettivamente a un singolo carattere o a qualsiasi numero di caratteri. IL LIKE La clausola deve eseguire la scansione dell'intero database per trovare il modello particolare, che è lento per tabelle di grandi dimensioni.

D'altro canto, regexp_like è una tecnica di ricerca di modelli più efficiente, ottimizzata e potente. Utilizza espressioni regolari più complesse per trovare modelli specifici in una stringa di caratteri. Queste espressioni regolari sono più specifiche della semplice corrispondenza con caratteri jolly perché consentono di cercare lo schema esatto che stiamo trovando. Per questo motivo, la quantità di dati da cercare è ridotta e la query viene eseguita più velocemente.

Tieni presente che regexp_like potrebbe non essere presente in tutti i sistemi di gestione dei database. La sua sintassi e funzionalità possono variare in altri sistemi.

Ecco un esempio per una migliore comprensione.

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

Le query precedenti vengono utilizzate per trovare gli elementi il cui nome inizia con A o B. Nel primo esempio, LIKE si usa per cercare tutti i nomi che iniziano con A o B. A% significa che il primo carattere è A; dopodiché, può essere presente un numero qualsiasi di caratteri. Nel secondo esempio, regexp_like si usa. Dentro ^[AB], ^ rappresenta che il simbolo corrisponderà all'inizio della stringa, [AB] rappresenta che il carattere iniziale può essere A o B, e .* rappresenta tutti i personaggi successivi.

utilizzando regexp_like, il database può filtrare rapidamente le righe che non corrispondono al modello, migliorando le prestazioni e riducendo l'utilizzo delle risorse.

In questo articolo, abbiamo discusso vari metodi e suggerimenti per ottimizzare la query SQL. Questo articolo fornisce una chiara comprensione di come scrivere query SQL efficienti e dell'importanza di ottimizzarle. Esistono molti altri modi per ottimizzare le query, come preferire l'uso di valori interi piuttosto che caratteri o utilizzare Union All invece di Union quando la tabella non contiene duplicati, ecc.

Ariano Garg è un B.Tech. Studente di ingegneria elettrica, attualmente all'ultimo anno del suo corso di laurea. Il suo interesse risiede nel campo dello sviluppo Web e dell'apprendimento automatico. Ha perseguito questo interesse e sono ansioso di lavorare di più in queste direzioni.