Skip to content

Пакетное изменение и добавление строк в MySQL

29/10/2009

При множестве мелких запросов к MySQL сам overhead запроса может занимать значительную часть времени. Например, нам нужно создать алиасы для элементов:

	$elements_result = db_query("SELECT 
			`element_id` AS `id`, 
			`element_name` AS `name`
		FROM `web_elements` WHERE 1");
		
	while ($element = db_fetch_object($elements_result)) {
		$alias = create_element_alias($element->name);
		$result = db_query("INSERT INTO `element_aliases` VALUES ('%d', '%s')", $element->id, $alias);
	}
	unset($elements_result);

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

	$elements_result = db_query("SELECT 
			`element_id` AS `id`, 
			`element_name` AS `name`
		FROM `web_elements` WHERE 1");
		
	$aliases = array();
	
	while ($element = db_fetch_object($elements_result)) {
		$aliases[$element->id] = create_element_alias($element->name);
		
		// размер пакета, тут для каждой задачи оптимальное значение будет разным
		if (count($aliases) == 100) { 
			$inserts = array();
			foreach ($aliases AS $el_id => $el_alias) {
				// собираем части запроса в массив для простой склейки
				$inserts[] = "('" . $el_id . "', '" . $el_alias . "')"; 
			}
			$result = db_query("INSERT INTO `element_aliases` (`element_id`, `element_alias`) VALUES " . implode(',', $inserts));
			unset($inserts);
			// обнулим массив для уже вставленных в таблицу alias'ов, чтобы не вставлять их по несколько раз
			$aliases = array(); 
		}
	}
	unset($elements_result);
	if (count($aliases) > 0) {
		$inserts = array();
		foreach ($aliases AS $el_id => $el_alias) {
			$inserts[] = "('" . $el_id . "', '" . $el_alias . "')";
		}
		$result = db_query("INSERT INTO `element_aliases` VALUES " . implode(',', $inserts));
		unset($inserts);
		$aliases = array();
	}

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

В моём случае при обработке таблицы в ~5000 элементов время обработки всей таблицы упало с 2 минут 10 секунд до 3.14 секунд — в 41 раз! При размере пакета 1000 элементов время возросло: ~18 секунд на тот же объём данных.

Хорошо, с INSERT разобрались, но с UPDATE всё несколько сложнее: синтаксис UPDATE не позволяет указать несколько значений сразу. Зато можно поместить новые значения столбцов во временную таблицу и выполнить UPDATE с помощью INNER JOIN:

  connect();

  set_time_limit(0); // На всякий случай

  // Обычный UPDATE
  $timer = microtime(TRUE);
  $result = mysql_query("SELECT `id`, `name` FROM `cards`") or die("Query error: " . mysql_error());

  while ($card = mysql_fetch_object($result)) {
	  $alias = sanitize_name($card->name);

	  $res = mysql_query(sprintf("UPDATE `cards` SET  `alias` =  '%s' WHERE `id` = '%d'", $alias, $card->id)) or die("Query error: " . mysql_error());
  }

  $timer = round(microtime(TRUE) - $timer, 3);
  // Закончили обычный UPDATE

  echo '<html><body>';

  echo '<div>Update done in ' . $timer . ' seconds</div>';

  $result = mysql_query("UPDATE `cards` SET `alias` = NULL WHERE 1") or die("Query error: " . mysql_error()); // обнулим поле

  // Batch UPDATE
  $timer = microtime(TRUE);

  $result = mysql_query("SELECT `id`, `name` FROM `cards` WHERE 1") or die("Query error: " . mysql_error());
  $res = mysql_query("CREATE TEMPORARY TABLE `aliases` (
		`card_id` INT NOT NULL ,
		`card_alias` VARCHAR( 100 ) NOT NULL ,
		PRIMARY KEY (  `card_id` )
		) ENGINE = MYISAM ;") or die("Query error: " . mysql_error());

  $aliases = array();

  while ($card = mysql_fetch_object($result)) {
	  $aliases[$card->id] = sanitize_name($card->name);

	  if (count($aliases) == 100) {
		  $inserts = array();
		  foreach ($aliases AS $card_id => $card_alias) {
			  $inserts[] = "('" . $card_id . "','" . $card_alias . "')";
		  }
		  $res = mysql_query("INSERT INTO `aliases` (`card_id`, `card_alias`) VALUES " . implode(',', $inserts)) or die("Query error: " . mysql_error());
		  $aliases = array();
	  }

  }
  if (count($aliases) > 0) {
	  $inserts = array();
	  foreach ($aliases AS $card_id => $card_alias) {
		  $inserts[] = "('" . $card_id . "','" . $card_alias . "')";
	  }
	  $res = mysql_query("INSERT INTO `aliases` (`card_id`, `card_alias`) VALUES " . implode(',', $inserts)) or die("Query error: " . mysql_error());
	  unset($aliases);
  }
  $res = mysql_query("UPDATE `cards` INNER JOIN `aliases` ON `cards`.`id` = `aliases`.`card_id`
		SET `cards`.`alias` = `aliases`.`card_alias`
		WHERE `aliases`.`card_alias` IS NOT NULL") or die("Query error: " . mysql_error());

  $timer = round(microtime(TRUE) - $timer, 3);
  echo '<div> Batch update done in ' . $timer . ' seconds</div>';
  // Закончили Batch UPDATE

  echo '</body></html>';

Здесь мы создаём алиасы для ~14300 карт Magic the Gathering и записываем их в базу. Внутри sanitize_name не происходит ничего особенно интересного: несколько замен с preg_match и preg_replace. Результат выполнения вышеуказанного кода при размере пакета 100 элементов:

Update done in 27.361 seconds
Batch update done in 3.356 seconds

Ускорение в ~8 раз. Если выставить размер пакета в 1000, получим примерно такой же выигрыш в 7.7 ~ 8 раз. Здесь также лучше подбирать значение для конкретной задачи.

Кстати, вместо UPDATE ... INNER JOIN можно использовать INSERT ... ON DUPLICATE KEY UPDATE, но это я попробую уже как нибудь в другой раз 🙂

Реклама
2 комментария
  1. Юдин Алексей permalink

    Используйте INSERT … ON DUPLICATE KEY UPDATE. Он заменяет горы приведенного вами кода.

  2. Александр permalink

    INSERT … ON DUPLICATE KEY UPDATE я бы не стал использовать. Эта команда увеличивает счетчик AUTO_INCREMENT столбцов даже в случае UPDATE. Если этот счетчик достигнет максимума (а это в слечае дефолтного SIGNET INT может произойти довольно быстро), обновление перестанет работать, хотя команда будет завершаться без ошибки. Неопытный админ будет в большом затруднении в процессе поиска проблемы.

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

%d такие блоггеры, как: