Каковы оптимальные методы использования GUID в качестве первичного ключа, особенно в отношении производительности?
У меня есть приложение, которое использует GUID в качестве первичного ключа почти во всех таблицах, и я прочитал, что существуют проблемы с производительностью при использовании GUID в качестве первичного ключа. Честно говоря, я не видел никаких проблем, но я собираюсь запустить новое приложение, и я все еще хочу использовать GUID в качестве первичных ключей, но я думал об использовании составного первичного ключа (GUID и, возможно, другое поле.)
Я использую GUID, потому что ими удобно и легко управлять, когда у вас есть разные среды, такие как "производственная", "тестовая" и "dev" базы данных, а также для миграции данных между базами данных.
Я буду использовать Entity Framework 4.3 и хочу назначить Guid в коде приложения, прежде чем вставлять его в базу данных. (т.е. я не хочу, чтобы SQL генерировал Guid).
Каков наилучший способ создания первичных ключей на основе GUID, чтобы избежать предполагаемых падений производительности, связанных с этим подходом?
9 ответов
Идентификаторы GUID могут показаться естественным выбором для вашего первичного ключа - и, если вам действительно нужно, вы, вероятно, можете поспорить, что он будет использоваться для ПЕРВИЧНОГО КЛЮЧА таблицы. Что я настоятельно рекомендую не делать, так это использовать столбец GUID в качестве ключа кластеризации, что SQL Server делает по умолчанию, если вы специально не запретите это делать.
Вам действительно нужно держать в стороне две проблемы:
Первичный ключ - это логическая конструкция - один из ключей-кандидатов, который однозначно и надежно идентифицирует каждую строку в вашей таблице. Это может быть что угодно, действительно -
INT,GUIDСтрока - выберите наиболее подходящий для вашего сценария.ключ кластеризации (столбец или столбцы, которые определяют "кластеризованный индекс" в таблице) - это физическая вещь, связанная с хранилищем, и здесь вам лучше выбрать небольшой, стабильный, постоянно увеличивающийся тип данных -
INTили жеBIGINTкак ваш вариант по умолчанию.
По умолчанию первичный ключ в таблице SQL Server также используется в качестве ключа кластеризации, но это не обязательно должно быть так! Я лично наблюдал значительное увеличение производительности, когда разбивал предыдущий первичный / кластерный ключ на основе GUID на два отдельных ключа - первичный (логический) ключ на GUID и ключ кластеризации (упорядочения) на отдельном INT IDENTITY(1,1) колонка.
Как Кимберли Трипп - королева индексации - и другие очень много раз заявляли GUID поскольку ключ кластеризации не является оптимальным, так как из-за его случайности он приведет к большой фрагментации страниц и индексов и в целом к плохой производительности.
Да я знаю - есть newsequentialid() в SQL Server 2005 и более поздних версиях - но даже это не является действительно последовательным и, следовательно, также страдает от тех же проблем, что и GUID - чуть менее заметно.
Затем следует рассмотреть еще одну проблему: ключ кластеризации в таблице будет добавлен к каждой записи в каждом некластеризованном индексе в вашей таблице - таким образом, вы действительно хотите убедиться, что он как можно меньше. Как правило, INT с 2+ миллиардов строк должно быть достаточно для подавляющего большинства таблиц - и по сравнению с GUID в качестве ключа кластеризации вы можете сэкономить сотни мегабайт памяти на диске и в памяти сервера.
Быстрый расчет - с помощью INT против GUID как первичный и кластерный ключ:
- Базовая таблица с 1 000 000 строк (3,8 МБ против 15,26 МБ)
- 6 некластеризованных индексов (22,89 МБ против 91,55 МБ)
ИТОГО: 25 МБ против 106 МБ - и это только на одном столе!
Еще немного пищи для размышлений - отличные вещи Кимберли Триппа - прочитайте, прочитайте еще раз, переварите! Это на самом деле индексное Евангелие SQL Server.
- GUID как ПЕРВИЧНЫЙ КЛЮЧ и / или кластерный ключ
- Дискуссия по кластерному индексу продолжается
- Постоянно растущий ключ кластеризации - Дебаты о кластеризованных индексах.......... снова!
- Дисковое пространство дешево - не в этом дело!
PS: конечно, если вы имеете дело только с несколькими сотнями или несколькими тысячами строк - большинство из этих аргументов не окажут большого влияния на вас. Однако: если вы попадаете в десятки или сотни тысяч строк или начинаете считать в миллионах - тогда эти точки становятся очень важными и очень важными для понимания.
Обновление: если вы хотите, чтобы ваш PKGUID столбец как ваш первичный ключ (но не ключ кластеризации), а другой столбец MYINT (INT IDENTITY) как ключ кластеризации - используйте это:
CREATE TABLE dbo.MyTable
(PKGUID UNIQUEIDENTIFIER NOT NULL,
MyINT INT IDENTITY(1,1) NOT NULL,
.... add more columns as needed ...... )
ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY NONCLUSTERED (PKGUID)
CREATE UNIQUE CLUSTERED INDEX CIX_MyTable ON dbo.MyTable(MyINT)
В основном: вам просто нужно явно сказать PRIMARY KEY ограничение, что это NONCLUSTERED (иначе он создается как ваш кластерный индекс по умолчанию), а затем вы создаете второй индекс, который определяется как CLUSTERED
Это будет работать - и это допустимый вариант, если у вас есть существующая система, которую необходимо "перепроектировать" для повышения производительности. Для новой системы, если вы начинаете с нуля, и у вас нет сценария репликации, то я всегда выбираю ID INT IDENTITY(1,1) как мой кластерный первичный ключ - гораздо эффективнее, чем все остальное!
Я использую GUID в качестве PK с 2005 года. В этом мире распределенных баз данных это абсолютно лучший способ объединения распределенных данных. Вы можете запустить и забыть таблицы слияния, не беспокоясь о совпадении целых чисел между объединенными таблицами. Соединения GUID могут быть скопированы без каких-либо забот.
Это мои настройки для использования GUID:
PK = GUID. Идентификаторы GUID индексируются аналогично строкам, поэтому для таблиц с высокими строками (более 50 миллионов записей) может потребоваться разбиение таблиц или другие методы повышения производительности. SQL Server становится чрезвычайно эффективным, поэтому проблемы с производительностью становятся все менее и менее применимыми.
PK Guid является некластеризованным индексом. Никогда не кластеризируйте индекс GUID, если это не NewSequentialID. Но даже тогда перезагрузка сервера приведет к серьезным перебоям в заказе.
Добавьте ClusterID Int к каждой таблице. Это ваш КЛАСТЕРНЫЙ индекс... который заказывает ваш стол.
Объединение по ClusterID (int) более эффективно, но я работаю с 20-30 миллионами таблиц записей, поэтому объединение по GUID не оказывает заметного влияния на производительность. Если вы хотите максимальной производительности, используйте концепцию ClusterID в качестве основного ключа и присоединитесь к ClusterID.
Вот моя таблица электронной почты...
CREATE TABLE [Core].[Email] (
[EmailID] UNIQUEIDENTIFIER CONSTRAINT [DF_Email_EmailID] DEFAULT (newsequentialid()) NOT NULL,
[EmailAddress] NVARCHAR (50) CONSTRAINT [DF_Email_EmailAddress] DEFAULT ('') NOT NULL,
[CreatedDate] DATETIME CONSTRAINT [DF_Email_CreatedDate] DEFAULT (getutcdate()) NOT NULL,
[ClusterID] INT NOT NULL IDENTITY,
CONSTRAINT [PK_Email] PRIMARY KEY NonCLUSTERED ([EmailID] ASC)
);
GO
CREATE UNIQUE CLUSTERED INDEX [IX_Email_ClusterID] ON [Core].[Email] ([ClusterID])
GO
CREATE UNIQUE NonCLUSTERED INDEX [IX_Email_EmailAddress] ON [Core].[Email] ([EmailAddress] Asc)
В настоящее время я занимаюсь разработкой веб-приложения с EF Core, и вот шаблон, который я использую:
Все мои классы (таблицы) и INT PK и FK. У меня есть дополнительный столбец с типом Guid (сгенерированный конструктором C#) с некластеризованным индексом.
Все соединения таблицы в EF управляются через клавиши int, а весь доступ извне (контроллеры) осуществляется с помощью направляющих.
Это решение позволяет не показывать ключи int на URL-адресах, но позволяет поддерживать чистоту и порядок в модели.
Эта ссылка говорит, что это лучше, чем я мог, и помог в принятии решений. Я обычно выбираю int в качестве первичного ключа, если у меня нет особой необходимости, и я также позволяю SQL-серверу автоматически генерировать / поддерживать это поле, если у меня нет особых причин не делать этого. В действительности, проблемы производительности должны быть определены на основе вашего конкретного приложения. Здесь есть много факторов, в том числе ожидаемый размер БД, правильное индексирование, эффективные запросы и многое другое. Хотя люди могут с этим не согласиться, я думаю, что во многих сценариях вы не заметите различий ни с одним из этих вариантов, и вам следует выбрать то, что больше подходит для вашего приложения, а что позволяет разрабатывать проще, быстрее и эффективнее (если вы никогда не завершите приложение какая разница для остальных:).
PS Я не уверен, почему вы бы использовали Composite PK или какую выгоду вы считаете, что это даст вам.
Что ж, если ваши данные никогда не доходят до миллионов строк, все в порядке. Если вы спросите меня, я никогда не использую GUID в качестве столбца идентификации базы данных любого типа, включая PK, даже если вы заставляете меня проектировать с дробовиком во главе.
Использование GUID в качестве первичного ключа является решающим и решающим ограничителем масштабирования. Я рекомендую вам проверить идентичность базы данных и вариант последовательности. Последовательность не зависит от таблицы и может предоставить решение для ваших нужд (в MS SQL есть последовательности).
Если ваши таблицы начнут достигать нескольких десятков миллионов строк, например 50 миллионов, вы не сможете читать / записывать информацию в приемлемые сроки, и даже стандартное обслуживание индекса базы данных станет невозможным.
Затем вам нужно использовать секционирование и масштабируемость до полумиллиарда или даже 1-2 миллиардов строк. Добавление разделения по пути - не самое простое дело, все операторы чтения / записи должны включать столбец раздела (полные изменения приложения!).
Разумеется, эти числа (50 миллионов и 500 миллионов) предназначены для использования с небольшим отбором. Если вам нужно выбрать информацию сложным образом и / или иметь много вставок / обновлений / удалений, это может быть даже 1-2 миллиона и 50 миллионов вместо этого для очень требовательной системы. Если вы также добавите такие факторы, как модель полного восстановления, высокая доступность и отсутствие окна обслуживания, обычные для современных систем, все станет чрезвычайно некрасивым.
Обратите внимание, что на этом этапе 2 миллиарда - это предел int, который выглядит плохо, но int в 4 раза меньше и представляет собой последовательный тип данных, небольшой размер и последовательный тип являются фактором №1 для масштабируемости базы данных. И вы можете использовать большой int, который всего в два раза меньше, но все же последовательный, последовательный - это то, что действительно смертельно важно - даже более важно, чем размер - когда дело доходит до многих миллионов или нескольких миллиардов строк.
Если GUID также кластеризован, дела обстоят гораздо хуже. Просто вставка новой строки будет фактически храниться случайным образом повсюду в физическом положении.
Даже если это просто столбец, а не часть PK или PK, просто индексировать это проблема. С точки зрения фрагментации.
Наличие столбца guid совершенно нормально, как и любого столбца varchar, если вы не используете его как часть PK и в целом как ключевой столбец для объединения таблиц. Ваша база данных должна иметь свои собственные элементы PK, фильтруя и объединяя данные с их помощью - фильтрация также по GUID после этого вполне нормальна.
Если вы используете GUID в качестве первичного ключа и создаете кластерный индекс, тогда я предлагаю использовать для него значение по умолчанию NEWSEQUENTIALID()
Наличие последовательных идентификаторов значительно облегчает взломщику или провайдеру данных возможность взломать ваш сайт и данные. Имейте это в виду при выборе ПК для сайта.
Еще одна причина не раскрывать идентификатор в пользовательском интерфейсе заключается в том, что конкурент может видеть, как ваш идентификатор увеличивается в течение дня или другого периода, и таким образом рассчитывает объем вашего бизнеса.
В большинстве случаев его не следует использовать в качестве первичного ключа для таблицы, поскольку он действительно снижает производительность базы данных. полезные ссылки, касающиеся влияния GUID на производительность и в качестве первичного ключа.