Первичный ключ – GUID или автоинкремент

09.04.2019

Автоинкрементная в новой записи будет генерировать уникальный номер в таблицу во.

Поле AUTO ИНКРЕМЕНТ

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

Мы можем создать поле Автоинкрементная в таблице.

Синтаксис для MySQL

CREATE TABLE Persons
ID int NOT NULL AUTO_INCREMENT,

FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

MySQL использует ключевое слово AUTO_INCREMENT для выполнения автоинкрементным.

По умолчанию начальное значение AUTO_INCREMENT равно 1, и увеличивается на 1 для каждой новой записи.

Пусть последовательность AUTO_INCREMENT начать с другого значения, используйте следующий синтаксис SQL:

ALTER TABLE Persons AUTO_INCREMENT=100

VALUES ("Lars","Monsen")

Синтаксис для SQL Server

Следующий SQL заявление "Лица" таблицы в столбце "ИД" определяется как поля первичного ключа автоинкрементируемого:

CREATE TABLE Persons
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

MS SQL Server, используя ключевое слово IDENTITY выполнить Автоинкрементная.

В приведенном выше примере, начало значение тождественности равно 1, и увеличивается на 1 для каждой новой записи.

Совет: Чтобы указать столбец "ID" к исходным шагом 10 и 5, пожалуйста идентичность персонами (10,5).

Для того, чтобы вставить новую запись в таблице "Лица", мы не должны "ID" столбец заранее заданное значение (автоматически добавляет уникальное значение):

INSERT INTO Persons (FirstName,LastName)
VALUES ("Lars","Monsen")

SQL выше утверждение будет вставить новую запись в таблице "Лица". колонка "ID" присваивается уникальное значение. Колонка "FirstName" будет установлен в положение "Ларс", колонка "LastName" будет установлен в положение "MONSEN".

Синтаксис для доступа

CREATE TABLE Persons
ID Integer PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

MS Access, используя ключевое слово AUTOINCREMENT выполнить Автоинкрементная.

По умолчанию начальное значение AUTOINCREMENT равно 1, и увеличивается на 1 для каждой новой записи.

Совет: Чтобы указать столбец "ID" к исходной с шагом 10 и 5, пожалуйста, Autoincrement к Autoincrement (10,5).

INSERT INTO Persons (FirstName,LastName)
VALUES ("Lars","Monsen")

Синтаксис для Oracle

В Oracle, код немного сложнее.

Необходимо создать Автоинкрементная поля от объекта последовательности (который генерирует последовательность цифр).

Пожалуйста, используйте следующий синтаксис CREATE SEQUENCE:

CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10

Приведенный выше код создает объект последовательности под названием seq_person, который, начиная с 1 и увеличивается на 1. Значения кэша объектов 10 для повышения производительности. Кэш опция обеспечивает быстрый доступ к числу значений последовательности, которые будут сохранены.

Для того, чтобы вставить новую запись в таблице "Лица", мы должны использовать NEXTVAL функцию (эта функция возвращает следующее значение из последовательности seq_person):

INSERT INTO Persons (ID,FirstName,LastName)
VALUES (seq_person.nextval,"Lars","Monsen")

SQL выше утверждение будет вставить новую запись в таблице "Лица". столбец "ID" присвоен следующий номер в последовательности из seq_person. Колонка "FirstName" будет установлен в положение "Ларс", колонка "LastName" будет установлен в положение "MONSEN".

Программирование

Зачастую, когда разработчики сталкиваются с созданием модели данных, тип первичного ключа выбирается «по привычке», и чаще всего это автоинкрементное целочисленное поле. Но в реальности это не всегда является оптимальным решением, так как для некоторых ситуаций более предпочтительным может оказаться GUID. На практике возможны и другие, более редкие, типы ключа, но в данной статье мы их рассматривать не будем.

Ниже приведены преимущества каждого из вариантов.

Автоинкремент

Занимает меньший объем
Теоретически, более быстрая генерация нового значения
Более быстрая десериализация
Проще оперировать при отладке, поддержке, так как число гораздо легче запомнить

