Быстрая работа с файлами данных

Знакомый програмист рассказал, как ему дали проект, где нужно было ускорить работу сайта, сделанного коллегами-индусами. После недолгого копания, он обнаружил, что данные везде получаются из БД построчно — циклами на PHP.

Часто и мы пишем что-то подобное.

Например, когда дело касается файлов данных. Поговорим о CSV (данные, разделённые запятыми). Да, есть в PHP функции fgetcsv()/fputcsv(), но они парсят файл построчно. Что на продакшн-объёмах есть тормоза и нагрузки.

Для тех, кто не в курсе, рассказываю страшную тайну: если эти данные должны попасть в базу данных, то есть такая вещь, как BULK INSERT — это когда разбором файла и его загрузкой в БД занимается сам сервер БД. Результаты просто плачевные (это слёзы радости): 500 Мб файл пожирается за несколько секунд, в то время как при построчной обработке потребуется не один час.

Как это юзать.

Есть два пути — консольные проги и SQL.

Консольные проги смотрите в поставке своего сервера. Для MS SQL это bcp, для MySQL — mysqlimport. Всё просто — указываешь файл, имя таблицы, разделители и рвёшь чеку.

C SQL как-то привычнее. В MySQL есть стандартная структура LOAD DATA INFILE, которая, получив нужные параметры, скушает ваш файл, не поперхнувшись. Ищите — и обрящете, упомяну только, что кодировка при парсинге задаётся глобальной переменной, если, например, пользовательский файл не в кодировке сервера по умолчанию:

SET SESSION character_set_database = utf8;

Кроме того, опыт показал, что XML и XLS (Excel) в базу грузится намного быстрее, если даже силами РНР с 8 мегабайтами его дефолтовой памяти преобразовать их в CSV, а потом загрузить полученный файл указанным методом.

Что касается выгрузки данных из БД, то тоже можно обойтись без построчного онанизма, а использовать старый добрый SELECT. Опять же, для всеми-гонимого-всеми-любимого MySQL это пишется так:

SELECT * FROM 'test' INTO OUTFILE 'c:\\1.txt';

Ссылки по теме:
LOAD DATA INFILE Syntax
Class: Quick CSV import
ua_mysql Google Group
Преобразование XML в структуру
MySQL: быстрая работа с CSV-файлами
Импорт данных в базы MySQL

Теми: MySQL, PHP, tech

Andriy Burkov 15.04.2011 04:38

О, спасибо Саша!

Відповісти

Підтримати

anonymous 20.09.2010 19:45

Прочитайте свое сообщение через 5 лет.

Да не проблема найти хостинг, и не проблема работать с MySQL, но ето ужасное извращение и тонна неудобств.У меня комерческий хостинг с поддержкой всей необходимой гадости, но от етого ничего не меняется.Помогите найти алгоритмы и способы создание быстрой базы на основе файлов.

Alexander Skakunov Founder в Місія на Марс 20.09.2010 19:45

Дружище, не гони: щас надо очень потрудится, чтобы не смочь найти халявный хостинг РНР + мускул.

Ето всё конечно хорошо, но не слишком хочется зависеть от хостеров несколькими технологиями одновременно (к примеру PHP & MySQL) + способ доступа через запросы — идея не из лучших.Подскажите как написать быструю базу данных на самом PHP. Или хотябы общие принцыпы. Пусть даже прийдётся переписать всю MySQL на PHP, но ето будет намного удобней в итоге чем использование оригинальной БД, пусть даже немного медленней.

Комфортно — не подскажу:)

круто.идея должна сработать.Может, уже по ходу скажете, чем однострочный файл ~70Mb открыть и комфортно поредактировать? Спасибо.

У меня была такая же задача, и я решил её в два присеста: 1. Использую LOAD DATA INFILE для очень быстрой загрузки данных во временную таблицу (CREATE TEMPORARY TABLE), потому что импортить файл в рабочую таблицу напрямую — опасно.2. По необходимости переколбашиваю данные в нужных полях временной таблицы (она теперь является как бы отображением на CSV-файл) и из неё заливаю что мне надо уже в рабочую таблицу: INSERT INTO

table

(

field1

field2

field3

)SELECT FROM

temp_tablecsv_field2

csv_field4

csv_field5

Получается всё равно в миллиорд раз быстрее, чем инсёртами из PHP заливать CSV-файл.Есть ответ?

