Mvs3d.ru: блог Web-разработчика

При сохранении различной статистики, довольно часто встречается задача обновить существующую запись а если она не существует – создать.

Например, существует таблица “test” с полями “day” (дата, без времени) и “counter” (число – счетчик). Есть уникальный индекс по полю “day”. Надо его увеличивать при наступлении какого-то внешнего события (у меня это downloads).

Довольно часто я применял такой метод – делаем SELECT (ищем запись в базе), если ее нет – INSERT, иначе – UPDATE. В последнее время стал использовать функцию PHP mysql_affected_rows(), а вчера увидел еще одно решение – всегда делается вставка, и если произошла ошибка дублирования ключа – выполняем UPDATE.

Сначала я подумал что это решение ужасно, но потом сильно задумался.

Решил опросить коллег, вызвался помочь 1 человек, порекомендовал mysql_affected_rows(), но без всяких пояснений. Тогда я закинул вопрос в сервис Google “Вопросы и ответы”, там мне рассказали что в MySQL существует встроенный оператор для осуществления того что мне надо -
“INSERT INTO … ON DUPLICATE KEY UPDATE”.

Как оказалось он наиболее эффективен из всех решений. Продублирую сюда свои выкладки:

Я написал тестовый скриптик с обновлением счетчика 100тыс раз а таблицу заполнил случайными данными (2200записей).

Тестировалсь 4 метода:

1. Встроенный синтаксис MySQL (INSERT INTO test(day, counter) VALUES (…) ON DUPLICATE KEY UPDATE…
2. Внутри PHP пытаемся делать update, если mysql_affected_rows()<>1 то вставляем новую запись.
3. Делаем SELECT (ищем есть ли в таблице запись на нужный нам Day), если запись найдена – update, иначе – insert (именно этот способ я чаще всего использвал в своих приложениях, поэтому интересно было сравнить).
4. Делаем INSERT, если вернулась ошибка mysql DUPLICATE KEY выполняем update.

Скриптик написал с использованием нативных функций PHP mysql_.. Выполнил его 3 раза, на компе остановил все тяжелые задачи, даже проигрывание музыки
Версия PHP 4.3.11, mysql – 4.1.11-nt. Конектился к localhost.

Результаты такие:

первый запуск:

Start of testing method1..finished, executed time: 10.931 (100000 iterations)
Start of testing method2..finished, executed time: 11.207 (100000 iterations)
Start of testing method3..finished, executed time: 27.775 (100000 iterations)
Start of testing method4..finished, executed time: 20.738 (100000 iterations)

второй запуск:

Start of testing method1..finished, executed time: 10.354 (100000 iterations)
Start of testing method2..finished, executed time: 11.048 (100000 iterations)
Start of testing method3..finished, executed time: 27.807 (100000 iterations)
Start of testing method4..finished, executed time: 20.449 (100000 iterations)

третий запуск:

Start of testing method1..finished, executed time: 10.571 (100000 iterations)
Start of testing method2..finished, executed time: 11.101 (100000 iterations)
Start of testing method3..finished, executed time: 28.179 (100000 iterations)
Start of testing method4..finished, executed time: 20.634 (100000 iterations)

(Время дано в секундах)

Вывод:

Для подобной операции наиболее эффективна встроенная команда, хотя метод с mysql_affected_rows проигрывает совсем немного. Наиболее часто используемый мной способ почти в 3 раза медленней, а тот код, с которого я начал разбирательство – в 2 раза.

PS Кстати, использование сервиса от Google “Вопросы и ответы” было экспериментальным, – хотел проверить насколько он эфективен в работе. И мне понравилось. Ответ я получил буквально в течении часа. Видимо сервис очень популярен (на данный момент у меня 95 просмотров моего вопроса). Так что всем рекомендую его использовать, и не только для вопросов но и ответов