Схема первичного ключа MySQL

Девиз сегодняшнего дня: Пусть все вернется к истокам, к первоначалу почему.

Эта статья объясняет Mysqlпервичный ключпроблема, отПочемуС точки зрения понимания знаний, связанных с первичным ключом Mysql, и расширения до проблемы схемы генерации первичного ключа. Не бойтесь знать только CRUD, когда вас спросят о Mysql.

Во-первых, почему необходимость в первичном ключ

Записи данных должны иметьуникальность(Первая нормальная форма)
Данные должны быть связаныjoin
Базовый индекс базы данных требуется для извлечения данных

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

"ИнформацияЭто то, что используется для устранения случайной неопределенности» (Шеннон) Люди могут понимать и преобразовывать мир, получая и распознавая различную информацию в природе и обществе, чтобы различать разные вещи.данныеЭто запись, отражающая атрибуты объективных вещей, и это особая форма информации. После обработки данные становятся информацией, а информация должна быть преобразована в цифровую форму, прежде чем ее можно будет сохранить и передать.база данныхОн используется для хранения записей данных. Раз так,записыватьЭто информация с достоверностью (относительной), и ее достоверность уникальна. Мы подошли к первой причине:

1. уникальная запись данных для

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

2. Данные должны быть связаны

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

3. Для извлечения данных требуется базовый индекс базы данных.

Во-вторых, почему первичный ключ не должен быть слишком длинным

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

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

3. Почему рекомендуется использовать автоматически увеличивающиеся идентификаторы?

Использование InnoDBкластеризованный индекс, как показано на рисунке выше, сама запись данных хранится на конечном узле основного индекса (B+Tree). Для этого требуется, чтобы каждая запись данных находилась в одном и том же листовом узле (размером является одна страница памяти или страница диска).Хранить в порядке первичного ключа, поэтому всякий раз, когда вставляется новая запись, MySQL вставляет ее в соответствующий узел и позиционирует в соответствии с ее первичным ключом и открывает новую страницу (узел), если страница достигает коэффициента загрузки (InnoDB по умолчанию равен 15/16).

Если в таблице используется автоматически увеличивающийся первичный ключ, то каждый раз, когда вставляется новая запись, запись будетДобавить по порядкуК последующей позиции текущего индекса, когда страница заполнена, новая страница будет открыта автоматически. Это сформируеткомпактныйСтруктура индекса примерно последовательно заполняется. Поскольку существующие данные не нужно перемещать каждый раз при их вставке, это очень эффективно и не требует больших накладных расходов на обслуживание индекса, как показано в левой части следующего рисунка. В противном случае, поскольку значение первичного ключа, вставляемого каждый раз, является приблизительно случайным, каждый раз, когда новая запись вставляется в определенную позицию в середине существующей индексной страницы, MySQL должен вставлять новую запись в соответствующую позицию.мобильные данные, как показано в правой части рисунка ниже, что влечет за собой определенные накладные расходы. Из-за этого Mysql может потребоваться частое обновление буфера для поддержания индекса, что увеличивает количество дисковых операций ввода-вывода методов и часто требует реорганизации структуры индекса.

4. Бизнес-ключ против логического ключа

Бизнес-ключ, то есть используйте идентификатор, имеющий значение для бизнеса, в качестве ключа, например, используйте серийный номер заказа в качестве первичного ключа таблицы заказов.Логический ключ, то есть ключ, не относящийся к бизнесу.Ключ генерируется по определенному правилу, например, самоинкрементный ключ.

Преимущества бизнес-ключей

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

Недостатки бизнес-ключей

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

Преимущества логических ключей

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

Недостатки логического ключа

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

5. Генерация первичного ключа

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

Реализация базы данных

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

Mysql поддерживает в памятисамоувеличивающийся счетчик, каждый раз, когда осуществляется доступ к счетчику автоинкремента, InnoDB добавляетАВТО-ВКЛ замокДо конца инструкции (обратите внимание, что блокировка удерживается только до конца инструкции, а не до конца транзакции). Блокировка AUTO-INC — это специальная блокировка на уровне таблицы, используемая для улучшения одновременных вставок с помощью столбцов auto_increment.

Фактически, в случае распределенной службы для генерации идентификатора можно использовать службу и базу данных и по-прежнему полагаться на способность автоматического увеличения идентификатора таблицы Mysql для единообразной генерации идентификаторов для сторонних служб. По соображениям производительности для разных служб можно использовать разные таблицы.

Mongodb ObjectId

Чтобы предотвратить конфликт первичного ключа, Mongodb разработал ObjectId в качестве идентификатора первичного ключа. Он состоит из 12-байтового шестнадцатеричного числа со следующими частями:

Время: Отметка времени. 4 байта. секунды.
Машина: Идентификатор машины. 3 байта. Как правило, это хеш-значение имени хоста машины, которое гарантирует, что разные хосты генерируют разные хэш-значения машины, и гарантирует отсутствие конфликта в распределении, а значение одной и той же машины является такой же.
PID: идентификатор процесса. 2 байта. Вышеупомянутая машина гарантирует, что идентификаторы объектов, сгенерированные на разных компьютерах, не конфликтуют, а pid гарантирует, что идентификаторы объектов, сгенерированные разными процессами mongodb на одном компьютере, не конфликтуют.
INC: Самоувеличивающийся счетчик. 3 байта. Первые девять байтов гарантируют, что идентификаторы объектов, сгенерированные разными машинами и разными процессами, не конфликтуют в течение одной секунды, а самоувеличивающийся счетчик используется для гарантии того, что идентификаторы объектов, сгенерированные в одну и ту же секунду, не обнаружат конфликтов, что позволяет 256 в степени из 3 равно 16777216 записей Уникальность записей.

Cassandra TimeUUID

Cassandra использует следующие правила для создания уникального идентификатора:time + MAC + sequence

план

Автоинкремент Zookeeper: реализован с помощью механизма автоинкремента zk.
Автоинкремент Redis: реализован с помощью механизма автоинкремента Redis.
UUID: используйте строку UUID в качестве ключа.
алгоритм снежинки: аналогичен реализации Mongodb,1位符号位 + 41位时间戳（毫秒级）+ 10位数据机器位 + 12位毫秒内的序列.

Реализация с открытым исходным кодом

Baidu UidGenerator: на основеsnowflakeалгоритм.
Meituan Leaf: Одновременно были реализованы механизмы, основанные на алгоритмах автоинкремента (оптимизации) Mysql и алгоритмах снежинки.