8.3.4 Column Indexes — MySQL 5.7

8.3.4 Индексы по столбцам

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

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

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

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

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

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

Префиксы могут иметь длину до 1000 байт (767 байт для таблиц InnoDB, если не задан параметр innodb_large_prefix).

Примечание

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

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

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

Индексы FULLTEXT

Индексы FULLTEXT используются для полнотекстовых поисков. Только движки хранения InnoDB и MyISAM поддерживают индексы FULLTEXT и только для столбцов типов CHAR, VARCHAR и TEXT. Индексирование всегда происходит по всему столбцу, и индексирование префикса столбца не поддерживается. Подробнее см. в разделе 12.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.