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. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.
|
