Вы можете не знать об автоинкрементном идентификаторе

MySQL

Введение: при использовании MySQL для создания таблицы мы обычно создаем поле автоинкремента (AUTO_INCREMENT) и используем это поле в качестве первичного ключа. В этой статье будет рассказано все об автоинкрементировании id в форме вопроса и ответа.

Примечание:Эта статья основана наInnodbнакопительный двигатель.

1. Почему MySQL рекомендует установить идентификатор столбца с автоинкрементом в качестве первичного ключа?

  • Если мы определим первичный ключ (PRIMARY KEY), тоInnoDBПервичный ключ будет выбран в качестве кластеризованного индекса.Если первичный ключ явно не определен, InnoDB выберет первый уникальный индекс, который не содержит значений NULL, в качестве индекса первичного ключа.Если такого уникального индекса нет, InnoDB выберет встроенный 6-байтовый длинный. ROWID используется как неявный кластеризованный индекс (ROWID увеличивается по мере записи записи строки, и на этот ROWID не так ссылаются, как на ROWID ORACLE, который является неявным).
  • Сами записи данных хранятся на листовых узлах основного индекса (B+Tree). Это требует, чтобы каждая запись данных в одном и том же листовом узле (размером является одна страница памяти или страница диска) хранилась в порядке первичного ключа, поэтому всякий раз, когда вставляется новая запись, MySQL будет вставлять ее в соответствующий узел в соответствии с его первичный ключ и положение, если страница достигает коэффициента загрузки (InnoDB по умолчанию 15/16), открыть новую страницу (узел)
  • Если в таблице используется автоинкрементный первичный ключ, каждый раз, когда вставляется новая запись, запись будет последовательно добавляться на следующую позицию текущего узла индекса.Когда страница заполнена, новая страница будет открываться автоматически.
  • Если используется несамоинкрементный первичный ключ (если это идентификационный номер или номер студента и т. д.), поскольку значение вставляемого каждый раз первичного ключа примерно случайно, каждая новая запись должна быть вставлена ​​в определенную позицию в середина существующей страницы индекса MySQL должен перемещать данные, чтобы вставить новую запись в нужное место, и даже целевая страница может быть записана обратно на диск и сброшена из кеша, а затем считана с диска, что добавляет много накладных расходов, а частые операции перемещения и подкачки вызывают большую фрагментацию, что приводит к недостаточно компактной структуре индекса.Позже для перестроения таблицы и оптимизации заполнения страницы необходимо использовать OPTIMIZE TABLE.

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

2. Должен ли самоувеличивающийся идентификатор столбца быть непрерывным?

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

Давайте сначала посмотрим на самоценную стратегию сохранения MySQL:

Самоинкремент движка InnoDB фактически хранится в памяти, а после версии MySQL 8.0 реализована возможность «самооценки персистентности», то есть «если происходит перезагрузка, самозначение таблицы может быть восстановлено до значения до перезапуска MySQL", конкретная ситуация:

В MySQL 5.7 и более ранних версиях самоинкремент хранится в памяти и не является постоянным. После каждого перезапуска, когда таблица открывается в первый раз, она находит максимальное значение самоинкремента max(id), а затем использует max(id)+1 в качестве текущего самоинкремента таблицы.

Например, если наибольший идентификатор в текущей строке таблицы равен 10, AUTO_INCREMENT=11. В это время мы удаляем строку с id=10, а AUTO_INCREMENT по-прежнему равно 11. Но если экземпляр будет перезапущен немедленно, AUTO_INCREMENT этой таблицы станет равным 10 после перезапуска.

То есть перезапуск MySQL может изменить значение AUTO_INCREMENT таблицы.

В версии MySQL 8.0 самостоятельные изменения записываются в журнал повторов, а журнал повторов используется для восстановления значения до перезапуска при перезапуске.

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

  • 1. Конфликт уникальных ключей
  • 2. Откат транзакции
  • 3. вставить ... выбрать идентификатор автоматического увеличения пакетного приложения оператора

3. Существует ли верхний предел для идентификатора автоинкремента?

Самоувеличивающийся идентификатор представляет собой целочисленное поле.Мы часто используем тип int для определения идентификатора роста, а тип int имеет верхний предел, то есть идентификатор роста также имеет верхний предел.

В таблице ниже перечисленыintиbigintДиапазон типов полей:

тип размер диапазон (со знаком) диапазон (без знака)
int 4 байта (-2147483648,2147483647) (0,4294967295)
bigint 8 байт (-9223372036854775808,9223372036854775807) (0,18446744073709551615)

Как видно из приведенной выше таблицы: при использовании в поле автоинкремента знакового типа int максимум может достигать 2147483647 или более 2,1 миллиарда, при использовании типа int unsigned максимум может достигать 4294967295 или более 4,2 миллиарда . Конечно, bigint может представлять больший диапазон.

Давайте проверим, что происходит, когда данные вставляются снова, когда идентификатор автоинкремента достигает максимума:

create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
insert into t values(null);
// 成功插入一行 4294967295
show create table t;
/* CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295;
*/

insert into t values(null);
//Duplicate entry '4294967295' for key 'PRIMARY'

Из эксперимента видно, что когда идентификатор автоинкремента достигает максимума, он не сможет расширяться.После того, как первый оператор вставки успешно вставит данные, AUTO_INCREMENT этой таблицы не изменится (по-прежнему 4294967295), что приводит ко второму оператору вставки, получающему то же самое значение самоувеличивающегося идентификатора, а затем пытающемуся выполнить оператор вставки, сообщается об ошибке конфликта первичного ключа.

4. Как мы должны поддерживать столбец автоинкремента?

Что касается технического обслуживания, мы в основном предлагаем следующие два предложения:

  • 1. Выбор типа поля: рекомендуется использовать тип int unsigned, если можно предсказать, что объем данных в таблице будет очень большим, вместо него можно использовать тип bigint unsigned.
  • 2. Уделите больше внимания самоинкременту больших таблиц, чтобы предотвратить возникновение переполнения первичного ключа.
Категории