Значение по умолчанию MySQL AUTOCOMMIT=1 может наложить ограничения на производительность загруженного сервера базы данных. В тех случаях, где это практично, объедините несколько связанных операций изменения данных в одну транзакцию, используя оператор SET AUTOCOMMIT=0 или оператор START TRANSACTION, а затем оператор COMMIT после внесения всех изменений.

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

В качестве альтернативы, для транзакций, состоящих только из одного оператора SELECT, включение AUTOCOMMIT помогает InnoDB распознать только-для-чтения транзакции и оптимизировать их. См. Раздел 8.5.3, «Оптимизация только-для-чтения транзакций InnoDB» для требований.

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

Чтобы минимизировать вероятность возникновения этой проблемы:

Чтобы избавиться от неуправляемого отката после его возникновения, увеличьте пул буфера так, чтобы откат стал ограниченным процессором и выполнялся быстро, или остановите сервер и перезапустите его с innodb_force_recovery=3, как описано в Разделе 14.19.2, «Восстановление InnoDB».

Ожидается, что эта проблема будет редка при значении по умолчанию innodb_change_buffering=all, которое позволяет кэшировать операции обновления и удаления в памяти, что делает их выполнение быстрее в первую очередь и также быстрее при необходимости отката. Убедитесь, что вы используете это значение параметра на серверах, которые обрабатывают долговременные транзакции с множеством вставок, обновлений или удалений.

Если вы можете позволить себе потерю некоторых последних коммитированных транзакций в случае непредвиденного завершения работы, вы можете установить параметр innodb_flush_log_at_trx_commit в значение 0. InnoDB пытается записать журнал на диск один раз в секунду, хотя запись не гарантируется. Также установите значение параметра innodb_support_xa в 0, что уменьшает число записей на диск из-за синхронизации данных на диске и двоичного журнала.

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

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

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

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