Я должен разработать таблицу с первичным ключом varchar или интервала?

При выборе первичного ключа обычно вы также выбираете кластерный ключ. Их часто путают, но вы должны понимать разницу.

Первичные ключи - это логические бизнес-элементы . Первичный ключ используется вашим приложением для идентификации объекта, и обсуждение первичных ключей в основном сводится к использованию естественных ключей или суррогатных ключей . Ссылки содержат более подробные сведения, но основная идея состоит в том, что естественные ключи являются производными от существующего свойства объекта, такого как ssn или номер телефона , в то время как суррогатные ключи не имеют никакого значения в отношении для бизнес-объекта, например id или rowid , и они обычно имеют тип IDENTITY или какой-то uuid. Мое личное мнение таково, что суррогатные ключи превосходят естественные ключи, и всегда следует выбирать значения идентичности только для локальных приложений, а также руководства для любого типа распределенных данных. Первичный ключ никогда не изменяется в течение срока существования объекта.

Кластерные ключи - это ключ, который определяет физическое хранение строк в таблице. В большинстве случаев они перекрываются с первичным ключом (идентификатором логического объекта), но на самом деле это не является обязательным и не требуется. Если они различны, это означает, что в таблице есть некластеризованный уникальный индекс, реализующий первичный ключ. Кластеризованные ключевые значения могут фактически изменяться в течение срока службы строки, в результате чего строка физически перемещается в таблице в новое место. Если вам нужно отделить первичный ключ от кластерного ключа (а иногда и нужно), выбрать хороший кластерный ключ значительно сложнее, чем выбрать первичный ключ. Существует два основных фактора, которые определяют структуру кластерного ключа:

1. Распространенный шаблон доступа к данным .
2. Рекомендации по хранению .

Шаблон доступа к данным . Под этим я понимаю способ запроса и обновления таблицы. Помните, что кластерные ключи определяют фактический порядок строк в таблице. Для определенных шаблонов доступа некоторые макеты имеют большое значение в отношении скорости запросов или согласованности обновлений:

• текущие и архивные данные. Во многих приложениях доступ к данным, относящимся к текущему месяцу, осуществляется часто, а к прошлому - редко. В таких случаях конструкция таблицы использует разделение таблицы по дате транзакции, часто используется алгоритм скользящего окна . Раздел текущего месяца хранится в файловой группе, расположенной на горячем быстром диске, заархивированные старые данные перемещаются в файловые группы, размещенные на более дешевом, но более медленном хранилище. Очевидно, что в этом случае кластерный ключ (дата) не является первичным ключом (идентификатором транзакции). Разделение этих двух разделов обусловлено требованиями к масштабированию, так как оптимизатор запросов сможет определить, что запросы интересны только текущему разделу и даже не смотреть на исторические.

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

• Коррелированная обработка. Когда приложение хорошо спроектировано, оно будет разделять обработку коррелированных элементов между своими рабочими потоками. Например, процессор спроектирован так, чтобы иметь 8 рабочих потоков (скажем, чтобы соответствовать 8 процессорам на сервере), поэтому процессоры разделяют данные между собой, например. рабочий 1 выбирает только учетные записи с именами от A до E, рабочий 2 с F до J и т. д. В таких случаях таблица должна быть фактически кластеризована по имени учетной записи (или по составному ключу, который имеет крайнюю левую позицию, первую букву имени учетной записи), чтобы работники локализовали свои запросы и обновления в таблице. Такой стол будет иметь 8 различных горячих точек вокруг области, в которой каждый рабочий концентрируется в данный момент, но важно то, что они не перекрываются (без блокировки). Этот вид дизайна преобладает в проектах OLTP с высокой пропускной способностью и в тестовых нагрузках TPCC, где такое разделение также отражается на расположении в памяти страниц, загруженных в буферный пул (расположение NUMA), но я отвлекся.

Соображения о хранении . Кластерный ключ шириной имеет огромные повторные перкурсии в хранилище таблицы. Например, ключ занимает место на каждой нелистовой странице b-дерева, поэтому большой ключ будет занимать больше места. Во-вторых, что часто более важно, кластеризованный ключ используется в качестве ключа поиска для каждого некластеризованного ключа, поэтому каждый некластеризованный ключ должен будет хранить всю ширину кластеризованного ключа для каждого ряд. Кроме того, выбор ключа влияет на фрагментацию кластеризованного индекса, иногда резко влияя на производительность.

