В MySQL: REPLACE vs INSERT...ON DUPLICATE KEY UPDATE

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

Ключевое отличие между командами REPLACE и INSERT ... ON DUPLICATE KEY UPDATE заключается в том, как они обращаются с уже существующими данными: REPLACE сначала удаляет, а затем создает запись anew, в то время как INSERT ... ON DUPLICATE KEY UPDATE обновляет только конкретно указанные поля, оставляя остальные данные без изменений.

Рассмотрим пример на REPLACE:

REPLACE INTO users (id, name) VALUES (1, 'Jane');

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

А вот пример на INSERT ... ON DUPLICATE KEY UPDATE:

INSERT INTO users (id, name) VALUES (1, 'Jane') ON DUPLICATE KEY UPDATE name = VALUES(name);

Здесь мы обновим только поле name у записи с id 1, при этом значение age останется прежним.

Детальный анализ

Ограничения внешних ключей

При использовании REPLACE могут возникнуть проблемы с внешними связями из-за его удаления и вставки записи. Это может привести к каскадному удалению в связанных таблицах. В отличие от этого, INSERT ... ON DUPLICATE KEY UPDATE не влияет на связи между таблицами и не вызывает каскадного удаления.

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

Последствия для автоинкрементных полей и вопросы производительности

Использование команды REPLACE может увеличить счётчик поля автоинкремента, в результате чего он может быстро исчерпаться. Напротив, INSERT ... ON DUPLICATE KEY UPDATE не изменяет значение автоинкремента при обновлении записи, что способствует оптимизации размера таблицы.

Побочные эффекты от обновления

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

Преимущества атомарной операции и избегание блокировок

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

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

Допустим, вы добавляете новую песню в плейлист или обновляете имеющуюся:

Обновление плейлиста 📻 | Песня 1 | Песня 2 | Песня 3 |

REPLACE: Если запись уже есть, то она удаляется и создается заново.

🎶🚫🎶+🎶➕ = | Песня 1 | НОВАЯ Песня 2 | Песня 3 |

INSERT ... ON DUPLICATE KEY UPDATE: Если запись найдена, обновляются только указанные в запросе данные.

🎶🔍🔑🔄🏷️ = | Песня 1 | Обновленная Песня 2 | Песня 3 |

Оба метода управляют вашими данными, но они обновляют их по-разному.

Дополнительные соображения

Влияние размера: таблицы и колонки

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

Типичные задачи — наилучший критерий выбора

Выбор между REPLACE и INSERT ... ON DUPLICATE KEY UPDATE непосредственно зависит от конкретной задачи. Если для вас важна целостность данных, вполне возможно, что лучше использовать INSERT ... ON DUPLICATE KEY UPDATE.

Особенности у Federated Storage Engine (Федеративных движков хранения)

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

Руководство по принятию решений

Поддержание сложных отношений

Очень важно поддерживать структуру взаимосвязей в реляционной базе данных. В подавляющем большинстве случаев для этого лучше всего подходит INSERT ... ON DUPLICATE KEY UPDATE.

Подход к тестированию производительности

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

Глубокое понимание команд

Перед применением команд REPLACE и INSERT ... ON DUPLICATE KEY UPDATE тщательно изучите их особенности и оцените возможные последствия их использования.

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

1. "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE" – Stack Overflow — Обсуждения различий и случаев использования.
2. Руководство по MySQL 8.0 :: 13.2.12 Оператор REPLACE — Документация MySQL на тему REPLACE.
3. Руководство по MySQL 8.0 :: 13.2.7.2 Оператор INSERT ... ON DUPLICATE KEY UPDATE — Объяснение INSERT ... ON DUPLICATE KEY UPDATE в контексте MySQL.
4. INSERT ON DUPLICATE KEY UPDATE – База знаний MariaDB — Руководство по использованию этого метода в MariaDB.
5. Техники Upsert в MySQL: INSERT If Not Exists | Atlassian — Обзор различных способов внесения данных в MySQL.