MySQL: Синхронизация данных (пример с категориями и записями)

Здравствуйте, уважаемые читатели блога Курилка.co.ua. Работать с базой данных (БД) – удобно, но нагрузки требуют определенной оптимизации. В некоторых случаях для этих целей создаются дополнительные поля, которые хранят динамичные данные. В этой заметке я хотел бы остановиться на проблеме синхронизации счетчиков, на примере категорий и записей.

Рассмотрим вот такой простой пример структур БД:

У нас есть база данных items, в которой будут храниться какие-то записи. Каждая из записей соответствует той или иной категории из БД cats. Нас интересует поле cat_counter в БД cats, значением которой является количество записей в той или иной категории. Надеюсь всё более-менее понятно?

Шаг 1: подсчёт записей в категории

Подсчитать количество записей соответствующих той или иной категории достаточно просто:

SELECT COUNT(*) FROM items WHERE cat_id=num_cat_id

где num_cat_id – идентификатор категории.

Однако, такой способ потребует делать запрос к БД для каждой из категорий. Чтобы ограничиться только одним запросом, можно воспользуемся группировкой строк (ORDER BY):

SELECT cat_id, COUNT(cat_id) FROM items GROUP BY cat_id

Результатом запроса станет таблица, состоящая из полей cat_id и COUNT(cat_id). Второе, будет представлять собой количество строк с одинаковым значением cat_id. Постараюсь проиллюстрировать данную ситуацию вот таким образом:

Именно эти значения мы и должны присвоить полю cat_counter в базе данных cats.

Шаг 2: синхронизация данных (счетчиков)

Обновить значение поля cat_counter для той или иной категории достаточно просто:

UPDATE cats SET cat_counter=num_items WHERE cid=num_cat_id

где num_items – количество записей в категории num_cat_id.

Однако, такой способ потребует делать запрос к БД для каждой из категорий. Чтобы ограничиться только одним запросом можно попробовать включить подзапросы:

UPDATE cats SET cats.cat_counter = ( SELECT COUNT(*) FROM items WHERE items.cat_id=cats.cat_id )

Суть заключается в том, что в подзапросе мы подсчитываем записи соответствующие условию items.cat_id=cats.cat_id. Здесь наиболее интересным является поле cats.cat_id подставляемое из таблицы cats.

Можно попробовать и другой вариант, основанный на объединении таблиц:

UPDATE cats as t1
LEFT JOIN ( SELECT cat_id, COUNT(cat_id) as cat_counter FROM items GROUP BY cat_id ) as t2
ON t1.cat_id=t2.cat_id
SET t1.cat_counter=t2.cat_counter

Суть заключается в том, что БД cats (алиас t1) как бы объединяется с промежуточной t2, на основе условия t1.cat_id=t2.cat_id. Выглядеть это может так:

Вот собственно и всё. Согласен, ничего сложного здесь нет, да и без нюансов не обойдется. Однако, разобравшись с предложенным методом можно значительно уменьшить количество запросов к БД и спокойно пользоваться вспомогательными полями, вроде cat_counter. Спасибо за внимание.