Как правильно использовать ключи MySQL?

Question

[gracer]

Решив впервые подойти с головой к проектированию БД для проекта, по неопытности столкнулся с проблемой.
Есть таблица подписчиков subscribers, в которой есть колонки userid INT UNSIGNED и subsriberid INT UNSIGNED. В колонках лежат id юзеров из таблицы users(один пользователь может подписаться на многих и наоборот, многие на одного). Эта таблица subscribers будет чаще использоваться на выборку, чем на изменение.
Проблема в том, что я понимаю, что для точного определения записи в таблицы мне достаточно этих двух колонок, но как primary key ни одна из них не подойдёт. Выборки будут по userid и subsсriberid. Имеет ли смысл добавлять ещё 1 колонку типа id только для ключа, который скорее всего понадобится только для удаления записи? Или просто навесить индексы на эти колонки и не загоняться?
Спасибо!

Answer 1

[Kerman]

Primary key нужно повесить на ОБЕ колонки. Заодно это не даст возможности одному юзеру дважды подписаться на другого. Отдельный id для этой таблицы не нужен. Два индекса тоже не нужны.

Comments

[gracer]

я правильно понимаю, что это называется составной ключ и делается как PRIMARY KEY(userid, subscriberid) ?

[Данила Риджи]

Да, вы все правильно поняли.

[Александр]

@gracer стоит заметить, что данный составной ключ даёт выигрыш ТОЛЬКО при запросе вида WHERE userid = xxx AND subscriberid = yyy. Если будете искать только по userid, например, то всё же нужен будет индекс отдельно на данное поле.

[gracer]

@kryoz т.е. нужно будет составной примари + 2 индекса для выборок по каждой колонке? Надо ведь будет смотреть на кого пользователь подписан и кто подписан на него.

[Александр]

@gracer похоже что да, в итоге 3 индекса получится

[kazmiruk]

@kryoz не вводите в заблуждение человека. Составной индекс вида (userid, subscriberid) будет использоваться при запросах WHERE userid = xxx AND subscriberid = yyy и при запросах WHERE userid = xxx и не будет использоваться только при запросах WHERE subscriberid = yyy. Это связано со структурой индекса в виде бинарного дерева. Для индекса на поля (поле1, поле2, поле3) индекс будет работать для запросов WHERE поле1 AND поле2 AND поле3, WHERE поле1 AND поле2, WHERE поле1, но не будет работать для: WHERE поле2 AND поле3, WHERE поле3. Для запроса WHERE поле1 AND поле3 индекс будет задействован частично (по поле1 будет произведен поиск с использованием индекса, а поле3 уже фулсканом). Т.е. в индексе важен порядок!

[Александр]

@kazmiruk вы правы, перепроверил документацию.

[gracer]

@kazmiruk т.е. будет достаточно составной повесить на оба (userid, subscriberid) + 1 индекс на subscraberid?

[kazmiruk]

@gracer да, именно так. В таком случае вы сможете делать запросы по любому полю (или сразу по двум) и использовать индекс.

Answer 2

[svd71]

Вы правильно начали рассуждать, но не правильно закончили. Первичный ключ может состоять из нескольких полей. Только все поля должны быть NOT NULL.

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