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

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

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

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

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 ;

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

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

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

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

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

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

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

  1. Mahabum

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

    Ответить ↓
    1. Дмитрий Амиров Автор

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

      Ответить ↓
  2. Слава

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

    Ответить ↓
    1. Дмитрий Амиров Автор

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

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

      Ответить ↓
  3. kivagant

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

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

    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 ;
-- Query OK, 0 rows affected (0.07 sec)

INSERT INTO `test_table` (`date`, `value`) VALUES ('2015-12-24', 1) ON DUPLICATE KEY UPDATE `value` = `value` + 1;
-- Query OK, 1 row affected (0.00 sec)
-- SELECT * FROM `test_table`\G
--    id: 1
--  date: 2015-12-24
-- value: 1
-- 1 row in set (0.00 sec)

INSERT INTO `test_table` (`date`, `value`) VALUES ('2015-12-24', 1) ON DUPLICATE KEY UPDATE `value` = `value` + 1;
-- Query OK, 2 rows affected (0.00 sec)

INSERT INTO `test_table` (`date`, `value`) VALUES ('2015-12-24', 1) ON DUPLICATE KEY UPDATE `value` = `value` + 1;
-- Query OK, 2 rows affected (0.00 sec)

INSERT INTO `test_table` (`date`, `value`) VALUES ('2015-12-24', 1) ON DUPLICATE KEY UPDATE `value` = `value` + 1;
-- Query OK, 2 rows affected (0.00 sec)

INSERT INTO `test_table` (`date`, `value`) VALUES ('2015-12-24', 1) ON DUPLICATE KEY UPDATE `value` = `value` + 1;
-- Query OK, 2 rows affected (0.00 sec)

SELECT * FROM `test_table`\G
--    id: 1
--  date: 2015-12-24
-- value: 5
-- 1 row in set (0.00 sec)

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

    Ответить ↓
    1. Дмитрий Амиров Автор

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

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

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

      INSERT INTO `test_table` (`date`, `value`) VALUES ('2015-12-25', 1) ON DUPLICATE KEY UPDATE `value` = `value` + 1;

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

      +----+------------+-------+
| id | date       | value |
+----+------------+-------+
|  1 | 2015-12-24 |     5 |
|  6 | 2015-12-25 |     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 | 2015-12-24 |     5 |
|  2 | 2015-12-25 |     8 |
| 10 | 2015-12-26 |     5 |
| 15 | 2015-12-27 |     3 |
| 18 | 2015-12-28 |     1 |
| 19 | 2015-12-29 |     2 |
| 21 | 2015-12-30 |     4 |
| 22 | 2015-12-31 |    10 |
        Ответить ↓
        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 для реализации счётчиков и статистики на высоких нагрузках может давать неожиданный результат и больше подходит для каких-то более тривиальных манипуляций состоянием сущностей в базе.

            Ответить ↓
            1. max

              Тогда делайте так:

              ALTER TABLE table_name AUTO_INCREMENT = 1;

              Если AUTO_INCREMENT <= MAX(`id`), то будет AUTO_INCREMENT = MAX(`id`) + 1

              Ответить ↓
    2. Serg

      Никто никого не обманывает, проблема на самом деле очень серьёзная существует.
      После 10 неудачных по UNIQUE постов в таблицу, действительно при запросе
      SELECT * FROM `test_table`\G
      будет id: 1
      НО! при следующем удачном посте id=11.
      В этом офигезная проблема!

      Ответить ↓
    1. Дмитрий Амиров Автор

      Тем что в случае с REPLACE

      старая запись перед занесением новой будет удалена

      По сути в таком случае вместо апдейта, выполнится два действия — delete и insert. Это более накладно.

      Ответить ↓
  5. Сергей

    Это объясняется особенностями построковй блокировки таблиц InnoDB и сделано для ускорения работы.

    Поведение становится немного лучше если в my.cnf добавить строку
    innodb_autoinc_lock_mode=0

    автоинкремент все равно не последовательный, но растет не так быстро.
    Кстати, такая же проблема если используются INSERT IGNORE
    если вставки игнорируются, то счетчик все равно прирастает

    Самое кардинальное, для таких таблиц использовать не InnoDB а MyISAM

    Ответить ↓

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

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

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