Что значит duplicate key

IntSystem.org

Случаи из опыта разработки различных WEB проектов. Интересные факты, статьи, впечатления. Программирование и все о нем в сфере WEB.

Неявная проблема ON DUPLICATE KEY и AUTO INCREMENT

Сейчас хочу вам предложить обсудить один интересный факт. Возьмем обычную таблицу:

1. id — первичный ключ с автоинкриментом
2. date — уникальный ключ
3. value — некие данные

Не так давно я писал насколько круто использовать конструкцию ON DUPLICATE KEY. Но как оказалось, данная конструкция имеет один подводный камень.

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

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

Допустим это некий скрипт счетчик посещений за день.

Суть в том что любое выполнение этого запроса, вне зависимости от того был INSERT или UPDATE, приводит к увеличению счетчика AUTO INCRIMENT (справедливо только для InnoDB).

Что это значит? Это значит что ваша таблица через некоторое время будет выглядеть вот так (все внимание на столбец id ):

id	date	value
1	2015-08-05	109339
109340	2015-08-06	114171
223511	2015-08-07	154750

id	date	value
1	2015-08-05	109339
2	2015-08-06	114171
3	2015-08-07	154750

Переполнение первичного ключа

Представим на секунду что таких запросов в день у нас около 10 миллионов. Максимальное значение для INT UNSIGNED — 4 294 967 295 . Т.е. через 429 дней, значение AUTO INCRIMENT приблизится к пороговому, будет создана последняя запись с id 4 294 967 295 .

Далее счетчик AUTO INCRIMENT увеличиться не сможет и изменяться будет только последняя запись, т.к. любые попытки вставить будут вызывать ошибку duplicate key `id` , и соответственно вместо INSERT будет выполнятся UPDATE.

Как исправить

Решение этой проблемы — использовать эту конструкцию со знанием этого подводного камня, т.е. не стоит использовать ее где ожидается 10млн запросов в день 🙂

Второй вариант — отсрочить эту проблему — использовать BIGINT в качестве первичного ключа. Так например BIGINT UNSIGNED хватит на 18 446 744 073 709 551 615 запросов — 5 миллиардов лет при нашем количестве запросов в день (10 млн/день).

Неявная проблема ON DUPLICATE KEY и AUTO INCREMENT

Смотрите также

Правильное хранение IP адреса в БД. Преобразование адреса в unsigned intger 32 bit. Поиск по диапазону IP адресов. Получение маски подсети. Работа с IPv6.

Как ни странно, но многие разработчики до сих пор хранят ip адрес в базе плейн текстом, что вобщем то совсем.

Краткое описание добавления графика статистики запросов MySQL в Munin. Как добавить mysql в munin.

Кратенько о том как добавить MySQL в Munin.

Тестирование различных способов записи данных в таблицу. Сравнение конструкции insert on duplicate key с обычным подходом в три запроса. Производительность индексов MySQL.

Очень часто любому разработчику приходится сталкиваться с такой ситуацией: Проверить существует ли определенная запись в таблице. Если такой записи нет.

Реализация капчи на php. Алгоритм реализации не использует базу данных или файлы. Основан на генерации и проверки ключей. Быстрая и не сложная капча.

На данный момент все известные капчи делятся на категории Хранение некоего ключа в базе данных Хранение некоего ключа в сессии.

Здравствуйте. Хорошо что я прочитала эту статью, она мне очень помогла теперь я знаю что делать!

Хоть звучит это как сарказм, но все равно спасибо за комментарий

Настараживает вот эта строка -» date – уникальный ключ», как дата может быть уникальным ключем в данном случае, получается в определенную конкретную дату, может быть только одна строка записана, то есть Вы считаете сколько каждый день заходило пользователей и храните это в БД, зачем все это нужно хранить подневно, это никогда никому не пригодится, так как никакой пользы не несет. Вот если б с этого начинали писать код, то до этой ошибки не дошли б. Эта оплошность данной команды, для многих задач практически не значительна )

Ну хм. Это же всего лишь пример) Хотя вполне реальный пример скрипта счетчика посещений за день. Почему вы считаете что это бесполезная информация?

Вот кстати в подтверждение того что проблема реальна http://habrahabr.ru/post/156489/ случай из практики.

Допустим это некий скрипт счетчик посещений за день. Суть в том что любое выполнение этого запроса, вне зависимости от того был INSERT или UPDATE, приводит к увеличению счетчика AUTO INCRIMENT (справедливо только для InnoDB). Что это значит?

Источник

Несколько заметок о MySQL

За время работы с MySQL набралось некоторое количество нюансов и приемов, из которых я составил эту статью в виде набора заметок. Все это не секрет и, разумеется, можно найти в документации.

Буду использовать дефолтные настройки MySQL. Некоторые заметки связаны с PHP, поэтому для примеров буду использовать расширение mysqli.

Для запуска sql-запросов из статьи можно инициализировать таблицы так:

Проверить текущие автоинкременты можно так:

У обоих таблиц они равны 4. При этом имеется такое подключение к БД:

