La chiave esterna migliora le prestazioni della query?

Supponiamo di avere 2 tabelle, prodotti e categorie di prodotti. Entrambe le tabelle hanno una relazione su CategoryId. E questa è la domanda.

SELECT p.ProductId, p.Name, c.CategoryId, c.Name AS Category FROM Products p INNER JOIN ProductCategories c ON p.CategoryId = c.CategoryId WHERE c.CategoryId = 1; 

Quando creo il piano di esecuzione, la tabella ProductCategories esegue la ricerca dell’indice del cluster, che è come un’aspettativa. Ma per i prodotti da tavola, esegue la scansione dell’indice del cluster, il che mi fa dubitare. Perché FK non aiuta a migliorare le prestazioni delle query?

Quindi devo creare un indice su Products.CategoryId. Quando creo di nuovo il piano di esecuzione, entrambe le tabelle eseguono la ricerca dell’indice. E il costo del sottostrato stimato viene ridotto molto.

Le mie domande sono:

  1. Oltre a FK aiuta a vincolare la relazione, ha qualche altra utilità? Migliora le prestazioni della query?

  2. Devo creare un indice su tutte le colonne FK (mi piace Products.CategoryId) in tutte le tabelle?

Le chiavi esterne sono uno strumento di integrità referenziale, non uno strumento di performance. Almeno in SQL Server, la creazione di un FK non crea un indice associato e devi creare indici su tutti i campi FK per migliorare i tempi di ricerca.

Le chiavi esterne possono migliorare (e danneggiare) le prestazioni

  1. Come indicato qui: le chiavi esterne aumentano le prestazioni

  2. È sempre necessario creare indici sulle colonne FK per ridurre le ricerche. SQL Server non lo fa automaticamente.

modificare

Dato che il collegamento sembra essere morto (complimenti a Chris per averlo notato) , di seguito viene mostrato il motivo per cui le chiavi esterne possono migliorare (e danneggiare) le prestazioni.

La chiave esterna può migliorare le prestazioni

Il vincolo di chiave esterna migliora le prestazioni al momento della lettura dei dati ma allo stesso tempo rallenta le prestazioni al momento dell’inserimento / modifica / eliminazione dei dati.

In caso di lettura della query, l’ottimizzatore può utilizzare vincoli di chiave esterna per creare piani di query più efficienti poiché i vincoli di chiave esterna sono regole dichiarate in precedenza. Questo di solito comporta l’omissione di alcune parti del piano di query poiché, ad esempio, l’ottimizzatore può vedere che a causa di un vincolo di chiave esterna, non è necessario eseguire quella parte specifica del piano.

Una chiave esterna è un concetto di DBMS per garantire l’integrità del database.

Eventuali implicazioni / miglioramenti delle prestazioni saranno specifici della tecnologia del database utilizzata e sono secondari rispetto allo scopo di una chiave esterna.

È buona pratica in SQL Server assicurarsi che tutte le chiavi esterne abbiano almeno un indice non cluster su di esse.

Spero che questo chiarisca le cose per te ma non esitate a richiedere maggiori dettagli.

La tua scommessa migliore è quella di utilizzare gli indici sui campi che utilizzi frequentemente. Se si utilizza SQL Server, è ansible utilizzare profiler per creare un profilo per un database specifico e prendere il file che emette e utilizzare la procedura guidata di ottimizzazione per ricevere consigli su dove posizionare gli indici. Mi piace anche usare il profiler per scovare le stored procedure a lungo termine, ho una lista dei peggiori 10 tra i peggiori che pubblico ogni settimana, mantiene le persone oneste: D.

Puoi usarlo per rendere più efficiente una query. Ti consente di ristrutturare le query in SQL Server per utilizzare un join esterno anziché uno interno che rimuove la necessità di SQL Server di dover verificare se c’è un valore null nella colonna. Non è necessario inserire quel qualificatore perché la relazione con la chiave esterna lo rende già applicabile.

Così questo:

  select p.ProductId, p.Name, c.CategoryId, c.Name AS Category from Products p inner join ProductCategories c on p.CategoryId = c.CategoryIdwhere c.CategoryId = 1; 

Diventa questo:

 SELECT p.ProductId, p.Name, c.CategoryId, c.Name AS Category FROM ProductCategories c LEFT OUTER JOIN Products P ON c.CategoryId = p.CategoryId WHERE c.CategoryId = 1; 

Ciò non produrrà necessariamente grandi prestazioni in piccole query, ma quando le tabelle diventano grandi può essere più efficiente.

Non so molto sul server SQL, ma nel caso di Oracle, avere una colonna chiave esterna riduce le prestazioni del caricamento dei dati. Questo perché il database deve verificare l’integrità dei dati per ogni inserto. E sì, come già accennato, avere un indice sulla colonna chiave straniera è una buona pratica.

L’aggiunta di una chiave esterna nella tabella non migliorerà le prestazioni, semplicemente dicendo che se si sta inserendo un record in una tabella delle categorie ProductCategories si tenterà di trovare la colonna della chiave esterna con un valore che esiste nel valore della chiave primaria della tabella prodotti, questa ricerca, l’operazione è sovraccarico sul tuo database ogni volta che aggiungi una nuova voce nella tabella ProductCategories. Quindi, aggiungendo una chiave esterna non migliorerà le prestazioni del database, ma si prenderà cura dell’integrità del database. Sì, migliorerà le prestazioni del tuo db se stai controllando l’integrità utilizzando la chiave esterna invece di eseguire molte query per verificare che il record sia presente nel database del tuo programma.

Per MySQL 5.7, è in grado di accelerare incredibilmente le query che coinvolgono più join!

Ho usato ‘spiega’ per capire la mia domanda e ho scoperto che stavo partecipando a 4-5 tabelle, in cui non sono state utilizzate affatto chiavi. Non ho fatto altro che aggiungere una chiave esterna a queste tabelle e il risultato è stato una riduzione del 90% del tempo di caricamento. Le query che hanno richiesto> 5 s ora richiedono 500 ms o meno.

Questo è un miglioramento ENORME!

E, come altri hanno già detto, ottieni il vantaggio aggiuntivo di garantire l’integrità relazionale.

Oltre a ciò, garantire l’integrità referenziale ha anche i propri vantaggi prestazionali. Ha l’effetto del secondo ordine di garantire che le tabelle che hanno la chiave esterna siano “aggiornate” con la tabella esterna. Supponiamo che tu abbia una tabella utenti e una tabella dei commenti e che tu stia facendo delle statistiche sulla tabella dei commenti. Probabilmente se elimini l’utente, non vuoi più nemmeno i loro commenti.

A partire da SQL Server 2008, le chiavi esterne possono influenzare le prestazioni influenzando il modo in cui il motore di database sceglie di ottimizzare la query. Fare riferimento a Star Join Heuristics nel seguente articolo: https://technet.microsoft.com/en-us/library/2008.04.dwperformance.aspx