Что такое индексы в MySQL и каковы их разновидности (B-tree, full-text, spatial)?

Индексы в MySQL: назначение и виды

Индекс — это вспомогательная структура данных, которая ускоряет поиск строк без полного сканирования таблицы. MySQL хранит индексы отдельно от данных и обновляет их при каждой операции INSERT/UPDATE/DELETE.

B-Tree индекс

Тип по умолчанию для InnoDB и MyISAM. Данные хранятся в сбалансированном дереве, листья связаны в список — это позволяет делать range-запросы. Подходит для операторов =, <, >, BETWEEN, LIKE 'prefix%'.

-- Обычный B-Tree индекс
CREATE INDEX idx_salary ON employees(salary);

-- Составной индекс (порядок столбцов важен!)
CREATE INDEX idx_dept_salary ON employees(department_id, salary);

-- Уникальный индекс
CREATE UNIQUE INDEX idx_email ON users(email);

-- Посмотреть план выполнения
EXPLAIN SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000;

FULLTEXT индекс

Предназначен для полнотекстового поиска по текстовым столбцам (CHAR, VARCHAR, TEXT). Поддерживается в InnoDB (начиная с MySQL 5.6) и MyISAM. Использует специальные операторы MATCH ... AGAINST.

CREATE TABLE articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT idx_ft (title, body)
) ENGINE=InnoDB;

-- Natural Language Mode (по умолчанию)
SELECT id, title,
       MATCH(title, body) AGAINST ('mysql index performance') AS score
FROM articles
WHERE MATCH(title, body) AGAINST ('mysql index performance')
ORDER BY score DESC;

-- Boolean Mode: обязательные и исключённые слова
SELECT * FROM articles
WHERE MATCH(title, body)
      AGAINST ('+mysql -oracle' IN BOOLEAN MODE);

Минимальная длина слова задаётся переменной innodb_ft_min_token_size (по умолчанию 3). Стоп-слова хранятся в таблице INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD.

Spatial (пространственный) индекс

Применяется для геометрических типов: POINT, LINESTRING, POLYGON и т.д. Основан на R-Tree. Поддерживается только в MyISAM и (с MySQL 5.7.5+) в InnoDB. Столбец должен быть NOT NULL.

CREATE TABLE locations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  coords POINT NOT NULL SRID 4326
) ENGINE=InnoDB;

CREATE SPATIAL INDEX idx_coords ON locations(coords);

-- Поиск точек внутри полигона (MySQL 8.0+)
SELECT name FROM locations
WHERE ST_Contains(
  ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 4326),
  coords
);

Hash индекс

Нативно поддерживается только в MEMORY-таблицах. InnoDB реализует Adaptive Hash Index автоматически — вручную создать его нельзя. Работает только для точного совпадения (=, IN), не поддерживает range-запросы.

Дополнительные виды

• Covering index — составной индекс, содержащий все столбцы запроса; MySQL не обращается к строке данных (Extra: Using index).
• Prefix index — индексируется только первые N символов строки: CREATE INDEX idx ON t(col(20)).
• Invisible index (MySQL 8.0) — индекс существует, но оптимизатор его игнорирует: ALTER TABLE t ALTER INDEX idx INVISIBLE. Удобно для проверки влияния перед удалением.
• Descending index (MySQL 8.0) — CREATE INDEX idx ON t(col DESC); помогает при ORDER BY ... DESC.
• Functional index (MySQL 8.0) — индекс по выражению: CREATE INDEX idx ON t((LOWER(email))).

Просмотр индексов

SHOW INDEX FROM employees;
SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'employees';

Подводные камни

• Составной индекс работает только с «левым префиксом»: индекс (a, b, c) не поможет запросу по одному столбцу b.
• LIKE с ведущим подстановочным знаком (LIKE '%word') не использует B-Tree индекс — всегда полное сканирование.
• Функции над индексированным столбцом в WHERE (WHERE YEAR(created_at) = 2024) делают индекс бесполезным; используйте range: created_at BETWEEN '2024-01-01' AND '2024-12-31'.
• FULLTEXT-поиск не работает для слов, совпадающих со стоп-словами, и слов короче innodb_ft_min_token_size.
• Spatial-индексы в InnoDB требуют SRID; смешивание координат без SRID приведёт к некорректным геометрическим вычислениям.
• Invisible index (MySQL 8.0) игнорируется оптимизатором, но продолжает обновляться при DML — нагрузка сохраняется.
• InnoDB всегда кластеризует данные по PRIMARY KEY; вторичный индекс хранит значение PK, поэтому длинный PK увеличивает размер всех вторичных индексов.
• NULL-значения включаются в B-Tree индекс (в отличие от некоторых других СУБД), но запрос WHERE col = NULL никогда не вернёт строк — нужно IS NULL.

• Не уточнять storage engine и говорить о индексы mysql так, будто MyISAM и InnoDB ведут себя одинаково.
• Игнорировать implicit commit, autocommit, isolation level или binlog там, где они меняют ответ.
• Описывать PostgreSQL-поведение как MySQL-поведение.
• Забывать проверить план через EXPLAIN и фактическую версию MySQL.

• Кандидат объясняет индексы mysql через реальный механизм MySQL, а не общими словами.
• Приводит корректный SQL или диагностический запрос для этой СУБД.
• Называет ограничения, версионные отличия или эксплуатационные последствия.
• Связывает ответ с проектированием приложения, производительностью, надежностью или безопасностью.