L'ottimizzazione nei Sistemi di Gestione di Basi di Dati (DBMS) comprende diverse tecniche finalizzate a migliorare le prestazioni delle query, l'utilizzo delle risorse e l'efficienza complessiva del sistema.

Ottimizzatori di query

Gli ottimizzatori di query nei sistemi di gestione di basi di dati (DBMS) sono componenti fondamentali responsabili dell'analisi delle query SQL e della generazione di piani di esecuzione efficienti.
L'obiettivo è minimizzare il tempo di risposta della query considerando vari fattori come indici disponibili, statistiche sui dati e algoritmi per l'accesso e l'elaborazione dei dati, migliorando così le prestazioni complessive delle operazioni sul database.

Gli ottimizzatori di query sono integrati nei DBMS e operano in modo indipendente per migliorare le prestazioni.
Tuttavia, gli utenti possono comunque contribuire all'ottimizzazione affinando le query e applicando strategie di indicizzazione appropriate, migliorando ulteriormente le prestazioni del database.

Tecniche di riscrittura delle query

1. Specificare esplicitamente le colonne: Invece di utilizzare il carattere jolly (*), indicare esplicitamente i nomi delle colonne nelle query per ottenere migliori prestazioni, leggibilità e manutenibilità;

2. Minimizzare le subquery: Ridurre l'uso delle subquery per ottimizzare le prestazioni delle query. Considerare alternative come join o tabelle derivate per evitare complessità e sovraccarico;

3. Evitare l'uso ripetuto dell'operatore IN: Limitare l'uso dell'operatore IN nelle query per prevenire impatti sulle prestazioni. In alternativa, considerare l'utilizzo di clausole JOIN o EXISTS per piani di esecuzione più efficienti;

4. Organizzare i join in modo logico: Iniziare i join SQL dalla tabella principale e poi unire le tabelle correlate per ottimizzare l'organizzazione della query e l'ottimizzazione del motore di database;

5. Utilizzare condizioni WHERE restrittive: Migliorare le prestazioni delle query includendo condizioni restrittive nella clausola WHERE per filtrare le righe e aumentare la velocità di esecuzione;

6. Rifattorizzare il codice in stored procedure o funzioni: Incapsulare segmenti di codice ripetitivo in stored procedure o funzioni definite dall'utente per la riutilizzabilità del codice, la modularità e una manutenzione più semplice. Queste possono ridurre la ridondanza e ottimizzare le query SQL.

Partizionamento dei dati

Il partizionamento dei dati è una tecnica di ottimizzazione del database utilizzata per suddividere grandi tabelle o indici in segmenti più piccoli e gestibili chiamati partizioni. Ogni partizione contiene un sottoinsieme dei dati e opera in modo indipendente, consentendo un miglioramento delle prestazioni delle query, una gestione dei dati più efficiente e una maggiore scalabilità.

Nota

Presta attenzione al fatto che la partizione dei dati e la replica dei dati sono due processi distinti. Nella replica dei dati, vengono create diverse copie degli stessi dati, mentre nella partizione, si suddividono gli stessi dati e si memorizzano su server differenti.

Strategie di indicizzazione

L'indicizzazione può essere utile per migliorare le prestazioni delle query consentendo un recupero dei dati più rapido in alcuni casi. Tuttavia, un uso indiscriminato degli indici può portare a sovraccarico del sistema e a una diminuzione delle prestazioni.

Ecco alcune raccomandazioni per utilizzare efficacemente gli indici:

• Analisi dei pattern delle query: Identificazione delle query eseguite frequentemente e di quelle che coinvolgono grandi set di dati. Applicazione degli indici alle colonne utilizzate frequentemente nelle condizioni di ricerca o nelle operazioni di join;

• Considerazione della distribuzione dei dati: Comprensione della distribuzione dei dati all'interno delle colonne indicizzate. Per colonne con bassa cardinalità, come campi booleani o di genere, l'indicizzazione potrebbe non essere vantaggiosa. Al contrario, per colonne altamente selettive, come chiavi primarie o identificatori univoci, l'indicizzazione può migliorare significativamente le prestazioni;

• Bilanciamento tra operazioni di lettura e scrittura: Utilizzo degli indici su colonne lette frequentemente per velocizzare le operazioni di lettura. Tuttavia, evitare di aggiungere indici su colonne modificate frequentemente, poiché possono rallentare le operazioni di scrittura a causa dell'overhead aggiuntivo;

• Evitare l'eccessiva indicizzazione: Creare indici su ogni colonna o indicizzare eccessivamente le tabelle può portare a un aumento dei requisiti di archiviazione, a un maggiore onere di manutenzione e a una diminuzione delle prestazioni. Dare priorità all'indicizzazione delle colonne cruciali per le prestazioni delle query.

Denormalizzazione

La denormalizzazione è una tecnica di ottimizzazione dei database che mira a migliorare le prestazioni delle query introducendo strategicamente ridondanza nelle tabelle. A differenza della normalizzazione, che dà priorità all'eliminazione della ridondanza e all'integrità dei dati suddividendo le tabelle in entità più piccole e correlate, la denormalizzazione aggiunge deliberatamente dati duplicati. Questa ridondanza aiuta a ridurre la necessità di join complessi e di operazioni costose durante le query, con conseguente aumento delle prestazioni, soprattutto per attività con prevalenza di lettura.