Вставьте большой файл Excel в базу данных
я использую Носик для чтения файла Excel с более чем 500 000 записей (с 7 столбцами в каждом, не слишком много информации).
Проблема в том, что мой сценарий становится тайм-аутом.
Я попытался загрузить эти ограничения, и они стали лучше, но до сих пор я не мог сделать полную вставку, только частичные, около 50.000 строк.
Это не вариант для меня.
Есть ли способ разбить этот файл Excel, но по коду?
Что я вижу, так это то, что манипулирование файлом, даже если он не вставляется в базу данных, уже происходит медленно и время ожидания истекло
Итак … какой-нибудь совет?
Спасибо!
Решение
Вы можете попробовать позвонить set_time_limit() несколько раз, например, после каждой вставляемой строки. Он сбрасывает лимит времени каждый раз, когда вы звоните. Если администратор вашего сервера установил глобальный лимит времени, это не позволит вам его превысить.
Но вставка полмиллиона строк одна за другой в таблицу InnoDB в MySQL по своей сути медленна, потому что она должна выполнять автокоммит после каждой строки.
Если вы сделаете вставку партиями, вы получите большую скорость. Например, вы, вероятно, делаете что-то вроде этого сейчас:
INSERT INTO table (col1, col2, col3) VALUES (1, 'baker', 'charlie');
INSERT INTO table (col1, col2, col3) VALUES (2, 'delta', 'echo');
INSERT INTO table (col1, col2, col3) VALUES (3, 'foxtrot', 'golf');
INSERT INTO table (col1, col2, col3) VALUES (4, 'hotel', 'india');
INSERT INTO table (col1, col2, col3) VALUES (5, 'lima', 'mike');
Вместо этого сделайте это:
INSERT INTO table (col1, col2, col3) VALUES
(1, 'baker', 'charlie'),
(2, 'delta', 'echo'),
(3, 'foxtrot', 'golf'),
(4, 'hotel', 'india'),
(5, 'lima', 'mike');
Таким образом, вы будете нести издержки на коммит на MySQL для каждых пяти строк, а не для каждой. Обратите внимание, что в одну INSERT можно поместить много строк, а не только пять. Единственный предел MySQL для длины запроса может быть найден с SHOW VARIABLES LIKE 'max_allowed_packet';.
Конечно, это немного сложнее для программирования, но это много Быстрее.
Другие решения
Чтение файла с 3 500 000 ячеек не будет быстрым, несмотря ни на что. Это займет не менее минуты, если он работает на мощном оборудовании и если файл Excel использует встроенные строки.
Итак, вот варианты у вас есть:
- Если вы управляете созданием файла Excel, который читаете, убедитесь, что в нем используются встроенные строки (это стандартное поведение, если вы используете Spout). Это ускорит чтение драматично. Это связано с медлительностью, о которой вы упоминали, даже если вы читаете только первые две строки. Когда не используются встроенные строки, Spout должен предварительно обработать файл, содержащий значения ячеек, так как на них ссылаются в другом месте. С помощью встроенных строк Spout может пропустить этот дорогостоящий шаг и выполнять потоковую передачу.
- Увеличьте ограничение по времени, чтобы оставить больше времени вашему скрипту для завершения его обработки (set_time_limit)
- Пакетные вставки вашей БД: вместо того, чтобы вставлять строки одну за другой, вы должны вставить их 1000 на 1000 (или больше). Каждое соединение с БД занимает некоторое время, поэтому ограничение их является хорошей идеей.
Разделение файла может сработать, но это нужно сделать заранее (не в том же сценарии, иначе это просто добавит время к общему времени обработки …).
Надеюсь, это поможет!