Java Interview Questions

Главная / Все ответы / Базы данных SQL PostgreSQL

Вопрос 3 · Раздел 1

Что такое составной индекс?

Составной индекс сортирует данные иерархически:

Russian Базы данных SQL PostgreSQL Исходный Markdown
Версии по языкам: English Russian Ukrainian

🟢 Junior Level

Составной (композитный) индекс — это индекс, созданный на нескольких колонках одновременно.

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

Простая аналогия: Представьте телефонную книгу, где контакты отсортированы сначала по фамилии, потом по имени. Сначала вы ищёте нужную фамилию, а внутри неё — нужное имя.

Основная идея:

  • Один индекс может ускорить поиск по нескольким полям
  • Порядок колонок в индексе критически важен
  • Работает правило левого префикса

Пример:

-- Создаём составной индекс
CREATE INDEX idx_users_city_age ON users(city, age);

-- ✅ Будет использован (по city)
SELECT * FROM users WHERE city = 'Moscow';

-- ✅ Будет использован (по city и age)
SELECT * FROM users WHERE city = 'Moscow' AND age = 25;

-- ❌ НЕ будет использован (только age)
SELECT * FROM users WHERE age = 25;

Когда использовать:

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

🟡 Middle Level

Как это работает

Составной индекс сортирует данные иерархически:

  1. Сначала по первой колонке
  2. Внутри одинаковых значений первой — по второй
  3. И так далее

Правило левого префикса: Индекс (A, B, C) может использоваться для поиска по:

  • ✅ A
  • ✅ A, B
  • ✅ A, B, C
  • ❌ B (без A)
  • ❌ C (без A и B)
  • ❌ B, C (без A)

Золотые правила порядка колонок

1. Равенство перед диапазоном (= before >)

-- Запрос: WHERE city = 'Moscow' AND age > 25

-- ✅ Правильный порядок
CREATE INDEX idx_city_age ON users(city, age);

-- ❌ Неправильный порядок (age как диапазон сделает city бесполезным)
CREATE INDEX idx_age_city ON users(age, city);

2. Сортировка (ORDER BY)

-- Индекс покрывает ORDER BY в том же порядке
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- ✅ Использует индекс
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC;

-- ❌ Не использует индекс для сортировки
SELECT * FROM orders ORDER BY created_at DESC; -- нет фильтра по user_id

Типичные ошибки

  1. Неправильный порядок колонок 
    -- ❌ Если city используется с '=', а age с '>', city должен быть первым
CREATE INDEX idx_age_city ON users(age, city); -- Неправильно
  2. Дублирование индексов 
    -- ❌ Индекс (A, B) уже покрывает (A)
CREATE INDEX idx_a ON users(a);           -- Лишний
CREATE INDEX idx_a_b ON users(a, b);      -- Покрывает оба случая
  3. Слишком много колонок
    • PostgreSQL поддерживает до 32 колонок в индексе
    • На практике: более 5 колонок — красный флаг
    • Большой индекс → нужно больше RAM для удержания в кэше → если RAM не хватает → Cache Misses → Random I/O → падение производительности

Сравнение с отдельными индексами

Запрос Индекс (A) + (B) Индекс (A, B)
WHERE A = 1 ✅ Использует (A) ✅ Использует (A,B)
WHERE B = 2 ✅ Использует (B) ❌ Не использует
WHERE A = 1 AND B = 2 ⚠️ Bitmap или один из них ✅ Полностью использует

Практический пример

-- Типичный e-commerce сценарий
CREATE INDEX idx_orders_customer_date ON orders(customer_id, created_at DESC);

-- Запрос 1: Все заказы клиента
SELECT * FROM orders WHERE customer_id = 123;
-- Plan: Index Scan (по customer_id)

-- Запрос 2: Последние заказы клиента
SELECT * FROM orders 
WHERE customer_id = 123 
ORDER BY created_at DESC 
LIMIT 10;
-- Plan: Index Scan + Limit (очень быстро!)

🔴 Senior Level

Internal Implementation

Лексикографическая сортировка: Ключ составного индекса — это фактически одна большая строка. В B-tree хранится конкатенация значений колонок.

Структура ключа в Leaf Node:

