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

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


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

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

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

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

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

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

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

    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    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
    {
        [DllImport("rpcrt4.dll", SetLastError = true)]
        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 [dbo].[AutoIncrementIntKeyEntities]([Name], [Count]) VALUES (@0, @1)
    SELECT [Id] FROM [dbo].[AutoIncrementIntKeyEntities] WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()
    

    В случае с GUID, сгенерированным на стороне сервера, получаем более сложный вариант:
    DECLARE @generated_keys table([Id] uniqueidentifier)
    INSERT [dbo].[GuidKeyDbNonSequentialEntities]([Name], [Count]) OUTPUT inserted.[Id] INTO @generated_keys VALUES (@name, @count)
    SELECT t.[Id] FROM @generated_keys AS g JOIN [dbo].[GuidKeyDbNonSequentialEntities] AS t ON g.[Id] = t.[Id] WHERE @@ROWCOUNT > 0
    

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

    Краткий тест показывает, что такой набор команд выполняется в среднем почти в три раза медленнее, чем просто INSERT на той же таблице. В случае автоинкремента не используется никаких дополнительных табличных переменных, поэтому и оверхед меньше.
    Share post
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 72

      +1
      Использование генерации GUID на стороне базы данных в разы медленнее, чем генерации на стороне клиента. Скорее всего, это связано с затратами на чтение только что добавленного идентификатора.

      Не очень ясно почему, неужели именно указанная причина будет оказывать такое влияние?
        +2
        Планирую разобраться с этим более детально и добавить результаты исследования в статью.
          0
          прочел статью, и нигде не нашел упоминания негативного эффекта использования случайной строки (GUID) в качестве primary key на индексацию таблицы. Primary key это же clustered index, те добавляя такую запись мы вынуждаем сиквел сервер копаться в таблице, чтобы разместить новую запись в правильное место. Для табличек размером в тысячи записей — плевать, а для милионов?
            0
            Во-первых, primary key — не обязательно кластерный. А во-вторых, именно для решения этой проблемы и берут псевдопоследовательные гуиды.
              0
              Странно, статья как раз в том числе и об этом. Вот эти абзацы например:

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


              и далее по тексту. Потом:

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


              и далее
            0
            Добавил информацию в конце статьи о том, почему получается такая разница
            +2
            Я конечно понимаю, что шанс сгенерировать два одинаковых GUID'a очень мал, но что будет если это все-таки произойдет? Отловить ошибку и попытаться вставить с новым GUID'ом? А если и тогда сгенерирует существующий?
              0
              В общем случае — повторить попытку. Для пользовательской транзакции это может выражаться в попытке сохранить изменения еще раз, для обработчика сообщений из очереди — сделать автоматический retry несколько раз, прежде чем поместить сообщение в очередь «проблемных» и т.д. Вероятность того, что GUID генератор подряд даст два одинаковых значения практически равна 0. Мне на практике не встречалось ни одного случая коллизий у GUID
                +3
                Ну тут следует учесть что шанс этот напрямую зависит от кол-ва записей в базе. Если у вас в базе уже есть несколько сотен миллионов GUID'ов, то шанс нарваться на коллизию значительно выше чем в случае «сгенерировать два идентичных ключа». Интересно кстати было бы посчитать вероятности
                  0
                  Все давно посчитано ;)

                  en.wikipedia.org/wiki/Globally_unique_identifier

                  the total number of unique such GUIDs is 2122 (approximately 5.3×1036). This number is so large that the probability of the same number being generated randomly twice is negligible
                    +1
                    Если очень быстро генерить GUID, то можно получить один и тот же два раза подряд.
                    В моей практике был очень неприятный случай.
                    Диск был SSD-шный, а данных на запись мало.
                    Вылетело необработанное исключение, не предусмотренное логикой программы.
                    Так как приложение было «почти enterprise» уровня, на орехи досталось всем.
                    Поставили костыль, обработку исключения и попытку получить новый GUID, после трех не успешных попыток добавлять интервал Sleep(100) между следующими тремя попытками. И если и это не помогло, то корректно откатывать транзакцию и показывать пользователю фигу.
                    Кроме всего прочего noname-сетевые платы могут содержать один и тот же MAC-адрес на всю партию, а он используется при генерации GUID.
                    В общем, ни в коем случае не стоит расслабляться.
                      +5
                      Если очень быстро генерить GUID, то можно получить один и тот же два раза подряд.

                      Что такое «очень быстро»? Как скорость генерации влияет на псевдослучайный генератор, используемый в гуидах?

                      Кроме всего прочего noname-сетевые платы могут содержать один и тот же MAC-адрес на всю партию, а он используется при генерации GUID.

                      У вас генератор первой версии, что ли, был? MAC-и (и время) использовались только в первой версии.
                        0
                        Да скорее всего первый.
                        В то время про версии GUID-а мало кто что слышал, около 2003-2004 годов.
                        Сейчас то конечно с уникальностью уже получше и от сетевых карт отказались.
                          0
                          Так может не стоит это переносить на существующие реалии?
                            0
                            Переносить наверно не стоит, но обжегшийся на молоке на воду дует.
                            Я же на всякий случай try… catch… буду ставить. Так спокойней спится.
                            А да диск был не ссд-шный, там рам-диск был, чего это с памятью то моей…
                              0
                              Проблема, понимаете ли, в том, что в ситуации, когда вам действительно нужен сгенеренный на клиенте GUID, try/catch вам не поможет, потому что данные уже давно улетели. Вам надо либо всю архитектуру строить исходя из того, что уникальные идентификаторы генерятся рядом с базой (что в определенных случаях просто существенно дороже), либо… доверять гуидам.
                          +2
                          Далее с символьного сервера Microsoft при
                          помощи WinDBG вытянул rpcrt4.dbg и rpcrt4.pdb. Скормив их IDA Pro, получил картину, поразившую меня.
                          Ниже схематично представлен алгоритм, как я его понял — от вершины и до функций нижнего уровня.

                          Заголовок спойлера
                          RPC_STATUS __stdcall UuidCreate(UUID *Uuid){

                          rc4_safe_select(x,x,x);

                          NewGenRandom(x,x,x,x);

                          rc4_safe_key(x,x,x,x);

                          }

                          __stdcall NewGenRandom(x,x,x,x)

                          {

                          NewGenRandomEx(x,x,x);

                          }

                          __stdcall NewGenRandomEx(x,x,x){

                          InitRand(x,x);

                          InitializeRNG(x);

                          GenRandom(x,x,x);

                          }

                          __stdcall InitRand(x,x){

                          InitCircularHash(x,x,x,x);

                          ReadSeed(x,x);

                          }

                          int __stdcall ReadSeed(LPBYTE lpData,DWORD cbData){

                          AccessSeed(x,x);

                          RegQueryValueEx(«SOFTWARE\Microsoft\Cryptography\RNG\Seed»);

                          }

                          int __stdcall AccessSeed(REGSAM samDesired,DWORD dwDisposition){

                          RegCreateKeyEx(«SOFTWARE\Microsoft\Cryptography\RNG\Seed»);

                          }

                          __stdcall InitializeRNG(x){

                          rc4_safe_startup(x);

                          InterlockedCompareExchangePointerWin95(x,x,x);

                          }

                          __stdcall InterlockedCompareExchangePointerWin95(x,x,x){

                          InterlockedCompareExchange();

                          }

                          __stdcall GenRandom(x,x,x){

                          RandomFillBuffer(x,x);

                          }

                          __stdcall RandomFillBuffer(x,x){

                          UpdateCircularHash(x,x,x);

                          rc4_safe_select(x,x,x);

                          GetCircularHashValue(x,x,x);

                          GatherRandomKey(x,x,x,x);

                          rc4_safe_key(x,x,x,x);

                          rc4_safe(x,x,x,x);

                          }

                          __stdcall UpdateCircularHash(x,x,x){

                          MD4Init(x);

                          MD4Update(x,x,x);

                          MD4Final(x);

                          }

                          int __stdcall GatherRandomKey(LPVOID lpInBuffer,DWORD nInBufferSize,LPVOID lpOutBuffer,LPDWORD lpBytesReturned){

                          GatherRandomKeyFastUserMode(x,x,x,x);

                          }

                          int __stdcall GatherRandomKeyFastUserMode(LPVOID lpInBuffer,DWORD nInBufferSize,LPVOID lpOutBuffer,LPDWORD lpBytesReturned){

                          IsRNGWinNT();

                          NtOpenFile("\\Device\\KsecDD");

                          InterlockedCompareExchangePointerWin95(x,x,x);

                          }


                          Таким образом, основа — это значение из реестра SOFTWARE\Microsoft\Cryptography\RNG\Seed, хэш MD4 и потоковый шифр RC4.

                          NtOpenFile("\\Device\\KsecDD");

                          обращение к драйверу KsecDD — и есть запрос hardware конфигурации.

                          именно этот драйвер вызывается при создании отчета «синий экран смерти» и при создании журнала аудита.

                          однозначно утверждать, что аппаратная конфигурация используется я не могу, но ряд строчек из статьи дают пищу для размышлений:

                          --In response, DevCon displays the device instance IDs of the USB devices

                          — The command specifies the Net class and then refines the search by specifying devices in the class whose hardware ID or compatible ID include «ndiswan.»

                          --In response, DevCon displays the device instance ID of the device and reports the result.

                    0
                    Я на работе у коллег слышал о случае совпадения гуидов в .NET-приложении лет шесть назад.
                    Но это так, к слову… Руководствоваться следует фактами, а не рассказами людей из интернетов :)
                      0
                      Стандартный механизм генерации GUID какой-то уникальный номер или MAC (детали не знаю) от сетевой карты. Поэтому в природных условиях сгенерировать одинаковые гуиды можно только путем целенаправленного шаманства.
                        0
                        Или путем покупки ноунейм сетевых, где один мак может быть на всю партию.
                          0
                          Как уже говорилось, не используйте генератор гуидов первой версии.
                    0
                    Вроде MS SQL Server гарантирует, что на стороне сервера генерируются именно что уникальные, а вот на клиенте можно нагенерировать чего угодно.
                    +1
                    Остается вопрос, как получать последовательные GUID на клиенте? К сожалению, стандартной функции в .NET для этих целей нет, но ее можно сделать, воспользовавшись P/Invoke:

                    Я видел другой вариант — GUID генерировался из двух частей, первая — текущая дата, вторая — рандом.
                      0
                      Ключ должен быть строго возрастающим. Если вторая часть — рандом, то он уже не дает строгого возрастания, если это не какой-то специальный рандом.
                        +1
                        Cтрогого возрастания нет, а так ли это нужно? Нужен гарантированно уникальный ключ, а возрастание даёт бонус в производительности, который теряем на P/Invoke www.codeproject.com/Articles/253444/PInvoke-Performance
                          0
                          Внимательно прочитайте статью, там как раз сравнивается перформанс разных вариантов, и затраты на P/Invoke там не видны. Как раз время вставки записей, в которых последовательный GUID ключ генерится через P/Invoke оказывается самым быстрым, потому что затраты на «обработку» непоследовательного ключа на порядки больше. Цитата приведеная выше из моей статьи говорит именно про последовательные гуиды, поэтому я не совсем понял, как предложенный способ генерации ключа соотносится с «последовательным».
                            0
                            Я считаю возможным три варианта: GUID строго последователен, GUID не строго последователен, GUID рандомен. В статье увидел только два варианта: seq и non-seq. Я может чего не понимаю, но для не строго последовательной генерации в большинстве случаев последовательность будет соблюдаться. Вижу только две ситуации когда этот вариант будет хуже — хайлоад и логи.

                            Вообще будет здорово добавить тест в сравнение, и учесть не только время вставки, но и время генерации самого GUIDа
                              0
                              Вообще будет здорово добавить тест в сравнение, и учесть не только время вставки, но и время генерации самого GUIDа


                              Время генерации включено во всех случаях (можно посмотреть в коде, который я на гитхабе выложил).
                          0
                          NEWSEQUENTIALID() не дает строго возрастания, он дает возрастание в рамках некой группы, группы периодически ротируются, поэтому в некий момент вы можете получить идентификатор, который меньше ранее созданного.
                            0
                            Согласен. Строго говоря он работает так:
                            Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique.

                            Взято вот отсюда
                              0
                              Я правильно понял, чтобы UuidCreateSequential отработал корректно и запись действительно попала в конец, клиент, генерирующий гуид и ms sql server должны находится на одной машине?
                                0
                                UuidCreateSequential не гарантирует, что запись попадет в конец. Все, что он гарантирует — это то, что какое-то время последовательные вызовы будут давать монотонно возрастающие гуиды.
                          +2
                          Реализация comb guid из NHibernate — старшие байты берем из даты-времени (что обеспечивает возрастание), младшие байты из Guid.NewGuid() обеспечивают уникальность.
                            0
                            Но и вероятность коллизии растет, ведь случайных байтов остается сильно меньше.

                            Хм, только сейчас обратил внимание на время комментария. Прошу прощения, кинули ссылкой, не заметил что год уже немного другой)
                          0
                          Спасибо, полезное исследование. Какая версия SQL Server использовалась? Интересно будут ли результаты отличаться в старых версиях (2008 например) и новых.
                            0
                            Использовался SQL Server 2012, если будет время — попробую разные версии.
                            0
                            Только нужно помнить, что «меньший объем» и «быстрее генерируется» — вещи относительные. Относительные относительно, в этих примерах, размера таблицы и скорости вставки, т.е. в подавляющем большинстве реальных сценариев это ничто, это даже не экономия на спичках.
                              0
                              Возможно, вопрос немного глупый, но что если бы можно было генерировать автоинкремент на клиенте? К примеру, при подключении к БД программа резервирует себе некоторый набор id-шников — [1001-1100] — и расходует при создании новых записей. По окончании запрашивает снова — [1601-1700]. Какие недостатки будут у такой системы?
                                0
                                Как вы предлагаете «резервировать»?
                                  0
                                  Одна общая последовательность на всю базу. Хотя, как сказали ниже, это будет узким местом.
                                    0
                                    Вот это и недостаток.
                                  0
                                  Можно, только зачем? Плюс нужно отслеживать коллизии, когда два параллельных процесса смогли зарезервировать одинаковый набор.
                                    0
                                    Или жестко лочить доступ к генератору последовательности на этапе получения «резервного» отрезка, что создает bottleneck. Потом, если приложение за время рабочей сессии ничего не добавило, что делать с резервированным отрезком? Если просто игнорировать и всегда получать по возрастанию, то можно быстро дойти до конца 32-битного integer. Можно взять больший тип, но в любом случае решение получается очень хрупкое и плохо масштабируемое.

                                    В реальной жизни в сценариях с репликацией и автоинкрементными ключами на разных репликах вручную прописывают разные диапазоны ключа. Например, на реплике А он стартует с 1, на реплике Б — с 1000000. Но это, опять же, требует ручного вмешательства, плохо масштабируется, и существует вероятность коллизии, когда одна реплика вставит по какой-то причине неожиданно большое количество записей.
                                      0
                                      Насколько я понимаю, последовательные GUID-ы тоже не на единицу отличаются, если рассматривать его как 128-битное число? Как вы сказали, 32 бита брать необязательно, можно те же 128. Если даже раздавать всем клиентам по 1000 каждый раз, то получится примерно 2^118 вариантов. Если база большая, к примеру расположена на 4 машинах, то можно реализовать такую же схему, как с клиентами — генератор последовательности на каждой машине получает свой большой диапазон у мастер-генератора (назовем его так): [1 — 1000000], [1000001 — 2000000],… Как вы считаете, такая схема реальна, или не стоит заморачиваться? В общем-то, основная причина, по которой я спрашиваю — поиск по целочисленному ключу теоретически должен быть быстрее поиска по GUID (что имеет значение при отказе от join-ов).
                                        0
                                        Если те же 128, то строго говоря разницы не будет. Что ГУИД, что 128-битовое число — набор байт одинаковой длины. Разница может быть только в порядке сравнения этих байт.

                                        Насчет скорости поиска — я планирую либо сделать вторую часть статьи, либо проапдейтить эту сравнениями производительности выборки. Действительно, это очень нужный в данном случае тест.
                                          0
                                          У меня подобная реализация несколько лет вполне успешно работает: #
                                          0
                                          автоинкримент всегда можно увеличивать с определенным шагом, зависящем от количества реплик + какой-нибудь запас.
                                            0
                                            Главное в этом случае не пользоваться identity(1, 10) и identity(2, 10) ибо если во втором данные обгонят по величине первые и перелить из второго в первое, то в первом новые ключи будут нумероваться с двойкой на конце. Ну или не переливать друг в друга.
                                          0
                                          для 2 процессов достаточно использовать позитивные и негативные диапазоны
                                          0
                                          Примерно так и работае HiLo Identity Generator в NHibernate.
                                          Hi (номер cледующего свободного блока) хранится в отдельной таблице в БД, при старте приложение атомарно считывает и инкрементирует текущее значение.
                                          Lo часть генерируется на клиентском приложении.
                                          Id вычисляется как Hi * 32767 + Lo.

                                          Из минусов могу назвать:
                                          1. Int32 действительно быстро кончаются.
                                          2. Неудобно вставлять данные из скриптов — приходится повторять логику по инкрементированию Hi и вычислению Id.
                                          3. Последовательности Id получаются с пропусками.
                                            0
                                            Такой подход требует, чтобы клиент поддерживал сеанс. Поэтому в случае REST\Web работать не будет.
                                              0
                                              В Entity Framework 7 появилась возможность получать последовательные айдишники блоками по 10 штук (по умолчанию). Гарантируется, что все блоки не пересекаются, получение потокобезопасно и коллизий возникнуть не может.
                                              С полученными блоками можно работать напрямую, явно указывая id при сохранении объекта.

                                              Проблемы те же, что и в NHibernate — пропуски в id, быстрое увеличение значений ключа. По умолчанию по-прежнему ключ генерируется в БД.
                                              Подробнее рассмотрено в видео блоге.
                                            0
                                            Простите чайниковский вопрос, конечно, но разве в столь продвинутой БД как MS SQL нет сущностей SEQUENCE? Мне отчего-то кажется, что использование последовательностей снимает все вопросы, поднятые в статье, нет?
                                              +3
                                              Есть. Но — никак не снимает. Фактически, использование последовательностей для идентификаторов ничем особо не отличается от Identity.
                                              +2
                                              Хосподи, ну что за статья-обрубок??
                                              «так как для некоторых ситуаций более предпочтительным может оказаться GUID» — ну хотя бы ма-аленько так про ситуации. Ну хоть парочку для каждого случая, чтобы сориентировать про какие ситуации вообще вам известно.
                                              «Приведены преимущества каждого из вариантов...» — вот это вообще не имеет смысла при отсутствии анализа недостатков (что там у нас с джойнами по GUIDу на серьезной базе?).
                                              «На практике возможны и другие, более редкие, типы ключа» — действительно, нафиг всю эту тягомотину с денормализацией и подходами к выбору оптимальных ключей! GUID и автоинкремент — наше все! А все остальное — редкая фигня.

                                              Теперь о тестах (и в продолжение об обрубках). Судя по ним, автор в базу исключительно пишет. Кстати, давайте поиграем со вставкой, когда таблица не пустая, а содержит миллион записей. С индексом, конечно. А с несколькими индексами? А с кластерным по ключу?
                                              А где, кстати, чтение, обновление, удаление? Джойны опять же?
                                              Слова про «ограничение оперативной памяти» есть только на диаграммах. Что сие означает и как (зачем) это было реализовано?
                                              Ну и раз уж речь идет о тестах, было бы любезно указать версию MSSQL и описать железо.

                                              Какая-то абстракция ни о чем. Вот на кой такое писать?
                                                0
                                                вот это вообще не имеет смысла при отсутствии анализа недостатков (что там у нас с джойнами по GUIDу на серьезной базе?).

                                                Что вы считаете серьезной базой? Десять на десять миллионов записей — все нормально.

                                                  0
                                                  Мы же с вами инженеры, правильно? ;)

                                                  Если основные задачи на базе — это запросы для построения отчетов и конечный пользователь радуется результату через 5 минут после запроса отчета за годовой период — это нормально. В общем случае при равных условиях джойн по GUIDу (16 байт) будет медленнее джойна по целочисленному (4 байта, например) ключу.
                                                  Если у нас онлайн торговая площадка/агрегатор — то здесь речь о нормально может идти при разговоре о сотнях миллисекунд.

                                                  Для абстрактного же (и поучающего) теста я ожидаю более четкой постановки задачи и всестороннего рассмотрения вариантов решения зачачи при этих ограничениях, иначе все это похоже на плохую лабу.
                                                    +1
                                                    В общем случае при равных условиях джойн по GUIDу (16 байт) будет медленнее джойна по целочисленному (4 байта, например) ключу.

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

                                                    Если у нас онлайн торговая площадка/агрегатор — то здесь речь о нормально может идти при разговоре о сотнях миллисекунд.

                                                    Там вообще не факт, что надо РСУБД использовать, так что обсуждение, что в качестве первичного ключа, теряет смысл.
                                                  0
                                                  Прежде чем писать в таком тоне, очень рекомендую самому попытаться написать хотя бы одну статью. И прочитать внимательно критикуемую статью, кстати, тоже.

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

                                                  Ниже вообще-то расписываются критерии, по которым предпочтительнее GUID, и даже выводы насчет его производительности в плане вставки есть в конце статьи? Или я должен был написать в стиле «Если у вас каталог для интенет магазина — используйте автоинкремент», если у вас база данных пользователей — используйте ГУИД"?

                                                  «Приведены преимущества каждого из вариантов...» — вот это вообще не имеет смысла при отсутствии анализа недостатков (что там у нас с джойнами по GUIDу на серьезной базе?).

                                                  Да, это указать можно, и в тексте статьи упоминается, как на скорость вычитки могут повлиять непоследовательные ГУИДы

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

                                                  Составные ключи (например, версия + номер записи), строковые ключи, иерархические ключи и т.д. Всех их рассмотреть в статье? В каком проценте решений они используются? Статья не претендует на полный гайдлайн по выбору ключей. Заголовок даже на это не намекает. Что за юношеский максимализм?

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

                                                  Про миллион записей — это когда вся таблица не умещается в памяти. И этот кейс рассмотрен.

                                                  А где, кстати, чтение, обновление, удаление? Джойны опять же?

                                                  Про чтение и джойны я плнаирую, как будет время, написать в part 2.

                                                  Слова про «ограничение оперативной памяти» есть только на диаграммах. Что сие означает и как (зачем) это было реализовано?

                                                  Если прочитать внимательно, то все можно увидеть.

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

                                                  Цель этого тестирования — сравнительные цифры, а не абсолютные. Сравнение дает понять как в одних и тех же условиях ведут себя разные варианты. Знание конкретного железа чем-то поможет?
                                                  +1
                                                  Недавно видел пост, о том как чуваки умудрились сделать коллизию, используя sequential id. Так что для распределенных случаев его категорически нельзя использовать. В итоге остается один достойный вариант — autoincrement.

                                                  Кроме того первичный ключ (вернее кластерный ключ) хранится во всех листах не кластерных индексов, поэтому выбор guid в качестве кластерного ключа увеличивает объем индексов и замедляет их обработку. Это я уже не говорю о том, что внешние ключи в вид guid увеличивают размеры таблиц.

                                                  Поэтому всегда имеет смысл делать кластерный ключ auto increment. Но если вы хорошо знаете SQL Server, то догадаетесь, что кластерный ключ и первичный ключ таблицы — разные вещи. Можно иметь кластерный ключ — auto increment для оптимизации хранения и первичный ключ guid, если у вас планируется распределенная БД.
                                                    0
                                                    И все-таки насчет одного достойного варианта — спорное утверждение. Настройка и поддержка репликации при использовании автоинкрементов — очень неприятное занятие.

                                                    Насчет кластерного и первичного — конечно же знаю разницу. И на практике были разные случаи. Например, когда таблица кластеризована по дате изменения объекта, а первичный ключ, естественно, не кластерный.
                                                      0
                                                      Встроенный механизм репликации в SQL Server как раз добавляет колонку с GUID (если не было объявлено колонки ROWGUID) и Timestamp. Вообще для репликации гуиду вовсе не надо быть ключом.

                                                      Например, когда таблица кластеризована по дате изменения объекта
                                                      При каждом обновлении меняется кластерный ключ? Это же гениально!
                                                        +1
                                                        А если эти изменения редки, а вычитка записей за последние 30 дней идет крайне часто? Все зависит от бизнес кейса ;)
                                                      0
                                                      В мульти-мастер кластере MySQL Galera интересно решается проблема с коллизиями автоинкрементов при записи с разных нод: Auto increments in Galera

                                                      Каждая нода генерирует свои уникальные непересекающиеся с другими нодами ID используя оффсет и размер инкремента.
                                                      +1
                                                      Тесты в статье некорректны.
                                                      При длине строкового поля размером более 4000 байт, то при вставке строки в страницы IN_ROW_DATA не попадает ничего и данные пишутся в страницы ROW_OVERFLOW_DATA. Это значит, что в тесте GUID произошло по факту гораздо меньше page splits, чем случилось бы в реальной системе.
                                                        0
                                                        Скорее всего вы говорите о количестве символов. Т.к. в NVarchar длина символа — 2 байта, то при 8000 байт там как раз 4000 символов. Или я как-то не так трактую technet.microsoft.com/en-us/library/ms186981%28v=sql.105%29.aspx
                                                          0
                                                          Да, 8000 байт или 4000 символов в nvarchar.
                                                            0
                                                            Т.е. в моих тестах все корретно. Единственное — опечатка на предпоследнем графике (там написано «символов» вместо «байт»), поправлю ее вечером.
                                                        0
                                                        Мое REPO на TSQL
                                                        Запускал по 3 раза
                                                        Microsoft SQL Server 2014 — 12.0.2269.0 (X64)
                                                        Jun 10 2015 03:35:45
                                                        Copyright © Microsoft Corporation
                                                        Developer Edition (64-bit) on Windows NT 6.3 (Build 9600: )
                                                        SSD drive

                                                        Заголовок спойлера
                                                        — IDENTITY
                                                        create table TestTable (
                                                           id int identity(1,1) not null primary key clustered,
                                                           sequence int not null,
                                                           data char(250) not null default '');
                                                        go
                                                        


                                                        — NEWID
                                                        create table TestTable (
                                                           id uniqueidentifier default newid() not null primary key clustered,
                                                           sequence int not null,
                                                           data char(250) not null default '');
                                                        go
                                                        


                                                        — NEWSEQUENTIALID
                                                        create table TestTable (
                                                           id uniqueidentifier default newsequentialid() not null primary key clustered,
                                                           sequence int not null,
                                                           data char(250) not null default '');
                                                        go
                                                        

                                                        тестируем вставкой 50.000 записей

                                                        declare @count int;
                                                        set @count = 0;
                                                        while @count < 50000 begin
                                                           insert TestTable (sequence)
                                                           values (@count);
                                                        
                                                           set @count = @count + 1;
                                                        end;
                                                        go
                                                        

                                                        смотрим что получилось:

                                                        — Get the number of read / writes for this session…
                                                        select reads, writes
                                                        from sys.dm_exec_sessions
                                                        where session_id = @@spid;
                                                        


                                                        — Get the page fragmentation and density at the leaf level.
                                                        select index_type_desc, index_depth, page_count, avg_page_space_used_in_percent, avg_fragmentation_in_percent, record_count
                                                        from sys.dm_db_index_physical_stats(db_id(), object_id('TestTable'), null, null, 'detailed')
                                                        where index_level = 0;
                                                        go
                                                        



                                                        ________________| inserting..| reads.....| writes.....| page_count...| avg_page_space_used_in_percent...| avg_fragmentation_in_percent
                                                        identity.....................| 13s..........| 61411.....| 58110....| 1667.............| 98.9177044724487............................| 0.659868026394721
                                                        NEWID()....._______| 12s.........| 57351.....| 56435....| 2498..............| 68.9704472448727............................| 98.7673956262425
                                                        newsequentialid.......| 11s..........| 62453.....| 59852....| 1725..............| 99.8882752656289............................| 0.869565217391304

                                                          0
                                                          А с джойнами как?
                                                            0
                                                            с joina-ми тока удвоенная нагрузка на CPU — оно и понятно — сравнение за 2 операции делается, вместо одной int-а.

                                                            SQL Repo
                                                            IF EXISTS( SELECT * FROM sysobjects WHERE name='ParentTable' AND (xtype='U') )
                                                            DROP TABLE dbo.ParentTable
                                                            GO
                                                            IF EXISTS( SELECT * FROM sysobjects WHERE name='TestTable' AND (xtype='U') )
                                                            DROP TABLE dbo.TestTable
                                                            GO

                                                            create table TestTable ( id int identity(1,1) not null primary key clustered, sequence int not null );
                                                            go
                                                            create table ParentTable (
                                                            id int identity(1,-1) not null primary key clustered,
                                                            ParentTableId int not null INDEX idx_ParentTableId
                                                            );
                                                            go
                                                            alter TABLE dbo.ParentTable with nocheck add CONSTRAINT FK_ParentTable FOREIGN KEY (ParentTableId) REFERENCES dbo.TestTable(id);
                                                            GO

                                                            --— NEWID
                                                            create table TestTable ( id uniqueidentifier default newid() not null primary key clustered, sequence int not null );
                                                            create table ParentTable (
                                                            id uniqueidentifier default newid() not null primary key clustered,
                                                            ParentTableId uniqueidentifier not null,
                                                            );
                                                            go
                                                            alter TABLE dbo.ParentTable with nocheck add CONSTRAINT FK_ParentTable FOREIGN KEY (ParentTableId) REFERENCES dbo.TestTable(id);
                                                            GO

                                                            --— NEWSEQUENTIALID
                                                            create table TestTable ( id uniqueidentifier default newsequentialid() not null primary key clustered, sequence int not null );
                                                            go
                                                            create table ParentTable (
                                                            id uniqueidentifier default newsequentialid() not null primary key clustered,
                                                            ParentTableId uniqueidentifier not null
                                                            );
                                                            go
                                                            alter TABLE dbo.ParentTable with nocheck add CONSTRAINT FK_ParentTable FOREIGN KEY (ParentTableId) REFERENCES dbo.TestTable(id);
                                                            GO

                                                            — тестируем вставкой 2,000,000 записей

                                                            declare count int = 0;
                                                            while count < 2000000 begin
                                                            INSERT INTO TestTable (sequence) VALUES ( count);
                                                            set count = count + 1;
                                                            end;
                                                            INSERT INTO ParentTable (ParentTableId) SELECT id FROM TestTable
                                                            go

                                                            select max(sequence) from ParentTable p join TestTable t on p.ParentTableId = t.id
                                                            where exists (select * from TestTable s where p.ParentTableId = s.id)

                                                            --смотрим что получилось:

                                                            --— Get the number of read / writes for this session…
                                                            select reads, writes
                                                            from sys.dm_exec_sessions
                                                            where session_id = @@spid;

                                                            --— Get the page fragmentation and density at the leaf level.
                                                            select index_type_desc, index_depth, page_count, avg_page_space_used_in_percent, avg_fragmentation_in_percent, record_count
                                                            from sys.dm_db_index_physical_stats(db_id(), object_id('TestTable'), null, null, 'detailed')
                                                            where index_level = 0;
                                                            go



                                                            http://www.figvam.ca/cloud/pics/i/7354.jpg

                                                            ________________| select join | inserting..| writes.....| page_count...| avg_page_space_used_in_percent...| avg_fragmentation_in_percent
                                                            identity.....................| 2s.............| 8m56s.....| 161777..| 4202. ............| 99.9429948109711............................| 0.404569252736792
                                                            NEWID()....._______| 2s.............| 8m48s.....| 227244..| 9999..............| 71.6404867803311............................| 99.0799079907991
                                                            newsequentialid.......| 2s.............| 8m49s.....| 244776..| 7169..............| 99.9306893995552............................| 0.711396289580137
                                                          0
                                                          У меня была ситуация, когда клиент вставлял пачками по 10-15М очень маленьких записей. Чтобы ускорить вставку, я пришёл к составному ключу: два поля Int32. Первое — автоинкремент с сервера, сеансовый ключ, который каждый клиент получает при каждом коннекте к серверу. Второе — внутренний инкремент на клиенте. Так у нас будет и последовательность и непротиворечивость, при чём обе гарантированные. Последовательность обеспечивается ещё и тем, что клиент перед заливкой данных, даже если и висел на связи несколько часов, всё равно делает реконнект, чтобы получить свежий сеансовый ключ и его заливка шла в конец базы. Клиентов много, но одновременная заливка двух крайне маловероятна, в основном они читают. Но даже если лить будут два, будем просто иметь перерасход кэша (нужно будет в памяти держать одновременно две страницы, по одной на каждого клиента).

                                                          Only users with full accounts can post comments. Log in, please.