Форум пользователей MySQL

Questor · 17.01.2010 20:20:34

Дано: упрощенная база данных интернет-магазина.
Вопрос: верно ли составлена структура БД?
Найти: единый алгоритм объединения всех таблиц для выборки различных результатов.

Описание БД.
Для магазина существуют различные профили, внутри которых содержатся разделы магазина. Каждый раздел может принадлежать только одному профилю. Так же в магазине есть список таблиц размеров (например: размеры для Европы, США и т. п.). Каждый товар может принадлежать только одному разделу и только одной таблице размеров. Каждый цвет товара может принадлежать только одному товару. Для каждого цвета есть доступные размеры товара, которые принадлежат той же таблице размера, что и сам товар. Каждый объект можно отключить (поле SWITCH).

Структура базы данных:

Проблема состоит в том, что при запросе различных результатов необходимо заново составлять объединения для всех таблиц. Основная задача: существует ли какой-то уникальный алгоритм объединения таблиц, которые со связями представлены в виде графа?

Объясняю на примерах.

Текущая задача. Выбрать одним запросом все товары из заданной категории, для каждого товара подсчитать количество доступных цветов. Учитывать только все включенные элементы.

Предлагаю на рассмотрение такое решение:

select

    I.*,

    count(distinct C.ColorID) as ColorsCount

from

    item as I

left join color as C on C.ItemID=I.ItemID

left join section as S on S.SectionID=I.SectionID

left join size_table as T on T.TableID=I.TableID

left join profile as P on P.ProfileID=S.ProfileID

left join item_size as ISZ on ISZ.ColorID=C.ColorID

left join size as SZ on (SZ.TableID=T.TableID and SZ.SizeID=ISZ.SizeID)

where

    S.SectionID=10

    and

    I.SWITCH=1

    and

    C.SWITCH=1

    and

    S.SWITCH=1

    and

    T.SWITCH=1

    and

    P.SWITCH=1

    and

    ISZ.SWITCH=1

    and

    SZ.SWITCH=1

group by

    I.ItemID

Казалось бы, все отлично. Да не тут-то было. Попробуем задачу усложнить, добавив к результату подсчет количества возможных размеров для товара. Т. е., по сути нам нужно еще узнать сколько размеров содержит таблица размеров, к которой принадлежит каждый товар. В этом случае последние 2 таблицы в объединении нужно менять местами.

select

    I.*,

    count(distinct C.ColorID) as ColorsCount,

    count(distinct SZ.SizeID) as AllowedSizesCount

from

    item as I

left join color as C on C.ItemID=I.ItemID

left join section as S on S.SectionID=I.SectionID

left join size_table as T on T.TableID=I.TableID

left join profile as P on P.ProfileID=S.ProfileID

left join size as SZ on SZ.TableID=T.TableID

left join item_size as ISZ on (ISZ.ColorID=C.ColorID and ISZ.SizeID=SZ.SizeID)

where

    S.SectionID=10

    and

    I.SWITCH=1

    and

    C.SWITCH=1

    and

    S.SWITCH=1

    and

    T.SWITCH=1

    and

    P.SWITCH=1

    and

    ISZ.SWITCH=1

    and

    SZ.SWITCH=1

group by

    I.ItemID

В первом примере я взял с потолка предположение (из-за недостаточного знания теории), что таблицы нужно обойти в порядке удаления вершины от заданной, если таблицы -- вершины, связи -- ребра, причем ненаправленные.

http://uaimages.com/thumbs/889374cheme2.jpg

После решения дополнения к задаче пришлось сдаться. А ведь задачи для проекта могут быть различными: и выборка доступных размеров для каждого цвета, и сколько таблиц соответствуют каждому разделу (или наборот) и т. п.

Так вот, еще раз основной вопрос: в каком порядке нужно обходить таблицы в объединении? Кроме того, с какой таблицы начинать? Посоветуйте, может, кто-то знает, где можно почерпнуть достаточно теории. Спасибо.

vasya · 17.01.2010 23:55:54

Логично начинать обход с той таблицы, которая выдает наименьшее кол-во записей при наложении условий.
Сначала ваш запрос попадает в оптимизатор, который строит план выполнения запроса, исходя из правила, указанного выше. План выполнения можно посмотреть с помощью команды EXPLAIN. Он может и не совпадать с тем порядком объединения таблиц, который вы указали в запросе. Для указания нужного вам порядка объединения таблиц нужно использовать STRAIGHT_JOIN, см http://dev.mysql.com/doc/refman/5.0/en/join.html

Questor · 18.01.2010 12:03:41

vasya написал:
Сначала ваш запрос попадает в оптимизатор, который строит план выполнения запроса, исходя из правила, указанного выше.

Вы имеете в виду теорию графов, обход вершин по мере удаленности?

vasya · 18.01.2010 20:15:58

С теорией графоф не знаком.

Я имею в виду следующее.

SELECT * FROM t1 JOIN t2 USING(a) WHERE t1.b='x' AND t2.c='y';

Если условию t2.c='y' соответствует одна строка. а условию t1.b='x' много, то оптимизатор начнет объединение таблиц с t2.

Форум пользователей MySQL

#1 17.01.2010 20:20:34

Алгоритм объединений или расширенное использование JOIN

#2 17.01.2010 23:55:54

Re: Алгоритм объединений или расширенное использование JOIN

#3 18.01.2010 12:03:41

Re: Алгоритм объединений или расширенное использование JOIN

vasya написал:

#4 18.01.2010 20:15:58

Re: Алгоритм объединений или расширенное использование JOIN

Board footer