Требования к производительности и пространству - Проблемы ALTER TABLE тормозит

Это продолжение увлекательной статьи про alter table.

...

Раздел « Перемещение таблиц между табличными пространствами с помощью ALTER TABLE» .

ALTER TABLE t1 COMMENT = "NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RA_BY_NODE";

Чтобы убедиться, что параметры таблицы были изменены должным образом, используйте таблицу SHOW CREATE TABLEили запросите ее INFORMATION_SCHEMA.TABLES.

Требования к производительности и пространству

ALTER TABLE операции обрабатываются по одному из следующих алгоритмов:

Предложение не ALGORITHM является обязательным. Если ALGORITHM предложение опущено, MySQL использует ALGORITHM=INPLACEмеханизмы хранения и ALTER TABLEпредложения, которые его поддерживают. В противном случае ALGORITHM=COPYиспользуется.

Указание ALGORITHM предложения требует, чтобы операция использовала указанный алгоритм для предложений и механизмов хранения, которые его поддерживают, иначе в противном случае произойдет сбой с ошибкой. Указание ALGORITHM=DEFAULTаналогично исключению ALGORITHMпредложения.

ALTER TABLE операции, использующие COPYалгоритм, ожидают завершения других операций, изменяющих таблицу. После внесения изменений в копию таблицы данные копируются, исходная таблица удаляется, а копия таблицы переименовывается в имя исходной таблицы. Во время выполнения ALTER TABLE операции исходная таблица доступна для чтения другим сеансам (за исключением, отмеченным вкратце). Обновления и записи в таблицу, запущенные после начала ALTER TABLE операции, останавливаются до тех пор, пока новая таблица не будет готова, а затем автоматически перенаправляются в новую таблицу. Временная копия таблицы создается в каталоге базы данных исходной таблицы, если она не являетсяRENAME TO операция, которая перемещает таблицу в базу данных, которая находится в другом каталоге.

Исключением, упомянутым ранее, является то, что ALTER TABLE блокирует чтение (а не только запись) в момент, когда он готов к установке новой версии .frmфайла таблицы , отбрасывает старый файл и очищает устаревшие структуры таблиц из кешей таблиц и определений таблиц. На этом этапе он должен получить эксклюзивную блокировку. Для этого он ожидает завершения текущих считывателей и блокирует новые операции чтения и записи.

ALTER TABLE Операция , которая использует COPYалгоритм предотвращает параллельные операции DML. Параллельные запросы по-прежнему разрешены. То есть операция копирования таблицы всегда включает, по крайней мере, ограничения параллелизма LOCK=SHARED(разрешить запросы, но не DML). Вы можете дополнительно ограничить параллелизм для операций, поддерживающих это LOCKпредложение, указав LOCK=EXCLUSIVE, что предотвращает DML и запросы. Для получения дополнительной информации см. Управление параллелизмом .

Чтобы принудительно использовать COPY алгоритм для ALTER TABLE операции, которая в противном случае не использовала бы его, включите old_alter_table системную переменную или укажите ALGORITHM=COPY. Если существует конфликт между old_alter_tableпараметром и ALGORITHMпредложением со значением, отличным от DEFAULT, то ALGORITHM предложение имеет приоритет.

Для InnoDBтаблиц ALTER TABLE операция, использующая COPYалгоритм для таблицы, находящейся в общем табличном пространстве, может увеличить объем пространства, используемого табличным пространством. Такие операции требуют столько же дополнительного места, сколько данных в таблице плюс индексы. Для таблицы, находящейся в общем табличном пространстве, дополнительное пространство, используемое во время операции, не возвращается обратно в операционную систему, как это происходит для таблицы, которая находится в табличном пространстве файлов на таблицу .

Для получения информации о требованиях к пространству для онлайн-операций DDL см. Раздел 14.13.3, « Требования к пространству для онлайн-DDL » .

ALTER TABLE операции, которые используют INPLACE алгоритм, включают:

ALTER TABLE Обновление MySQL 5.5 временные столбцы в формате 5.6 ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, и FORCEопераций. Это преобразование не может быть выполнено с использованием INPLACEалгоритма, потому что таблица должна быть перестроена, поэтому указание ALGORITHM=INPLACEв этих случаях приведет к ошибке. При ALGORITHM=COPYнеобходимости уточняйте.

