Оптимизированный способ множественного обновления строк в MySQL

Быстрый ответ

Множественные обновления в MySQL можно выполнять с помощью оператора CASE. Приведем пример:

UPDATE your_table
SET your_column = CASE
    WHEN condition1 THEN value1
    WHEN condition2 THEN value2
    ELSE your_column
END
WHERE your_column IN (value1, value2);

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

Метод "INSERT ... ON DUPLICATE KEY UPDATE"

Повышение эффективности обновлений в MySQL можно достичь путем использования операцией INSERT...ON DUPLICATE KEY UPDATE. Вот пример ее применения:

INSERT INTO your_table (id, Col1, Col2)
VALUES 
    (1, 'Value1', 'Value2'),    -- Ввод новых данных!
    (2, 'Value3', 'Value4'),    -- Добавляем еще новые данные!
    (3, 'Value5', 'Value6')     -- Добро пожаловать, новые данные!
ON DUPLICATE KEY UPDATE
    Col1 = VALUES(Col1),    -- Если нашлось совпадение, обновляем данные!
    Col2 = VALUES(Col2);    -- Найдем для тебя обновление, друг мой!

Заметьте: наличие уникального индекса в поле id позволяет системе определять, когда вставить новую запись, а когда обновить существующую. Такой метод позволяет сократить количество операций до одного запроса, уменьшая нагрузку на сервер баз данных.

CASE против INSERT ... ON DUPLICATE KEY UPDATE: выбор метода

Ваш выбор между CASE и INSERT ... ON DUPLICATE KEY UPDATE должен зависеть от конкретной задачи.

• Оператор CASE подойдет, когда требуется произвести обновления, основанные на сложных условиях и когда важно использование условной логики.
• Используйте INSERT, когда вам необходимы массовые обновления больших объемов данных с более простыми или однородно распределенными условиями.

Тесты производительности показывают, что в некоторых случаях оператор CASE может быть в два раза быстрее, чем INSERT...ON DUPLICATE. Но, не следует слепо доверять этим выводам. Важно проводить индивидуальные тесты!

Использование JOIN и подзапросов

Также можно использовать JOIN в сочетании с подзапросами для реализации сложных сценариев обновления:

UPDATE your_table t
JOIN (
    SELECT id, new_value
    FROM another_table
) sub
ON t.id = sub.id
SET t.your_column = sub.new_value;

Такой подход — это своего рода швейцарский армейский нож в мире работы с базами данных: он позволяет сочетать различные источники данных и параметризовать обновления в соответствии с усложняющимися требованиями.

Визуализация

Представьте, что вы дали команду группе художников перекрасить стены согласно определенным инструкциям:

Стена 1: 🖌️🟢 ➡   До обновления
Стена 2: 🖌️🔵 ➡   До обновления
Стена 3: 🖌️🔴 ➡   До обновления

Запрос SQL:

UPDATE my_walls
SET color = CASE
    WHEN wall_id = 1 THEN 'blue'    -- Стена 1 перекрашивается в синий!
    WHEN wall_id = 2 THEN 'red'     -- Стена 2 станет красной!
    WHEN wall_id = 3 THEN 'green'   -- Стена 3 обретет зеленый цвет!
END
WHERE wall_id IN (1, 2, 3);

Результат будет следующим:

Стена 1: 🖌️🔵 ✅ После обновления
Стена 2: 🖌️🔴 ✅ После обновления
Стена 3: 🖌️🟢 ✅ После обновления

Так, будто художники исполнили всю работу одновременно и всего одной командой, множество строк в базе данных могут быть обновлены одним SQL-запросом.

Особенности и проблемы

Особенности работы с полями, не допускающими NULL значения

Когда дело касается полей, не допускающих NULL-значений и не имеющих значений по умолчанию, стоит явно задавать значение, чтобы избежать ошибок вроде "столбец 'Col' не может быть NULL":

INSERT INTO your_table (id, Col1, Col2)
VALUES (1, 'Value1', 'Value2')
ON DUPLICATE KEY UPDATE
    Col1 = IFNULL(VALUES(Col1), 'default_value'),    -- В случае отсутствия значения поля, задаем значение по умолчанию!
    Col2 = IFNULL(VALUES(Col2), 'default_value');    -- Предотвращаем ошибки пустого поля!

Подробнее об этом расскажет наш спикер на видео

Учет репликации и логирования

Учтите, что операции изменения данных могут привести к увеличению объема хранимых логов и данных для репликации. Так, транзакционные обновления могут привести к увеличению объема логов. С другой стороны, использование INSERT ... ON DUPLICATE KEY UPDATE обычно генерирует меньше логов, что предпочтительно для систем с активной репликацией.

Особенности работы AUTO_INCREMENT в InnoDB

Помните, что INSERT ... ON DUPLICATE KEY UPDATE может неожиданно увеличить значения AUTO_INCREMENT в системах InnoDB. В то время как в движке MyISAM такого эффекта не будет.

Ясность псевдонимов таблиц при обновлениях

При выполнении UPDATE с участием нескольких таблиц или подзапросов рекомендуется использовать псевдонимы таблиц для лучшей читаемости кода:

UPDATE your_table AS t
INNER JOIN another_table AS a
ON t.id = a.id
SET t.col = a.val;                -- Ясный и понятный код обновления!

Полезные материалы

1. MySQL :: Руководство по Справочнику MySQL 8.0 :: 13.2.17 Синтаксис команды UPDATE — Подробное руководство об использовании синтаксиса команды UPDATE в MySQL.
2. sql – Множественные обновления в MySQL – Stack Overflow — Обсуждение вопроса массовых обновлений в MySQL с множеством ответов от пользователей сообщества.
3. MySQL :: Руководство по Справочнику MySQL 8.0 :: 13.3.1 Команды START TRANSACTION, COMMIT и ROLLBACK — Полное руководство по транзакциям в MySQL и безопасным массовым обновлениям.
4. MySQL :: Руководство по Справочнику MySQL 8.0 :: 13.2.15 Подзапросы — Узнайте, как эффективно использовать подзапросы в UPDATE для реализации сложных операций.
5. MySQL :: Руководство по Справочнику MySQL 8.0 :: 8.3 Оптимизация работы с индексами — Советы по оптимизации индексов для высокопроизводительных обновлений в MySQL.