Справочное руководство по MySQL 4.0. :: 5 Оптимизация в MySQL :: 5.2 Оптимизация SELECT и других запросов :: 5.2.9 Скорость выполнения запросов INSERT

Подсоединение: (3)

Посылка запроса на сервер: (2)

Синтаксический анализ запроса: (2)

Вставка записи: (1 * размер записи)

Вставка индексов: (1 * число индексов)

Закрытие: (1)

Если с одного клиента одновременно вставляется большое количество строк, используйте операторы INSERT в форме, содержащей множество записей. При этом вставка будет происходить намного быстрее (в некоторых случаях в несколько раз), чем при использовании отдельных операторов INSERT. При добавлении данных в непустую таблицу можно настроить переменную bulk_insert_buffer_size так, чтобы это делалось еще быстрее. See Раздел 4.5.6.4, «SHOW VARIABLES».

При вставке нескольких строк с различных клиентов можно повысить скорость, используя оператор INSERT DELAYED. See Раздел 6.4.3, «Синтаксис оператора INSERT».

Обратите внимание: при использовании таблиц MyISAM можно вставлять строки во время выполнения операторов SELECT, если в таблицах нет удаленных строк.

При загрузке таблицы из текстового файла используйте команду LOAD DATA INFILE. При этом обычно вставка будет происходить в 20 раз быстрее, чем при использовании соответствующего количества операторов INSERT. See Раздел 6.4.9, «Синтаксис оператора LOAD DATA INFILE».

Если таблица имеет много индексов, можно проделать некоторую дополнительную работу, чтобы команда LOAD DATA INFILE выполнялась еще быстрее. Используйте следующую процедуру:

Обратите внимание: команда LOAD DATA INFILE также выполняет вышеупомянутую оптимизацию при вставках в пустую таблицу. Главное отличие этой команды от вышеупомянутой процедуры заключается в том, что при помощи myisamchk можно выделить намного больше временной памяти для создания индекса, чем MySQL, по вашему мнению, должен выделять для каждого воссоздания индексов. Начиная с MySQL 4.0 можно также использовать команду ALTER TABLE tbl_name DISABLE KEYS вместо myisamchk --keys-used=0 -rq /path/to/db/tbl_name и ALTER TABLE tbl_name ENABLE KEYS вместо myisamchk -r -q /path/to/db/tbl_name. Таким образом можно также пропускать шаги FLUSH TABLES.

Можно ускорять операции вставки, выполняемые несколькими операторами, путем установки блокировки таблиц:

mysql> LOCK TABLES a WRITE;
mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33);
mysql> INSERT INTO a VALUES (8,26),(6,29);
mysql> UNLOCK TABLES;

Главный фактор, влияющий на скорость, - то, что буфер индексов сбрасывается на диск только один раз, после завершения всех операторов INSERT. Обычно содержимое индексных буферов сбрасывалось бы на диск столько раз, сколько имеется различных операторов INSERT. Блокировка не нужна, если можно вставить все строки при помощи одного оператора. Для транзакционных таблиц, чтобы повысить скорость, следует использовать BEGIN/COMMIT вместо LOCK TABLES. Блокировка также понизит полное время проверки подсоединений (multi-connection tests), но максимальное время ожидания для некоторых потоков повысится (потому что они ожидают снятия блокировки). Например:

поток 1 делает 1000 вставок
потоки 2, 3 и 4 делают 1 вставку
поток 5 делает 1000 вставок

Если блокировка не используется, 2, 3, и 4 завершат выполнение раньше, чем 1 и 5. Если блокировка используется, 2, 3 и 4, видимо, не закончат выполнение раньше, чем 1 или 5, но общее время должно приблизительно уменьшиться на 40%. Так как в MySQL операции INSERT, UPDATE и DELETE очень быстрые, общая производительность будет улучшаться, если добавлять блокировки ко всем командам, делающим более 5 вставок или обновлений подряд. Если делается очень много вставок строк, можно время от времени сопровождать команду LOCK TABLES командой UNLOCK TABLES (после каждых 1000 строк), чтобы позволить другим потокам обращаться к таблице. Результатом всего этого будет получение хорошей производительности. Конечно, для загрузки данных намного более быстрой является команда LOAD DATA INFILE.