1. История развития Online DDL

Функция MySQL Online DDL была официально представлена ​​в версии 5.6 и развита до текущей версии 8.0, претерпев множество изменений и улучшений. В этой статье в основном описывается процесс разработки Online DDL и различия между каждой версией. Фактически, метод INPLACE DDL был добавлен еще в MySQL 5.5, но из-за проблем реализации он по-прежнему блокирует операции INSERT, UPDATE и DELETE. Это одна из причин, по которой ранние версии MySQL долгое время подвергались критике. .

существовать MySQL 5.6 середина,официальный стартподдерживать Болееиз ALTER TABLE Манипулирование типами, чтобы избежать копирования данных, пока существование существует в сети. DDL без блокировки во время процесса DML Операция, реализованная в реальном смысле Online ДДЛ. Однако не все DDL Все операции выполняются онлайн и будут прикреплены позже. MySQL Официальная документация по DDL Краткое описание операций.

приезжать MySQL 5.7,существовать 5.6 На основе были добавлены некоторые новые функции, такие как: добавлена ​​поддержка переименования индексов, поддержка увеличения и уменьшения длины числовых типов, а также поддержка VARCHAR Типа изсуществовать расширение строки и так далее. Но базовая логика реализации и ограничения по сравнению с 5.6 Больших изменений нет.

MySQL 8.0 действительно переработана реализация DDL,Это самое большое улучшение да DDL Операции поддерживают атомарные свойства. Кроме того, онлайн DDL из ALGORITHM В параметр добавлена ​​новая опция: INSTANT. Вам нужно только изменить словарь данных, не нужно копировать данные или перестраивать поверхность. Также нет необходимости добавлять эксклюзивы. MDL Lock, исходные данные таблицы не будут затронуты. весь DDL Процесс завершается практически мгновенно и не будет заблокирован. DML。

2. Онлайн-алгоритм DDL

учиться Online DDL Сначала поучись, прежде чем DDL из 2 алгоритм copy и inplace。

Копировать алгоритм

1. Создайте одну новую временную поверхность в соответствии с определением исходной поверхности.
2. верно Оригиналповерхностьдобавлять Написать Замок (запрещено) ДМЛ, разреши select）
3. шаг 1) Создание временного исполнения поверхности. DDL
4. Воля Оригиналповерхностьсерединаизданные copy во временную таблицу
5. выпускатьпомещать Оригиналповерхность из замка
6. Удалите исходную таблицу и переименуйте временную таблицу в исходную таблицу. Видимый, принятый copy Замокповерхность обязательна в пути, запрещена DML, поэтому да нет Online из。например：Удалить первичный ключ、Изменить тип столбца, измените набор символов, эти операции приведут к изменению формата записи строки (невозможно передать полный код). + постепенное внедрение Online）。

Алгоритм замены

существовать Оригиналповерхностьвносить изменения в,Нет необходимости создавать временную поверхность,Нет необходимости выполнять копирование данных из процесса. В зависимости от того, следует ли менять формат записи строки,Разделен на две категории：

1. перестроить: необходимо перестроить поверхность (реорганизовать индекс кластера). например optimize таблица, добавить индекс, добавить/удалить столбцы, изменить столбцы NULL/NOT NULL Недвижимость и т. д.;
2. no-rebuild: нет необходимости перестраивать поверхность.,Просто нужно изменить данные поверхности.,например Удалить индекс、Изменить столбецимя、Изменить значение столбца по умолчанию、Изменить столбецсамовозрастающийценитьждать。

верно В rebuild Способ достижения Online через кеширование DDL Период из ДМЛ, подожди DDL После завершения DML Нанесите на поверхность, чтобы добиться из.

проиллюстрировать:

