In che modo un piano di esecuzione della query influisce sull'esecuzione della query?

Se sei un programmatore, noterai che SQL non assomiglia molto al normale codice.

Il codice normale è scritto in uno stile noto come imperativo : dice al computer cosa fare. SQL, d'altra parte, non dice al database cosa fare; dice al database ciò che si vuole invece e lo lascia al motore DBMS per capire cosa fare per recuperarlo. Questo è noto come codice di stile dichiarativo e rende le query del database molto più semplici. Ma in realtà non dice al database cosa fare.

Quando il database riceve una query SQL, la prima cosa che deve fare è capire come prendere una richiesta che dice "Voglio questi dati" e trasformarla in un modo per recuperare quei dati in modo ragionevolmente efficiente. Quindi guarda attraverso la query e le tabelle interessate e si costruisce un insieme di comandi imperativi da eseguire. Questo è il piano di query . Poiché la creazione del piano di query può richiedere del tempo, la maggior parte dei DBMS li memorizzerà nella cache in determinate circostanze quando il motore ha motivo di aspettarsi che riutilizzi la stessa query.

Esistono modi per rivedere il piano di query e persino forzarlo a utilizzare determinati comandi imperativi in determinati punti della query, ma il meccanismo esatto tende a differire un po 'tra un DBMS e un altro. Per i dettagli, prova a cercare su Google "ottimizzazione del piano di query" oltre al nome del tuo motore di database; probabilmente troverai un bel po 'di informazioni.

La risposta di Mason Wheeler è molto buona. Ci sono alcuni altri punti da aggiungere:

Quando il motore di database esegue per primo una query, non viene solo con un piano di esecuzione. Ne vengono fuori molti. Escludendo bug, tutti questi piani di esecuzione sono logicamente equivalenti: vale a dire, genereranno tutti i risultati corretti per la query.

Tuttavia, ciò che li rende diversi è quanto tempo ci vorrà per eseguire il piano. Semplificando un po ', dirò "quanti accessi al disco" invece di "quanto tempo". Questo non è assolutamente vero, ma è vicino. Associato a ciascun piano di esecuzione è un costo stimato. Quello con il costo stimato più basso è quello che viene usato.

Ora, la tua domanda originale può essere riformulata come può fare un dba o un programmatore per fare in modo che Query Optimizer trovi un piano migliore e selezionarlo? La risposta è sì ... ma.

Prima di tutto, la progettazione dell'indice è fondamentale per i piani di esecuzione. Molto spesso, la presenza dell'indice corretto può comportare il rilevamento di query ottimizzatore e il prelievo di una strategia fino a cento volte più veloce. Ma devi stare attento. L'aggiunta di un indice rallenterà alcune altre operazioni del database e dovrai fare attenzione a non arrecare più danni che benefici.

Una seconda cosa che si può fare è analizzare frequentemente le tabelle. Questo è diverso per i diversi prodotti DBMS, ma generalmente l'analisi delle tabelle memorizza i dati sulla tabella che aiutano l'ottimizzatore a effettuare stime dei costi migliori.

Una terza cosa che si può fare è riscrivere le query per essere più logiche e dirette. A volte, i programmatori esperti imparano come scrivere SQL che legge come un linguaggio imperativo negativo invece di un buon linguaggio dichiarativo. Generalmente si tratta di non fidarsi dell'ottimizzatore per svolgere il proprio lavoro. A volte, un umano può superare in astuzia l'ottimizzatore. Più spesso ottieni risultati migliori semplicemente ottimizzando l'ottimizzazione.

Una quarta cosa che si può fare è fornire ciò che si chiamava "suggerimenti" in Oracle con la query. Non conosco la parola equivalente con altri prodotti. Non l'ho visto fare per 15 anni, ma suppongo che sia ancora possibile. Circa 20 anni fa, questa era quasi una necessità per fare le cose. Il mondo è cambiato molto da allora.

Il lungo e breve è che l'ottimizzatore è tuo amico, anche se imperfetto. Lascia che l'ottimizzatore ti aiuti.

La cosa più semplice da fare / un buon punto di partenza è l'Ottimizzazione guidata motore di database. In genere, otterrai suggerimenti sugli indici da applicare a tabelle specifiche.

Avrai comunque bisogno di valutare e testare quegli indici e soppesare qualsiasi vantaggio rispetto ad alcuni degli svantaggi che potresti incontrare (ad es. Inserimenti, Aggiornamenti ed Elimina rallentano a causa del sovraccarico di ricostruzione dell'indice.)

Strutturare correttamente i dati può anche fare molto per migliorare le prestazioni. Ti suggerirei di farlo prima di affrontare le impostazioni più avanzate nella tua query. I novizi potrebbero non essere in grado di notare le conseguenze indesiderate finché non è troppo tardi.

È possibile confrontare un piano di query con un programma compilato. Normalmente, ogni SQL riceverebbe un piano di query calcolato individualmente, il che, ovviamente, comporterà la compilazione di un overhead.

In una stored procedure o in una query SQL con parametri, la compilazione verrà eseguita una volta per tutte le query successive. Ciò richiede la separazione del codice SQL / procedura dai valori dei parametri effettivi. Il riutilizzo normalmente non funzionerà se si concatenano i valori dei parametri come stringhe nell'SQL, una cattiva pratica comunque. SQL Server riconosce l'SQL parametrizzato uguale (per lo più fornito attraverso la procedura sp_executesql) per l'uguaglianza delle stringhe, pertanto @parameterPlaceholders deve essere lo stesso, mentre i valori associati possono essere diversi. Le query parametrizzate sono, in termini di velocità e risorse, equivalenti alle stored procedure, tranne che di solito non hanno controllo di flusso.

Lo svantaggio potrebbe essere che la compilazione iniziale valuta i valori dei parametri e le statistiche delle tabelle, denominata Parameter Sniffing in SQL Server, e crea un piano di query per loro, che potrebbe essere diverso da un piano ottimale per le query successive.

Immagina una query che esegue sempre il polling delle voci più recenti in una tabella: la query iniziale inizierà con un valore minimo ed eseguirà la scansione dell'intera tabella, quindi la stessa query effettuerà ancora una scansione completa della tabella in un secondo momento, anche se si suppone che scelga gli ultimi pochi milioni di righe!

Per questo motivo, si dovrebbe prestare attenzione a ciò che accade con le query, anche se i piani di query precompilati sono generalmente validi e salvare il database per un sacco di tempo e il lavoro della CPU.