Ежедневная сторона - большая таблица MySQL добавляет столбец

MySQL

Вопрос взят из:Ууху. Call.com/question/44…, В mysql 300 миллионов данных в таблице, которая не разделена на таблицы.Требование состоит в том, чтобы добавить столбец данных в эту большую таблицу. База данных не может быть остановлена, и есть добавления, удаления и модификации. Как это сделать? Ответ личный

Способ добавления столбца в предыдущих версиях MySQL:

ALTER TABLE 你的表 ADD COLUMN 新列 char(128);

Это вызовет блокировку таблицы.Простой процесс выглядит следующим образом:

  1. Создайте новую таблицу Table2, которая полностью изоморфна таблице Table1.
  2. Добавить блокировку записи в таблицу Table1
  3. Выполнить на таблице Table2ALTER TABLE 你的表 ADD COLUMN 新列 char(128)
  4. Скопируйте данные из таблицы 1 в таблицу 2.
  5. Переименуйте Table2 в Table1 и удалите Table1, сняв все связанные блокировки.

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

противДо MySQL 5.6 (не входит в комплект)версия, обновление одной таблицы повторяется в другой таблице с помощью триггеров, и выполняется синхронизация данных.Когда синхронизация данных завершена, бизнес-имя таблицы изменяется и новая таблица публикуется. Бизнес не будет приостановлен. Настройка триггера аналогична:

create trigger person_trigger_update AFTER UPDATE on 原有表 for each row 
begin set @x = "trigger UPDATE";
Replace into 新表 SELECT * from 原有表 where 新表.id = 原有表.id;
END IF;
end;

MySQL 5.6 (в комплекте)Представлены более поздние версииОсобенности онлайн-DDL:

Alter table 你的表 , ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}, LOCK [=] { DEFAULT| NONE| SHARED| EXCLUSIVE }

Среди параметров:

  • АЛГОРИТМ:
    • DEFAULT: метод по умолчанию. В MySQL 8.0, если указанный АЛГОРИТМ не отображается, предпочтение отдается алгоритму INSTANT. Если нет, будет использоваться алгоритм INPLACE. Если алгоритм INPLACE не поддерживается, будет использоваться метод COPY.
    • INSTANT: Недавно добавленный алгоритм в 8.0, добавленный столбец возвращается немедленно. Но это не может быть виртуальный столбец. Принцип очень прост: для нового столбца все исходные данные в таблицене изменить сразу, просто запишите этот столбец и значение по умолчанию в словаре таблицы.Для динамического формата строки по умолчанию (который на самом деле является вариантом сжатого), если этот столбец обновляется, исходные данные помечаются как удаленные, а обновленная запись добавляется в конец. Таким образом, пространство удаления из очереди не резервируется заранее, и изменение пространства записи строки может часто происходить после обновления. ноДля большинства предприятий будет изменена только самая последняя запись., так что без проблем.
    • INPLACE: изменение непосредственно в исходной таблице без копирования временной таблицы, вы можетезапись за записьюМодификация не будет генерировать большое количество андологов и редологов и не будет занимать много буферов. Это позволяет избежать нагрузки на операции ввода-вывода и ЦП, вызванной перестроением таблицы, и обеспечить хорошую производительность и параллелизм в течение периода.
    • COPY: копирование в новую временную таблицу для модификации. Из-за копирования записей будет сгенерировано большое количество андологов и редологов, а также будет занято много буферов, что повлияет на эффективность бизнеса.
  • ЗАМОК:
    • DEFAULT: Аналогично ПО УМОЛЧАНИЮ АЛГОРИТМА.
    • NONE: Нет блокировок, разрешены одновременные чтения и обновления таблицы.
    • SHARED: Общая блокировка, разрешает чтение и не разрешает обновления
    • EXCLUSIVE: чтение и обновление запрещено

Сравнение алгоритмов, используемых для онлайн-модификации DDL, поддерживаемых каждой версией:

image

Справочная документация:

в состоянии пройти:

ALTER TABLE 你的表 ADD COLUMN 新列 char(128), ALGORITHM=INSTANT, LOCK=NONE;

Аналогичный оператор используется для добавления полей онлайн. Лучше указать АЛГОРИТМ и БЛОКИРОВКУ,Таким образом, при выполнении DDL вы можете четко знать, какое влияние это окажет на ваш онлайн-бизнес..

В то же время процесс выполнения онлайн-DDL, вероятно, выглядит следующим образом:

imageСсылка на изображение из:zhuanlan.zhihu.com/p/162073721

Видно, что блокировка метаданных требуется на начальном этапе.Блокировка метаданных была введена в MySQL в 5.5.Существовал аналогичный механизм для защиты метаданных ранее, но концепция блокировки метаданных явно не предлагалась. Однако между версией до 5.5 (например, 5.1) и версией после 5.5 есть существенная разница в защите метаданных: защита метаданных в 5.1 находится на уровне операторов, а защита метаданных в 5.5 — на уровень сделки. Так называемый уровень операторов означает, что после завершения выполнения оператора, независимо от того, была ли транзакция зафиксирована или откатана, ее структура таблицы может быть обновлена ​​другими сеансами, в то время как уровень транзакции снимает блокировку метаданных после завершения транзакции.

После введения блокировки метаданных это в основном решает две проблемы.Одна из них проблема изоляции транзакций.Например, при повторяющемся уровне изоляции во время двух запросов сеанса A сеанс B изменяет структуру таблицы, а результаты двух запросы будут несовместимы., не может удовлетворить требования повторяемого чтения; другая проблема репликации данных. Например, когда сеанс A выполняет несколько операторов обновления, другой сеанс B изменяет структуру таблицы и отправляет ее первой, что приведет к подчиненный для повтора.Сначала повторите изменение, а затем повторите обновление, когда произойдет ошибка репликации.

Если в настоящее время выполняется много транзакций и есть транзакции, содержащие большие запросы, например:

START TRANSACTION;
select count(*) from 你的表
。。。。

Это аналогично выполнит долгосрочные транзакции и заблокирует.

Итак, в принципе:

  • Избегайте большого бизнеса
  • Вносите ли изменения в структуру таблицы при низких пиковых нагрузках
Категории