Съставен индекс: индекс по няколко колони в SQL

Проблем

Последните десет

Страницата „Наскоро разглеждани“ показва последните 10 посещения на клиента, от най-новото. Индексът по customer_id вече е направен. Виж плана:

SQLбаза: магазин

EXPLAIN QUERY PLAN
SELECT seen_at, product_id FROM visits
WHERE customer_id = 1234
ORDER BY seen_at DESC LIMIT 10;

Индексът намира 60-те посещения, но не ги дава подредени по дата. Базата ги сортира сама (USE TEMP B-TREE). При клиент с хиляди посещения това е най-бавната част, а от всичките трябват само 10.

Идеята

Две колони в един индекс

Индексът може да е по няколко колони: CREATE INDEX име ON таблица(колона1, колона2). Това е индекс по няколко колони (composite index). Подреден е като телефонен указател: първо по фамилия, а при еднаква фамилия по име.

Индексът по (customer_id, seen_at) е подреден първо по клиент, а посещенията на един клиент са подредени по дата. Базата намира клиента и чете датите му отзад напред. Сортиране не трябва:

SQLбаза: магазин

EXPLAIN QUERY PLAN
SELECT seen_at, product_id FROM visits
WHERE customer_id = 1234
ORDER BY seen_at DESC LIMIT 10;

Същият индекс помага и когато условието е по двете колони. Базата отива на клиента и в неговата част търси от датата нататък:

SQLбаза: магазин

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM visits
WHERE customer_id = 1234 AND seen_at >= '2026-01-01';

И само по първата колона, все едно е индекс само по customer_id:

SQLбаза: магазин

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM visits WHERE customer_id = 1234;

Пробвай

Посещенията за период

Отделът за обслужване често пита колко пъти клиентът е гледал продукти от дадена дата нататък. Направи индекс само по тези две колони, с който заявката е SEARCH и по клиента, и по датата.

SQLбаза: магазин

SELECT COUNT(*) FROM visits
WHERE customer_id = 1234 AND seen_at >= '2026-01-01';

Решение

CREATE INDEX idx_visits_customer_seen ON visits(customer_id, seen_at);

Виж го

Отляво надясно

Със същия индекс отчетът по дни пита само за датата:

SQLбаза: магазин

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM visits WHERE seen_at = '2026-03-01';

Планът е SCAN. Пише USING COVERING INDEX, защото COUNT(*) се смята от индекса, но базата пак чете всичките 300 000 записа в него. Пусни самата заявка и виж времето:

SQLбаза: магазин

SELECT COUNT(*) FROM visits WHERE seen_at = '2026-03-01';

В телефонния указател не можеш да намериш всички с име „Мария“, без да прочетеш целия указател. Имената са подредени само вътре във всяка фамилия.

Това е правилото отляво надясно: индекс по (a, b) помага за условия по a, по a и b, и за ORDER BY b, когато a е дадено с =. За условие само по b не помага. Ако не си сигурен, провери с EXPLAIN QUERY PLAN.

Идеята

Цената на индекса

Индексите не са безплатни. Всеки нов ред трябва да се впише на правилното място във всеки индекс на таблицата. Двете полета добавят по 100 000 посещения. Първото е без индекси, второто с четири. Пусни ги и сравни времената:

SQLбаза: магазин

INSERT INTO visits (customer_id, product_id, seen_at)
SELECT customer_id, product_id, seen_at FROM visits WHERE id <= 100000;
SELECT COUNT(*) FROM visits;

SQLбаза: магазин

INSERT INTO visits (customer_id, product_id, seen_at)
SELECT customer_id, product_id, seen_at FROM visits WHERE id <= 100000;
SELECT COUNT(*) FROM visits;

Същото е при UPDATE на индексирана колона и при DELETE.

Индексите заемат и място. SQLite пази базата на страници от по 4 096 байта, а pragma_page_count() казва колко са страниците. Ето базата без индекси и с четирите:

SQLбаза: магазин

SELECT page_count FROM pragma_page_count();

SQLбаза: магазин

SELECT page_count FROM pragma_page_count();

С четирите индекса базата е над три пъти по-голяма. Затова се слагат индекси за заявките, които наистина имаш, а не за всяка колона „за всеки случай“.

По-точно, за любопитните

Вторият индекс от четирите, (customer_id, seen_at), прави първия, (customer_id), излишен: по правилото отляво надясно той върши и неговата работа. Такива двойки се срещат често и рядко се забелязват. PostgreSQL пази колко пъти е ползван всеки индекс (pg_stat_user_indexes), така че неползваните могат да се намерят.

Упражнение

Две задачи

1. В базата има индекс по (customer_id) и индекс по (customer_id, seen_at). Махни излишния.

Махни индекса, чиято работа върши другият:

SQLбаза: магазин

SELECT name FROM sqlite_master WHERE type = 'index';

Решение

DROP INDEX idx_visits_customer;

2. Индексът по (customer_id, seen_at) е направен. Покажи датите на последните 3 посещения на клиент 1234, от най-новото.

Последните 3 дати на посещение на клиент 1234:

SQLбаза: магазин

SELECT seen_at FROM visits
WHERE customer_id = 1234;

Решение

SELECT seen_at FROM visits
WHERE customer_id = 1234
ORDER BY seen_at DESC LIMIT 3;

Тест

Провери се

1. Има индекс по (customer_id, seen_at). Кое условие НЕ може да го ползва за търсене?WHERE customer_id = 7WHERE seen_at = '2026-03-01'WHERE customer_id = 7 AND seen_at >= '2026-01-01'

2. Има индекс по (customer_id, seen_at). Трябва ли отделен индекс само по customer_id?Да, иначе търсенето по клиент е бавноНе: търсенето по първата колона ползва индекса по дветеСамо ако таблицата е голяма

3. Защо индексите забавят INSERT?Не го забавятЗащото всеки нов ред се вписва на правилното място във всеки индекс на таблицатаЗащото базата проверява синтаксиса им

4. Кога е добре да има индекс по колона?Само за първичния ключКогато заявките често търсят, свързват или подреждат по неяВинаги, за всяка колона

Индекс по няколко колони

Бисквитки