Преобразование таблиц из MyISAM в InnoDB

Задача

Вы решили изменить одну или несколько таблиц из MyISAM на InnoDB. Это должно быть так же просто, как ALTER TABLE foo ENGINE=InnoDB. Но вы слышали, что могут быть некоторые тонкие проблемы.

В этом документе описаны возможные проблемы, которые могут возникнуть, и как с ними справиться.

Рекомендация. Один из способов поиска проблем заключается в выполнении следующих действий (по крайней мере, в *nix):

mysqldump --no-data --all-databases >schemas
egrep 'CREATE|PRIMARY' schemas   # Focusing on PRIMARY KEYs
egrep 'CREATE|FULLTEXT' schemas  # Looking for FULLTEXT indexes
egrep 'CREATE|KEY' schemas       # Looking for various combinations of indexes

Понимание работы индексов поможет вам лучше понять, что может работать быстрее или медленнее в InnoDB.

Проблемы с индексами

(У большинства этих рекомендаций и некоторых фактов есть исключения.)

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

Рекомендация. Найдите таблицы без первичного ключа. Явно укажите первичный ключ, даже если это искусственный AUTO_INCREMENT. Это не абсолютное требование, но это более важное требование для InnoDB, чем для MyISAM. В какой-то момент вам может потребоваться пройтись по таблице; без явного первичного ключа это невозможно.

Факт. Поля первичного ключа включены в каждый вторичный ключ.

• Учитывая это, проверьте наличие избыточных индексов.

PRIMARY KEY(id),
INDEX(b), -- effectively the same as INDEX(b, id)
INDEX(b, id) -- effectively the same as INDEX(b)

• (Сохраните один из индексов, а не оба)

• Обратите внимание на такие тонкие моменты, как

PRIMARY KEY(id),
UNIQUE(b), -- keep for uniqueness constraint
INDEX(b, id) -- DROP this one

• Кроме того, так как первичный ключ и данные сосуществуют:

PRIMARY KEY(id),
INDEX(id, b) -- DROP this one; it adds almost nothing

Различие. Эта функция MyISAM недоступна в InnoDB; значение 'id' будет сбрасываться до 1 для каждого различного значения 'abc':

id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (abc, id)

Способ смоделировать функцию MyISAM может быть таким: То, что вы хотите, но это не сработает, потому что оно ссылается на таблицу дважды:

INSERT INTO foo
    (other, id, ...)
    VALUES
    (123, (SELECT MAX(id)+1 FROM foo WHERE other = 123), ...);

Вместо этого вам нужна какая-то вариация этого. (У вас может уже быть BEGIN...COMMIT.)

BEGIN;
SELECT @id := MAX(id)+1 FROM foo WHERE other = 123 FOR UPDATE;
INSERT INTO foo
    (other, id, ...)
    VALUES
    (123, @id, ...);
COMMIT;

Транзакция обязательна для предотвращения захвата одного и того же id другим потоком.

Рекомендация. Поищите такие первичные ключи. Если вы найдете такие, подумайте, как изменить дизайн. Нет простого обходного решения. Однако следующее может подойти. (Убедитесь, что тип данных для id достаточно большой, так как он не будет сбрасываться):

id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (abc, id),
UNIQUE(id)

Рекомендация. Держите первичный ключ коротким. Если у вас есть вторичные ключи, помните, что они включают поля PK. Длинный PK сделает вторичные ключи громоздкими. Ну, может быть, и нет — если есть много перекрывающихся полей. Пример: PRIMARY KEY(a,b,c), INDEX(c,b,a) — без дополнительной громоздкости.

Рекомендация. Проверьте размеры AUTO_INCREMENT.

• BIGINT почти никогда не нужен. Он тратит как минимум 4 байта на строку (по сравнению с INT).
• Всегда используйте UNSIGNED и NOT NULL.
• MEDIUMINT UNSIGNED (максимум 16 млн) может оказаться достаточным вместо INT
• Будьте пессимистичны — ALTER выполнять больно.