Вставка по уникальному ключу и автоинкремент

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

Автоинкремент стал 5, хотя вставки не было. А что будет с MyISAM:

Там автоинкремент остался 4. Аналогичная ситуация будет с ON DUPLICATE KEY UPDATE:

В user_innodb автоинкремент стал 6, а у user_myisam остался 4.

REPLACE работает иначе: в случае нахождения совпадений в уникальном ключе, он удалит старую запись и добавт новую.

Увеличит автоинкремент до 7, теперь у Петрова >
У MyISAM аналогично:

Автоинкремент стал 5, а Петров получил >
Итак, REPLACE работает на обоих движках таблиц одинаково, а INSERT IGNORE и ON DUPLICATE KEY UPDATE изменяют автоинкремент на InnoDB.

Получение id изменяемой записи после обновления

После вставки/обновления, с использованием INSERT… ON DUPLICATE KEY UPDATE, $mysqli->insert_id содержит id только если произошло добавление записи. Если нужно вытащить id изменяемой записи независимо от того была вставка или редактирование, можно сделать так:

Выведет 7 и 7, первый раз запись была добавлена под второй раз изменена.

С INSERT IGNORE такой трюк не выйдет. Код ниже выведет 9 и 0

Реализация SEQUENCE

У MySQL, в отличие от других СУРБД, нет такой штуки как SEQUENCE. Есть автоинкремент, но он не позвоялет решить все задачи, с которыми может помочь SEQUENCE. Например, шардинг.

В общем случае, если надо раскладывать записи по различным таблицам или даже базам, нужна будет мастер-таблица с автоинкрементным полем, в котором централизованно генерировался бы ID новой записи.

Решить эту задачу можно так:

В одной таблице получается сразу несколько последовательностей, в этом примере три.

Далее можно получать следующий ID из нужной последовательности с помощью функции
last_insert_id():

Для автоинкремента есть возможность указать шаг приращения опцией конфигурации auto_increment_increment. В этом примере такую функцию можно реализовать примерно так:

Немного о беззнаковых целых

С аккуратностью используйте беззнаковые целые как типы полей MySQL, если обращаетесь к БД из PHP.

История моего «озарения» по этому поводу. Для поля id всегда использовал беззнаковый целый тип, все равно классический id не бывает отрицательным. Однажды, генерируя модель с помощью Gii (скаффолдинг Yii), я обратил внимание, на то что правила валидации в модели для моих id и других беззнаковых целых полей генерируются как для строк. “WTF?” — подумал я и полез в код фреймворка, где обнаружил, что при разборе типов полей есть такой “хардкод” проверки на наличие unsigned:

Я посчитал это ошибкой, обрадовался, что сейчас у меня есть шанс внести свою лепту в исправление багов Yii. Но радость быстро сменилась мыслью “это ж-ж-ж-ж, неспроста”.

Действительно, в PHP нет беззнаковых целых, а в общем случае целые в PHP 32-х разрядные (под 32-bit Linux и под Windows). Если целочисленное значение превышает PHP_INT_MAX, то оно приводится к float, и тут самое место для возникновения магии со странными багами. Так что господин Qiang Xue все правильно сделал.

Источник

Поведение INSERT… ON DUPLICATE KEY UPDATE в крайней ситуации

Несколько недель назад, я работал над проблемой клиента, который столкнулся с падением производительности БД и даже ее отказами, которые происходили приблизительно каждые 4 недели. Ничего особенного в окружении, в железе или запросах. В сущности, большей частью базы данных была одна таблица, в которой присутствовали, кроме прочего, INT AUTO_INCREMENT PRIMARY KEY и UNIQUE KEY .

Запросы, работающие с этой таблицей, почти все были типа INSERT . ON DUPLICATE KEY UPDATE (далее — INSERT ODKU ), где столбцы, перечисленные в INSERT , соответствовали столбцам с UNIQUE KEY . И выполнялись они с частотой, приблизительно 1500-2000 запросов в секунду, непрерывно 24 часа в сутки. Если вы хороши в математике, то наверное, уже догадались в чем дело.

Для целей дискуссии мы будем использовать следующую таблицу, как иллюстрацию к ситуации:

Теперь представим следующую последовательность событий:

Ничего необычного, да? Мы вставили один ряд в пустую таблицу и если мы сделаем SHOW CREATE TABLE , то мы увидим что счётчик AUTO_INCREMENT сейчас имеет значение 2 . Если мы сделаем INSERT ODKU в эту таблицу, то увидим следующее:

И теперь, даже если мы не вставили новый ряд, наш счётчик AUTO_INCREMENT вырос до 3 . Это, вообще-то, ожидаемое поведение. InnoDB проверяет ограничения в том порядке, в котором они были определены, и PRIMARY KEY всегда идёт первым. Поэтому MySQL проверяет наш INSERT , видит, что следующее значение AUTO_INCREMENT доступно и использует его, но потом, проверяет UNIQUE KEY и находит нарушение, поэтому вместо INSERT делает UPDATE . Если мы посмотрим счётчики handler status , мы можем увидеть, что был один запрос на вставку, который завершился неудачей, и один запрос на обновление, который прошёл успешно (это объясняет, почему изменены 2 ряда, а не 1).

