L'ottimizzazione Derived Table Merge

Background

Chi lavora con "grandi" sistemi di database è abituato a strutturare le query facendo uso delle subquery nella clausola FROM. Per esempio, a volte uno pensa per prima cosa di aver bisogno di selezionare le città con una popolazione maggiore di 10.000, e poi che da quelle città deve selezionare quelle che si trovano in Germania. Allora scrive la seguente istruzione SQL:

SELECT * 
FROM 
  (SELECT * FROM City WHERE Population > 10*1000) AS big_city
WHERE 
  big_city.Country='DEU'

In MySQL, questa sintassi era un taboo. Se si esegue una EXPLAIN su quella query, si capisce il perché:

mysql> EXPLAIN SELECT * FROM (SELECT * FROM City WHERE Population > 1*1000) AS big_city WHERE big_city.Country='DEU' ;
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 4068 | Using where |
|  2 | DERIVED     | City       | ALL  | Population    | NULL | NULL    | NULL | 4079 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.60 sec)

Ecco illustrato il piano di esecuzione:

Da sinistra a destra:

1. Eseguire la subquery: (SELECT * FROM City WHERE Population > 1*1000), esattamente nel modo in cui è scritta nella query.
2. Immettere i risultati della subquery in una tabella temporanea.
3. Leggerli e applicare la condizione WHERE della select madre: big_city.Country='DEU'

Eseguire una subquery come questa è decisamente inefficiente, perché la condizione selettiva della select madre (Country='DEU') non viene applicata nella scansione della tabella City. Si leggono quindi troppi record dalla tabella City, che poi vengono scritti nella tabella temporanea e riletti, prima di filtrarli.

Derived table merge in azione

Se si esegue questa query in MariaDB/MySQL 5.6, si ottiene questo output:

MariaDB [world]> EXPLAIN SELECT * FROM (SELECT * FROM City WHERE Population > 1*1000) AS big_city WHERE big_city.Country='DEU';
+----+-------------+-------+------+--------------------+---------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys      | key     | key_len | ref   | rows | Extra                              |
+----+-------------+-------+------+--------------------+---------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | City  | ref  | Population,Country | Country | 3       | const |   90 | Using index condition; Using where |
+----+-------------+-------+------+--------------------+---------+---------+-------+------+------------------------------------+
1 row in set (0.00 sec)

Da ciò si vede che:

1. L'output ha una sola riga. Questo significa che la subquery è stata unita alla SELECT di livello superiore.
2. L'accesso alla tabella City avviene attraverso un indice nella colonna Country. Apparentemente, per costruire l'accesso ref alla tabella è stata usata la condizione Country='DEU'.
3. La query legge circa 90 righe, il che è un notevole miglioramente rispetto alle 4079 lette prima, a cui si aggiungono le 4068 nella tabella temporanea.

Riassunto

• Le derived tables (subquery nella clausola FROM) vengono unite alla select principale quando non vi sono raggruppamenti, funzioni aggreganti oppure clausole ORDER BY ... LIMIT. Questi sono requisiti sono gli stessi che si applicano alle VIEW perché possano avere algorithm=merge.
• Questa ottimizzazione è attiva per default. Può essere disabilitata con:

  set @@optimizer_switch='derived_merge=OFF'
  

• Le versioni di MySQL e MariaDB che non supportano tale ottimizzazione eseguono le subquery anche quando si esegue EXPLAIN. Questo comportamento è la causa di un problema conosciuto, cioè le istruzioni EXPLAIN possono impiegare molto tempo (si veda ad esempio MySQL Bug #44802). A partire da MariaDB 5.3+ e MySQL 5.6+, i comandi EXPLAIN vengono eseguiti istantaneamente, indipendentemente dall'impostazione derived_merge.