Если ALTER TABLE операция над многоколоночным индексом, используемым для разделения таблицы, KEYизменяет порядок столбцов, ее можно выполнить только с помощью ALGORITHM=COPY.

Предложения WITHOUT VALIDATIONи WITH VALIDATIONвлияют на то, ALTER TABLE выполняется ли операция на месте для модификаций виртуальных сгенерированных столбцов . См. Раздел 13.1.8.2, «ALTER TABLE и сгенерированные столбцы» .

NDB Cluster ранее при поддержке интернет - ALTER TABLE операции с использованием ONLINE и OFFLINE ключевых слов. Эти ключевые слова больше не поддерживаются; их использование вызывает синтаксическую ошибку. MySQL NDB Cluster 7.5 (и более поздние версии) поддерживает онлайн-операции с использованием того же ALGORITHM=INPLACE синтаксиса, что и для стандартного сервера MySQL. NDBне поддерживает изменение табличного пространства онлайн. См. Раздел 21.5.11, «Операции в сети с ALTER TABLE в кластере NDB» , для получения дополнительной информации.

ALTER TABLEс DISCARD ... PARTITION ... TABLESPACEили IMPORT ... PARTITION ... TABLESPACE не создает никаких временных таблиц или файлов временных разделов.

ALTER TABLE с ADD PARTITION, DROP PARTITION, COALESCE PARTITION, REBUILD PARTITION, или REORGANIZE PARTITION не создавать временные таблицы (кроме случаев , когда используется с NDB таблицами); однако эти операции могут создавать и создают временные файлы разделов.

ADD или DROP операции для RANGEили LIST разделов являются немедленными операциями или почти таковыми. ADDили COALESCEоперации для HASH или KEYразделов копировать данные между всеми разделами, если LINEAR HASHи LINEAR KEYне был использован; это фактически то же самое, что и создание новой таблицы, хотя операция ADD или COALESCE выполняется секция за секцией. REORGANIZE операции копируют только измененные разделы и не трогают неизмененные.

Для MyISAMтаблиц вы можете ускорить воссоздание индекса (самая медленная часть процесса изменения), установив для myisam_sort_buffer_sizeсистемной переменной высокое значение.

Контроль параллелизма

Для ALTER TABLE операций, которые его поддерживают, вы можете использовать это LOCKпредложение для управления уровнем одновременных операций чтения и записи в таблице во время ее изменения. Указание для этого предложения значения, отличного от значения по умолчанию, позволяет вам потребовать определенный объем одновременного доступа или исключительных прав во время операции изменения и останавливает операцию, если запрошенная степень блокировки недоступна. Параметры этого LOCKпредложения:

Добавление и удаление столбцов

Используйте ADDдля добавления новых столбцов в таблицу и DROPдля удаления существующих столбцов. является расширением MySQL для стандартного SQL. DROP col_name

Чтобы добавить столбец в определенное место в строке таблицы, используйте FIRST или . По умолчанию столбец добавляется последним. AFTER col_name

Если таблица содержит только один столбец, этот столбец нельзя удалить. Если вы собираетесь удалить таблицу, используйте DROP TABLEвместо этого инструкцию.

Если столбцы удаляются из таблицы, столбцы также удаляются из любого индекса, частью которого они являются. Если все столбцы, составляющие индекс, удаляются, индекс также удаляется.

Переименование, переопределение и изменение порядка столбцов

Предложения CHANGE, MODIFYи ALTERпозволяют изменять имена и определения существующих столбцов. У них есть следующие сравнительные характеристики:

CHANGEявляется расширением MySQL для стандартного SQL. MODIFYэто расширение MySQL для совместимости с Oracle.

Чтобы изменить столбец, чтобы изменить его имя и определение, используйте CHANGE, указав старое и новое имена и новое определение. Например, чтобы переименовать INT NOT NULLстолбец с aна bи изменить его определение для использования BIGINTтипа данных при сохранении NOT NULLатрибута, сделайте следующее:

ALTER TABLE t1 CHANGE a b BIGINT NOT NULL;

Чтобы изменить определение столбца, но не его имя, используйте CHANGEили MODIFY. При CHANGEсинтаксисе требуется два имени столбца, поэтому вы должны указать одно и то же имя дважды, чтобы оставить имя без изменений. Например, чтобы изменить определение столбца b, сделайте следующее:

ALTER TABLE t1 CHANGE b b INT NOT NULL;

MODIFY удобнее изменять определение без изменения имени, потому что имя столбца требуется только один раз:

ALTER TABLE t1 MODIFY b INT NOT NULL;

Чтобы изменить имя столбца, но не его определение, используйте CHANGE. Синтаксис требует определения столбца, поэтому, чтобы оставить определение без изменений, вы должны повторно указать определение, имеющееся у столбца в настоящее время. Например, чтобы переименовать INT NOT NULLстолбец с bв a, сделайте следующее:

ALTER TABLE t1 CHANGE b a INT NOT NULL;

Для изменения определения столбца с помощью CHANGEили MODIFYопределение должно включать тип данных и все атрибуты, которые должны применяться к новому столбцу, кроме атрибутов индекса, таких как PRIMARY KEYили UNIQUE. Атрибуты, присутствующие в исходном определении, но не указанные в новом определении, не переносятся. Предположим, что столбец col1определен как, INT UNSIGNED DEFAULT 1 COMMENT 'my column'и вы изменяете столбец следующим образом, намереваясь изменить только INTна BIGINT:

ALTER TABLE t1 MODIFY col1 BIGINT;

Это оператор изменяет тип данных от INT к BIGINT, но и сбрасывает UNSIGNED, DEFAULTи COMMENTатрибуты. Чтобы сохранить их, инструкция должна включать их явно:

ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';

При изменении типа данных с помощью CHANGEили MODIFYMySQL пытается как можно лучше преобразовать существующие значения столбцов в новый тип.

Если вы используете CHANGEили MODIFY для сокращения столбца, для которого существует индекс в столбце, а результирующая длина столбца меньше длины индекса, MySQL автоматически сокращает индекс.

Для столбцов, переименованных в CHANGE, MySQL автоматически переименовывает эти ссылки в переименованный столбец:

Для столбцов, переименованных в CHANGE, MySQL не переименовывает эти ссылки автоматически в переименованный столбец:

Чтобы изменить порядок столбцов в таблице, используйте операции FIRST и AFTER в CHANGEили MODIFY.

ALTER ... SET DEFAULTлибо ALTER ... DROP DEFAULT укажите новое значение по умолчанию для столбца, либо удалите старое значение по умолчанию, соответственно. Если старое значение по умолчанию удалено и столбец может быть удален NULL, новое значение по умолчанию будет NULL. Если столбец не может быть NULL, MySQL присваивает значение по умолчанию, как описано в Раздел 11.6, «Значения по умолчанию для типов данных» .

Первичные ключи и индексы

DROP PRIMARY KEYотбрасывает первичный ключ . Если первичного ключа нет, возникает ошибка. Для получения информации о характеристиках производительности первичных ключей, особенно для InnoDBтаблиц, см. Раздел 8.3.2, «Оптимизация первичного ключа» .

Если вы добавляете UNIQUE INDEXили PRIMARY KEYв таблицу, MySQL сохраняет ее перед любым неуникальным индексом, чтобы разрешить обнаружение повторяющихся ключей как можно раньше.

DROP INDEXудаляет индекс. Это расширение MySQL для стандартного SQL. См. Раздел 13.1.25, «Заявление DROP INDEX» . Чтобы определить имена индексов, используйте . SHOW INDEX FROM tbl_name

Некоторые механизмы хранения позволяют указать тип индекса при создании индекса. Синтаксис index_typeспецификатора . Для получения дополнительной информации см. Раздел 13.1.14, «Оператор CREATE INDEX» . Предпочтительная позиция - после списка столбцов. Вы должны ожидать поддержки использования этой опции до того, как список столбцов будет удален в будущем выпуске MySQL. USING type_nameUSING

index_optionзначения определяют дополнительные параметры для индекса. Подробнее о допустимых index_optionзначениях см. Раздел 13.1.14, «Оператор CREATE INDEX» .

RENAME INDEX old_index_name TO new_index_nameпереименовывает индекс. Это расширение MySQL для стандартного SQL. Содержание таблицы остается без изменений. old_index_nameдолжно быть именем существующего индекса в таблице, который не удаляется тем же ALTER TABLE оператором. new_index_name- это новое имя индекса, которое не может дублировать имя индекса в результирующей таблице после применения изменений. Ни одно из названий индексов быть не может PRIMARY.

