MySQL / 11. Формирование и использование последовательностей

Стоит ли повторно упорядочивать столбец

Задача
В столбце последовательности есть пропуски, и вы думаете о том, стоит ли повторно упорядочить его.

Решение
Не беспокойтесь об этом. Или по крайней мере не делайте этого без достаточных на то оснований, которых очень немного.

Обсуждение
Если вы вставляете записи в таблицу, содержащую столбец AUTO_INCREMENT, и никогда их не удаляете, то значения столбца образуют непрерывную последовательность. Но если вы удаляете записи, в последовательности начинают появляться пустоты. Например, таблица Джуниора insect сейчас выглядит примерно так, с «дырами» в последовательности (предполагается, что были вставлены записи про сверчка (cricket) и мотылька (moth), упомянутые в предыдущем разделе про извлечение значений последовательности):

mysql> SELECT * FROM insect ORDER BY id;

+----+--------------------+---------------+------------+
| id | name                     | date             | origin        |
+----+--------------------+---------------+------------+
| 1   | housefly               | 2001-09-10 | kitchen      |
| 3   | grasshopper         | 2001-09-10 | front yard |
| 4   | stink bug               | 2001-09-10 | front yard |
| 5   | cabbage butterfly | 2001-09-10 | garden      |
| 6   | ant                        | 2001-09-10 | back yard  |
| 9   | cricket                   | 2001-09-11 | basement  |
| 10 | moth                     | 2001-09-14 | windowsill  |
+----+--------------------+---------------+-------------+

MySQL не будет пытаться заполнить пропуски неиспользованными значениями при вставке новых записей. Те, кому не нравится такое поведение, обычно периодически переупорядочивают столбцы AUTO_INCREMENT для удаленияпустот. В следующих разделах показано, как это сделать. Кроме того, рассказано о том, как добавить столбец последовательности в таблицу, в которой его еще нет, инициировать повторное использование удаленных значений вершины последовательности и указать начальное значение последовательности при создании или повторном упорядочивании таблицы.

Причины избегать повторного упорядочивания
Прежде чем принять решение о повторном упорядочивании столбца AUTO_INCREMENT, подумайте о том, действительно ли вы этого хотите и есть ли такая необходимость. В большинстве случаев это совсем необязательно. На самом деле, повторная нумерация последовательности может создать серьезные проблемы. Например, нельзя переупорядочивать столбец значений последовательности, на который ссылается другая таблица. Изменение нумерации разрушает связь значений со значениями в другой таблице, делая невозможным корректное сопоставление записей двух таблиц.

Причины, по которым стремятся к повторному упорядочиванию:
Эстетика. Иногда хочется перенумеровать столбец из эстетических соображения. Неразрывные последовательности выглядят более красиво, чем последовательности с дырами. Если ваша причина именно такова, я вряд ли смогу вас переубедить. Тем не менее, это не самая убедительная причина.

Производительность. Стремление к повторному упорядочиванию может объясняться мнением о том, что удаление пробелов делает столбец последовательности более компактным и позволяет запросам MySQL выполняться быстрее. Но это неверно. Наличие или отсутствие пробелов не беспокоит MySQL, и перенумерация столбца AUTO_INCREMENT не увеличивает производительность. Можно даже сказать, что повторное упорядочивание отрицательно влияет на производительность в том смысле, что таблица остается заблокированной на время выполнения операции, которая может быть достаточно долгой для больших таблиц. Другие клиенты могут в это время читать данные из таблицы, и если они захотят вставить новые строки, то придется подождать завершения операции.

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

Статьи по MySQL на эту тему:

Генераторы однострочных последовательностей
Добавление последовательности в существующую таблицу
Использование значений AUTO_INCREMENT для связывания таблиц
Как начать последовательность с определенного значения
Перенумерация существующей последовательности