Эти две силы иногда могут быть антагонистическими: для схемы доступа к данным требуется определенный большой кластерный ключ, что вызовет проблемы с хранением. В таких случаях, конечно, нужен баланс, но волшебной формулы нет. Вы измеряете и проверяете, чтобы попасть в золотую середину.

Итак, что мы извлекаем из всего этого? Всегда начинайте с рассмотрения кластерного ключа, который также является первичным ключом формы entity_id IDENTITY (1,1) NOT NULL . Разделите их и организуйте таблицу соответствующим образом (например, разделите по дате), когда это необходимо.

Я согласен с тем, что в целом тип поля INT (или идентификатор) является лучшим выбором в большинстве «обычных» проектов баз данных:

• он не требует «алгоритма» для генерации идентификатора / key / value
• у вас есть быстрые (эр) соединения, и оптимизатор может работать намного усерднее над диапазонами и тому подобным под капотом
• вы следуете стандарту де-факто

Тем не менее, вам также нужно знать ваши данные. Если вы собираетесь пропустить 32-битное int со знаком, вам нужно подумать о unsigned. Если вы собираетесь пройти через это, возможно, вам нужны 64-битные целые числа. Или, может быть, вам понадобится UUID / хэш, чтобы упростить синхронизацию между экземплярами / шардами базы данных.

К сожалению, это зависит от YMMV, но я бы определенно использовал int / identity, если у вас нет веской причины , а не к.

Как вы сказали, последовательность является ключевым моментом. Я лично использую беззнаковые целые числа. Они не закончатся, если вы не работаете с смехотворными объемами данных, и вы всегда можете знать, что любой ключевой столбец должен быть этого типа, и вам никогда не придется искать правильное значение для отдельных столбцов.

Для наилучшей производительности , 99,999% в большинстве случаев первичный ключ должен быть единственным целочисленным полем.

Если вам не требуется, чтобы первичный ключ был уникальным для нескольких таблиц в базе данных или для нескольких баз данных. Я предполагаю, что вы спрашиваете о MS SQL-Server, потому что именно так был отмечен ваш вопрос. В этом случае рассмотрите возможность использования поля GUID. Хотя лучше, чем варчар,

Если пройти это упражнение бесчисленное количество раз и затем поддержать систему результатами, есть некоторые предостережения в отношении общего утверждения, что INT всегда лучше. В общем, если нет на то причины, я бы с этим согласился. Однако, в окопах, вот некоторые плюсы и минусы.

INT

• Используйте, если нет веской причины не делать этого.

GUID

• Уникальность - Одним из примеров является случай, когда есть один способ связи между удаленными частями программы и стороной, которую необходимо инициировать, не является стороной с базой данных. В этом случае установка Guid на удаленной стороне безопасна, а выбор INT - нет.
• И снова уникальность - Более надуманный сценарий - это система, в которой несколько клиентов сосуществуют в отдельных базах данных, и происходит миграция между клиентами, такими как аналогичные пользователи, с использованием набора программ. Если этот пользователь регистрируется в другой программе, его пользовательская запись может использоваться там без конфликтов. Другой сценарий - если клиенты приобретают предприятия друг у друга. Если оба находятся в одной системе, они часто ожидают, что миграция будет проще. По сути, любая частая миграция между клиентами.

• Трудно использовать - Даже опытный программист не может вспомнить руководство. При устранении неполадок часто неприятно копировать и вставлять идентификаторы для запросов, особенно если поддержка осуществляется с помощью инструмента удаленного доступа. Намного проще постоянно обращаться к SELECT * FROM Xxx WHERE ID = 7, чем к SELECT * FROM Xxx WHERE ID = 'DF63F4BD-7DC1-4DEB-959B-4D19012A6306'

• Индексирование - использование кластерного индекса для GUID field требует постоянной перестановки страниц данных и не так эффективно индексируется, как INT или даже короткие строки. Это может убить производительность - не делайте этого.

CHAR

