Внутреннее устройство различных типов индексов, их преимущества и ограничения
Индексы — это структуры данных, которые ускоряют поиск в таблице ценой дополнительных затрат на запись и хранение. В MySQL доступно несколько типов индексов, каждый со своей областью применения. Неправильный выбор типа индекса — одна из самых частых причин деградации производительности.
B-Tree (точнее B+Tree) — это структура индекса по умолчанию в MySQL для движков InnoDB и MyISAM. Она представляет собой сбалансированное дерево, в котором:
-- B-Tree индекс создаётся по умолчанию
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_name ON users(last_name, first_name);B-Tree поддерживает три типа операций поиска:
WHERE email = 'test@test.com'WHERE created_at > '2024-01-01'ORDER BY created_at DESC — данные уже упорядочены в B-TreeВысота B-Tree обычно составляет 3-4 уровня даже для таблиц в десятки миллионов строк, потому что каждая страница (по умолчанию 16 КБ) содержит сотни ключей. Это значит любой поиск требует всего 3-4 чтения страниц.
Hash-индекс строит хэш-таблицу: ключ → указатель на строку. Хэш-функция преобразует значение ключа в числовой хэш, который используется для быстрого поиска.
-- Hash индекс можно создать явно только для MEMORY engine
CREATE INDEX idx_hash ON users(email) USING HASH;
-- Для InnoDB адаптивный хэш-индекс создаётся автоматически
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';Ключевое ограничение: Hash-индекс поддерживает ТОЛЬКО точный поиск по равенству (=). Он НЕ работает для:
WHERE price BETWEEN 100 AND 200ORDER BY priceLIKE 'prefix%'Это происходит потому, что хэш-функция разрушает порядок значений: даже 100 и 101 дают совершенно разные, несвязанные хэши.
В InnoDB используется Adaptive Hash Index — механизм, который автоматически строит хэш-индекс для часто используемых значений B-Tree. Вы не управляете им напрямую — InnoDB мониторит обращения и решает сам, когда построить хэш.
Fulltext индекс предназначен для полнотекстового поиска по текстовым столбцам (CHAR, VARCHAR, TEXT). Он разбивает текст на отдельные слова (токены) и строит инвертированный индекс: слово → список документов.
-- Создание Fulltext индекса
CREATE FULLTEXT INDEX idx_ft_body ON articles(body);
CREATE FULLTEXT INDEX idx_ft_title_body ON articles(title, body);
-- Полнотекстовый поиск (по умолчанию NATURAL LANGUAGE MODE)
SELECT id, title, MATCH(body) AGAINST('MySQL optimization') AS score
FROM articles
WHERE MATCH(body) AGAINST('MySQL optimization')
ORDER BY score DESC;
-- BOOLEAN MODE с операторами
SELECT id, title FROM articles
WHERE MATCH(body) AGAINST('+MySQL -PostgreSQL' IN BOOLEAN MODE);Fulltext индекс не заменяет B-Tree для структурированных данных. Его сила — поиск слов и фраз внутри большого текста, с учётом релевантности и булевых операторов.
| Задача | Тип индекса | Пример |
|---|---|---|
| Поиск по email, ID | B-Tree | WHERE email = ? |
| Диапазон по дате | B-Tree | WHERE created_at BETWEEN ? AND ? |
| Сортировка результатов | B-Tree | ORDER BY created_at DESC |
| Точечный lookup (InnoDB) | Adaptive Hash (авто) | Частые WHERE id = ? |
| Поиск слов в тексте | Fulltext | MATCH(body) AGAINST('...') |
| Поисковый движок на сайте | Fulltext | Булевы операторы, релевантность |
Создание индекса для столбца с низкой кардинальностью. Индекс на status со значениями active/inactive (50/50) редко помогает — оптимизатор предпочтёт full table scan, так как индекс фильтрует слишком мало строк.
Ожидание, что Hash-индекс ускорит диапазонный запрос. Hash не поддерживает >, <, BETWEEN, LIKE 'prefix%'. Для этих операций нужен B-Tree.
Fulltext индекс на коротких строках. Если столбец содержит короткие значения (имена, коды), Fulltext может не найти их из-за минимальной длины слова (ft_min_word_len = 4).
Далее: Внутреннее устройство B-Tree индексов