Mysql — как оптимизировать связь Many To Many?

Question

[Zohei]

Добрый день. Есть ряд вопросов по оптимизации SELECT запросов, надеюсь на вашу помощь)

Имеется две таблицы: product (товары) и product_relation (сопутствующие товары). Связь - многие ко многим.
Какая схема будет более оптимальной?

CREATE TABLE `product_relation`(
  `id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `product_id` Int(11) NOT NULL,
  `relation_id` Int(11) NOT NULL,
  `is_del` ENUM("n", "y") NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE = InnoDB;

или

CREATE TABLE `product_relation`(
  `product_id` Int(11) NOT NULL,
  `relation_id` Int(11) NOT NULL,
  `is_del` ENUM("n", "y") NOT NULL,
 PRIMARY KEY (`product_id`,`relation_id`)
) ENGINE = InnoDB;

Какие индексы строить в том или ином случаем?
Нужен ли составной UNIQUE INDEX (`product_id`,`relation_id`) или INDEX (`product_id`) для первой схемы?
Нужен ли FOREIGN KEY, если данные из таблиц никогда не удаляются (ставится пометка is_del = "y")?

Спасибо)

Comments

[Zohei]

примеры запросов:

SELECT * from product_relation where product_id=N;
SELECT * from product_relation where relation_id=N;

[Zohei]

Спасибо за ответы, вторая схема действительно оптимальней и работать с ней куда удобней.

Answer 1

[Melkij]

Индексы строятся только под запросы. Если запросов, использующих этот индекс нет - то индекс только мешает работе.

Констрейты (fk и unique) - ставятся в зависимости от требования к данным, при этом мешают программисту делать глупости. Потому их лучше всегда ставить, пока не будет сделан обоснованный вывод, что их надо убрать.

По схемам - вторая будет оптимальнее. Меньше объём рабочего набора - меньше i/o. В большинстве случаев, судя по всему, будет поиск по первичному ключу - ещё меньше i/o, т.к. в innodb данные кластеризованы по первичному ключу.
И скорей всего забыли указать unsigned, это ведь наверняка ссылки на автоинкременты.

Answer 2

[Александр]

По индексам в MySQL есть хорошая статься тут.
Как пишут выше - внешние ключи нужны для сохранения связей между таблицей и предотвращения разрыва связей объектов. Если кто-то удали строчку из одной таблицы, а id о ней останется в другой - у вас будет бедлам и хаос, куча мусора и с разрастанием таблиц - просидание производительности. Про это можно прочитать тут.

Первую схему забудьте для связей многие-ко-многим.

Comments

[Zohei]

А есть ли смысл в айдишнике (тот который `id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) для связи один-ко-многим, к примеру, для связи пользователя со списком емейлов? Или все же есть ситуации, когда он может быть необходим?

[Александр]

Для связи может быть и можно оставить. Но такая структура применяется для сокращения избыточности данных, таким образом, чтобы данные не дублировались. То есть в вашем случае таблицу с адресами электронной почты принято называть справочником и она в минимальной своей форме будет иметь вид:
`id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
`address` NVARCHAR(255) NOT NULL

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