При переходе от MyISAM таблиц, уменьшите значение параметра конфигурации key_buffer_size для освобождения памяти, больше не необходимой для кэширования результатов. Увеличьте значение параметра конфигурации innodb_buffer_pool_size, который выполняет аналогичную роль, выделяя кеш-память для InnoDB таблиц. InnoDB кэширует данные таблиц и индексы, ускоряя поиск по запросам и сохраняя результаты запросов в памяти для повторного использования. Для получения рекомендаций по настройке размера буфера пула, обратитесь к Разделу 10.12.3.1, «Как MySQL использует память».

Поскольку таблицы MyISAM не поддерживают , вы, возможно, не уделяли много внимания параметру конфигурации autocommit и операторам COMMIT и ROLLBACK. Эти ключевые слова важны для одновременного чтения и записи в таблицы InnoDB несколькими сеансами, обеспечивая существенные преимущества масштабируемости при работе с интенсивными операциями записи.

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

Представленные советы экономят память и дисковое пространство, которое может быть потрачено во время слишком долгих транзакций. Когда транзакции короче, чем нужно, проблема заключается в избыточном вводе/выводе. При каждом COMMIT, MySQL гарантирует, что каждое изменение безопасно записано на диск, что предполагает некоторый ввод/вывод.

Дополнительную информацию см. в Разделе 17.7.2.2, «autocommit, Commit, and Rollback».

В журнале ошибок MySQL или выводе SHOW ENGINE INNODB STATUS могут появляться сообщения об ошибках, относящиеся к «тупикам». Тупик — это несерьёзная проблема для таблиц InnoDB, и зачастую не требует никаких корректирующих действий. Когда две транзакции начинают изменять несколько таблиц, обращаясь к ним в разном порядке, они могут достичь состояния, где каждая транзакция ожидает другую, и ни одна из них не может продолжить выполнение. Если обнаружение тупиков включено (по умолчанию), MySQL немедленно определяет это условие и отменяет («уменьшает») транзакцию, позволяя другой транзакции продолжить выполнение. Если обнаружение тупиков отключено с помощью параметра конфигурации innodb_deadlock_detect, то InnoDB полагается на настройку innodb_lock_wait_timeout для отката транзакций в случае тупика.

В любом случае, ваши приложения должны иметь логику обработки ошибок, чтобы перезапустить транзакцию, которая была принудительно отменена из-за тупика. При повторном выполнении тех же SQL-запросов, что и раньше, проблема временной зависимости уже не актуальна. Либо другая транзакция уже завершилась, и ваша может продолжить работу, либо другая транзакция всё ещё выполняется, и ваша ждёт её завершения.

Если сообщения об ошибках тупика появляются постоянно, вам следует пересмотреть код приложения, чтобы переупорядочить SQL-операции последовательным образом или сократить транзакции. Вы можете протестировать, включив параметр innodb_print_all_deadlocks, чтобы увидеть все предупреждения о тупиках в журнале ошибок MySQL, а не только последнее предупреждение в выводе SHOW ENGINE INNODB STATUS.

Дополнительную информацию см. в Разделе 17.7.5, «Тупики в InnoDB».

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

При преобразовании больших, часто используемых таблиц MyISAM, содержащих важные данные, изучите и рассмотрите переменные innodb_file_per_table и innodb_page_size, а также ROW_FORMAT и KEY_BLOCK_SIZE в операторе CREATE TABLE.

Во время начальных экспериментов наиболее важным параметром является innodb_file_per_table. При включенном значении, что является стандартным по умолчанию, новые таблицы InnoDB неявно создаются в пространствах имен таблиц. В отличие от системного пространства имен таблиц InnoDB, пространства имен таблиц с отдельным файлом для каждой таблицы позволяют операционной системе восстанавливать дисковое пространство при усечении или удалении таблицы. Пространства имен таблиц с отдельным файлом для каждой таблицы также поддерживают форматы строк и и связанные с ними функции, такие как сжатие таблиц, эффективное хранение вне страницы для длинных столбцов переменной длины и большие префиксы индексов. Более подробную информацию см. в Разделе 17.6.3.2, «Пространства имен таблиц с отдельным файлом для каждой таблицы».

Также можно хранить таблицы InnoDB в общем общем пространстве имен таблиц, которое поддерживает несколько таблиц и все форматы строк. Более подробную информацию см. в Разделе 17.6.3.3, «Общие пространства имен таблиц».

Для преобразования не-InnoDB таблицы для использования InnoDB используйте ALTER TABLE:

ALTER TABLE table_name ENGINE=InnoDB;

Вы можете создать таблицу InnoDB, которая является клоном таблицы MyISAM, вместо использования ALTER TABLE для выполнения преобразования, чтобы протестировать старую и новую таблицу бок о бок перед переключением.

Создайте пустую таблицу InnoDB с идентичными определениями столбцов и индексов. Используйте SHOW CREATE TABLE table_name\G, чтобы увидеть полное CREATE TABLE утверждение для использования. Измените предложение ENGINE на ENGINE=INNODB.

