0Pricing
SQL Interview Prep · Урок

Покрывающие индексы и индексное сканирование

Добавление столбцов, чтобы запросу никогда не приходилось обращаться к таблице

«Покрывающие индексы и индексное сканирование» — бесплатный урок SQL Interview Prep на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Interview Prep содержит 4 уроков всего.

Вспоминаем об извлечении из кучи

Ранее Вы узнали, что обычное B-дерево хранит только индексированные столбцы и указатель на строку, поэтому после того, как индекс находит совпадения, СУБД всё равно обращается к таблице, чтобы прочитать остальные столбцы. Это обращение называется извлечением из кучи, и именно его призван устранить покрывающий индекс.

Интервьюеры спрашивают о покрывающих индексах, чтобы понять, знаете ли Вы, почему индекс может полностью ответить на запрос, не обращаясь к таблице.

Что означает «покрывающий»

Индекс покрывает запрос, если каждый нужный запросу столбец — в SELECT, WHERE, ORDER BY и GROUP BY — присутствует в самом индексе.

В этом случае СУБД читает только индекс и никогда не обращается к таблице. В PostgreSQL это называется сканированием только индекса, а в сервере SQL и других СУБД — покрывающим индексом. Результат — меньше чтений страниц и более быстрые запросы.

Пример: покрываемый запрос

Предположим, запросу нужны только customer_id и order_date. Составной индекс ровно по этим столбцам содержит всё необходимое запросу, поэтому на него можно ответить, не обращаясь ни к чему, кроме самого индекса.

CREATE INDEX idx_orders_cust_date
  ON orders (customer_id, order_date);

-- Covered: both selected columns are in the index
SELECT customer_id, order_date
FROM orders
WHERE customer_id = 42;

Один дополнительный столбец нарушает покрытие

Добавьте столбец, которого нет в индексе, — и покрытие будет потеряно: СУБД придётся извлекать данные из кучи, чтобы получить этот столбец.

Здесь total отсутствует в индексе, поэтому, хотя customer_id определяет поиск, для каждой совпавшей строки выполняется извлечение из кучи, чтобы прочитать total.

-- NOT covered: total is not in the index, forces heap fetches
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 42;

Предложение INCLUDE

Можно добавить total как четвёртый ключевой столбец, но если Вы никогда не фильтруете и не сортируете по нему, это напрасно увеличивает объём данных в порядке сортировки дерева. Более аккуратный инструмент — INCLUDE (поддерживается PostgreSQL и сервером SQL): он хранит дополнительные столбцы только в листьях индекса как полезные данные, а не как часть ключа сортировки.

Теперь запрос покрывается без раздувания доступной для поиска части индекса.

CREATE INDEX idx_orders_cust_date_inc
  ON orders (customer_id, order_date)
  INCLUDE (total);

-- Now covered: total is carried in the leaf
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 42;

Ключевые и включённые столбцы

Точное различие, которое произведёт впечатление на интервьюеров:

  • Ключевые столбцы определяют порядок сортировки и могут использоваться для индексного поиска и сканирования диапазона. На них распространяется правило крайнего левого префикса.
  • Включённые столбцы хранятся только в листьях как дополнительные данные; искать по ним нельзя, но они позволяют индексу покрывать больше запросов.

Практическое правило: столбцы, по которым Вы фильтруете или сортируете, помещайте в ключ, а столбцы, которые Вы только возвращаете, — в INCLUDE.

MySQL/InnoDB: кластерный нюанс

Продемонстрируйте знание различий между диалектами. Таблицы InnoDB (MySQL) кластеризованы по первичному ключу: вторичные индексы неявно содержат столбцы первичного ключа. Поэтому вторичный индекс автоматически покрывает любой запрос, выбирающий только индексированные столбцы и столбцы первичного ключа; предложение INCLUDE не требуется (в MySQL нет INCLUDE).

