УрокЗапись большого объёма данных в таблицу

Каждый хороший разработчик думает о производительности. И сегодня я расскажу о том, как можно быстро записать большие объёмы данных в таблицу за минимальный период времени.

Я уверен, что многие сталкивались с задачей, например, собирать какую-либо статистику и сохранять её в отдельную таблицу. Простейший пример: сохранять количество просмотров и комментариев к материалу за каждый день. А потом по этим данным построить график, который бы визуально отображал "популярность" материала за определённый период. Сама по себе задача простая - выбрать из двух таблиц данные и записать её в свою отдельную. Но когда у вас на сайте, скажем, 200 тысяч материалов, то вставка такого объёма данных может занять приличное количество времени. Но давайте поговорим о возможных вариантах записи этих данных.

Сначала выбираем тип таблицы

Касательно Друпала в MySQL распространены 2 типа таблиц: MyISAM и InnoDB. Они имеют ряд отличительных особенностей, в том числе и по производительности. Я не буду сейчас рассказывать обо всех отличиях, их довольно просто нагуглить либо почитать в источниках (MyISAM и InnoDB). Я затрону лишь то, что касается темы статьи:

  • MyISAM работает быстрее на вставке данных в таблицу.
  • InnoDB работает быстрее при выборке данных из базы.
  • Пока идёт запись в таблицу типа MyISAM вы не сможете выбрать из неё данные и показать пользователю: таблица полностью блокируется.
  • InnoDB блокирует таблицу на уровне строк, поэтому пока одни данные записываются, другие можно в это время читать.

Как видите, эти типы таблиц являются практически полной противоположностью друг друга. Иными словами то, что для MyISAM хорошо, то для InnoDB плохо, и наоборот. Поэтому сначала надо решить какой тип таблицы более приемлем для поставленной задачи. Для себя я выбрал InnoDB: у меня запись в таблицу происходила раз в сутки, зато данные из неё выбираются практически на каждой странице (кэширование не спасает, т.к. данные персонализированы).

Оптимизируем запись данных в таблицу

Будем считать, что получить данные со статистикой у нас получилось. Теперь стоит задача записи её в таблицу. И тут многими разработчиками допускается эпическая ошибка ($data - массив с данными для записи):

foreach ($data as $item) {
  db_insert('mytable')
    ->values(array(
      'nid' => $item['nid'],
      'comment_count' => $item['comment_count'];
      'total_views' => $item['total_views'],
    ))
  ->execute();
}

Поясняю ошибку (а я напоминаю, что у нас речь идёт о больших объёмах данных). Каждый раз при выполнении db_insert():

  • PDO конструкция разбирается и превращается в обычную строку с запросом к базе
  • открывается новая транзакция (а после выполнения запроса закрывается)

Потери в производительности при этом огромнейшие. Я даже думать не хочу думать о том, что если у вас таблица MyISAM, то таблица может быть вообще недоступна для чтения в течении нескольких минут. А теперь представьте себе, у какого количества пользователей не откроется сайт из-за этой ошибки разработчика?

Решение

Я, пожалуй, опущу разговор о возможном переходе на другие базы данных, ибо это зависит от задачи. Перейдём к основному: оптимизация запроса. Если вы переносили данные между базами то наверняка вы обратили внимание, что при экпорте формируется примерно такая строка (только, естественно, больше):

INSERT INTO `mytable` (`nid`, `comment_count`, `total_views`) VALUES 
(997, 9917, 997), 
(998, 9915, 998),
(999, 9914, 999),  
(1000, 9913, 1000);

То же самое я предлагаю сделать и с большим объёмом данных: просто превратить его в аналог записи выше. В этом случае открывается всего одна транзакция и PDO конструкция разбирается всего один раз. Вот как это выглядит для седьмого Друпала:

$query = db_insert('mytable')
  ->fields(array('nid', 'comment_count', 'total_views'));
 
foreach ($data as $item) {
  $query->values(array(
    'nid' => $item['nid'],
    'comment_count' => $item['comment_count'];
    'total_views' => $item['total_views'],
  ));
}
 
$query->execute();

Выполнив этот нехитрый трюк вы сможете записывать огромнейшие объёмы в пределах 5-10 секунд. В то время как раньше это могло бы занять N минут.

Оптимизируйте на здоровье!

Комментарии

Аватар пользователя xandeadx
xandeadx написал:

записать двести тысяч нод за одну секунду? ну ну)

14.08.2012 16:39
Аватар пользователя Spleshka
Spleshka написал:

Только что проверил на 100 тысячах записей записей (с втрое бОльшим количеством столбцов) - записалось за 8 секунд (на InnoDB). На MyISAM за 5.8 сек записалось. С одной секундой я перегнул маленько, но всё равно скорость работы более чем удовлетворяет.

14.08.2012 16:57
Аватар пользователя emzzy
emzzy написал:

А как с нагрузкой на mysql для такой большой порции INSERT?

15.08.2012 10:53
Аватар пользователя Spleshka
Spleshka написал:

Нагрузка, конечно, точечно сильно возрастает.

06.03.2013 23:09
Аватар пользователя Ch
Ch написал:

> Выполнив этот нехитрый трюк вы сможете записывать огромнейшие объёмы
На самом деле "огромнейшие" объемы ограничены директивой max_allowed_packet. В значительном количестве случаев при загрузке сериализованных или blob данных данный метод в чистом виде не подходит.

16.08.2012 14:34
Аватар пользователя Павел
Павел написал:

Большое спасибо, помогло. :)

26.10.2012 17:22
Аватар пользователя Тимур
Тимур написал:

Совсем недавно реализовал обновление каталога продукции из Excel (примерно 2-3 тыс. записей за раз). Вставлял, естественно, одним большим запросом в таблицу типа MyISAM с инструкцией INSERT DELAYED.

24.01.2013 17:09
Аватар пользователя pr0g
pr0g написал:

Спасибо. Интересная информация.
Только хотелось узнать, можно ли ее применить совместно с Batch API и если можно то как?
У меня что-то не получилось запустить, записывается только одна (последняя) запись.

10.03.2013 00:30
Аватар пользователя Spleshka
Spleshka написал:

К батчу такой подход нельзя применить. Это 2 разных подхода к записи данных. Либо одной большой пачкой записать за раз, либо поштучно на батче.

06.03.2013 22:55
Аватар пользователя pr0g
pr0g написал:

Спасибо за ответ!
А вы не проверяли как сказывается ваш метод (который вы описали в вашей статье) записи большого количества записей на расход оперативной памяти? Сколько потребуется памяти?

07.03.2013 09:03
Аватар пользователя Spleshka
Spleshka написал:

Так от объёма записываемых данных зависит, всё очень индивидуально.

07.03.2013 21:11

Комментировать