Для переноса большого объема данных в пустую таблицу InnoDB, созданную, как показано в предыдущем разделе, вставьте строки с помощью INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns.

Вы также можете создать индексы для таблицы InnoDB после вставки данных. Раньше создание новых вторичных индексов было медленной операцией для InnoDB, но теперь вы можете создать индексы после загрузки данных с относительно небольшими накладными расходами от этапа создания индексов.

Если у вас есть UNIQUE ограничения на вторичные ключи, вы можете ускорить импорт таблицы, временно отключив проверки уникальности во время операции импорта:

SET unique_checks=0;
... import operation ...
SET unique_checks=1;

Для больших таблиц это экономит ввод-вывод с диска, потому что InnoDB может использовать его, чтобы записывать записи вторичных индексов как пакет. Убедитесь, что данные не содержат дублирующих ключей. unique_checks разрешает, но не требует, чтобы движки хранения игнорировали дублирующие ключи.

Для лучшего контроля над процессом вставки вы можете вставлять большие таблицы частями:

INSERT INTO newtable SELECT * FROM oldtable
   WHERE yourkey > something AND yourkey <= somethingelse;

После вставки всех записей вы можете переименовать таблицы.

Во время преобразования больших таблиц увеличьте размер кэша буфера InnoDB, чтобы уменьшить ввод-вывод с диска. Обычно рекомендуемый размер буфера кэша составляет от 50 до 75 процентов оперативной памяти. Вы также можете увеличить размер файлов журнала InnoDB.

Если вы намерены сделать несколько временных копий данных в таблицах InnoDB во время процесса преобразования, рекомендуется создавать таблицы в пространствах имен таблиц с отдельным файлом для каждой таблицы, чтобы вы могли восстанавливать дисковое пространство при удалении таблиц. Когда параметр конфигурации innodb_file_per_table включен (по умолчанию), вновь созданные таблицы InnoDB неявно создаются в пространствах имен таблиц с отдельным файлом для каждой таблицы.

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

В случае бесконечного отката, если у вас нет ценных данных в вашей базе данных, может быть целесообразно убить процесс базы данных, а не ждать миллионов операций ввода-вывода с диска для завершения. Полная процедура описана в Разделе 17.20.3, «Вынужденное восстановление InnoDB».

Предложение PRIMARY KEY является критически важным фактором, влияющим на производительность запросов MySQL и использование места для таблиц и индексов. Первичный ключ однозначно идентифицирует строку в таблице. Каждая строка в таблице должна иметь значение первичного ключа, и две строки не могут иметь одинаковое значение первичного ключа.

Ниже приведены рекомендации по первичному ключу, за которыми следуют более подробные объяснения.

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

Если у таблицы уже есть первичный ключ в более длинном столбце, например, в VARCHAR, рассмотрите возможность добавления нового беззнакового столбца AUTO_INCREMENT и переключения первичного ключа на него, даже если этот столбец не упоминается в запросах. Эта модификация дизайна может привести к значительной экономии места во вторичных индексах. Вы можете обозначить бывшие столбцы первичного ключа как UNIQUE NOT NULL, чтобы применить такие же ограничения, как и в предложении PRIMARY KEY, то есть запретить дубликаты или нулевые значения по всем этим столбцам.

Если вы распространяете связанную информацию по нескольким таблицам, каждая таблица обычно использует один и тот же столбец для своего первичного ключа. Например, база данных персонала может иметь несколько таблиц, каждая из которых имеет первичный ключ — номер сотрудника. База данных продаж может иметь некоторые таблицы с первичным ключом — номер клиента, и другие таблицы с первичным ключом — номер заказа. Поскольку поиск по первичному ключу очень быстрый, можно создать эффективные запросы соединения для таких таблиц.

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

Особенности надёжности и масштабируемости InnoDB требуют большего объема дискового пространства, чем аналогичные таблицы MyISAM. Вы можете немного изменить определения столбцов и индексов для лучшего использования пространства, уменьшения операций ввода-вывода и потребления памяти при обработке наборов результатов, а также для создания более эффективных планов оптимизации запросов, обеспечивающих эффективное использование поисков по индексу.

Если вы настраиваете числовой столбец ID в качестве первичного ключа, используйте это значение для кросс-ссылок на связанные значения в других таблицах, особенно для запросов. Например, вместо того, чтобы принимать имя страны в качестве входных данных и выполнять запросы, ищущие то же самое имя, выполните один поиск, чтобы определить идентификатор страны, а затем выполните другие запросы (или один объединяющий запрос) для поиска соответствующей информации по нескольким таблицам. Вместо того, чтобы хранить номер клиента или товара каталога в виде строки цифр, потенциально занимая несколько байтов, преобразуйте его в числовой идентификатор для хранения и выполнения запросов. Столбец с 4-байтовым беззнаковым INT может индексировать более 4 миллиардов элементов (по американскому пониманию миллиарда: 1000 миллионов). Диапазоны различных целочисленных типов приведены в разделе 13.1.2 «Целочисленные типы (точное значение) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT».

Файлы InnoDB требуют большего внимания и планирования, чем файлы MyISAM.