Идея покрывающего индекса универсальна, а синтаксис и автоматически доступные столбцы зависят от СУБД.

Проверка сканирования только индекса

Докажите наличие покрытия с помощью EXPLAIN. В PostgreSQL узел плана называется Сканирование только индекса, а не Index Scan. Следите за Heap Fetches: 0 в EXPLAIN (ANALYZE): это однозначный признак того, что обращения к таблице не было.

Если Вы ожидали сканирование только индекса, но видите Index Scan с извлечением из кучи, значит, выбранный столбец отсутствует в индексе.

EXPLAIN (ANALYZE)
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 42;
-- Look for: Index Only Scan ... Heap Fetches: 0

Нюанс с картой видимости в PostgreSQL

Нюанс PostgreSQL, достойный дополнительного балла: сканирование только индекса всё ещё может обращаться к куче, если страница не отмечена как полностью видимая в карте видимости. После большого количества обновлений выполните VACUUM, чтобы карта видимости была актуальной; иначе значение Heap Fetches растёт, а преимущество «сканирования только индекса» сокращается.

-- Keeps the visibility map fresh so index-only scans stay heap-free
VACUUM ANALYZE orders;

Когда следует NOT создавать широкий покрывающий индекс

Покрывающие индексы не бесплатны. Добавление множества столбцов в INCLUDE делает индекс большим, расходует кэш и замедляет записи (каждая соответствующая запись обновляет индекс). Вот какие компромиссы стоит озвучить:

  • Отличный вариант для активно используемых, узких и часто выполняемых запросов на чтение.
  • Плохой вариант, если превращать индекс в свалку всех столбцов «на всякий случай».

Покрывайте важный запрос, а не всю строку.

Как сформулировать ответ на собеседовании

Краткое резюме:

«Покрывающий индекс содержит каждый столбец, к которому обращается запрос, поэтому СУБД отвечает на него только по индексу — выполняет сканирование только индекса, пропуская извлечение из кучи. Я помещаю искомые столбцы в ключ, а столбцы, которые только возвращаются, — в INCLUDE, проверяю, что число извлечений из кучи равно нулю, с помощью EXPLAIN ANALYZE и сохраняю индекс компактным, чтобы не снижать скорость записи».

Быстрая проверка

Определите, какие столбцы покрываются и где должен находиться каждый из них.

Итоги: покрывающие индексы

Главные выводы:

  • Индекс покрывает запрос, если содержит каждый нужный запросу столбец, благодаря чему выполняется сканирование только индекса без извлечения из кучи.
  • Ключевые столбцы определяют индексный поиск и подчиняются правилу крайнего левого префикса; столбцы INCLUDE — это полезные данные только в листьях, предназначенные для покрытия.
  • Вторичные индексы InnoDB неявно включают первичный ключ.
  • Проверяйте результат с помощью EXPLAIN (ANALYZE) и следите за Heap Fetches; в PostgreSQL регулярно выполняйте VACUUM.
  • Сохраняйте покрывающие индексы компактными, чтобы не снижать производительность записи.

Далее — обратная сторона: когда индексы действительно вредят.

Часто задаваемые вопросы

Урок «Покрывающие индексы и индексное сканирование» бесплатный?

Да — полный текст урока «Покрывающие индексы и индексное сканирование» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Interview Prep, подпишись на CoddyKit PRO. Курс SQL Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «Покрывающие индексы и индексное сканирование»?

Добавление столбцов, чтобы запросу никогда не приходилось обращаться к таблице Ты практикуешь SQL Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Interview Prep?

Предыдущий опыт не требуется. SQL Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.

Сколько времени занимает урок «Покрывающие индексы и индексное сканирование»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Interview Prep?

Да. Каждый урок SQL Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Индексы B-дерева и их преимущества
  2. Порядок столбцов составного индекса
  3. Покрывающие индексы и индексное сканирование
  4. Когда индексы вредят: записи и избирательность
← Назад к SQL Interview Prep