Как выгрузить в Excel документ 200 000 записей из бд?

Question

[Twitt]

Работаю с PHPExcel библиотекой, стоит задача выгрузить из таблицы в БД 200к записей в xlsx файл. Как сделать это лучше всего? Потому что если за раз, то выдается ошибка, мол беда с памятью. Я думал может выгружать лимитами (по 30к допустим), тогда еще рождается пара вопросов: как это сделать? (сначала 30 000 записей, потом с 30 001 по 60 000 и т.д.)? И второй вопрос, там они делятся на категории, то есть в этот лимит 30к может попасть еще другая категория какая должна быть в другом лимите, как этого избежать? Проще говоря, как в лимит брать только то, что находится в нужной категории допустим, а как оно выгрузит с той категории, перейти на другую чтоб сначала полностью ее выгрузило, а потом опять же начало выгружать третью категорию и тд.

Comments

[alex-1917]

хостинг-то какой? небось 100рублевый?)))

[alex-1917]

Твоя главная ОШИБКА - неправильно составлена ЛОГИКА приложения! Тяжелой работой по вычислениям - что куда и с сколько - пусть делает php! Все что рутинное и где не надо ничего считать или фильтровать - отдай MYSQL! Т.е. еще раз говорю - выгружай в массив ВСЮ таблицу, это поверь секунды займет, если тупой селект без order и where!!! Не заставляй MYSQL делать сложные работы, она начинает сама делать по кускам вот тут память ейная и выжирается! Отдай массив php-скрипту - там спокойно сортируй, для PHP всяко больше и памяти и процессора будет выделено. Ну или беги на выделенный сервак!!!

Answer 1

[alex-1917]

После тестирования порядка 12 разных программулин я нашел для себя единственный вариант - MySQL-Front
И все эти лимиты не нужны будут, он сам пакеты формирует, главное не паниковать и сидеть молча ждать, иной раз у меня минут по 10 крутилось колесико и в итоге все пучком.
Беда с памятью тогда может быть только у вашего компьютера, если есть хотя бы 8Гб, то все ок
А вы пытаетесь выгружать силами хостинга? Тогда переведите на самый мощный тариф и пробуйте, но думаю не вариант.

Что такое 200к записей?

• Запись - это одно слово?
  
• Или это текстовые блоки по 5000 символов?
  
• Или это куски html кода?
  
• Сколько колонок в каждой записи?
  
• Размер в кБ какой у таблицы???
  

Да, кстати, вытаскивал по 45-65 млн записей, каждая запись - это 12 колонок, в трех текста, в остальных числовые значения

Ну и конечно должна быть возможность подключения к БД с внешнего ip, если не совсем захудалый хостинг, то должна быть такая галка.

Comments

[alex-1917]

Только скачивай у немцев (.de), все остальное или с вирусом или старые версии

[alex-1917]

и про PHPExcel можно будет забыть, в той программмке есть конструктор запросов, твори что хошь))))

[Twitt]

alex-1917, не на хостинге, прямо на ПК это всё происходит
8 гб ОЗУ есть, но все равно когда получается хотя бы 150к записей, уже проблема с памятью да есть. работать надо с PHPExcel (не моя прихоть)
мне нужно сделать такую выборку, чтобы выбралось 8 колонок из БД, в которых где то есть ссылка, где то одно слово и т.д.

[Twitt]

т.е. мне надо сделать селект типо такой:
select url, name, surname, ... from table1 where category in (cat1,cat2,cat3);
Вот сюда приходят все записи какие лежат в этих категориях. Но я понимаю, что скорее всего этот запрос надо по умному доработать, по этому обращаюсь за помощью

[alex-1917]

Twitt, семен семеныч, это отдельный вопрос. вопрос был как выгрузить - выгружай MySQL-Front. Как сделать поиск по базе со своими параметрами - совсем другой коленкор, как минимум для этого используется LIMIT, если надо выводить на экран или тем более такому монстру отдавать как phpexcel! Подсказка- выгружай в массив и дальше с массивом работай, а как ты хочешь - на ходу да сразу в обработку сторонним php-скриптом - вот тебе и перегруз памяти!
тебе надо запрос доработать??? А чо ж сразу в вопросе не поставить это? молодец.

[Twitt]

alex-1917, да, мне нужно запрос, потому что я банально не понимаю как его сделать. мне сказали что есть два варианта, либо джойнить 3 раза одну и ту же таблицу (но я не понимаю нахрен надо) либо как то доработать запрос:
select url, name, surname, ... from table1 where category in (cat1,cat2,cat3);

И если ты поможешь, я буду нереально благодарен

Answer 2

[Dmitry Bay]

Сочувствую вам. Задача в целом не сложная. Но будете спотыкаться о мелочь, по типу памяти.
Из своего опыта, мне удавалось выгружать 80К строк товаров в ИМ.

1) Используйте кэширование отличное от стандартного в памяти.

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);

2) Используйте offset в БД чтобы не загружать в память сразу все строчки.
3) пробуйте выгружать по большим категориям небольшими порциями.

Comments

[Twitt]

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

[Twitt]

т.е. мне надо сделать селект типо такой:
select url, name, surname, ... from table1 where category in (cat1,cat2,cat3);
Вот сюда приходят все записи какие лежат в этих категориях. Но я понимаю, что скорее всего этот запрос надо по умному доработать, по этому обращаюсь за помощью

[Dmitry Bay]

limit 500 offset 0
limit 500 offset 500
limit 500 offset 1000

и тд

[Dmitry Bay]

только сортировку указывайте.

[Twitt]

Дмитрий Байчапанов, Спасибо, буду пробовать

[Twitt]

Дмитрий Байчапанов, только по поводу сортировки - вопрос
А зачем она тут надо? Мне тоже говорили про "смотри в сторону Order by", но я так и не понял - какая разница? Разве сортировка не будет занимать время для запроса? И сортировать какое поле?
Короче вопрос:
Зачем она нужна в данном случае? И какому полю надо применить order by?
спасибо

[alex-1917]

Twitt, да забей на order, это одна из самых тяжелых для MYSQL операции!!! Выгружай ВСЁ сразу в массив, а дальше пиши php-скрипт, где уже что надо отсортировать и отфильтровать, php с ним влегкую справится!!! Я не про phpexceel,а про php

[alex-1917]

я для примера тебе скажу - я когда писал скрипт для голдмиллиона, там надо было искать в таблице 1000*1000 свободные ячейки! при этом это не просто 1млн строк, там еще и увязка по геометрии, т.е. рядом стоящие ячейки по горизонтали и вертикали подбирались и высчитывалось! сервер подыхал, пока я не решил все отдать php - перешел с 10 минут обработки или полного обрушения до миллисекнуд!

Answer 3

[Егор]

А если сохранить Excel в csv файл, и выгрузить через запрос LOAD DATA INFILE ?