Как вам ваши первичные ключи?
В довольно оживленной дискуссии в моей команде меня заставили думать, что большинство людей предпочитают в качестве первичных ключей. У нас были следующие группы-
- Int / BigInt, автоинкремент которого является достаточно хорошими первичными ключами.
- Должно быть не менее 3 столбцов, составляющих первичный ключ.
- Идентификатор, идентификатор GUID и понятные человеку строки идентификаторы должны обрабатываться по-разному.
Какой лучший подход для ПК? Было бы здорово, если бы вы могли обосновать свое мнение. Есть ли лучший подход, что выше?
РЕДАКТИРОВАТЬ: У кого-нибудь есть простой пример / алгоритм для генерации удобочитаемых идентификаторов для строк, который хорошо масштабируется?
26 ответов
Если вы собираетесь выполнять какую-либо синхронизацию между базами данных с периодически подключаемыми приложениями, то вы должны использовать GUID для своих первичных ключей. Это своего рода боль для отладки, так что кроме этого случая, я склонен придерживаться целых значений этого автоинкремента.
Автоинкрементные интты должны быть вашими по умолчанию, и их использование не должно быть оправдано.
Я не вижу ответа, который указывает (что я считаю) действительно фундаментальным моментом, а именно: первичный ключ - это то, что гарантирует, что вы не получите две записи в таблице для одной и той же сущности реального мира (так как по образцу базы данных). Это наблюдение помогает установить, что является хорошим и что является плохим выбором для первичного ключа.
Например, в таблице названий и кодов штатов (США) первичным ключом может быть либо имя, либо код - они представляют собой два разных ключа-кандидата, и один из них (обычно более короткий - код) выбирается как основной ключ. В теории функциональных зависимостей (и объединяющих зависимостей - от 1NF до 5NF) ключи-кандидаты имеют решающее значение, а не первичный ключ.
В качестве контрпримера, человеческие имена обычно делают неправильный выбор для первичного ключа. Есть много людей, которые называют "Джон Смит" или другие подобные имена; даже принимая во внимание вторые имена (помните: не у всех есть такие - например, у меня нет), есть много возможностей для дублирования. Следовательно, люди не используют имена в качестве первичных ключей. Они изобретают искусственные ключи, такие как номер социального страхования (SSN) или номер сотрудника, и используют их для обозначения личности.
Идеальный первичный ключ - короткий, уникальный, запоминающийся и естественный. Из этих характеристик уникальность обязательна; остальные должны сгибаться с учетом ограничений реальных данных.
Поэтому, когда дело доходит до определения первичного ключа данной таблицы, вам нужно посмотреть, что представляет эта таблица. Какой набор или набор значений столбцов в таблице однозначно идентифицирует каждую строку в таблице? Это ключи-кандидаты. Теперь, если каждый ключ-кандидат состоит из 4 или 5 столбцов, вы можете решить, что они слишком неуклюжи, чтобы сделать хороший первичный ключ (в основном из-за краткости). В этих обстоятельствах вы можете ввести суррогатный ключ - искусственно сгенерированный номер. Очень часто (но не всегда) достаточно простого 32-разрядного целого числа для суррогатного ключа. Затем вы назначаете этот суррогатный ключ в качестве первичного ключа.
Тем не менее, вы все равно должны убедиться, что другие ключи-кандидаты (для суррогатного ключа также является ключом-кандидатом, а также выбранным первичным ключом) все поддерживаются в качестве уникального идентификатора - обычно путем наложения уникального ограничения на эти наборы столбцов.
Иногда людям трудно определить, что делает строку уникальной, но для этого должно быть что-то, потому что простое повторение фрагмента информации не делает его более правдивым. И если вы не будете осторожны и получите две (или более) строки, предназначенные для хранения одной и той же информации, и вам потребуется обновить информацию, существует опасность (особенно если вы используете курсоры), что вы обновите только одну строку а не каждая строка, поэтому строки не синхронизированы, и никто не знает, какая строка содержит правильную информацию.
Это довольно жесткая точка зрения, в некоторых отношениях.
У меня нет особых проблем с использованием GUID, когда они необходимы, но они имеют тенденцию быть большими (как в 16-64 байтах), и они используются слишком часто. Очень часто достаточно хорошего 4-байтового значения. Использование GUID, в котором 4-байтового значения будет достаточно, приводит к потере дискового пространства и замедляет даже индексированный доступ к данным, поскольку на страницу индекса меньше значений, поэтому индекс будет глубже, и для того, чтобы добраться до Информация.
Это только религиозный вопрос, потому что люди ищут универсальный правильный ответ. Тот факт, что и ваша команда, и этот поток SO демонстрируют такое большое несогласие, должны быть подсказкой, что есть веские причины использовать все решения, которые вы описываете, в разных обстоятельствах.
- Суррогатные ключи полезны, когда никакой другой атрибут или набор атрибутов в таблице не подходит для уникальной идентификации строк.
- Естественные ключи предпочтительнее, когда это возможно, чтобы сделать таблицу более удобочитаемой. Естественные ключи также позволяют внешнему ключу в зависимой таблице содержать действительное значение вместо суррогатного идентификатора. Например, когда вам нужно хранить
state(Калифорния, Техас, Нью-Йорк) вы также можете использоватьchar(2)естественный ключ вместо int. - При необходимости используйте составные первичные ключи. Не добавлять "
id"Суррогатный ключ без необходимости, когда существует совершенно хороший составной ключ (это особенно верно в таблицах" многие ко многим "). Мандат для ключа из трех столбцов в каждой таблице - абсолютная чепуха. - GUID - это решение, когда вам нужно сохранить уникальность на нескольких сайтах. Они также удобны, если вам нужно, чтобы значения в первичном ключе были уникальными, но не упорядоченными или последовательными.
- INT против BIGINT: редко для таблицы требуется 64-битный диапазон для первичных ключей, но с увеличением доступности 64-битного оборудования это не должно быть обременительным и дает больше уверенности в том, что вы не переполнитесь. INT, конечно, меньше, поэтому, если пространство стоит дороже, это может дать небольшое преимущество.
Мне нравится блог The Database Programmer как источник информации такого рода.
3 столбца для первичного ключа? Я бы сказал, что столбцы должны иметь соответствующие уникальные ограничения в соответствии с требованиями бизнес-правил, но у меня все равно будет отдельный суррогатный ключ. Составные ключи означают, что бизнес-логика входит в ключ. Если логика меняется, вся ваша схема облажалась.
Немного не по теме, но я чувствую себя обязанным...
Если ваш первичный ключ - GUID, не делайте его кластеризованным индексом. Поскольку GUID не являются последовательными, данные будут переупорядочены на диске почти во время каждой вставки. (Тьфу.) При использовании GUID в качестве первичных ключей они должны быть некластерными индексами.
Я думаю, что использование слова "Primary" в фразе "Primary" Key в реальном смысле вводит в заблуждение.
Во-первых, используйте определение, что "ключ" - это атрибут или набор атрибутов, которые должны быть уникальными в таблице,
Тогда наличие любого ключа служит нескольким часто несовместимым целям.
- Использовать в качестве условий соединения с одной или несколькими записями в дочерних таблицах, которые имеют отношение к этой родительской таблице. (Явное или неявное определение внешнего ключа в этих дочерних таблицах)
- (связанный) Обеспечение того, чтобы дочерние записи имели родительскую запись на родительской вкладке;e (дочерняя таблица FK должна существовать как ключ в родительской таблице)
Для увеличения производительности запросов, которым необходимо быстро найти конкретную запись / строку в таблице.
Обеспечить согласованность данных, предотвращая вставку дубликатов строк, представляющих один и тот же логический объект, в таблицу. (Это часто называют "естественным" ключом и должен состоять из атрибутов таблицы (сущности), которые являются относительно инвариантными.)
Ясно, что любой не имеющий смысла, неестественный ключ (например, GUID или автоматически сгенерированное целое число) совершенно не способен удовлетворить #4.
Но часто, во многих (большинстве) таблицах, полностью естественный ключ, который может предоставить # 4, часто состоит из нескольких атрибутов и будет чрезмерно широким или настолько широким, что использование его в целях #1, #2 или #3 приведет к неприемлемому последствия производительности.
Ответ прост. Используйте оба. Используйте простой авто-генерирующий интегральный ключ для всех объединений и FK в других дочерних таблицах, но убедитесь, что у каждой таблицы, которая требует согласованности данных (очень мало таблиц), есть альтернативный естественный уникальный ключ, который предотвратит вставку несогласованных строк данных. Плюс, если у вас всегда есть оба, тогда все возражения против использования естественного ключа (что, если он изменится? Я должен менять каждое место, на которое он ссылается как FK), становятся спорными, поскольку вы не используете его для этого... Вы используете его только в одной таблице, где это PK, чтобы избежать противоречивых дублирующих данных...
Что касается идентификаторов GUID, будьте очень осторожны при их использовании, так как использование направляющих в индексе может привести к фрагментации индекса. Наиболее распространенные алгоритмы, используемые для их создания, помещают "случайную" часть guid в наиболее значимые битовые позиции... Это увеличивает потребность в регулярной дефрагментации индекса / переиндексации при добавлении новых строк.
Я всегда хожу с суррогатным ключом. Суррогатный ключ (обычно столбец идентификаторов, автоинкремент или GUID) - это ключ, в котором ключ отсутствует в самих данных. С другой стороны, естественный ключ - это тот, который сам по себе уникально идентифицирует строку. Насколько я могу судить по жизни, реальных натуральных ключей почти нет. Даже такие вещи, как SSN в Соединенных Штатах, не являются естественным ключом. Составные первичные ключи - это катастрофа, ожидающая своего возникновения. Вы не можете редактировать какие-либо из этих данных (что является основным недостатком любого естественного ключа, составного или нет), но хуже всего то, что с составным ключом теперь вам нужно увековечить эти данные ключа в каждой связанной таблице. Что за гигантские отходы.
Теперь для выбора суррогатного ключа я использую столбцы идентификаторов (в основном я работаю в MS SQL Server). GUID слишком велики, и Microsoft не рекомендует использовать их в качестве PK. Если у вас есть несколько серверов, все, что вам нужно сделать, это сделать приращение 10 или 20, или, как вы думаете, максимальное количество серверов, которое вам когда-либо потребуется синхронизировать / расширить, и просто включить начальное число для каждой таблицы на каждом последующем сервере. и вы никогда не столкнетесь с данными.
Конечно, из-за приращения я делаю столбец идентификаторов BigInt (иначе известный как long [64 бит]).
Немного подсчитав, даже если вы сделаете шаг 100, вы все равно можете иметь 92,233,720,368,547,758 (> 92 квадриллионов) строк в вашей таблице.
Одна вещь, которую вы никогда не должны делать, это использовать умный ключ. Это ключ, в котором информация о записи закодирована в самом ключе, и в конечном итоге она вас укусит.
Я работал в одном месте, где первичным ключом был идентификатор учетной записи, который представлял собой комбинацию букв и цифр. Я не помню никаких подробностей, но, например, те учетные записи, которые были определенного типа, были бы в диапазоне 600, и другого типа, начинались с 400. Это было здорово, пока тот клиент не решил попросить оба виды работ. Или изменили тип работы, которую они сделали.
Другое место, использовало местоположение в дереве в качестве первичного ключа для записей. Таким образом, будут записи, подобные следующим.
Cat1.subcatA.record1
Cat1.subcatA.record2
Cat1.subcatB.record1
Cat2.subcatA.record1
Конечно, первое, что хотели клиенты, это способ перемещения предметов по дереву. Весь набор программного обеспечения умер до того, как это произошло.
Пожалуйста, пожалуйста, пожалуйста, если вы пишете код, который мне когда-либо придется поддерживать, пожалуйста, не используйте умный ключ!
Я фанат автоинкремента в качестве первичного ключа. В глубине души я знаю, что это отговорка, но она позволяет легко сортировать данные по тому времени, когда они были добавлены (ORDER BY ID DESC, например).
3 колонки звучат ужасно жестко для человеческого разбора.
И это компромисс - сколько реляционных возможностей вам нужно, вместо того, чтобы сделать ЭТОТ ТАБЛИЦУ ПРАВАЯ ЗДЕСЬ понятной для человека, который его опрашивает (по сравнению с хранимой процедурой или программным интерфейсом).
Автоинкремент для нас, людей.:-(
Как правило, это зависит.
Лично мне нравятся автоинкрементные бронзы.
Но я могу сказать вам одну вещь: никогда не доверяйте данным из других источников в качестве вашего ключа. Клянусь, каждый раз, когда я это делаю, он возвращается, чтобы укусить меня. Ну, больше никогда!
Должно быть не менее 3 столбцов, составляющих первичный ключ.
Я не понимаю этого.
Вы говорите о "естественном ключе", например, "имя и дата рождения"? Естественный ключ может быть идеальным, если он существует, но большинство кандидатов на естественный ключ либо не являются уникальными (несколько человек с одинаковыми именами), либо не являются постоянными (кто-то может изменить свое имя).
Int / BigInt, автоинкремент которого является достаточно хорошими первичными ключами.
Я предпочитаю Guid. Потенциальная проблема с автоинкрементом заключается в том, что значение (например, "идентификатор заказа") назначается экземпляром базы данных (например, "базой данных продаж") ..., который не будет работать полностью (вместо этого вам понадобятся составные ключи), если Вам когда-либо нужно объединить данные, созданные более чем одним экземпляром базы данных (например, из нескольких офисов продаж, каждый со своей базой данных).
RE GUID's
Остерегайтесь, если это будет действительно ДЕЙСТВИТЕЛЬНО ДЕЙСТВИТЕЛЬНО большая база данных, большая нагрузка и быстрый доступ.
На моей последней работе, где у нас были базы данных от 100 до 500 миллионов записей, сотрудники нашей базы данных решительно выступали против идентификаторов GUID и десятичного числа соответствующего размера. Они чувствовали, что (под Oracle) разница в размерах внутреннего хранилища для строки Guid - против десятичного значения будет иметь очень заметное значение при поиске. (Большие ключи = более глубокие деревья, чтобы пройти)
Случайный характер идентификаторов GUID также значительно снижает коэффициент заполнения для страниц индекса - это резко увеличивает разрыв и дисковый ввод-вывод.
Я склонен использовать вариант № 1 или № 3 в зависимости от размера, количества людей, подключающихся, и от того, является ли это ситуацией с несколькими серверами баз данных или нет.
Вариант № 2 не имеет особого смысла для меня. Если одного из трех недостаточно для идентификации уникальной записи, то возможно (без дополнительных манипуляций) две имеют две записи с одинаковыми значениями во всех трех столбцах. Если вы хотите обеспечить уникальность любой комбинации из трех, просто добавьте индекс для них.
Авто приращение столбцов. Я могу заставить свой код работать без проблем с SQL Server или Oracle, один с использованием идентификатора, другой с использованием последовательностей через мой DAL, и я не мог быть счастливее. Я согласен, GUID иногда необходимы, если вы выполняете репликацию или отправляете данные, чтобы получить их позже при последующей обработке.
Это классическое "все зависит". Там нет одного правильного ответа для каждого проекта. Мне нравятся разные вещи для разных ситуаций. Это зависит от того, использую ли я ORM и что он поддерживает. Это зависит от общей архитектуры (распределенной или нет и т. Д.). Просто выберите тот, который, по вашему мнению, будет работать, и переходите к обсуждению через табуляции и пробелы.
Я всегда использовал суррогатный ключ - автоинкрементное целое число, называемое "id". Я вижу множество причин сделать это, даже когда очевиден другой вариант:
- консистенция
- Данные независимы (уникальны, не уничтожаются при изменении формата)
- Человек читаемый
... и без веской причины не
- Неоднозначность в соединении? - алиасинг таблиц - лучшая практика, имхо
- Оптимальные столы? - Удаление одного байта на запись является преждевременной оптимизацией, ИМХО
- Решение за столом? - больше не соответствует
- Проблемы с масштабированием? - а? Зачем?
- Иерархическая структура данных? - Это денормализация, совсем другой предмет религии. Достаточно сказать, что я фанат в некоторых обстоятельствах в теории, но никогда на практике:)
разумные доводы против этого я еще не придумал и не встречал, но всегда приветствуется...
Почти всегда целые числа.
У них есть и другие веские причины, помимо того, что они меньше / быстрее обрабатываются. Что бы вы предпочли записать - "404040" или "3463b5a2-a02b-4fd4-aa0f-1d3c0450026c"?
Мне нравятся натуральные ключи, когда я могу им доверять. Я готов заплатить небольшую цену за производительность, чтобы использовать ключи, которые имеют смысл для экспертов в данной области.
Для таблиц, которые описывают сущности, должен быть простой естественный ключ, который идентифицирует отдельные экземпляры так же, как и предмет, который делают люди. Если предмет не имеет надежных идентификаторов для одного из объектов, я прибегну к суррогатному ключу.
Для таблиц, которые описывают отношения, я использую составной ключ, где каждый компонент ссылается на сущность, которая участвует в связи, и, следовательно, на строку в таблице сущностей. Опять же, снижение производительности при использовании составного ключа, как правило, минимально.
Как уже отмечали другие, термин "первичный ключ" немного вводит в заблуждение. В реляционной модели данных используется термин "ключи-кандидаты". Для одной таблицы может быть несколько ключей-кандидатов. По логике, каждый так же хорош, как и другой. Выбор одного из них в качестве "основного" и создание всех ссылок через этот ключ - это просто выбор, который может сделать дизайнер.
Способ, которым я подхожу к первичным ключам (и я чувствую, что это лучше всего), состоит в том, чтобы избежать использования подхода по умолчанию. Это означает, что вместо того, чтобы просто набирать целое число с автоинкрементом и вызывать его один раз в день, я смотрю на проблему и спрашиваю: "Есть ли столбец или группа столбцов, которые всегда будут неизменными и не изменятся?" Если ответ "да", то я использую этот подход.
Я использую только автоинкремент int или GUID. 99% времени я использую автоинкремент int. Это то, чему меня учили пользоваться, когда я впервые узнал о базах данных и никогда не сталкивался с причиной не использовать их (хотя я знаю причины, по которым GUID был бы лучше).
Мне нравятся автоматические инкременты, потому что это помогает с удобочитаемостью. Например, я могу сказать "взгляните на запись 129383", и для кого-то довольно легко найти и найти ее. С GUID это практически невозможно сделать.
Только немного уместно, но одна вещь, которую я начал делать недавно, когда у меня есть маленькие классификационные таблицы (в основном те, которые будут представлять ENUM в коде), - это то, что я сделаю первичный ключ char(3) или char(4). Затем я делаю эти первичные ключи репрезентативными для значения поиска.
Например, у меня есть система квотирования для наших внутренних торговых агентов. У нас есть "Категории затрат", которым каждой позиции цитаты назначается один из... Итак, у меня есть таблица поиска типов с именем "tCostCategories", где первичным ключом является "MTL", "SVC", "TRV", "TAX", "ОДС". Другие столбцы в таблице поиска хранят более подробную информацию, такую как обычные английские значения кодов, "Материал", "Сервис", "Поездка", "Налоги", "Прочие прямые расходы" и т. Д.
Это действительно хорошо, потому что он не использует больше места, чем int, и когда вы смотрите на исходные данные, вам не нужно связывать таблицу поиска, чтобы знать, что это за значение. Например, строка цитаты может выглядеть так:
1 PartNumber $ 40 MTL
2 OtherPartNumber $ 29,99 SVC
3 PartNumber2 $ 150 TRV
Гораздо проще, если использовать int для представления категорий, а затем связать 1, 2, 3 во всех строках - у вас есть данные прямо перед вами, и производительность, похоже, никак не влияет (не то чтобы я мы действительно проверены.)
Что касается реального вопроса... Мне нравятся уникальные идентификаторы RowGUID. Я не на 100% в этом, но разве не все строки имеют внутренний RowGuid? Если это так, то использование RowGuid фактически заняло бы меньше места, чем целые числа (или что-то еще). Все, что я знаю, это то, что если M$ достаточно для использования в GreatPlains, то для меня это достаточно хорошо. (Должен ли я утка??)
О, еще одна причина, по которой я использую GUID - я использую иерархическую структуру данных. То есть у меня есть таблица "Компания" и таблица "Продавец", для которых совпадают первичные ключи. Но у меня также есть таблица "Производитель", которая также "наследует" от Компании. Поля, которые являются общими для поставщиков и производителей, не отображаются в этих таблицах - они отображаются в компании. В этой настройке использование int намного более болезненно, чем Guids. По крайней мере, вы не можете использовать идентификационные первичные ключи.
Прошлый ответ на основные определения: то, что составляет хороший первичный ключ, остается в основном за религией и аргументами. Если у вас есть что-то, что всегда будет отображаться однозначно в отдельную строку, то это будет нормально работать в качестве первичного ключа. После этого есть и другие соображения:
- Является ли определение первичного ключа не слишком сложным? Избегает ли это введения ненужных сложностей ради следования "лучшей практике"?
- Есть ли лучший возможный первичный ключ, который потребовал бы меньше ресурсов для обработки базы данных (т. Е. INTEGER против VARCHAR и т. Д.)?
- Я АБСОЛЮТНО уверен, что инвариант уникальности и определенности моего первичного ключа не изменится?
Этот последний, вероятно, привлекает большинство людей к использованию таких вещей, как GUID или самоинкрементные целочисленные столбцы, потому что полагаться на такие вещи, как адреса, номера телефонов, имена / фамилии и т. Д., Просто не урезать. Единственный инвариант о людях, о которых я могу думать, это SSN, но тогда я даже не на 100% уверен в том, что они остаются навсегда уникальными.
Надеюсь, это поможет добавить ясности...
Guids.period.
В случае, если вам нужно уменьшить масштаб или вам нужно назначить первичный ключ альтернативным способом, они станут вашим другом. Вы можете добавить индексы для всего остального.
обновить, чтобы уточнить мое заявление.
Я работал на разных сайтах. От небольших сделок с одним сервером до крупных сделок с несколькими БД и веб-серверами. Конечно, были приложения, которые были бы хороши с автоматически увеличивающимися целыми числами в качестве первичных ключей. Тем не менее, они не соответствуют модели, как я делаю вещи.
При использовании GUID вы можете генерировать идентификатор в любом месте. Он может быть создан удаленным сервером, вашим веб-приложением, в самой базе данных или даже в нескольких базах данных в ситуации с несколькими хозяевами.
С другой стороны, автоматически увеличиваемый INT может быть безопасно сгенерирован только в первичной базе данных. Опять же, это может быть хорошо, если у вас есть приложение, которое будет тесно связано с этим одним резервным сервером БД, и масштабирование - это не то, что вас беспокоит.
Конечно, использование идентификаторов GUID означает, что у вас должны быть ночные процессы переиндексации. Тем не менее, если вы используете что-то другое, чем INT с автоматическим приращением, вам все равно следует это сделать. Черт возьми, даже с INT в качестве основного, скорее всего, у вас есть другие индексы, которые необходимо регенерировать, чтобы справиться с фрагментацией. Следовательно, использование идентификаторов GUID точно не добавляет другой проблемы, потому что эти задачи должны выполняться независимо.
Если вы посмотрите на более крупные приложения, то заметите нечто важное: все они используют GUID в кодировке Base64 в качестве ключей. Причина этого проста: использование идентификаторов GUID позволяет легко масштабировать , в то время как при попытке масштабирования INT можно совершить множество скачков.
Наше последнее приложение проходит период тяжелых вставок, который длится около месяца. После этого все 90+% запросов выбираются для отчетности. Чтобы увеличить емкость, я могу вызвать дополнительные серверы БД в течение этого большого периода вставки; а затем легко объединить их в одну БД для составления отчетов. Попытка сделать это с INT будет абсолютным кошмаром.
Откровенно говоря, каждый раз, когда вы кластеризуете базу данных или настраиваете репликацию, сервер БД все равно будет требовать, чтобы у вас в таблице были GUID. Итак, если вы думаете, что ваша система может нуждаться в росте, выберите ту, которая хороша.
Это сложный вопрос, осознали ли вы это или нет. Может попадать в раздел этого часто задаваемого вопроса Stackru.
Какие вопросы мне не следует задавать здесь?
Старайтесь не задавать вопросы, которые носят субъективный, аргументативный характер или требуют расширенного обсуждения. Это место для вопросов, на которые можно ответить!
Это обсуждалось годами и будет обсуждаться годами. Единственные намеки на консенсус, которые я видел, - это то, что ответы несколько предсказуемы, в зависимости от того, спрашиваете ли вы OO-парня (GUID - единственный путь!), Модельера данных (Натуральные ключи - единственный путь!), или администратор, ориентированный на производительность (INT - единственный путь!).