GUID

При репликации между несколькими экземплярами базы, где добавление новых записей происходит более, чем в одну реплику, GUID гарантирует отсутствие коллизий
Позволяет генерировать идентификатор записи на клиенте, до сохранения ее в базу
Обобщение первого пункта - обеспечивает уникальность идентификаторов не только в пределах одной таблицы, что для некоторых решений может быть важно
Делает практически невозможным «угадывание» ключа в случаях, когда запись можно получить, передав ее идентификатор в какой-нибудь публичный API

Фраза «теоретически, более быстрая генерация нового значения» звучит неубедительно. Подобные соображения всегда лучше подкреплять практическими примерами. Но прежде чем писать программу для тестирования, рассмотрим, какие варианты реализации первичного ключа есть с каждым из этих двух типов.

GUID можно генерировать как на клиенте, так и самой базой данных — уже два варианта. К тому же, в MS SQL есть две функции для получения уникального идентификатора — NEWID и NEWSEQUENTIALID. Давайте разберемся, в чем их отличие и может ли оно быть существенным на практике.

Привычная генерация уникальных идентификаторов в том же.NET через Guid.NewGuid() дает множество значений, не связанных друг с другом никакой закономерностью. Если ряд GUID-ов, полученных из этой функции, держать в отсортированном списке, то каждое новое добавляемое значение может «попадать» в любую его часть. Функция NEWID() в MS SQL работает аналогично — ряд ее значений весьма хаотичен. В свою очередь, NEWSEQUENTIALID() дает те же уникальные идентификаторы, только каждое новое значение этой функции больше предыдущего, при этом идентификатор остается «глобально уникальным».

Если использовать Entity Framework Code First, и объявить первичный ключ вот таким образом

Public Guid Id { get; set; }
в базе данных будет создана таблица с первичным кластерным ключом, который имеет значение по умолчанию NEWSEQUENTIALID(). Сделано это из соображений производительности. Опять же, в теории, вставлять новое значение в середину списка более накладно, чем добавление в конец. База данных, конечно же, не массив в памяти, и вставка новой записи в середину списка строк не приведет к физическому сдвигу всех последующих. Тем не менее, дополнительные накладные расходы будут — разделение страниц (page split). По итогу также будет сильная фрагментация индексов, которая может отразиться на производительности выборки данных. Неплохое объяснение того, как происходит вставка данных в кластеризованую таблицу, можно найти в ответах форума по этой ссылке .

Таким образом, для GUID мы имеем 4 варианта, которые стоит проанализировать в плане производительности: последовательный и непоследовательный GUID с генерацией на клиенте, и та же пара вариантов, но с генерацией на стороне базы. Остается вопрос, как получать последовательные GUID на клиенте? К сожалению, стандартной функции в.NET для этих целей нет, но ее можно сделать, воспользовавшись P/Invoke:

Internal static class SequentialGuidUtils { public static Guid CreateGuid() { Guid guid; int result = NativeMethods.UuidCreateSequential(out guid); if (result == 0) { var bytes = guid.ToByteArray(); var indexes = new int { 3, 2, 1, 0, 5, 4, 7, 6, 8, 9, 10, 11, 12, 13, 14, 15 }; return new Guid(indexes.Select(i => bytes[i]).ToArray()); } else throw new Exception("Error generating sequential GUID"); } } internal static class NativeMethods { public static extern int UuidCreateSequential(out Guid guid); }
Обратите внимание на то, что без специальной перестановки байт, GUID нельзя отдавать. Идентификаторы получатся корректные, но с точки зрения SQL сервера — непоследовательные, поэтому никакого выигрыша по сравнению с «обычным» GUID даже теоретически не получится. К сожалению, ошибочный код приведен во многих источниках.

К списку остается добавить пятый вариант — автоинкрементный первичный ключ. Других вариантов у него нет, так как на клиенте его генерировать нормально не получится.