┌─────────────────────────────────────────┐
│ City="Moscow" | Age=25 | TID=(123,456)  │
├─────────────────────────────────────────┤
│ City="Moscow" | Age=26 | TID=(123,457)  │
├─────────────────────────────────────────┤
│ City="SPb"     | Age=20 | TID=(124,100) │
└─────────────────────────────────────────┘

Сжатие префиксов: PostgreSQL может сжимать ключи в Internal узлах, храня только часть, необходимую для навигации.

Архитектурные Trade-offs

Правило порядка колонок:

[Equi-columns] → [Range-column] → [Include-columns]
     (WHERE =)      (WHERE >,<)     (SELECT only)

Пример:

-- Запрос: WHERE status = 'ACTIVE' AND city = 'Moscow' AND created_at > '2024-01-01'
-- ORDER BY created_at DESC

-- ✅ Оптимальный индекс
CREATE INDEX idx_orders_optimal 
ON orders(status, city, created_at DESC);

-- status (=) → city (=) → created_at (>) → идеальный порядок

Смешанные направления (PG 13+):

-- Для ORDER BY a ASC, b DESC
CREATE INDEX idx_mixed ON table_name(a ASC, b DESC);

-- Обычный индекс (ASC, ASC) НЕ покроет такой ORDER BY

На PG 12 и ниже смешанные направления (ASC + DESC) в одном индексе не оптимизируются корректно — индекс будет отсортирован в одном направлении. Решение: отдельный индекс под конкретный ORDER BY или дополнительная сортировка в запросе.

Эмуляция Index Skip Scan

PostgreSQL не имеет встроенного Skip Scan (в отличие от Oracle/MySQL 8.0). Но можно эмулировать:

-- Проблема: индекс (status, user_id), но ищем только по user_id
-- status имеет мало уникальных значений (ACTIVE, INACTIVE, DELETED)

-- ✅ Recursive CTE эмуляция Skip Scan
WITH RECURSIVE skip_scan AS (
  (SELECT MIN(status) AS status FROM orders)
  UNION ALL
  SELECT (SELECT MIN(status) FROM orders WHERE status > s.status)
  FROM skip_scan s WHERE s.status IS NOT NULL
)
SELECT * FROM orders o
JOIN skip_scan s ON o.status = s.status
WHERE o.user_id = 123;

Производительность: На таблице с 100 млн записей и 3 уникальными status:

  • Без Skip Scan: Seq Scan ~30 секунд
  • С эмуляцией: ~50 миллисекунд (разница в 600 раз!)

Edge Cases

  1. NULL в составном индексе 
    -- NULL сортируется последним по умолчанию (NULLS LAST)
CREATE INDEX idx_a_b ON t(a, b);
   
-- WHERE a IS NULL → ищем в конце индекса
-- WHERE a = 1 AND b IS NULL → ищем в конце группы a=1
  2. Оператор класса (OpClass) 
    -- OpClass (Operator Class) — набор правил, определяющий, как значения колонки
-- сравниваются и сортируются в индексе. По умолчанию для text используется
-- обычное посимвольное сравнение, но можно использовать, например,
-- сравнение по хэшу или без учёта регистра
CREATE INDEX idx_name ON users(name COLLATE "C");

-- Для специфичных операций
CREATE INDEX idx_tags ON posts USING gin(tags);
  3. Index-Only Scan и Visibility Map
    • Даже если все колонки есть в индексе, PG может пойти в Heap
    • Причина: Страницы не помечены в Visibility Map
    • Решение: VACUUM обновляет Visibility Map

Производительность

Влияние на запись:

  • Каждая колонка в индексе увеличивает размер ключа
  • Большой ключ → меньше записей на страницу → больше уровней дерева
  • Для 100 млн записей:
    • Индекс (int): ~2 ГБ
    • Индекс (int, text(50)): ~8 ГБ
    • Индекс (int, text(50), text(100)): ~15 ГБ

Консолидация индексов:

-- ❌ Было (3 индекса, 15 ГБ суммарно)
CREATE INDEX idx_a ON t(a);           -- 2 ГБ
CREATE INDEX idx_a_b ON t(a, b);      -- 5 ГБ
CREATE INDEX idx_a_b_c ON t(a, b, c); -- 8 ГБ

-- ✅ Стало (1 индекс, 8 ГБ)
CREATE INDEX idx_a_b_c ON t(a, b, c); -- Покрывает все префиксы!