ага, а еще как при этом на ходу эксплодить значение столбца и подставлять его блоки в качестве значений для двух других полей.Например, как для CSV: f1, f2, f3, f4, f51, 2, 3, 4 5, 6добиться логики вставки: insert into table {field1, field2, field3} values (2, 4, 5);

Если бы еще кто-то объяснил, как импортировать CVS с избыточными столбцами.Как ему описать столбцы, если мне надо только 2й, 8, 9, 14й? Притом имена полей базы отличаются от тех, которые в хидере файла описаны.

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

При импорте CSV средствами MySQL нет возможности контроля данных. PHP ведь не учавствует — вот и нечем проверить

Да, я старался убедить вас, что лучше не грузить большие объёмы данных PHP (неподходящий инструмент). Но никто не запрещает управлять загрузкой с помощью PHP (эдакий командный пункт), запуская нужные запросы, ниже я покажу как.

это прямая дорога к SQL-инъекциям и дефейсу

На лицо непонимание процесса загрузки: MySQL сервер сам отквочивает что надо. SQL-инъекции возможны как раз из-за использования PHP, с помощью которого нормальный запрос может быть исправлен на вредоносный. Другими словами, я не представляю, куда надо засунуть, скажем, «DROP DATABASE» в загружаемый файл, чтобы этот запрос выполнился, eval () тут нет.

Или я просто не знаю аналогов addslashes и htmlspecialchars в мускуле

QUOTE () , но он вряд ли пригодится.

Нет возможности подстраиваться под пользовательскую структуру

Кто ж хардкодит такие вещи... Сначала с помощью PHP fgetcsv () считываем первую строку (заголовок), показываем юзеру список полученных полей — пусть сам укажет, что куда заливать, типы данных, короче, что угодно. Когда такая инфа сохранена, то перемещение столбцов никак не повлияет на загрузку, потому что они запоминаются поимённо (а вообще часто приходится сталкиваться с переставленными столбцами?) При использовании XML алгоритм будет похожий, с той разницей, что в этим случае уже целый геморрой получить список полей (XML ведь иерархический).

если организовать импорт того же CSV, но построчно средствами PHP, то можно в скрипте реализовать опрос пользователя на типы каждого столбца

Связи никакой:] Почему если грузить построчно через PHP, то только тогда можно опрашивать пользователя? Для каждой загружаемой строки это делать? Пусть РНР только опрашивает пользователя о столбцах, а в загрузку пусть не лезет, этот болт не для него — для этого есть другой инструмент.

Для этого просто достаточно проверить наличие в тексте закрывающего корневого контейнера

Интересный способ проверки удачного завершения операции, но я больше доверяю кодам возврата или ошибки (mysql_error () скажем).

Как только речь идет о внешних клиентах — им надо недоверять, данные проверять

Так вот, как это сделал я, не потеряв в безопасности и выиграв в скорости: загрузил всю инфу как есть во временную таблицу (CREATE TEMPORARY TABLE), и уже в ней выкусывал/форматировал/удалял что мне надо (PHP запускал нужные запросы). После этого уже чистые данные направлялись в рабочую таблицу.

Забудьте о скорости — нужна надежность

Как и все обобщения, это утверждение ложно.Если мы спорим о файликах данных в 1−2 метра, то я могу согласиться даже с ручным написанием INSERT-запросов:] Я веду речь о файлах данных реального мира, отсчёт начиная с 40−50 Мб: я думаю, при вариантах «16 секунд» и «4 часа» пользователь будет меньше всего обеспокоен безопасностью, потому что во втором случае он может просто перестать пользоваться такой «безопасной», но медленной системой. И что обидно — даже прогресс-бар по-человечески не сделаешь...P.S.

не такие уже индусы глупые

Я русский, а у меня нет ни ушанки, ни медведя — так что я согласен;]