• Читаемость - Хотя общепринято считать, что никого не должно быть в базе данных, реальность систем такова, что люди будут иметь доступ - надеюсь, персонал из вашей организация. Когда эти люди не разбираются в синтаксисе соединения, нормализованная таблица с целыми числами или идентификаторами непонятна без множества других запросов. Та же нормализованная таблица с НЕКОТОРЫМИ строковыми ключами может быть гораздо более полезной для устранения неполадок. Я обычно использую это для типа таблицы, куда я добавляю записи во время установки, чтобы они не менялись. Такие вещи, как StatusID в основной таблице, гораздо удобнее использовать для поддержки, когда ключ закрыт или ожидает выполнения, чем цифра. Использование традиционных ключей в этих областях может превратить легко решаемую проблему в проблему, требующую помощи разработчика. Подобные узкие места - это плохо, даже если они вызваны предоставлением сомнительному персоналу доступа к базе данных.
• Ограничение - Даже если вы используете строки, сохраняйте их фиксированную длину, что ускоряет индексацию, и добавляйте ограничение или внешний ключ, чтобы убрать мусор. . Иногда использование этой строки может позволить вам удалить таблицу поиска и сохранить выбор в виде простого Enum в коде - по-прежнему важно ограничить данные, поступающие в это поле.

Используйте INT. Все ваши баллы действительны; Я бы расставил следующие приоритеты:

1. Простота использования возможности автоматического увеличения SQL - зачем изобретать колесо?
2. Управляемость - вам не нужно менять ключевое поле.
3. Производительность
4. Дисковое пространство

] 1 и 2 требуют времени / энергии / усилий разработчика. 3 и 4, вы можете бросить оборудование.

Если бы здесь был Джо Селко, он бы сказал несколько резких слов ...; -)

Я хочу отметить, что INT как жесткое правило не всегда подходит . Допустим, у вас есть таблица транспортных средств со всеми типами автомобилей, грузовиков и т. Д. Теперь предположим, что у вас есть таблица VehicleType. Если вы хотите получить все грузовики, вы можете сделать это (с начальным значением идентификатора INT):

SELECT V.Make, V.Model
FROM Vehicle as V
INNER JOIN VehicleType as VT
ON V.VehicleTypeID = VT.VehicleTypeID
WHERE VT.VehicleTypeName = 'Truck'

Теперь, с PK Варчара на VehicleType:

SELECT Make, Model
FROM Vehicle 
WHERE VehicleTypeName = 'Truck'

Код немного чище, и вы избегаете присоединения. Возможно, объединение - это еще не конец света, но если у вас есть только один инструмент в вашем наборе инструментов, вы упускаете некоторые возможности для повышения производительности и более чистых схем.

Просто подумайте. : -)

Хотя обычно рекомендуется INT , это действительно зависит от вашей ситуации.

Если вы обеспокоены с ремонтопригодностью, то возможны и другие типы. Например, вы можете очень эффективно использовать Guid в качестве первичного ключа. Есть причины не делать этого, но согласованность не входит в их число.

Но да, если у вас нет веской причины не делать этого, int является самым простым в использовании и с наименьшей вероятностью доставит вам какие-либо проблемы.

В PostgreSQL я обычно использую "тип данных" "Serial" или "BigSerial" для генерации первичных ключей. Значения увеличиваются автоматически, и я всегда считаю, что с целыми числами легко работать. По сути, они эквивалентны целочисленному полю MySQL, для которого установлено значение «auto_increment».

Следует хорошо подумать о том, 32- битового диапазона достаточно для того, что вы делаете. Идентификаторы статуса Twitter были 32-битными INT, и у них были проблемы, когда они закончились.

Использовать ли BIGINT или UUID / GUID в этой ситуации - спорный вопрос, и я не хардкорный специалист по базам данных, но UUID можно хранить в VARCHAR фиксированной длины, не беспокоясь о том, что вам нужно изменить размер поля.

Мы должны помнить, что первичный ключ таблицы не должен иметь "бизнес-логики" и должен быть только идентификатором записи, которой он принадлежит. Следуя этому простому правилу, int и особенно identity int - очень хорошее решение. Спрашивая о varchar, я предполагаю, что вы имеете в виду, например, использование «Полного имени» в качестве ключа к таблице «люди». Но что, если мы захотим изменить название с «Джорджа что-то» на «Джордж А. И какого размера будет поле? Если мы изменим размер, нам придется изменить размер и для всех сторонних таблиц. Поэтому нам следует избегать логики с ключами. Иногда мы можем использовать социальный идентификатор (целое число) в качестве ключа, но я тоже этого избегаю. Теперь, если у проекта есть перспективы для масштабирования, вам также следует подумать об использовании Guids (тип SQL uniqueidentifier).