Claudio Cherubino's blog Life of a Googler

16Jul/0713

10 trucchi per migliorare le performance di MySQL

Da qualche mese sto studiando per ottenere la certificazione Certified MySQL 5.0 Developer e dopo aver passato il primo dei due esami, mi sto ovviamente concentrando su alcuni aspetti più avanzati, ad esempio l'ottimizzazione delle query e del sistema.

Un paio di giorni fa ho trovato un articolo molto interessante sull'argomento, scritto da Jay Pipes, il Community Relations Manager di MySQL AB, sul suo blog, e ho pensato di tradurlo in italiano e pubblicarlo qui, ovviamente con la benedizione di Jay.

Si tratta di 10 domande frequenti relative all'ottimizzazione delle query, e in generale di un server MySQL, penso che una lettura possa risultare interessante a qualunque sviluppatore e amministratore che abbia a che fare con questo database:

  1. Quale di queste due query è più veloce?

    SELECT ... WHERE some_col IN (1,2,3) or
    SELECT ... WHERE some_col = 1 OR some_col = 2 OR some_col = 3

    Nessuna. L'ottimizzatore riscrive l'operatore IN() come una serie di condizioni OR, pertanto non ci saranno differenze nelle performance. Si usa IN() per rendere il codice più conciso e leggibile.

  2. Dove vengono memorizzati in cache i record delle tabelle MyISAM?

    Da nessun parte. MyISAM non mette in cache i record delle tabelle come fa InnoDB con il suo innodb_buffer_pool. Invece MyISAM si appoggia sul sistema di buffering del sistema operativo sottostante per bufferizzare i record delle tabelle mentre vengono letti dal file .MYD. Il key_buffer MyISAM memorizza solo i blocchi indice, non i record con i dati.

  3. Quale di queste due query è più veloce?

    SELECT ... FROM t1, t2 WHERE t1.id = t2.id
    SELECT ... FROM t1 INNER JOIN t2 ON t1.id = t2.id

    Nessuna. L'ottimizzatore riscrive la seconda query nella prima forma. Lo stile SQL che adottate, ovviamente, dipende esclusivamente da voi, tuttavia vi consiglio di usare il secondo stile (noto come sintassi ANSI) invece del primo (noto come sintassi Theta) per un paio di ragioni:

    * MySQL supporta esclusivamente inner e cross join con la sintassi Theta. Tuttavia, MySQL supporta anche INNER, CROSS, LEFT e RIGHT join se si usa la sintassi ANSI. Mescolare i due stili può portare a del codice SQL poco leggibile.
    * E' molto facile scordare una condizione di join usando lo stile Theta, specialmente quando si collegano molte tabelle insieme. Dimenticarsi per sbaglio una condizione di join nella clausola WHERE porterà  ad un prodotto cartesiano (e non è buono!). La sintassi ANSI è più esplicita ed è più difficile dimenticarsi di una condizione di join.

  4. InnoDB è più veloce/migliore di MyISAM?

    Dipende. Non piace a nessuno questa risposta, ma è assolutamente vera. Ci sono vantaggi e svantaggi in ogni storage engine storage engine. MyISAM ha delle ottime performance in lettura e nel caso di grossi carichi, e richiede poche risorse. InnoDB, invece, è preferibile nel caso di grossi UPDATE, quando si ha necessità  di avere transazioni, integrità  referenziale e ricerche veloci su singola chiave. Si deve scegliere l'engine in base alle esigenze della propria applicazione e non secondo dei dettami generali del tipo "usate solo l'engine XXX".

  5. CHAR è più veloce di VARCHAR?

    No, non proprio. Se ci sono differenze di performance, sono trascurabili. Usate CHAR se sapete che i vostri dati saranno formati da un determinato numero di caratteri (come un numero di previdenza sociale, ad esempio), altrimenti VARCHAR.

  6. VARCHAR(80) è più veloce di VARCHAR(255)?

    Si, ma probabilmente non nel modo in cui pensate. Per quanto ne so io, non c'è differenza (almeno nelle versioni più recenti di MySQL e InnoDB) nei tempi necessari per recuperare colonne VARCHAR di differente lunghezza dal disco o dalla memoria. C'è invece una grande differenza in uno dei seguenti scenari:

    * Viene creata implicitamente una tabella temporanea per gestire una clausola GROUP BY o ORDER BY ed è presente una colonna VARCHAR nell'istruzione SELECT
    * Viene creata esplicitamente una tabella temporanea che contiene una colonna VARCHAR

    In questi casi la lunghezza della colonna VARCHAR viene presa in considerazione. La motivazione? Le tabelle temporanee in memoria non sono altro che tabelle dello storage engine MEMORY che, per qualche ragione, tratta tutte le colonne VARCHAR(X) come CHAR(X).

    Ciò comporta che se definite due campi, uno come VARCHAR(255) ed un altro come VARCHAR(128), il secondo consumerà  la metà  dello spazio quando viene allocato in una tabella temporanea. Più record possono entrare all'interno di max_heap_table_size, e meno sono le probabilità  di richiedere swapping su disco (cercate SHOW STATUS LIKE 'Created_tmp_disk_';), portando a migliori performance generali.

  7. Ci sono cali di performance quando si effettuano join fra tabelle di storage engine differenti?

    No. Eseguire una SELECT su storage engine multipli va bene. Si hanno risultati impredicibili quando si mescolano engine transazionali e non-transazionali all'interno di una transazione che modifica i dati.

  8. Se cambio una tabella derivata in una vista, le performance migliorano?

    No. Una vista è semplicemente una tabella derivata (almeno quando viene creata usando l'algoritmo TEMPTABLE). Ciò implica che non ci sono differenze di performance fra una normale tabella derivata e una vista. La vista rende semplicemente il codice più leggibile e meno "componentizzato".

  9. Se vedo "Using temporary; Using filesort" nella colonna Extra dell'output di un comando EXPLAIN, vuol dire che è stata creata una tabella temporanea su disco?

    No. Una tabella su disco viene creata solo nelle situazioni seguenti:

    * Quando la dimensione della tabella temporanea creata implicitamente (da un GROUP BY o ORDER BY su una colonna non indicizzata) è maggiore sia di tmp_table_size che di max_heap_table_size
    * Quando ci sono campi di tipo BLOB o TEXT nell'espressione SELECT
    * Quando avviene una scansione completa della tabella che eccede il valore della variabile read_buffer_size (configurata per thread della connessione)

    Questi sono gli scenari (che ho in testa) che possono causare la creazione di tabelle temporanee su disco. Ce ne potrebbero essere altri. In ogni caso la tabella temporanea creata su disco sarà  una tabella MyISAM.

  10. E' possibile effettuare una FULL OUTER JOIN in MySQL?

    Si. Si usino sia LEFT che RIGHT JOIN nella stessa condizione di join della query, in questo modo:

    SELECT *
    FROM A LEFT JOIN B ON A.id = B.id
    UNION ALL
    SELECT *
    FROM A RIGHT JOIN B ON A.id = B.id
    WHERE A.id IS NULL

Comments (13) Trackbacks (1)
  1. :smile: Caro Claudio, molto interessante…
    Ho una domanda da farti. Sto cercando di ottimizzare una query : SELECT *,accettazione.id AS idacc,accettazione.data_ins AS dat_ins,anagrafica.codpaz AS codpaz_ot FROM (anagrafica INNER JOIN accettazione ON anagrafica.codpaz = accettazione.codpaz) LEFT JOIN dimissione ON accettazione.id = dimissione.id_accettaz WHERE accettazione.codirc = ’120015′ AND dimissione.tipdim Is Null AND accettazione.datric

  2. Ciao Vittorio,
    mi sa che il tuo messaggio è stata troncato.

    Forse è meglio che mandi il tutto a claudiocherubinoATgmail.com e vediamo se posso aiutarti.

  3. ciao tonino,
    potresti dirmi dove sto sbagliando??

    SELECT “.$default_table3.”.ID_shot FROM $default_table3 LEFT JOIN (SELECT $default_table1.ID_shot FROM $default_table1 LEFT JOIN $default_table2 ON ($default_table1.ID_shot = $default_table2.ID_shot) WHERE “.$str_GResult.$tag.”)
    ON ($default_table3.ID_shot = $default_table1.ID_shot AND $default_table3.Num_unit=1 AND $default_table3.Face_unit=’front’)
    WHERE “.$str_IDTarget.$PERS_GResult_radio.$str_GResult;

  4. l’errore che ottengo è
    Every derived table must have its own alias

  5. Come riportato dall’errore, ogni tabella derivata deve avere un nome.

    Nel tuo caso, la tabella derivata è quella ottenuta dalla subquery, e quindi devi semplicemente darle un alias, aggiungendo “AS derivata1″ prima di “ON …”.

    Ti scrivo di seguito la query completa, ma poi mi spieghi chi è Tonino??? :shock:

    SELECT “.$default_table3.”.ID_shot FROM $default_table3 LEFT JOIN (SELECT $default_table1.ID_shot FROM $default_table1 LEFT JOIN $default_table2 ON ($default_table1.ID_shot = $default_table2.ID_shot) WHERE “.$str_GResult.$tag.”) AS derivata1
    ON ($default_table3.ID_shot = $default_table1.ID_shot AND $default_table3.Num_unit=1 AND $default_table3.Face_unit=’front’)
    WHERE “.$str_IDTarget.$PERS_GResult_radio.$str_GResult;

  6. ehehe … :-)
    booooob …. ma sai è un pò che dormo solo 3 ore per notte, e quindi qualche fesseria la dico anche io …
    ma che senso ha mettere li un alias se non lo si usa?

    bello il tuo blog,…

  7. E’ molto interessante il discorso che fai sul full outer,
    ma come potrei applicarlo al mio caso ?..

    ciao

  8. A dire la verità non credo che nel tuo caso si possa usare la full outer join, o almeno non capisco a cosa possa servire.

  9. Ciao, che tu sappia esiste un modo di fare una query join tra due tabelle ad esempio, tale che l’optimizer non debba andarsi a sfogliare per forza tutti i record di una delle due tabelle? esempio
    SELECT … FROM t1 INNER JOIN t2 ON t1.id = t2.id
    Se fai l’Explain ti mostra che l’optimizer usando gli indici giusti sfoglia un record della t1 e tutti i record della t2.
    Confermi?

  10. Se le colonne che richiedi nella tua query non sono tutte presenti negli indici, allora MySQL avrà comunque bisogno di scorrere tutti i record della tabella e magari l’optimizer riterrà inutile sfruttare gli indici.

    Questa è solo una delle possibilità, per immaginare le altre è necessario dare un’occhiata alla query, al risultato dell’EXPLAIN e alla struttura delle tabelle coinvolte.

    Ovviamente io sono a disposizione :wink:

  11. ciao scusa per l’ignoranza ma da poco sto progettando e ho creato un database…e ho creato una tabella ma quando ci metto i records domanda varchar(1) default NOT NULL,
    risposta varchar(20) default NOT NULL,
    etc etc ..cmq mi da errore a questi 2 rigli perchè?se puoi ti sarei grato se mi daresti 1 mano thx!ciaooo

  12. Ciao,
    La creazione di una tabella su disco (punto 9) migliora o peggiora le performance sulla singola query?
    Mi spiego meglio se la query non è poco frequente i tempi di risposta peggiorano, cominciano a migliorare dalla seconda ripetizione della query. Giusto?
    Grazie in anticipo
    Andrea

  13. La tabella temporanea su disco viene creata per eseguire la query e poi buttata perché l’optimizer non sa se ne avrà bisogno per altre query che potranno arrivare in futuro, quindi la situazione non migliora dalla seconda ripetizione.


Leave a comment

(required)