1. существовать copy данныек новомуповерхностьпериод,существовать Оригиналповерхностьначальстводадобавлятьиз MDL Читать Замок (разрешить ДМЛ, запрещено DDL）
2. существовать Применить приращенияпериодверно Оригиналповерхностьдобавлять MDL Написать Замок (запрещено) DML и DDL）
3. в соответствии споверхностьAперестроенизданныедапомещатьсуществовать tmp_file Вот из-за этого временного файла да InnoDB существуют внутренне созданные из, целого DDL Этот процесссуществовать InnoDB Внутренняя отделка. верно Ю server По слоям данные не были перемещены. временную таблице, даодин работает на месте, отсюда и произошло название да «на месте».

Существует 2 типа MySQL середина, уровень поверхности и з Замок. Что-то вроде да, мы обычно говорим иззамок стол, реализованный движком InnoDB, например lock tables … read/write,замок стол имеет большее влияние и обычно не используется. Еще один уровень поверхности из ЗамокдаMDL( metadata lock ), реализованный уровнем Сервера, мы не используем MDL явно, дасуществовать автоматически добавляется библиотекой данных при доступе к одинповерхности, Когда запись вернойповерхности добавлена, удалена, изменена и проверена, добавьте MDL для чтения Замок; когда структура вернойповерхности изменена, добавьте MDL для чтения Замок; MDL Замок, чтение и чтение не являются взаимоисключающими, чтение и запись, запись и запись являются взаимоисключающими. Каковы общие операции «блокировки таблицы»? Создать вторичный индекс (вторичный индексда относится к изиндексу в дополнение к индексу первичного ключа), удалить индекс, переименовать индекс, изменить тип индекса - Нет «запирайте стол». Добавление полей, удаление полей, переименование полей, настройка порядка полей, установка значений полей по умолчанию, удаление значений полей по умолчанию, изменение значений автоприращения, настройка полей для разрешения NULL, настройка полей для запрета NULL —— Не «запирайте стол». Расширенный размер поля Varchar - Нет «запирайте стол». Измените тип данных поля, например, изменив varchar на текст — «заблокировать таблицу».

3. Блокировки в процессе Online DDL

По умолчанию,MySQLДаподдерживатьonlineизDDLдействоватьиз,существуютонлайнизDDL выполнение оператора из процесса середина,MySQL будет стараться как можно меньше использовать лимит Замков.,Для этого нам не нужны специальные операции.

MySQLсуществоватьвыбиратьизкогда,Используйте Замок как можно реже,Но да не исключает, что он предпочтет использовать Замок. И если я беспокоюсь, что он выберет Замок, и мы изповерхность не сможем ни читать, ни писать.,Очевидно, это не тот результат, который нам нужен,Мы надеемся: если вы выберете Замок, не выполняйте его.,Выйти из выполнения напрямую; выполнить, если Замок не выбран. Чтобы добиться желаемого эффекта,Что делать?

существование может казнить нас в онлайне DDLзаявлениеизкогда,Используйте ключевое слово ALGORITHMиLOCK.,Эти два ключевых слова существуют в операторе DDL в конце.,Просто разделите их запятыми. Примеры следующие:

ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM=INPLACE, LOCK=NONE;

Варианты АЛГОРИТМА

• INPLACE: Замена: Непосредственное выполнение операций DDLiz над исходной поверхностью.
• КОПИЯ: копировать: использовать способ временной поверхности,Клонировать одну временную поверхность,существоватьвременныйповерхностьначальствоосуществлятьDDL,Затем импортируйте данные в во временную таблицасередина, существование, переименование и т.д. В этот период для поддержки этого потребуется в два раза больше дискового пространства. действовать. Во время выполнения поверхность не позволяет выполнять операции DMLиз.
• ПО УМОЛЧАНИЮ: режим по умолчанию.,Есть MySQL на выбор,Метод INPLACEиз является предпочтительным.

Параметры блокировки

• SHARE：общий Замок,Выполнение DDL изповерхности может читать,Но да нельзя написать.
• NONE: Никаких ограничений, выполнение DDL-изповерхности можно читать и записывать.
• ЭКСКЛЮЗИВ: эксклюзивный Замок,Выполняемая DDL-изповерхность не может быть прочитана,Ты тоже не можешь писать.
• DEFAULT：по умолчаниюценить,также ДасуществоватьDDLзаявлениесередина Не указанLOCKпунктизкогдаиспользоватьизпо умолчаниюценить。Если указаноLOCKизценитьдляDEFAULT,Что ДасдаватьMySQLпунктчувствовать Замок Ну давай жк Нет Замок поверхности. Не рекомендуется,Если вы уверены, что ваш оператор DDL не будет,Вы не можете указать блокировку или указать ее по умолчанию.,В противном случае рекомендуется указать его тип из Замок.

При выполнении операций DDL,Опцию АЛГОРИТМ указывать не нужно.,В это время MySQL автоматически выбирает соответствующий режим в порядке INSTANT, INPLACE и COPY. Вы также можете указать ALGORITHM=DEFAULT.,И тот же эффект. Если указана опция АЛГОРИТМ,Но нет слов,Об ошибке будет сообщено напрямую.

Уведомление:

существовать до выполнения OnlineDDL,Это должно осуществляться в непиковые часы работы.,И необходимо подтвердить отсутствие неотправленных транзакций, замков и другой информации об ожидании исполнения. Вы можете использовать следующий оператор изSQL, чтобы проверить, есть ли в да транзакции и Замок и другая информация.

select * from information_schema.innodb_locks;
select * from information_schema.innodb_trx;
select * from information_schema.innodb_lock_waits;
select * from information_schema.processlist;

4. Понять потребности и проблемы операций DDL.

Операции DDL включают в себя верданные библиотеки структуры поверхности из модификаций.,Например, добавьте /Удалить столбец, Изменить определение столбца, добавить/удалить индекс и т. д. существуют предыдущая версия середина,При выполнении этих операций DDL вам необходимо определить всю систему.,Доступность проверенных библиотек пострадала негативно. поэтому,Внедрение существующего онлайн-DDL стало важным требованием для повышения гибкости и производительности системы.

5. Функциональные возможности онлайн-DDL MySQL 5.7.

MySQL 5.7 реализует существующие онлайн-функции DDL за счет улучшений механизма хранения InnoDB. Ниже приведены основные особенности этой функции:

• Поддержка добавления вспомогательного индекса: Можно добавить вспомогательный индекс на существование, работая серединаизповерхность, без проверки всей поверхности в блокировке. строки Конечно。
• поддерживать Изменить столбец Конечноправедный：Можетсуществовать Проволока Изменить столбецизданныетип、длинаждать Конечноправедный。
• Поддержка Изменение набора символов и параметров сортировки: вы можете изменить набор символов поверхности и параметры сортировки онлайн.
• поддержка переименования столбца: вы можете существовать, не влияя на нормальное существование, из операций чтения и записи из регистра,Колонка вернаяповерхностьсерединаиз переименована.

6. Принципы реализации и оптимизация

Реализация существующей линейной функциональности DDL включает в себя следующие ключевые этапы оптимизации:

• 1. Создайте временную поверхность. Создайте временную поверхность для хранения новой структуры, необходимой для операций DDL. так,Старую поверхность все еще можно использовать для операций чтения и записи.
• 2 данныекопироватьисинхронный：будет старымповерхностьсерединаизданныепостепеннокопироватьво временную таблицусередина,и сохрани староеповерхностьданныеивременныйповерхностьданныеизсинхронный。Этот процесс обеспечиваетданныесуществоватьDDLдействовать Период изчестностьипоследовательность。
• 3 Захват и воспроизведение изменений. Используя такие механизмы, как журналы и журналы повторного выполнения, фиксируйте изменения, произошедшие во время выполнения операций DDL, и воспроизводите их во время. временную таблицасередина. Это обеспечивает согласованность данных после завершения операции DDL.
• 4 Окончательное переключение: когда операция DDL будет завершена, механизм библиотеки данных переключится в соответствующее время. временную таблицу,Сделайте это новой структурой изповерхности.,И действительно, «поверхность» выполняет последующие операции чтения и записи.

7. Ограничения использования и меры предосторожности.

Хотя MySQL 5.7 реализует функциональность DDL в примерном соответствии с опытом, но все же существуют некоторые ограничения и соображения:

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

8. Подробности поддержки для каждой версии

Все данные в этой статье взяты из MySQL Официальная документация,Продолжить здесьодиннаборсерединаизаккуратныйи Подвести итог：

https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

9. Режим выполнения DDL

Сочетая приведенный выше формат изповерхности, MySQL текущий DDL из режима выполнения резюмируется следующим образом:

1. INSTANT DDL да MySQL 8.0 представлятьизновые возможности,текущийподдержаниеменьший диапазон,включать:

• Изменить тип вторичного индекса
• Добавить новый столбец
• Изменить значение столбца по умолчанию
• Изменить значение столбца ENUM
• Переименовать таблицу

2. существоватьосуществлять DDL Во время работы MySQL Внутреннее верно Ю ALGORITHM из Стратегия выбора:

Если пользователь явно указывает АЛГОРИТМ, используйте указанную пользователем опцию;

Если пользователь не укажет, то если операция поддерживает INPLACE, предпочтение будет отдано INPLACE, в противном случае будет выбрана COPY;

текущий Нетподдерживать INPLACE издействовать В основном включают：

• Удалить первичный ключ
• Изменить столбецданныетип
• Изменить набор символов таблицы

3. Мы часто говорим из Online DDL, по сути, даот DML Описание операции из угла если DDL Операция не блокируется DML операция, то это DDL Да Online из。текущий Нет Online из DDL На самом деле их уже относительно немного, в основном в том числе:

• Добавить полнотекстовый индекс
• Добавить индекс пространства
• Удалить первичный ключ
• Изменить столбецданныетип
• Укажите набор символов таблицы
• Изменить набор символов таблицы

Более подробные примеры можно найти в официальном документе по указанному выше адресу.

10. Обсудите несколько вопросов

Наконец, давайте обсудим некоторые очень запутанные вопросы:

• Онлайн-DDL не работает и может быть выполнен по желанию.
• поддерживать INPLACE Алгоритм DDL Определенно Online из。
• верно Вподдерживать INPLACE Алгоритм DDL,Операция DDL для изменения данных на месте,Никакого дополнительного места не требуется.

Вопрос 1. Будет ли блокироваться таблицы Online DDL?

Online DDL Это будет Замокповерхность? Чтобы ответить на этот вопрос, надо сначала уточнить значение слова «Замокповерхность» из. много MySQL Пользователи зачастую существующейповерхности не могут нормально работать. DML В то время я чувствовал, что да Замокповерхность на самом деле слишком широка и действительно может повлиять на DML Операции из Замок включают как минимум следующие типы (по умолчанию: InnoDB поверхность):

Замок MDL

замок стола

блокировка строки

GAP Замок

что кроме Замок MDLдасуществовать Server За исключением слоя плюс, остальные три дасуществовать. InnoDB Слой плюс из. Конкретная логика из плюс Замок здесь не будет подробно описана, но необходимо прояснить: все операции (независимо от да) DDL Ну давай же DML Ну давай же Запросзаявление）Всенуждаться Возьми это первым Server слойиз Замок леев, а затем возьми это InnoDB Слой из определенной потребности из Замок.

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

• Первый выбор,существоватьначать DDL Когда вам нужно получить надежную поверхностьиз MDL X Закройте, а затем проведите ряд подготовительных работ;
• а потом MDL X Замок понижен до MDL S Замок, выполняй настоящее из DDL действовать;
• Наконец-то снова MDL S Замок повышен до MDL X Замок,Заканчивать DDL действовать, выпускать Замок MDL；

Так существование действительно работает DDL Во время операции, правда, не будет "Замокповерхности", но если будет существовать, то первый этап займет MDL X Замокповерхность невозможно получить нормально, тогда возможно, что из действительно знает "Замокповерхность". Один простой пример выглядит следующим образом:

# session 1
select sleep(500) from mytest.t1;

# session 2
optimize table mytest.t1;

# session 3
select * from mytest.t1;

session 1 смоделировал один медленный запрос, а затем session 2 начать DDL Операция, не может быть получена MDL X Замок, ждём пока середина. в это время session 3 Необходимо выполнить один запрос, но выяснилось, что он не может быть выполнен. На самом деле существуют session 1 Прежде чем закончить, таблица t1 из Все операции недоступны. Также можно сказать, что поверхность. t1 “Замокповерхность”Понятно。MySQL 5.7/8.0 Можно включить performance_schema Прямой запрос в случае из metadata_locks поверхность. Алибаба Облако RDS 5.6 Добавлена ​​новая версия I_S.MDL_INFO стол, предусмотрен MDL запрос.

MySQL [performance_schema]> select * from metadata_locks where OBJECT_NAME = 't1';
+-------------+---------------+-------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE            | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------+-------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | mytest        | t1          | NULL        |       140730442220576 | SHARED_READ          | TRANSACTION   | GRANTED     | sql_parse.cc:5916 |            1083 |             24 |
| TABLE       | mytest        | t1          | NULL        |       140730576178368 | SHARED_NO_READ_WRITE | TRANSACTION   | PENDING     | sql_parse.cc:5916 |            1091 |              3 |
| TABLE       | mytest        | t1          | NULL        |       140730374843168 | SHARED_READ          | TRANSACTION   | PENDING     | sql_parse.cc:5916 |            1092 |              3 |
+-------------+---------------+-------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
3 rows in set (0.00 sec)

После уточнения вышеизложенного понятия из,Вернемся к нашему вопросу,Online DDL да Нетда Нет Замокповерхность? Если вам нужно ответить,Тогда я могу только сказать,Онлайн-DDL небезопасен,Даже Нетда Можетслучайныйизосуществлять。Проволоканачальстводействовать Ну давай женуждатьсясуществовать Будьте осторожны в периоды низкой деловой активности.действовать。

Вопрос 2. DDL, поддерживающий алгоритм INPLACE, должен быть онлайн.

Концептуально INPLACE и Online да Вещи в двух разных измерениях. КОПИРОВАТЬ и INPLACE означает изда DDL Внутреннюю логику выполнения можно просто понять так: дасуществовать Server Слой операции, INPLACE дасуществовать InnoDB Слой операции. И пользователей больше беспокоит Online или нет, обычно связано только с одним вопросом: разрешен ли параллелизм DML。

Два основных вывода:

• COPY Выполнение алгоритма из DDL Определенно нет Online из；
• INPLACE Выполнение алгоритма из DDL Нет Определенно Online из；

Вопрос 3. Требует ли INPLACE DDL дополнительного пространства для данных?

Как мы упоминали ранее, MySQL Внутреннее верно Ю DDL из ALGORITHM Есть два варианта: ВСТАВИТЬ и COPY（8.0 Добавлен МГНОВЕННЫЙ, но да имеет меньшую дальность применения). КОПИРОВАТЬ Алгоритм относительно прост для понимания: создайте временную поверхность, а потом Оригиналповерхностьизданныекопироватьво временную таблицасередина и, наконец, замените исходную поверхность временной поверхностью. Верно на приведенный выше изшаг, в связи с необходимостью копирования оригинальной поверхностиизданныево временную таблицысередина, поэтому она обязательно будет занимать дополнительные изданные места.

Что Что?верно Вподдерживать INPLACE Алгоритм DDL, не требуется ли дополнительное пространство?

Ответ: Да. На самом деле, причина, по которой я задаю этот вопрос, заключается в том, что,Ну давай жепотому чтодляверно INPLACE В моем понимании есть отклонение. Проще говоря: ВСТАВИТЬ Описание издаповерхности, а не даданных файла. Если не создать временную поверхность, то всё да INPLACE из。

сделано, многие INPLACE DDL перестроят поверхность (будут созданы временные файлы данных),Поэтому потребуется дополнительное пространство.,Например:

• Добавить первичный ключ
• Восстановить первичный ключ
• Добавить новый столбец（8.0 поддерживать INSTANT DDL, не требуется)
• Удалить столбец
• Изменить порядок столбцов
• Удалить столбецпо умолчаниюценить
• Добавить значение столбца по умолчанию
• Исправлятьповерхность из ROW_FORMAT
• Таблица ОПТИМИЗАЦИЯ

Навыки обновляются благодаря обмену ими, и каждый раз, когда я получаю новые знания, мое сердце переполняется радостью. Искренне приглашаем вас подписаться на публичный аккаунт 『 Код на тридцать пять 』 , для получения дополнительной технической информации.