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

На загруженном сервере выполните бенчмаркинг с выключенным кэшем запросов. InnoDB буфер пула предоставляет аналогичные преимущества, поэтому кэш запросов может излишне занимать память. Для получения информации о кэше запросов см. Раздел 8.10.3, «Кэш запросов MySQL».

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

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

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

Для получения дополнительной информации см. Раздел 14.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.

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

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

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

В ходе начальных экспериментов наиболее важным параметром является innodb_file_per_table. При включённом значении, которое является значением по умолчанию начиная с MySQL 5.6.6, новые таблицы InnoDB неявно создаются в табличных пространствах. В отличие от системного табличного пространства InnoDB, табличные пространства «файл на таблицу» позволяют операционной системе возвращать дисковое пространство при усечении или удалении таблицы. Табличные пространства «файл на таблицу» также поддерживают формат файла и связанные с ним функции, такие как сжатие таблиц, эффективное хранение вне страницы для длинных столбцов переменной длины и больших префиксов индексов. Дополнительную информацию см. в Разделе 14.6.3.2 «File-Per-Table Tablespaces».

Также вы можете хранить таблицы InnoDB в общем табличном пространстве. Общие табличные пространства поддерживают формат файла Barracuda и могут содержать несколько таблиц. Дополнительную информацию см. в Разделе 14.6.3.3 «General Tablespaces».

Для преобразования таблицы, не использующей 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 раз больше времени, чем вставка.

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

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

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

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

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

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

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

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

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

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