С вариантами определились, но есть еще один параметр, который следует учесть при написании теста — физический размер строк таблицы. Размер страницы данных в MS SQL — 8 килобайт. Записи близкого или даже большего размера могут показать более сильный разброс производительности для каждого из вариантов ключа, чем на порядок меньшие записи. Чтобы обеспечить возможность варьировать размер записи, достаточно добавить в каждую из тестовых таблиц NVARCHAR поле, которое затем заполнять нужным количеством символов (один символ в NVARCHAR поле занимает 2 байта).

Тестирование

По этой ссылке находится проект с программой, которая была разработана с учетом указанных выше соображений.

Ниже приведены результаты тестов, которые выполнялись по такой схеме:

Всего три серии тестов с длиной текстового поля в записи 80, 800 и 8000 байт соответственно (количество символов в тестовой программе будет в два раза меньше в каждом из случаев, так как один символ в NVARCHAR занимает два байта).
В каждой из серий — по 5 запусков, каждый из которых добавляет по 10000 записей в каждую из таблиц. По результатам каждого из запусков можно будет проследить зависимость времени вставки от количества строк, уже находящихся в таблице.
Перед началом каждой из серий таблицы полностью очищаются.

И результаты с разбивкой по каждому запуску:

Из результатов сразу видно, что:

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

Над последним пунктом стоит задуматься. Из-за чего может происходить замедление работы при использовании непоследовательных GUID-ов, кроме как частого разделения страниц? Скорее всего — из-за частого чтения «случайных» страниц с диска. При использовании последовательного GUID, нужная страница всегда будет в памяти, так как добавление идет только в конец индекса. С непоследовательным будет много вставок в произвольные места индекса, и не во всех случаях нужные страницы будут находиться в памяти. Чтобы проверить, насколько такое случайное чтение влияет на результаты тестов, искусственно ограничим объем памяти SQL Server так, чтобы ни одна таблица не могла полностью уместиться в памяти.

Грубый расчет показывает, что в тесте с длиной строки 4000 символов (8000 байт) при количестве записей 50000 тысяч, размер таблицы будет не менее 400Мб. Ограничим допустимый объем памяти SQL Server до 256Мб и повторим этот тест.

Как и следовало ожидать, вставка в таблицы с непоследовательными GUID-ами в ключах заметно замедлилась, и замедление увеличивается с ростом количества строк в таблице. В то же время, производительность вставки для последовательных GUID-ов и автоинкремента остается стабильной.

Выводы

Если по каким-либо критериям, указанным в начале статьи, возникла надобность использовать GUID в качестве первичного ключа — наилучшим вариантом в плане производительности будет последовательный GUID, сгенерированный для каждой записи на клиенте.
Если создание GUID на клиенте по каким-либо причинам неприемлемо — можно воспользоваться генерацией идентификатора на стороне базы через NEWSEQUENTIALID(). Entity Framework делает это по умолчанию для GUID ключей, генерируемых на стороне базы. Но следует учесть, что производительность вставки будет заметно меньше по сравнению с созданием идентификатора на стороне клиента. Для проектов, где количество вставок в таблицы невелико, эта разница не будет критична. Еще, скорее всего, этот оверхед можно избежать в сценариях, где не нужно сразу же получать идентификатор вставленной записи, но такое решение не будет универсальным.
Если в вашем проекте уже используются непоследовательные GUID, то следует задуматься об исправлении, если количество вставок в таблицы велико и размер базы значительно больше, чем размер доступной оперативной памяти.
У других СУБД разница в производительности может быть совершенно другой, поэтому полученные результаты можно рассматривать только применительно к Microsoft SQL Server. В то время как базовые критерии, указанные в начале статьи, справедливы независимо от конкретной СУБД.

UPD: Почему вариант с генерацией GUID ключа на стороне базы работает медленно

