MySQL Як перевірити цілісність зовнішніх ключів у InnoDB

Для прискорення вставки великого обсягу даних в InnoDB одна з рекомендацій – відключити перевірку цілісності ключів. Більше того, це ще дозволить і вирішити проблему з порядком вставки цих даних: якщо ваша сутність має посилання на будь-яку іншу сутність, яка ще не була створена, то ви маєте всі шанси впіймати помилку foerign-key constraint'ів, виглядає це приблизно так:

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

А після вставки увімкнути назад:

Проблема в тому, що це не ініціалізує повторну перевірку для вже існуючих даних. Тільки для нових або при оновленні старих. Якщо у вас немає сумнівів у вихідних даних, то можна сильно не турбуватися з цього приводу, але якщо є, то постає питання — як її реалізувати. Можна написати свій велосипед та вручну перевіряти всі зовнішні ключі, можна спробувати перестворити їх. Але це все довго, якщо під рукою є вже готове рішення.

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

При вставці код безпосередньо в консоль mysql можуть виникнути труднощі, тому рекомендую зберегти цей код у файл (наприклад, check_consistency.sql) і запускати так:

Небагато пояснень за кодом:

Спочатку створюється SQL-процедура з ім'ям ANALYZE_INVALID_FOREIGN_KEYS (увага, якщо процедура з таким ім'ям вже є - вона буде видалена). Потім вона викликається та видаляється.

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

  • Паттерн імені БД (LIKE style)
  • Паттерн імені таблиць (LIKE style)
  • Де зберігати результати перевірки. Варіанти: Y, N, NULL.

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

N Використовується, якщо вам потрібно отримати результат з інших сесій.

NULL Пропускає створення таблиці. Корисно, якщо ви використовуєте цю перевірку всередині транзакції, т.к. під час створення або видалення таблиці відбудеться коміт. У цьому випадку вам необхідно створити таблицю для результату поза блоком транзакції: