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

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

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

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