Составные индексы и порядок столбцов
Выбирайте порядок столбцов в составных индексах, разберитесь с правилом левого префикса и проектируйте индексы для нескольких запросов
«Составные индексы и порядок столбцов» — бесплатный урок SQL Academy на CoddyKit. Это урок 2 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.
Что такое составной индекс
Индекс более чем по одному столбцу. B-деревья PostgreSQL могут индексировать до 32 столбцов; на практике обычно используют 2–4.
CREATE INDEX orders_user_created_idx ON orders(user_id, created_at DESC);Зачем нужен составной индекс
Два сценария:
- Запрос фильтрует данные сразу по нескольким столбцам
- Запрос фильтрует данные по одному столбцу и сортирует по другому
Правило левого префикса
Индекс по (a, b, c) может использоваться для:
WHERE a = ?WHERE a = ? AND b = ?WHERE a = ? AND b = ? AND c = ?
Он NOT используется только для WHERE b = ? и не используется для WHERE c = ?.
Порядок имеет значение
Поместите наиболее селективный AND наиболее часто фильтруемый столбец первым. Столбцы с проверкой равенства располагайте перед столбцами с диапазоном:
CREATE INDEX orders_user_total_idx ON orders(user_id, total);
-- Best for: WHERE user_id = 42 AND total > 100
-- Also OK: WHERE user_id = 42
-- Useless: WHERE total > 100 (without user_id)Равенство и диапазон
Предикаты с равенством эффективно используют индекс; диапазоны тоже используют его, но ограничивают дальнейшую пользу. Сначала должны идти столбцы с равенством:
-- Index: (status, created_at)
WHERE status = 'paid' AND created_at >= ... -- both columns used
WHERE created_at >= ... AND status = 'paid' -- index used for status, range scan on created_atПорядок индекса = порядок сортировки
Индекс по (user_id, created_at DESC) обслуживает запрос «заказы пользователя, сначала новые» без отдельного шага сортировки:
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC LIMIT 10;
-- Index Scan, no Sort node neededВключение неключевых столбцов (INCLUDE)
PG 11+ поддерживает INCLUDE для «покрывающих» индексов — дополнительные столбцы хранятся в листьях, но не используются для упорядочивания:
CREATE INDEX orders_user_idx ON orders(user_id)
INCLUDE (total, status);
-- Enables Index-Only Scan for queries that need just these columns.Когда НЕ следует создавать составные индексы
Если к столбцам обращаются независимо, два одностолбцовых индекса могут обслужить больше запросов. Составные индексы обменивают гибкость на принцип «в одном запросе есть всё».
Комбинация растровых индексов
PostgreSQL может объединять несколько одноколоночных индексов с помощью сканирования растрового индекса:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';
-- BitmapAnd of two index scans — works without a composite, but slower than the right composite.Следите за разрастанием индексов
Составные индексы шире. Более широкие индексы = файлы большего размера = меньшая часть индекса помещается в кэш. Планируйте их использование обдуманно.
Выбор порядка столбцов — практический пример
Запрос: «заказы одного пользователя за указанный период, отсортированные по дате».
-- Index design:
CREATE INDEX orders_user_created_idx
ON orders(user_id, created_at DESC);
-- Query (efficiently uses the index):
SELECT * FROM orders
WHERE user_id = 42
AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY created_at DESC LIMIT 10;Итоги
Составные индексы предназначены для фильтрации по нескольким столбцам и позволяют избежать этапов сортировки.
- Правило крайнего левого префикса
- Сначала столбцы с проверкой на равенство
- INCLUDE для столбцов, необходимых только для покрытия запроса
- Два отдельных индекса или один составной — зависит от набора запросов
Быстрая проверка
У вас есть индекс по (user_id, status, created_at). Какой запрос CANNOT эффективно его использовать?
Часто задаваемые вопросы
Урок «Составные индексы и порядок столбцов» бесплатный?
Да — полный текст урока «Составные индексы и порядок столбцов» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.
Чему я научусь в уроке «Составные индексы и порядок столбцов»?
Выбирайте порядок столбцов в составных индексах, разберитесь с правилом левого префикса и проектируйте индексы для нескольких запросов Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать SQL Academy?
Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 2 из 4.
Сколько времени занимает урок «Составные индексы и порядок столбцов»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке SQL Academy?
Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.
Все уроки этого курса
- Индексы B-tree, Hash, GiST и GIN
- Составные индексы и порядок столбцов
- Частичные индексы и индексы по выражениям
- Обслуживание индексов и раздувание