Частичный и составной индексы в реляционных базах данных

Частичный индекс (Partial Index / Filtered Index)

Что это такое: Частичный индекс — это индекс, который создается не для всех строк таблицы, а только для подмножества строк, которые удовлетворяют определенному условию (WHERE клауза).

Зачем нужен:

• Экономия места на диске: Индекс будет меньше, так как хранит только данные из части таблицы.
• Увеличение скорости записи/обновления: Поскольку индекс меньше, СУБД требуется меньше времени для его обновления при операциях INSERT, UPDATE, DELETE.
• Увеличение скорости чтения (иногда): Если запросы часто обращаются к данным, которые индексируются частичным индексом, он может быть очень эффективным.
• Оптимизация специфических запросов: Идеально подходит, когда у вас есть запросы, которые регулярно фильтруют данные по определенному условию, и только часть таблицы соответствует этому условию.

Пример: Предположим, у вас есть таблица orders (заказы), и большинство ваших запросов работают только с "активными" (active) или "невыполненными" (pending) заказами, в то время как "завершенных" (completed) заказов очень много, и они редко запрашиваются.

-- Таблица orders:
-- order_id (PK)
-- customer_id
-- order_date
-- status (текст: 'pending', 'active', 'completed', 'cancelled')
-- total_amount

Вместо того чтобы индексировать status для всех заказов, вы можете создать частичный индекс только для активных и ожидающих заказов:

CREATE INDEX idx_orders_active_status
ON orders (customer_id, order_date) -- Здесь можно указать несколько столбцов, это будет составной частичный индекс
WHERE status IN ('pending', 'active');

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

SELECT * FROM orders
WHERE status IN ('pending', 'active') AND customer_id = 123
ORDER BY order_date DESC;

База данных может использовать idx_orders_active_status, который будет намного меньше и быстрее, чем индекс по (customer_id, order_date) на всей таблице.

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

• Когда столбец имеет много NULL значений, и вы запрашиваете только не-NULL значения.
• Когда большинство данных имеет одно общее значение, и вы запрашиваете редкие значения (например, status = 'error').
• Когда у вас есть логически разделенные на части данные (например, "активные" и "архивные"), и вы в основном работаете только с одной частью.

---

Составной индекс (Composite Index / Concatenated Index)

Что это такое: Составной индекс — это индекс, который создается на двух или более столбцах одной таблицы. Порядок столбцов в составном индексе имеет значение.

Зачем нужен:

• Оптимизация запросов с несколькими условиями WHERE: Когда вы фильтруете или сортируете по нескольким столбцам.
• Покрытие запросов (Covering Index): Если все столбцы, необходимые для запроса (в SELECT и WHERE), включены в индекс, СУБД может получить все необходимые данные прямо из индекса, не обращаясь к таблице (это называется "index-only scan" или "покрывающий индекс"), что значительно ускоряет выполнение.
• Поддержка сортировки (ORDER BY): Если ORDER BY соответствует порядку столбцов в индексе, СУБД может использовать индекс для сортировки.

Порядок столбцов важен: СУБД использует столбцы в составном индексе слева направо. Индекс на (col1, col2, col3) может быть использован для запросов по:

• col1
• col1, col2
• col1, col2, col3
• Он не может быть использован только для col2 или col3 (если col1 не участвует в запросе), или для col2, col1 (из-за порядка).

Пример: Продолжим с таблицей orders. Вы часто запрашиваете заказы по customer_id и order_date:

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date DESC); -- Важно указывать порядок сортировки, если он используется в запросах

Теперь для запросов типа:

SELECT order_id, total_amount
FROM orders
WHERE customer_id = 456 AND order_date > '2023-01-01'
ORDER BY order_date DESC;

Индекс idx_orders_customer_date будет очень эффективным, так как он может:

1. Быстро найти строки для customer_id = 456.
2. Отфильтровать по order_date.
3. Предоставить данные уже отсортированными по order_date DESC.
4. Если бы order_id и total_amount были также включены в индекс (как "включенные столбцы" или "included columns" в некоторых СУБД, или просто как часть составного индекса), то запрос мог бы быть покрывающим.

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

• Когда вы регулярно фильтруете по двум или более столбцам в WHERE клаузе.
• Когда вы сортируете (ORDER BY) по одному или нескольким столбцам, которые также используются в WHERE или SELECT.
• Когда вы хотите создать покрывающий индекс для повышения производительности.

---

Основные отличия вкратце

Характеристика	Частичный индекс (Partial Index)	Составной индекс (Composite Index)
Основная цель	Индексировать часть строк таблицы.	Индексировать несколько столбцов для одной строки.
Условие WHERE	Обязательно содержит WHERE клаузу для определения подмножества строк, подлежащих индексации.	Может использоваться для оптимизации запросов с WHERE клаузами по нескольким столбцам.
Размер/Ресурсы	Обычно меньше и требует меньше ресурсов для обслуживания.	Может быть большим (если много столбцов или столбцы широкие), порядок столбцов важен.
Оптимизация запросов	Для запросов, которые фильтруют по тому же условию, что и в индексе.	Для запросов, которые фильтруют/сортируют по комбинации столбцов, в правильном порядке.
Идемпотентность	Сам по себе индекс не влияет на идемпотентность операции UPDATE, но повышает её эффективность.	Сам по себе индекс не влияет на идемпотентность операции UPDATE, но повышает её эффективность.

Часто эти концепции могут быть объединены. Например, вы можете создать частичный составной индекс (CREATE INDEX ... ON table (col1, col2) WHERE col3 = 'value'). Это будет очень мощный индекс, если ваши запросы часто соответствуют этому условию WHERE и используют col1 и col2 вместе.