Приведу компактный пример: схема без индексов, несколько типичных запросов, затем какие индексы и почему их форматируют план выполнения.
1) Схема (без индексов, без PK/FK — чтобы действительно не было автоматических индекс-структур):
CREATE TABLE users (
id BIGINT,
name TEXT,
country TEXT
);
CREATE TABLE products (
id BIGINT,
name TEXT,
category TEXT,
stock INT
);
CREATE TABLE orders (
id BIGINT,
user_id BIGINT,
product_id BIGINT,
amount DECIMAL,
status VARCHAR(20),
created_at TIMESTAMP
);
Оценки объёмов (для наглядности): число пользователей $=10^6$, заказов $=10^7$, товаров $=10^4$.
2) Примеры часто выполняемых запросов (без индексов)
Q1: Выбрать последние 20 заказов пользователя:
SELECT * FROM orders WHERE user_id = 12345 ORDER BY created_at DESC LIMIT 20;
Q2: Суммарные продажи по товару за промежуток:
SELECT product_id, SUM(amount) FROM orders
WHERE created_at BETWEEN '2025-01-01' AND '2025-06-30'
GROUP BY product_id;
Q3: Список товаров с информацией о последних заказах (join + order/limit):
SELECT p.id, p.name, o.created_at FROM products p
JOIN orders o ON p.id = o.product_id
WHERE p.category = 'A'
ORDER BY o.created_at DESC
LIMIT 50;
3) План выполнения без индексов (поведение и причина)
- Q1: движок выполнит полное сканирование таблицы orders (sequential/ table scan), чтобы найти строки с user_id = 12345, затем сортировку по created_at и LIMIT. Стоимость ~ просмотреть $n=10^7$ строк → сложность примерно $O(n)$.
- Q2: при агрегации по product_id обычно делается полное сканирование orders + хеш-агрегация; снова скан $=n$.
- Q3: join products → orders: без индекса придётся сканировать всю orders и делать hash-join или nested-loop с полным сканом, плюс сортировка по created_at.
Итог: при больших объёмах каждый запрос читает миллионы строк, CPU и I/O большие.
4) Какие индексы создать и почему (и как это меняет план)
Примеры индексов (SQL):
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);
CREATE INDEX idx_orders_created_at ON orders(created_at);
CREATE INDEX idx_orders_product ON orders(product_id);
CREATE INDEX idx_products_category_id ON products(category, id);
Объяснения и эффект:
- idx_orders_user_created (композитный): покрывает Q1 — план сменится с sequential scan на индексный поиск (index scan / index range scan). Сначала движок найдёт в индексе все записи с user_id = $12345$ в порядке created_at DESC сразу — сортировка не нужна, LIMIT быстро выполняется. Количество прочитанных индексовых записей ~ число заказов пользователя $m$, обычно $m\ll n$. Эффект: чтение $O(\log n+m)$ вместо $O(n)$.
- idx_orders_created_at (одно-колоночный): полезен для диапазонных запросов по дате (Q2). План: index range scan по created_at, затем группировка. Если производительность агрегирования важна и диапазон небольшой, это спасает от полного скана.
- idx_orders_product (по product_id): ускоряет join products->orders и группировки по product_id; вместо хеш-join можно использовать nested-loop с index lookup или индексную агрегацию. Для Q2/ Q3 уменьшает число строк, которые нужно просматривать.
- idx_products_category_id: если сначала фильтруете по category, индекс по (category, id) позволяет быстро получить список product.id для категории и затем делать по ним быстрый lookup в orders (nested loop + index on orders.product_id). Также уменьшает размер внешнего набора для join.
5) Конкретные изменения в плане join/агрегатах
- Join без индекса: обычно hash join — нужно материализовать/сканировать всю orders (скан $=n$).
- С индексом по orders.product_id и/или composite по (user_id, product_id): план может перейти на nested-loop join, где внешний (меньший) набор продуктов $p$ для каждой строки делается index seek в orders — стоимость ~ $O(m\cdot \log n)$ с $m$ = число строк внешнего набора. Если внешний набор мал, это намного дешевле.
- Index-only scan (напр., в PostgreSQL): если индекс покрывает все выбираемые столбцы (или INCLUDE), СУБД может обслужить запрос только из индекса без доступа к таблице — существенное ускорение чтения.
6) Пример численного сравнения (оценка выигрыша)
Пусть orders $n=10^7$, у пользователя среднее число заказов $m=100$. Тогда:
- без индекса: читать $n=10^7$ строк.
- с индексом: читать индексные страницы ~ $O(\log n)+m$ ≈ константно + $100$ → выигрыш ~ $10^5$-$10^6$ раз по объёму прочитанных строк (приблизительно).
7) Дополнительные рекомендации и компромиссы
- Composite indexes полезны, если WHERE/ORDER BY используют несколько колонок; порядок колонок важен.
- Для частых точечных запросов — B-tree (по умолчанию) хорош; для полнотекст/GIN/BRIN — специальные случаи.
- Trade-off: индексы ускоряют чтение, замедляют вставки/обновления и занимают место. Правило: индексировать колонки с высокой селективностью и часто используемые в WHERE/JOIN/ORDER BY.
- Мониторьте планы (EXPLAIN/EXPLAIN ANALYZE) и статистику кардинальностей — оптимизатор выбирает по оценкам.
Краткий итог: создайте для приведённых запросов
- orders(user_id, created_at DESC) — для быстрых последних заказов;
- orders(created_at) или orders(product_id) — для диапазонов/агрегатов;
- products(category, id) — для фильтрации категорий + join.
Эти индексы поменяют план с полного сканирования таблицы на индексный поиск / index-range / index-only и часто заменят дорогие хеш-сканы на быстрые nested-loop с индексными обращениями, существенно снижая I/O.