Сейчас хочу вам предложить обсудить один интересный факт. Возьмем обычную таблицу:
1 2 3 4 5 6 7 |
CREATE TABLE `test_table` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `date` DATE NOT NULL , `value` INT UNSIGNED NOT NULL , PRIMARY KEY ( `id` ) , UNIQUE ( `date` ) ) ENGINE = INNODB ; |
В ней три поля:
- id — первичный ключ с автоинкриментом
- date — уникальный ключ
- value — некие данные
Не так давно я писал насколько круто использовать конструкцию ON DUPLICATE KEY. Но как оказалось, данная конструкция имеет один подводный камень.
Описание проблемы
Представим себе скрипт который, выполняет следующий запрос:
1 2 3 4 5 6 |
INSERT INTO `test_table` (`date`, `value`) VALUES ('[some_date]', 1) ON DUPLICATE KEY UPDATE `value` = `value` + 1 |
Допустим это некий скрипт счетчик посещений за день.
Суть в том что любое выполнение этого запроса, вне зависимости от того был 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 млн/день).
Здравствуйте. Хорошо что я прочитала эту статью, она мне очень помогла теперь я знаю что делать!
Хоть звучит это как сарказм, но все равно спасибо за комментарий
Настараживает вот эта строка -» date – уникальный ключ», как дата может быть уникальным ключем в данном случае, получается в определенную конкретную дату, может быть только одна строка записана, то есть Вы считаете сколько каждый день заходило пользователей и храните это в БД, зачем все это нужно хранить подневно, это никогда никому не пригодится, так как никакой пользы не несет. Вот если б с этого начинали писать код, то до этой ошибки не дошли б. Эта оплошность данной команды, для многих задач практически не значительна )
Ну хм. Это же всего лишь пример) Хотя вполне реальный пример скрипта счетчика посещений за день. Почему вы считаете что это бесполезная информация?
Вот кстати в подтверждение того что проблема реальна http://habrahabr.ru/post/156489/ случай из практики.
Зачем людей обманываете?
Тестируйте свои примеры прежде чем всякую чепуху публиковать.
Статью внимательнее читайте, прежде чем всякую чепуху в комментариях писать.
После ваших запросов, делаем (допустим наступил следующий день):
И смотрим в таблицу:
Проблема, описанная в статье, видна наглядно. А именно — ID второй записи равен шести, а не двум, как это должно было бы быть, если рассуждать логически.
Не поленюсь, я всегда признаю если не прав и тем более рад узнать что-то новое. Приношу свои извинения.
Так вот, во-первых да, автоинкремент приростает для следующей записи.
Но вот вам ещё сюрприз, о котором вы, возможно, тоже не знали:
1.
INSERT INTO `test_table` (`date`, `value`) VALUES (‘2015-12-31’, 1) ON DUPLICATE KEY UPDATE `value` = `value` + 1;
(несколько одинаковых вставок подряд)
2.
show create table test_table;
… ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8
3. service mysql restart
4.
show create table test_table;
… ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8
Автоинкремент пересчитался до значения последней строки плюс одна запись:
select * from test_table
Хм, честно сказать, никогда не задумывался об этом.
В принципе в этом нет ничего удивительного, насколько я помню, в InnoDB счетчик AUTO_INCREMENT хранится в памяти, а не где то физически, и логично что после перезапуска сервера он инициализируется заново.
PS: я немного вспылил, сорри
UPD: да, хранится в памяти, и инициализируется после перезапуска см. тут:
Конечно, рекомендовать перезапускать сервис после каждого insert было бы смешно.
А в целом да, получается что ON DUPLICATE KEY UPDATE для реализации счётчиков и статистики на высоких нагрузках может давать неожиданный результат и больше подходит для каких-то более тривиальных манипуляций состоянием сущностей в базе.
Никто никого не обманывает, проблема на самом деле очень серьёзная существует.
После 10 неудачных по UNIQUE постов в таблицу, действительно при запросе
SELECT * FROM `test_table`\G
будет id: 1
НО! при следующем удачном посте id=11.
В этом офигезная проблема!
Я конечно может не по теме.
А что мешает делать REPLACE
Тем что в случае с REPLACE
По сути в таком случае вместо апдейта, выполнится два действия — delete и insert. Это более накладно.