Вопросы по теме «Индексы в SQL: B-tree, составные индексы, EXPLAIN и когда индекс не работает»

Всего: 7 вопросов

1. 

Что такое индекс в SQL, зачем он нужен и чем за него приходится платить?

Индекс — отдельная упорядоченная структура, которая хранит значения одного или нескольких столбцов вместе со ссылками на строки и позволяет найти нужные строки без полного перебора таблицы. В InnoDB почти все индексы — B-дерево (B+tree).

  • Без индекса MySQL проверяет каждую строку (full scan, в EXPLAIN — type: ALL).
  • С индексом спускается по дереву к нужным значениям: ускоряются WHERE, соединения и сортировка — ORDER BY столбец LIMIT 10 берёт первые 10 значений из индекса без Using filesort.
  • Цена — место на диске (копия индексированных столбцов) и более медленная запись: каждый INSERT и DELETE обновляет все индексы таблицы, а UPDATE индексированного столбца переставляет запись в индексе.

Индекс и ключ — не одно и то же: ключ задаёт правило (уникальность, ссылку), индекс ускоряет поиск. Для PRIMARY KEY, UNIQUE и FOREIGN KEY MySQL строит индекс сам, а обычный индекс из CREATE INDEX ничего не запрещает.

2. 

Как устроен B-tree индекс в InnoDB и чем кластерный индекс отличается от вторичного?

B-tree — сбалансированное дерево: значения отсортированы, все листья на одной глубине, поиск идёт от корня к листу за O(log n) чтений страниц. Соседние листья связаны, поэтому диапазоны и сортировка читаются подряд. InnoDB использует вариант B+tree: данные лежат только в листьях. Страница — 16 КБ, и даже для десятков миллионов строк высота дерева обычно 3–4 уровня.

  • Кластерный индекс — это PRIMARY KEY: его листья содержат строки целиком, поэтому поиск по id самый быстрый. Без первичного ключа InnoDB берёт первый UNIQUE по столбцам NOT NULL, а если нет и его — скрытый 6-байтовый идентификатор строки.
  • Вторичный индекс хранит значения своих столбцов и значение первичного ключа. Поиск идёт в два этапа: найти id во вторичном индексе, затем по каждому id спуститься в кластерный.

Отсюда два вывода. Длинный первичный ключ (UUID в CHAR(36)) раздувает каждый вторичный индекс. А если запросу хватает столбцов вторичного индекса (включая id), второй этап не нужен — это покрывающий индекс, в EXPLAIN — Extra: Using index.

3. 

Как создать, удалить и посмотреть индексы в MySQL?

CREATE INDEX idx_topics_title ON topics (title);
ALTER TABLE topics ADD INDEX idx_topics_title (title);   -- то же через ALTER TABLE
CREATE UNIQUE INDEX uq_topics_title ON topics (title);   -- уникальный
CREATE INDEX idx_messages_topic_created ON messages (topic_id, created_at);
DROP INDEX idx_topics_title ON topics;                   -- или ALTER TABLE ... DROP INDEX
SHOW INDEX FROM messages;
  • Индекс можно объявить и в CREATE TABLE: INDEX idx_topics_title (title), KEY — синоним INDEX.
  • Имя уникально в пределах таблицы: повтор — ERROR 1061: Duplicate key name. Уникальный индекс не создастся, если в столбце уже есть повторы (ERROR 1062).
  • TEXT и BLOB целиком не индексируются: ERROR 1170: BLOB/TEXT column 'body' used in key specification without a key length. Нужен префикс — (body(50)).
  • SHOW INDEX выводит строку на каждый столбец каждого индекса: Non_unique = 0 — уникальный, Cardinality — оценка числа разных значений, Index_type — BTREE.

Индексы для PRIMARY KEY, UNIQUE и внешнего ключа MySQL создаёт сам.

4. 

Что такое составной индекс и правило левого префикса? В каком порядке ставить столбцы?

