Обновление первичного ключа главной и дочерней таблиц для больших таблиц
У меня довольно большая база данных с главной таблицей с одним столбцом GUID (настраиваемый GUID, подобный алгоритму) в качестве первичного ключа и 8 дочерних таблиц, которые имеют отношения внешнего ключа с этим столбцом GUID. Во всех таблицах примерно 3-8 миллионов записей. Ни в одной из этих таблиц нет BLOB / CLOB / TEXT или каких-либо других причудливых типов данных, только обычных чисел, переменных, дат и временных меток (около 15-45 столбцов в каждой таблице). Никаких разделов или других индексов, кроме первичного и внешнего ключей.
Теперь настраиваемый алгоритм GUID изменился, и, хотя коллизий нет, я хотел бы перенести все старые данные на использование GUID, сгенерированных с использованием нового алгоритма. Никакие другие столбцы менять не нужно. Приоритет номер один - целостность данных, а производительность - вторична.
Некоторые из возможных решений, которые я мог придумать, были (как вы, вероятно, заметили, все они вращаются только вокруг одной идеи)
- добавить новый столбец ngu_id и заполнить новым gu_id; отключить ограничения; обновить дочерние таблицы с ngu_id как gu_id; переименовать ngu_id-> gu_id; повторно включить ограничения
- читать одну основную запись и зависимые от нее дочерние записи из дочерних таблиц; вставить в ту же таблицу с новым gu_id; удалить все записи со старыми gu_ids
- ограничения сброса; добавить триггер в главную таблицу, чтобы обновились все дочерние таблицы; начать обновлять старые gu_id новыми новыми gu_ids; повторно включить ограничения
- добавить триггер в главную таблицу, чтобы обновились все дочерние таблицы; начать обновлять старые gu_id новыми новыми gu_ids
- создать новый столбец ngu_ids во всех основных и дочерних таблицах; создать ограничения внешнего ключа для столбцов ngu_id; добавить триггер обновления в главную таблицу для каскадирования значений в дочерние таблицы; вставить новые значения gu_id в столбец ngu_id; удалить старые ограничения внешнего ключа на основе gu_id; удалите столбец gu_id и переименуйте ngu_id в gu_id; при необходимости воссоздать ограничения;
- использовать
on update cascade, если есть?
Мои вопросы:
- Есть ли способ лучше? (Не могу зарыться в песок, надо это сделать)
- Как лучше всего это сделать? (Я должен сделать это в Oracle, SQL server и mysql4, поэтому хаки, специфичные для поставщика, приветствуются)
- Каковы типичные точки отказа для такого упражнения и как их минимизировать?
Если вы до сих пор со мной, спасибо и надеюсь, что вы можете помочь :)
Ответы 4
Ваши идеи должны работать. первый, вероятно, так я бы использовал. Некоторые предостережения и о чем следует помнить при этом:
Не делайте этого, если у вас нет текущей резервной копии.
Я бы оставил оба значения в основной таблице. Таким образом, если вам когда-либо придется выяснить из старых документов, к какой записи вам нужно получить доступ, вы сможете это сделать.
При этом отключите базу данных для обслуживания и переведите ее в однопользовательский режим. Последнее, что вам нужно, когда вы делаете что-то вроде этого, - это пользователь, пытающийся внести изменения, пока вы находитесь в середине потока. Конечно, первое действие в однопользовательском режиме - это вышеупомянутое резервное копирование. Вероятно, вам следует запланировать время простоя на время, когда использование будет минимальным.
Сначала протестируйте на разработчиках! Это также должно дать вам представление о том, как долго вам нужно будет закрыть производство. Кроме того, вы можете попробовать несколько методов, чтобы определить, какой из них самый быстрый.
Обязательно сообщите пользователям заранее, что база данных выйдет из строя в запланированное время для обслуживания и когда они могут ожидать, что она снова станет доступной. Убедитесь, что время подходящее. Люди действительно злятся, когда они планируют опоздать, чтобы составлять квартальные отчеты, а база данных недоступна, и они не знали об этом.
Имеется довольно большое количество записей, возможно, вы захотите запускать обновления дочерних таблиц в пакетном режиме (одна из причин не использовать каскадные обновления). Это может быть быстрее, чем пытаться обновить 5 миллионов записей за одно обновление. Однако не пытайтесь обновлять одну запись за раз, иначе вы все равно будете здесь в следующем году, выполняя эту задачу.
Отбросьте индексы в поле GUID во всех таблицах и создайте заново после того, как закончите. Это должно улучшить эффективность изменения.
Создайте новую таблицу со старым и новым значениями pk. Установите уникальные ограничения на оба столбца, чтобы убедиться, что вы пока ничего не сломали.
Отключить ограничения.
Выполните обновления для всех таблиц, чтобы изменить старое значение на новое.
Включите PK, затем включите FK.
Трудно сказать, какой подход является «лучшим» или «наиболее подходящим», поскольку вы не описали, что вы ищете в решении. Например, должны ли таблицы быть доступны для запроса при переходе на новые идентификаторы? Должны ли они быть доступны для одновременной модификации? Важно ли завершить миграцию как можно быстрее? Важно ли минимизировать пространство, используемое для миграции?
Сказав это, я бы предпочел №1 другим вашим идеям, если они все соответствуют вашим требованиям.
Все, что связано с триггером для обновления дочерних таблиц, кажется подверженным ошибкам, слишком сложным и, вероятно, не будет работать так же хорошо, как №1.
Можно ли предположить, что новые идентификаторы никогда не будут конфликтовать со старыми идентификаторами? В противном случае решения, основанные на обновлении идентификаторов по одному, должны будут беспокоиться о коллизиях - в спешке это станет беспорядком.
Рассматривали ли вы возможность использования CREATE TABLE AS SELECT (CTAS) для заполнения новых таблиц новыми идентификаторами? Вы будете делать копию своих существующих таблиц, и для этого потребуется дополнительное пространство, однако это, вероятно, будет быстрее, чем обновление существующих таблиц на месте. Идея заключается в следующем: (i) использовать CTAS для создания новых таблиц с новыми идентификаторами вместо старых, (ii) создавать нужные индексы и ограничения для новых таблиц, (iii) удалять старые таблицы, (iv) переименовывать новые таблицы к старым названиям.
Фактически, это зависит от вашей СУБД.
Используя Oracle, самый простой выбор - сделать все ограничения внешнего ключа «отложенными» (проверка при фиксации), выполнить обновления в одной транзакции, а затем зафиксировать.