Первичный ключ таблицы SQL-many-to-many
этот вопрос возникает после прочтения комментария в этом вопросе:
когда вы создаете таблицу "многие ко многим", должны ли вы создать составной первичный ключ на двух столбцах внешнего ключа или создать суррогатный первичный ключ" ID " с автоматическим приращением и просто поместить индексы на два столбца FK (и, возможно, уникальное ограничение)? Каковы последствия для производительности при вставке новых записей/повторной индексации в каждой из них случае?
в основном, это:
PartDevice
----------
PartID (PK/FK)
DeviceID (PK/FK)
и так:
PartDevice
----------
ID (PK/auto-increment)
PartID (FK)
DeviceID (FK)
комментатор говорит:
создание двух идентификаторов PK означает
таблица физически сортируется на диске
в этом порядке. Так что если мы вставим
(Часть1/Устройство1), (Часть1/Устройства2),
(Часть 2 / Device3), затем (Часть 1 / Device3)
базу данных придется разбить
разделите таблицу и вставьте последнюю
между входами 2 и 3. Для многих
записи, это становится очень проблематичным
как он включает в себя перетасовку сотен,
тысячи или миллионы записей
каждый раз, когда добавляется. По контрасту,
с автоматическим приращением ПК позволяет новым
записи, которые будут прикреплены к концу.
причина, по которой я спрашиваю, заключается в том, что я всегда был склонен делать составной первичный ключ без суррогатного столбца автоматического приращения, но я не уверен, что суррогатный ключ на самом деле более эффективен.
5 ответов:
С помощью простого двухколоночного сопоставления "многие ко многим" я не вижу реального преимущества в наличии суррогатного ключа. Имея первичный ключ
(col1,col2)гарантируется уникальный (при условии, что вашcol1иcol2значения в ссылочных таблицах уникальны) и отдельный индекс на(col2,col1)будет ловить те случаи, когда противоположный порядок будет выполняться быстрее. Суррогат-это пустая трата пространства.вам не понадобятся индексы для отдельных столбцов, так как таблица должна использоваться только для объединения две таблицы вместе.
этот комментарий, на который вы ссылаетесь в вопросе, не стоит электронов, которые он использует, на мой взгляд. Похоже, что автор считает, что таблица хранится в массиве, а не в чрезвычайно высокопроизводительной сбалансированной многоходовой древовидной структуре.
для начала, это никогда не нужно хранить или получить на стол отсортированы, только индекс. И индекс не будет хранящиеся последовательно, он будет храниться в эффективный способ, который нужно мочь быть восстановленным быстро.
кроме того, подавляющее большинство таблиц базы данных читать далеко чаще, чем написано. Это делает все, что вы делаете на стороне выбора, гораздо более актуальным, чем что-либо на стороне вставки.
для таблиц ссылок не требуется суррогатный ключ.
один PK on (col1, col2) и другой уникальный индекс on (col2, col1) - это все, что вам нужно
Если вы не используете ORM, который не может справиться и диктует свой дизайн БД для вас...
Edit: я ответил То же самое здесь: SQL: вам нужен автоматический инкрементный первичный ключ для многих-многих таблиц?
инкрементный первичный ключ может потребоваться, если на таблицу ссылаются. В таблице "многие ко многим" могут быть детали, которые необходимо извлечь из другой таблицы с помощью инкрементного первичного ключа.
PartDevice ---------- ID (PK/auto-increment) PartID (FK) DeviceID (FK) Other Detailsлегко вытащить "другие детали" с помощью PartDevice.ID как и сам ФК. Таким образом, необходимо использовать инкрементный первичный ключ.
самый короткий и самый прямой способ, которым я могу ответить на ваш вопрос, - это сказать, что будет влияние на производительность, если две таблицы, которые вы связываете, не имеют последовательных первичных ключей. Как вы указали/процитировали, индекс для таблицы ссылок либо станет фрагментированным, либо СУБД будет сложнее вставлять записи, если таблица ссылок не имеет собственного последовательного первичного ключа. Именно по этой причине большинство людей помещают последовательно увеличивающийся первичный ключ в таблицы ссылок.
таким образом, кажется, что если единственное задание-связать две таблицы, лучшим PK будет двухколоночный PK.
но если он служит другим целям, то добавьте еще один NDX в качестве PK с внешними ключами и вторым уникальным индексом.
индекс или PK-это лучший способ убедиться, что нет дубликатов. PK позволяет таким инструментам, как Microsoft Management Studio, выполнять часть работы (создавать представления) для вас
Comments