Если вы используете ALTER TABLEдля MyISAMтаблицы, все неуникальные индексы создаются в отдельном пакете (как и в случае REPAIR TABLE). Это должно работать ALTER TABLEнамного быстрее, когда у вас много индексов.

Для MyISAM таблиц обновлением ключей можно управлять явно. Используется ALTER TABLE ... DISABLE KEYSдля указания MySQL прекратить обновление неуникальных индексов. Затем используйте ALTER TABLE ... ENABLE KEYSдля воссоздания недостающих индексов. MyISAMделает это с помощью специального алгоритма, который намного быстрее, чем вставка ключей по одному, поэтому отключение ключей перед выполнением операций массовой вставки должно дать значительное ускорение. Для использования ALTER TABLE ... DISABLE KEYS требуется INDEXпривилегия в дополнение к привилегиям, упомянутым ранее.

Хотя неуникальные индексы отключены, они игнорируются для таких операторов, как SELECTи, EXPLAINкоторые в противном случае использовали бы их.

После ALTER TABLE оператора может потребоваться выполнить ANALYZE TABLE обновление информации о мощности индекса. См. Раздел 13.7.5.22, «Заявление SHOW INDEX» .

Внешние ключи и другие ограничения

FOREIGN KEYИ REFERENCES положения поддерживаются InnoDBи NDBхранения двигателей, которые реализуют . См. Раздел 1.7.3.2, «Ограничения FOREIGN KEY» . Для других механизмов хранения предложения разбираются, но игнорируются. ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (...) REFERENCES ... (...)

Предложение CHECK ограничения анализируется, но игнорируется всеми механизмами хранения. См. Раздел 13.1.18, «Оператор CREATE TABLE» . Причина принятия, но игнорирования предложений синтаксиса заключается в совместимости, для упрощения переноса кода с других серверов SQL и для запуска приложений, которые создают таблицы со ссылками. См. Раздел 1.7.2, «Отличия MySQL от стандартного SQL» .

Ибо ALTER TABLE, в отличие от CREATE TABLE, ADD FOREIGN KEY игнорирует, index_nameесли задано, и использует автоматически сгенерированное имя внешнего ключа. В качестве обходного пути включите CONSTRAINT предложение, чтобы указать имя внешнего ключа:

ADD CONSTRAINT name FOREIGN KEY (....) ...

Сервер MySQL и кластер NDB поддерживают использование ALTER TABLEдля удаления внешних ключей:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

Добавление и удаление внешнего ключа в одном ALTER TABLE операторе поддерживается, ALTER TABLE ... ALGORITHM=INPLACEно не поддерживается ALTER TABLE ... ALGORITHM=COPY.

Сервер запрещает изменения столбцов внешнего ключа, которые могут привести к потере ссылочной целостности. Обходной путь - использовать ALTER TABLE ... DROP FOREIGN KEYперед изменением определения столбца и ALTER TABLE ... ADD FOREIGN KEYпосле него. Примеры запрещенных изменений:

ALTER TABLE tbl_name RENAME new_tbl_nameизменяет генерируемые внутри имена ограничений внешнего ключа и определенные пользователем имена ограничений внешнего ключа, которые начинаются со строки « tbl_name_ibfk_ », чтобы отразить новое имя таблицы. InnoDBинтерпретирует имена ограничений внешнего ключа, которые начинаются со строки « tbl_name_ibfk_ », как имена, сгенерированные внутри.

Изменение набора символов

Для того, чтобы изменить набор символов по умолчанию таблицы и все столбцы символов ( CHAR, VARCHAR, TEXT) в новый набор символов, используйте оператор , как это:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

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

продолжение следует...

Продолжение:

Часть 1 Проблемы ALTER TABLE тормозит база данных Mysql и варианты решения
Часть 2 Параметры таблицы - Проблемы ALTER TABLE тормозит база данных Mysql
Часть 3 Требования к производительности и пространству - Проблемы ALTER TABLE тормозит
Часть 4 Отказ от и импорт табличных пространств InnoDB - Проблемы ALTER