Время, необходимое для вставки записи, можно грубо разделить на такие промежутки:
Подсоединение: (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
выполнялась еще быстрее. Используйте следующую процедуру:При необходимости создайте таблицу при помощи оператора
CREATE TABLE
(например, используяmysql
илиPerl-DBI
).Выполните оператор
FLUSH TABLES
или команду оболочки:mysqladmin flush-tables
.Используйте
myisamchk --keys-used=0 -rq /path/to/db/tbl_name
. После этого индексы не будут использоваться для данной таблицы.Вставьте данные в таблицу при помощи
LOAD DATA INFILE
. При этом никакие индексы обновляться не будут и, следовательно, скорость будет высокой весьма.Если вы собираетесь в будущем только лишь читать таблицу, выполните
myisampack
для этой таблицы, чтобы уменьшить ее размер. See Раздел 4.7.4, «myisampack
, MySQL-генератор сжатых таблиц (только для чтения)».Воссоздайте индексы при помощи команды
myisamchk -r -q /path/to/db/tbl_name
. Эта процедура создает индексное дерево в памяти, перед тем как записать его на диск, что гораздо быстрее за счет исключения большого количества дисковых операций. Индексное дерево, получившееся в результате, к тому же отлично сбалансировано.Выполните оператор
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, «Настройка параметров сервера».