К вопросу об идентификаторах

Автоинкременты и все, все, все…

Иван Бодягин

О чем речь

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

Идентификация

Как уже говорилось уникальная идентификация записей – это основа реляционных СУБД. Вообще данный вопрос довольно тесно связан с давним теоретическим спором «Суррогатные ключи» vs. «Естественные ключи». Но поскольку существует замечательная статья Анатолия Тенцера “Естественные ключи против искуственных ключей”, то не думаю, что здесь имеет смысл освещать этот вопрос. Вне зависимости от используемого подхода, очень часто, по тем или иным причинам, данные, относящиеся непосредственно к приложению, не удается заведомо однозначно разделить по записям. И логично, что в таких случаях прибегают к помощи сервера, который генерирует искусственные идентификаторы для каждой записи.

Особого внимания заслуживает термин «уникальный». Уникальность серверного идентификатора имеет свои границы. В каких-то случаях гарантируется уникальность в пределах таблицы, то есть если не предпринимать специальных усилий, то есть шанс получить одинаковые идентификаторы в разных таблицах, что не всегда приемлемо. В каких-то случаях этот идентификатор гарантировано уникален в пределах БД, чего тоже не всегда бывает достаточно. И, наконец, есть идентификаторы, гарантирующие уникальность в пределах всех СУБД вообще…

Способ генерации и вид этого идентификатора стандартом не оговорен, да и вообще стандарт подобными мелочами не занимается, поэтому каждый производитель СУБД реализует эту функциональность так, как ему удобно. В итоге, мы имеем потрясающий набор различных способов, выудить из сервера уникальный идентификатор, беда только в том, что у каждого сервера этот способ свой. Однако, несмотря на все многообразие подходов, практически во всех СУБД, в том или ином виде, реализована автоматическая генерация последовательных идентификаторов, это так называемые «автоинкременты». Иными словами, при обращении к серверу за таким идентификатором гарантируется, что возвращенное число будет больше, чем число, полученное при предыдущем обращении.

Microsoft SQL Server

Эта СУБД предоставляет два способа получить обеспечить уникальность записи. Один работает в пределах таблицы и является автоинкрементным, а другой замахивается на уникальность «вообще», и в принципе подобное решение покрывает практически все потребности в уникальных идентификаторах.

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

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

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

Здесь, как не сложно догадаться, seed – это самое первое значение, а increment – это величина, которая каждый раз будет прибавляться к предыдущему значению. По умолчанию seed и increment равны единице, то есть выставить у столбца свойство IDENTITY, равноценно выставлению IDENTITY(1,1). Ключевое слово IDENTITY может быть указано при создании CREATE TABLE, или изменении таблицы ALTER TABLE. При этом тип столбца должен быть tinyint, smallint, int, bigint, decimal(p,0) или numeric(p,0), то есть автоникремент должен быть целочисленным. Следует так же помнить, что только один столбец может быть объявлен IDENTITY. Таким образом, создание таблицы с автоинкрементным полем выглядит примерно так:

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

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

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

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

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

Если же теперь опять вернуть серверу возможность вставлять номера в автоинкрементное поле, то следующее значение будет больше, на число указанное в increment при создании столбца, самого большого значения в этом поле. Не важно вручную было введено это самое большое значение или сгенерировано сервером.

Все это конечно здорово, но, как правило, просто уникально идентифицировать запись недостаточно, необходимо еще связать эту запись с записью из другой таблицы по этому самому идентификатору. А для этого надо уметь получать этот идентификатор сразу же после его генерации. Для выполнения этой задачи в Microsoft SQL Server существуют 3 функции: @@IDENTITY, SCOPE_IDENTITY() и IDENT_CURRENT().

Функция @@IDENTITY возвращает последнее значение, записанное севером в автоинкрементный столбец в текущей сессии. Что это означает? Если между вызовом INSERT и вызовом @@IDENTITY успеет пролезть вставка из другой сессии, то @@IDENTITY вернет идентификатор, который был записан при первой вставке. То есть, при пользовании @@IDENTITY нет необходимости заботиться о том, что параллельные вставки будут мешать друг другу при получении правильных идентификаторов, сервер все сделает сам.

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

Для того чтобы избежать таких неприятностей, служит SCOPE_IDENTITY(), который возвращает значение, записанное сервером в автоинкрементный столбец не только в рамках сессии, но и в рамках текущего пакета (batch).

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

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

Однако следует учитывать еще ряд нюансов. Во-первых, очевидно, что никто не гарантирует отсутствия «дырок» при автоматической генерации значений в столбце. А, во вторых, генерация нового значения для автоинкремента выполняется в не явной автономной транзакции. Это означает, что если сама по себе операция добавления записи не увенчается успехом, или транзакция, в которой будет производиться добавление, закончится отменой, то сервер следующее автоинкрементное значение сгенерирует так, как будто бы предыдущее добавление новой записи произошло успешно. И таким образом, образуется разрыв в автоматической нумерации.

И можно наблюдать разрыв в идентификации записей.