Различие. «Вертикальное разбиение». Это когда вы искусственно разделяете таблицу, чтобы перенести громоздкие столбцы (например, BLOB) в другую параллельную таблицу. Это полезно в MyISAM, чтобы избежать перехода через blob, когда вам не нужно его читать. InnoDB хранит BLOB и TEXT по-другому — 767 байт находятся в записи, остальные — в другом блоке. Поэтому может (или не может) стоить объединить таблицы. Осторожно: запись InnoDB ограничена 8 КБ, а 767 байт учитываются в этом пределе.

Факт. FULLTEXT (до MariaDB 10.0.5) и SPATIAL индексы недоступны в InnoDB. Обратите внимание, что MyISAM и InnoDB FULLTEXT индексы используют разные списки stopword и разные системные переменные.

Рекомендация. Найдите такие индексы. Держите такие таблицы в MyISAM. Еще лучше — сделайте вертикальное разбиение (см. выше), чтобы выделить минимальное количество столбцов из InnoDB.

Факт. Максимальная длина индекса отличается между движками. (Эта модификация, скорее всего, не повлияет на вас, но будьте осторожны.) MyISAM допускает 1000 байт; InnoDB допускает 767 байт, что достаточно для

VARCHAR(255) CHARACTER SET utf8.

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Факт. Первичный ключ включен в данные. Следовательно, SHOW TABLE STATUS покажет и Index_length 0 байт (или 16 КБ) для таблицы без вторичных индексов. В противном случае Index_length — это общий размер для вторичных ключей.

Факт. Первичный ключ включен в данные. Следовательно, точное совпадение по первичному ключу может быть немного быстрее с InnoDB. Кроме того, сканирование по диапазону по первичному ключу, вероятно, будет быстрее.

Факт. Поиск по вторичному ключу проходит по B-дереву вторичного ключа, извлекает первичный ключ, а затем проходит по B-дереву первичного ключа. Следовательно, поиск по вторичному ключу немного более сложен в InnoDB.

Различие. Поля первичного ключа включены в каждый вторичный ключ. Это может привести к «Используется индекс» (в плане EXPLAIN) для InnoDB в тех случаях, когда этого не произошло в MyISAM. (Это незначительное улучшение производительности и компенсирует двойной поиск, который в противном случае необходим.) Однако, когда «Используется индекс» был бы полезен по первичному ключу, MyISAM выполнил бы «сканирование индекса», в то время как InnoDB фактически должен выполнить «сканирование таблицы».

То же, что и в MyISAM. Почти всегда

INDEX(a)   -- DROP this one because the other one handles it.
INDEX(a,b)

Различие. Данные хранятся в порядке первичного ключа. Это означает, что «новые» записи «группируются» вместе в конце. Это может обеспечить лучшую «локальность ссылок», чем в MyISAM.

То же, что и в MyISAM. Оптимизатор почти никогда не использует два индекса в одном операторе SELECT. (5.1 иногда выполняет «слияние индексов».) SELECT в подзапросах и UNION могут независимо выбирать индексы.

Тонкая проблема. При удалении строки идентификатор AUTO_INCREMENT сгорает. То же самое относится к REPLACE, который представляет собой DELETE плюс INSERT.

Очень тонкая проблема. Репликация происходит при COMMIT. Если у вас есть несколько потоков, использующих транзакции, AUTO_INCREMENT могут прийти на сервер-слепок в неправильном порядке. Одна транзакция начинается, захватывает идентификатор. Затем другая транзакция захватывает идентификатор, но завершает её до завершения первой.

То же, что и в MyISAM. Индексация по префиксу обычно плоха как в InnoDB, так и в MyISAM. Пример: INDEX(foo(30))

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

Дисковое пространство для InnoDB, вероятно, будет в 2-3 раза больше, чем для MyISAM.

MyISAM и InnoDB используют оперативную память по-разному. Если вы измените все свои таблицы, вам нужно внести существенные коррективы:

• key_buffer_size — небольшой, но не нулевой; скажем, 10 МБ;
• innodb_buffer_pool_size — 70% доступной оперативной памяти

InnoDB по существу не нуждается в CHECK, OPTIMIZE или ANALYZE. Удалите их из своих скриптов обслуживания. (Никакого вреда, если вы их оставите.)

