SQLinfo.ru - Все о MySQL

MySQL 5.7: улучшение производительности запросов, использующих производные таблицы

Дата: 13.01.2017

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

Недавно мой коллега Roy презентовал свою работу, которая позволяет оптимизатору запросов в MySQL 5.7 проводить слияние from-подзапросов (называемых также производными таблицами) с внешним запросом. В этой статье я покажу пример того, как эта работа повышает производительность запросов MySQL.

В тесте DBT-3 есть следующий запрос, содержащий 2 производные таблицы:

SELECT t2.o_clerk, t1.price - t2.o_totalprice
FROM
    (SELECT l_orderkey, SUM( l_extendedprice * (1 - l_discount)) price
     FROM lineitem GROUP by l_orderkey) t1
JOIN
    (SELECT o_clerk, o_orderkey, o_totalprice
     FROM orders
     WHERE o_orderdate BETWEEN '1995-01-01' AND '1995-12-31') t2
ON t1.l_orderkey = t2.o_orderkey WHERE t1.price > t2.o_totalprice;

До версии 5.7 Mysql выполнял каждый подзапрос отдельно и сохранял результаты в 2 временные таблицы, которые затем соединял (JOIN):

Эту диаграмму показывает компонент Visual EXPLAIN в MySQL Workbench. Каждый подзапрос отображен внутри рамки, которая представляет временную таблицу. Диаграмма показывает, что временная таблица для производной таблицы t2 будет полностью сканирована и для каждой её строки будет проведен поиск по индексу во временной таблице t1. Это также можно увидеть в традиционном выводе команды EXPLAIN:

+----+-------------+------------+-------+--------------------------+-------------+---------+---------------+----------+--------------------------+
| id | select_type | table      | type  | possible_keys            | key         | key_len | ref           | rows     | Extra                    |
+----+-------------+------------+-------+--------------------------+-------------+---------+---------------+----------+--------------------------+
| 1  | PRIMARY     | <derived3> | ALL   | NULL                     | NULL        | NULL    | NULL          | 4812318  | NULL                     |
+----+-------------+------------+-------+--------------------------+-------------+---------+---------------+----------+--------------------------+
| 1  | PRIMARY     | <derived2> | ref   | <auto_key0>              | <auto_key0> | 4       | t2.o_orderkey | 599860   | Using where; Using index |
+----+-------------+------------+-------+--------------------------+-------------+---------+---------------+----------+--------------------------+
| 3  | DERIVED     | orders     | ALL   | i_o_orderdate            | NULL        | NULL    | NULL          | 15000000 | Using where              |
+----+-------------+------------+-------+--------------------------+-------------+---------+---------------+----------+--------------------------+
| 2  | DERIVED     | lineitem   | index | PRIMARY, i_l_shipdate, … | PRIMARY     | 8       | NULL          | 59986052 | NULL                     |
+----+-------------+------------+-------+--------------------------+-------------+---------+---------------+----------+--------------------------+

EXPLAIN показывает, что запрос состоит из трех частей. Основной (внешний) запрос с ID равным 1 и два from-подзапроса с идентификаторами 2 и 3. Внешний запрос обращается к таблицам <derived2> и <derived3>, которые являются временными таблицами, сгенерированными запросами 2 и 3 соответственно. В таблице <derived2> создан индекс <auto_key0>, который используется при поиске соответствий значениям колонки o_orderkey из таблицы <derived3>.

Обратите внимание: возможность создавать индексы на производных таблицах была введена в MySQL 5.6. В более ранних версиях выполнение этого запроса могло длиться несколько месяцев, так как сервер MySQL делал полное сканирование одной временной таблицы для каждого значения из другой временной таблицы.

В MySQL 5.7 оптимизатор пытается избавиться от from-подзапросов, путем их слияния с основным запросом. Это возможно не во всех случаях. Visual EXPLAIN показывает, что удалось упростить только один подзапрос:

Подзапрос, производящий таблицу t1, не может быть объединен с внешним, так как содержит группировку. Поэтому MySQL 5.7 материализует этот подзапрос во временную таблицу, сканирует её и для каждого значения делает поиск по первичному индексу напрямую в таблице orders. Табличный вывод EXPLAIN также показывает, что один из подзапросов был объединен с внешним запросом.

+----+-------------+------------+--------+--------------------------+-------------+---------+---------------+----------+--------------------------+
| id | select_type | table      | type   | possible_keys            | key         | key_len | ref           | rows     | Extra                    |
+----+-------------+------------+--------+--------------------------+-------------+---------+---------------+----------+--------------------------+
| 1  | PRIMARY     | <derived2> | ALL    | NULL                     | NULL        | NULL    | NULL          | 59986052 | NULL                     |
+----+-------------+------------+--------+--------------------------+-------------+---------+---------------+----------+--------------------------+
| 1  | PRIMARY     | orders     | eq_ref | PRIMARY, i_o_orderdate   | PRIMARY     | 4       | t1.l_orderkey | 1        | Using where              |
+----+-------------+------------+--------+--------------------------+-------------+---------+---------------+----------+--------------------------+
| 2  | DERIVED     | lineitem   | index  | PRIMARY, i_l_shipdate, … | PRIMARY     | 8       | NULL          | 59986052 | NULL                     |
+----+-------------+------------+--------+--------------------------+-------------+---------+---------------+----------+--------------------------+

Как вы можете видеть, блок №3 был объединен с блоком №1.

Результаты тестов показывают, что в MySQL 5.7 запрос выполняется в 2 раза быстрее, чем в MySQL 5.6:

Разобранный выше пример был частью моего выступления “How to Analyze and Tune MySQL Queries for Better Performance” на конференции Oracle OpenWorld 2015. Скачать слайды презентации можно тут.

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

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

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