Способ ускорения одно-табличных UPDATE/DELETE

Для одно-табличных UPDATE/DELETE в MySQL предусмотрено меньше способов оптимизации, чем для запросов SELECT. Я думаю, что главной причиной этого является стремление ограничить расходы оптимизатора в очень простых случаях. Однако, это также означает, что возможные оптимизации иногда пропускаются для более сложных UPDATE/DELETE запросов.

Пример

В тесте DBT-3 есть запрос, увеличивающий на 10% цену деталей от поставщиков из определенных стран:

UPDATE part
SET p_retailprice = p_retailprice*1.10
WHERE p_partkey IN
(SELECT ps_partkey
FROM partsupp JOIN supplier
ON ps_suppkey = s_suppkey
WHERE s_nationkey = 4);

Компонент Visual EXPLAIN в MySQL Workbench показывает какой план выполнения выбрал оптимизатор для этого UPDATE запроса:

То есть для каждой строки в таблице part MySQL будет проверять - поставляется ли эта деталь поставщиком, указанной национальности.

SELECT * FROM part
WHERE p_partkey IN
(SELECT ps_partkey
FROM partsupp JOIN supplier
ON ps_suppkey = s_suppkey
WHERE s_nationkey = 4);

В MySQL 5.6 оптимизатор применит semi-join преобразование для этого запроса. Поэтому план выполнения значительно отличается от рассмотренного ранее UPDATE запроса.

Как видно из рисунка, план выполнения не содержит подзапросов. Запрос был трансформирован в join трёх таблиц. Огромное преимущество этого преобразования заключается в том, что теперь оптимизатор может выбирать оптимальный порядок соединения таблиц. Вместо того, чтобы сканировать все 179 000 деталей, он начнет с предполагаемых 414 поставщиков из указанной страны и найдет все поставляемые ими детали. Очевидно, что это более эффективно, и было бы хорошо использовать такую же стратегию при выполнении обновления.

Много-табличный трюк

В отличии от одно-табличного UPDATE в случае много-табличного UPDATE оптимизатор MySQL может использовать все доступные оптимизации. Это означает, что если переписать запрос следующим образом, то к нему будут применены semi-join оптимизации:

UPDATE part, (SELECT 1) dummy
SET p_retailprice = p_retailprice*1.10
WHERE p_partkey IN
(SELECT ps_partkey
FROM partsupp JOIN supplier
ON ps_suppkey = s_suppkey
WHERE s_nationkey = 4);

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

mysql> UPDATE part SET p_retailprice = p_retailprice*1.10 WHERE p_partkey IN (SELECT ps_partkey FROM partsupp JOIN supplier ON ps_suppkey = s_suppkey WHERE s_nationkey = 4);
Query OK, 31097 rows affected, 28003 warnings (2.63 sec)
Rows matched: 31097 Changed: 31097 Warnings: 28003

mysql> ROLLBACK;
Query OK, 0 rows affected (0.20 sec)

mysql> UPDATE part, (SELECT 1) dummy SET p_retailprice = p_retailprice*1.10 WHERE p_partkey IN (SELECT ps_partkey FROM partsupp JOIN supplier ON ps_suppkey = s_suppkey WHERE s_nationkey = 4);
Query OK, 31097 rows affected, 28003 warnings (0.40 sec)
Rows matched: 31097 Changed: 31097 Warnings: 28003

Как видно, использование этого трюка уменьшило время выполнения с 2.63 до 0.40 секунд. (Я выполнил оба запроса несколько раз, так что указанное время выполнения соответствует состоянию, когда все запрашиваемые данные в памяти.)

Много-табличный DELETE

Аналогичный трюк можно использовать и для DELETE запросов. Вместо одно-табличного запроса:

DELETE FROM part
WHERE p_partkey IN
(SELECT ps_partkey
FROM partsupp JOIN supplier
ON ps_suppkey = s_suppkey
WHERE s_nationkey = 4);

вы можете использовать эквивалентный много-табличный вариант:

DELETE part FROM part
WHERE p_partkey IN
(SELECT ps_partkey
FROM partsupp JOIN supplier
ON ps_suppkey = s_suppkey
WHERE s_nationkey = 4);

Это изменение дает такое же улучшение производительности как и в случае UPDATE, описанном ранее.

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