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

В 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 раза:

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