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

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

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

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 ;

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. Но как оказалось, данная конструкция имеет один подводный камень.

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

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

INSERT INTO 
    `test_table` (`date`, `value`) 
VALUES 
    ('[some_date]', 1)
ON DUPLICATE KEY UPDATE 
    `value` = `value` + 1

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

Mahabum 18.08.2015 в 23:56

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

Ответить ↓
1. Дмитрий Амиров Автор20.08.2015 в 12:42
  
  Хоть звучит это как сарказм, но все равно спасибо за комментарий
  
  Ответить ↓
Слава 03.11.2015 в 23:02

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

Ответить ↓
1. Дмитрий Амиров Автор03.11.2015 в 23:28
  
  Ну хм. Это же всего лишь пример) Хотя вполне реальный пример скрипта счетчика посещений за день. Почему вы считаете что это бесполезная информация?
  
  Вот кстати в подтверждение того что проблема реальна http://habrahabr.ru/post/156489/ случай из практики.
  
  Ответить ↓

kivagant 24.12.2015 в 20:35

Допустим это некий скрипт счетчик посещений за день. Суть в том что любое выполнение этого запроса, вне зависимости от того был 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)

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)

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

Ответить ↓

Дмитрий Амиров Автор25.12.2015 в 08:30

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

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

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

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

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 | date | value |

+----+------------+-------+

| 1 | 2015-12-24 | 5 |

| 6 | 2015-12-25 | 1 |

+----+------------+-------+

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

Ответить ↓

kivagant 25.12.2015 в 11:13

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

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

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

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

Ответить ↓

Дмитрий Амиров Автор25.12.2015 в 11:45

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

В принципе в этом нет ничего удивительного, насколько я помню, в 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 25.12.2015 в 14:13
  
  Конечно, рекомендовать перезапускать сервис после каждого insert было бы смешно.
  
  А в целом да, получается что ON DUPLICATE KEY UPDATE для реализации счётчиков и статистики на высоких нагрузках может давать неожиданный результат и больше подходит для каких-то более тривиальных манипуляций состоянием сущностей в базе.
  
  Ответить ↓

Serg 31.03.2018 в 14:21

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

Ответить ↓

Иван 18.12.2016 в 10:46

Я конечно может не по теме.
А что мешает делать REPLACE

Ответить ↓
1. Дмитрий Амиров Автор18.12.2016 в 20:16
  
  Тем что в случае с REPLACE
  
  старая запись перед занесением новой будет удалена
  
  По сути в таком случае вместо апдейта, выполнится два действия — delete и insert. Это более накладно.
  
  Ответить ↓

IntSystem.org

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

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

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

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

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

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

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

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

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

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

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