Come forzare i piani delle query

L'ottimizzatore si basa in gran parte sui costi di esecuzione nel cercare di trovare il piano ottimale per ciascuna query. Tuttavia può succedere che non abbia sufficienti informazioni per identificare un piano perfetto; in questi casi è possibile forzare la scelta di un particolare piano.

E' possibile esaminare il piano per una SELECT scrivendo EXPLAIN prima dell'istruzione.

Per i seguenti esempi, si userà il database world.

Preparare il database di esempio world

Si scarichi il database da ftp://ftp.askmonty.org/public/world.sql.gz

Lo si installi:

mysqladmin create world
zcat world.sql.gz | ../client/mysql world

oppure:

mysqladmin create world
gunzip world.sql.gz
../client/mysql world < world.sql

Forzare l'ordine delle JOIN

E' possibile forzare l'ordine delle JOIN utilizzando STRAIGHT_JOIN nella clausola SELECT o nella clausola JOIN.

Il caso più semplice è quello in cui si forza l'ordine delle tabelle nella clausola FROM e si specifica SELECT STRAIGHT_JOIN:

SELECT STRAIGHT_JOIN SUM(City.Population) FROM Country,City WHERE
City.CountryCode=Country.Code AND Country.HeadOfState="Vladimir Putin";

Se si desidera forzare l'ordine delle JOIN per una quantità limitata di tabelle, si può utilizzare STRAIGHT_JOIN nella clausola FROM: verrà imposto un ordine solo per le tabelle connesse da STRAIGHT_JOIN.

SELECT SUM(City.Population) FROM Country STRAIGHT_JOIN City WHERE
City.CountryCode=Country.Code AND Country.HeadOfState="Vladimir Putin";

In entrambi i casi, Country verrà letta per prima e per ogni Paese corrispondente (uno in questo caso) verrà cercata una corrispondenza in tutte le righe di City. Poiché vi è soltanto un record corrispondente, questa query sarà più veloce di quella originale.

L'output di EXPLAIN per gli esempi di cui sopra è:

Questo è uno dei rari casi in cui il valore ALL è accettabile, perché la scansione della tabella Country troverà soltanto una corrispondenza.

Forzare la scelta di un indice specifico per la clausola WHERE

In alcuni casi l'ottimizzatore potrebbe scegliere un indice non ottimale o non scegliere alcun indice, anche se teoricamente potrebbe usarne alcuni.

In questo caso si può dire all'ottimizzatore di utilizzare un insieme di indici limitato, di ignorare uno o più indici, oppure forzare l'uso di alcuni indici.

Usare un insieme limitato di indici

E' possibile limitare gli indici che l'ottimizzatore prenderà in considerazione tramite l'opzione USE INDEX.

