Главная > Администрирование, общие вопросы, Программирование > MySQL: Как проверить целостность внешних ключей в InnoDB.

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

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

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`foreign_key_0022`.`table`, CONSTRAINT `item_id` FOREIGN KEY (`item_id`) REFERENCES `child_table` (`item_id`))

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

SET FOREIGN_KEY_CHECKS=0;

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

SET FOREIGN_KEY_CHECKS=1;

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

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

DELIMITER $$

DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS$$

CREATE
PROCEDURE `ANALYZE_INVALID_FOREIGN_KEYS`(
checked_database_name VARCHAR(64),
checked_table_name VARCHAR(64),
temporary_result_table ENUM('Y', 'N'))

LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA

BEGIN
DECLARE TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE TABLE_NAME_VAR VARCHAR(64);
DECLARE COLUMN_NAME_VAR VARCHAR(64);
DECLARE CONSTRAINT_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_COLUMN_NAME_VAR VARCHAR(64);
DECLARE KEYS_SQL_VAR VARCHAR(1024);

DECLARE done INT DEFAULT 0;

DECLARE foreign_key_cursor CURSOR FOR
SELECT
`TABLE_SCHEMA`,
`TABLE_NAME`,
`COLUMN_NAME`,
`CONSTRAINT_NAME`,
`REFERENCED_TABLE_SCHEMA`,
`REFERENCED_TABLE_NAME`,
`REFERENCED_COLUMN_NAME`
FROM
information_schema.KEY_COLUMN_USAGE
WHERE
`CONSTRAINT_SCHEMA` LIKE checked_database_name AND
`TABLE_NAME` LIKE checked_table_name AND
`REFERENCED_TABLE_SCHEMA` IS NOT NULL;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

IF temporary_result_table = 'N' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;

CREATE TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024)
);
ELSEIF temporary_result_table = 'Y' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;

CREATE TEMPORARY TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024)
);
END IF;

OPEN foreign_key_cursor;
foreign_key_cursor_loop: LOOP
FETCH foreign_key_cursor INTO
TABLE_SCHEMA_VAR,
TABLE_NAME_VAR,
COLUMN_NAME_VAR,
CONSTRAINT_NAME_VAR,
REFERENCED_TABLE_SCHEMA_VAR,
REFERENCED_TABLE_NAME_VAR,
REFERENCED_COLUMN_NAME_VAR;
IF done THEN
LEAVE foreign_key_cursor_loop;
END IF;

SET @from_part = CONCAT('FROM ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', ' AS REFERRING ',
'LEFT JOIN `', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', ' AS REFERRED ',
'ON (REFERRING', '.`', COLUMN_NAME_VAR, '`', ' = ', 'REFERRED', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ') ',
'WHERE REFERRING', '.`', COLUMN_NAME_VAR, '`', ' IS NOT NULL ',
'AND REFERRED', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ' IS NULL');
SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' INTO @invalid_key_count;');
PREPARE stmt FROM @full_query;

EXECUTE stmt;
IF @invalid_key_count > 0 THEN
INSERT INTO
INVALID_FOREIGN_KEYS
SET
`TABLE_SCHEMA` = TABLE_SCHEMA_VAR,
`TABLE_NAME` = TABLE_NAME_VAR,
`COLUMN_NAME` = COLUMN_NAME_VAR,
`CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR,
`REFERENCED_TABLE_SCHEMA` = REFERENCED_TABLE_SCHEMA_VAR,
`REFERENCED_TABLE_NAME` = REFERENCED_TABLE_NAME_VAR,
`REFERENCED_COLUMN_NAME` = REFERENCED_COLUMN_NAME_VAR,
`INVALID_KEY_COUNT` = @invalid_key_count,
`INVALID_KEY_SQL` = CONCAT('SELECT ',
'REFERRING.', '`', COLUMN_NAME_VAR, '` ', 'AS "Invalid: ', COLUMN_NAME_VAR, '", ',
'REFERRING.* ',
@from_part, ';');
END IF;
DEALLOCATE PREPARE stmt;

END LOOP foreign_key_cursor_loop;
END$$

DELIMITER ;

CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y');
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS;

SELECT * FROM INVALID_FOREIGN_KEYS;

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

$ mysql -u <username> -v <db_name> < check_consistency.sql

Или так:

$ mysql -u <username> -v <db_name>
mysql> source check_consistency.sql
...

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

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

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

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

  1. Паттерн имени БД (LIKE style)
  2. Паттерн имени таблиц (LIKE style)
  3. Где хранить результат проверки. Варианты: 'Y', 'N', NULL.
    Y
    Хранить результат во временной таблице. Временная таблица не будем видима для других сессий. С этим параметром можно запустить сразу несколько проверок параллельно.
    N
    Используется если вам надо получить результата из других сессий.
    NULL
    Пропускает создание таблицы. Полезно если вы используете эту проверку внутри транзакции, т.к. при создании или удалении таблицы произойдёт коммит. В этом случае вам необходимо создать таблицу для результата вне блока транзакции:
    CREATE TABLE INVALID_FOREIGN_KEYS(
    `TABLE_SCHEMA` VARCHAR(64),
    `TABLE_NAME` VARCHAR(64),
    `COLUMN_NAME` VARCHAR(64),
    `CONSTRAINT_NAME` VARCHAR(64),
    `REFERENCED_TABLE_SCHEMA` VARCHAR(64),
    `REFERENCED_TABLE_NAME` VARCHAR(64),
    `REFERENCED_COLUMN_NAME` VARCHAR(64),
    `INVALID_KEY_COUNT` INT,
    `INVALID_KEY_SQL` VARCHAR(1024)
    );

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

Пожалуйста, оцените полезность и качество данной статьи. Одна звезда - плохо, 5 - хорошо.
1/5. Мы будем признательны, если вы напишете комментарий с причиной низкой оценки.2/5. Мы будем признательны, если вы напишете комментарий с причиной низкой оценки.3/5. Мы будем признательны, если вы напишете комментарий с причиной низкой оценки.4/5.5/5. (1 голосов, средний: 5,00 из 5)
Загрузка...
  1. Пока что нет комментариев.
  1. Пока что нет уведомлений.