Индексы: зачем они и как не навредить

Индексы: зачем они и как не навредить

Индекс - это как оглавление в книге. Можно искать главу по оглавлению за секунды, а можно листать 500 страниц и чувствовать жизнь.

Открываешь книгу на 800 страниц. Тебе нужно одно слово. Вариант первый: листать всё подряд, страницу за страницей, пока не наткнёшься. Вариант второй: открыть алфавитный указатель в конце - слово, номер страницы, готово. Секунда.

База данных работает точно так же. Пишешь WHERE email = '...', а индекса на email нет - и база честно листает всю таблицу. От начала до конца. Все два миллиона строк. Каждый раз.

Проверить просто: напиши перед запросом EXPLAIN. Увидел Seq Scan на большой таблице - вот он, твой вечер с листанием 800 страниц. Как читать такие планы целиком - в уроке EXPLAIN.

Что ускоряет индекс

Обычно индексы ускоряют:

• поиск по WHERE
• соединения по ключам (JOIN ON)
• сортировку ORDER BY (иногда)
• уникальность (UNIQUE)

Как работает индекс: B-tree

По умолчанию PostgreSQL использует B-tree индекс (родственник бинарного дерева). Мысли о нём так:

• Всё отсортировано и организовано в дерево
• БД может быстро сказать: «вот эти записи точно не нужны»
• Дальше ищет только в нужных листьях дерева

Не вникай в детали, но знай: это быстро для большого диапазона значений. Хеш-таблица (та самая, что внутри map) находит значение по точному ключу ещё быстрее, но не умеет в диапазоны и сортировку - поэтому в базах по умолчанию именно B-tree. Указатель в книге полезен ровно по той же причине - он отсортирован по алфавиту. Свали слова в кучу, и листать придётся уже сам указатель.

Создание индекса

-- Простой индекс
CREATE INDEX idx_users_email ON users(email);

-- С индивидуальным именем (good practice: idx_tablename_column)
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- На нескольких столбцах (composite index)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Для быстрого поиска в частичном диапазоне
CREATE INDEX idx_users_created_at ON users(created_at DESC);

Composite индексы - порядок имеет значение

Когда ты создаёшь индекс на (user_id, status), БД сортирует данные сначала по user_id, потом по status внутри каждого user_id.

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Этот запрос ПОЛНОСТЬЮ использует индекс (быстро):
SELECT * FROM orders WHERE user_id = 42 AND status = 'completed';

-- Этот тоже (по первому столбцу):
SELECT * FROM orders WHERE user_id = 42;

-- А этот НЕ использует индекс полностью:
SELECT * FROM orders WHERE status = 'completed'; - status стоит вторым!

Правило: первыми в индексе ставь столбцы, которые используются в WHERE чаще всего. Указатель тоже отсортирован сначала по первой букве - искать в нём по второй букве бессмысленно.

UNIQUE индекс vs UNIQUE constraint

UNIQUE гарантирует, что значения уникальны. Это уже территория ограничений целостности, просто под капотом всё равно живёт индекс:

-- Через индекс
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Через constraint (часто удобнее)
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE(email);

-- Результат одинаков, но constraint более явный в схеме

Когда НЕ делать индекс

• Булевы столбцы (is_active, is_deleted) - обычно 50/50 распределение, индекс не поможет
• Очень маленькие таблицы (< 1000 строк) - полный скан быстрее, чем поиск по индексу
• Низкая селективность (много повторений) - индекс может быть медленнее

-- Нет смысла:
CREATE INDEX idx_users_is_active ON users(is_active); - half/half распределение

-- Зато полезно:
CREATE INDEX idx_orders_user_id ON orders(user_id); - few users, many orders

Как посмотреть существующие индексы

PostgreSQL:

-- Все индексы в текущей БД
\di

-- Индексы конкретной таблицы
\d orders

-- Или через запрос
SELECT indexname FROM pg_indexes WHERE tablename = 'users';

SQLite:

.indices users

Когда индекс почти обязателен

• Внешние ключи (orders.user_id) - нужен для быстрого JOIN
• Уникальные поля (email, username) - автоматически создаётся UNIQUE INDEX
• Частые фильтры в WHERE - индекс по полям, по которым часто ищешь
• Большие таблицы (> 100k строк) - индексы становятся критичны

Если даже с индексом запрос остаётся тяжёлым (сложная агрегация, десятки миллионов строк), следующий шаг - не двадцатый индекс, а кеш перед базой.

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

-- Таблица заказов (миллионы строк)
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id INT NOT NULL,
    status VARCHAR(20),
    total DECIMAL,
    created_at TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Индексы:
-- На внешний ключ (автоматически для FK, но явно ускоряет JOIN)
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- На комбинацию для популярного WHERE
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- На дату для сортировки и фильтрации
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Запросы, которые эти индексы ускорят:
SELECT * FROM orders WHERE user_id = 42; - idx_orders_user_id
SELECT * FROM orders WHERE user_id = 42 AND status = 'completed'; - idx_orders_user_status
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10; - idx_orders_created_at

Мини-задание

• Посмотри существующие индексы в своей БД (\di в PostgreSQL)
• Подумай, какие поля в твоей схеме ты часто ищешь в WHERE
• Создай composite индекс на два часто используемых вместе столбца
• Проверь, что индекс создан (через \d tablename)