Как лучше всего добиться быстрой вставки больших объемов данных в MySQL?
Я написал программу на C для анализа больших файлов XML, а затем создания файлов с помощью операторов вставки. Какой-то другой процесс будет загружать файлы в базу данных MySQL. Эти данные будут служить службой индексации, чтобы пользователи могли легко находить документы.
Я выбрал InnoDB из-за возможности блокировки на уровне строк. Программа на C будет генерировать от 500 до 5 миллионов операторов вставки при каждом вызове.
Как лучше всего поместить все эти данные в базу данных как можно быстрее? Еще следует отметить, что БД находится на отдельном сервере. Стоит ли перемещать файлы на этот сервер для ускорения вставки?
Обновлено: эта таблица на самом деле не будет обновляться, но строки будут удалены.
Ответы 6
Я бы сделал по крайней мере следующие вещи согласно эта ссылка:
- Переместите файлы туда и подключитесь через сокет unix
- Создайте вместо INSERTS файл ЗАГРУЗИТЬ ДАННЫЕ В ФАЙЛ
- Отключение индексов во время загрузки
- Используйте инструмент mysqlimport или команду ЗАГРУЗИТЬ ДАННЫЕ INFILE.
- Временно отключите индексы, которые вам не нужны для целостности данных
MySQL со стандартными форматами таблиц работает удивительно быстро, пока это таблица только для записи; Итак, первый вопрос: собираетесь ли вы обновлять или удалять. Если нет, не используйте innosys - блокировка не требуется, если вы просто добавляете. Вы можете периодически обрезать или переименовывать выходной файл, чтобы справиться с размером таблицы.
1. Убедитесь, что вы используете транзакцию.
Сделки устраняют
ВСТАВИТЬ, СИНХРОНИЗАЦИЯ К ДИСКУ
фаза повторения, и вместо этого весь дисковый ввод-вывод выполняется, когда вы ЗАКЛЮЧАЕТ транзакцию.
2. Обязательно используйте сжатие соединения.
Необработанный текст + сжатый поток GZip ~ = в некоторых случаях экономия полосы пропускания до 90%.
3. По возможности используйте обозначение параллельной вставки.
INSERT INTO TableName(Col1,Col2) VALUES (1,1),(1,2),(1,3)
(Меньше текста для отправки, более короткое действие)
Вы серьезно думаете, что это быстрее, чем ЗАГРУЗИТЬ ДАННЫЕ?
хех, вероятно, нет, но если ЗАГРУЗИТЬ ДАННЫЕ не было вариантом, остальное имеет смысл.
Если вы не можете использовать LOAD DATA INFILE, как предлагали другие, используйте подготовленные запросы для вставок.
Действительно зависит от двигателя. Если вы используете InnoDB, действительно используйте транзакции (вы не можете их избежать - но если вы используете autocommit, каждый пакет неявно находится в своем собственном txn), но убедитесь, что они не слишком велики или слишком малы.
Если вы используете MyISAM, транзакции бессмысленны. Вы можете добиться большей скорости вставки, отключив и включив индексы, но это хорошо только для пустой таблицы.
Обычно лучше всего начинать с пустой таблицы.
LOAD DATA - победитель в любом случае.
Если это таблица только для записи, и его можно уговорить использовать стандартный формат MySQL, это все накладные расходы. Особенно накладные расходы на транзакции, которые вам не нужны.