index - mysql dimensione indice



L'introduzione di chiavi esterne a MySQL riduce le prestazioni (4)

Sto costruendo l'app Ruby on Rails 2.3.5. Per impostazione predefinita, Ruby on Rails non fornisce contorni di chiave esterna, quindi devo eseguirlo manualmente. Mi stavo chiedendo se l'introduzione di chiavi esterne riduce le prestazioni delle query sul lato del database abbastanza da renderlo inutile. Le prestazioni in questo caso sono la mia priorità principale in quanto posso verificare la coerenza dei dati con il codice. Qual è la tua raccomandazione in generale? ti consiglia di utilizzare le chiavi esterne? e come suggerisci che dovrei misurare questo?

https://src-bin.com


Answer #1

È necessario definire le chiavi esterne. In generale (anche se non conosco le specifiche di mySQL), non c'è alcun effetto sulle query (e quando c'è un ottimizzatore, come l'ottimizzatore basato sui costi in Oracle, può anche avere effetti positivi poiché l'ottimizzatore può fare affidamento sul informazioni chiave esterna per scegliere piani di accesso migliori). In base all'effetto sull'inserimento e sull'aggiornamento, è possibile che si verifichi un impatto, ma i vantaggi che si ottengono (integrità referenziale e coerenza dei dati) superano di gran lunga l'impatto sulle prestazioni. Naturalmente, puoi progettare un sistema che non funzionerà affatto, ma il motivo principale non sarà perché hai aggiunto le chiavi esterne. E l'impatto sul mantenimento del codice quando decidi di utilizzare un'altra lingua, o perché le regole aziendali sono leggermente cambiate, o perché un nuovo programmatore entra nel tuo team, ecc., È molto più costoso dell'impatto sulle prestazioni. La mia raccomandazione, quindi, è sì, vai e definisci le chiavi esterne. Il tuo prodotto finale sarà più robusto.


Answer #2

È una buona idea utilizzare chiavi esterne perché ciò garantisce la coerenza dei dati (non si desiderano righe orfane e altri problemi di dati incoerenti).

Ma allo stesso tempo l'aggiunta di una chiave straniera introduce qualche colpo di performance. Supponendo che si stia utilizzando INNODB come motore di archiviazione, utilizza l'indice cluster per PK in cui essenzialmente i dati vengono archiviati insieme al PK. Per accedere ai dati utilizzando l'indice secondario è necessario passare sopra la struttura dell'indice secondario (dove i nodi contengono il PK) e quindi un secondo passaggio sull'indice cluster per recuperare effettivamente i dati. Quindi qualsiasi DML sulla tabella padre che coinvolge l'FK in questione, richiederà due passaggi sull'indice nella tabella figlio. Ovviamente, l'impatto del calo di prestazioni dipende dalla quantità di dati, dalle prestazioni del disco, dai vincoli di memoria (dati / indice memorizzati nella cache). Quindi è meglio misurarlo con il sistema di destinazione in mente. Direi che il modo migliore per misurarlo è con i dati di esempio del campione, o almeno alcuni dati di destinazione rappresentativi per il sistema. Quindi provare a eseguire alcuni benchmark con e senza vincoli FK. Scrivi script lato client che generano lo stesso carico in entrambi i casi.

Tuttavia, se stai controllando manualmente i vincoli FK, ti consiglio di lasciarlo a mysql e lasciare che mysql lo gestisca.


Answer #3

In generale, più chiavi (estranee o meno) ridurranno le prestazioni INSERT / UPDATE e aumenteranno le prestazioni SELECT.

Il vantaggio aggiuntivo dell'integrità dei dati, probabilmente vale sempre la piccola riduzione delle prestazioni che si ottiene con l'aggiunta delle chiavi esterne. A che serve un'app rapida se i dati al suo interno sono spazzatura (parti mancanti o ecc.)?

Trovato una query simile qui: La chiave esterna migliora le prestazioni della query?


Answer #4

assumendo:

  1. Stai già utilizzando un motore di archiviazione che supporta FK (ad esempio: InnoDB)
  2. Hai già indici sulle colonne coinvolte

Quindi suppongo che otterrai prestazioni migliori avendo MySQL che impone l'integrità. Applicare l'integrità referenziale, dopo tutto, è qualcosa che i motori di database sono ottimizzati per fare. Il confronto con il tuo codice personale per gestire l'integrità in Ruby sarà lento.

Se è necessario passare da MyISAM a InnoDB per ottenere la funzionalità FK, è necessario considerare i compromessi in termini di prestazioni tra i due motori.

Se non si dispone già di indizi, è necessario decidere se li si desidera. In generale, se stai facendo più letture che scritture, vuoi (servono, anche) gli indirizzi.

L'impilamento di un FK su materiale attualmente indicizzato dovrebbe causare un impatto sulle prestazioni inferiore rispetto all'implementazione di quei tipi di controllo nel codice dell'applicazione.





database-design