Quando un utente invia una query, tipicamente scritta come un'istruzione in linguaggio di query strutturato (SQL), il database valuta diversi modi per recuperare i dati richiesti. Questo processo decisionale è gestito da un componente noto come query optimizer, che seleziona la strategia di esecuzione più efficiente.
I moderni sistemi di gestione dei database (DBMS) utilizzano ottimizzatori basati sui costi che stimano il costo delle diverse strategie di esecuzione prima di selezionare l'opzione più efficiente. A causa di questo processo, due query di database che producono risultati identici possono avere tempi di esecuzione molto diversi, spesso misurati in millisecondi, che influiscono sulle prestazioni delle query e sui tempi di risposta.
Resta al passo con le tendenze più importanti e interessanti del settore relative ad AI, automazione, dati e oltre con la newsletter Think. Leggi l' Informativa sulla privacy IBM.
L'ottimizzazione delle query (o ottimizzazione SQL) influisce molto più sulle prestazioni delle singole query. Determina l'efficienza di interi sistemi dati, modelli di machine learning e iniziative di intelligenza artificiale (AI) migliorando il modo in cui i sistemi scalano e utilizzano le risorse.
Le applicazioni si affidano ai database per recuperare le informazioni in modo rapido e coerente. Quando le query sono inefficienti, i database possono impiegare tempo inutile a eseguire scansioni di tabelle, ordinare record o unire grandi set di dati. Questi ritardi possono rallentare le application programming interface (API) e i workload di analytics, creando colli di bottiglia che degradano l'esperienza complessiva dell'utente.
Man mano che le organizzazioni raccolgono più dati, i database devono supportare workload sempre più complessi guidati dal grande volume, dalla varietà di tipi di dati e da modelli di query più impegnativi.
Con la datasphere globale prevista per raggiungere 393,9 zettabyte entro il 2028, le query che un tempo processavano migliaia di righe potrebbero infine processarne milioni o miliardi. L'ottimizzazione delle query migliora la scalabilità consentendo query efficienti, anche se la quantità di dati e la complessità dei workload aumentano.
Piani di esecuzione efficienti riducono anche le risorse necessarie per elaborare le query. Ogni operazione di database richiede risorse di sistema per elaborare i dati, inclusi i cicli dell'unità centrale di elaborazione (CPU) e l'input/output (I/O) del disco.
Le query mal ottimizzate sono ad alto consumo di risorse e richiedono un'elaborazione molto superiore a quella necessaria per produrre lo stesso risultato. Questo aumento del consumo di risorse può essere costoso negli ambienti cloud dove l'uso delle risorse influisce direttamente sul prezzo.
Le moderne piattaforme dati che supportano il machine learning, real-time analytics, Retrieval-augmented generation (RAG) e AI dipendono da un accesso rapido e affidabile a grandi volumi di dati. L'ottimizzazione delle query aiuta a garantire che questi sistemi possano recuperare le informazioni rilevanti abbastanza rapidamente da supportare decisioni in tempo reale senza compromettere i budget.
Gli ottimizzatori di database possono utilizzare diversi approcci per valutare le potenziali strategie di esecuzione. I primi sistemi di database utilizzavano spesso l'ottimizzazione basata su regole, che applicava regole predefinite per determinare i piani di esecuzione in base alla struttura delle query.
I DBMS moderni danno tipicamente priorità all'ottimizzazione basata sui costi, che valuta molteplici strategie di esecuzione possibili e stima le risorse necessarie per ciascuna. Alcuni sistemi incorporano anche tecniche basate su euristiche, che applicano linee guida pratiche per semplificare la pianificazione delle query e ridurre il sovraccarico di ottimizzazione.
Indipendentemente dall'approccio di ottimizzazione adottato, diversi concetti tecnici influenzano il modo in cui gli ottimizzatori valutano le potenziali strategie di esecuzione, tra cui:
Gli ottimizzatori di query sono il componente del database responsabile della selezione di piani di esecuzione efficienti, spesso utilizzando tecniche di ottimizzazione basate sui costi. Nei database relazionali, questo processo aiuta il motore del database a determinare il modo più efficiente per eseguire una SQL Query.
Invece di dipendere da regole fisse, gli ottimizzatori basati sui costi analizzano le caratteristiche dei dati e la struttura delle query per determinare l'approccio più efficiente. Questa flessibilità consente ai database di adattare le strategie di esecuzione in base all'evoluzione dei set di dati e dei workload.
Gli ottimizzatori si affidano fortemente alle statistiche del database per stimare quanto saranno costosi i diversi piani di esecuzione. Le statistiche descrivono caratteristiche chiave dei dati memorizzati, tra cui:
Queste statistiche permettono all'ottimizzatore di stimare quante righe restituirà una query e quanto lavoro richiedono le diverse strategie di esecuzione. Se le statistiche diventano obsolete o inaccurate, l'ottimizzatore può selezionare piani di esecuzione inefficienti.
La stima della cardinalità si riferisce alla previsione di quanti risultati risulteranno da ogni passaggio di una query. Ad esempio, se una query filtra le righe utilizzando clausole WHERE come:
WHERE region = 'Nord America'
L'ottimizzatore deve stimare quanti record corrispondono a quel filtro.
Queste stime influenzano diverse decisioni chiave. L'ottimizzatore può usarli per determinare l'ordine in cui le tabelle devono essere unite, gli ordini di join più efficienti, quali algoritmi di join utilizzare o se si debba utilizzare una scansione indice invece di scansionare una tabella completa.
Gli indici consentono ai database di individuare dati specifici in modo più efficiente rispetto alla scansione di intere tabelle. Gli ottimizzatori utilizzano indici per ridurre la quantità di lavoro richiesta per il recupero dei dati.
I percorsi di accesso comuni includono scansioni complete delle tabelle che leggono ogni riga di una tabella, scansioni indicizzate che leggono le righe attraverso una struttura indicizzata, le ricerche indice che recuperano righe specifiche tramite consultazioni indicizzate e scansioni solo indicizzate, che recuperano i dati direttamente dall'indice senza accedere alla tabella sottostante.
La scelta del percorso di accesso corretto può ridurre significativamente la quantità di lavoro necessaria per eseguire una query, in particolare quando si lavora con tabelle di grandi dimensioni.
Molte query recuperano dati da più tabelle. Quando ciò accade, l'ottimizzatore deve determinare come combinare queste tabelle. Gli algoritmi di join comuni includono:
L'ottimizzatore seleziona tra questi algoritmi in base a fattori come la dimensione dei dati, gli indici disponibili e il numero di righe stimato.
Per capire come funziona l'ottimizzazione delle query, è utile think a SQL come a un linguaggio dichiarativo: descrive quali dati dovrebbero essere recuperati piuttosto che come tali dati dovrebbero essere recuperati.
L'ottimizzatore è responsabile di determinare come eseguire la richiesta, e nel modo più efficiente. Per raggiungere questo obiettivo, la maggior parte dei database segue diverse fasi di ottimizzazione:
Quando viene inviata una query, il database analizza innanzitutto l'istruzione SQL e ne convalida la sintassi. Durante questa fase, il sistema conferma che esistono tabelle, colonne e indici di riferimento e che la struttura della query è valida.
Verifica inoltre che siano disponibili gli oggetti pertinenti nello schema del database. Questo passaggio garantisce che il database comprenda la richiesta prima di tentare di ottimizzarla o eseguirla.
Dopo il parsing, il database può riscrivere la query in una forma equivalente che può essere eseguita in modo più efficiente. Queste trasformazioni preservano i risultati della query migliorandone la struttura di esecuzione. Le tecniche comuni di riscrittura delle query includono:
Queste trasformazioni permettono all'ottimizzatore di esplorare strategie di esecuzione più efficienti senza alterare il risultato finale. Possono anche aiutare a limitare il trattamento dei dati non necessari.
Una volta riscritta la query, l'ottimizzatore genera molteplici piani di esecuzione potenziali. Ogni piano rappresenta una strategia diversa per recuperare i dati richiesti.
I piani possono differire in base agli indici utilizzati, all'ordine di unione delle tabelle o al modo in cui vengono elaborati i risultati intermedi. Anche query relativamente semplici possono produrre diverse possibili strategie di esecuzione.
Ad esempio, una singola query che recupera gli ordini della settimana precedente ha diverse opzioni: può scansionare la tabella degli ordini e filtrare le righe dopo, usare un indice sulla data dell'ordine per localizzare rapidamente i record recenti o restringere prima il dataset prima di unirsi alle tabelle relative di clienti o prodotti.
L'ottimizzatore valuta quindi ogni piano candidato utilizzando un modello di costi. I modelli di costo stimano la quantità di lavoro che il database dovrà svolgere per eseguire un particolare piano. Queste stime in genere tengono conto di fattori quali:
Poiché il database non può conoscere in anticipo il costo esatto, si basa sulle informazioni statistiche memorizzate sui dati. Queste informazioni aiutano l'ottimizzatore a stimare il probabile tempo di elaborazione e a determinare quale algoritmo e struttura dati di supporto siano più appropriati.
Dopo aver valutato i piani candidati, l'ottimizzatore seleziona il piano con il costo stimato più basso. Questa strategia selezionata diventa il piano di esecuzione delle query, che descrive la sequenza di operazioni che il database esegue durante l'esecuzione delle query.
Un piano di esecuzione efficiente include in genere operazioni come scansioni di tabelle, join, ordinamento e aggregazioni (ad esempio, utilizzando GROUP BY o LEFT JOIN). Gli utenti possono consultare i piani EXPLAIN per vedere i passaggi che l'ottimizzatore compie per recuperare i dati richiesti.
Nonostante la sofisticazione dei moderni ottimizzatori di database, diversi fattori possono rendere difficile l'ottimizzazione delle query.
Sebbene l'ottimizzazione delle query avvenga automaticamente, gli sviluppatori, gli amministratori e i data engineer possono migliorare le prestazioni attraverso diverse tecniche di ottimizzazione.
Gli indici possono migliorare in modo significativo le prestazioni delle query quando supportano i filtri o le condizioni di join utilizzati di frequente. Indici ben progettati permettono all'ottimizzatore di recuperare rapidamente righe specifiche senza dover scansionare intere tabelle. Tuttavia, un'indicizzazione eccessiva può introdurre sovraccarico durante gli aggiornamenti dei dati. Gli indici dovrebbero quindi essere progettati con cura per bilanciare le prestazioni di lettura e l'efficienza di scrittura.
Poiché gli ottimizzatori utilizzano le statistiche per stimare i costi delle query, è fondamentale mantenere aggiornate le statistiche per garantire piani di esecuzione efficienti. L'aggiornamento regolare delle statistiche garantisce che l'ottimizzatore abbia informazioni accurate sulle distribuzioni dei dati e sulle dimensioni delle tabelle.
Applicare filtri in precedenza nell'esecuzione della query riduce il numero di righe che devono essere elaborate successivamente nella query. Risultati intermedi più piccoli possono aiutare ad accelerare l'esecuzione delle query. Per questo motivo, le query che applicano filtri selettivi precocemente spesso funzionano in modo più efficiente.
Le query che combinano molte tabelle possono produrre query complesse e piani di esecuzione altrettanto complessi. Quando i join non sono necessari o ridondanti, rimuoverli può ridurre significativamente la complessità dell'esecuzione. In alcuni casi, la denormalizzazione può anche migliorare le prestazioni riducendo la necessità di join, anche se può aumentare l'uso dello storage e la ridondanza dei dati.
Le query che recuperano colonne non necessarie aumentano la quantità di dati che devono essere letti ed elaborati. Limitare i set di risultati ai soli campi richiesti riduce l'uso di memoria e le operazioni di I/O del disco. Questo piccolo aggiustamento può migliorare notevolmente le prestazioni in set di dati di grandi dimensioni.
In alcuni ambienti, il partizionamento può aiutare a suddividere tabelle molto grandi in segmenti più gestibili, mentre la memorizzazione nella cache può ridurre le operazioni ripetute sul database per i risultati a cui si accede frequentemente. Questi approcci non sono correzioni universali, ma possono integrare altre strategie di ottimizzazione.
Molte piattaforme di database forniscono anche strumenti integrati che aiutano sviluppatori e amministratori ad analizzare le prestazioni delle query e a identificare piani di esecuzione inefficienti.
Ad esempio, SQL Server Management Studio (SSMS) può aiutare a monitorare le prestazioni delle query e identificare i colli di bottiglia; MySQL Workbench fornisce strumenti per analizzare i piani di query e ottimizzare l'esecuzione; e Oracle SQL Tuning Advisor può generare raccomandazioni automatiche per migliorare le query SQL.
L'ottimizzazione delle query e la mise a punto delle query sono strettamente correlate ma rappresentano processi diversi.
L'ottimizzazione delle query si riferisce ai processi automatizzati utilizzati dai database per determinare strategie di esecuzione efficienti.
La messa a punto delle query, al contrario, si riferisce a sforzi manuali per migliorare le prestazioni delle query. Questi sforzi possono includere la riscrittura di query inefficienti, la creazione di nuovi indici, l'aggiornamento delle statistiche o l'adattamento delle impostazioni della configurazione del database.
In pratica, l'ottimizzazione delle query e la mise a punto delle query spesso lavorano insieme per migliorare le prestazioni del database. Insieme, formano un insieme pratico di strategie di ottimizzazione per migliorare le prestazioni SQL nei sistemi di produzione.
L'ottimizzazione delle query si sta evolvendo oltre la tradizionale pianificazione basata sui costi. I moderni sistemi di database incorporano ora l'automazione, l'esecuzione adattiva e l'intelligenza artificiale per migliorare il modo in cui le query vengono analizzate ed eseguite.
Una direzione emergente è lo sviluppo di funzionalità autonome di database, in cui i sistemi monitorano continuamente le prestazioni e rispondono automaticamente ai problemi. Invece di affidarsi interamente al troubleshooting reattivo, questi sistemi analizzano il comportamento del workload, le prestazioni e i segnali di sistema per identificare precocemente potenziali problemi di prestazione e raccomandare azioni correttive.
Molte architetture di database autonome organizzano queste funzionalità in tre aree operative, spesso alimentate da agenti AI.
Queste funzionalità agentiche sono progettate per funzionare all'interno di un modello human-in-the-loop, in cui l'automazione gestisce attività operative ben definite mentre i team del database mantengono la supervisione dei sistemi critici.
Man mano che le organizzazioni continuano a scalare le piattaforme dati e ad adottare applicazioni basato sull'AI, i sistemi in grado di monitorare, ottimizzare e mantenere la propria autonomia giocheranno un ruolo sempre più importante nel garantire prestazioni affidabili del database.
Watsonx.data ti consente di scalare analytics e AI con tutti i tuoi dati, ovunque risiedano, attraverso uno storage dei dati aperto, ibrido e governato.
Esegui applicazioni, analytics e AI generativa con database presenti su qualsiasi cloud.
Scala con successo l'AI con la strategia, la sicurezza, la governance e i dati giusti.