УрокЗапись большого объёма данных в таблицу
Каждый хороший разработчик думает о производительности. И сегодня я расскажу о том, как можно быстро записать большие объёмы данных в таблицу за минимальный период времени.
Я уверен, что многие сталкивались с задачей, например, собирать какую-либо статистику и сохранять её в отдельную таблицу. Простейший пример: сохранять количество просмотров и комментариев к материалу за каждый день. А потом по этим данным построить график, который бы визуально отображал "популярность" материала за определённый период. Сама по себе задача простая - выбрать из двух таблиц данные и записать её в свою отдельную. Но когда у вас на сайте, скажем, 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 минут.
Оптимизируйте на здоровье!
- Spleshka
- 14.08.2012
- 16332
Комментарии
записать двести тысяч нод за одну секунду? ну ну)
Только что проверил на 100 тысячах записей записей (с втрое бОльшим количеством столбцов) - записалось за 8 секунд (на InnoDB). На MyISAM за 5.8 сек записалось. С одной секундой я перегнул маленько, но всё равно скорость работы более чем удовлетворяет.
А как с нагрузкой на mysql для такой большой порции INSERT?
Нагрузка, конечно, точечно сильно возрастает.
> Выполнив этот нехитрый трюк вы сможете записывать огромнейшие объёмы
На самом деле "огромнейшие" объемы ограничены директивой max_allowed_packet. В значительном количестве случаев при загрузке сериализованных или blob данных данный метод в чистом виде не подходит.
Большое спасибо, помогло. :)
Совсем недавно реализовал обновление каталога продукции из Excel (примерно 2-3 тыс. записей за раз). Вставлял, естественно, одним большим запросом в таблицу типа MyISAM с инструкцией INSERT DELAYED.
Спасибо. Интересная информация.
Только хотелось узнать, можно ли ее применить совместно с Batch API и если можно то как?
У меня что-то не получилось запустить, записывается только одна (последняя) запись.
К батчу такой подход нельзя применить. Это 2 разных подхода к записи данных. Либо одной большой пачкой записать за раз, либо поштучно на батче.
Спасибо за ответ!
А вы не проверяли как сказывается ваш метод (который вы описали в вашей статье) записи большого количества записей на расход оперативной памяти? Сколько потребуется памяти?
Так от объёма записываемых данных зависит, всё очень индивидуально.
Комментировать