Debug dell'ottimizzatore di MariaDB 5.3
Contents
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:
FirstMatchMaterializationLooseScanDuplicateWeedout
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
