L'ottimizzazione Table Pullout

Il Table pullout è un'ottimizzazione per le subquery di tipo semi-join.

L'idea di Table Pullout

A volte, una subquery può essere riscritta come join. Per esempio:

select *
from City 
where City.Country in (select Country.Code
                       from Country 
                       where Country.Population < 100*1000);

Se si sa che può esserci al massimo uno stato (country) per ogni singolo valore di Country.Code (questo è certamente vero se Country ha una chiave primaria o un indice univoco su quella colonna), si può riscrivere la query così:

select City.* 
from 
  City, Country 
where
 City.Country=Country.Code AND Country.Population < 100*1000;

Il Table pullout in azione

Se si esegue EXPLAIN per la query riportata sopra su MySQL 5.1-5.6 o MariaDB 5.1-5.2, si ottiene il piano seguente:

MySQL [world]> explain select * from City where City.Country in (select Country.Code from Country where Country.Population < 100*1000);
+----+--------------------+---------+-----------------+--------------------+---------+---------+------+------+-------------+
| id | select_type        | table   | type            | possible_keys      | key     | key_len | ref  | rows | Extra       |
+----+--------------------+---------+-----------------+--------------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | City    | ALL             | NULL               | NULL    | NULL    | NULL | 4079 | Using where |
|  2 | DEPENDENT SUBQUERY | Country | unique_subquery | PRIMARY,Population | PRIMARY | 3       | func |    1 | Using where |
+----+--------------------+---------+-----------------+--------------------+---------+---------+------+------+-------------+
2 rows in set (0.00 sec)

Esso dimostra che l'ottimizzatore intende eseguire una scansione completa della tabella City, e cercare ogni singola città nella tabella Country.

Se si esegue la stessa query su MariaDB 5.3, si ottiene il piano seguente:

MariaDB [world]> explain select * from City where City.Country in (select Country.Code from Country where Country.Population < 100*1000);
+----+-------------+---------+-------+--------------------+------------+---------+--------------------+------+-----------------------+
| id | select_type | table   | type  | possible_keys      | key        | key_len | ref                | rows | Extra                 |
+----+-------------+---------+-------+--------------------+------------+---------+--------------------+------+-----------------------+
|  1 | PRIMARY     | Country | range | PRIMARY,Population | Population | 4       | NULL               |   37 | Using index condition |
|  1 | PRIMARY     | City    | ref   | Country            | Country    | 3       | world.Country.Code |   18 |                       |
+----+-------------+---------+-------+--------------------+------------+---------+--------------------+------+-----------------------+
2 rows in set (0.00 sec)

Gli elementi interessanti sono i seguenti:

• Entrambe le tabelle hanno select_type=PRIMARY, eid=1 come se fossero parte di una join.
• La tabella `Country` appare per prima, seguita da `City`.

Se si esegue EXPLAIN EXTENDED; SHOW WARNINGS, si vedrà che la subquery è stata sostituita con una join:

MariaDB [world]> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `world`.`City`.`ID` AS `ID`,`world`.`City`.`Name` AS 
`Name`,`world`.`City`.`Country` AS `Country`,`world`.`City`.`Population` AS 
`Population` 

  
   from `world`.`City` join `world`.`Country` where 


((`world`.`City`.`Country` = `world`.`Country`.`Code`) and (`world`.`Country`.
`Population` < (100 * 1000)))
1 row in set (0.00 sec)

Trasformare la subquery in una join permette di ottimizzarla appunto come una join, il che permette di scegliere tra i due possibili ordini di unione delle tabelle:

1. City -> Country
2. Country -> City

a differenza della vecchia ottimizzazione, che permetteva solo:

1. City->Country

Nell'esempio sopra, grazie a questa scelta si ha un piano di esecuzione migliore. Senza il Table pullout, il piano di esecuzione della subquery consisterebbe nel leggere (4079 + 1*4079)=8158 record. Con il Table pullout, il piano della join permette di leggere (37 + 37 * 18) = 703 righe. Le righe da leggere non sono sempre uguali, ma generalmente leggere un numero di record inferiore di 10 volte è molto più rapido.

Riassunto sul Table pullout

• Il Table pullout è possibile solo sulle semi-join.
• Il Table pullout si basa sulle definizioni delle chiavi UNIQUE/PRIMARY.
• Il Table pullout non taglia fuori altri possibili piani di esecuzione, perciò MariaDB cercherà sempre di ottenere il massimo.
• Il Table pullout è in grado di estrarre (pull-out) le singole tabelle delle subquery. Se tutte le tabelle in una subquery sono state estratte, la subquery (semi-join) viene rimossa completamente.
• Un consiglio abbastanza comune per ottimizzare MySQL era "Se possibile, riscrivi le subquery come join". Il Table pullout fa esattamente questo, perciò non è più necessario riscrivere le query manualmente.

Controllare il Table pullout

Non esiste un flag separato in @@optimizer_switch per controllare il Table pullout. Il Table pullout può essere disabilitato disattivando tutte le ottimizzazioni sulle semi-join, con il comando SET @@optimizer_switch='semijoin=off'.