MySQL
Ottimizzazione delle prestazioni

Sintassi

• Non utilizzare DISTINCT e GROUP BY nella stessa SELECT.

• Non impaginare tramite OFFSET, "ricorda dove hai lasciato".

• WHERE (a, b) = (22,33) non ottimizza affatto.

• Esplicitamente dire TUTTO o DISTINTIVO dopo UNION - ti ricorda di scegliere tra il più veloce ALL o il più lento DISTINCT.

• Non usare SELECT *, specialmente se hai colonne TEXT o BLOB che non ti servono. C'è sovraccarico nelle tabelle e nella trasmissione del tmp.

• È più veloce quando GROUP BY e ORDER BY possono avere esattamente la stessa lista.

• Non utilizzare FORCE INDEX; potrebbe essere d'aiuto oggi, ma probabilmente farà male domani.

Osservazioni

Aggiungi l'indice corretto

Questo è un argomento enorme, ma è anche il più importante problema di "rendimento".

La lezione principale per un principiante è quella di imparare gli indici "compositi". Ecco un rapido esempio:

INDEX(last_name, first_name)

è eccellente per questi:

WHERE last_name = '...'
WHERE first_name = '...' AND last_name = '...'   -- (order in WHERE does not matter)

ma non per

WHERE first_name = '...'   -- order in INDEX _does_ matter
WHERE last_name = '...' OR first_name = '...'   -- "OR" is a killer

Imposta la cache correttamente

innodb_buffer_pool_size dovrebbe essere circa il 70% della RAM disponibile.

Evitare costrutti inefficienti

x IN ( SELECT ... )

trasformarsi in un JOIN

Quando possibile, evita OR .

Non "nascondere" una colonna indicizzata in una funzione, ad esempio WHERE DATE(x) = ... ; riformulare come WHERE x = ...

In generale, è possibile evitare WHERE LCASE(name1) = LCASE(name2) con un confronto appropriato.

Non utilizzare OFFSET per "paginazione", invece "ricorda dove hai lasciato".

Evita SELECT * ... (a meno che non si esegua il debug).

Nota a Maria Deleva, Barranka, Batsu: Questo è un segnaposto; ti preghiamo di rimuovere questi elementi mentre crei esempi completi. Dopo aver fatto quello che puoi, mi trasferirò per approfondire il resto e / o lanciarlo.

negativi

Ecco alcune cose che non sono in grado di aiutare le prestazioni. Derivano da informazioni non aggiornate e / o ingenuità.

• InnoDB è migliorato al punto in cui è improbabile che MyISAM sia migliore.
• PARTITIONing offre raramente vantaggi prestazionali; può anche danneggiare le prestazioni.
• L'impostazione di query_cache_size superiore a 100M di solito danneggia le prestazioni.
• Aumentare molti valori in my.cnf può portare a "swapping", che è un serio problema di prestazioni.
• "Gli indici di prefisso" (come INDEX(foo(20)) ) sono generalmente inutili.
• OPTIMIZE TABLE è quasi sempre inutile. (E comporta il blocco del tavolo.)

Avere un INDICE

La cosa più importante per velocizzare una query su qualsiasi tabella non piccola è avere un indice adatto.

WHERE a = 12  --> INDEX(a)
WHERE a > 12  --> INDEX(a)

WHERE a = 12 AND b > 78  --> INDEX(a,b) is more useful than INDEX(b,a)
WHERE a > 12 AND b > 78  --> INDEX(a) or INDEX(b); no way to handle both ranges

ORDER BY x  --> INDEX(x)
ORDER BY x, y  --> INDEX(x,y) in that order
ORDER BY x DESC, y ASC  --> No index helps - because of mixing ASC and DESC

Non nasconderti nella funzione

Un errore comune è quello di nascondere una colonna indicizzata all'interno di una chiamata di funzione. Ad esempio, questo non può essere aiutato da un indice:

WHERE DATE(dt) = '2000-01-01'

Invece, dato INDEX(dt) questi possono usare l'indice:

WHERE dt = '2000-01-01'  -- if `dt` is datatype `DATE`

Funziona per DATE , DATETIME , TIMESTAMP e anche DATETIME(6) (microsecondi):

WHERE dt >= '2000-01-01'
  AND dt  < '2000-01-01' + INTERVAL 1 DAY

O

In generale OR uccide l'ottimizzazione.

WHERE a = 12 OR b = 78

non può usare INDEX(a,b) , e può o non può usare INDEX(a), INDEX(b) tramite "indice unione". La fusione indice è meglio di niente, ma solo a malapena.

WHERE x = 3 OR x = 5

è trasformato in

WHERE x IN (3, 5)

che può usare un indice con x in esso.

subquery

Le sottoquery hanno diversi gusti e hanno un potenziale di ottimizzazione diverso. Innanzitutto, nota che le sottoquery possono essere "correlate" o "non correlate". Correlati significa che dipendono da un valore esterno alla subquery. Ciò implica in genere che la sottoquery deve essere rivalutata per ciascun valore esterno.

Questa subquery correlata è spesso piuttosto buona. Nota: deve restituire al massimo 1 valore. È spesso utile come alternativa a, anche se non necessariamente più veloce, un LEFT JOIN .

SELECT a, b, ( SELECT ... FROM t WHERE t.x = u.x ) AS c
    FROM u ...
SELECT a, b, ( SELECT MAX(x) ... ) AS c
    FROM u ...
SELECT a, b, ( SELECT x FROM t ORDER BY ... LIMIT 1 ) AS c
    FROM u ...

Questo di solito non è correlato:

SELECT ...
    FROM ( SELECT ... ) AS a
    JOIN b ON ...

Note su FROM-SELECT :

• Se restituisce 1 riga, ottimo.
• Un buon paradigma (di nuovo "1 riga") è per la sottoquery ( SELECT @n := 0 ) , inizializzando così una variabile @ per l'uso nel resto o nella query.
• Se restituisce molte righe e anche JOIN è ( SELECT ... ) con molte righe, l'efficienza può essere terribile. Pre-5.6, non c'era alcun indice, quindi è diventato un CROSS JOIN ; 5.6+ comporta la deduzione del miglior indice sulle tabelle temporanee e quindi la generazione, solo per buttarlo via una volta terminato con SELECT .

ISCRIVITI + GRUPPO DI

Un problema comune che porta a una query inefficiente è simile a questo:

SELECT ...
    FROM a
    JOIN b  ON ...
    WHERE ...
    GROUP BY a.id

Innanzitutto, il JOIN espande il numero di righe; quindi GROUP BY ridimensiona il numero di righe in a .

Non ci possono essere buone scelte per risolvere questo problema implode-implode. Una possibile opzione è trasformare il JOIN in una subquery correlata in SELECT . Questo elimina anche GROUP BY .