MySQL PRIMARY KEYs: UUID/GUID против BIGINT (временная метка + случайная)

tl; dr: Является ли назначение идентификаторов строк {unixtimestamp} {randomdigits} (например, 1308022796123456) как BIGINT хорошей идеей, если я не хочу иметь дело с UUID?

Просто интересно, есть ли у кого-нибудь представление о производительности или других технических соображениях/ограничениях в отношении идентификаторов /PRIMARY KEY, назначенных для записей базы данных на нескольких серверах.

Мое приложение PHP + MySQL работает на нескольких серверах, и данные должны быть объединены. Таким образом, я перерос стандартный метод последовательного/автоматического_инкремента для определения строк.

Мои исследования в решении привели меня к концепции использования UUID/GUID. Однако необходимость изменить мой код для работы с преобразованием строк UUID в двоичные значения в MySQL кажется немного болью/работой. Я не хочу хранить UUID как VARCHAR для хранения и повышения производительности.

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

В качестве промежуточного уровня я придумал идею создания идентификационных колонок BIGINT и назначение идентификаторов с использованием текущей временной отметки unix, а затем 6 случайных цифр. Так что скажем, что мое случайное число составило 123456, мой сгенерированный идентификатор сегодня выйдет как: 1308022796123456

Один из 10 миллионов шансов на конфликт для строк, созданных в течение одной секунды, отлично со мной. Я не делаю создания массового ряда быстро.

Одна проблема, которую я прочитал со случайно генерируемыми UUID, заключается в том, что они плохо относятся к индексам, поскольку значения не являются последовательными (они разбросаны повсюду). Функция UUID() в MySQL обращается к этому путем генерации первой части UUID с текущей временной метки. Поэтому я скопировал эту идею с отметкой unix в начале моего BIGINT. Будут ли мои индексы медленными?

Плюсы моей идеи BIGINT:

  • Дает мне преимущества для нескольких серверов/слияния UUID.
  • Требуется очень малое изменение кода приложения (все уже запрограммировано для обработки целых чисел для идентификаторов)
  • Половина памяти UUID (8 байт против 16 байт)

Минусы:

  • ??? - Пожалуйста, дайте мне знать, если вы можете думать о них.

Некоторые последующие вопросы, чтобы согласиться с этим:

  • Должен ли я использовать более или менее 6 случайных цифр в конце? Будет ли это иметь значение для производительности индекса?

  • Является ли один из этих методов "случайным"?: Получение PHP для генерации 6 цифр и объединения их вместе. - Получение PHP для генерации числа в диапазоне 1 - 999999, а затем zerofilling для обеспечения 6 цифр.

Спасибо за любые советы. Извините за стену текста.

Ответы

Ответ 1

Я столкнулся с этой проблемой в своей профессиональной жизни. Мы использовали timestamp + random number и столкнулись с серьезными проблемами, когда наши приложения расширялись (больше клиентов, больше серверов, больше запросов). Конечно, мы (тупо) использовали только 4 цифры, а затем изменили на 6, но вы были бы удивлены, как часто ошибки все же происходят.

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

Используя UUID, ваш индексный размер будет увеличиваться, а больший индекс приведет к снижению производительности (возможно, незаметной, но более бедной). Однако MySQL поддерживает собственный тип UUID (никогда не используйте varchar в качестве первичного ключа!) И может эффективно обрабатывать индексирование, поиск и т.д., Даже по сравнению с bigint. Наибольшая производительность, связанная с вашим индексом, - это почти всегда число индексированных строк, а не размер элемента, являющегося индексом (если вы не хотите индексировать длинный текст или что-то нелепо подобное).

Чтобы ответить на ваш вопрос: Bigint (со случайными номерами прилагается) будет одобрен, если вы не планируете значительно масштабировать свое приложение/услугу. Если ваш код может обрабатывать изменения без значительных изменений, и ваше приложение не будет взорваться при возникновении дублирующей ключевой ошибки, пойдите с ним. В противном случае, bite-the-bullet и перейдите к более существенному варианту.

Вы можете всегда выполнять более крупное изменение позже, например, переключиться на совершенно другой бэкэнд (с которым мы теперь сталкиваемся...: P)

Ответ 2

Вы можете вручную изменить начальный номер автонабора.

ALTER TABLE foo AUTO_INCREMENT = ####

Беззнаковый int может хранить до 4 294 967 295, позволяет округлить его до 4 290 000 000.

Используйте первые 3 цифры для серийного номера сервера и последние 7 цифр для идентификатора строки.

Это дает вам до 430 серверов (включая 000) и до 10 миллионов идентификаторов для каждого сервера.

Итак, для сервера # 172 вы вручную меняете номер автонабора, чтобы начать с 1,720,000,000, затем пусть он последовательно назначает идентификаторы.

Если вы считаете, что у вас может быть больше серверов, но меньше идентификаторов на сервер, затем настройте их на 4 цифры на сервер и 6 для ID (то есть до 1 миллиона идентификаторов).

Вы также можете разбить число, используя двоичные цифры вместо десятичных цифр (возможно, 10 двоичных цифр на сервер и 22 для идентификатора. Так, например, сервер 76 начинается с 2 ^ 22 * ​​76 = 318,767,104 и заканчивается на 322,961,407).

В этом случае вам даже не нужен четкий раскол. Возьмите 4,294,967,295, разделите его на максимальное количество серверов, которые, по вашему мнению, когда-либо будут, и что ваш интервал.

Вы можете использовать bigint, если считаете, что вам нужно больше идентификаторов, но это очень большое количество.

Ответ 3

Если вы хотите использовать метод отметки времени, сделайте следующее:

Дайте каждому серверу номер, к которому добавляется идентификатор профайла приложения, которое выполняет вставку (или идентификатор потока) (в PHP это getmypid()), а затем добавляет, сколько времени этот процесс был жив/активен для (в PHP это getrusage()) и, наконец, добавляет счетчик, который начинается с 0 в начале каждого вызова script (т.е. каждая вставка в пределах того же script добавляет один к нему).

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

Ответ 4

Используйте GUID в качестве уникального индекса, но также рассчитайте 64-битный (BIGINT) хэш GUID, сохраните его в отдельном столбце и проиндексируйте. Чтобы получить, извлеките несколько записей, которые соответствуют столбцу хэша, а затем используйте GUID, чтобы найти правильный.