MySQL / 8. Изменение таблицы с помощью предложения ALTER TABLE

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

Задача
Просмотр таблицы выполняется слишком медленно. Или вставка и обновление записей требуют слишком много времени.

Решение
Предложение ALTER TABLE умеет удалять и добавлять не только столбцы, но и индексы для этих столбцов. Подобные операции часто улучшают производительность базы данных. Обычно индексирование часто используемого столбца ускоряет выполнение предложений SELECT за счет отсутствия необходимости полного просмотра таблиц. В некоторых случаях пользу может принести и удаление индекса. При любом обновлении строки MySQL приходится обновлять все индексы, содержащие измененные столбцы. Если вы редкоиспользуете какой-то индекс, это может свидетельствовать о том, что таблица перегружена индексами, и удаление какого-то из них может повысить эффективность обработки таблицы.

Обсуждение
Для удобства работы начнем с создания нового экземпляра тестовой таблицы mytbl. Используем предложения DROP TABLE и CREATE TABLE для удаления существующей версии и воссоздания таблицы в ее первоначальной форме:

DROP TABLE mytbl;
CREATE TABLE mytbl
(
i INT,
c CHAR(1)
);

В начале главы мы применяли SHOW COLUMNS для наблюдения за результатами изменения таблицы. Теперь будем исследовать изменения индекса и выводить результаты при помощи SHOW INDEX, а не SHOW COLUMNS. В настоящий момент в таблице нет индексов, так как они не были указаны в предложении CREATE TABLE:

mysql> SHOW INDEX FROM mytbl;
Empty set (0.00 sec)

Добавление индексов
Существует четыре типа предложений, добавляющих индексы в таблицу:

ALTER TABLE имя_таблицы ADD PRIMARY KEY (список_столбцов);
ALTER TABLE имя_таблицы ADD UNIQUE имя_индекса (список_столбцов);
ALTER TABLE имя_таблицы ADD INDEX имя_индекса (список_столбцов);
ALTER TABLE имя_таблицы ADD FULLTEXT имя_индекса (список_столбцов);

Первое предложение добавляет первичный ключ (PRIMARY KEY), то есть индексированные значения должны быть уникальными и не содержать NULL. Второе предложение создает индекс, для которого значения должны быть уникальными (за исключением значений NULL, которые могут встречаться многократно). Третье предложение добавляет обычный индекс, в котором любое значение может появляться несколько раз. Последнее же создает специальный индекс FULLTEXT, который используется для просмотра текста.

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

Столбцы для индексирования указываются в параметре список_столбцов – списке из одного или нескольких имен столбцов, разделенных запятыми.

Рассмотрим два простых примера: первый создает одностолбцовый индекс для c, а второй – многостолбцовый индекс, включающий c и i:

ALTER TABLE mytbl ADD INDEX (c);
ALTER TABLE mytbl ADD INDEX (c,i);

Во многих случаях индексируемые столбцы должны быть объявлены как не-NULL. Например, если вы создадите mytbl как таблицу типа ISAM, то приведенные выше предложения ADD INDEX не выполнятся, так как таблицы ISAM не допускают NULL ни в каких типах индексов. Кроме того, индексы типа PRIMARY KEY не могут содержать значения NULL вне зависимости от типа таблицы. Если вы пытаетесь добавить индекс, а MySQL жалуется на проблемы, связанные с NULL, используйте предложение ALTER TABLE для изменения соответствующего столбца (столбцов) на не-NULL и повторите попытку создания индекса. Например, если попробовать сделать первичным ключом столбец i, возникнет ошибка:

mysql> ALTER TABLE mytbl ADD PRIMARY KEY (i);
ERROR 1171 at line 5: All parts of a PRIMARY KEY must be NOT NULL;
If you need NULL in a key, use UNIQUE instead

Необходимо предварительно переопределить столбец i так, чтобы он не допускал использования NULL:

mysql> ALTER TABLE mytbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE mytbl ADD PRIMARY KEY (i);

Все получилось. А в первом случае, как видно из сообщения об ошибке, вместо первичного ключа можно было бы создать индекс UNIQUE в случае необходимости присутствия в индексе значений NULL.Удаление индексов Чтобы удалить индекс, используйте одно из предложений:

ALTER TABLE имя_таблицы DROP PRIMARY KEY;
ALTER TABLE имя_таблицы DROP INDEX имя_индекса;

Проще всего удалить индекс PRIMARY KEY, так как не нужно знать имя индекса:

ALTER TABLE mytbl DROP PRIMARY KEY;

Чтобы удалить индекс, не являющийся первичным ключом, необходимо указать его имя. Если вы не знаете, как называется индекс, используйте SHOW INDEX. Во избежание вывода чересчур длинных строк используем вертикальный вывод (\G):

mysql> SHOW INDEX FROM mytbl\G
*************************** 1. row ***************************
Table: mytbl
Non_unique: 1
Key_name: c
Seq_in_index: 1
Column_name: c
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Comment:
*************************** 2. row ***************************
Table: mytblNon_unique: 1
Key_name: c_2
Seq_in_index: 1
Column_name: c
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Comment:
*************************** 3. row ***************************
Table: mytbl
Non_unique: 1
Key_name: c_2
Seq_in_index: 2
Column_name: i
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Comment:

Значения Key_name и Seq_in_index соответствуют именам индексов и позициям столбцов в индексе. Теперь вы знаете, что в таблице mytbl есть одностолбцовый индекс с именем c и многостолбцовый индекс с именем c_2 (эти имена выбраны MySQL для двух созданных нами ранее индексов). Предложение, удаляющее индексы, будет таким:

ALTER TABLE mytbl DROP INDEX c, DROP INDEX c_2;

Как видите, в одном предложении ALTER TABLE можно выполнить несколько операций, которые необходимо разделять запятыми.

Статьи по MySQL на эту тему:

Использование предложения ALTER TABLE для нормализации таблицы
Удаление дубликатов путем добавления индекса