SQLinfo.ru - Все о MySQL Фестиваль «Российские интернет-технологии» 2017

MySQL 8.0: улучшение производительности при использовании ОТВ

Дата: 20.04.2017

Данный материал является переводом статьи Øystein Grøvlen.

В MySQL 8.0 были добавлены Обобщенные Табличные Выражения (ОТВ). Мой коллега Guilhem написал серию статей о том как их использовать, также об этом можно прочитать в документации. В этой статье я сосредоточусь на том, как использование ОТВ вместо представлений или производных таблиц может улучшить производительность.

Как обычно, в качестве примера я буду использовать запрос из теста DBT-3 В этот раз рассмотрим запрос №15, называющийся Top Supplier Query:


SELECT s_suppkey, s_name, s_address, s_phone, total_revenue
FROM supplier, revenue0
WHERE s_suppkey = supplier_no
  AND total_revenue = (SELECT MAX(total_revenue) FROM revenue0)
ORDER BY s_suppkey;
 

В запросе дважды используется представление revenue0: в части FROM и в подзапросе в части WHERE. Представление определяется следующим образом:


CREATE VIEW revenue0(supplier_no , total_revenue) AS
    SELECT l_suppkey, SUM(l_extendedprice * (1 - l_discount))
    FROM lineitem
    WHERE l_shipdate >= '1996-07-01'
      AND l_shipdate < DATE_ADD('1996-07-01', INTERVAL '90' DAY)
    GROUP BY l_suppkey;
 

Представление находит общий доход каждого поставщика за указанный период в 90 дней. Так как представление содержит группировку, то MySQL не может объединить его с запросом, в котором используется представление (т.е. добавить во внешний запрос соответствующие части из определения представления). Вместо этого содержимое представления заносится во временную таблицу. Компонент Visual EXPLAIN в MySQL Workbench показывает следующий план выполнения для запроса №15:

На диаграмме видно, что представление выполняется и материализуется дважды; по одному разу на каждое упоминание в запросе.

Для того, чтобы использовать ОТВ вместо представления, нужно поместить определение представления перед запросом, используя оператор WITH:


WITH revenue0(supplier_no , total_revenue) AS (
    SELECT l_suppkey, SUM(l_extendedprice * (1 - l_discount))
    FROM lineitem
    WHERE l_shipdate >= '1996-07-01'
      AND l_shipdate < DATE_ADD('1996-07-01', INTERVAL '90' DAY)
    GROUP BY l_suppkey )
SELECT s_suppkey, s_name, s_address, s_phone, total_revenue
FROM supplier, revenue0
WHERE s_suppkey = supplier_no
  AND total_revenue = (SELECT MAX(total_revenue) FROM revenue0)
ORDER BY s_suppkey;
 

Visual EXPLAIN показывает, что ОТВ материализуется один раз:

Иными словами, MySQL использует одну и ту же временную таблицу при многократном упоминании ОТВ в запросе. При исполнении запроса №15 большая часть времени уходит на материализацию. Таким образом, за счет однократной материализации время выполнения запроса сокращается почти в 2 раза:

Дата публикации: 20.04.2017

© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.

Статьи :
 Установка и настройка MySQL
 Коды ошибок в MySQL
 Программирование в MySQL
>Оптимизация производительности
 Кодировка символов в MySQL
 Хранение данных в MySQL
 MySQL Cluster
См. также:
 Оптимизация производительности MySQL
 Онлайн-курс по оптимизации MySQL
 Услуги по оптимизации MySQL