Типы индексов MySQL
Автор: Татьяна Сергиенко, Software Engineer
В литературе встречается следующая терминология:

  • кластеризованные - специальные индексы, Primary Key и Unique Index (Key и Index – это синонимы в данном случае)

  • некластеризованные, или вторичные, индексы - все остальные индексы, которые не попадают под Primary и Unique
Кластеризованный индекс – это древовидная структура данных, при которой значения индекса хранятся вместе с данными, им соответствующими. И индексы, и данные при такой организации упорядочены. При добавлении новой строки в таблицу, она дописывается не в конец файла, не в конец плоского списка, а в нужную ветку древовидной структуры, соответствующую ей по сортировке.

В некластеризованном индексе на листьях древовидной структуры хранятся указатели (или ссылки) на соответствующие строки с данными в таблице. Каждая запись во вторичном индексе содержит столбцы первичного ключа для строки, а также столбцы, указанные в некластеризованном индексе.
Общие правила при создании индекса:
  1. Каждая таблица всегда имеет только один кластеризованный индекс.

  2. Когда вы определяете PRIMARY KEY для таблицы, MySQL использует PRIMARY KEY в качестве кластеризованного индекса.

  3. Если у вас нет PRIMARY KEY для таблицы, MySQL будет искать первый UNIQUE индекс, в котором находятся все ключевые столбцы, и будет использовать этот UNIQUE индекс в качестве кластеризованного индекса.

  4. В случае, если таблица не имеет PRIMARY KEY или подходящего UNIQUE индекса, MySQL внутренне генерирует скрытый кластерный индекс, названный GEN_CLUST_INDEX на синтетическом столбце, который содержит значения идентификатора строки.
Ключи в системе индексов MySQL
MySQL использует значение первичного ключа для поиска строк в некластеризованном индексе.

Следовательно, предпочтительно иметь короткий первичный ключ, иначе вторичные индексы будут использовать больше места. Обычно для столбца первичного ключа используется целочисленный столбец с автоинкрементом.
Primary key (первичный ключ)

Первичный ключ - это столбец или набор столбцов, которые однозначно идентифицируют каждую строку в таблице.

Первичный ключ следует следующим правилам:

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

  • Столбец первичного ключа не может иметь NULL значений. Любая попытка вставить или обновить NULL столбцы первичного ключа приведет к ошибке.

Обратите внимание, что MySQL неявно добавляет NOT NULL ограничение к столбцам первичного ключа.

  • Таблица может иметь один и только один первичный ключ.
Unique key

Чтобы обеспечить значение уникальности одного или нескольких столбцов, часто используют PRIMARY KEY. Однако каждая таблица может иметь только один первичный ключ.

UNIQUE index позволяет обеспечить уникальность значений в одном или нескольких столбцах. В отличие от PRIMARY KEY, вы можете создать более одного UNIQUE индекса для каждой таблицы.

Также, в отличие от PRIMARY key, MySQL допускает NULL значения в UNIQUE индексе.
Индекс префиксов

Если столбцы являются строковыми, при создании индекса, он будет занимать много места на диске и потенциально замедлять INSERT операции.

Чтобы решить эту проблему, MySQL позволяет создавать индекс для ведущей части значений столбцов строковых столбцов.
Невидимый индекс (только для MySQL 8.0)

По умолчанию индексы видимые (VISIBLE). Невидимые индексы (INVISIBLE) позволяют помечать индексы как недоступные для оптимизатора запросов.

MySQL поддерживает невидимые индексы в актуальном состоянии при изменении данных в столбцах, связанных с индексами.

Чтобы сделать индекс невидимым, с помощью ключевых слов VISIBLE и INVISIBLE, вы должны явно заявить о видимости индекса во время создания или с помощью ALTER TABLE команды.

Например, вы можете сделать индекс невидимым, чтобы увидеть, влияет ли он на производительность, и снова пометить индекс как видимый, если это так.

PRIMARY key оr UNIQUE index нельзя сделать невидимыми.
Составной индекс

Составной индекс - это индекс по нескольким столбцам. MySQL позволяет создавать составной индекс, состоящий до 16 столбцов.

Оптимизатор запросов использует составные индексы для запросов, которые проверяют все столбцы в индексе, или запросов, которые проверяют первые столбцы, первые два столбца и т.д.

Если вы укажете столбцы в правильном порядке в определении индекса, единый составной индекс может ускорить выполнение таких запросов к одной и той же таблице.