Как изменить и обновить большую таблицу, чтобы добавить составные ключевые столбцы из другой таблицы
У нас есть две очень большие таблицы в нашей базе данных Mysql(MariaDb). Table_1 содержит карту многие ко многим. Он имеет автоматически увеличивающийся первичный ключ и составной ключ из двух столбцов. Table_2 относится к первичному ключу Table_1. Мы не хотим исправлять эту очевидную ошибку в дизайне,
- Использовать составной первичный ключ в Table_1
- Добавьте два столбца в Table_2
- Заполните составной ключ в Table_2, скопировав данные из Table_1, и создайте для него индекс.
- Желательно удалить автоматически увеличивающийся ключевой столбец из обеих таблиц.
Эти таблицы содержат около 300 млн строк, а размер таблиц составляет примерно 10 ГБ. Нам нужно сделать эти обновления в течение примерно 6-часового сервисного окна. Я изучаю, как сделать это эффективно, и провожу испытания на реплике базы данных. До сих пор я не пробовал запускать что-либо с реальными данными, потому что обычных скриптов было бы недостаточно. Я не опытный администратор БД. Так что мне нужно немного света, чтобы это сделать. Мой вопрос в том, что было бы лучшим подходом/советами, чтобы сделать это эффективно?
Вещи, которые я пытался до сих пор
Я читал о новой функции столбец мгновенного добавления, но наша производственная БД находится на базе MariaDb версии 10.0, которая старше.
Я последовал предложениям в этот ответ и запустил приведенный ниже сценарий в последней версии БД с поддержкой мгновенного добавления столбца (изменение таблицы было мгновенным). В таблице было ~ 50 миллионов строк (1/6 от оригинала). Это заняло около двух часов, не считая создания новых индексов. Поэтому этого будет недостаточно.
SET join_buffer_size = 4 * 50 * 1024 * 1024; -- 50M keys of 4 bytes each
SET optimizer_switch='mrr=on,mrr_cost_based=off,mrr_sort_keys=on,optimize_join_buffer_size=on';
SET join_cache_level = 8;
UPDATE TABLE_2
JOIN TABLE_1 ON TABLE_1_Id = TABLE_2_FKT1_Id
SET
TABLE_2_KeyPart_1 = TABLE_1_KeyPart_1,
TABLE_2_KeyPart_2 = TABLE_1_KeyPart_2
Также рассматриваю возможность оценки этого инструмента https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html
Спасибо за предложение @DanielE. Мы рассмотрим это.
Ответы 1
План A: Используйте инструмент Percona: pt-online-schema-change.
План Б: Используйте конкурирующий продукт: gh-ost.
План C: не используйте UPDATE, это убийца. Вместо этого перестройте таблицу (таблицы) простым способом, а затем используйте RENAME TABLE, чтобы поставить новую версию на место.
Разделение вряд ли как-то поможет. Ссылка Даниэля помогает при длительном UPDATE, но дает компромисс между временем (это занимает больше времени) и инвазивностью (что не является проблемой, поскольку у вас есть окно обслуживания).
Еще немного подробностей о плане C (который я предпочитаю для случая это):
CREATE TABLE(s) ... -- with new names, and all the new features except secondary indexes
INSERT INTO new SELECT ... FROM old table(s)
RENAME TABLE real1 TO old1,
new1 TO real1,
real2 TO old2,
new2 TO real2;
test -- you still undo the RENAME if necessary
DROP TABLE old1, old2;
Спасибо за предложения! Да, мы хотели бы сделать это без внешних инструментов, если это возможно. Итак, я начну с плана С.
Можно ли использовать разделы? Затем сделать это раздел за разделом? Если нет, вот идея разбить его на части: mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks