Курилка.co.ua
Orphus RSS kurilka.co.ua

Category

Archives

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

Author wmas wmas | Category Category MySQL

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

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

пример структуры БД: items и cats

У нас есть база данных 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. Постараюсь проиллюстрировать данную ситуацию вот таким образом:

группировка строк базы данных (GROUP BY)

Именно эти значения мы и должны присвоить полю 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. Спасибо за внимание.

Publish: Пятница Янв 30, 2009

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

feed for comments on this post

  • Комментарий #2497 author: Александр Reply (subscribed to comments)
    publish: Вторник Ноя 24, 2009 at 1:02 дп

    спасибо большое за статью, очень полезно. Но это в случаи если разделы 1-го уровня, а что в случаи много уровней структуры, при классической связи разделов parent_id как тут быть?

  • Комментарий #2498 author: wmas Reply
    publish: Вторник Ноя 24, 2009 at 2:01 дп

    2Александр: hi! По сути, для такого рода вариантов я использую отдельный класс, где загоняю в переменную данные таблицы категорий в виде ассоционного массива, т.е. двухмерных массив, строки и поля. Плюс вспомогательные переменные. Потом очень просто написать функцию которая для родительской категории подсчитывала бы суммарное значение cat_counter подкатегорий. Думаю, концепция ясна, надеюсь смог помочь :roll: ;-)

  • Комментарий #2499 author: Александр Reply (subscribed to comments)
    publish: Вторник Ноя 24, 2009 at 3:10 дп

    Спасибо за быстрый ответ. Для себя я писал скрипт, который за 1 раз рекурсивно проходил по всем разделам, и подсчитывал кол-во записей, вот только не знаю, как вы оценили бы этот код, его я загрузил на сайте: narod.ru/disk/15328134000/_update_count.php.html — посмотрите плз.

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

  • Комментарий #2500 author: wmas Reply
    publish: Вторник Ноя 24, 2009 at 11:32 дп

    2Александр: hi! По сути, ваш все относительно. Предложенный вами вариант тоже имеет право на жизнь, если уж он работает :mrgreen: Основная проблема, которая стояла передо мной, это количество запросов к базе данных — их я свёл к минимуму, даже при древовидной структуре каталогов. Однако, есть и существенный недостаток в виде объема потребляемой на обработку памяти. Таким образом существует ограничение на количество категорий. Однако, и в вашем варианте есть подгрузка данных в переменную массива, что тоже не есть хорошо. Но, если я правильно понял, вы синхронизируете счетчик для всех категорий? Т.е. и для родителей? Тогда это разовая нагрузка при синхронизации. У меня же идет постоянных подсчет, обеспечивая большую динамичность и ограничивая вероятность ошибки (в некотором роде). :roll: Ну а раз вы заинтересовались в моем варианте, попробую рассказать — следите за новыми записями :-)

  • Комментарий #2501 author: Александр Reply (subscribed to comments)
    publish: Вторник Ноя 24, 2009 at 4:51 пп

    спасибо, буду обязательно следить на вашем сайте, и было бы интересно посмотреть ваш вариант. В своём варианте, я сперва сделал подсчёт для каждого раздела, после этого выделил только родительские разделы, и через рекурсию суммировал кол-во записей с вложеных разделов обновил родительские. Правда запросов выходит много, сейчас проверил из 223 разделов, некоторые из разделов имеют 1-3 уровня вложенности, после запуска скрипта вышло 470 запросов. А если разделов будет больше 1 тыс. то и запросов будет гораздо больше. В принципе если тот запрос что в начале где происходит подсчёт записей для каждого раздела, переделать на вложенный запрос, что вы привели в этой статье, то можно думаю на половину сократить запросы.

  • Комментарий #2502 author: wmas Reply
    publish: Вторник Ноя 24, 2009 at 5:12 пп

    2Александр: уже написал заметку. По сути, в моем варианте идет только один запрос по синхранизации. Если всё таки синхронизировать по полной, то здесь думаю неизбежно количество запросов будет равно количеству категорий, ну или если поработать то количеству родительских категорий плюс один… где-то так.


Popular links

Copyright © since 2006 Курилка.co.ua,
powered by WordPress