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

Время, необходимое для вставки записи, можно грубо разделить на такие промежутки:

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

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

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

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

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

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

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

Размер таблицы замедляет вставку индексов в log N раз (B-деревья).

Некоторые способы ускорения вставки:

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

  1. При необходимости создайте таблицу при помощи оператора CREATE TABLE (например, используя mysql или Perl-DBI).

  2. Выполните оператор FLUSH TABLES или команду оболочки: mysqladmin flush-tables.

  3. Используйте myisamchk --keys-used=0 -rq /path/to/db/tbl_name. После этого индексы не будут использоваться для данной таблицы.

  4. Вставьте данные в таблицу при помощи LOAD DATA INFILE. При этом никакие индексы обновляться не будут и, следовательно, скорость будет высокой весьма.

  5. Если вы собираетесь в будущем только лишь читать таблицу, выполните myisampack для этой таблицы, чтобы уменьшить ее размер. See Раздел 4.7.4, «myisampack, MySQL-генератор сжатых таблиц (только для чтения)».

  6. Воссоздайте индексы при помощи команды myisamchk -r -q /path/to/db/tbl_name. Эта процедура создает индексное дерево в памяти, перед тем как записать его на диск, что гораздо быстрее за счет исключения большого количества дисковых операций. Индексное дерево, получившееся в результате, к тому же отлично сбалансировано.

  7. Выполните оператор FLUSH TABLES или команду оболочки: mysqladmin flush-tables.

  Обратите внимание: команда 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.

Чтобы дополнительно повысить скорость выполнения команд LOAD DATA INFILE и INSERT, увеличьте буфер ключа (key buffer). See Раздел 5.5.2, «Настройка параметров сервера».