Скрипты резервного копирования могут потребовать проверки. Таблица MyISAM может быть резервной копией, копируя три файла. В InnoDB это возможно только в том случае, если innodb_file_per_table установлено в 1. До MariaDB 10.0 захват таблицы или базы данных для копирования из производственной среды в среду разработки был невозможен. Используйте mysqldump. С MariaDB 10.0 можно создать горячую копию — см. Обзор резервного копирования и восстановления.

До MariaDB 5.5 параметр DATA DIRECTORY опции таблицы не поддерживался для InnoDB. С MariaDB 5.5 он поддерживается, но только в CREATE TABLE. INDEX DIRECTORY не имеет эффекта, так как InnoDB не использует отдельные файлы для индексов. Для лучшего балансирования нагрузки через несколько дисков можно также изменить пути некоторых файлов журнала InnoDB.

Поймите autocommit и BEGIN/COMMIT.

• (по умолчанию) autocommit = 1: При отсутствии операторов BEGIN или COMMIT каждый оператор является транзакцией сам по себе. Это близко к поведению MyISAM, но не совсем лучшее решение.
• autocommit = 0: COMMIT закроет транзакцию и начнёт новую. Для меня это неуклюже.
• (рекомендуется) BEGIN...COMMIT даёт вам контроль над тем, какая последовательность операций должна рассматриваться как транзакция и «атомарная». Включите оператор ROLLBACK, если вам нужно отменить действия обратно к BEGIN.

В Perl's DBIx::DWIW и Java's JDBC есть API-вызовы для выполнения BEGIN и COMMIT. Они, вероятно, лучше, чем «выполнение» BEGIN и COMMIT.

Тестируйте на наличие ошибок везде! Поскольку InnoDB использует блокировку на уровне строк, он может столкнуться с тупиками, которых вы не ожидаете. Двигатель автоматически вернёт операцию к BEGIN. Обычно восстановление — это повторить, начиная с BEGIN. Обратите внимание, что это веская причина для использования BEGIN.

LOCK/UNLOCK TABLES — удалите их. Замените их (вроде) на BEGIN ... COMMIT. (LOCK будет работать, если innodb_table_locks установлено в 1, но это менее эффективно и может иметь тонкие проблемы.)

В 5.1 ALTER ONLINE TABLE может значительно ускорить некоторые операции. (Обычно ALTER TABLE копирует таблицу и перестраивает индексы.)

«Пределы» практически всего отличаются между MyISAM и InnoDB. Если у вас нет огромных таблиц, широких строк, множества индексов и т. д., вы вряд ли столкнетесь с другими ограничениями.

Смесь MyISAM и InnoDB? Это нормально. Но есть оговорки.

• Настройки оперативной памяти следует соответствующим образом настроить.
• Объединение таблиц разных движков работает.
• Транзакция, которая затрагивает таблицы обоих типов, может отменить изменения InnoDB, но оставит изменения MyISAM.
• Репликация: операторы MyISAM реплицируются при завершении; операторы InnoDB сохраняются до COMMIT.

FIXED (в отличие от DYNAMIC) не имеет смысла в InnoDB.

PARTITION — Вы можете разделить таблицы MyISAM и InnoDB. Помните странное правило: вы должны либо

• не иметь уникальных (или первичных) ключей, или
• иметь значение, по которому вы «разделяете», в каждом уникальном ключе.

Первый вариант не рекомендуется для InnoDB. Второй вариант сложен, если вы хотите AUTO_INCREMENT.

Первичный ключ в PARTITION — Так как каждый ключ должен включать поле, по которому вы разделяете, как может работать AUTO_INCREMENT? Похоже, есть удобный специальный случай:

• Это работает: PRIMARY KEY(autoinc, partition_key)

• Это не работает для InnoDB: PRIMARY KEY(partition_key, autoinc)

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

См. также

Рик Джеймс любезно разрешил нам использовать эту статью в базе знаний.

Сайт Рика Джеймса содержит дополнительные полезные советы, руководства, оптимизации и советы по отладке.

Исходный источник: http://mysql.rjweb.org/doc.php/myisam2innodb