Spec-Zone .ru
спецификации, руководства, описания, API
|
Исторически, много операций DDL на InnoDB
таблицы были дороги. Многие ALTER TABLE
операции, работавшие, составляя новую, пустую таблицу, определенную с
помощью требуемых табличных опций и, индексируют, затем копируя существующие строки в новую таблицу один за
другим, обновляя индексирование, поскольку строки были вставлены. После того, как все строки от исходной таблицы
были скопированы, старая таблица была отброшена, и копия была переименована с именем исходной таблицы.
MySQL 5.5, и MySQL 5.1 с Плагином InnoDB, оптимизированным CREATE INDEX
и DROP INDEX
избегать копирующего таблицу поведения. Та функция была известна как
Быстрое Создание индекса. MySQL 5.6 улучшает много других типов ALTER TABLE
операции, чтобы избежать копировать таблицу. Другое улучшение
позволяет SELECT
запросы и INSERT
,
UPDATE
,
и DELETE
Операторы (DML),
чтобы продолжиться, в то время как таблица изменяется. В MySQL 5.7, ALTER TABLE RENAME INDEX
был также улучшен, чтобы избежать табличного
копирования. Эта комбинация функций теперь известна как онлайновый DDL.
Этот новый механизм также означает, что можно обычно ускорить полный процесс создания, и загрузка таблицы и связанный индексирует, составляя таблицу с без любого вторичного устройства, индексирует, затем добавляя, что вторичное устройство индексирует после того, как данные загружаются.
Хотя никакие изменения синтаксиса не требуются в CREATE
INDEX
или DROP INDEX
команды, некоторые факторы влияют на производительность, использование
пространства, и семантику этой работы (см. Раздел 5.5.9, "Ограничения
Онлайнового DDL").
Онлайновые улучшения DDL в MySQL 5.6 улучшают много операций DDL, которые прежде потребовали табличной копии,
блокировал операции DML на таблице, или обоих. Таблица
5.8, "Сводка Онлайнового Состояния для Операций DDL" показывает изменения ALTER TABLE
оператор и шоу, как онлайновая функция DDL применяется к каждому.
Шоу столбца "In-Place?", которые операции позволяют ALGORITHM=INPLACE
пункт; привилегированное значение - "Да".
Шоу столбца "Copies Table?", какие операции в состоянии избежать дорогой копирующей таблицу работы; привилегированное значение является "Нет". Этот столбец является главным образом реверсом столбца "In-Place?", за исключением того, что несколько операций позволяют ALGORITHM=INPLACE
но все еще включите некоторое количество табличного копирования.
Шоу столбца "Allows Concurrent DML?", какие операции могут быть выполнены полностью онлайн; привилегированное значение - "Да". Можно определить LOCK=NONE
чтобы утверждать, что полный параллелизм позволяется во время DDL, но MySQL автоматически позволяет этот уровень параллелизма когда возможный. Когда параллельный DML позволяется, параллельные запросы также всегда позволяются.
Шоу столбца "Allows Concurrent Queries?", которые операции DDL позволяют запросам на таблице, в то время как работа происходит; привилегированное значение - "Да". Параллельный запрос позволяется во время всех онлайновых операций DDL. Это показывают с "Да" перечисленным для всех ячеек в ссылочных целях. Можно определить LOCK=SHARED
чтобы утверждать, что параллельные запросы позволяются во время DDL, но MySQL автоматически позволяет этот уровень параллелизма когда возможный.
Столбец "Notes" объясняет любые исключения "да/нет" значения других столбцов, такой как тогда, когда ответ зависит от установки параметра конфигурации или некоторого другого пункта в операторе DDL. Значения "Да *" и "Нет *" не указывают, что ответ зависит от этих дополнительных примечаний.
Таблица 5.8. Сводка Онлайнового Состояния для Операций DDL
Работа | Оперативный? | Копирует Таблицу? | Позволяет Параллельный DML? | Позволяет Параллельный Запрос? | Примечания |
---|---|---|---|---|---|
CREATE INDEX , ADD
INDEX |
Yes* | No* | Да | Да | Некоторые ограничения для FULLTEXT индексируйте; см.
следующую строку. В настоящий момент работа не является оперативной (то есть, она копирует
таблицу), если то же самое индексирует быть создаваемым, был также отброшен более ранним пунктом
в том же самом ALTER
TABLE оператор.
|
ADD FULLTEXT INDEX |
Да | No* | Нет | Да | Создание первого FULLTEXT индексируйте для таблицы,
включает табличную копию, если нет предоставленный пользователем FTS_DOC_ID
столбец. Последующий FULLTEXT индексирует на той же самой таблице,
может быть создан оперативный.
|
RENAME INDEX |
Да | Нет | Нет | Нет | |
DROP INDEX |
Да | Нет | Да | Да | |
Установите значение по умолчанию для столбца | Да | Нет | Да | Да | Изменяет .frm файл только, не файл данных. |
Измените автоинкрементное значение для столбца | Да | Нет | Да | Да | Изменяет значение, сохраненное в памяти, не файле данных. |
Добавьте ограничение внешнего ключа | Yes* | No* | Да | Да | Чтобы избежать копировать таблицу, отключить foreign_key_checks во время ограничительного создания.
|
Отбросьте ограничение внешнего ключа | Да | Нет | Да | Да | foreign_key_checks опция может быть включена или отключена.
|
Переименуйте столбец | Yes* | No* | Yes* | Да | Чтобы позволить параллельный DML, сохраните тот же самый тип данных и только измените имя столбца. |
Добавьте столбец | Да | Да | Yes* | Да | Параллельный DML не позволяется, добавляя столбец
автоприращения. Хотя ALGORITHM=INPLACE позволяется, данные
реорганизовываются существенно, таким образом, это - все еще дорогая работа.
|
Отбросьте столбец | Да | Да | Да | Да | Хотя ALGORITHM=INPLACE позволяется, данные
реорганизовываются существенно, таким образом, это - все еще дорогая работа.
|
Переупорядочьте столбцы | Да | Да | Да | Да | Хотя ALGORITHM=INPLACE позволяется, данные
реорганизовываются существенно, таким образом, это - все еще дорогая работа.
|
Изменение ROW_FORMAT свойство |
Да | Да | Да | Да | Хотя ALGORITHM=INPLACE позволяется, данные
реорганизовываются существенно, таким образом, это - все еще дорогая работа.
|
Изменение KEY_BLOCK_SIZE свойство |
Да | Да | Да | Да | Хотя ALGORITHM=INPLACE позволяется, данные
реорганизовываются существенно, таким образом, это - все еще дорогая работа.
|
Сделайте столбец NULL |
Да | Да | Да | Да | Хотя ALGORITHM=INPLACE позволяется, данные
реорганизовываются существенно, таким образом, это - все еще дорогая работа.
|
Сделайте столбец NOT NULL |
Yes* | Да | Да | Да | Когда SQL_MODE включает strict_all_tables
или strict_all_tables , работа перестала работать, если столбец
содержит кого-либо, обнуляет. Хотя ALGORITHM=INPLACE позволяется,
данные реорганизовываются существенно, таким образом, это - все еще дорогая работа.
|
Измените тип данных столбца | Нет | Да | Нет | Да | |
Добавьте первичный ключ | Yes* | Да | Да | Да | Хотя ALGORITHM=INPLACE позволяется, данные
реорганизовываются существенно, таким образом, это - все еще дорогая работа. ALGORITHM=INPLACE не позволяется при определенных условиях, если
столбцы должны быть преобразованы в NOT NULL . См. Пример
5.9, "Создавая и Отбрасывая Первичный ключ".
|
Отбросьте первичный ключ и добавьте другого | Да | Да | Да | Да | ALGORITHM=INPLACE только позволяется, когда Вы
добавляете новый первичный ключ в том же самом ALTER TABLE ; данные реорганизовываются существенно, таким
образом, это - все еще дорогая работа.
|
Отбросьте первичный ключ | Нет | Да | Нет | Да | Ограничения применяются, когда Вы отбрасываете первичный ключ первичного ключа, не
добавляя новый в том же самом ALTER TABLE оператор.
|
Преобразуйте набор символов | Нет | Да | Нет | Да | Восстанавливает таблицу, если новая кодировка символов отличается. |
Определите набор символов | Нет | Да | Нет | Да | Восстанавливает таблицу, если новая кодировка символов отличается. |
Восстановите с FORCE опция |
Нет | Да | Нет | Да | Законы как ALGORITHM=COPY пункт или установкаold_alter_table=1 .
|
Следующие разделы показывают основной синтаксис, и примечания использования, связанные с онлайновым DDL, для каждой из главных операций, которые могут быть выполнены с параллельным DML, оперативным, или оба:
Создайте вторичный, индексирует: CREATE INDEX
или name
ON table
(col_list
)ALTER TABLE
. (Создание a table
ADD INDEX name
(col_list
)FULLTEXT
индексируйте все еще требует блокировки таблицы.)
Вторичное
отбрасывание индексирует:
DROP INDEX
или name
ON table
;ALTER TABLE
table
DROP INDEX name
Создание и отбрасывание вторичного индексируют на InnoDB
таблицы пропускают
копирующее таблицу поведение, то же самое как в MySQL 5.5 и MySQL 5.1 с InnoDB
Плагин.
В MySQL 5.6 и выше, таблица остается доступной для операций чтения и операций записи, в то время как
индексирование создается или отбрасывается. CREATE
INDEX
или DROP INDEX
оператор только заканчивается после того, как все транзакции, которые получают доступ к таблице, завершаются,
так, чтобы начальное состояние индексирования отразило новое содержание таблицы. Ранее, изменение таблицы, в то
время как индексирование создавалось или отбрасывалось обычно, приводило к мертвой
блокировке, которая отменяла INSERT
,
UPDATE
,
или DELETE
оператор на таблице.
Установите значение по умолчанию для столбца: ALTER TABLE
или tbl
ALTER COLUMN col
SET DEFAULT literal
ALTER
TABLE
tbl
ALTER COLUMN col
DROP DEFAULT
Значения по умолчанию для столбцов сохранены в.frm файле для таблицы, не
InnoDB
словарь данных.
Изменение автоинкрементного значения для столбца:
ALTER TABLE
table
AUTO_INCREMENT=next_value
;
Особенно в распределенной системе, используя репликацию или sharding, Вы иногда сбрасываете автоинкрементный счетчик для таблицы к определенному значению. Следующая строка, вставленная в таблицу, использует указанное значение для своего столбца автоприращения. Вы могли бы также использовать этот метод в среде организации хранилищ данных, где Вы периодически пустой все таблицы и перезагружают их, и можно перезапустить автоинкрементную последовательность от 1.
Переименование столбца: ALTER TABLE
tbl
CHANGE old_col_name
new_col_name
datatype
Когда Вы сохраняете тот же самый тип данных и [NOT] NULL
атрибут,
только изменяя имя столбца, эта работа может всегда выполняться онлайн.
Как часть этого улучшения, можно теперь переименовать столбец, который является частью ограничения
внешнего ключа, которое не было позволено прежде. Определение внешнего ключа автоматически
обновляется, чтобы использовать новое имя столбца. Переименование столбца, участвующего во внешнем
ключе только, работает с оперативным режимом ALTER TABLE
. Если Вы используете ALGORITHM=COPY
пункт, или некоторое другое условие заставляет команду использовать ALGORITHM=COPY
негласно, ALTER TABLE
оператор перестанет работать.
Расширение VARCHAR
размер используя оперативное ALTER
TABLE
оператор, как в этом примере:
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(255);
Число байтов длины требуется a VARCHAR
столбец должен остаться тем же самым. Для VARCHAR
значения от 0 до 255, байт одной длины обязан кодировать
значение. Для VARCHAR
значения 256 байтов или больше, два байта длины требуются. В результате оперативный ALTER TABLE
только поддерживает увеличение VARCHAR
размер от 0 до 255 байтов или увеличение VARCHAR
размер от значения, равного или больше чем 256 байтов.
Оперативный ALTER TABLE
не
поддерживает увеличение VARCHAR
размер меньше чем от 256 байтов до значения, равного или
больше чем 256 байтов. В этом случае число необходимых байтов длины изменилось бы от 1 до 2, который
только поддерживается табличной копией (ALGORITHM=COPY
).
Уменьшение VARCHAR
размер, использующий оперативный ALTER
TABLE
не поддерживается. Уменьшение VARCHAR
размер требует табличной копии (ALGORITHM=COPY
).
Добавление или отбрасывание ограничения внешнего ключа:
ALTER TABLEtbl1
ADD CONSTRAINTfk_name
FOREIGN KEYindex
(col1
) REFERENCEStbl2
(col2
)referential_actions
;ALTER TABLEtbl
DROP FOREIGN KEYfk_name
;
Отбрасывание внешнего ключа может быть выполнено онлайн с foreign_key_checks
опция, включенная или отключенная. Создание
внешнего ключа онлайн требует foreign_key_checks
быть отключенным.
Если Вы не знаете имена об ограничениях внешнего ключа на определенную таблицу, делаете следующее
заявление и находите ограничительное имя в CONSTRAINT
пункт для каждого
внешнего ключа:
show create table table
\G
Или, запросите information_schema.table_constraints
таблица и использование constraint_name
и constraint_type
столбцы,
чтобы идентифицировать имена внешнего ключа.
Как следствие этого улучшения можно теперь также отбросить внешний ключ, и его связанные индексируют в единственном операторе, который ранее потребовал отдельных операторов в строгом порядке:
ALTER TABLEtable
DROP FOREIGN KEYconstraint
, DROP INDEXindex
;
Если внешние ключи уже
присутствуют в измененной таблице (то есть, это - дочерняя таблица, содержащая любого
FOREIGN KEY ... REFERENCE
пункты), дополнительные ограничения применяются к
онлайновым операциям DDL, даже те, которые не непосредственно включают столбцы внешнего ключа:
Параллельный DML отвергается во время онлайновых операций DDL на таких дочерних таблицах. (Это ограничение оценивается как ошибка и могло бы быть снято.)
ALTER TABLE
на дочерней таблице мог также ожидать другой транзакции, чтобы фиксировать, если изменение к
родительской таблице вызванные связанные изменения в дочерней таблице через ON
UPDATE
или ON DELETE
пункт используя CASCADE
или SET NULL
параметры.
Таким же образом, если таблица является родительской таблицей в отношении внешнего
ключа, даже при том, что это не содержит никого FOREIGN KEY
пункты, это могло
ожидать ALTER TABLE
завершаться если INSERT
, UPDATE
,
или DELETE
оператор, вызванный ON UPDATE
или ON DELETE
действие в дочерней таблице.
ALGORITHM=COPY
Любой ALTER TABLE
работа, выполненная с ALGORITHM=COPY
пункт предотвращает параллельные операции DML. Параллельные запросы все еще позволяются. Таким образом,
копирующая таблицу работа всегда включает, по крайней мере, ограничения параллелизма LOCK=SHARED
(позвольте запросы, но не DML). Можно далее ограничить параллелизм для таких операций, определяя LOCK=EXCLUSIVE
(предотвратите DML и запросы).
Некоторый другой ALTER TABLE
операции позволяют параллельный DML, и являются быстрее чем MySQL 5.5
и предшествующими: копирующая таблицу работа оптимизируется, даже при том, что табличная копия все еще
требуется:
Добавление, отбрасывая, или переупорядочивая столбцы.
Добавление или отбрасывание первичного ключа.
Изменение ROW_FORMAT
или KEY_BLOCK_SIZE
свойства для таблицы.
Изменение nullable состояния для столбца.
Поскольку Ваша схема базы данных развивается с новыми столбцами, типами данных, ограничениями,
индексирует, и так далее, сохраните Ваш CREATE
TABLE
операторы, современные с последними табличными определениями. Даже с улучшениями
производительности онлайнового DDL, более эффективно создать устойчивые структуры базы данных вначале,
вместо того, чтобы создать часть схемы и затем выйти ALTER TABLE
операторы позже.
Основное исключение к этой направляющей линии для вторичного, индексирует на таблицах с большими количествами строк. Является обычно самым эффективным составить таблицу со всеми деталями, определенными кроме вторичного устройства, индексирует, загрузите данные, затем создайте вторичное устройство, индексирует. Можно использовать тот же самый метод с внешними ключами (загрузите данные сначала, затем установите внешние ключи), если Вы знаете, что исходные данные чисты, и не нуждаются в проверках непротиворечивости во время процесса загрузки.
Безотносительно последовательности CREATE
TABLE
, CREATE INDEX
, ALTER TABLE
, и подобные операторы вошли в соединение таблицы, можно получить
SQL, должен был восстановить текущую форму таблицы, делая заявление SHOW CREATE TABLE
(верхний регистр table
\G\G
требуемый для опрятного форматирования). Этот вывод показывает пункты,
такие как числовая точность, NOT NULL
, и CHARACTER
SET
это иногда добавляется негласно, и Вы могли бы иначе не учесть, клонируя таблицу на новой
системе или устанавливая столбцы внешнего ключа с идентичным типом.