Когда Entity Framework выполняет вставку в таблицу с автоинкрементным ключем, SQL команды выглядит примерно следующим образом:
INSERT .(, ) VALUES (@0, @1) SELECT FROM . WHERE @@ROWCOUNT > 0 AND = scope_identity()
В случае с GUID, сгенерированным на стороне сервера, получаем более сложный вариант:
DECLARE @generated_keys table( uniqueidentifier) INSERT .(, ) OUTPUT inserted. INTO @generated_keys VALUES (@name, @count) SELECT t. FROM @generated_keys AS g JOIN . AS t ON g. = t. WHERE @@ROWCOUNT > 0
Таким образом, чтобы клиент мог получить ключ только что добавленной строки, создается табличная переменная, в которую заносится значение ключа при вставке, а потом из этой табличной переменной идет выборка для возврата полученного значения на клиент.

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

Создание поля автоинкремента (счетчика) в таблице базы данных MS SQL Server , которая размещается в «*.mdf» -файле

В данной теме показано как создать поле-счетчик (уникальное поле) в таблице базы данных Microsoft SQL Server , которая размещается в файле «MyDataBase.mdf» .

Данная тема базируется на предыдущих темах:

Условие задачи

Заданы файлы «MyDatabase.mdf» и «MyDataBase.ldf» базы данных, которые предназначены для работы в системе управления реляционными базами данных Microsoft SQL Server . Файлы можно скачать в архиве .

База данных содержит одну таблицу с именем Source . Таблица Source содержит следующие поля:

ID_Source – целого типа (int );
Name – типа «строка» из 50 символов;
Address – типа строка из 100 символов.

В задаче нужно сделать поле ID_Source уникальным счетчиком. При добавлении новой записи в таблицу, значение поля должно увеличиваться на 1 (автоинкрементное поле), то есть быть уникальным.

Выполнение

1. Подключение «*.mdf» -файла базы данных в MS Visual Studio

Запустить на выполнение MS Visual Studio .

С помощью утилиты Server Explorer нужно подключить ранее созданніе файлы «MyDataBase.mdf» и «MyDataBase.ldf» . Архив с файлами можно загрузить .

Рекомендуется, чтобы файлы были размещены в одном каталоге.
Пример добавления готового (ранее созданного) «*.mdf» -файла локальной базы данных к MS Visual Studio подробно описывается в статье:

После подключения базы данных в окне Server Explorer будет отображена база данных «MyDataBase.mdf» (рисунок 1).

База данных содержит одну таблицу Source (рисунок 2), которая содержит поля в соответствии с условием задачи.

Рис. 1. База данных MyDataBase.mdf в окне Server Explorer

Рис. 2. Таблица Source

2. Настройка поля ID_Source как счетчика. Свойство «Identity Column»

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

Чтобы установить поле ID_Source уникальным, нужно выполнить следующие действия:

раскрыть вкладку Tables в базе данных MyDataBase.mdf (рисунок 3);
в вкладке Tables сделать правый клик «мышкой» и из контекстного меню вызвать команду «Open Table Definition» (рисунок 3). В результате откроется окно определения полей таблицы;
активировать поле ID_Source и в окне свойств «Properties» установить значение свойства «Identity Column = ID_Source» (рисунок 4);
сохранить и закрыть таблицу Source .

Рис. 3. Команда «Open Table Definition»

Рис. 4. Установление свойства Identity Column в значение ID_Source

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

Теперь эту таблицу можно использовать в своих проектах.

3. Заполнение таблицы данными

После установлки в таблице поля ID_Source как уникального счетчика можно программно или вручную заполнять таблицу данными (записями).

Чтобы заполнить таблицу данными (записями) нужно выполнить следующие действия:

вызвать контекстное меню с помощью правого клика мышкой на таблице Source и из контекстного меню выбрать команду «Show Table Data» (рисунок 5). В результате откроется окно ввода данных в таблицу (рисунок 6);
ввести данные в таблицу Source . Поскольку, поле ID_Source есть счетчиком, то вводить данные в это поле не удастся. Можно вводить данные только в полях Name и Address . Значение поля Source будет генерироваться автоматически.

Рис. 5. Команда «Show Table Data»

Рис. 6. Ввод данных в таблицу Source