@truezemez29 окт 2012 в 06:17

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

5 мин

154K

MySQL *

Перевод

+64

Комментарии 38

@AlexeyVD 29 окт 2012 в 07:04

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

@datacompboy 29 окт 2012 в 08:37

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

@ComodoHacker 30 окт 2012 в 08:34

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

@Oduvanchik 29 окт 2012 в 08:40

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

@ncix 29 окт 2012 в 08:16

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

@romy4 29 окт 2012 в 08:33

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

@DMakeev 29 окт 2012 в 08:46

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

@sl4mmer 29 окт 2012 в 09:29

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

@Alter_Ego 29 окт 2012 в 09:37

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

@bat 29 окт 2012 в 09:42

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

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

@SamDark 29 окт 2012 в 10:52

И в 5.5+?

@bat 29 окт 2012 в 11:02

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

@DMakeev 29 окт 2012 в 19:04

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

@nikita2206 29 окт 2012 в 17:03

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

@DMakeev 29 окт 2012 в 18:56

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

@DMakeev 29 окт 2012 в 18:55

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

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

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

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

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

@maxshopen 29 окт 2012 в 12:30

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

@DMakeev 29 окт 2012 в 18: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 29 окт 2012 в 08:37

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

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

@romy4 29 окт 2012 в 16:59

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

@bat 29 окт 2012 в 09:32

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

@maxshopen 29 окт 2012 в 12:24

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

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

@el777 29 окт 2012 в 12: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 29 окт 2012 в 13: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 29 окт 2012 в 13:36

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

@NetBUG 29 окт 2012 в 15:04

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

@DMakeev 29 окт 2012 в 18:21

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

@romy4 29 окт 2012 в 17:13

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

@Mendel 29 окт 2012 в 19:01

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

@romy4 29 окт 2012 в 19:54

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

@Mendel 29 окт 2012 в 20:30

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

@romy4 29 окт 2012 в 20:42

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

@ComodoHacker 30 окт 2012 в 08:27

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

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

@Sergey-From-Irkutsk 26 апр 2017 в 00: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 26 апр 2017 в 03:50

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

@Sergey-From-Irkutsk 26 апр 2017 в 04: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 26 апр 2017 в 05:48

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

@Sergey-From-Irkutsk 26 апр 2017 в 06:19

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

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

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

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

Зарегистрируйтесь на Хабре, чтобы оставить комментарий