Индексы и сценарии чтения

Запросы каталога стали выразительными, но при росте таблицы хочется понять, как база найдёт нужные строки. Индекс предоставляет дополнительный способ доступа по выбранным ключам. Это не изменение смысла SQL и не обещание ускорения любого запроса. Сначала нужно назвать конкретный сценарий чтения, затем выбрать подходящую структуру и проверить решение планировщика.

Используем PostgreSQL 17.11 и снимок catalog-db/lesson-14 из архива серии. В нём всего шесть курсов, поэтому набор пригоден для объяснения структуры, но не для вывода о производительности большого сайта. SQL и измерения не запускались. Учебный индекс создаётся внутри транзакции и откатывается, а reset остаётся отдельным разрушительным восстановлением начальной схемы.

Индекс начинается с запроса

Публичная страница фронтенда выбирает курсы с topic_id = 1. Пока посмотрим все состояния, чтобы отделить связь с темой от публикации:

SELECT id, slug, title
FROM catalog.courses
WHERE topic_id = 1
ORDER BY id;

Ожидаются HTML, JavaScript и производительность. Индекс по topic_id соответствует условию равенства, поскольку помогает найти строки с одним значением выбранного ключа. Однако финальный порядок по id является отдельным требованием. Индекс только по теме не становится автоматически полным договором сортировки карточек.

Для такого сценария возможен кандидат:

BEGIN;
CREATE INDEX courses_topic_idx ON catalog.courses (topic_id);

В PostgreSQL обычный индекс без указания метода использует B-tree. Его можно рассматривать как упорядоченную дополнительную структуру ключей с доступом к соответствующим строкам. Основной смысл показан в введении в индексы PostgreSQL. Мы не переносим сюда физическую модель кластеризованного индекса SQL Server из старого курса сайта.

Создание не меняет количество курсов. Тот же SELECT должен получить тот же предметный результат с индексом и без него. Если страница требует определённый порядок, ORDER BY остаётся в запросе. Существование упорядоченной структуры не даёт приложению право ожидать порядок произвольной выборки без явного требования.

Уже существующие структуры

Первичный ключ и уникальность slug поддерживаются соответствующими индексами. Поэтому не нужно автоматически создавать ещё один обычный индекс на courses.id или courses.slug только потому, что приложение читает эти колонки. Сначала прочитайте метаданные и поймите, какая структура уже существует и какую задачу она выполняет.

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'catalog' AND tablename = 'courses'
ORDER BY indexname;

До учебного создания ожидаются структуры для первичного ключа и уникального кода. Во время транзакции появится также courses_topic_idx. После ROLLBACK он исчезнет, а индексы ограничений останутся. В lesson.sql чтение метаданных расположено до создания, после него и после отката, чтобы различие было видно при будущем ручном повторении.

Внешний ключ topic_id сам по себе не создаёт обычный индекс на ссылающейся колонке курса. Существование связи отвечает за целостность; способ чтения дочерних строк выбирают отдельно. С другой стороны, уникальность (course_id, position) у уроков уже даёт структуру, начинающуюся с ключа курса. Добавление ещё одного похожего индекса без разбора нагрузки может оказаться лишним.

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

Планировщик выбирает способ доступа

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

Снимок содержит запрос плана без выполнения выборки:

EXPLAIN (COSTS OFF)
SELECT id, slug, title
FROM catalog.courses
WHERE topic_id = 1
ORDER BY id;
ROLLBACK;

Здесь нет заранее обещанного вывода Index Scan. Мы не запускали PostgreSQL и не измеряли результат. Реальный ответ читатель получит только в своей будущей среде. Следующая глава объяснит, как читать форму плана, чтобы сравнение не сводилось к поиску одного знакомого слова.

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

Форма условия тоже важна

Индекс содержит ключи выбранного выражения. Простое равенство topic_id = 1 соответствует целочисленной колонке. Если обернуть индексируемое текстовое поле преобразованием, например искать по lower(title), обычный индекс на title не обязательно будет обслуживать такой поиск как равенство по своему ключу. Для выражений и текстового поиска существуют отдельные решения.

Даже поиск по slug имеет разные варианты. Точное совпадение одного кода отличается от поиска подстроки в середине названия. Нельзя обещать одинаковую работу обычного B-tree для обоих только потому, что входное значение текстовое. Сначала определяют оператор, порядок и формат данных, затем проверяют поддержку выбранного метода.

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

В старом уроке индексов SQL Server есть полезная аналогия указателя книги, но его дальнейшая модель относится к другому инструменту. В PostgreSQL нельзя делать вывод «первичный ключ физически упорядочил всю таблицу» из факта существования индекса. SQL-порядок и устройство хранения должны обсуждаться по выбранной системе.

Изменение данных и цена структуры

Курс может перейти из одной темы в другую. Тогда индексируемый ключ topic_id меняется, и дополнительная структура должна отражать новое значение. Созданный индекс не является статичной закладкой, которую можно обновлять вручную раз в месяц без влияния на чтение. Поддержание соответствия — часть работы базы при изменениях.

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

Если два индекса имеют похожий ведущий ключ, они не обязательно полностью дублируют друг друга. Один может обеспечивать уникальность, другой порядок и иной набор полей. Но похожесть является поводом прочитать определения и нагрузку до создания ещё одной структуры. Простое сравнение имён индексов не показывает их действительную область.

В следующих главах будем сохранять исходный предметный ответ и сравнивать кандидаты через план. Такая последовательность полезнее обещания определённого процента ускорения заранее. Изменение способа доступа должно объясняться проверяемыми условиями, а не появлением ещё одной строки CREATE INDEX в истории разработки.

При будущем ручном повторении сравните предметный ответ SELECT до и во время учебного индекса, а затем прочитайте метаданные после отката. Строки каталога остаются прежними, дополнительная структура исчезает. Такое наблюдение отделяет корректность модели от кандидата оптимизации: теперь вы можете обосновать индекс конкретным запросом и не выдавать само его наличие за доказанную быструю работу.

Оглавление курса.