В этом месте вы можете подумать — «Ну и что?». Давайте вернёмся к нашему клиенту. 1500 INSERT ODKU в секунду, непрерывно 24 часа в сутки. PRIMARY KEY их таблицы такой же, как я использовал в демонстрационной таблице — INT UNSIGNED . Считаем. Максимальное значение для INT UNSIGNED – это 4294967295 . Делим это на 1500 запросов в секунду и делим на 86400, что является количеством секунд в сутках, и мы получаем 33.1 дней, или чуть больше чем 4 недели. Совпадение? Я так не думаю. Итак, что именно происходит, когда мы выходим за пределы значения? Некоторое поведение может вас удивить. Вернёмся к нашей демонстрационной таблице и вставим в нее ряд с максимальным значением для столбца с AUTO_INCREMENT , а потом вставим ещё один.

Итак, мы попытались вставить ряд и это не вышло, т.к. AUTO_INCREMENT уже имел максимальное значение и запрос не прошёл. Но, что случится если мы попробуем сделать INSERT ODKU ? Сначала, посмотрим что у нас в таблице:

Выглядит нормально, да? 2 ряда изменено, очевидно, что для ряда который соответствовал условию username = «foo» , были обновлены host_id и last_modified , и мы можем радоваться. К сожалению, это не так:

Опа, обновлён был последний ряд, у которого id равен максимальному значению нашего AUTO_INCREMENT , а UNIQUE KEY на столбце username был проигнорирован.

Теперь мы можем легко понять в чем проблема клиента, чья база данных послужила вдохновением для этого поста. 1500 запросов в секунду, пытающихся заблокировать и обновить один и тот же ряд, ни к чему хорошему не приведут. Конечно, есть простое решение — изменить тип данных AUTO_INCREMENT -столбца c INT на BIGINT .

Оказывается, такое поведение документировано. Мануал говорит, что наш INSERT ODKU на таблице с несколькими уникальными индексами, будет эквивалентен запросу UPDATE update_test SET host_id = 7, last_modified = NOW() WHERE OR username = «foo» LIMIT 1 и конечно оптимизатор скорее выберет PRIMARY , нежели вторичный UNIQUE .

Источник

MySQL: insert … on duplicate key update

Часто ли вы используете данную конструкцию?

Наверняка любой программист, кто мало-мальски связан с вебом (а под «вебом» я понимаю LAMP — LinuxApacheMySQLPHP), сталкивался c ситуацией, когда перед вставкой новой записи в БД нужно проверить, а вдруг запись с таким ключом уже есть? И если таковая уже имеется, то надо не вставлять новую, а обновлять старую.

Пример применения простой. У вас есть интернет-магазин и вы периодически синхронизируете свою локальную БД с сайтом. Если товар уже присутствует, надо обновить остаток, а если в БД сайта товара нет, то надо добавить запись.

Есть простая таблица goods в БД сайта:

Из листинга понятно, что ключевое поле здесь id. Обратите внимание, что AUTO_INCREMENT для поля id не установлен. Айдишники скорее всего будет генерировать программа, с которой вы синхронизируете БД сайта. На сайте же достаточно того, что поле id будет уникальным. Мы, собственно, так и указали: PRIMARY KEY (id).

Самое очевидное, что приходит в голову — это в скрипте синхронизации (который у вас скорее всего на PHP) проверить наличие элемента с таким id. Получается довольно громоздкая конструкция. Вот фрагмент кода:

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

Возникает закономерный вопрос: зачем мы так делаем, если MySQL уже давно умеет делать это за нас? Все требуемые нами действия можно выполнить одним запросом:

Конструкция insert … on duplicate key update работает именно таким образом, которым нам и нужно. MySQL попробует добавить запись, а если не получится — обновит. Т.е. вместо возвращения ошибки ERROR MySQL Duplicate entry будет выполнено обновление существующей записи.

Кто-нибудь обязательно подумает: а почему бы мне просто не использовать оператор REPLACE?

Действительно, в этом случае мы тоже обойдемся всего одним запросом к БД:

Но на этом сходство заканчивается. Более того, не будет выполнено наше главное условие: «обновить, если такая запись уже существует». Связано это с принципом работы оператора. Отличие в том, что insert … on duplicate key update пытается сначала добавить запись, а если не получается, то обновляет. REPLACE же сначала удалит существующую запись (если такая имеется) а потом вставит новую. В нашем примере, если запись уже существовала, мы потеряем данные. Временной штамп (поле ts) особой важности для нас, допустим, не представляет. А вот поле с количеством просмотров (views) обнулится. Что будет весьма грустно. Ведь у нас правильный магазин и мы собираем статистику о популярности товаров.

И, естественно, триггеры в этих случаях будут срабатывать разные. Впрочем, если вы активно используете триггеры, то этот материал вряд ли для вас).

Как-то так, если вкратце и понятным языком.

Источник