SQLinfo.ru - Все о MySQL Highload++ Junior 2017

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

Дата: 13.01.2017

Это перевод статьи Øystein Grøvlen.

В MySQL одно-табличные UPDATE/DELETE имеют меньше способов оптимизации по сравнению с 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 запрос:


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, описанном ранее.

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

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

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