Должен ли я создать таблицу с первичным ключом varchar или int?
Я знаю, что это субъективно, но я хотел бы узнать мнения людей и, надеюсь, некоторые лучшие практики, которые я могу применить при разработке структур таблиц SQL Server.
Лично я чувствую, что наложение таблицы на varchar фиксированной (максимальной) длины - нет-нет, потому что это означает необходимость распространять ту же фиксированную длину на любые другие таблицы, которые используют это как внешний ключ. Используя int, можно было бы избежать применения одинаковой длины по всей доске, что неизбежно приведет к человеческим ошибкам, т.е. varchar (10), и другие varchar (20),
Это звучит как кошмар для первоначальной настройки, плюс означает, что дальнейшее ведение таблиц также обременительно. Например, скажем, столбец с ключом varchar внезапно становится 12 символами вместо 10. Теперь вам нужно перейти и обновить все другие таблицы, что может оказаться огромной задачей спустя годы.
Я ошибся? Я что-то здесь пропустил? Я хотел бы знать, что другие думают об этом, и если придерживаться int для первичных ключей - это лучший способ избежать кошмаров от keepace.
13 ответов
Я определенно рекомендую использовать INT NOT NULL IDENTITY(1,1) поле в каждой таблице в качестве первичного ключа.
С помощью поля IDENTITY вы можете позволить базе данных обрабатывать все детали, чтобы быть уверенным, что она действительно уникальна, и все, и тип данных INT имеет всего 4 байта и фиксирован, так что его проще и удобнее использовать для основного (и кластерного)) введите ваш стол.
И вы правы - INT - это INT, это INT - он ничего не изменит в своем размере, поэтому вам никогда не придется пересоздавать и / или обновлять ваши отношения с внешним ключом.
Использование VARCHAR(10) или (20) просто занимает слишком много места - 10 или 20 байтов вместо 4, и то, что многие люди не знают - значение ключа кластеризации будет повторяться для каждой записи индекса на каждом один некластеризованный индекс для таблицы, поэтому потенциально вы тратите много места (не только на диске - это дешево - но и в основной памяти SQL Server). Кроме того, поскольку он переменный (может быть 4, может быть 20 символов), SQL-серверу сложнее правильно поддерживать хорошую структуру индекса.
Марк
При выборе первичного ключа обычно вы также выбираете кластерный ключ. Их два часто путают, но вы должны понимать разницу.
Первичные ключи являются логическими бизнес- элементами. Первичный ключ используется вашим приложением для идентификации сущности, и обсуждение первичных ключей в значительной степени связано с использованием естественных ключей или суррогатных ключей. Ссылки идут гораздо подробнее, но основная идея заключается в том, что естественные ключи получены из существующего свойства сущности, такого как ssn или же phone numberв то время как суррогатные ключи не имеют никакого значения для хозяйствующего субъекта, как id или же rowid и они обычно имеют тип IDENTITY или какой-то UUID. Мое личное мнение состоит в том, что суррогатные ключи превосходят естественные ключи, и выбор должен всегда быть идентичными значениями только для локальных приложений, руководствами для любого вида распределенных данных. Первичный ключ никогда не изменяется в течение всего времени существования объекта.
Кластерные ключи - это ключ, который определяет физическое хранилище строк в таблице. В большинстве случаев они перекрываются с первичным ключом (идентификатором логического объекта), но это на самом деле не применяется и не требуется. Когда они различаются, это означает, что в таблице есть некластеризованный уникальный индекс, который реализует первичный ключ. Значения кластеризованного ключа могут фактически изменяться в течение всего времени жизни строки, в результате чего строка физически перемещается в таблицу в новое место. Если вам необходимо отделить первичный ключ от кластеризованного ключа (а иногда это происходит), выбрать хороший кластеризованный ключ значительно сложнее, чем выбрать первичный ключ. Есть два основных фактора, которые определяют ваш кластерный ключ:
- Преобладающая схема доступа к данным.
- Условия хранения.
Шаблон доступа к данным. Под этим я понимаю способ запроса и обновления таблицы. Помните, что кластеризованные ключи определяют фактический порядок строк в таблице. Для определенных шаблонов доступа некоторые макеты имеют огромное значение в отношении скорости запросов или обновления параллелизма:
текущие и архивные данные. Во многих приложениях данные, относящиеся к текущему месяцу, часто доступны, в то время как к прошлым редко обращаются. В таких случаях дизайн таблицы использует разбиение таблицы по дате транзакции, часто с использованием алгоритма скользящего окна. Раздел текущего месяца хранится в файловой группе, расположенной на быстром быстром диске, заархивированные старые данные перемещаются в файловые группы, размещенные в более дешевом, но более медленном хранилище. Очевидно, что в этом случае кластерный ключ (дата) не является первичным ключом (идентификатор транзакции). Разделение этих двух факторов обусловлено требованиями к масштабированию, поскольку оптимизатор запросов сможет обнаружить, что запросы интересуют только текущий раздел и даже не смотрят на исторические.
Обработка стиля очереди FIFO. В этом случае в таблице есть две горячие точки: хвост, где происходит вставка (постановка в очередь), и голова, в которой происходит удаление (снятие очереди). Кластерный ключ должен принять это во внимание и организовать таблицу так, чтобы физически разделить расположение хвоста и головки на диске, чтобы обеспечить согласованность между постановкой в очередь и снятием очереди, например. с помощью ключа порядка постановки в очередь. В чистых очередях этот кластеризованный ключ является единственным ключом, поскольку в таблице нет первичного ключа (он содержит сообщения, а не сущности). Но в большинстве случаев очередь не является чистой, она также служит хранилищем для сущностей, и линия между очередью и таблицей стирается. В этом случае также существует первичный ключ, который не может быть кластеризованным ключом: сущности могут быть повторно поставлены в очередь, таким образом изменяя значение кластеризованного ключа порядка постановки в очередь, но они не могут изменить значение первичного ключа. Неспособность увидеть разделение является основной причиной того, что очереди, поддерживаемые таблицами пользователей, так общеизвестно трудно получить правильно и изобиловать взаимоблокировками: потому что постановка очереди и снятие очереди чередуются между таблицами, а не локализуются в хвосте и в начале очереди.
Коррелированная обработка. Когда приложение хорошо спроектировано, оно разделит обработку связанных элементов между своими рабочими потоками. Например, процессор разработан так, чтобы иметь 8 рабочих потоков (скажем, для соответствия 8 процессорам на сервере), поэтому процессоры разделяют данные между собой, например. работник 1 выбирает только учетные записи с именами от A до E, работник 2 от F до J и т. д. В таких случаях таблица должна быть фактически кластеризована по имени учетной записи (или по составному ключу, который имеет крайнюю левую позицию - первую букву имени учетной записи), чтобы работники локализовали свои запросы и обновления в таблице. Такой стол будет иметь 8 различных горячих точек вокруг области, в которой каждый работник концентрируется в данный момент, но важно то, что они не перекрываются (не блокируются). Этот тип дизайна преобладает в проектах OLTP с высокой пропускной способностью и в тестовых нагрузках TPCC, где этот тип разделения также отражается в расположении памяти страниц, загруженных в пул буферов (локальность NUMA), но я отступаю.
Условия хранения. Ширина кластеризованного ключа имеет огромные последствия при хранении таблицы. Для одного ключ занимает место на каждой неконечной странице b-дерева, поэтому большой ключ будет занимать больше места. Второе, и часто более важное, заключается в том, что кластерный ключ используется в качестве ключа поиска каждым ключом без кластера, поэтому каждый некластеризованный ключ должен хранить полную ширину кластеризованного ключа для каждой строки. Это то, что делает большие кластерные ключи, такие как varchar(256), и делает неправильный выбор для ключей кластерного индекса.
Кроме того, выбор ключа влияет на фрагментацию кластеризованного индекса, иногда резко влияя на производительность.
Эти две силы иногда могут быть антагонистическими, схема доступа к данным требует определенного большого кластеризованного ключа, который вызовет проблемы с хранением. В таких случаях, конечно, необходим баланс, но нет волшебной формулы. Вы измеряете и вы испытываете, чтобы добраться до сладкого места.
Итак, что мы делаем из всего этого? Всегда начинайте с рассмотрения кластерного ключа, который также является первичным ключом формыentity_id IDENTITY(1,1) NOT NULL, Разделите их и упорядочите таблицу соответствующим образом (например, разделите по дате) при необходимости.
Я бы согласился с тем, что в общем случае тип поля INT (или идентичности) является лучшим выбором в большинстве "обычных" конструкций баз данных:
- для генерации идентификатора / ключа / значения не требуется "алгоритм"
- у вас есть быстрые (э) объединения, и оптимизатор может работать намного больше на диапазонах и тому подобном под капотом
- вы следуете стандарту де-факто
Тем не менее, вы также должны знать свои данные. Если вы собираетесь пройти через 32-битный int со знаком, вам нужно подумать о unsigned. Если вы собираетесь пройти через это, возможно, вам нужны 64-битные целые числа. Или, может быть, вам нужен UUID/ хеш, чтобы упростить синхронизацию между экземплярами / осколками базы данных.
К сожалению, это зависит и от YMMV, но я бы определенно использовал int/identity, если у вас нет веских причин не делать этого.
Как вы сказали, последовательность является ключевым фактором. Я лично использую неподписанные целые. Вы не исчерпаете их, если не будете работать со смехотворными объемами данных, и вы всегда можете знать, что любой ключевой столбец должен быть такого типа, и вам никогда не придется искать правильное значение для отдельных столбцов.
Основываясь на том, что мы выполняем это упражнение бесчисленное количество раз, а затем подкрепляем систему результатами, есть некоторые предостережения для общего утверждения, что INT всегда лучше. В общем, если нет причин, я бы согласился с этим. Однако в окопах есть некоторые плюсы и минусы.
INT
- Используйте, если на то нет веских причин не делать этого.
GUID
- Уникальность. Одним из примеров является случай, когда существует один способ связи между удаленными частями программы и стороной, которая должна инициировать, не является стороной с базой данных. В этом случае установка Guid на удаленной стороне безопасна, если нет выбора INT.
- Опять уникальность - более надуманный сценарий - это система, в которой несколько клиентов сосуществуют в отдельных базах данных, и существует миграция между клиентами, такими как аналогичные пользователи, использующие набор программ. Если этот пользователь подписывается на другую программу, его запись пользователя может использоваться там без конфликтов. Другой сценарий, если клиенты приобретают объекты друг от друга. Если оба находятся в одной системе, они часто ожидают, что миграция будет проще. По сути, любая частая миграция между клиентами.
Трудно использовать - даже опытный программист не может вспомнить руководство. При поиске и устранении неисправностей часто бывает неудобно копировать и вставлять идентификаторы для запросов, особенно если поддержка осуществляется с помощью инструмента удаленного доступа. Гораздо проще постоянно обращаться к SELECT * FROM Xxx WHERE ID = 7, чем SELECT * FROM Xxx WHERE ID = 'DF63F4BD-7DC1-4DEB-959B-4D19012A6306'
Индексирование - использование кластерного индекса для поля guid требует постоянной перестройки страниц данных и не так эффективно индексировать, как INT или даже короткие строки. Это может убить производительность - не делайте этого.
CHAR
- Удобочитаемость. Хотя общепринятым считается то, что в базе данных никого не должно быть, реальность систем заключается в том, что у людей будет доступ - надеюсь, персонал вашей организации. Когда эти люди не разбираются в синтаксисе соединения, нормализованная таблица с целочисленными значениями или направляющими не ясна без многих других запросов. Та же нормализованная таблица с НЕКОТОРЫМИ строковыми ключами может быть гораздо более полезной для устранения неполадок. Я склонен использовать это для типа таблицы, куда я поставляю записи во время установки, чтобы они не менялись. Такие вещи, как StatusID в основной таблице, гораздо удобнее использовать для поддержки, когда ключ "Закрыт" или "Ожидает", чем цифра. Использование традиционных ключей в этих областях может превратить легко решаемую проблему в проблему, требующую помощи разработчика. Подобные узкие места являются серьезными, даже если они вызваны тем, что сомнительный персонал получает доступ к базе данных.
- Ограничение - даже если вы используете строки, сохраняйте их фиксированной длины, что ускоряет индексацию и добавляет ограничение или внешний ключ, чтобы убрать мусор. Иногда использование этой строки может позволить вам удалить справочную таблицу и сохранить выделение в коде как простой Enum - все равно важно ограничить данные, поступающие в это поле.
Для лучшей производительности в99, 999% случаев первичный ключ должен представлять собой одно целочисленное поле.
Если вам не требуется, чтобы первичный ключ был уникальным для нескольких таблиц в базе данных или для нескольких баз данных. Я предполагаю, что вы спрашиваете о MS SQL-Server, потому что именно так был отмечен ваш вопрос. В этом случае рассмотрите возможность использования поля GUID. Хотя лучше, чем varchar, производительность поля GUID не так хороша, как целое число.
Используйте INT. Все ваши очки действительны; Я бы расставил приоритеты как:
- Простота использования возможностей автоматического увеличения SQL - зачем изобретать велосипед?
- Управляемость - вам не нужно менять ключевое поле.
- Спектакль
- Дисковое пространство
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'
Теперь с Varchar PK на VehicleType:
SELECT Make, Model
FROM Vehicle
WHERE VehicleTypeName = 'Truck'
Код немного чище, и вы избегаете объединения. Возможно, объединение - это не конец света, но если в вашем наборе инструментов есть только один инструмент, вам не хватает некоторых возможностей для повышения производительности и более четких схем.
Просто мысль.:-)
В то время как INT как правило, рекомендуется, это действительно зависит от вашей ситуации.
Если вас интересует ремонтопригодность, то другие типы также возможны. Например, вы можете очень эффективно использовать Guid в качестве первичного ключа. Есть причины не делать этого, но последовательность не является одной из них.
Но да, если у вас нет веской причины не делать этого, int является самым простым в использовании и наименее вероятным источником каких-либо проблем.
Мы должны помнить, что первичный ключ таблицы не должен иметь "бизнес-логики", а должен быть только идентификатором записи, которой он принадлежит. Следуя этому простому правилу, int и особенно идентификация int - очень хорошее решение. Задавая вопрос о varchar, я предполагаю, что вы имеете в виду использование, например, "Полного имени" в качестве ключа к таблице "people". Но что, если мы захотим изменить название с "George Something" на "George A. Something"? И какого размера будет поле? Если мы изменим размер, мы должны изменить размер и для всех сторонних таблиц. Поэтому мы должны избегать логики на ключах. Иногда мы можем использовать социальный идентификатор (целочисленное значение) в качестве ключа, но я также избегаю этого. Теперь, если у проекта есть перспективы для расширения, вам следует рассмотреть возможность использования Guids (тип SQL uniqueidentifier).
В PostgreSQL я обычно использую "Serial" или "BigSerial" "тип данных" для генерации первичных ключей. Значения автоматически увеличиваются, и я всегда нахожу целые числа, с которыми легко работать. По сути, они эквивалентны целочисленному полю MySQL, для которого установлено значение "auto_increment".
Надо задуматься о том, достаточно ли 32-битного диапазона для того, что вы делаете. Идентификаторы статуса Twitter были 32-битными INT, и у них были проблемы, когда они заканчивались.
Использование BIGINT или UUID/GUID в этой ситуации является спорным вопросом, и я не хардкорный специалист по работе с базами данных, но UUID могут храниться в VARCHAR фиксированной длины, не беспокоясь о том, что вам придется менять размер поля.
Помня, что это довольно старый вопрос, я все же хочу обосновать использование varchar с суррогатными ключами для будущих читателей:
- Среда с несколькими реплицированными машинами
- Сценарии, в которых требуется, чтобы идентификатор вставляемой строки был известен до фактической вставки (т. Е. Клиент назначает этот идентификатор, а не базу данных)