6.1 Introduzione

Ora passiamo dalla teoria alla pratica.

“In teoria, non c'è differenza tra teoria e pratica. In pratica lo sono".

Viviamo nel mondo reale e tutti i prodotti software sono in definitiva creati per persone viventi. E queste persone viventi sono molto infastidite dai siti che si caricano lentamente e dai programmi che rallentano.

E se una query di database richiede più di un secondo, questo è inaccettabile . Gli utenti semplicemente non useranno un prodotto che ha pagine/funzionalità così lente.

Ma spesso, per visualizzare una pagina, è necessario eseguire diverse dozzine di query al database. E se vengono eseguiti in sequenza, non hai più un secondo limite, ma diciamo 100 ms per richiesta.

Ecco i 5 modi principali in cui i programmatori accelerano le query del database:

  1. Aggiunta di indici alle tabelle nel database.
  2. Riscrittura e ottimizzazione delle query.
  3. Abilita (e configura) la memorizzazione nella cache lato database.
  4. Abilita la memorizzazione nella cache sul lato client.
  5. Esecuzione della denormalizzazione del database.

Hai già familiarità con tutte queste cose per la maggior parte, quindi i seguenti saranno solo consigli pratici.

6.2 Indici

Non è un segreto che lavorare con un database richieda la maggior parte del lavoro di quasi tutti i siti. E sta lavorando con il database che è più spesso il collo di bottiglia delle applicazioni web.

In questo articolo vorrei dare consigli pratici sull'utilizzo di MySQL.

Java university

Dirò subito:

  • questo articolo è scritto su MySQL, anche se è probabile che le cose generali siano vere per qualsiasi DBMS.
  • tutto ciò che è scritto nell'articolo è il mio punto di vista personale e non è la verità ultima.
  • il consiglio non pretende di essere nuovo ed è il risultato di una generalizzazione della letteratura letta e dell'esperienza personale.
  • nell'ambito di questo articolo, non toccherò i problemi di configurazione di MySQL.

I problemi durante l'utilizzo di MySQL possono essere suddivisi nei seguenti tre gruppi (in ordine di importanza):

  1. Non uso o uso improprio degli indici.
  2. Struttura del database errata.
  3. Query SQL errate \ non ottimali.

Diamo un'occhiata più da vicino a ciascuno di questi gruppi.

Utilizzo degli indici

Il mancato utilizzo o l'uso improprio degli indici è ciò che più spesso rallenta le query. Per coloro che non hanno familiarità con il meccanismo di funzionamento degli indici o non l'hanno ancora letto nel manuale, consiglio vivamente di leggerlo.

Suggerimenti per l'utilizzo degli indici:

  • Non è necessario indicizzare tutto . Molto spesso, senza capirne il significato, le persone semplicemente indicizzano tutti i campi di una tabella. Gli indici accelerano i recuperi, ma rallentano gli inserimenti e gli aggiornamenti delle righe, quindi la scelta di ciascun indice deve essere significativa.
  • Uno dei parametri principali che caratterizzano l'indice è la selettività, che è il numero di elementi diversi nell'indice. Non ha senso indicizzare un campo che ha due o tre valori possibili. Ci saranno pochi benefici da un tale indice.
  • La scelta degli indici dovrebbe iniziare con un'analisi di tutte le query rispetto a una determinata tabella. Molto spesso, dopo tale analisi, invece di tre o quattro indici, puoi crearne uno composito.
  • Quando si utilizzano indici compositi, l'ordine dei campi nell'indice è fondamentale.
  • Non dimenticare di coprire gli indici. Se tutti i dati in una query possono essere recuperati da un indice, MySQL non accederà direttamente alla tabella. Tali richieste verranno eseguite molto rapidamente. Ad esempio, per una query SELECT name FROM user WHERE login='test'con un indice (login, nome), non è richiesto l'accesso alla tabella. A volte ha senso aggiungere un campo aggiuntivo a un indice composito, che renderà la copertura dell'indice e velocizzerà le query.
  • Per gli indici di riga, spesso è sufficiente indicizzare solo una parte della riga. Questo può ridurre significativamente la dimensione dell'indice.
  • Se %è all'inizio, LIKE(SELECT * FROM table WHERE field LIKE '%test')gli indici non verranno utilizzati.
  • L' indice FULLTEXT viene utilizzato solo con la sintassi MATCH ... AGAINST .

6.3 Struttura della banca dati

Un database ben progettato è la chiave per un lavoro rapido ed efficiente con il database. D'altra parte, un database mal progettato è sempre un grattacapo per gli sviluppatori.

Suggerimenti per la progettazione del database:

  1. Utilizzare i tipi di dati più piccoli possibili. Più grande è il tipo di dati, più grande è la tabella, più accessi al disco sono necessari per ottenere i dati. Utilizzare una procedura molto comoda: SELECT * FROM table_name PROCEDURE ANALYSE();determinare i tipi di dati minimi possibili.
  2. Osservare le forme normali durante la fase di progettazione. Spesso i programmatori ricorrono alla denormalizzazione già in questa fase. Tuttavia, nella maggior parte dei casi, all'inizio del progetto, è tutt'altro che ovvio come ciò possa risultare. Denormalizzare una tabella è molto più facile che soffrire di una denormalizzazione non ottimale. E JOINa volte funziona più velocemente delle tabelle denormalizzate in modo errato.
  3. Non utilizzare NULLle colonne a meno che tu non ne abbia consapevolmente bisogno.

6.4 Interrogazioni SQL.

Altrettanto spesso c'è il desiderio di riscrivere tutte le query in SQL nativo in modo che la query sia il più veloce possibile. Se decidi di farlo, ecco alcuni suggerimenti:

  1. Evita le richieste in loop. SQL è un linguaggio di insiemi e la scrittura di query dovrebbe essere affrontata non nel linguaggio delle funzioni, ma nel linguaggio degli insiemi.
  2. Evitare *(asterischi) nelle query. Sentiti libero di elencare esattamente i campi che scegli. Ciò ridurrà la quantità di dati recuperati e inviati. Inoltre, non dimenticare di coprire gli indici. Anche se selezioni tutti i campi della tabella, è meglio elencarli. Innanzitutto , migliora la leggibilità del codice. Quando si utilizzano gli asterischi, è impossibile scoprire quali campi sono presenti nella tabella senza esaminarli. In secondo luogo , oggi la tua tabella ha cinque colonne INT e un mese dopo sono state aggiunte un'altra TEXT e BLOB e l'asterisco è rimasto com'era.
  3. Quando impaginato, per ottenere il numero totale di record, utilizzare SQL_CALC_FOUND_ROWSe SELECT FOUND_ROWS();When used SQL_CALC_FOUND_ROWS MySQL, memorizza nella cache il numero selezionato di righe (prima che venga applicato LIMIT) e, quando utilizzato, SELECT FOUND_ROWS()restituisce solo questo valore memorizzato nella cache senza dover rieseguire la query.
  4. Non dimenticare che esiste INSERTuna sintassi per più inserimenti. Una query eseguirà un ordine di grandezza più velocemente di più query in un ciclo.
  5. Usa LIMITdove non hai bisogno di tutti i dati.
  6. Utilizzare INSERT… ON DUPLICATE KEY UPDATE…al posto di eo INSERTdopo UPDATEla selezione e spesso al posto di REPLACE.
  7. Non dimenticare questa fantastica funzionalità GROUP_CONCAT. Può aiutare con query complesse.