Размещено на http://www.allbest.ru/

Стратегии проектирования первичного ключа в Microsoft Sql Server

Герасимов А.А.,

Тихомирова А.Н.

Аннотации

В данной работе представлены стратегии проектировании первичного ключа на примере базы данных Microsoft SQL Server. В данной статье показаны две стратегии проектирования первичного ключа, описаны их преимущества и недостатки. Также в статье показаны, какие типы первичного ключа лучше использовать в различных типах разрабатываемых системах.

Ключевые слова: Microsoft SQL Server, первичный ключ, стратегии проектирования первичного ключа

STRATEGIES FOR DESIGNING A PRIMARY KEY IN MICROSOFT SQL SERVER

Gerasimov A.A., Tikhomirova A.N.

In this paper, we present strategies for designing a primary key using the example of a Microsoft SQL Server database. This article shows two strategies for designing a primary key, describes their advantages and disadvantages. Also, the article shows which types of primary key are best used in various kinds of developed systems.

Keywords: Microsoft SQL Server, primary key, primary key design strategies

Введение

При разработке многих современных информационных систем и приложений используются реляционные базы данных. Реляционная база данных состоит из таблиц, в которой и хранится вся информация. Так как любую информацию нужно идентифицировать и работать с ней, был придуман первичный ключ. Проблемы с проектированием первичного ключа постоянно возникают у многих разработчиков баз данных, потому что нет однозначного решения для различных типов систем, любой подход хорош в чем-то своём. В работах [1],[3],[4] описаны данные стратегии с точки зрения разработчика, то есть как их создавать и внедрять в базу, но нет никакой информации о том, какая лучше стратегия и способ подходит для конкретной системы, а также почти отсутствует информация о различиях в типах хранения обычных данных и первичного ключа. В данной работе будет рассмотрено проектирование первичного ключа, а также показаны различные случаи применение конкретных стратегий.

Цель

Описать стратегии проектирования первичного ключа в Microsoft SQL Server, показать преимущества и недостатки этих стратегий, а также рассмотреть случаи применения данных стратегий для различного рода систем.

Первичный ключ

Первичный ключ - в реляционной модели данных один из потенциальных ключей отношения, выбранный в качестве основного ключа (или ключа по умолчанию). По сути, первичный ключ таблицы однозначно определяет каждую строку в этой таблице. Первичный ключ делится на несколько видов, а точнее несколько стратегий, которые можно использовать при проектировании таблиц: естественный и суррогатный. [2]

Естественный первичный ключ

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

Рассмотрим пример представленный на рисунке 1.

Рис. 1 - Изменение хранения данных при построении кластерного индекса

На рисунке 1, в таблице слева, представлен тип хранения данных в памяти компьютера строк таблицы, у которой нет первичного ключа или индекса. Такие данные хранятся в памяти типом хранения под названием "куча", что подразумевает, что данные хранятся в такой последовательности, в которой их добавили. После построения первичного ключа или индекса данные сортируются и хранятся в памяти уже согласно их значению (в таблице справа). Такая структура построения первичного ключа делается для того, чтобы впоследствии при выполнении необходимой операции (добавления, обновления или поиска) база данных не просматривала все записи (как это происходит при способе хранения "куча"), а просматривала лишь часть данных. Стоит отметить, что после построения первичного ключа поиск данных (а также добавление и обновление) по столбцу, по которому был построен этот ключ, происходит при помощи алгоритма бинарного дерева (иногда поиск в базе данных происходит по алгоритму 2-3 деревьев или красночерного дерева, это уже зависит непосредственно от реализации самой базы данных), что позволяет сильно увеличить скорость поиска. На рисунке 1 представлен пример, в котором происходит построение естественного первичного ключа по номеру паспорта. Выбор данного первичного ключа плох с точки зрения оптимизации, потому что теперь при работе с новым значением, база данных будет сравнивать значение номера паспорта новой строки с уже имеющимися, а уже потом производить добавление в нужное место. Когда в базе данных первичный ключ построен по столбцу, которое содержит большое значение, то все операцию происходят с задержкой, потому что база данных тратит много ресурсов при работе с такими значениями. Если же в виде естественного первичного ключа будет строковый тип данных, то все операции будут работать ещё дольше, так как операции со строками занимают больше времени, чем работа с числами. ключ кластерный индекс

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

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

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

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

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

Суррогатный первичный ключ

Суррогатный первичный ключ - это дополнительное служебное поле, добавленное к уже имеющимся информационным полям таблицы, единственное предназначение которого - служить первичным ключом. Значение этого поля не образуется на основе каких-либо других данных из БД, а генерируется искусственно.[5]

