Как можно оптимизировать таблицу по двум полям с помощью индексов?

Оптимизация индексов по двум полям

Это очень частая задача. Нужно понимать, как работают составные индексы и когда их использовать.

Основная концепция: составной индекс

Для запросов по двум полям обычно нужен составной индекс (composite index):

-- Создать составной индекс
CREATE INDEX idx_user_status_created ON users(status, created_at);

Этот индекс эффективен для:

-- ✅ Работает идеально (оба поля в индексе)
SELECT * FROM users WHERE status = active AND created_at > 2024-01-01;

-- ✅ Работает (первое поле в индексе)
SELECT * FROM users WHERE status = active;

-- ❌ Неэффективно (пропущено первое поле)
SELECT * FROM users WHERE created_at > 2024-01-01;

Порядок полей в индексе — важен!

Для разных query паттернов нужны разные порядки:

-- Паттерн 1: Равенство + Диапазон
SELECT * FROM orders WHERE customer_id = 123 AND amount > 1000;

-- Оптимальный индекс:
CREATE INDEX idx_orders ON orders(customer_id, amount);

-- Паттерн 2: Два диапазона
SELECT * FROM transactions 
WHERE created_at > 2024-01-01 AND amount BETWEEN 100 AND 5000;

-- Оптимальный индекс:
CREATE INDEX idx_transactions ON transactions(created_at, amount);

Правило ESR (Equality, Sort, Range)

Порядок полей в индексе должен быть:

1. E — Equality (=) — поля с точным совпадением
2. S — Sort — поля для сортировки
3. R — Range (>, <, BETWEEN) — поля с диапазоном

-- Запрос:
SELECT * FROM users 
WHERE role = admin          -- Equality
AND department = IT         -- Equality
AND created_at > 2024-01-01 -- Range
ORDER BY created_at;          -- Sort

-- Оптимальный индекс:
CREATE INDEX idx_users ON users(role, department, created_at);

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

Пример 1: Поиск + Фильтр по статусу

# ORM запрос (SQLAlchemy)
from sqlalchemy import and_

users = session.query(User).filter(
    and_(
        User.status == active,
        User.created_at >= datetime(2024, 1, 1)
    )
).all()

-- SQL
SELECT * FROM users 
WHERE status = active AND created_at >= 2024-01-01;

-- Оптимальный индекс (Equality первым):
CREATE INDEX idx_users_status_created ON users(status, created_at);

Пример 2: Сортировка по одному полю с фильтром

# Получить последних активных пользователей
users = session.query(User).filter(
    User.status == active
).order_by(User.created_at.desc()).limit(10)

SELECT * FROM users 
WHERE status = active 
ORDER BY created_at DESC 
LIMIT 10;

-- Индекс (фильтр + сортировка)
CREATE INDEX idx_users_status_created_desc ON users(status, created_at DESC);

Пример 3: Два фильтра + Сортировка

orders = session.query(Order).filter(
    and_(
        Order.customer_id == customer_id,
        Order.status.in_([pending, shipped])
    )
).order_by(Order.created_at.desc())

SELECT * FROM orders 
WHERE customer_id = 123 
AND status IN (pending, shipped)
ORDER BY created_at DESC;

-- Индекс
CREATE INDEX idx_orders_customer_status_date 
ON orders(customer_id, status, created_at DESC);

Когда нужны разные индексы

-- Запрос 1: Фильтр по customer_id и status
SELECT * FROM orders WHERE customer_id = 123 AND status = active;
-- CREATE INDEX idx1 ON orders(customer_id, status);

-- Запрос 2: Фильтр по status и date
SELECT * FROM orders WHERE status = active AND created_at > 2024-01-01;
-- CREATE INDEX idx2 ON orders(status, created_at);

-- Если оба запроса важны, может потребоваться два индекса

Использование COVERING INDEX (Include)

Если в SELECT есть поля, которых нет в WHERE, можно использовать covering index:

-- Запрос
SELECT id, customer_id, amount FROM orders 
WHERE customer_id = 123 AND status = active;

-- Covering index (PostgreSQL 11+)
CREATE INDEX idx_orders_cover ON orders(customer_id, status) 
INCLUDE (amount);

Анализ индексов — EXPLAIN

from sqlalchemy import text

# Проверить план выполнения
query = text("""
    EXPLAIN ANALYZE
    SELECT * FROM users 
    WHERE status = active AND created_at > 2024-01-01
""")

result = session.execute(query)
for row in result:
    print(row)

Ищем в выводе:

• ✅ Index Scan — хорошо, индекс работает
• ❌ Seq Scan — плохо, сканируется вся таблица
• ❌ Index Cond отсутствует — индекс не используется

Миграция с Goose

-- migrations/0025_add_user_indexes.sql

-- UP
CREATE INDEX idx_users_status_created ON users(status, created_at);
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- DOWN (в секции после комментария)
-- +goose Down
DROP INDEX IF EXISTS idx_users_status_created;
DROP INDEX IF EXISTS idx_orders_customer_status;

Правила большого пальца

1. Составной индекс лучше, чем два отдельных (в большинстве случаев)
2. Порядок имеет значение — используй ESR
3. Не переусложняй — слишком много индексов замедляет INSERT/UPDATE
4. Используй EXPLAIN — никогда не угадывай
5. Помни о NULL — индексы обрабатывают NULL, но с нюансами
6. Избегай лишних индексов — они занимают место и замедляют мутации

Полезные запросы для аудита индексов

-- PostgreSQL: все индексы таблицы
SELECT indexname, indexdef FROM pg_indexes 
WHERE tablename = 'users';

-- MySQL: информация об индексах
SHOW INDEX FROM users;

-- Размер индексов
SELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;