Production Experience

Реальный сценарий:

  • SaaS платформа: 200 млн записей заказов
  • Проблема: 12 отдельных индексов, 80 ГБ суммарно
  • Симптом: INSERT > 500ms, RAM не хватает, Cache Hit rate < 60%
  • Анализ через pg_stat_user_indexes:
    • 8 индексов имеют idx_scan < 10 за месяц
    • 4 индекса дублируют префиксы
  • Решение:
    • Consolidate до 3 составных индексов
    • Добавили INCLUDE для Index-Only Scan горячих запросов
    • Результат: 15 ГБ индексов, INSERT < 50ms, Cache Hit > 95%

Monitoring

-- Проверка использования составных индексов
SELECT 
    indexrelname,
    idx_scan,
    idx_tup_read,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;

-- Проверка порядка колонок
SELECT 
    i.relname as table_name,
    ix.indexrelid::regclass as index_name,
    array_agg(a.attname ORDER BY k.n) as columns
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indrelid
JOIN pg_class idx ON idx.oid = ix.indexrelid
JOIN generate_series(0, array_length(ix.indkey, 1)-1) as k(n) ON true
JOIN pg_attribute a ON a.attrelid = i.oid AND a.attnum = ix.indkey[k.n]
WHERE i.relname = 'orders'
GROUP BY i.relname, ix.indexrelid;

-- Проверка дубликатов префиксов
-- (нужно расширение pg_stat_statements или кастомный запрос к pg_index)

Best Practices

  1. Порядок колонок: = → диапазоны → INCLUDE
  2. Консолидация: Один индекс (a,b,c) заменяет (a) и (a,b)
  3. Ограничьте размер: Максимум 3-5 колонок в индексе
  4. INCLUDE: Для колонок только в SELECT используйте INCLUDE
  5. Смешанные направления: Создавайте индексы под конкретные ORDER BY
  6. Мониторинг: Регулярно проверяйте pg_stat_user_indexes на неиспользуемые индексы

Резюме для Senior

  • Составной индекс — это лексикографически отсортированная строка-ключ
  • Порядок колонок критичен: Equi → Range → Include
  • Эмулируйте Skip Scan через Recursive CTE для низкокардинальных первых колонок
  • Консолидируйте индексы для экономии RAM и ускорения записи
  • Visibility Map влияет на Index-Only Scan — следите за VACUUM
  • Всегда проверяйте план через EXPLAIN (ANALYZE, BUFFERS)

🎯 Шпаргалка для интервью

Обязательно знать:

  • Составной индекс сортирует данные иерархически: 1-я колонка → 2-я → …
  • Правило левого префикса: (A, B, C) работает для A, A,B, A,B,C но НЕ для B, C
  • Порядок колонок: = (equality) → диапазоны (>, <) → INCLUDE (только SELECT)
  • Консолидация: (a), (a,b), (a,b,c) → один (a,b,c) экономит место
  • PostgreSQL не имеет Skip Scan → эмуляция через Recursive CTE
  • Максимум 3-5 колонок на практике (PG поддерживает до 32)
  • Смешанные направления: (a ASC, b DESC) — PG 13+

Частые уточняющие вопросы:

  • «Почему WHERE age = 25 не использует индекс (city, age)?» → Левый префикс: city первый
  • «Как заменить 3 отдельных индекса одним?» → Составной (a,b,c) покрывает все префиксы
  • «Что такое Skip Scan и почему PG его не имеет?» → Пропуск первой колонки; в PG нет, эмулируем CTE
  • «Как ORDER BY влияет на порядок колонок?» → Индекс должен совпадать с ORDER BY

Красные флаги (НЕ говорить):

  • ❌ «Порядок колонок не важен» (критически важен!)
  • ❌ «Создам отдельный индекс на каждое поле» (консолидируйте!)
  • ❌ «32 колонки в индексе — нормальная практика» (максимум 3-5 на практике)

Связанные темы:

  • [[Для чего нужны индексы]] → когда создавать индексы
  • [[Как работает B-tree индекс]] → внутренняя структура
  • [[Что такое кардинальность индекса]] → порядок колонок по кардинальности
  • [[Какие недостатки индексов]] → стоимость записи