truezemez Oct 29 2012 at 10:17

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

5 min

148K

MySQL*

Translation

+64

Comments 38

AlexeyVD Oct 29 2012 at 11:04

А если бы не было в этой таблице автоинкрементного поля, то проблема бы и не возникла. В целом пример показательный.

datacompboy Oct 29 2012 at 12:37

Действительно, зачем тут autoincrement?

ComodoHacker Oct 30 2012 at 12:34

Наверное разработчик в свое время прочитал, что «к любой таблице нужно обязательно навесить суррогатный PK с автоинкрементом».

Oduvanchik Oct 29 2012 at 12:40

Ага, можно было бы вынести данные, которые обновляются в таблицу без автоинкримента.

ncix Oct 29 2012 at 12:16

В других СУБД есть конструкции вроде UPDATE OR INSERT INTO… или MERGE а вместо AUTOINCREMENT юзать SEQUENCE.

romy4 Oct 29 2012 at 12:33

Могу вас поздравить с полезным опытом. BIGINT нормальный тип данных и следовало его учесть в архитектуре.
А вобще вставка овер 1000 запросов в секунду плохая идея. Хорошая это накапливать 2 секунды данные и делать вставку одним запросом предварительно отключив ключи. И не забыть их включить потом. Это я вам из опыта говорю.

DMakeev Oct 29 2012 at 12:46

Операция Удалить индексы / Вставить 3k записей / Создать индексы при табличке на 500k записей может привести к весьма неоднозначным последствиям. Например, если операция не уложится в 2 секунды и на нее наложится следующая. Хотя подход, конечно же, правильный. Вопрос лишь в требованиям к актуальности данных — может быть там была жесткая необходимость иметь в любой момент актуальные данные.

sl4mmer Oct 29 2012 at 13:29

имеется таблица в 10 миллиардов записей, с обычными и полнотекстовыми индексами — если не отключать ключи перед вставкой, то можете себе представить время вставки пары тысяч новых строк.

Alter_Ego Oct 29 2012 at 13:37

Про 10 миллиардов не скажу, но на таблице в 50 млн. записей с тремя обычными индексами без отключения индексов запрос на вставку 1000 записей отрабатывает примерно за 200 мс.

bat Oct 29 2012 at 13:42

с обычными и полнотекстовыми индексами

значит MyISAM
В InnoDb ALTER TABLE приводит к полному пересозданию таблицы.

SamDark Oct 29 2012 at 14:52

И в 5.5+?

bat Oct 29 2012 at 15:02

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

DMakeev Oct 29 2012 at 23:04

В 5.5+ создание и удаление индекса в InnoDB не приводит к пересозданию таблицы.
В 5.1, кстати, тоже, но только при использовании InnoDB Plugin.

nikita2206 Oct 29 2012 at 21:03

Мне одному тут кажется, что убрать что-то? Из:
* RDBMS
* 10 миллиардов записей
* полнотекстовые индексы

DMakeev Oct 29 2012 at 22:56

* того кто все это проектировал

DMakeev Oct 29 2012 at 22:55

Что-то мне подсказывает что у Вас интенсивность запросов чуть поменьше чем 1500/сек.

Исходя из опыта, наиболее эффективным в таком случае является следующий подход (мало ли, вдруг кому интересно будет):
1. Сделать идентичную табличку, но без индексов вообще. Назовем ее tempA.
2. В течение разумного интервала времени заполняем эту табличку данными.
3. Переименовываем табличку tempA в tempB, создавая попутно пустую tempA
4. Создаем все необходимые индексы в tempB (на уже имеющихся данных)
5. Переименовываем tempB в рабочую таблицу.

Плюсы:
1. выборки идут из рабочей таблицы, в которую ничего не вставляется.
2. вставки идут в таблицу без индексов, причем не нужно создавать никаких буферов на время пересоздания индексов
3. переименование таблицы происходит практически мгновенно — переименование файлов, поэтому ни поток записи ни поток чтения практически не прерывается.
4. нагрузка на сервер становится более предсказуемой, меньше вероятность что сервер упадет при ее увеличении.

