Первичный ключ / Кластеризованный ключ для Объединяющих таблиц
Скажем, у нас есть Таблица product и таблица Order и (объединяющая таблица) ProductOrder.
ProductOrder будет иметь ProductID и OrderID.
В большинстве наших систем эти таблицы также имеют столбец автонумерации, названный идентификатором.
Какова лучшая практика для размещения первичного ключа (и для этого кластеризировал ключ)?
Если я сохраняю первичный ключ поля ID и создаю некластерный индекс для пары внешнего ключа (ProductID и OrderID)
Или если я поместил первичный ключ пары внешнего ключа (ProductID и OrderID) и поместил некластерный индекс на столбец ID (если даже необходимый)
Или... (умный комментарий одним из Вас :))
4 ответа
Я знаю, что эти слова могут заставить вас съежиться, но «это зависит от обстоятельств».
Скорее всего, вы хотите, чтобы порядок был основан на ProductID и / или OrderId, а не столбец autonumber (суррогат), поскольку autonumber не имеет естественного значения в вашей базе данных. Вероятно, вы захотите заказать объединенную таблицу по тому же полю, что и родительская таблица.
Сначала разберитесь, почему и как вы используете идентификатор суррогатного ключа ; от этого часто будет зависеть, как вы его индексируете. Я предполагаю, что вы используете суррогатный ключ, потому что вы используете некоторую структуру, которая хорошо работает с ключами одного столбца. Если нет конкретной причины разработки , то для таблицы соединения я бы упростил проблему и просто удалил идентификатор автонумерации, если это не принесет никаких других преимуществ . Первичный ключ становится (ProductID, OrderID). Если нет, вам нужно хотя бы убедиться, что ваш индекс в кортеже (ProductID, OrderID) уникален, чтобы сохранить целостность данных.
Кластерные индексы хороши для последовательных сканирований / объединений, когда для запроса требуются результаты в том же порядке, в котором упорядочен индекс. Итак, посмотрите на свои шаблоны доступа, выясните, по каким ключ (ы), которые вы будете выполнять последовательную выборку / сканирование из нескольких строк, и с помощью какого ключа вы будете выполнять произвольный доступ к отдельной строке и создать кластеризованный индекс ключа, который вы будете сканировать чаще всего, и некластеризованный индекс ключа ключа, который вы будете использовать для произвольного доступа. Вы должны выбрать один или другой, поскольку вы не можете кластеризовать оба.
ПРИМЕЧАНИЕ: Если у вас есть противоречащие друг другу требования, существует метод («уловка»), который может помочь. Если все столбцы в запросе находятся в индексе, то этот индекс является таблицей-кандидатом, которую ядро базы данных будет использовать для удовлетворения требований запроса. Вы можете использовать этот факт для хранения данных более чем в одном порядке, даже если они конфликтуют друг с другом. Просто помните о плюсах и минусах добавления дополнительных полей в индекс и принимайте осознанное решение после понимания характера и частоты запросов, которые будут обрабатываться.
Я всегда предпочитал создавать автономные номера для первичных ключей. Затем я создаю уникальный индекс для двух внешних ключей, чтобы они не дублировались.
Причина, по которой я это делаю, заключается в том, что чем больше я нормализую свои данные, тем больше ключей я должен использовать в соединениях. Я закончил с проектами с шестью-семью уровнями в глубину, и если я использую ключи, переходящие с одного уровня на другой, я потенциально могу получить ключи n ^ 2 в объединении.
Попробуйте убедить моих разработчиков SQL использовать все это для одного запроса, и я им действительно понравится .
Я все просто.
Правильный и единственный ответ:
- Первичный ключ -
('orderid', 'productid') - Другой индекс на
('productid', 'orderid') - Любой из них может быть кластеризован, но PK по умолчанию
Потому что:
- Вам не нужен индекс для
orderidили] productidтолько: оптимизатор будет использовать один из индексов - Скорее всего, вы будете использовать таблицу «обоими» способами
- Вам не нужен суррогатный ключ, потому что они уже есть в связанных таблицах . Таким образом, 3-я колонка тратит впустую пространство.
Это похоже на динамическую систему, в которую будет добавлено много заказов. Следовательно, кластеризованный индекс должен находиться в вашем столбце с автоматической нумерацией.
Вы можете сделать индексирование первичного ключа и поместить другой уникальный индекс в пару столбцов. Или вы можете сделать пару столбцов первичным (но некластеризованным) ключом.
Выбор использования первичного ключа или уникального индексного ключа зависит от вас. Но я хотел бы убедиться, что тот, который сгруппирован , предназначен для вашего столбца автонумерации.