Оптимизация массивных операций MySQL INSERT

Большинство баз данных имеют оптимизированный процесс массовой вставки — MySQL — это ФАЙЛ ЗАГРУЗКИ ДАННЫХ. синтаксис.

Чтобы загрузить файл CSV, используйте:

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

Вставьте несколько значений вместо того, чтобы делать

insert into table values(1,2);

do

insert into table values (1,2),(2,3),(4,5);

До соответствующего количества строк за раз.

Или выполните массовый импорт, который является наиболее эффективным способом загрузки данных, см.

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Обычно я бы сказал, просто используйте LOAD DATA INFILE, но, похоже, вы не можете этого сделать в своей среде общего хостинга.

Я не использовал MySQL несколько лет, но у них есть очень хороший документ, в котором описывается, как ускорить вставку для массовых вставок: http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

Несколько идей, которые можно почерпнуть из этого:

• Отключить/включить клавиши вокруг вставок:

  ALTER TABLE tbl_name DISABLE KEYS; ALTER TABLE tbl_name ENABLE KEYS;

• Используйте много значений в ваших операторах вставки.

  То есть: INSERT INTO table (col1, col2) VALUES (val1, val2),(.., ..),...

  Если я правильно помню, у вас может быть до 4096 значений на оператор вставки.

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

Думаю, это ускорит дело. Я бы предложил использовать LOCK TABLES, но я думаю, что отключение ключей делает это спорным.

ОБНОВЛЕНИЕ

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

• Убедитесь, что в вашей таблице нет данных, которые «конфликтуют» с загружаемыми новыми данными (если вы начинаете с нуля, здесь будет полезен оператор TRUNCATE).
• Написание сценария для очистки ваших входных данных, чтобы исключить дублирование локально. Проверка дубликатов, вероятно, в любом случае будет стоить вам много времени в базе данных.
• Если вы сделаете это, ENABLE KEYS не должен дать сбой.

Вы можете создать скрипт cronjob, который добавляет x записей в базу данных по одному запросу. Скрипт Cronjob проверит, не добавил ли последний импорт всех необходимых строк, он возьмет еще x строк.

Таким образом, вы можете добавить столько строк, сколько вам нужно.

Если у вас есть свой выделенный сервер, это проще. Вы просто запускаете цикл со всеми запросами на вставку.

Конечно, вы можете попробовать установить для time_limit значение 0 (если оно работает на DreamHost) или увеличить его.

Ваш PHP-скрипт, скорее всего, завершается, потому что он превысил лимит времени скрипта. Поскольку вы находитесь на общем хосте, вам не повезло.

Если вы переключитесь на выделенный сервер и получите доступ к оболочке, лучшим способом будет использовать инструмент командной строки mysql для вставки данных.

Предложение OMG Ponies отличное, но я также «вручную» отформатировал данные в тот же формат, который использует mysqldump, а затем загрузил их таким образом. Очень быстро.

Вы пробовали делать транзакции? Просто отправьте команду BEGIN в MySQL, сделайте все вставки, затем выполните COMMIT. Это значительно ускорит его, но, как сказал Касабланка, ваш скрипт, вероятно, тоже истекает.

Я сам сталкивался с этой проблемой раньше, и никто почти не понял ее правильно, но вам нужно сделать немного больше, чтобы она работала лучше всего.

Я обнаружил, что в моей ситуации я не мог MySQL принять один большой оператор INSERT, но обнаружил, что если я разделю его на группы примерно по 10 000 INSERT за раз, как это было предложено nos, то он сделает свою работу довольно быстро. Следует отметить, что при выполнении нескольких INSERT, подобных этому, вы, скорее всего, достигнете ограничения времени ожидания PHP, но этого можно избежать, сбросив время ожидания с помощью set_time_limit($seconds), я обнаружил, что делая это после каждого успешного INSERT работал очень хорошо.

Вы должны быть осторожны при этом, потому что вы можете случайно попасть в цикл с неограниченным тайм-аутом, и для этого я бы предложил проверить, чтобы убедиться, что каждый INSERT был успешным, либо проверив ошибки, о которых сообщает MySQL, с помощью mysql_errno() или mysql_error(). Вы также можете отлавливать ошибки, проверяя количество строк, затронутых INSERT, с помощью mysql_affected_rows(). Затем вы можете остановиться после первой ошибки.

Было бы лучше, если бы вы использовали sqlloader. Вам понадобятся две вещи: первый управляющий файл, в котором указаны действия, которые должен выполнять загрузчик SQL, и второй файл csv, который вы хотите загрузить. Вот ссылка ниже, которая поможет вам. http://www.oracle-dba-online.com/sql_loader.htm

Перейдите в phpmyadmin и выберите таблицу, в которую вы хотите вставить.

На вкладке «Операции», а затем в разделе «Параметры таблицы» измените механизм хранения с InnoDB на MyISAM.

У меня когда-то была похожая задача. Хорошо тебе провести время.