Шишки.Они, как известно, в процессе.Так вот, у предложенного в статье стопоба есть рад недостатков, делающим его использования невозможным в ОГРОМНОМ количестве случаев: 1) При импорте CSV средствами MySQL нет возможности контроля данных. PHP ведь не учавствует — вот и нечем проверить. А это непозволительный риск, если предполагается хоть малейшее внешнее взаимодействие: это прямая дорога к SQL-инъекциям и дефейсу. Или я просто не знаю аналогов addslashes и htmlspecialchars в мускуле? 2) Нет возможности подстраиваться под пользовательскую структуру.Если во входном файле просто переставить местами два столбца — все, скрипт надо подправлять. В xml, например, внутри группы порядок неважен и даже можно назначать псевдонимы на типовые контейнеры.Опять же, если организовать импорт того же CSV, но построчно средствами PHP, то можно в скрипте реализовать опрос пользователя на типы каждого столбца, что решит вопрос.3) Правильноструктурный CSV не провериш на полноту заливки.Речь идет о том, что xml-файл можно проверить, полностью ли он уже залит на сервер. Для этого просто достаточно проверить наличие в тексте закрывающего корневого контейнера. А что вы будете проверять в CSV? разве что наличие дописывания. Но и это не показатель. Если, скажем, клиент пытался залить файл, записал первые 10 строк и связь оборвалась. А он по какой-либо причине не стал его перекачивать. Втянув такой файл вы и предыдущие валидные данные прибъете и новые полностью не втянете.Словом, не такие уже индусы глупые.Естественно, csv намного меньше по размеру и его импорт средствами СУБД может быть в десятки раз быстрее. Но все это годится для внутрикорпоративного использования.Как только речь идет о внешних клиентах — им надо недоверять, данные проверять. Забудьте о скорости — нужна надежность.

Кстати, есть урок для мам: когда уходите с детского сада, не говори «Малыш, что надо сказать деткам? », а просто скажи «до свидания»!

Намёк понял: спасибо! XML хорош только тем, что в нём можно формализовать любую структуру данных, но в этом же и пуканус — в идеале надо знать структуру файла на приёмной стороне.И поэтому, на мой взгляд, гемора с XML на два порядка больше.Про докачку: очевидно, она тебе понадобится в любом случае, какой метод загрузки в БД ты бы ни выбрал. Качать файлы через инет можно разными способами: — copy () — оптимистический; — fopen () + while (fread ()) — более реалистичный, но не спасает при разрыве связи (но мне хватило этого способа); — закачивать файлы кусками — самый верный метод. Поставь себе FlashGet и посмотри в логе, какими HTTP-заголовками он меняется — там это хорошо видно.

Забыл поблагодарить:]

:)) Спасибо! Кстати, есть урок для мам: когда уходите с детского сада, не говори «Малыш, что надо сказать деткам? », а просто скажи «до свидания»! Тема и правда интересна. И она наконец-то дала понять, почему Фругл использует csv (после яндекс-маркета с его xml это было мне странным) Но мне еще интересно вот что: хорошо, если файл 500 (или даже 10) Мб — локальный. А если его сначала надо скачать с другого сервера? Ведь тут однозначно нужна докачка! Как ее реализовать на PHP? или хоть что искать на Гугле? Как «докачка» по-английски?

Интересная тема

Забыл поблагодарить:] Я было подумал, что это никому, кроме меня, не интересно.

Excel тут.Ты имеешь ввиду, что INSERT DELAYED будет хоть сколько-нибудь близок по скорости к LOAD DATA INFILE? Близок по-моему он не будет, плюс есть вероятность, что некий хвост данных останется ждать наполнения буфера.Что касается выгрузки данных. Сложнее сделать, чтобы PHP не знал, что файл уже сформирован — это уже асинхронная работа, я так, к сожалению, не умею. Пока PHP продолжает работу, как только отработал вызов mysql_query (). Верно?

Интересная тема.Есть два вопроса: 1)

Можете привести пример или дать ссылку стабильно работающий модуль для разбора XLS средствами РНР? Спасибо.Кстати, Insert delayed по-моему должен существенно упростить задачу.2)

SELECT... INTO OUTFILE

часто экспорт производится по событию обращения к скрипту. Итого, если использовать ваш вариант, то надо дождаться создания файла сервером БД и отдать хидер на созданный файл. Как PHP узнает, что файл уже сформирован?

2 Opik: Я так понимаю, ты про последний пример? Вообще это о выводе данных. Например, ты пишешь в своей системе раздел экспорта данных, тогда вместо генерации файла на РНР ты сделаешь такой дамп на SQL, что намного быстрее.

И теперь работать с файлом? А смысл?

Быстрая работа с файлами данных

19 коментарів

Підписатись на коментарі

Новини