При переходе от таблиц 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 и 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.