Составной индекс строится по нескольким столбцам: записи упорядочены по первому, при равном первом — по второму и так далее. Поэтому индекс (a, b, c) работает для условий на a, на a и b, на все три, но не на b или c без a. Это правило левого префикса, как в справочнике по фамилии и имени: всех Ивановых найти легко, всех Иванов — нет. Порядок условий в WHERE не важен, важен порядок столбцов в индексе.

  • Сначала равенство, потом диапазон. Для topic_id = 2 AND created_at >= '2024-03-01' индекс (topic_id, created_at) читает один отрезок (key_len 9), а (created_at, topic_id) захватывает все темы после 1 марта (key_len 5). После диапазона следующие столбцы для поиска не используются.
  • Индекс под WHERE и ORDER BY. WHERE topic_id = 2 ORDER BY created_at DESC LIMIT 20 с индексом (topic_id) даёт Using filesort, а с (topic_id, created_at) — Backward index scan: строки уже упорядочены, MySQL останавливается после 20.
5. 

Как прочитать EXPLAIN в MySQL: что означают type, key, key_len, rows и Extra?

EXPLAIN SELECT ... показывает план и не выполняет запрос.

  • type от лучшего к худшему: const (одна строка по первичному или уникальному ключу), eq_ref, ref (все строки с значением неуникального индекса), range (диапазон индекса), index (весь индекс), ALL (вся таблица).
  • possible_keys — подходящие индексы, key — выбранный. possible_keys не пуст, а key: NULL — индекс есть, но оптимизатор решил читать таблицу.
  • key_len — байты ключа: INT NOT NULL 4, INT NULL 5, DATETIME 5. По нему видно, сколько столбцов составного индекса задействовано.
  • rows и filtered — оценки по статистике, а не точные числа.
  • Extra: Using index — покрывающий индекс, Using index condition — условие проверяется на записях индекса, Using filesort — отдельная сортировка, Using temporary — временная таблица.

EXPLAIN ANALYZE (MySQL 8.0.18+) запрос выполняет и показывает реальное время и число строк рядом с оценками.

6. 

Почему MySQL не использует индекс, хотя он есть?

Индекс бесполезен, когда условие нельзя превратить в поиск по упорядоченным значениям. В EXPLAIN это key: NULL или type: ALL.

  • Функция над столбцом: DATE(created_at) = '2024-03-01'. Переписать в диапазон created_at >= '2024-03-01' AND created_at < '2024-03-02' или создать функциональный индекс.
  • Ведущий %: LIKE '%рыбалк%'; LIKE 'Зимн%' индекс использует.
  • Строковый столбец сравнивают с числом: phone = 79001234567 — Note 1739 в SHOW WARNINGS. Передавать строку.
  • OR по разным столбцам — нужен индекс на каждом (index merge) или UNION.
  • Нарушен левый префикс: author_id = 3 при индексе (topic_id, author_id).
  • Низкая селективность: author_id = 1 — треть таблицы, последовательный перебор дешевле переходов в кластерный индекс. Это не ошибка.
  • !=, NOT IN и разные кодировки или collation в JOIN.
7. 

Когда индекс не нужен или вредит и как безопасно удалить лишний индекс?

Индекс не нужен на маленьких таблицах (справочник из десятка строк умещается в одну страницу), на столбцах, по которым не ищут, и на столбцах с двумя-тремя значениями. Вредит он на часто меняющихся данных: каждый лишний индекс замедляет INSERT, UPDATE и DELETE. Счётчик просмотров в индексе при каждом UPDATE удаляется и вставляется на новое место.

Дублирующий индекс: (a) лишний, если есть (a, b), — составной обслуживает те же запросы по левому префиксу. Обратное неверно, а (b, a) — другой индекс. Индекс внешнего ключа fk_messages_topic (topic_id) удаляется, когда есть (topic_id, created_at); без него — ERROR 1553: Cannot drop index ... needed in a foreign key constraint.

На рабочей базе индекс сначала делают невидимым: ALTER TABLE messages ALTER INDEX имя INVISIBLE; (MySQL 8.0). Оптимизатор его не использует, но индекс обновляется, и если запрос замедлился, видимость возвращают мгновенно.

Страница 1 из 1