Быстрая работа с файлами данных
Александр СкакуновОпубликовано 26.10.2006 в Статьи
Знакомый програмист рассказал, как ему дали проект, где нужно было ускорить работу сайта, сделанного коллегами-индусами. После недолгого копания, он обнаружил, что данные везде получаются из БД построчно — циклами на 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
Загрузка ...Понравилась статья? Подпишись на обновления по RSS/E-mail
И теперь работать с файлом? А смысл?
2 Opik:
Я так понимаю, ты про последний пример? Вообще это о выводе данных. Например, ты пишешь в своей системе раздел экспорта данных, тогда вместо генерации файла на РНР ты сделаешь такой дамп на SQL, что намного быстрее.
Интересная тема.
Есть два вопроса:
1)
Можете привести пример или дать ссылку стабильно работающий модуль для разбора XLS средствами РНР? Спасибо.
Кстати, Insert delayed по-моему должен существенно упростить задачу.
2)
часто экспорт производится по событию обращения к скрипту. Итого, если использовать ваш вариант, то надо дождаться создания файла сервером БД и отдать хидер на созданный файл. Как PHP узнает, что файл уже сформирован?
Excel тут.
Ты имеешь ввиду, что INSERT DELAYED будет хоть сколько-нибудь близок по скорости к LOAD DATA INFILE? Близок по-моему он не будет, плюс есть вероятность, что некий хвост данных останется ждать наполнения буфера.
Что касается выгрузки данных. Сложнее сделать, чтобы PHP не знал, что файл уже сформирован – это уже асинхронная работа, я так, к сожалению, не умею. Пока PHP продолжает работу, как только отработал вызов mysql_query(). Верно?
Забыл поблагодарить :]
Я было подумал, что это никому, кроме меня, не интересно.
Кстати, есть урок для мам: когда уходите с детского сада, не говори “Малыш, что надо сказать деткам?” а просто скажи “до свидания”!
Тема и правда интересна. И она наконец-то дала понять, почему Фругл использует csv (после яндекс-маркета с его xml это было мне странным)
Но мне еще интересно вот что: хорошо, если файл 500 (или даже 10) Мб — локальный. А если его сначала надо скачать с другого сервера? Ведь тут однозначно нужна докачка! Как ее реализовать на PHP? или хоть что искать на Гугле? Как “докачка” по-английски?
Намёк понял: спасибо!
XML хорош только тем, что в нём можно формализовать любую структуру данных, но в этом же и пуканус – в идеале надо знать структуру файла на приёмной стороне.
И поэтому, на мой взгляд, гемора с XML на два порядка больше.
Про докачку: очевидно, она тебе понадобится в любом случае, какой метод загрузки в БД ты бы ни выбрал. Качать файлы через инет можно разными способами:
– copy() – оптимистический;
– fopen() + while(fread()) – более реалистичный, но не спасает при разрыве связи (но мне хватило этого способа);
– закачивать файлы кусками – самый верный метод. Поставь себе FlashGet и посмотри в логе, какими HTTP-заголовками он меняется – там это хорошо видно.
Шишки.
Они, как известно, в процессе.
Так вот, у предложенного в статье стопоба есть рад недостатков, делающим его использования невозможным в ОГРОМНОМ количестве случаев:
1) При импорте CSV средствами MySQL нет возможности контроля данных. PHP ведь не учавствует — вот и нечем проверить. А это непозволительный риск, если предполагается хоть малейшее внешнее взаимодействие: это прямая дорога к SQL-инъекциям и дефейсу. Или я просто не знаю аналогов addslashes и htmlspecialchars в мускуле?
2)Нет возможности подстраиваться под пользовательскую структуру.
Если во входном файле просто переставить местами два столбца — все, скрипт надо подправлять. В xml, например, внутри группы порядок неважен и даже можно назначать псевдонимы на типовые контейнеры.
Опять же, если организовать импорт того же CSV но построчно средствами PHP, то можно в скрипте реализовать опрос пользователя на типы каждого столбца, что решит вопрос.
3) Правильноструктурный CSV не провериш на полноту заливки.
Речь идет о том, что xml-файл можно проверить, полностью ли он уже залит на сервер. Для этого просто достаточно проверить наличие в тексте закрывающего корневого контейнера. А что вы будете проверять в CSV? разве что наличие дописывания. Но и это не показатель. Если, скажем, клиент пытался залить файл, записал первые 10 строк и связь оборвалась. А он по какой-либо причине не стал его перекачивать. Втянув такой файл вы и предыдущие валидные данные прибъете и новые полностью не втянете.
Словом, не такие уже индусы глупые.
Естественно, csv намного меньше по размеру и его импорт средствами СУБД может быть в десятки раз быстрее. Но все это годится для внутрикорпоративного использования.
Как только речь идет о внешних клиентах — им надо недоверять, данные проверять. Забудьте о скорости — нужна надежность.
Короткий ответ: болты лучше откручиваются не молотком, а гаечным ключом. Вариативность нужна.
Теперь подробно.
Если бы я сам не пользовался предложенным в статье методом, я бы его и не советовал другим.
Да, я старался убедить вас, что лучше не грузить большие объёмы данных PHP (неподходящий инструмент). Но никто не запрещает управлять загрузкой с помощью PHP (эдакий командный пункт), запуская нужные запросы, ниже я покажу как.
На лицо непонимание процесса загрузки: MySQL сервер сам отквочивает что надо. SQL-инъекции возможны как раз из-за использования PHP, с помощью которого нормальный запрос может быть исправлен на вредоносный. Другими словами, я не представляю, куда надо засунуть, скажем, “DROP DATABASE” в загружаемый файл, чтобы этот запрос выполнился, eval() тут нет.
QUOTE(), но он вряд ли пригодится.
Кто ж хардкодит такие вещи… Сначала с помощью PHP fgetcsv() считываем первую строку (заголовок), показываем юзеру список полученных полей – пусть сам укажет, что куда заливать, типы данных, короче, что угодно. Когда такая инфа сохранена, то перемещение столбцов никак не повлияет на загрузку, потому что они запоминаются поимённо (а вообще часто приходится сталкиваться с переставленными столбцами?)
При использовании XML алгоритм будет похожий, с той разницей, что в этим случае уже целый геморрой получить список полей (XML ведь иерархический).
Связи никакой :] Почему если грузить построчно через PHP, то только тогда можно опрашивать пользователя? Для каждой загружаемой строки это делать? Пусть РНР только опрашивает пользователя о столбцах, а в загрузку пусть не лезет, этот болт не для него – для этого есть другой инструмент.
Интересный способ проверки удачного завершения операции, но я больше доверяю кодам возврата или ошибки (mysql_error() скажем).
Так вот, как это сделал я, не потеряв в безопасности и выиграв в скорости: загрузил всю инфу как есть во временную таблицу (CREATE TEMPORARY TABLE), и уже в ней выкусывал/форматировал/удалял что мне надо (PHP запускал нужные запросы). После этого уже чистые данные направлялись в рабочую таблицу.
Как и все обобщения, это утверждение ложно.
Если мы спорим о файликах данных в 1-2 метра, то я могу согласиться даже с ручным написанием INSERT-запросов :] Я веду речь о файлах данных реального мира, отсчёт начиная с 40-50 Мб: я думаю, при вариантах “16 секунд” и “4 часа” пользователь будет меньше всего обеспокоен безопасностью, потому что во втором случае он может просто перестать пользоваться такой “безопасной”, но медленной системой. И что обидно – даже прогресс-бар по-человечески не сделаешь…
P.S.
Я русский, а у меня нет ни ушанки, ни медведя – так что я согласен ;]
Если бы еще кто-то объяснил, как импортировать CVS с избыточными столбцами.
Как ему описать столбцы, если мне надо только 2й, 8,9,14й ? Притом имена полей базы отличаются от тех, которые в хидере файла описаны.
ага, а еще как при этом на ходу эксплодить значение столбца и подставлять его блоки в качестве значений для двух других полей.
Например, как для CSV:
f1,f2,f3,f4,f5
1, 2, 3, 4 5, 6
добиться логики вставки:
insert into table {field1, field2, field3} values (2, 4, 5);
У меня была такая же задача, и я решил её в два присеста:
1. Использую LOAD DATA INFILE для очень быстрой загрузки данных во временную таблицу (CREATE TEMPORARY TABLE), потому что импортить файл в рабочую таблицу напрямую – опасно.
2. По необходимости переколбашиваю данные в нужных полях временной таблицы (она теперь является как бы отображением на CSV-файл) и из неё заливаю что мне надо уже в рабочую таблицу:
INSERT INTO `table` (`field1`, `field2`, `field3`)
SELECT FROM `temp_table` `csv_field2`, `csv_field4`, `csv_field5`
Получается всё равно в миллиорд раз быстрее, чем инсёртами из PHP заливать CSV-файл.
Есть ответ?
круто.
идея должна сработать.
Может, уже по ходу скажете, чем однострочный файл ~70Mb открыть и комфортно поредактировать?
Спасибо.
Комфортно – не подскажу
Ето всё конечно хорошо, но не слишком хочется зависеть от хостеров несколькими технологиями одновременно(к примеру PHP & MySQL) + способ доступа через запросы – идея не из лучших.
Подскажите как написать быструю базу данных на самом PHP. Или хотябы общие принцыпы. Пусть даже прийдётся переписать всю MySQL на PHP, но ето будет намного удобней в итоге чем использование оригинальной БД, пусть даже немного медленней.
Дружище, не гони: щас надо очень потрудится, чтобы не смочь найти халявный хостинг РНР + мускул.
Да не проблема найти хостинг, и не проблема работать с MySQL но ето ужасное извращение и тонна неудобств.
У меня комерческий хостинг с поддержкой всей необходимой гадости, но от етого ничего не меняется.
Помогите найти алгоритмы и способы создание быстрой базы на основе файлов.
Прочитайте свое сообщение через 5 лет.