SQL - первичный ключ таблицы many-many
Этот вопрос подходит после чтения комментария в этом вопросе:
Когда Вы составляете many-many таблицу, необходимо ли создать составной первичный ключ на двух столбцах внешнего ключа, или создать ли автоинкрементный суррогатный "идентификационный" первичный ключ и просто поместить ли индексы на два столбца FK (и возможно ограничение на уникальность данных)? Каковы последствия на производительности для вставки нового records/re-indexing в каждом случае?
В основном, это:
PartDevice
----------
PartID (PK/FK)
DeviceID (PK/FK)
по сравнению с этим:
PartDevice
----------
ID (PK/auto-increment)
PartID (FK)
DeviceID (FK)
Комментатор говорит:
при создании этих двух идентификаторов, которые означает PK, таблица физически отсортирована на диске в том порядке. Таким образом, если мы вставляем (Part1/Device1), (Part1/Device2), (Part2/Device3), затем (Часть 1/Device3), база данных должна будет разбить таблицу и вставить последнюю между записями 2 и 3. Для многих записей это становится очень проблематичным, поскольку это включает перестановку сотни, тысячи или миллионы записей каждый раз, когда каждый добавляется. В отличие от этого, автопостепенное увеличение PK позволяет новым записям прикрепляться на в конец.
Причина, которую я спрашиваю, состоит в том, потому что я всегда был склонен сделать составной первичный ключ без суррогатного столбца автоприращения, но я не уверен, более ли суррогатный ключ на самом деле производителен.
3 ответа
Я не вижу реальных преимуществ суррогатного ключа с помощью простого отображения «многие ко многим» из двух столбцов. Наличие первичного ключа на (col1, col2) гарантированно уникально (при условии, что ваши значения col1 и col2 в ссылочных таблицах уникальны) и отдельный индекс на (col2, col1) перехватит те случаи, когда противоположный порядок будет выполняться быстрее. Суррогат - пустая трата места.
Вам не нужны индексы для отдельных столбцов, так как таблица должна использоваться только для объединения двух таблиц, на которые есть ссылки.
На мой взгляд, этот комментарий, на который вы ссылаетесь в вопросе, не стоит тех электронов, которые он использует. Похоже, автор думает, что таблица хранится в массиве, а не в чрезвычайно высокопроизводительной сбалансированной многосторонней древовидной структуре.
Во-первых, нет необходимости сохранять или получать отсортированную таблицу , только индекс. И индекс не будет сохранен последовательно, он будет сохранен эффективным образом, чтобы его можно было быстро получить.
Кроме того, подавляющее большинство таблиц базы данных читаются гораздо гораздо чаще, чем записываются. Это делает все, что вы делаете на стороне выбора, гораздо более актуальным, чем что-либо на стороне вставки.
Нет суррогатного ключа не требуется для таблиц ссылок.
Один PK ON (COL1, COL2) и еще один уникальный индекс ON (COL2, COL1) - это все, что вам нужно
, если вы не используете ORM, который не может справиться и диктует ваш дизайн БД для вас ...
Редактировать: Я ответил то же самое здесь: SQL: Вам нужен автоматически инкрементный первичный ключ для многих столов?
Самый короткий и самый прямой способ ответить на ваш вопрос, в том, чтобы сказать, что воздействие производительности, если две таблицы вы связываете, не имеют последовательных первичных клавиш. Когда вы заявили / цитируемые, индекс для таблицы ссылок либо станет фрагментированным, либо СУБД будет работать усерднее для вставки записей, если таблица ссылок не имеет собственного последовательного первичного ключа. Это причина, по которой большинство людей ставят последовательно увеличивая первичный ключ на таблицах ссылок.