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

9 комментариев

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

В ней три поля:

  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 млн/день).

  1. Mahabum

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

    1. Дмитрий Амиров Автор

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

  2. Слава

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

    1. Дмитрий Амиров Автор

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

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

  3. kivagant

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

    Зачем людей обманываете?

    Тестируйте свои примеры прежде чем всякую чепуху публиковать.

    1. Дмитрий Амиров Автор

      Тестируйте свои примеры прежде чем всякую чепуху публиковать.

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

      После ваших запросов, делаем (допустим наступил следующий день):

      И смотрим в таблицу:

      Проблема, описанная в статье, видна наглядно. А именно — ID второй записи равен шести, а не двум, как это должно было бы быть, если рассуждать логически.

      1. kivagant

        Надеюсь вы не поленитесь зайти и принести свои извинения

        Не поленюсь, я всегда признаю если не прав и тем более рад узнать что-то новое. Приношу свои извинения.

        Так вот, во-первых да, автоинкремент приростает для следующей записи.

        Но вот вам ещё сюрприз, о котором вы, возможно, тоже не знали:
        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

        1. Дмитрий Амиров Автор

          Хм, честно сказать, никогда не задумывался об этом.

          В принципе в этом нет ничего удивительного, насколько я помню, в InnoDB счетчик AUTO_INCREMENT хранится в памяти, а не где то физически, и логично что после перезапуска сервера он инициализируется заново.

          PS: я немного вспылил, сорри

          UPD: да, хранится в памяти, и инициализируется после перезапуска см. тут:

          This counter is stored only in main memory, not on disk.

          To initialize an auto-increment counter after a server restart, InnoDB executes the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column.

          SELECT MAX(ai_col) FROM table_name FOR UPDATE;

          1. kivagant

            Конечно, рекомендовать перезапускать сервис после каждого insert было бы смешно.

            А в целом да, получается что ON DUPLICATE KEY UPDATE для реализации счётчиков и статистики на высоких нагрузках может давать неожиданный результат и больше подходит для каких-то более тривиальных манипуляций состоянием сущностей в базе.

Добавить комментарий

Прочли запись? Понравилась? Не стесняйтесь, оставьте, пожалуйста, свой комментарий. Мне очень интересно, что вы думаете об этом. Кстати в комментарии вы можете задать мне любой вопрос. Я обязательно отвечу.

Вы можете оставить коментарий анонимно, для этого можно не указывать Имя и email. Все комментарии проходят модерацию, поэтому ваш комментарий появится не сразу.