microcell (microcell) wrote,
microcell
microcell

Category:

mysql: InnoDb в сочетании с INSERT...IGNORE

Таблицы InnoDB после версии MySQL 5.1.22 приобрели одну неприятную особенность (кстати, нигде не документированную sic!)

Традиционно, если у вас есть в таблице поле с флагом auto_increment, оно будет увеличиваться на единицу при каждой новой строчки. Эта вещь весьма подходит для создания разнообразных индексов, и я, к примеру, использую его весьма широко. До версии 5.1.22, при записи новой строки в таблицу происходила, так называемая, AUTO-INC блокировка всей таблицы. Она гарантировала, к примеру, что если вы будете производить запись из двух параллельных потоков, вы не сможете присвоить двум строчкам одинаковые индексы.

На мой взгляд, ничего преступного в этой блокировке не существовала, но разработчики InnoDb, очевидно, считали иначе. Теперь, при записи новой строки в таблицу, содержащую поле auto_increment, AUTO-INC блокировка активируется только на время, необходимое для генерации нового индекса. Теоретически, вы можете теперь записывать несколько строчек одновременно, что особенно становится полезно в ситуации конструкций вида INSERT ... SELECT, но на практики, если вы используете конструкции вида INSERT IGNORE или INSERT ... ON DUPLICATE KEY UPDATE, у вас могут появляться промежутки в индексах. Это происходит потому, что новый индекс будет сгенерирован перед записью строки в таблицу. Если запись строки не состоялась, индекс будет попросту проигнорирован. Размер же auto_increment поля, конечно, может быть довольно большим, но всё же он конечен, и когда вы выйдете за пределы unsigned long long int, у вас начнутся проблемы.

Что же делать в сложившийся ситуации? Ну у вас есть несколько вариантов:

1) Не использовать поле auto_increment, используя вместо него свою собственную таблицу. Долго и муторно, но зато - надёжно.

2) Отказаться от использования конструкций INSERT IGNORE или/или INSERT ... ON DUPLICATE KEY UPDATE. Всегда можно проверить наличие строки в базе перед её записью. Функция поиска по ключу займёт гораздо меньше времени, чем неудачная попытка записи строки. В случае же, если необходимо обновление существующей информации, можно получить PRIMARY ID строки. Если его нет - смело создавайте новую строку, если оно есть - обновляйте существующую. К слову, в 90% случаев я теперь использую именно этот способ.

3) Можно вернуть старую AUTO-INC блокировку. Если у вас всего один поток работает с базой данных, то вы не должны заметить какой-либо особой разницы. Для этого выполните команду: SET innodb_autoinc_lock_mode 0. По умолчанию оно, естественно, равно 1.

4) Существуют ещё более экзотические способы, связанные с использованием LEFT OUTER JOIN, которые на время вызывают старую блокировку. Эти методы описаны, например, здесь. Поскольку я сам к подобным трюкам не прибегаю, то не стану описывать их подробно.
Tags: innodb, mysql, пособие по вкручиванию лампочек
Subscribe
  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 0 comments