MariaDB 5.3 ha una patch per il debug dell'ottimizzatore. La patch è in

lp:maria-captains/maria/5.3-optimizer-debugging

Questa patch si trova in una #ifdef, ma c'è un #define diretto in mysql_priv.h, perciò compilando semplicemente l'albero si dovrebbe ottenere un binario con il debug dell'ottimizzatore abilitato.

La patch aggiunge due variabili di sistema:

  • @@debug_optimizer_prefer_join_prefix
  • @@debug_optimizer_dupsweedout_penalized

Esse sono sia di sessione sia globali e sono impostabili tramite la riga di comando del server.

debug_optimizer_prefer_join_prefix

Se questa variabile è diversa da NULL, specifica il prefisso di una join, sottoforma di lista separata da virgole degli alias delle tabelle:

set debug_optimizer_prefer_join_prefix='tbl1,tbl2,tbl3';

L'ottimizzatore fa del suo meglio per costruire un piano per la join che corrisponda al prefisso specificato. Per fare questo, confronta i prefissi delle join che sta considerando con @@debug_optimizer_prefer_join_prefix, e se il piano non corrisponde moltiplica il costo per un milione.

In questo modo è più o meno possibile decidere l'ordine delle join. Per esempio, si consideri la query seguente:

MariaDB [test]> explain select * from ten A, ten B, ten C;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                              |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
|  1 | SIMPLE      | A     | ALL  | NULL          | NULL | NULL    | NULL |   10 |                                    |
|  1 | SIMPLE      | B     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using join buffer (flat, BNL join) |
|  1 | SIMPLE      | C     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using join buffer (flat, BNL join) |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
3 rows in set (0.00 sec)

E si richieda come ordine di join: C,A,B:

MariaDB [test]> set debug_optimizer_prefer_join_prefix='C,A,B';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain select * from ten A, ten B, ten C;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                              |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
|  1 | SIMPLE      | C     | ALL  | NULL          | NULL | NULL    | NULL |   10 |                                    |
|  1 | SIMPLE      | A     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using join buffer (flat, BNL join) |
|  1 | SIMPLE      | B     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using join buffer (flat, BNL join) |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
3 rows in set (0.00 sec)

Ed ecco fatto.

Si noti che questo approccio è basato sullo sforzo minore:

  • Non è possibile forzare un ordine delle join che l'ottimizzatore considera non valido (per esempio, per "t1 LEFT JOIN t2" non è possibile ottenere un ordine di: t2,t1).
  • L'ottimizzatore esclude diversi piani e potrebbe scartare anche l'ordine di join richiesto, se valuta che sia un milione di volte più costoso di un altro.

Semi-join

E' anche possibile forzare un ordine con alcune join più alcune semi-join. Se lo si fa, è possibile che venga scelta una diversa strategia:

MariaDB [test]> set debug_optimizer_prefer_join_prefix=NULL;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain select * from ten A where a in (select B.a from ten B, ten C where C.a + A.a < 4);
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                      |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------+
|  1 | PRIMARY     | A     | ALL  | NULL          | NULL | NULL    | NULL |   10 |                            |
|  1 | PRIMARY     | B     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where                |
|  1 | PRIMARY     | C     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; FirstMatch(A) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------+
3 rows in set (0.00 sec)

MariaDB [test]> set debug_optimizer_prefer_join_prefix='C,A,B';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain select * from ten A where a in (select B.a from ten B, ten C where C.a + A.a < 4);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                           |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
|  1 | PRIMARY     | C     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Start temporary                                 |
|  1 | PRIMARY     | A     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (flat, BNL join) |
|  1 | PRIMARY     | B     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; End temporary                      |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
3 rows in set (0.00 sec)

La materializzazione delle semi-join è un caso particolare, perché "join prefix" non è esattamente ciò che compare nell'output di EXPLAIN. Per la materializzazione delle semi-join:

  • "<subqueryN>" non deve essere inserito in @@debug_optimizer_prefer_join_prefix.
  • Inserire invece le tabelle materializzate, dove si desidera avere la riga <subqueryN>.
  • Non è possibile in alcun modo controllare l'ordine delle join in una materializzazione annidata. Esempio: si desidera A-C-B-AA:
MariaDB [test]> set debug_optimizer_prefer_join_prefix='A,C,B,AA';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain select * from ten A, ten AA where A.a in (select B.a from ten B, ten C);
+----+-------------+-------------+--------+---------------+--------------+---------+------+------+------------------------------------+
| id | select_type | table       | type   | possible_keys | key          | key_len | ref  | rows | Extra                              |
+----+-------------+-------------+--------+---------------+--------------+---------+------+------+------------------------------------+
|  1 | PRIMARY     | A           | ALL    | NULL          | NULL         | NULL    | NULL |   10 |                                    |
|  1 | PRIMARY     | <subquery2> | eq_ref | distinct_key  | distinct_key | 5       | func |    1 |                                    |
|  1 | PRIMARY     | AA          | ALL    | NULL          | NULL         | NULL    | NULL |   10 | Using join buffer (flat, BNL join) |
|  2 | SUBQUERY    | B           | ALL    | NULL          | NULL         | NULL    | NULL |   10 |                                    |
|  2 | SUBQUERY    | C           | ALL    | NULL          | NULL         | NULL    | NULL |   10 |                                    |
+----+-------------+-------------+--------+---------------+--------------+---------+------+------+------------------------------------+
5 rows in set (0.00 sec)

Ma si ottiene A-B-C-AA.

debug_optimizer_dupsweedout_penalized

Esistono quattro strategie di esecuzione delle semi-join:

  1. FirstMatch
  2. Materialization
  3. LooseScan
  4. DuplicateWeedout

Le prime tre strategie possono essere disabilitate attraverso altrettanti flag in @@optimizer_switch. DuplicateWeedout invece non ha alcun flag. Il motivo è che questa strategia può gestire tutti i tipi di subquery, con tutti i tipi di ordini delle join, e viene usata quando non è possibile sceglierne un'altra. (Prima o poi sarà possibile abilitare FirstMatch e disabilitare tutto il resto, ma per il momento non è così.)

Siccome non è possibile disabilitare DuplicateWeedout, vi sono casi in cui viene scelta al posto della strategia che si desidera. Ecco a cosa serve debug_optimizer_dupsweedout_penalized. Se si imposta:

MariaDB [test]> set debug_optimizer_dupsweedout_penalized=TRUE;

...il costo dei piani delle query che fanno uso di DuplicateWeedout viene moltiplicato per un milione. Questo non significa che ci si libera realmente di DuplicateWeedout è ancora possibile che venga scelta in luogo di piani meno costosi, a causa del bug Bug #898747. Un rimedio parziale è eseguire:

MariaDB [test]> set optimizer_prune_level=0;

E' possibile utilizzare allo stesso tempo sia debug_optimizer_dupsweedout_penalized, sia debug_optimizer_prefer_join_prefix. In questo modo si dovrebbe riuscire a ottenere la strategia e l'ordine di join desiderati.

Approfondimenti

  • Alcuni esempi sono repesibili in mysql-test/t/debug_optimizer.test, nei sorgenti di MariaDB

Commenti

Sto caricando i commenti......