И еще один нюанс, даже если удалить все записи из таблицы, последующие идентификаторы, возвращаемые сервером, не обнулятся, а будут продолжать увеличиваться, как будто записи не удалялись. В некоторых случаях может понадобиться обнулить серверный генератор последовательностей или проинициализировать его каким-нибудь другим значением. Для этих целей существует системная функция DBCC CHECKIDENT. С ее помощью можно проверить счетчик «на правильность» и/или проинициализировать его новым значением. Если же необходимо сбросить счетчик в начальное значение при удалении всех записей, то можно воспользоваться не оператором DELETE, а оператором TRUNCATE. В этом случае счетчик автоматически сбросится в первоначальное значение, но следует помнить, что TRUNCATE нельзя пользоваться, если на таблицу ссылаются внешние ключи других таблиц.

Существуют так же специальные команды, с помощью которых можно узнать начальное значение генератора определенной таблицы и приращение, то есть те значения, которые были установлены при указании IDENTITY. Это IDENT_INCR и IDENT_SEED соответственно.

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

Глобальный идентификатор

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

Для выхода из подобной ситуации Microsoft предлагает использовать тип данных uniqueidentifier - 16 байтное число, которое, будучи сгенеренным с помощью специальной функции, является гарантировано уникальным при любых обстоятельствах. Вся прелесть такого подхода заключается в том, что такой идентификатор, будучи полученным на одном сервере, заведомо не пересечется с другими подобными же идентификаторами, полученными на других серверах. Уникальный идентификатор получается с помощью функции NewID(). Типичный сценарий работы с таким идентификатором выглядит примерно так:

Для разработчиков под ADO.Net есть еще один удобный повод использовать подобный идентификатор. Поскольку, на самом деле, это обычный GUID, то при добавлении записей в отсоединенный набор данных (Dataset), можно совершенно спокойно получить этот идентификатор на клиенте и быть уверенным, что на сервере такого нет.

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

Производительность

Довольно часто возникает вопрос о производительности этих двух способов. Естественно, что в общем случае работа с identity быстрее, чем с GUID. Но заметно это становится только на больших объемах данных, порядка десятков миллионов записей на таблицу. На таких объемах выигрыш от использования identity составляет от 5 до 20% в зависимости от сложности запросов, но эти данные очень приблизительные и всерьез на них ориентироваться не стоит. Если стоит проблема выбора, то лучше померить на конкретных данных и структуре базы.

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

Timestamp (rowvesion)

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

Если в таблице имеется поле типа rowversion (оно, как и identity, может быть только одно на таблицу), то значение в этом поле будет автоматически меняться, при изменении любого другого поля в записи. Таким образом, запомнив предыдущее значение, можно определить - менялась запись, или нет, не проверяя всех полей. Для каждой базы SQL сервер ведет отдельный счетчик rowversion. При внесении изменения в любую таблицу со столбцом такого типа, счетчик увеличивается на единицу, после чего новое значение сохраняется в измененной строке. Текущее значение счетчика в базе данных хранится в переменной @@DBTS. Для хранения данных rowversion используется 8 байт, посему этот тип вполне может быть представлен, как varbinary(8) или binary(8).

Номер строки

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

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

ANSI SQL

Этот способ пронумеровать выводимые записи на сервере по идее должен работать на любых СУБД, минимально удовлетворяющих требованиям стандарта ANSI SQL.

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

Microsoft SQL Server

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

Oracle

Здесь можно отделаться более простым запросом, но тоже не совсем тривиальным. Эта СУБД дает некоторый доступ к своей внутренней информации, и внутри у нее записи пронумерованы. Но проблема в том, что сервер нумерует строки для своих нужд до сортировки, поэтому приходится делать вложенный запрос с сортировкой.

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

Постраничный вывод

Довольно часто, особенно при построении web-сайтов, приходится сталкиваться с задачей постраничного вывода записей. В некоторых СУБД есть специальные параметры для подобного вывода, а в некоторых все приходится делать самостоятельно. Но в любом случае серверу приходится выполнять примерно один и тот же объем работы. Сначала необходимо выбрать все записи, затем отсортировать их, а затем отправить клиенту нужный диапазон. Очевидно, что выдача диапазона без сортировки смысла не имеет, так как СУБД не гарантирует выдачу записей в каком-либо определенном порядке.

Microsoft SQL Server

Здесь можно придумать довольно много решений и все они будут по-своему хороши.

Во-первых, в данном случае, на удивление эффективным может оказаться применение динамических запросов. При удачном стечении обстоятельств (нужные индексы в нужных местах, и достаточно простые выборки пусть и на большом объеме данных) этот способ является самым быстрым.

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

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

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

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

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

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

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

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

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

Oracle

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

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

Yukon

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

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

Упростить постраничный вывод вряд ли возможно. Существуют СУБД, в которых введен специальный синтаксис для вывода данных постранично, но это не более чем syntactic sugar, так как производятся те же действия, что и в примерах выше, просто часть реализации остается за кадром.