SQL ·
‹ Предыдущий Следующий ›
⏱ 5 минут чтения Обновлено: 2026-11-21

Индексы в 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.

Комментарии

Зарегистрируйтесь или войдите, чтобы иметь возможность оставить комментарий.