Comments 72
Использование генерации GUID на стороне базы данных в разы медленнее, чем генерации на стороне клиента. Скорее всего, это связано с затратами на чтение только что добавленного идентификатора.
Не очень ясно почему, неужели именно указанная причина будет оказывать такое влияние?
Планирую разобраться с этим более детально и добавить результаты исследования в статью.
прочел статью, и нигде не нашел упоминания негативного эффекта использования случайной строки (GUID) в качестве primary key на индексацию таблицы. Primary key это же clustered index, те добавляя такую запись мы вынуждаем сиквел сервер копаться в таблице, чтобы разместить новую запись в правильное место. Для табличек размером в тысячи записей — плевать, а для милионов?
Во-первых, primary key — не обязательно кластерный. А во-вторых, именно для решения этой проблемы и берут псевдопоследовательные гуиды.
Странно, статья как раз в том числе и об этом. Вот эти абзацы например:
и далее по тексту. Потом:
и далее
Привычная генерация уникальных идентификаторов в том же .NET через Guid.NewGuid() дает множество значений, не связанных друг с другом никакой закономерностью. Если ряд GUID-ов, полученных из этой функции, держать в отсортированном списке, то каждое новое добавляемое значение может «попадать» в любую его часть.
и далее по тексту. Потом:
Над последним пунктом стоит задуматься. Из-за чего может происходить замедление работы при использовании непоследовательных GUID-ов, кроме как частого разделения страниц? Скорее всего — из-за частого чтения «случайных» страниц с диска.
и далее
Добавил информацию в конце статьи о том, почему получается такая разница
Я конечно понимаю, что шанс сгенерировать два одинаковых GUID'a очень мал, но что будет если это все-таки произойдет? Отловить ошибку и попытаться вставить с новым GUID'ом? А если и тогда сгенерирует существующий?
В общем случае — повторить попытку. Для пользовательской транзакции это может выражаться в попытке сохранить изменения еще раз, для обработчика сообщений из очереди — сделать автоматический retry несколько раз, прежде чем поместить сообщение в очередь «проблемных» и т.д. Вероятность того, что GUID генератор подряд даст два одинаковых значения практически равна 0. Мне на практике не встречалось ни одного случая коллизий у GUID
Ну тут следует учесть что шанс этот напрямую зависит от кол-ва записей в базе. Если у вас в базе уже есть несколько сотен миллионов GUID'ов, то шанс нарваться на коллизию значительно выше чем в случае «сгенерировать два идентичных ключа». Интересно кстати было бы посчитать вероятности
Все давно посчитано ;)
en.wikipedia.org/wiki/Globally_unique_identifier
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
Если очень быстро генерить GUID, то можно получить один и тот же два раза подряд.
В моей практике был очень неприятный случай.
Диск был SSD-шный, а данных на запись мало.
Вылетело необработанное исключение, не предусмотренное логикой программы.
Так как приложение было «почти enterprise» уровня, на орехи досталось всем.
Поставили костыль, обработку исключения и попытку получить новый GUID, после трех не успешных попыток добавлять интервал Sleep(100) между следующими тремя попытками. И если и это не помогло, то корректно откатывать транзакцию и показывать пользователю фигу.
Кроме всего прочего noname-сетевые платы могут содержать один и тот же MAC-адрес на всю партию, а он используется при генерации GUID.
В общем, ни в коем случае не стоит расслабляться.
В моей практике был очень неприятный случай.
Диск был SSD-шный, а данных на запись мало.
Вылетело необработанное исключение, не предусмотренное логикой программы.
Так как приложение было «почти enterprise» уровня, на орехи досталось всем.
Поставили костыль, обработку исключения и попытку получить новый GUID, после трех не успешных попыток добавлять интервал Sleep(100) между следующими тремя попытками. И если и это не помогло, то корректно откатывать транзакцию и показывать пользователю фигу.
Кроме всего прочего noname-сетевые платы могут содержать один и тот же MAC-адрес на всю партию, а он используется при генерации GUID.
В общем, ни в коем случае не стоит расслабляться.
Если очень быстро генерить GUID, то можно получить один и тот же два раза подряд.
Что такое «очень быстро»? Как скорость генерации влияет на псевдослучайный генератор, используемый в гуидах?
Кроме всего прочего noname-сетевые платы могут содержать один и тот же MAC-адрес на всю партию, а он используется при генерации GUID.
У вас генератор первой версии, что ли, был? MAC-и (и время) использовались только в первой версии.
Да скорее всего первый.
В то время про версии GUID-а мало кто что слышал, около 2003-2004 годов.
Сейчас то конечно с уникальностью уже получше и от сетевых карт отказались.
В то время про версии GUID-а мало кто что слышал, около 2003-2004 годов.
Сейчас то конечно с уникальностью уже получше и от сетевых карт отказались.
Так может не стоит это переносить на существующие реалии?
Переносить наверно не стоит, но обжегшийся на молоке на воду дует.
Я же на всякий случай try… catch… буду ставить. Так спокойней спится.
А да диск был не ссд-шный, там рам-диск был, чего это с памятью то моей…
Я же на всякий случай try… catch… буду ставить. Так спокойней спится.
А да диск был не ссд-шный, там рам-диск был, чего это с памятью то моей…
Проблема, понимаете ли, в том, что в ситуации, когда вам действительно нужен сгенеренный на клиенте GUID, try/catch вам не поможет, потому что данные уже давно улетели. Вам надо либо всю архитектуру строить исходя из того, что уникальные идентификаторы генерятся рядом с базой (что в определенных случаях просто существенно дороже), либо… доверять гуидам.
Далее с символьного сервера Microsoft при
помощи WinDBG вытянул rpcrt4.dbg и rpcrt4.pdb. Скормив их IDA Pro, получил картину, поразившую меня.
Ниже схематично представлен алгоритм, как я его понял — от вершины и до функций нижнего уровня.
Таким образом, основа — это значение из реестра 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.
помощи 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);
}
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.
Я на работе у коллег слышал о случае совпадения гуидов в .NET-приложении лет шесть назад.
Но это так, к слову… Руководствоваться следует фактами, а не рассказами людей из интернетов :)
Но это так, к слову… Руководствоваться следует фактами, а не рассказами людей из интернетов :)
Вроде MS SQL Server гарантирует, что на стороне сервера генерируются именно что уникальные, а вот на клиенте можно нагенерировать чего угодно.
Остается вопрос, как получать последовательные GUID на клиенте? К сожалению, стандартной функции в .NET для этих целей нет, но ее можно сделать, воспользовавшись P/Invoke:
Я видел другой вариант — GUID генерировался из двух частей, первая — текущая дата, вторая — рандом.
Ключ должен быть строго возрастающим. Если вторая часть — рандом, то он уже не дает строгого возрастания, если это не какой-то специальный рандом.
Cтрогого возрастания нет, а так ли это нужно? Нужен гарантированно уникальный ключ, а возрастание даёт бонус в производительности, который теряем на P/Invoke www.codeproject.com/Articles/253444/PInvoke-Performance
Внимательно прочитайте статью, там как раз сравнивается перформанс разных вариантов, и затраты на P/Invoke там не видны. Как раз время вставки записей, в которых последовательный GUID ключ генерится через P/Invoke оказывается самым быстрым, потому что затраты на «обработку» непоследовательного ключа на порядки больше. Цитата приведеная выше из моей статьи говорит именно про последовательные гуиды, поэтому я не совсем понял, как предложенный способ генерации ключа соотносится с «последовательным».
Я считаю возможным три варианта: GUID строго последователен, GUID не строго последователен, GUID рандомен. В статье увидел только два варианта: seq и non-seq. Я может чего не понимаю, но для не строго последовательной генерации в большинстве случаев последовательность будет соблюдаться. Вижу только две ситуации когда этот вариант будет хуже — хайлоад и логи.
Вообще будет здорово добавить тест в сравнение, и учесть не только время вставки, но и время генерации самого GUIDа
Вообще будет здорово добавить тест в сравнение, и учесть не только время вставки, но и время генерации самого GUIDа
NEWSEQUENTIALID() не дает строго возрастания, он дает возрастание в рамках некой группы, группы периодически ротируются, поэтому в некий момент вы можете получить идентификатор, который меньше ранее созданного.Согласен. Строго говоря он работает так:
Взято вот отсюда
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.
Взято вот отсюда
Реализация comb guid из NHibernate — старшие байты берем из даты-времени (что обеспечивает возрастание), младшие байты из Guid.NewGuid() обеспечивают уникальность.
Спасибо, полезное исследование. Какая версия SQL Server использовалась? Интересно будут ли результаты отличаться в старых версиях (2008 например) и новых.
Только нужно помнить, что «меньший объем» и «быстрее генерируется» — вещи относительные. Относительные относительно, в этих примерах, размера таблицы и скорости вставки, т.е. в подавляющем большинстве реальных сценариев это ничто, это даже не экономия на спичках.
Возможно, вопрос немного глупый, но что если бы можно было генерировать автоинкремент на клиенте? К примеру, при подключении к БД программа резервирует себе некоторый набор id-шников — [1001-1100] — и расходует при создании новых записей. По окончании запрашивает снова — [1601-1700]. Какие недостатки будут у такой системы?
Как вы предлагаете «резервировать»?
Можно, только зачем? Плюс нужно отслеживать коллизии, когда два параллельных процесса смогли зарезервировать одинаковый набор.
Или жестко лочить доступ к генератору последовательности на этапе получения «резервного» отрезка, что создает bottleneck. Потом, если приложение за время рабочей сессии ничего не добавило, что делать с резервированным отрезком? Если просто игнорировать и всегда получать по возрастанию, то можно быстро дойти до конца 32-битного integer. Можно взять больший тип, но в любом случае решение получается очень хрупкое и плохо масштабируемое.
В реальной жизни в сценариях с репликацией и автоинкрементными ключами на разных репликах вручную прописывают разные диапазоны ключа. Например, на реплике А он стартует с 1, на реплике Б — с 1000000. Но это, опять же, требует ручного вмешательства, плохо масштабируется, и существует вероятность коллизии, когда одна реплика вставит по какой-то причине неожиданно большое количество записей.
В реальной жизни в сценариях с репликацией и автоинкрементными ключами на разных репликах вручную прописывают разные диапазоны ключа. Например, на реплике А он стартует с 1, на реплике Б — с 1000000. Но это, опять же, требует ручного вмешательства, плохо масштабируется, и существует вероятность коллизии, когда одна реплика вставит по какой-то причине неожиданно большое количество записей.
Насколько я понимаю, последовательные GUID-ы тоже не на единицу отличаются, если рассматривать его как 128-битное число? Как вы сказали, 32 бита брать необязательно, можно те же 128. Если даже раздавать всем клиентам по 1000 каждый раз, то получится примерно 2^118 вариантов. Если база большая, к примеру расположена на 4 машинах, то можно реализовать такую же схему, как с клиентами — генератор последовательности на каждой машине получает свой большой диапазон у мастер-генератора (назовем его так): [1 — 1000000], [1000001 — 2000000],… Как вы считаете, такая схема реальна, или не стоит заморачиваться? В общем-то, основная причина, по которой я спрашиваю — поиск по целочисленному ключу теоретически должен быть быстрее поиска по GUID (что имеет значение при отказе от join-ов).
Если те же 128, то строго говоря разницы не будет. Что ГУИД, что 128-битовое число — набор байт одинаковой длины. Разница может быть только в порядке сравнения этих байт.
Насчет скорости поиска — я планирую либо сделать вторую часть статьи, либо проапдейтить эту сравнениями производительности выборки. Действительно, это очень нужный в данном случае тест.
Насчет скорости поиска — я планирую либо сделать вторую часть статьи, либо проапдейтить эту сравнениями производительности выборки. Действительно, это очень нужный в данном случае тест.
У меня подобная реализация несколько лет вполне успешно работает: #
автоинкримент всегда можно увеличивать с определенным шагом, зависящем от количества реплик + какой-нибудь запас.
для 2 процессов достаточно использовать позитивные и негативные диапазоны
Примерно так и работае HiLo Identity Generator в NHibernate.
Hi (номер cледующего свободного блока) хранится в отдельной таблице в БД, при старте приложение атомарно считывает и инкрементирует текущее значение.
Lo часть генерируется на клиентском приложении.
Id вычисляется как Hi * 32767 + Lo.
Из минусов могу назвать:
1. Int32 действительно быстро кончаются.
2. Неудобно вставлять данные из скриптов — приходится повторять логику по инкрементированию Hi и вычислению Id.
3. Последовательности Id получаются с пропусками.
Hi (номер cледующего свободного блока) хранится в отдельной таблице в БД, при старте приложение атомарно считывает и инкрементирует текущее значение.
Lo часть генерируется на клиентском приложении.
Id вычисляется как Hi * 32767 + Lo.
Из минусов могу назвать:
1. Int32 действительно быстро кончаются.
2. Неудобно вставлять данные из скриптов — приходится повторять логику по инкрементированию Hi и вычислению Id.
3. Последовательности Id получаются с пропусками.
Такой подход требует, чтобы клиент поддерживал сеанс. Поэтому в случае REST\Web работать не будет.
В Entity Framework 7 появилась возможность получать последовательные айдишники блоками по 10 штук (по умолчанию). Гарантируется, что все блоки не пересекаются, получение потокобезопасно и коллизий возникнуть не может.
С полученными блоками можно работать напрямую, явно указывая id при сохранении объекта.
Проблемы те же, что и в NHibernate — пропуски в id, быстрое увеличение значений ключа. По умолчанию по-прежнему ключ генерируется в БД.
Подробнее рассмотрено в видео блоге.
С полученными блоками можно работать напрямую, явно указывая id при сохранении объекта.
Проблемы те же, что и в NHibernate — пропуски в id, быстрое увеличение значений ключа. По умолчанию по-прежнему ключ генерируется в БД.
Подробнее рассмотрено в видео блоге.
Простите чайниковский вопрос, конечно, но разве в столь продвинутой БД как MS SQL нет сущностей SEQUENCE? Мне отчего-то кажется, что использование последовательностей снимает все вопросы, поднятые в статье, нет?
Хосподи, ну что за статья-обрубок??
«так как для некоторых ситуаций более предпочтительным может оказаться GUID» — ну хотя бы ма-аленько так про ситуации. Ну хоть парочку для каждого случая, чтобы сориентировать про какие ситуации вообще вам известно.
«Приведены преимущества каждого из вариантов...» — вот это вообще не имеет смысла при отсутствии анализа недостатков (что там у нас с джойнами по GUIDу на серьезной базе?).
«На практике возможны и другие, более редкие, типы ключа» — действительно, нафиг всю эту тягомотину с денормализацией и подходами к выбору оптимальных ключей! GUID и автоинкремент — наше все! А все остальное — редкая фигня.
Теперь о тестах (и в продолжение об обрубках). Судя по ним, автор в базу исключительно пишет. Кстати, давайте поиграем со вставкой, когда таблица не пустая, а содержит миллион записей. С индексом, конечно. А с несколькими индексами? А с кластерным по ключу?
А где, кстати, чтение, обновление, удаление? Джойны опять же?
Слова про «ограничение оперативной памяти» есть только на диаграммах. Что сие означает и как (зачем) это было реализовано?
Ну и раз уж речь идет о тестах, было бы любезно указать версию MSSQL и описать железо.
Какая-то абстракция ни о чем. Вот на кой такое писать?
«так как для некоторых ситуаций более предпочтительным может оказаться GUID» — ну хотя бы ма-аленько так про ситуации. Ну хоть парочку для каждого случая, чтобы сориентировать про какие ситуации вообще вам известно.
«Приведены преимущества каждого из вариантов...» — вот это вообще не имеет смысла при отсутствии анализа недостатков (что там у нас с джойнами по GUIDу на серьезной базе?).
«На практике возможны и другие, более редкие, типы ключа» — действительно, нафиг всю эту тягомотину с денормализацией и подходами к выбору оптимальных ключей! GUID и автоинкремент — наше все! А все остальное — редкая фигня.
Теперь о тестах (и в продолжение об обрубках). Судя по ним, автор в базу исключительно пишет. Кстати, давайте поиграем со вставкой, когда таблица не пустая, а содержит миллион записей. С индексом, конечно. А с несколькими индексами? А с кластерным по ключу?
А где, кстати, чтение, обновление, удаление? Джойны опять же?
Слова про «ограничение оперативной памяти» есть только на диаграммах. Что сие означает и как (зачем) это было реализовано?
Ну и раз уж речь идет о тестах, было бы любезно указать версию MSSQL и описать железо.
Какая-то абстракция ни о чем. Вот на кой такое писать?
вот это вообще не имеет смысла при отсутствии анализа недостатков (что там у нас с джойнами по GUIDу на серьезной базе?).
Что вы считаете серьезной базой? Десять на десять миллионов записей — все нормально.
Мы же с вами инженеры, правильно? ;)
Если основные задачи на базе — это запросы для построения отчетов и конечный пользователь радуется результату через 5 минут после запроса отчета за годовой период — это нормально. В общем случае при равных условиях джойн по GUIDу (16 байт) будет медленнее джойна по целочисленному (4 байта, например) ключу.
Если у нас онлайн торговая площадка/агрегатор — то здесь речь о нормально может идти при разговоре о сотнях миллисекунд.
Для абстрактного же (и поучающего) теста я ожидаю более четкой постановки задачи и всестороннего рассмотрения вариантов решения зачачи при этих ограничениях, иначе все это похоже на плохую лабу.
Если основные задачи на базе — это запросы для построения отчетов и конечный пользователь радуется результату через 5 минут после запроса отчета за годовой период — это нормально. В общем случае при равных условиях джойн по GUIDу (16 байт) будет медленнее джойна по целочисленному (4 байта, например) ключу.
Если у нас онлайн торговая площадка/агрегатор — то здесь речь о нормально может идти при разговоре о сотнях миллисекунд.
Для абстрактного же (и поучающего) теста я ожидаю более четкой постановки задачи и всестороннего рассмотрения вариантов решения зачачи при этих ограничениях, иначе все это похоже на плохую лабу.
В общем случае при равных условиях джойн по GUIDу (16 байт) будет медленнее джойна по целочисленному (4 байта, например) ключу.
Да, медленнее. Но вопрос в том, критично ли это замедление для решаемой задачи.
Если у нас онлайн торговая площадка/агрегатор — то здесь речь о нормально может идти при разговоре о сотнях миллисекунд.
Там вообще не факт, что надо РСУБД использовать, так что обсуждение, что в качестве первичного ключа, теряет смысл.
Прежде чем писать в таком тоне, очень рекомендую самому попытаться написать хотя бы одну статью. И прочитать внимательно критикуемую статью, кстати, тоже.
А теперь по пунктам:
Ниже вообще-то расписываются критерии, по которым предпочтительнее GUID, и даже выводы насчет его производительности в плане вставки есть в конце статьи? Или я должен был написать в стиле «Если у вас каталог для интенет магазина — используйте автоинкремент», если у вас база данных пользователей — используйте ГУИД"?
Да, это указать можно, и в тексте статьи упоминается, как на скорость вычитки могут повлиять непоследовательные ГУИДы
Составные ключи (например, версия + номер записи), строковые ключи, иерархические ключи и т.д. Всех их рассмотреть в статье? В каком проценте решений они используются? Статья не претендует на полный гайдлайн по выбору ключей. Заголовок даже на это не намекает. Что за юношеский максимализм?
Про миллион записей — это когда вся таблица не умещается в памяти. И этот кейс рассмотрен.
Про чтение и джойны я плнаирую, как будет время, написать в part 2.
Если прочитать внимательно, то все можно увидеть.
Цель этого тестирования — сравнительные цифры, а не абсолютные. Сравнение дает понять как в одних и тех же условиях ведут себя разные варианты. Знание конкретного железа чем-то поможет?
А теперь по пунктам:
«так как для некоторых ситуаций более предпочтительным может оказаться GUID» — ну хотя бы ма-аленько так про ситуации. Ну хоть парочку для каждого случая, чтобы сориентировать про какие ситуации вообще вам известно.
Ниже вообще-то расписываются критерии, по которым предпочтительнее GUID, и даже выводы насчет его производительности в плане вставки есть в конце статьи? Или я должен был написать в стиле «Если у вас каталог для интенет магазина — используйте автоинкремент», если у вас база данных пользователей — используйте ГУИД"?
«Приведены преимущества каждого из вариантов...» — вот это вообще не имеет смысла при отсутствии анализа недостатков (что там у нас с джойнами по GUIDу на серьезной базе?).
Да, это указать можно, и в тексте статьи упоминается, как на скорость вычитки могут повлиять непоследовательные ГУИДы
«На практике возможны и другие, более редкие, типы ключа» — действительно, нафиг всю эту тягомотину с денормализацией и подходами к выбору оптимальных ключей! GUID и автоинкремент — наше все! А все остальное — редкая фигня.
Составные ключи (например, версия + номер записи), строковые ключи, иерархические ключи и т.д. Всех их рассмотреть в статье? В каком проценте решений они используются? Статья не претендует на полный гайдлайн по выбору ключей. Заголовок даже на это не намекает. Что за юношеский максимализм?
Теперь о тестах (и в продолжение об обрубках). Судя по ним, автор в базу исключительно пишет. Кстати, давайте поиграем со вставкой, когда таблица не пустая, а содержит миллион записей. С индексом, конечно. А с несколькими индексами? А с кластерным по ключу?
Про миллион записей — это когда вся таблица не умещается в памяти. И этот кейс рассмотрен.
А где, кстати, чтение, обновление, удаление? Джойны опять же?
Про чтение и джойны я плнаирую, как будет время, написать в part 2.
Слова про «ограничение оперативной памяти» есть только на диаграммах. Что сие означает и как (зачем) это было реализовано?
Если прочитать внимательно, то все можно увидеть.
Ну и раз уж речь идет о тестах, было бы любезно указать версию MSSQL и описать железо.
Цель этого тестирования — сравнительные цифры, а не абсолютные. Сравнение дает понять как в одних и тех же условиях ведут себя разные варианты. Знание конкретного железа чем-то поможет?
Недавно видел пост, о том как чуваки умудрились сделать коллизию, используя sequential id. Так что для распределенных случаев его категорически нельзя использовать. В итоге остается один достойный вариант — autoincrement.
Кроме того первичный ключ (вернее кластерный ключ) хранится во всех листах не кластерных индексов, поэтому выбор guid в качестве кластерного ключа увеличивает объем индексов и замедляет их обработку. Это я уже не говорю о том, что внешние ключи в вид guid увеличивают размеры таблиц.
Поэтому всегда имеет смысл делать кластерный ключ auto increment. Но если вы хорошо знаете SQL Server, то догадаетесь, что кластерный ключ и первичный ключ таблицы — разные вещи. Можно иметь кластерный ключ — auto increment для оптимизации хранения и первичный ключ guid, если у вас планируется распределенная БД.
Кроме того первичный ключ (вернее кластерный ключ) хранится во всех листах не кластерных индексов, поэтому выбор guid в качестве кластерного ключа увеличивает объем индексов и замедляет их обработку. Это я уже не говорю о том, что внешние ключи в вид guid увеличивают размеры таблиц.
Поэтому всегда имеет смысл делать кластерный ключ auto increment. Но если вы хорошо знаете SQL Server, то догадаетесь, что кластерный ключ и первичный ключ таблицы — разные вещи. Можно иметь кластерный ключ — auto increment для оптимизации хранения и первичный ключ guid, если у вас планируется распределенная БД.
И все-таки насчет одного достойного варианта — спорное утверждение. Настройка и поддержка репликации при использовании автоинкрементов — очень неприятное занятие.
Насчет кластерного и первичного — конечно же знаю разницу. И на практике были разные случаи. Например, когда таблица кластеризована по дате изменения объекта, а первичный ключ, естественно, не кластерный.
Насчет кластерного и первичного — конечно же знаю разницу. И на практике были разные случаи. Например, когда таблица кластеризована по дате изменения объекта, а первичный ключ, естественно, не кластерный.
Встроенный механизм репликации в SQL Server как раз добавляет колонку с GUID (если не было объявлено колонки ROWGUID) и Timestamp. Вообще для репликации гуиду вовсе не надо быть ключом.
Например, когда таблица кластеризована по дате изменения объектаПри каждом обновлении меняется кластерный ключ? Это же гениально!
В мульти-мастер кластере MySQL Galera интересно решается проблема с коллизиями автоинкрементов при записи с разных нод: Auto increments in Galera
Каждая нода генерирует свои уникальные непересекающиеся с другими нодами ID используя оффсет и размер инкремента.
Каждая нода генерирует свои уникальные непересекающиеся с другими нодами ID используя оффсет и размер инкремента.
Тесты в статье некорректны.
При длине строкового поля размером более 4000 байт, то при вставке строки в страницы IN_ROW_DATA не попадает ничего и данные пишутся в страницы ROW_OVERFLOW_DATA. Это значит, что в тесте GUID произошло по факту гораздо меньше page splits, чем случилось бы в реальной системе.
При длине строкового поля размером более 4000 байт, то при вставке строки в страницы IN_ROW_DATA не попадает ничего и данные пишутся в страницы ROW_OVERFLOW_DATA. Это значит, что в тесте GUID произошло по факту гораздо меньше page splits, чем случилось бы в реальной системе.
Скорее всего вы говорите о количестве символов. Т.к. в NVarchar длина символа — 2 байта, то при 8000 байт там как раз 4000 символов. Или я как-то не так трактую technet.microsoft.com/en-us/library/ms186981%28v=sql.105%29.aspx
Мое 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
________________| 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
Запускал по 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
— NEWID
— NEWSEQUENTIALID
тестируем вставкой 50.000 записей
смотрим что получилось:
— Get the number of read / writes for this session…
— Get the page fragmentation and density at the leaf level.
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
А с джойнами как?
с joina-ми тока удвоенная нагрузка на CPU — оно и понятно — сравнение за 2 операции делается, вместо одной int-а.
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
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
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
У меня была ситуация, когда клиент вставлял пачками по 10-15М очень маленьких записей. Чтобы ускорить вставку, я пришёл к составному ключу: два поля Int32. Первое — автоинкремент с сервера, сеансовый ключ, который каждый клиент получает при каждом коннекте к серверу. Второе — внутренний инкремент на клиенте. Так у нас будет и последовательность и непротиворечивость, при чём обе гарантированные. Последовательность обеспечивается ещё и тем, что клиент перед заливкой данных, даже если и висел на связи несколько часов, всё равно делает реконнект, чтобы получить свежий сеансовый ключ и его заливка шла в конец базы. Клиентов много, но одновременная заливка двух крайне маловероятна, в основном они читают. Но даже если лить будут два, будем просто иметь перерасход кэша (нужно будет в памяти держать одновременно две страницы, по одной на каждого клиента).
Sign up to leave a comment.
Первичный ключ – GUID или автоинкремент?