angle-up arrow-clockwise arrow-counterclockwise arrow-down-up arrow-left at calendar card-list chat check envelope folder house info-circle pencil people person person-plus phone plus question-circle search tag trash x

Использование UUID вместо Integer Autoincrement Primary Keys с SQLAlchemy и MariaDb

9 апреля 2020 возле Peter

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

post main image
https://unsplash.com/@skylergeraldphoto

Мы все знаем Integer Autoincrement Primary Key. Работает отлично, многие программы делают это таким образом. Это быстро. Но у него также есть ограничения.

Альтернативой Integer Autoincrement Primary Key является UUID (Universally Unique IDentifier) Primary Key. UUID - 128-битное число. Оно может быть представлено, например, строкой:

05390f2b-48e8-45c7-8ae4-08272dc69122

или шестнадцатеричную строку (то же самое, что и выше с удаленным символом тире):

05390f2b48e845c78ae408272dc69122

или двоичное значение.

UUID уникальны, нет никакой нумерации. Если вы генерируете UUID на своем ноутбуке в Лондоне, а ваш коллега в Нью-Йорке генерирует UUID на своем ноутбуке, то оба UUID уникальны, они никогда не могут быть одинаковыми. Существует несколько версий UUIDs, здесь мы будем использовать версию 4, также известную как 'случайный' UUID. Вероятность столкновения практически отсутствует.

Является ли UUID Primary Keys хорошим выбором?

Я считаю, что причин для этого достаточно даже для небольших проектов. Все знают о проблеме при объединении записей базы данных или таблиц. Если таблицы ваших баз данных используют Integer Autoincrement Primary Keys , то это может быть грязно, но с UUID это ветер.

Пример#1: Блог с несколькими авторами в автономном режиме

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

Если ваша база данных использует Integer Autoincrement Primary Keys , то вы должны быть очень осторожны. Вероятно, вы делаете это, экспортируя некоторые записи, а затем импортируете их в вашу производственную базу данных. Вы не можете импортировать идентификаторы записей, оба автора могут иметь разные сообщения с одинаковыми идентификаторами. Это становится более сложным, когда записи в блоге ссылаются на (новые) записи таблицы, содержащие ссылки/ссылки для записи в блоге. Возможно, вы напишете скрипт, который разберется с этим.

Если бы мы использовали UUID Primary Keys , с другой стороны, мы просто экспортируем новые записи с обоих ноутбуков и импортируем их в производственную базу данных. Никаких конфликтов, все просто.

Пример №2: Веб-магазин с информацией о продукте, подготовленной в автономном режиме.

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

Плюсы и минусы использования UUID в качестве первичного ключа

Преимущества:

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

Недостатки:

  • Использует больше памяти и дискового пространства, индексные таблицы становятся больше
    Но кого это волнует сегодня?
  • Медленнее для SELECT с большими таблицами
    Но возможны оптимизации
  • Может быть намного медленнее для INSERT
    Потребуется больше времени для пересчета индекса, может быть серьезной проблемой
  • Более сложным для отладки из-за отсутствия порядка
    вставки Но вы можете добавить колонку DATETIME (микросекунды) 'create_on' и использовать это для сортировки.

UUID препятствуют раскрытию деловой информации (секретов).

Дополнительным важным преимуществом использования первичного ключа UUID вместо Integer Autoincrement Primary Key является то, что вы можете меньше беспокоиться о раскрытии бизнес-информации. Пример: контакты хранятся в таблице контактов. Если эта таблица имеет Integer Autoincrement Primary Key , представление редактирования может выставить в URL идентификатор user:

    /contact/edit/24

Это означает, что вы можете угадать номер user сайта. Если вы используете первичный ключ UUID , то user_id в URL ничего не выставляет:

    /contact/edit/5489b4d7abd9416f9ba31d48fbb0f5be

Конечно же, лучше никогда не выставлять ни одного типа Primary Key. Это может выглядеть тривиально, но может вызвать много дополнительной работы.

Пример: считайте, что запись о контакте содержит Иностранный ключ для города. Контакт Foreign Key ссылается на город Primary Key. Если мы не хотим выставлять Primary Keys , мы можем добавить столбец Surrogate Key в каждую таблицу и сгенерировать Surrogate Key для каждой создаваемой записи. При редактировании контакта мы должны показать выпадающий список с доступными городами. Мы не хотим, чтобы выставлялся Primary Keys городов, и вместо этого выбираем Surrogate Keys . Затем, когда форма Контакта будет отправлена, мы должны сначала посмотреть Primary Key города, используя отправленное значение Surrogate Key . После того, как мы получили Город Primary Key , мы можем обновить значение Контакта.

Python и UUIDs

Генерировать UUIDs в Python легко:

>>> import uuid
>>> uuid_obj = uuid.uuid4()
>>> uuid_obj
UUID('05390f2b-48e8-45c7-8ae4-08272dc69122')
>>> str(uuid_obj)
'05390f2b-48e8-45c7-8ae4-08272dc69122'
>>> uuid_obj.hex
'05390f2b48e845c78ae408272dc69122'

Мы также можем преобразовать hex UUID обратно в строку UUID:

>>> uuid_obj2 = uuid.UUID(hex='05390f2b48e845c78ae408272dc69122', version=4)
>>> str(uuid_obj2)
'05390f2b-48e8-45c7-8ae4-08272dc69122'

Порядок вставок

