Олимпийский девиз "Быстрее, выше, сильнее" постепенно начинает распространяться не только на спорт, но и на другие сферы жизни, и Веб не исключение: требования к скорости (читай — комфортности) работы постоянно растут. В этой статье мы рассмотрим, как ускорить обработку файлов обменных типов (CSV, XML) за счет использования базы данных.

Для начала обратим свои взоры на формат CSV.

CSV-файл (Comma Separated Values — "значения, разделенные запятыми") представляет собой обычный текстовый файл, содержащий таблицу данных. В первой строке такого файла обычно через запятую указаны имена столбцов, а во всех последующих строках — их значения в том же порядке. Так, например, при экспорте адресной книги из почтовой программы The Bat! в CSV-файл он может выглядеть так:

ФИО,  E-mail,  псевдоним  
Иванов  И.  И.,  i@server.ua,  Вано  
Петров  П.  П.,  p@server.ua,  Петруччо  
Сидоров  С.  С.,  s@server.ua,  Сидор

Самый большой плюс, снискавший популярность этому формату, — простота. Многие языки программирования имеют встроенные функции для работы с ним (например, fputcsv() и fgetcsv() в PHP), а в отсутствие оных программисту не составит труда написать функцию, разбирающую файл на составляюшие. Поэтому если наша задача — импортировать данные из CSV-файла в базу данных, то алгоритм будет таков:

1. считать очередную строку;
2. сформировать массив значений;
3. выполнить INSERT-запрос к базе с этими значениями.

Этот метод отлично работает, если файл данных небольшой. Но реальный мир предъявляет свои требования — и как поведет себя такая функция с файлом размером в несколько десятков мегабайт?

Поскольку вышеназванные функции обрабатывают файл построчно, то загрузка данных в базу происходит очень медленно: автор этих строк так ни разу и не дождался, когда же на PHP обработается тестовый файл размером 40 Мб.

Как решение можно использовать группировку INSERT-запросов: один большой INSERT вместо множества маленьких, MySQL это позволяет. Но построчная обработка все равно занимает львиную долю времени.

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

• имя загружаемого файла;
• опции полей и строк: какие символы используются для их разделения и экранирования;
• что делать с повторяющимися строками — пропускать или перезаписывать;
• пропуск первых N строк. Как правило, пропускают первую строку, содержащую заголовок;
• задать определенные значения нужным полям.

Читайте инструкцию, там все есть

Дополнительную информацию по более тонкой настройке можно найти в документации, впрочем, не слишком изобилующей примерами.

Поразительно, но упомянутый 40-мегабайтный файл был загружен таким образом за 16 секунд.

Кстати, в других серверах БД тоже есть похожий инструментарий. Например, в MS SQL можно воспользоваться консольной программой bcp. Тем не менее выполнение SQL-запроса предпочтительней, поскольку обеспечивает кросс-платформенность, в то время как дополнительное ПО обычно жестко привязано к определенной операционной системе.

Таким образом, загрузка файла с:\1.csv может быть выполнена следующим запросом:

LOAD  DATA  INFILE  'с:\\1.csv'  
REPLACE  
INTO  TABLE  table1  
FIELDS  
                TERMINATED  BY  ','  
LINES  
                TERMINATED  BY  '\r\n'  
IGNORE  1  LINES

Данные бывают разные, и часто нужно указать, в какой кодировке они созданы. К сожалению, запрос LOAD DATA INFILE не позволяет указать это явно, поэтому перед его выполнением нужно выставить нужную кодировку в серверной переменной character_set_database — согласно документации, именно она отвечает за разбор данных. Так, например, если файл был создан в Windows и содержит русский текст (кодировка Windows 1251), нужно запустить такую SQL-команду:

SET  SESSION  character_set_database  =  cp1251;

Как видно из этого примера, MySQL имеет собственный список имен кодировочных таблиц, не совпадающий со ставшими привычными обозначениями, используемыми в веб-страничках. Чтобы получить список доступных на MySQL-сервере кодировок (а этот список варьируется от одного сервера к другому), выполните следующий запрос:

SHOW  CHARACTER  SET;

Для импорта CSV-файлов в MySQL-базу через PHP можно использовать готовый класс Quick CSV import.

Пример работы класса Quick CSV import

Теперь рассмотрим работу с XML (eXtensible Markup Language — "расширяемый язык разметки").

Этот текстовый теговый формат весьма популярен, поскольку в унифицированной форме представляет данные любого формата, что делает его универсальным средством для обмена данными, но не их хранения, и потому его унифицированность наносит непоправимый ущерб удобству доступа к данным. XML в его стандратном обличье никогда не сравнится в удобстве работы с данными, скажем, с SQL.

Предыдущий пример CSV-файла, перенесенный в XML, будет выглядеть так:

<?xml  version="1.0"?>  
<book>  
  <entry>  
      <FIO>Иванов  И.  И.</FIO>  
      <E-mail>i@server.ua</E-mail>  
      <Nick>Вано</Nick>  
  </entry>  
  <entry>  
      <FIO>Петров  П.  П.</FIO>  
      <E-mail>p@server.ua</E-mail>  
      <Nick>Петруччо</Nick>  
  </entry>  
  <entry>  
      <FIO>Сидоров  С.  С.</FIO>  
      <E-mail>s@server.ua</E-mail>  
      <Nick>Сидор</Nick>  
  </entry>  
</book>

Для переноса данных из XML-файла в базу данных изначально использовался такой алгоритм:

1. считать весь файл в строку;
2. преобразовать XML в PHP-массив;
3. выполнить серию INSERT-запросов;

Этот метод, как и следовало ожидать, оказался, во-первых, очень медленным, во-вторых, требующим много памяти. По умолчанию PHP владеет 8 мегабайтами оперативной памяти, и попытки быстро работать с массивами, преобразованными из многомегабайтных прайс-листов в XML-формате, — жалкое зрелище. Другой замедляющий момент — обилие запросов к базе. Даже если веб-сервер и БД стоят на одной машине, что должно снижать накладные расходы, количество запросов будет так велико, что работа затянется надолго.

Поэтому имеет смысл опробовать такой алгоритм:

1. считать весь файл в строку;
2. преобразовать XML в PHP-массив;
3. сохранить весь массив в CSV-файл;
4. использовать быструю загрузку CSV в БД.

Несмотря на то что в этом алгоритме присутствует строковая переменная, содержащая данные всего файла, что должно было бы негативно сказаться на скорости, данный алгоритм в разы быстрее предыдущего. В принципе можно попробовать пойти дальше и ускорить этот процесс еще и тем, что сохранять в файл блоки данных, скажем, при достижении N строк или при пересечении массивом предела в объеме, однако и так алгоритм дает существенный прирост производительности.

Пример ошибки, возникающей, если файл уже существует

По поводу упомянутого преобразования XML в массив: множество примеров можно найти на сайте php.net. Алгоритм этот нетривиальный, поэтому следует поэкспериментировать и выбрать подходящий.

Пару слов стоит сказать о быстром экспорте данных в CSV — он намного проще. Опять же, не стоит заниматься построчной обработкой — производительнее и проще использовать до боли знакомый SELECT, у которого есть поддержка вывода результатов во внешний файл.

Единственное ограничение — файл не должен существовать; это сделано во избежание несчастных случаев на производстве вроде затертых системных файлов.

Данный запрос использует те же настройки, что и запрос LOAD DATA INFILE.

Простейший SQL-запрос для экспорта будет выглядеть так:

SELECT  *  FROM  'test'  
INTO  OUTFILE  'c:\\1.txt'  
FIELDS  TERMINATED  BY  ','

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