Способы генерации суррогатного первичного ключа

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

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

id int primary key identity(`Начальное значение','Шаг изменения')

При таком способе задания первичного ключа, каждое новая строка будет менять значение id настолько, насколько разработчик поставил `Шаг изменения'. Автоинкремент хорош тем, что каждое новое значение добавляется в конец, то есть не нужно тратить лишние ресурсы при работе с новым значением. База данных сама быстро находит место для нового значения и быстро выполняет операцию поиска по данному значению. Также при выборе такого первичного ключа не нужно задумываться о поиске уникального значения, база данных сама генерирует его.

Минусы такого подхода раскрываются тогда, когда нужно узнать точное значение следующего id. В текущем соединении можно узнать последнее значение первичного ключа, но узнать следующее невозможно, потому что `Шаг изменения' известен лишь разработчику базы данных, а чтобы узнать значение, пользователю базы нужно иметь на это разрешение, но в большинстве случаев пользователи такого разрешения не имеют (из-за того, что эти данные являются уже внутренней структурой базы, и доступа туда пользователь не должен иметь). Также невозможно узнать текущее значение первичного ключа, если на таблицу повешен триггер. При запросе о значении первичного ключа, база данных будет выдавать данные не о текущей таблице, а о таблице, на которую ссылается триггер. Также, к минусам автоинкремента можно отнести тот факт, что база данных сама берёт на себя работу с данным полем, поэтому изменить значение такого ключа невозможно.

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

2. Последовательности

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

Create sequense `Название последовательности'

Start with `Начальное значение'

Increment by `Шаг измениения'

После создания последовательности следующее значение можно узнать с помощью команды:

select next value for `Название последовательности'.

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

3. Глобальный уникальный индентификатор (GUID).

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

1. newID() - генерирует абсолютно случайное новое значение

2.newSequientialId() - генерирует новое значение, которое больше предыдущего, после генерации данной функции.

Если появилась необходимость использования глобального уникального идентификатора, то лучше использовать newSequientialId(), в связи с тем, что новые значения всегда будут добавляться в конец, не будут тратиться ресурсы для поиска нужного места новому значению.

Минусом данного стратегии является то, что данное поле занимает 16 байт, что является очень большим числом для одного поля. Также, данное поле имеет такой формат DC76B0EB-934342EF-A81B-B9CF45078C44, при котором сразу отпадает желание делать внешний ключ к такому первичному ключу. Также, хоть и по данному полю построен кластерный индекс (как уже писалось выше, то по любому первичному ключу строится кластерный индекс), но поиск необходимых значений всё равно занимает много ресурсов.

Данный подход чаще всего встречается, когда необходимо получить полностью уникальное поле, значение которого точно не совпадёт ни с каким другим. Такой случай можно встретить, когда на этапе разработки таблицы есть четкое понимание, что база данных, в которой будет лежать данная таблица, будет сливаться с другой базой данных, и есть потребность в точной уникальности какого-то поля в таблице. Если же есть потребность в уникальности лишь в пределах базы, то для этого идеально подходит timestamp. 4. timestamp (rowversion)

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

Получить данное значение можно с помощью команды:

Select @@DBTS

Данное значение будет представлено в шестнадцатиричном формате.

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

Анализ рассмотренных стратегий

На основе проделанной работы приведем итоговую таблицу анализа рассмотренных стратегий.

Таблица 1 - Анализ рассмотренных стратегий

Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Заключение

В рамках данной статьи были рассмотрены стратегии проектирования первичного ключа в Microsoft SQL Server. Рассмотрены различные способы проектирования первичного ключа, показаны преимущества и недостатки каждого из способов. Также в работе приведены случаи использования стратегий проектирования ключей для конкретных систем. В работе представлена итоговая таблица всех стратегий, в которой приведены ключевые параметры каждой из стратегий.

Список литературы

1. Л. Бейли. Изучаем SQL. Питер, 2012. - 415 с.

2. Ицик Бен-Ган. Основы T-SQL.ЭКСМО, 2015. - 398 с.

3. В.В. Кириллов, Г.Ю. Громов. Введение в реляционные базы данных. БХВ-Петербург, 2009. - 528 с.

4. Б.А. Новиков, Г.Р. Домбровская. Настройка приложений баз данных. БХВ-Петербург, 2006. - 412 с.

5. Д. Петкович. Microsoft SQL Server 2012. БХВ-Петербург, 2014-- 791с.

Размещено на Allbest.ru