На что нужно обратить внимание: время накопления данных в tempA было ощутимо больше, чем время создания индекса в tempB. Этот момент лучше отслеживать по существованию таблицы tempB, если она существует то tempA в tempB не переименовывается и данные продолжают накапливаться в tempA.

Хотя это, конечно же, не панацея.

maxshopen Oct 29 2012 at 16:30

Зачем их удалять? ALTER TABLE DISABLE KEYS имелось ввиду, я так понимаю

DMakeev Oct 29 2012 at 22:59

Увы, только удалять. DISABLE KEYS не работает с InnoDB и не распространяется на уникальные индексы:
«This feature can be activated explicitly for a MyISAM table. ALTER TABLE… DISABLE KEYS tells MySQL to stop updating nonunique indexes»

DMakeev Oct 29 2012 at 12:37

Отличный материал, спасибо.

Сдается мне что разработчик просто не знал что INSERT ODKU увеличивает счетчик AUTO_INCREMENT при апдейте существующей записи.

romy4 Oct 29 2012 at 20:59

только для InnoDB, myisam поступает логически правильно.

bat Oct 29 2012 at 13:32

Складывается впечатление, что ON DUPLICATE KEY UPDATE тот еще костыль.
Не в курсе как это сейчас, но года 1.5-2 назад имели проблему с этой конструкцией при обработке запросов в параллельных транзакциях, т.е. не в режиме autocommit. Проблема проявлялась при одновременном выполнении запросов с одинаковым значением уникального поля в двух разных транзакциях. Подробностей уже не помню, а по сути получалось что одна транзакция изменяла незафиксированные данные другой.

maxshopen Oct 29 2012 at 16:24

И теперь, даже если мы не вставили новый ряд, наш счётчик AUTO_INCREMENT вырос до 3. Это, вообще-то, ожидаемое поведение.

Интересно, почему такое поведение считается ожидаемым. Т.е. объяснение из статьи понятно, но это скорее объяснение некоего неожиданного эффекта, а не наоборот

el777 Oct 29 2012 at 16:50

> Использование SIGNED типов для AUTO_INCREMENT — почти всегда плохая идея. Вы теряете половину диапазона доступных значений.

Разве?
Вот тут написано по-другому:

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

Похоже в исходной статье опечатка, должно быть UNSIGNED.

И даже вот такая ненавязчивая рекомендация использовать правильный тип для первичного ключа:

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

truezemez Oct 29 2012 at 17:04

Может Вам так нагляднее будет:

18446744073709551615 - unsigned
 9223372036854775807 - signed

В положительном диапазоне unsigned в 2 раза больше чем signed. The behavior of the auto-increment mechanism is not defined if a user assigns a negative value to the column (мануал) — следовательно, мы теряем половину диапазона.

el777 Oct 29 2012 at 17:36

Точно, перепутал названия Signed и Unsigned :)

NetBUG Oct 29 2012 at 19:04

Рассуждение правильное, но если половина диапазона начинает играть роль, возможно, размерность типа выбрана неправильно. Либо вообще механизм работы с базой.

DMakeev Oct 29 2012 at 22:21

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

romy4 Oct 29 2012 at 21:13

На будущее ещё один совет: всегда указывайте AUTO_INCREMENT=1 для создаваемых таблиц, если он там используется. На некоторых серверах я получил ошибку Failed to read auto-increment value from storage engine. К проблеме это не относится, но может испортить настроение.

Mendel Oct 29 2012 at 23:01

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

romy4 Oct 29 2012 at 23:54

Ваше «всегда нельзя» означает именно «никогда не использовать»?

Mendel Oct 30 2012 at 00:30

)))
Да, немного не однозначно вышло.
Просто у Вас было «всегда указывайте», я же хотел сказать, что не всегда, а обдуманно.

romy4 Oct 30 2012 at 00:42

В зависимости от системы. Я не упоминал именно mysql подразумевая, что лишь о нём речь. Но вы правы, конечно.

ComodoHacker Oct 30 2012 at 12:27