При использовании первичных ключей UUID мы теряем порядок вставок. В моей модели каждая таблица имеет колонку MariaDb DATETIME 'create_on', что означает, что минимальное разрешение 1 секунда. Когда порядок вставок важен, мы можем увеличить разрешение колонки 'create_on' до микросекунд, используя DATETIME(6). Если вам действительно нужен порядок вставки, вы можете добавить дополнительный столбец Autoincrement.

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

Использование UUID для первичных ключей на самом деле не ускоряет запросы. Запросы SELECT работают медленнее из-за сравнения строк, а не из-за сравнения целых чисел. Запросы INSERT могут быть действительно медленными, потому что для каждой вставки индекс должен перестраиваться с случайными числами. Возможны оптимизации, но у них есть и недостатки, например, UUID больше не является 'случайным'.

SQLAlchemy, MariaDb, UUIDs и Alembic

Я использую SQLAlchemy и MariaDb, поэтому используем 32-символьное шестнадцатеричное значение UUID. PostgreSQL уже поддерживает UUID. Рецепт есть на сайте SQLAlchemy : 'Тип GUID с обратной диагностикой', смотрите ссылки ниже. Я включил этот рецепт (скрипт), с небольшой модификацией, в мой файл models.py. Затем я пишу свою модель UUIDCity как:

class  UUIDCity(Base):

    __tablename__ = 'uuid_city'

    id = Column(GUID(), primary_key=True, default=uuid.uuid4)
    ...

Это заботится о генерации UUID для новой записи. Я немного изменил скрипт, чтобы убедиться, что он возвращает строковое значение, а не объект GUID. Это значительно упрощает его использование в запросах. (Но я могу потерять специфические преимущества SQLAlchemy ?).

    ...
    def process_result_value(self, value, dialect):
        if value is  None:
            return value
        else:
            # 20200328/PPM commented the next two lines to return the db id as string 
            # if not isinstance(value, uuid.UUID):
            #     value = uuid.UUID(value)
            return value

Здесь есть проблема Alembic . При запуске Alembic для модели UUIDCity:

alembic revision --autogenerate -m "some version text"

Alembic генерирует файл версий с:

def upgrade():
    # ### commands auto generated by  Alembic  - please adjust! ###
    op.create_table('uuid_demo_city',
    sa.Column('id', shared.models.GUID(), nullable=False),
    sa.Column('name', sa.String(length=250), server_default='', nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_uuid_demo_city_name'), 'uuid_demo_city', ['name'], unique=False)

У меня есть папка с названием "share", и в этой папке находится файл models.py. Это сделает команду обновления неудачной. Результат команды:

alembic upgrade head

есть:

  File "...", line 17, in upgrade
    sa.Column('id', shared.models.GUID(), nullable=False),
NameError: name 'shared' is not defined

Это немного сбивает с толку, но это связано с тем, что отсутствует тип данных shared.models.GUID(). Можно модифицировать env.py и script.py.mako. Некоторая информация приведена по ссылкам ниже. Я этого еще не делал. На данный момент я отредактировал файл версий и изменил его:

    sa.Column('id', shared.models.GUID(), nullable=False),

в:

    sa.Column('id', sa.CHAR(32), nullable=False),

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

Резюме

Как я пришел, чтобы написать это сообщение? Думаю, я искал способы генерировать уникальные случайные коды. В Python использовать UUID просто. Возможно, я набрал случайный уникальный код + MySQL. Затем вы также нажимаете UUID Primary Keys. Я читал о них в прошлом, но на этот раз я нашел время, чтобы исследовать эту тему.

Если вы используете базу данных с Integer Autoincrement Primary Keys , стоит ли конвертировать ее в UUID Primary Keys? Думаю, что нет. Это может занять много времени и вы можете попасть в большие неприятности.

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

Конечно, есть исключения. Предположим, что вы примеряете изображения с веб-камеры и храните их ссылки в записи базы данных. В этом случае вам нужна максимальная производительность INSERT , поэтому не используйте UUID Primary Keys здесь!

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

Для этого сайта CMS/блога я решил (очень) медленно конвертировать существующий Integer Autoincrement Primary Keys в UUID Primary Keys.

Ссылки / кредиты

Alembic: How to migrate custom type in a model?
https://stackoverflow.com/questions/15668115/alembic-how-to-migrate-custom-type-in-a-model

Backend-agnostic GUID Type
https://docs.sqlalchemy.org/en/13/core/custom_types.html#backend-agnostic-guid-type

Best UUID data type?
https://www.memsql.com/forum/t/best-uuid-data-type/182

Configuring MySQL to allow generation of Primary Key values
https://stackoverflow.com/questions/21669815/configuring-mysql-to-allow-generation-of-primary-key-values

GUID/UUID Performance
https://mariadb.com/kb/en/guiduuid-performance/

How can I bind a list to a parameter in a custom query in sqlalchemy?
https://stackoverflow.com/questions/13190392/how-can-i-bind-a-list-to-a-parameter-in-a-custom-query-in-sqlalchemy

How can I use UUIDs in SQLAlchemy?
https://stackoverflow.com/questions/183042/how-can-i-use-uuids-in-sqlalchemy

Microseconds in MariaDB
https://mariadb.com/kb/en/microseconds-in-mariadb/

UUIDs are Popular, but Bad for Performance — Let’s Discuss
https://www.percona.com/blog/2019/11/22/uuids-are-popular-but-bad-for-performance-lets-discuss/

Оставить комментарий

Комментируйте анонимно или войдите в систему, чтобы прокомментировать.

Комментарии

Оставьте ответ

Ответьте анонимно или войдите в систему, чтобы ответить.