0Pricing
SQL Interview Prep · Урок

Когда индексы вредят: записи и избирательность

Усиление операций записи и причина, по которой индекс столбца с низкой избирательностью бесполезен

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

Вопрос, скрытый за вопросом

После трёх уроков о том, почему индексы помогают, интервьюеры меняют вопрос: «Почему просто не создать индекс для каждого столбца?» Сильный кандидат объясняет, что индексы имеют реальные издержки для записей, а также для кэша и хранилища, и что некоторые индексы планировщик вообще никогда не использует.

В этом уроке рассматриваются две главные причины, по которым индекс может вредить: усиление записи и низкая селективность.

Каждый индекс замедляет записи

Индекс должен оставаться синхронизированным с таблицей. Каждая операция INSERT, каждая операция DELETE и каждое UPDATE индексированного столбца также должны обновлять структуру индекса. Это и есть усиление записи: одно изменение строки превращается в одну запись в таблицу плюс по одной записи для каждого затронутого индекса.

Таблица с восемью индексами выполняет примерно в девять раз больше работы по записи, чем таблица без индексов. Для таблиц с большим количеством записей или высокой пропускной способностью это серьёзные издержки.

Пример: издержки записи

Представьте таблицу событий, которая принимает тысячи строк в секунду. Каждый дополнительный индекс заставляет каждую вставку выполнять больше работы: разделять страницы индекса, обновлять листья и конкурировать за место в кэше.

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

-- Each of these indexes adds cost to EVERY insert below
CREATE INDEX ix_events_user ON events (user_id);
CREATE INDEX ix_events_type ON events (event_type);
CREATE INDEX ix_events_ts   ON events (created_at);

INSERT INTO events (user_id, event_type, created_at)
VALUES (42, 'click', now());  -- now updates table + 3 indexes

Что означает селективность

Селективность показывает, насколько хорошо столбец различает строки: это доля строк, соответствующих обычному значению. Высокая селективность означает мало строк на одно значение (например, для адреса электронной почты или UUID). Низкая селективность означает много строк на одно значение (например, для логического значения или статуса с тремя вариантами).

Индексы особенно полезны для столбцов с высокой селективностью, где поиск отбрасывает почти все строки. Для столбцов с низкой селективностью это часто не так.

Почему индекс с низкой селективностью бесполезен

Предположим, что is_active имеет значение true для 90% пользователей. Поиск по индексу вернул бы 90% таблицы, и для такого количества строк СУБД пришлось бы выполнить извлечение из кучи для каждой строки — это медленнее, чем за один проход последовательно просканировать таблицу.

Поэтому планировщик правильно игнорирует индекс и выполняет последовательное сканирование. В итоге индекс лишь увеличивает накладные расходы на записи и место в хранилище, не давая никакой пользы при чтении.

-- 90% of rows match: the planner will likely skip this index
CREATE INDEX ix_users_active ON users (is_active);
SELECT * FROM users WHERE is_active = true;

Примерный порог

Полезное практическое правило, которое стоит озвучить: когда условие совпадает примерно более чем с 5–20% строк таблицы, последовательное сканирование обычно превосходит сканирование индекса, поскольку случайные обращения к куче стоят дороже, чем последовательное чтение страниц.

Точная граница зависит от размера строк, кэширования и скорости хранилища, поэтому планировщик принимает решение на основе статистики, а не фиксированного числа.

Частичные индексы приходят на помощь

Если Вы запрашиваете только редкие значения столбца с неравномерным распределением, частичный индекс (PostgreSQL) индексирует только эти строки: он компактен, обладает высокой селективностью и недорого поддерживается.

Если 1% заказов имеют статус pending и именно их Вы постоянно запрашиваете, индексируйте только эти строки. Индекс останется небольшим, и планировщик охотно его использует.

-- Index only the rare, frequently-queried rows
CREATE INDEX ix_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Устаревшая статистика вводит планировщик в заблуждение

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

Когда интервьюер говорит: «Индекс существует, но не используется», отличный ответ включает обновление статистики с помощью ANALYZE — прежде чем обвинять сам индекс.

ANALYZE orders;  -- refresh planner statistics

Другие способы, которыми индексы вредят

Дополните ответ менее очевидными издержками:

  • Хранилище и кэш: индексы занимают место на диске и конкурируют за память, вытесняя полезные страницы данных.
  • Избыточные и перекрывающиеся индексы: поддерживаются, но никогда не выбираются.
  • Раздувание: при интенсивных обновлениях B-деревья фрагментируются, и требуется выполнить REINDEX.
  • Путаница для оптимизатора: слишком большое количество похожих индексов замедляет построение плана и делает его менее предсказуемым.

Поиск неиспользуемых индексов

Чтобы обосновать очистку в реальном проекте, упомяните, что PostgreSQL отслеживает использование индексов. Индексы с idx_scan = 0 — кандидаты на удаление: они увеличивают затраты на записи и занимают место, но никогда не обслуживают чтение.

SELECT relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

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

Полное сбалансированное резюме:

«Индексы приводят к усилению записи: каждая вставка, операция обновления или удаления поддерживает их, а кроме того, создают нагрузку на хранилище и кэш. Они окупаются только для условий с высокой селективностью; если условию соответствует большинство строк, планировщик обоснованно выбирает последовательное сканирование, поэтому индекс становится чистыми накладными расходами. Для столбцов с неравномерным распределением я выбираю частичный индекс, поддерживаю статистику актуальной с помощью ANALYZE и удаляю неиспользуемые индексы».

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

Определите, какой индекс с наименьшей вероятностью оправдает свои затраты.

Итоги: когда индексы вредят

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

  • Каждый индекс увеличивает усиление записи, а также расходы на хранилище и кэш.
  • Индексы помогают для столбцов с высокой селективностью; для столбцов с низкой селективностью планировщик предпочитает последовательное сканирование.
  • Если совпадает примерно более 5–20% строк, обычно выигрывает сканирование.
  • Используйте частичный индекс для столбцов с неравномерным распределением, по которым Вы запрашиваете только редкие значения.
  • Поддерживайте статистику актуальной с помощью ANALYZE и удаляйте неиспользуемые индексы (idx_scan = 0).

На этом курс по стратегии индексирования завершён: создавайте индексы там, где они оправдывают себя, и подтверждайте это планом выполнения.

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

Урок «Когда индексы вредят: записи и избирательность» бесплатный?

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

Чему я научусь в уроке «Когда индексы вредят: записи и избирательность»?

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

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

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

Сколько времени занимает урок «Когда индексы вредят: записи и избирательность»?

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

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

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

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

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