Отладка оптимизатора MariaDB 5.3

MariaDB 5.3 имеет патч для отладки оптимизатора. Патч добавлен в

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

Патч обернут в #ifdef, но есть #define прямо в mysql_priv.h, поэтому простое компилирование этого дерева должно создать бинарник с включенной отладкой оптимизатора.

Патч добавляет две системные переменные:

• @@debug_optimizer_prefer_join_prefix
• @@debug_optimizer_dupsweedout_penalized

переменные присутствуют как сеансовые/глобальные переменные и также могут быть установлены через командную строку сервера.

debug_optimizer_prefer_join_prefix

Если эта переменная не NULL, предполагается, что она указывает префикс объединения в виде списка алиасов таблиц, разделенных запятыми:

set debug_optimizer_prefer_join_prefix='tbl1,tbl2,tbl3';

Оптимизатор постарается построить план объединения, соответствующий указанному префиксу объединения. Это делается путем сравнения рассматриваемых им префиксов объединения с @@debug_optimizer_prefer_join_prefix, и умножения стоимости на миллион, если план не соответствует префиксу.

В результате вы можете более или менее контролировать порядок объединения. Например, рассмотрим этот запрос:

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)

и запросим порядок объединения 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)

У нас получилось.

Обратите внимание, что это все еще подход «лучшее усилие»:

• у вас не получится принудительно установить порядок объединения, который оптимизатор считает недействительным (например, для «t1 LEFT JOIN t2» вы не сможете получить порядок объединения t2,t1).
• Оптимизатор выполняет различные операции обрезки планов и может отбросить запрошенный порядок объединения, прежде чем у него будет шанс узнать, что он в миллион раз дешевле, чем любой другой.

Полусоединения

Возможна принудительная установка порядка объединения для объединений плюс полусоединения. Это может привести к использованию другой стратегии:

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)

Материализация полусоединения — это несколько особый случай, потому что «префикс объединения» не совсем то, что вы видите в выводе EXPLAIN. Для материализации полусоединения:

• не ставьте «<subqueryN>» в @@debug_optimizer_prefer_join_prefix
• вместо этого поместите все таблицы материализации в то место, где вы хотите расположить <subqueryN> строку.
• Попытки контролировать порядок объединения внутри вложенного узла материализации будут безуспешны. Пример: мы хотим 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)

но получаем A-B-C-AA.

debug_optimizer_dupsweedout_penalized

Существует четыре стратегии выполнения полусоединения:

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

Для первых трех стратегий существуют флаги в @@optimizer_switch, которые можно использовать для их отключения. У стратегии DuplicateWeedout флага нет. Это сделано по причине, так как эта стратегия является универсальной и может обрабатывать все типы подзапросов во всех типах порядков объединения. (Мы медленно приближаемся к моменту, когда будет возможно работать с FirstMatch включенным и всем остальным отключенным, но мы еще не достигли этого.)

Поскольку DuplicateWeedout отключить нельзя, есть случаи, когда она «вмешивается», выбираясь вместо необходимой вам стратегии. Для этого и предназначена переменная debug_optimizer_dupsweedout_penalized. Если установить:

MariaDB [test]> set debug_optimizer_dupsweedout_penalized=TRUE;

...стоимость планов запросов, использующих DuplicateWeedout, будет умножена на миллион. Это не означает, что вы избавитесь от DuplicateWeedout — из-за ошибки #898747 все еще может использоваться DuplicateWeedout, даже если существует более дешевый план. Частичным решением является запуск с

MariaDB [test]> set optimizer_prune_level=0;

Можно использовать как debug_optimizer_dupsweedout_penalized, так и debug_optimizer_prefer_join_prefix одновременно. Это должно дать вам желаемую стратегию и порядок объединения.

Дополнительная информация

• См. mysql-test/t/debug_optimizer.test (в исходном коде MariaDB) для примеров