10.3.5 Column Indexes — MySQL 8.4

10.3.5 Индексы столбцов

Наиболее распространённый тип индекса — индекс по одному столбцу, хранящий копии значений из этого столбца в структуре данных, что позволяет быстро находить строки с соответствующими значениями столбца. Структура данных B-дерева позволяет индексу быстро находить определённое значение, набор значений или диапазон значений, соответствующий операторам, таким как =, >, ≤, BETWEEN, IN и так далее, в операторе WHERE.

Максимальное количество индексов на таблицу и максимальная длина индекса определяются для каждого хранилища данных. См. Главу 17, Двигатель хранилища InnoDB и Главу 18, Альтернативные двигатели хранилища. Все двигатели хранилища поддерживают как минимум 16 индексов на таблицу и общую длину индекса как минимум 256 байт. Большинство двигателей хранилища имеют более высокие лимиты.

Дополнительную информацию об индексах столбцов см. в Разделе 15.1.15, «Команда CREATE INDEX».

Префиксы индексов

С помощью синтаксиса col_name(N) в спецификации индекса для строкового столбца вы можете создать индекс, использующий только первые N символов столбца. Индексация только префикса значений столбца таким образом может значительно уменьшить размер файла индекса. При индексации столбца типа BLOB или TEXT, необходимо указать длину префикса для индекса. Например:

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

Длина префиксов может достигать 767 байт для таблиц InnoDB, использующих формат строк или . Предел длины префикса составляет 3072 байта для таблиц InnoDB, использующих формат строк или . Для таблиц MyISAM предел длины префикса составляет 1000 байт.

Примечание

Пределы префиксов измеряются в байтах, а длина префикса в операторах CREATE TABLE, ALTER TABLE и CREATE INDEX интерпретируется как количество символов для строковых типов без бинарного представления (CHAR, VARCHAR, TEXT) и как количество байт для бинарных строковых типов (BINARY, VARBINARY, BLOB). Учитывайте это при указании длины префикса для строкового столбца без бинарного представления, использующего многобайтовый набор символов.

Если термин поиска превышает длину префикса индекса, индекс используется для исключения несовпадающих строк, а оставшиеся строки проверяются на возможные совпадения.

Дополнительную информацию о префиксах индексов см. в Разделе 15.1.15, «Команда CREATE INDEX».

Индексы FULLTEXT

Индексы FULLTEXT используются для полнотекстового поиска. Только двигатели хранилища InnoDB и MyISAM поддерживают индексы FULLTEXT и только для столбцов типа CHAR, VARCHAR и TEXT. Индексация всегда происходит по всему столбцу, индексация префикса столбца не поддерживается. Подробности см. в Разделе 14.9, «Функции полнотекстового поиска».

Оптимизации применяются к определённым видам запросов FULLTEXT к отдельным таблицам InnoDB. Запросы с этими характеристиками особенно эффективны:

Запросы FULLTEXT, возвращающие только идентификатор документа или идентификатор документа и рейтинг поиска.
Запросы FULLTEXT, сортирующие совпадающие строки по убыванию оценки и применяющие оператор LIMIT для получения N лучших совпадающих строк. Для применения этой оптимизации не должно быть операторов WHERE и только один оператор ORDER BY по убыванию.
Запросы FULLTEXT, извлекающие только значение COUNT(*) строк, соответствующих термину поиска, без дополнительных операторов WHERE. Оператор WHERE записывается как WHERE MATCH(text) AGAINST ('other_text') без оператора сравнения > 0.

Для запросов, содержащих полнотекстовые выражения, MySQL оценивает эти выражения во время фазы оптимизации выполнения запроса. Оптимизатор не просто рассматривает полнотекстовые выражения и делает оценки, а фактически оценивает их в процессе разработки плана выполнения.

Следствием этого поведения является то, что EXPLAIN для полнотекстовых запросов, как правило, медленнее, чем для запросов без полнотекстового поиска, для которых не происходит оценка выражений во время фазы оптимизации.

EXPLAIN для полнотекстовых запросов может показывать Select tables optimized away в столбце Extra из-за того, что сопоставление происходит во время оптимизации; в этом случае доступ к таблицам во время последующего выполнения может не потребоваться.

Пространственные индексы

Вы можете создавать индексы на пространственных типах данных. MyISAM и InnoDB поддерживают индексы R-дерева для пространственных типов. Другие двигатели хранилища используют B-деревья для индексации пространственных типов (за исключением ARCHIVE, который не поддерживает индексацию пространственных типов).

Индексы в движке хранилища MEMORY

Двигатель хранилища MEMORY по умолчанию использует индексы HASH, но также поддерживает индексы BTREE.