Индексы в SQL: B-tree, составные индексы, EXPLAIN и когда индекс не работает
Индекс по created_at в таблице сообщений создан, а запрос SELECT * FROM messages WHERE DATE(created_at) = '2024-03-01'; всё равно перебирает все 100 000 строк: EXPLAIN показывает type: ALL и key: NULL. Тот же отбор, записанный как диапазон created_at >= '2024-03-01' AND created_at < '2024-03-02', читает через индекс 1440 строк. Разница всего лишь в том, что над столбцом стоит функция.
Индекс в базе данных — отдельная упорядоченная структура, которая хранит значения одного или нескольких столбцов таблицы вместе со ссылками на строки и позволяет найти нужные строки без полного перебора таблицы. В MySQL с движком InnoDB почти все индексы устроены как B-дерево (B+tree). За скорость поиска платят местом на диске и замедлением INSERT, UPDATE и DELETE: при каждом изменении строки база обновляет и все её индексы.
1. Что такое индекс в SQL и зачем он нужен
Индекс нужен, чтобы база находила строки по условию WHERE, соединяла таблицы и сортировала результат, не читая всю таблицу. Без индекса MySQL проверяет каждую строку (полный перебор, full scan), с индексом — спускается по упорядоченному дереву к нужным значениям. Цена — место на диске и более медленная запись.
Аналогия из жизни — предметный указатель в конце книги: чтобы найти все страницы про EXPLAIN, не нужно листать книгу целиком. Но указатель занимает страницы, и при каждой правке текста его приходится обновлять.
| Операция | Без индекса | С индексом по столбцу условия |
|---|---|---|
SELECT ... WHERE столбец = значение | Читает все строки таблицы | Читает несколько страниц дерева и только подходящие строки |
SELECT ... WHERE столбец BETWEEN a AND b | Читает все строки | Находит начало диапазона и идёт по соседним значениям |
ORDER BY столбец LIMIT 10 | Сортирует все строки (Using filesort) | Берёт первые 10 значений из индекса, сортировка не нужна |
INSERT одной строки | Записывает строку | Записывает строку и добавляет запись в каждый индекс таблицы |
UPDATE индексированного столбца | Меняет значение | Меняет значение и переставляет запись в индексе |
| Место на диске | Только данные | Данные плюс копия индексированных столбцов и ссылок на строки |
Индекс и ключ — не одно и то же: ключ задаёт правило (уникальность, ссылку на другую таблицу), а индекс ускоряет поиск. MySQL сам строит индекс для каждого PRIMARY KEY, UNIQUE и FOREIGN KEY, а обычный индекс, созданный командой CREATE INDEX, ничего не запрещает. Подробнее — в уроке про ключи.
2. Как устроен B-tree индекс в InnoDB
B-tree индекс — сбалансированное дерево, в котором значения хранятся отсортированными, а все листья находятся на одной глубине. Поиск значения спускается от корня к листу за O(log n) чтений страниц, а соседние листья связаны между собой, поэтому диапазоны и сортировка читаются подряд. В InnoDB используется вариант B+tree: данные лежат только в листьях.
Упрощённо индекс по messages.created_at выглядит так:
корень: [ 2024-02-01 | 2024-03-01 ]
/ | \
листья: [01-01 ... 01-31] <-> [02-01 ... 02-29] <-> [03-01 00:00 id=86404, 03-01 00:01 id=86405, ...] Чтобы найти сообщения за 1 марта, MySQL сравнивает значение с ключами корня, переходит в правую ветку, находит первую запись 2024-03-01 00:00 и идёт вправо по листьям, пока даты не выйдут за границу диапазона. Одна страница InnoDB занимает 16 КБ и вмещает сотни ключей, поэтому даже для таблицы в десятки миллионов строк дерево обычно имеет высоту 3–4 уровня.
Кластерный индекс: строки хранятся в PRIMARY KEY
В InnoDB таблица и есть индекс по первичному ключу: листья кластерного индекса (clustered index) содержат строки целиком, отсортированные по PRIMARY KEY. Поэтому поиск по id — самый быстрый: дошли до листа — получили строку. Если первичного ключа нет, InnoDB берёт первый UNIQUE-индекс по столбцам NOT NULL, а если нет и его — создаёт скрытый 6-байтовый идентификатор строки.
Вторичный индекс: сначала индекс, потом строка
Любой другой индекс — вторичный (secondary). В его листьях лежат значения индексированных столбцов и значение первичного ключа, а не физический адрес строки. Поиск по вторичному индексу проходит в два этапа: найти в нём подходящие id, затем по каждому id спуститься в кластерный индекс за остальными столбцами.
| Индекс | Что лежит в листьях | Как читается SELECT * |
|---|---|---|
Кластерный (PRIMARY KEY (id)) | Вся строка: id, topic_id, author_id, body, created_at | Один спуск по дереву |
Вторичный (created_at) | created_at + id | Спуск по вторичному индексу, затем для каждой найденной строки — спуск по кластерному |
Из этого следуют два практических вывода. Длинный первичный ключ (например, UUID в CHAR(36)) раздувает каждый вторичный индекс, потому что копируется в каждую его запись. А если запрос просит только столбцы, которые уже есть во вторичном индексе, второй этап не нужен — такой индекс называют покрывающим (covering index), и EXPLAIN отмечает его словами Using index в столбце Extra. Пример — в разделе 5.
3. Данные для примеров: 100 000 сообщений
Чтобы увидеть работу индексов, нужна таблица, которую дорого читать целиком. На пяти строках MySQL почти всегда выберет полный перебор: прочитать одну страницу данных дешевле, чем спускаться по индексу, а потом ещё и в кластерный индекс. Поэтому заполним таблицу messages учебной базы forum 100 000 строк.
Пересоздайте базу скриптом из раздела Схема учебной базы форума и выполните блок ниже в MySQL 8. Он добавляет сообщениям дату, создаёт ещё 98 тем и генерирует сообщения рекурсивным CTE внутри INSERT ... SELECT (сама вставка результата запроса разобрана в уроке про INSERT).
ALTER TABLE messages
ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
-- по умолчанию рекурсия ограничена 1000 шагов
SET SESSION cte_max_recursion_depth = 100000;
-- темы с id 3..100
INSERT INTO topics (title, author_id)
WITH RECURSIVE seq (n) AS (
SELECT 3 UNION ALL SELECT n + 1 FROM seq WHERE n < 100
)
SELECT CONCAT('Тема ', n), 1 + n % 3 FROM seq;
-- 100 000 сообщений: по 1000 на тему, одно сообщение в минуту с 1 января 2024
INSERT INTO messages (topic_id, author_id, body, created_at)
WITH RECURSIVE seq (n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 100000
)
SELECT 1 + n % 100,
1 + n % 3,
CONCAT('Сообщение ', n),
TIMESTAMP '2024-01-01 00:00:00' + INTERVAL n MINUTE
FROM seq;
ANALYZE TABLE messages; -- обновить статистику для оптимизатора
SELECT COUNT(*) FROM messages; +----------+
| COUNT(*) |
+----------+
| 100004 |
+----------+ К 100 000 новых строк добавились четыре сообщения из исходного скрипта. Получившееся распределение пригодится для проверки выводов EXPLAIN: у каждой темы около 1000 сообщений (у тем 1 и 2 — по 1002, с учётом двух исходных), у каждого из трёх авторов — примерно треть таблицы, за каждые сутки со 2 января по 9 марта 2024 — ровно 1440 сообщений.
Числа в EXPLAIN у вас будут другими
Значения rows, filtered и Cardinality — оценки, которые InnoDB строит по случайной выборке страниц. После ANALYZE TABLE на тех же данных вы увидите, например, 99 812 вместо 99 800. Точными в выводах урока являются type, key, key_len и Extra — по ним и читают план.
4. CREATE INDEX, DROP INDEX и SHOW INDEX
Индекс создают командой CREATE INDEX имя ON таблица (столбцы);, той же операцией внутри ALTER TABLE ... ADD INDEX или прямо в CREATE TABLE. Удаляют командой DROP INDEX имя ON таблица;, а список индексов таблицы выводит SHOW INDEX FROM таблица;. Имя индекса уникально в пределах таблицы.
| Действие | Команда | Что важно знать |
|---|---|---|
| Создать индекс | CREATE INDEX idx_topics_title ON topics (title); | Повтор имени — ERROR 1061: Duplicate key name |
| То же через ALTER TABLE | ALTER TABLE topics ADD INDEX idx_topics_title (title); | В одном ALTER TABLE можно добавить несколько индексов |
| При создании таблицы | CREATE TABLE ... (..., INDEX idx_topics_title (title)); | KEY — синоним INDEX |
| Уникальный индекс | CREATE UNIQUE INDEX uq_topics_title ON topics (title); | Не создастся, если в столбце уже есть повторы (ERROR 1062) |
| Составной индекс | CREATE INDEX idx_messages_topic_created ON messages (topic_id, created_at); | Порядок столбцов важен — раздел 6 |
| Удалить индекс | DROP INDEX idx_topics_title ON topics; | Или ALTER TABLE topics DROP INDEX idx_topics_title; |
| Посмотреть индексы | SHOW INDEX FROM messages; | Одна строка на каждый столбец каждого индекса |
Какие ещё действия умеет ALTER TABLE, разобрано в уроке про DDL. Столбец TEXT или BLOB целиком проиндексировать нельзя: CREATE INDEX idx_messages_body ON messages (body); завершится ошибкой ERROR 1170 (42000): BLOB/TEXT column 'body' used in key specification without a key length. Для таких столбцов указывают длину префикса — (body(50)), и индекс хранит только первые 50 символов.
Индексы, которые MySQL создаёт сам, отдельно объявлять не нужно: для PRIMARY KEY, каждого UNIQUE и для столбца внешнего ключа, если подходящего индекса ещё нет (урок про ключи). Поэтому у messages индексы уже есть:
SHOW INDEX FROM messages; +----------+------------+--------------------+--------------+-------------+-------------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Cardinality | Null | Index_type | Visible |
+----------+------------+--------------------+--------------+-------------+-------------+------+------------+---------+
| messages | 0 | PRIMARY | 1 | id | 99800 | | BTREE | YES |
| messages | 1 | fk_messages_topic | 1 | topic_id | 100 | | BTREE | YES |
| messages | 1 | fk_messages_author | 1 | author_id | 3 | YES | BTREE | YES |
+----------+------------+--------------------+--------------+-------------+-------------+------+------------+---------+ Вывод сокращён: показаны не все столбцы SHOW INDEX. Non_unique = 0 означает уникальный индекс. Cardinality — оценка числа разных значений: 100 тем, 3 автора. Чем ближе она к числу строк, тем избирательнее индекс. По author_id с кардинальностью 3 каждое значение встречается в трети таблицы, и такой индекс плохо отсекает строки — к этому вернёмся в разделе 7.
5. EXPLAIN в MySQL: как прочитать план запроса
EXPLAIN перед командой SELECT (а также UPDATE, DELETE, INSERT) показывает план выполнения: в каком порядке MySQL читает таблицы, какой индекс выбрал и сколько строк ожидает прочитать. Сам запрос при этом не выполняется. Главные столбцы плана — type, key, rows и Extra.
Возьмём запрос из начала урока — сообщения за 1 марта, записанные диапазоном. Индекса по created_at пока нет. Завершитель \G в клиенте mysql выводит строку плана вертикально, так её удобнее читать:
EXPLAIN SELECT * FROM messages
WHERE created_at >= '2024-03-01' AND created_at < '2024-03-02'\G *************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: messages
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 99800
filtered: 11.11
Extra: Using where type: ALL — полный перебор таблицы, подходящих индексов нет (possible_keys: NULL), MySQL ожидает прочитать около 99 800 строк и отфильтровать их условием (Using where). filtered: 11.11 — грубая оценка доли строк, которые пройдут условие: без индекса и гистограммы оптимизатор считает каждое неравенство пропускающим треть строк. Создадим индекс и повторим:
CREATE INDEX idx_messages_created ON messages (created_at);
EXPLAIN SELECT * FROM messages
WHERE created_at >= '2024-03-01' AND created_at < '2024-03-02'\G *************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: messages
partitions: NULL
type: range
possible_keys: idx_messages_created
key: idx_messages_created
key_len: 5
ref: NULL
rows: 1440
filtered: 100.00
Extra: Using index condition Теперь type: range — чтение диапазона индекса, и вместо 99 800 строк план обещает 1440 — ровно столько сообщений за сутки. key_len: 5 — столько байт ключа используется: DATETIME занимает 5 байт. Using index condition означает, что условие проверяется прямо на записях индекса (Index Condition Pushdown), до перехода в кластерный индекс за остальными столбцами строки.
Если же запросу нужны только столбцы, которые есть в индексе, переход в кластерный индекс не нужен вовсе. id хранится в каждом вторичном индексе, поэтому этот запрос читает только idx_messages_created:
EXPLAIN SELECT id, created_at FROM messages
WHERE created_at >= '2024-03-01' AND created_at < '2024-03-02'\G type: range
key: idx_messages_created
key_len: 5
rows: 1440
Extra: Using where; Using index (здесь и дальше вывод сокращён до значимых строк). Using index — признак покрывающего индекса.
Значения type: от лучшего к худшему
| type | Что делает MySQL | Пример на таблице messages |
|---|---|---|
const | Находит не больше одной строки по первичному или уникальному ключу и подставляет её как константу | WHERE id = 500 (key PRIMARY, key_len 4) |
eq_ref | При соединении для каждой строки первой таблицы находит ровно одну строку второй по уникальному ключу | JOIN users u ON u.id = m.author_id — для таблицы u |
ref | Находит все строки с заданным значением неуникального индекса | WHERE topic_id = 2 (key_len 4, ref const) |
range | Читает диапазон индекса: >, <, BETWEEN, IN, LIKE 'abc%' | WHERE created_at >= '2024-03-01' AND created_at < '2024-03-02' |
index | Читает индекс целиком, но не таблицу | SELECT author_id FROM messages |
ALL | Читает всю таблицу | WHERE body LIKE '%лёд%' |
index лучше ALL только тем, что индекс компактнее таблицы: строк перебирается столько же. Соединения разобраны в уроке про JOIN, а значение DEPENDENT SUBQUERY в столбце select_type — в уроке про подзапросы.
Остальные столбцы и Extra
| Столбец / значение | Что означает | На что смотреть |
|---|---|---|
possible_keys | Индексы, которые подходят к условиям запроса | NULL — подходящего индекса нет вообще |
key | Индекс, который оптимизатор выбрал | possible_keys не пуст, а key: NULL — индекс есть, но решено читать таблицу |
key_len | Сколько байт ключа используется | Для составного индекса показывает, сколько столбцов реально задействовано |
rows | Оценка числа строк, которые нужно прочитать | Сравнивайте с размером таблицы и с ожидаемым результатом |
filtered | Оценка процента строк, которые останутся после условий | Низкий процент при большом rows — читается много лишнего |
Using where | Строки дополнительно проверяются условием WHERE | Нормально; плохо в паре с type: ALL на большой таблице |
Using index | Покрывающий индекс: строки таблицы не читаются | Хороший признак |
Using filesort | Результат сортируется отдельно, индекс порядок не дал | На большом числе строк — кандидат на индекс под ORDER BY |
Using temporary | Нужна временная таблица: GROUP BY, DISTINCT, UNION по неиндексированным выражениям | Например, GROUP BY DATE(created_at) |
key_len легко посчитать вручную. INT NOT NULL — 4 байта, INT, допускающий NULL, — 5 (ещё байт на признак NULL), DATETIME NOT NULL — 5, VARCHAR(50) в utf8mb4 NOT NULL — 50 × 4 + 2 = 202 байта (2 байта хранят длину). Поэтому WHERE author_id = 3 даёт key_len: 5, а WHERE topic_id = 2 — 4: столбец messages.author_id допускает NULL.
EXPLAIN ANALYZE и FORMAT=TREE
С MySQL 8.0.18 команда EXPLAIN ANALYZE выполняет запрос и показывает план в виде дерева с реальным временем и числом строк рядом с оценками. Расхождение оценки (rows= в скобках с cost) и факта (actual ... rows=) в разы — повод выполнить ANALYZE TABLE.
EXPLAIN ANALYZE SELECT * FROM messages
WHERE created_at >= '2024-03-01' AND created_at < '2024-03-02'; -> Index range scan on messages using idx_messages_created, with index condition: ((messages.created_at >= TIMESTAMP'2024-03-01 00:00:00') and (messages.created_at < TIMESTAMP'2024-03-02 00:00:00')) (cost=648 rows=1440) (actual time=0.041..2.37 rows=1440 loops=1) Время и стоимость зависят от машины и версии сервера. Тот же формат дерева без выполнения запроса выдаёт EXPLAIN FORMAT=TREE SELECT ..., а FORMAT=JSON добавляет подробные оценки стоимости.
EXPLAIN ANALYZE не просто показывает план
Обычный EXPLAIN запрос не выполняет, а EXPLAIN ANALYZE выполняет его полностью, только не отдаёт строки клиенту. Запрос, который работает минуту, будет работать минуту и под EXPLAIN ANALYZE. На рабочей базе начинайте с обычного EXPLAIN.
6. Составной индекс и правило левого префикса
Составной индекс (composite index) строится по нескольким столбцам: записи упорядочены по первому столбцу, при равном первом — по второму, и так далее. Поэтому индекс (a, b, c) работает для условий на a, на a и b, на a, b и c, но не для условий только на b или c. Это правило левого префикса (leftmost prefix).
Порядок похож на телефонный справочник, отсортированный по фамилии, затем по имени: всех Ивановых найти легко, всех Иванов Ивановых — тоже, а всех людей с именем Иван справочник не поможет найти — они разбросаны по всем фамилиям.
Пусть в таблице есть индекс (topic_id, author_id, created_at). Вот какие условия он поддерживает и сколько байт ключа покажет key_len (4 + 5 + 5, как посчитано в разделе 5):
| Условие WHERE | Используется индекс? | key_len | Почему |
|---|---|---|---|
topic_id = 2 | Да | 4 | Префикс (a) |
topic_id = 2 AND author_id = 3 | Да | 9 | Префикс (a, b) |
topic_id = 2 AND author_id = 3 AND created_at >= '2024-03-01' | Да, все три столбца | 14 | Равенства по a и b, диапазон по последнему столбцу |
topic_id = 2 AND created_at >= '2024-03-01' | Только первый столбец | 4 | Пропущен author_id; условие на дату проверяется на записях индекса (Using index condition) |
topic_id > 50 AND author_id = 3 | Только первый столбец | 4 | После диапазона по a записи с нужным b не идут подряд |
author_id = 3 | Нет | — | Нет первого столбца |
author_id = 3 AND created_at >= '2024-03-01' | Нет | — | Нет первого столбца |
Исключение из правила — skip scan (MySQL 8.0.13+): оптимизатор может перебрать значения первого столбца и для каждого искать по второму. Он срабатывает редко — только когда запрос читает исключительно столбцы индекса и у первого столбца мало разных значений. Проектировать индексы в расчёте на него не стоит.
Порядок условий в WHERE роли не играет
WHERE author_id = 3 AND topic_id = 2 использует индекс (topic_id, author_id, ...) точно так же, как WHERE topic_id = 2 AND author_id = 3: оптимизатор сам сопоставляет условия со столбцами. Важен порядок столбцов в самом индексе, а не в тексте запроса.
Сначала равенство, потом диапазон
Для запроса WHERE topic_id = 2 AND created_at >= '2024-03-01' подходит индекс (topic_id, created_at): внутри темы 2 записи уже отсортированы по дате, и MySQL читает один непрерывный отрезок — key_len равен 4 + 5 = 9. Индекс (created_at, topic_id) для того же запроса хуже: диапазон по первому столбцу захватывает сообщения всех тем после 1 марта, и topic_id проверяется уже внутри этого отрезка (key_len: 5). Правило: столбцы с условием равенства ставят в индексе первыми, столбец с диапазоном — после них.
Индекс для WHERE и ORDER BY одновременно
Типичный запрос форума — последние 20 сообщений темы. С одиночным индексом по topic_id MySQL находит сообщения темы, но сортирует их отдельно. Чтобы сравнение было честным, временно сделаем индекс по дате из раздела 5 невидимым для оптимизатора — иначе тот может предпочесть его:
ALTER TABLE messages ALTER INDEX idx_messages_created INVISIBLE;
EXPLAIN SELECT id, body, created_at FROM messages
WHERE topic_id = 2
ORDER BY created_at DESC
LIMIT 20\G type: ref
possible_keys: fk_messages_topic
key: fk_messages_topic
key_len: 4
ref: const
rows: 1002
Extra: Using filesort Все 1002 сообщения темы читаются и сортируются, хотя нужны 20. Составной индекс (topic_id, created_at) хранит их уже упорядоченными по дате:
CREATE INDEX idx_messages_topic_created ON messages (topic_id, created_at);
EXPLAIN SELECT id, body, created_at FROM messages
WHERE topic_id = 2
ORDER BY created_at DESC
LIMIT 20\G type: ref
possible_keys: fk_messages_topic,idx_messages_topic_created
key: idx_messages_topic_created
key_len: 4
ref: const
rows: 1002
Extra: Backward index scan Using filesort исчез: MySQL читает индекс с конца отрезка темы 2 (Backward index scan, MySQL 8.0+) и останавливается после 20 строк. rows: 1002 в плане осталось — эта оценка не учитывает LIMIT. key_len: 4 тоже верен: для поиска используется только topic_id, а created_at даёт готовый порядок. Вернём индексу по дате видимость, он понадобится дальше:
ALTER TABLE messages ALTER INDEX idx_messages_created VISIBLE; Сортировка по индексу работает, когда ORDER BY идёт по столбцам индекса в его порядке после столбцов с равенством. ORDER BY created_at DESC, id DESC тоже обходится без сортировки (id неявно хранится в конце каждого вторичного индекса), а смешанное направление вроде ORDER BY topic_id ASC, created_at DESC без условия на тему требует индекса, объявленного как (topic_id ASC, created_at DESC) — убывающие индексы поддерживаются с MySQL 8.0.
7. Почему индекс не работает
Индекс не используется, когда условие нельзя превратить в поиск по упорядоченным значениям: столбец обёрнут функцией, строка ищется с ведущим %, типы сравниваемых значений не совпадают, нарушен левый префикс. Ещё один случай — оптимизатор сам решает, что полный перебор дешевле. Во всех случаях EXPLAIN показывает key: NULL или type: ALL.
| Причина | Пример | Что делать |
|---|---|---|
| Функция или выражение над столбцом | WHERE DATE(created_at) = '2024-03-01', WHERE YEAR(created_at) = 2024, WHERE id + 1 = 10 | Переписать в диапазон по самому столбцу или создать функциональный индекс |
LIKE с ведущим % | WHERE title LIKE '%рыбалк%' | LIKE 'Зимн%' индекс использует; поиск по слову — FULLTEXT-индекс и MATCH ... AGAINST |
| Строковый столбец сравнивают с числом | WHERE phone = 79001234567 при phone VARCHAR | Передавать строку: phone = '79001234567' |
| OR по разным столбцам | WHERE topic_id = 2 OR author_id = 3 | Индекс на каждом столбце (index merge) или UNION двух запросов |
| Нарушен левый префикс | WHERE author_id = 3 при индексе (topic_id, author_id) | Отдельный индекс или другой порядок столбцов (раздел 6) |
| Низкая селективность | WHERE author_id = 1 — треть таблицы | Это не ошибка: full scan тут действительно дешевле |
!=, NOT IN, NOT LIKE | WHERE topic_id != 2 — 99% строк | Обычно ничего: под условие попадает почти вся таблица |
| Разные кодировки или collation в JOIN | Столбец utf8mb3 соединяют со столбцом utf8mb4 | Привести столбцы к одной кодировке и collation |
Функция над столбцом
Индекс хранит значения created_at, а не DATE(created_at). Чтобы проверить условие с функцией, MySQL должен вычислить её для каждой строки:
EXPLAIN SELECT * FROM messages WHERE DATE(created_at) = '2024-03-01'\G type: ALL
possible_keys: NULL
key: NULL
rows: 99800
filtered: 100.00
Extra: Using where Это и есть запрос из начала урока. Перепишите условие так, чтобы столбец остался «голым»: created_at >= '2024-03-01' AND created_at < '2024-03-02' — план с type: range и 1440 строками показан в разделе 5. Так же YEAR(created_at) = 2024 превращается в created_at >= '2024-01-01' AND created_at < '2025-01-01'. Правая граница строгая: BETWEEN '2024-03-01' AND '2024-03-02' захватил бы ещё сообщение ровно в полночь 2 марта.
Если выражение нельзя переписать, в MySQL 8.0.13+ есть функциональный индекс — индекс по выражению, оно пишется в двойных скобках:
CREATE INDEX idx_messages_created_day ON messages ((DATE(created_at))); Он сработает только для запросов с точно таким же выражением. Для дат проще и универсальнее переписать условие в диапазон.
Неявное приведение типов: что выведет этот запрос?
Столбец users.phone — VARCHAR(20) с уникальным индексом uq_users_phone. Номер передали числом, без кавычек:
EXPLAIN SELECT * FROM users WHERE phone = 79001234567;
SHOW WARNINGS; В плане будет type: ALL, а среди предупреждений — примечание:
Note 1739: Cannot use ref access on index 'uq_users_phone' due to type or collation conversion on field 'phone' При сравнении строки с числом MySQL переводит в число каждое значение столбца. Строки '79001234567', '+79001234567' и ' 79001234567' все равны числу 79001234567, а в индексе они лежат в разных местах — отсортированный порядок строк для такого поиска бесполезен. Обратная ситуация безопасна: WHERE id = '5' по числовому id индекс использует, потому что приводится одна константа, а не столбец. Тот же Note 1739 появляется, когда в JOIN соединяют строковые столбцы с разной кодировкой или collation.
Индекс есть, но оптимизатор его не берёт
Оптимизатор MySQL выбирает план по оценке стоимости, а не по наличию индекса. Чтение через вторичный индекс — это для каждой строки отдельный переход в кластерный индекс, то есть случайное чтение страниц. Когда условию соответствует заметная доля таблицы (например, author_id = 1 — около 33 000 строк из 100 000), последовательно прочитать всю таблицу дешевле. В плане это видно так: possible_keys: fk_messages_author, а key: NULL. Если вы уверены, что индекс лучше, сначала обновите статистику (ANALYZE TABLE), а подсказки FORCE INDEX оставьте на крайний случай: при изменении данных они начинают мешать.
С OR по разным столбцам MySQL может объединить два индекса (type: index_merge, в Extra — Using union(...)), но если хотя бы для одного столбца индекса нет или одно из условий неизбирательно, читается вся таблица. Надёжная замена — два запроса через UNION, каждый со своим индексом.
8. Когда индекс не нужен или вредит
Индекс не нужен на маленьких таблицах, на столбцах, по которым не ищут, и на столбцах с двумя-тремя значениями. Вредит он там, где таблица часто меняется: каждый лишний индекс замедляет INSERT, UPDATE и DELETE и занимает место. Отдельная проблема — дублирующие индексы, которые только тратят ресурсы.
Таблица справочника из десятка строк целиком помещается в одну страницу — её индексы, кроме первичного ключа, не нужны. Столбец вроде счётчика просмотров, который обновляется при каждом открытии темы, дорого держать в индексе: при каждом UPDATE запись в индексе удаляется и вставляется на новое место.
Дублирующий индекс
Индекс (a) не нужен, если есть (a, b): по правилу левого префикса составной индекс обслуживает те же запросы. После раздела 6 так получилось с messages: индекс fk_messages_topic (topic_id) дублирует начало idx_messages_topic_created (topic_id, created_at). Внешнему ключу подходит любой индекс, где его столбец стоит первым, поэтому лишний индекс можно удалить:
DROP INDEX fk_messages_topic ON messages; Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0 Без составного индекса та же команда не выполнилась бы: MySQL не даёт удалить последний индекс, на который опирается внешний ключ (ERROR 1553: Cannot drop index 'fk_messages_topic': needed in a foreign key constraint). Обратное правило не работает: (a, b) не заменяется индексом (a), а (b, a) — совсем другой индекс.
Перед удалением индекса на рабочей базе его сначала делают невидимым, как в разделе 6: ALTER TABLE ... ALTER INDEX ... INVISIBLE (MySQL 8.0). Оптимизатор перестаёт его использовать, но индекс продолжает обновляться, и если какой-то запрос резко замедлился, видимость возвращается мгновенно, без пересоздания индекса.
9. Индексы и Java-приложение
В Java-коде индексы объявляют там же, где описана схема: в скриптах миграций (Flyway, Liquibase) или в JPA-аннотации @Table(indexes = ...), которую учитывает генерация схемы Hibernate. Медленные запросы ищут не в коде, а в журнале медленных запросов MySQL (slow query log) и затем разбирают через EXPLAIN.
@Entity
@Table(name = "messages",
indexes = @Index(name = "idx_messages_topic_created",
columnList = "topic_id, created_at"))
public class Message { ... } Индекс на столбце внешнего ключа нужен для каждой связи @ManyToOne: и JOIN, и ленивая загрузка коллекции topic.getMessages() выполняют WHERE topic_id = ?. В MySQL такой индекс появляется автоматически вместе с внешним ключом, а в PostgreSQL — нет, его создают явно.
10. Типичные промахи с индексами
Большинство проблем с индексами не выдаёт ошибок: запрос возвращает правильный результат, только читает всю таблицу. Поэтому любой запрос, который выполняется часто или по большой таблице, проверяют через EXPLAIN — в таблице ниже собрано, что в нём увидеть и как исправить.
| Ситуация | Что видно в EXPLAIN | Как исправить |
|---|---|---|
Функция над индексированным столбцом: DATE(created_at) = ... | type: ALL, possible_keys: NULL | Диапазон по столбцу или функциональный индекс |
Число вместо строки: phone = 79001234567 | type: ALL, Note 1739 в SHOW WARNINGS | Передавать значение того же типа, в Java — setString() |
Индекс (created_at, topic_id) для topic_id = ? AND created_at >= ? | type: range, key_len: 5 вместо 9 | Столбцы равенства первыми: (topic_id, created_at) |
| Поиск по второму столбцу составного индекса | key: NULL | Отдельный индекс или другой порядок столбцов |
Отдельные индексы на topic_id и created_at для WHERE topic_id = ? ORDER BY created_at | Using filesort | Один составной индекс (topic_id, created_at) |
| Индекс на каждый столбец «на всякий случай» | Планы не меняются, вставка медленнее | Индексировать под конкретные запросы, удалять дубли |
| Проверка индекса на таблице из 5 строк | type: ALL, хотя индекс есть | Проверять на объёме, близком к рабочему |
CREATE INDEX по столбцу TEXT | До плана не доходит: ERROR 1170 | Префикс (body(50)) или FULLTEXT-индекс |
Часто задаваемые вопросы
Сколько индексов можно создать на одной таблице?
В InnoDB — до 64 вторичных индексов на таблицу, а в один составной индекс входит не больше 16 столбцов. На практике упираются не в лимит, а в скорость записи: каждый индекс обновляется при каждом INSERT и DELETE. Для типичной таблицы приложения хватает первичного ключа, индексов внешних ключей и нескольких составных индексов под частые запросы.
Чем B-tree индекс отличается от HASH-индекса?
Хеш-индекс находит строку по точному равенству, но не умеет диапазоны, ORDER BY и левый префикс составного ключа. B-tree умеет всё это, поэтому он основной. InnoDB явных хеш-индексов не поддерживает: CREATE INDEX ... USING HASH на таблице InnoDB молча создаёт B-tree, что видно в столбце Index_type вывода SHOW INDEX. Настоящий HASH есть у движка MEMORY, а InnoDB сам строит внутренний adaptive hash index для часто читаемых страниц.
Использует ли MySQL индекс для условия IS NULL?
Да. В отличие от Oracle, где строки с NULL во всех столбцах индекса в B-tree не попадают, MySQL хранит NULL в индексе, и WHERE author_id IS NULL даёт type: ref по индексу fk_messages_author. Условие author_id = 3 OR author_id IS NULL выполняется одним проходом с типом ref_or_null.
Правда ли, что в составном индексе первым ставят самый селективный столбец?
Это правило вторично. Сначала идут столбцы, которые запрос сравнивает через равенство, затем столбец с диапазоном или сортировкой: индекс (topic_id, created_at) лучше (created_at, topic_id) для поиска по теме и дате, хотя у created_at больше разных значений. Селективность решает только выбор между столбцами, которые все сравниваются через равенство. Разбор с key_len — в разделе Составной индекс и правило левого префикса.
Почему пагинация с большим OFFSET медленная, даже если есть индекс?
ORDER BY created_at DESC LIMIT 20 OFFSET 50000 заставляет MySQL пройти по индексу 50 020 записей и выбросить первые 50 000. Индекс избавляет от сортировки, но не от пропуска строк. Для глубоких страниц используют keyset-пагинацию: запоминают дату и id последней показанной строки и запрашивают WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20. Синтаксис LIMIT и OFFSET разобран в уроке про SELECT.
Комментарии