MySQL: Как проверить целостность внешних ключей в InnoDB

Для ускорения вставки большого объёма данных в InnoDB одна из рекомендаций — отключить проверку целостности ключей. Более того, это ещё позволит и решить проблему с порядком вставки этих данных: если ваша сущность имеет ссылку на какую-либо другую сущность, которая ещё не была создана, то вы имеете все шансы словить ошибку foerign-key constraint’ов, выглядит это примерно так:

Чтобы увеличить скорость вставки и избежать подобных проблем, вы можете выключить проверку целостности внешних ключей перед вставкой данных:

А после вставки включить обратно:

Проблема в том, что это не инициализирует перепроверку для уже существующих данных. Только для новых или при обновлении старых. Если у вас нет сомнений в исходных данных, то, можно сильно не беспокоиться по этому поводу, но если есть, то встаёт вопрос — как её реализовать. Можно написать свой велосипед и вручную проверять все внешние ключи, можно попробовать пересоздать их. Но это всё долго, особенно если под рукой есть уже готовое решение.

Можно воспользоваться готовой SQL-процедурой, которая была найдена мной на stackoverflow.com:

При вставке код напрямую в консоль mysql могут возникнуть трудности, поэтому рекомендую сохранить этот код в файл (наппример, check_consistency.sql) и запускать так:

Или так:

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

Немного пояснений по коду:

В начале создаётся SQL-процедура с именем ANALYZE_INVALID_FOREIGN_KEYS (внимание, если процедура с таким именем уже есть — она будет удалена). Потом она вызывается и удаляется.

Параметры процедуры ANALYZE_INVALID_FOREIGN_KEYS:

  • Паттерн имени БД (LIKE style)
  • Паттерн имени таблиц (LIKE style)
  • Где хранить результат проверки. Варианты: ‘Y’, ‘N’, NULL.

Y Хранить результат во временной таблице. Временная таблица не будем видима для других сессий. С этим параметром можно запустить сразу несколько проверок параллельно.

N Используется если вам надо получить результата из других сессий.

NULL Пропускает создание таблицы. Полезно если вы используете эту проверку внутри транзакции, т.к. при создании или удалении таблицы произойдёт коммит. В этом случае вам необходимо создать таблицу для результата вне блока транзакции:

В таблице INVALID_FOREIGN_KEYS будут содержаться имя битой БД, имя таблицы и колонки. Найти битые ряды можно выполнив SQL из колонки INVALID_KEY_SQL.

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: