Латеральная оптимизация производных запросов

MariaDB поддерживает латеральную оптимизацию производных запросов, также известную в некоторых источниках как «оптимизация разделения группировок» или «оптимизация разделения материализованных данных».

Описание

Сфера применения данной оптимизации:

• Запрос использует производную таблицу (или представление, или нерекурсивную CTE).
• Производная таблица/представление/CTE имеет операцию GROUP BY как свою операцию верхнего уровня.
• Запрос требует данных только из нескольких групп GROUP BY.

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

create view OCT_TOTALS as
select
  customer_id,
  SUM(amount) as TOTAL_AMT
from orders
where
  order_date BETWEEN '2017-10-01' and '2017-10-31'
group by
  customer_id;

И запрос, который выполняет объединение с таблицей клиентов, чтобы получить октябрьские итоги для «Клиента №1» и Клиента №2:

select *
from
  customer, OCT_TOTALS
where
  customer.customer_id=OCT_TOTALS.customer_id and
  customer.customer_name IN ('Customer#1', 'Customer#2')

Перед латеральной оптимизацией производных запросов MariaDB выполнял запрос следующим образом:

1. Материализовать представление OCT_TOTALS. Это в сущности вычисляет OCT_TOTALS для всех клиентов.
2. Объединить его с таблицей customer.

План запроса (EXPLAIN) выглядел бы так:

+------+-------------+------------+-------+---------------+-----------+---------+---------------------------+-------+--------------------------+
| id   | select_type | table      | type  | possible_keys | key       | key_len | ref                       | rows  | Extra                    |
+------+-------------+------------+-------+---------------+-----------+---------+---------------------------+-------+--------------------------+
|    1 | PRIMARY     | customer   | range | PRIMARY,name  | name      | 103     | NULL                      | 2     | Using where; Using index |
|    1 | PRIMARY     | <derived2> | ref   | key0          | key0      | 4       | test.customer.customer_id | 36    |                          |
|    2 | DERIVED     | orders     | index | NULL          | o_cust_id | 4       | NULL                      | 36738 | Using where              |
+------+-------------+------------+-------+---------------+-----------+---------+---------------------------+-------+--------------------------+

Очевидно, что шаг №1 очень неэффективен: мы вычисляем итоги для всех клиентов в базе данных, в то время как нам понадобятся только итоги для двух клиентов. (Если клиентов 1000, то мы выполняем в 500 раз больше работы, чем необходимо).

Латеральная оптимизация производных запросов решает эту проблему. Она преобразует вычисление OCT_TOTALS в то, что стандарт SQL называет «латеральным подзапросом»: подзапрос, который может иметь зависимости от внешних таблиц. Это позволяет «продвинуть» условие равенства customer.customer_id=OCT_TOTALS.customer_id вниз в производную таблицу/представление, где оно может быть использовано для ограничения вычислений, вычисляя итоги только для интересующего клиента.

План запроса будет выглядеть следующим образом:

1. Прочитать таблицу customer и найти customer_id для Клиента №1 и Клиента №2.
2. Для каждого customer_id вычислить октябрьские итоги для этого конкретного клиента.

Вывод EXPLAIN будет выглядеть следующим образом:

+------+-----------------+------------+-------+---------------+-----------+---------+---------------------------+------+--------------------------+
| id   | select_type     | table      | type  | possible_keys | key       | key_len | ref                       | rows | Extra                    |
+------+-----------------+------------+-------+---------------+-----------+---------+---------------------------+------+--------------------------+
|    1 | PRIMARY         | customer   | range | PRIMARY,name  | name      | 103     | NULL                      | 2    | Using where; Using index |
|    1 | PRIMARY         | <derived2> | ref   | key0          | key0      | 4       | test.customer.customer_id | 2    |                          |
|    2 | LATERAL DERIVED | orders     | ref   | o_cust_id     | o_cust_id | 4       | test.customer.customer_id | 1    | Using where              |
+------+-----------------+------------+-------+---------------+-----------+---------+---------------------------+------+--------------------------+

Обратите внимание на строку с id=2: select_type - это LATERAL DERIVED. И таблица customer использует доступ ref, ссылающийся на customer.customer_id, что обычно не разрешается для производных таблиц.

В EXPLAIN FORMAT=JSON выводе оптимизация отображается следующим образом:

...
        "table": {
          "table_name": "<derived2>",
          "access_type": "ref",
...
          "materialized": {
            "lateral": 1,

Обратите внимание на элемент "lateral": 1.

Управление оптимизацией

Латеральная оптимизация производных запросов включена по умолчанию, оптимизатор принимает решение о её использовании на основе стоимости.

Если вам нужно отключить оптимизацию, она имеет флаг optimizer_switch. Отключить её можно так:

set optimizer_switch='split_materialized=off'

Ссылки

• Задача Jira: https://jira.mariadb.org/browse/MDEV-13369
• Комитт: https://github.com/MariaDB/server/commit/b14e2b044b