>В сущности, большей частью базы данных была одна таблица
>Запросы, работающие с этой таблицей, почти все были типа INSERT… ON DUPLICATE KEY UPDATE

То есть база, по сути, представляет из себя один большой map. NOSQL спешит на помошь!

Sergey-From-Irkutsk Apr 26 2017 at 03:13

в 2017 году юзаю сервер MySQL 5.6.25 и в нём фича с приращением автоинкремента при INSERT ODKU видимо отсутствует. Пробовал и REPLACE, такое же поведение. Если обновляются существующие строки где ключи UNIQUE остаются прежними, то автоинкремент не увеличивается, а вот если вместе с тем вставляется новая строка, тогда автоинктремент увеличивает свое значение.

INSERT INTO table (field_1, field_2) VALUES (1, foo), (2, bar), (3, baz) ON DUPLICATE KEY UPDATE field_2 = VALUES (field_2);

REPLACE INTO table (field_1, field_2) VALUES (1, foo), (2, bar), (3, baz);

truezemez Apr 26 2017 at 06:50

Вряд ли. Покажите схему.

Sergey-From-Irkutsk Apr 26 2017 at 07:11

создаю таблицу

--
-- База данных: `test_db`
--

-- --------------------------------------------------------

--
-- Структура таблицы `table_test`
--

CREATE TABLE `table_test` (
  `field_1` int(10) UNSIGNED NOT NULL COMMENT 'Уникальный идентификатор',
  `field_2` varchar(255) NOT NULL COMMENT 'Какое-то значение'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Тестовая таблица';

--
-- Дамп данных таблицы `table_test`
--

INSERT INTO `table_test` (`field_1`, `field_2`) VALUES
(1, 'foo'),
(2, 'bar'),
(3, 'baz');

--
-- Индексы таблицы `table_test`
--
ALTER TABLE `table_test`
  ADD PRIMARY KEY (`field_1`);

--
-- AUTO_INCREMENT для таблицы `table_test`
--
ALTER TABLE `table_test`
  MODIFY `field_1` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Уникальный идентификатор', AUTO_INCREMENT=4;

теперь делаю

INSERT INTO table_test (field_1, field_2) VALUES (1, 'foo2'), (2, 'bar2'), (3, 'baz2') ON DUPLICATE KEY UPDATE field_2 = VALUES (field_2)

в итоге значение автоинкремента не меняется

--
-- База данных: `test_db`
--

-- --------------------------------------------------------

--
-- Структура таблицы `table_test`
--

CREATE TABLE `table_test` (
  `field_1` int(10) UNSIGNED NOT NULL COMMENT 'Уникальный идентификатор',
  `field_2` varchar(255) NOT NULL COMMENT 'Какое-то значение'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Тестовая таблица';

--
-- Дамп данных таблицы `table_test`
--

INSERT INTO `table_test` (`field_1`, `field_2`) VALUES
(1, 'foo2'),
(2, 'bar2'),
(3, 'baz2');

--
-- Индексы таблицы `table_test`
--
ALTER TABLE `table_test`
  ADD PRIMARY KEY (`field_1`);

--
-- AUTO_INCREMENT для таблицы `table_test`
--
ALTER TABLE `table_test`
  MODIFY `field_1` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Уникальный идентификатор', AUTO_INCREMENT=4;

Если я не правильно понял на счет схемы, то подскажите как её посмотреть

truezemez Apr 26 2017 at 08:48

Статья описывает такое поведение.
Т.е. может возникнуть ситуация, когда вставка не работает потому, что автоинкремент дошел до максимума, хотя в таблице всего пара записей.

Sergey-From-Irkutsk Apr 26 2017 at 09:19

Я понял в чем у вас ситуация. Запросом упоминаются не все UNIQUE поля, в частности не упоминается поле id, которое автоинктрементное и уникальное.

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

Как бы там ни было, мне ваша статья позволила более детально понять происходящее, а нашел я её когда потребовалось делать UPDATE множества строк, не в цикле, а одним запросом.

Получается отлично. Спасибо вам за статью.