В категории: Базы данных

MySQL: быстрая работа с CSV-файлами

использование SQL для быстрой загрузки данных

MySQL: быстрая работа с CSV-файлами

 

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

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

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

 С помощью CSV-файла удобно обмениваться табличными данными

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

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

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

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

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

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

Использование SQL для быстрой загрузки данных
Читайте инструкцию, там все есть

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

Поразительно, но упомянутый 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
Пример работы класса 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-файла в базу данных изначально использовался такой алгоритм: 

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

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

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

MySQL: быстрая работа с CSV-файлами
Пример ошибки, возникающей, если файл уже существует

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

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

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

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

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

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

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

Источник: hostinfo.ru

MySQL: быстрая работа с CSV-файлами MySQL: быстрая работа с CSV-файлами MySQL: быстрая работа с CSV-файлами MySQL: быстрая работа с CSV-файлами MySQL: быстрая работа с CSV-файлами MySQL: быстрая работа с CSV-файлами MySQL: быстрая работа с CSV-файлами MySQL: быстрая работа с CSV-файлами MySQL: быстрая работа с CSV-файлами MySQL: быстрая работа с CSV-файлами MySQL: быстрая работа с CSV-файлами MySQL: быстрая работа с CSV-файлами MySQL: быстрая работа с CSV-файлами MySQL: быстрая работа с CSV-файлами

-