При создании PRIMARY KEY или UNIQUE автоматически создается B-Tree индекс.

Пример:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE
);

Это означает, что:

• PRIMARY KEY (id) создаст индекс для быстрого поиска по id.
• UNIQUE (email) создаст индекс, предотвращающий дублирование.

Когда полезно:

• id часто используется в JOIN.
• Поиск по email выполняется часто.

Внешние ключи (FOREIGN KEY) сами по себе не создают индексы автоматически (например, в PostgreSQL). Если по ним выполняются JOIN, индексация обязательна.

Пример:

CREATE INDEX idx_orders_user_id ON orders(user_id);

Теперь JOIN users ON orders.user_id = users.id работает быстрее.

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

• Если внешний ключ участвует в JOIN.
• Если часто используется в WHERE.

Индексация помогает, если запрос содержит WHERE, ORDER BY, GROUP BY.

Пример:

CREATE INDEX idx_users_lastname ON users(last_name);

Теперь WHERE last_name = 'Smith' и ORDER BY last_name работают быстрее.

• Редко используемые индексы. Если столбец редко используется в запросах, индекс только занимает место.
• Малые таблицы. Если таблица содержит менее 1000 строк, полный перебор (Seq Scan) быстрее индекса.
• Частые INSERT, UPDATE, DELETE. Индексы замедляют вставку и обновление. Например, если есть 5 индексов, каждое обновление должно изменять их все.

Пример проблемы:

UPDATE users SET email = 'new@example.com' WHERE id = 100;

Если email проиндексирован, обновление требует изменения индекса.

Решение:

• Избегать индексов на часто изменяемых столбцах.
• Использовать частичные индексы (ограничение индексации по WHERE).

Пример частичного индекса:

CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

Теперь индекс применяется только к активным пользователям.

Индексы создаются с помощью CREATE INDEX.

• Пример обычного B-Tree индекса:

CREATE INDEX idx_users_lastname ON users(last_name);

• Создание уникального индекса:

CREATE UNIQUE INDEX idx_users_email ON users(email);

• Создание многоколонного индекса:

CREATE INDEX idx_orders_date_status ON orders(order_date, status);

Такой индекс ускоряет:

• WHERE order_date = '2024-01-01' AND status = 'shipped'
• ORDER BY order_date, status
• Но не ускоряет поиск только по status.

Если индекс больше не нужен, его можно удалить:

DROP INDEX idx_users_lastname;

Или при необходимости переименовать:

ALTER INDEX idx_users_lastname RENAME TO idx_users_lname;

Некоторые СУБД создают индексы автоматически:

• PostgreSQL – создает индексы для PRIMARY KEY и UNIQUE, но не для FOREIGN KEY.
• MySQL (InnoDB) – автоматически индексирует PRIMARY KEY, UNIQUE и FOREIGN KEY.
• SQLite – индексирует PRIMARY KEY и UNIQUE.

Проверить индексы можно с помощью (пример для PostgreSQL):

SELECT * FROM pg_indexes WHERE tablename = 'users';

Обычный индекс хранит только ключевые столбцы. Покрывающий индекс позволяет добавить дополнительные данные, ускоряя SELECT.

CREATE INDEX idx_users_email_include ON users(email) INCLUDE (name, created_at);

Теперь SELECT name, created_at FROM users WHERE email = 'user@example.com' не требует обращения к таблице.

Индексировать только часть данных – полезно для больших таблиц.

CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

Теперь индекс применяется только к активным пользователям.

Перед оптимизацией важно понять, использует ли запрос индексы. Для этого применяется EXPLAIN:

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

Результат покажет, будет ли использован Index Scan или Seq Scan (полный перебор).

Для детального анализа с измерением времени выполнения добавляют ANALYZE:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

Это выполняет запрос и показывает реальные затраты.

Если используется Seq Scan – индекс не применяется, возможные причины:

• Нет индекса на поле в WHERE.
• Фильтрация возвращает слишком много строк (оптимальнее полный перебор).
• Условия запроса не соответствуют структуре индекса.

Если используется Index Scan или Index Only Scan – индекс задействован:

• Index Scan – индекс используется, но данные запрашиваются из таблицы.
• Index Only Scan – индекс полностью покрывает запрос (работает быстрее).

• Селективность индекса. Индекс эффективен, если он отбрасывает большую часть данных. Если индексируется поле с небольшим количеством уникальных значений (например, is_active = true/false), индекс может не дать прироста.

• Правильный порядок полей в составных индексах. При использовании индекса на нескольких столбцах (CREATE INDEX idx_orders ON orders(status, created_at)) запрос WHERE status = 'shipped' AND created_at > '2024-01-01' будет эффективным, но WHERE created_at > '2024-01-01' индекс не использует.

• Удалять неиспользуемые индексы – можно проверить статистику индексного доступа:

SELECT * FROM pg_stat_user_indexes WHERE schemaname = 'public';

• Использовать VACUUM ANALYZE (PostgreSQL) – обновляет статистику планировщика запросов:

VACUUM ANALYZE;

• Не индексировать столбцы с низкой селективностью – индекс на gender (м/ж) бесполезен.

• Использовать частичные индексы – уменьшает размер индекса, ускоряя работу.

• Перестраивать индексы (REINDEX) – удаляет фрагментацию, улучшая производительность:

REINDEX TABLE users;