USE INDEX [{FOR {JOIN|ORDER BY|GROUP BY}] ([elenco_indici])

Il default è FOR JOIN, che significa che il suggerimento vale solo per la clausola WHERE.

USE INDEX deve essere specificato dopo il nome della tabella nella clausola FROM.

Esempio:

CREATE INDEX Name ON City (Name);
CREATE INDEX CountryCode ON City (Countrycode);
EXPLAIN SELECT Name FROM City USE INDEX (CountryCode)
WHERE name="Helsingborg" AND countrycode="SWE";

Risultato:

Senza l'uso di USE INDEX, anche l'indice Name sarebbe stato incluso in possible keys.

Ignorare alcuni indici

E' possibile dire all'ottimizzatore di non considerare alcuni indici con l'opzione IGNORE INDEX.

IGNORE INDEX [{FOR {JOIN|ORDER BY|GROUP BY}] ([elenco_indici])

IGNORE INDEX deve essere specificato dopo il nome della tabella nella clausola FROM.

Esempio:

CREATE INDEX Name ON City (Name);
CREATE INDEX CountryCode ON City (Countrycode);
EXPLAIN SELECT Name FROM City IGNORE INDEX (Name)
WHERE name="Helsingborg" AND countrycode="SWE";

Risultato:

Il beneficio dell'uso di IGNORE_INDEX invece di USE_INDEX è che il primo non disabilita eventuali nuovi indici che potrebbero essere aggiunti in futuro.

Forzare l'uso di un indice

Forzare la scelta di un indice è utile soprattutto quando l'ottimizzatore decide di effettuare una scansione completa di una tabella nonostante l'uso di una certa chiave sia più auspicabile.

CREATE INDEX Name ON City (Name);
EXPLAIN SELECT Name,CountryCode FROM City FORCE INDEX (Name)
WHERE name>="A" and CountryCode >="A";

Risultato:

FORCE_INDEX impone all'ottimizzatore di considerare solo gli indici specificati (come USE_INDEX, ma in più dice all'ottimizzatore di considerare una scansione completa della tabella come qualcosa di estremamente costoso. Tuttavia, se nessuno degli indici specificati è adatto, la scansione completa avverrà comunque.

Forzare l'uso di un indice per ORDER BY o per GROUP BY

L'ottimizzatore cerca di utilizzare gli indici per elaborare le clausole ORDER BY e GROUP BY.

Per forzare o ignorare un indice specifico, è possibile usare USE INDEX, IGNORE INDEX e FORCE INDEX come si è fatto sopra per la clausola WHERE:

USE INDEX [{FOR {JOIN|ORDER BY|GROUP BY}] ([elenco_indici])

Queste opzioni devono essere specificate dopo il nome della tabella nella clausola FROM.

Esempio:

CREATE INDEX Name ON City (Name);
EXPLAIN SELECT Name,Count(*) FROM City
FORCE INDEX FOR GROUP BY (Name)
WHERE population >= 10000000 GROUP BY Name;

Risultato:

Senza l'opzione FORCE INDEX, il valore della colonna extra sarebbe Using where; Using temporary; Using filesort, perchè l'ottimizzatore creerebbe una tabella temporanea e la ordinerebbe.

Aiutare l'ottimizzatore a ottimizzare GROUP BY e ORDER BY

L'ottimizzatore usa diverse strategie per ottimizzare GROUP BY e ORDER BY:

• Elaborarla con un indice:
  • Scansionare la tabella seguendo l'ordine dell'indice e inviare i dati man mano. (Funziona solo se la clausola ORDER BY / GROUP BY può essere risolta con un indice dopo la propagazione delle costanti).

• Filesort:
  • Scansionare la tabella da ordinare e collezionare le chiavi ordinate in un file temporaneo.
  • Ordinare le chiavi + riferimenti in righe (con il filesort)
  • Scansionare la tabella nell'ordine stabilito

• Usare una tabella temporanea per ORDER BY.
  • Creare una tabella temporanea (in memoria) per i dati da ordinare. (Se però diventa più grande di max_heap_table_size o contiene colonne BLOB, verranno usate tabelle Aria o MyISAM su disco)
  • Ordinare le chiavi + riferimenti in righe (con il filesort)
  • Scansionare la tabella nell'ordine stabilito

Se i campi da ordinare non sono tutti nella prima JOIN, viene sempre usata una tabella temporanea.

• Usare una tabella temporanea per GROUP BY:
  • Creare una tabella temporanea contenente i risultati del raggruppamento con un indice che corrisponde ai campi usati nella clausola GROUP BY.
  • Produrre una riga di risultati
  • Se nella tabella temporanea esiste già una riga con l'indice usato per la GROUP BY, aggiungere ad esso i nuovi risultati. Altrimenti, creare una nuova riga.
  • Prima di inviare i risultati al client, ordinare le righe con il filesort per avere i risultati nell'ordine del GROUP BY.

Forzare/proibire l'uso delle tabelle temporeanee per GROUP BY

Usare una tabella in memoria (come descritto sopra) è solitamente l'opzione più veloce per risolvere la clausola GROUP BY quando i risultati sono pochi, ma non è ottimale quando l'insieme dei risultati è molto grande. E' possibile informare di questo l'ottimizzatore utilizzando SELECT SQL_SMALL_RESULT o SELECT SQL_BIG_RESULT:

EXPLAIN SELECT SQL_SMALL_RESULT Name,Count(*) AS Cities FROM City GROUP BY Name HAVING Cities > 2;

Risultato:

Esempio opposto:

EXPLAIN SELECT SQL_BIG_RESULT Name,Count(*) AS Cities FROM City
GROUP BY Name HAVING Cities > 2;

Risultato:

La differenza è che se si specifica SQL_SMALL_RESULT viene creata una tabella temporanea.

Forzare l'uso di una tabella temporanea

In alcuni casi è possibile forzare l'uso di una tabella temporanea per i risultati, per rilasciare al più presto possibile i lock a livello di tabella o di riga.

Per farlo, si specifica l'opzione SQL_BUFFER_RESULT:

CREATE INDEX Name ON City (Name);
EXPLAIN SELECT SQL_BUFFER_RESULT Name,Count(*) AS Cities FROM City
GROUP BY Name HAVING Cities > 2;

Risultato:

Senza l'opzione SQL_BUFFER_RESULT la tabella temporanea non sarebbe stata usata.

L'optimizer switch

In MariaDB 5.3 è stato aggiunto l'optimizer switch, che permette di specificare quali algoritmi devono essere presi in considerazione nell'ottimizzare una query.

Si veda la sezione optimizer per ulteriori informazioni su tali algoritmi.