Можно ли поставить индекс на каждую колонку?

Ответ

Короткий ответ: можно, но не нужно. Это распространённая ошибка начинающих разработчиков, которая снижает производительность вместо её улучшения.

Почему индексы на каждой колонке — плохая идея

1. Замедление INSERT/UPDATE/DELETE операций

Индекс — это отдельная структура данных (B-tree), которая должна обновляться при каждом изменении строки.

Без индекса:
INSERT → 1ms (обновляем только таблицу)

С индексом на 1 колонке:
INSERT → 2ms (обновляем таблицу + индекс)

С индексами на 10 колонках:
INSERT → 11ms (обновляем таблицу + 10 индексов)

Если приложение часто пишет данные, но редко читает — индексы замедлят это в несколько раз.

2. Утечка дискового пространства

Каждый индекс занимает место на диске. Если добавить индексы на все колонки:

Таблица users (10 колонок, 1 млн строк):
- Сама таблица: ~200 MB
- 1 индекс (обычно 30% размера таблицы): ~60 MB
- 10 индексов: ~600 MB (3x больше, чем сама таблица!)

При работе с диском это означает:

• Медленнее кэширование в памяти (буфер pool меньше)
• Больше дисковых операций
• Дольше создание бэкапов и восстановление после сбоя

3. Замедление планировщика запросов

Оптимизатор должен выбрать лучший индекс из 10 доступных. Он может выбрать неоптимальный или даже не использовать индекс.

-- С 10 индексами планировщик может запутаться
SELECT * FROM users 
WHERE email = 'user@example.com' 
AND age > 18 
AND status = 'active'
AND country = 'US';

-- Какой индекс использовать?
-- Индекс на email? age? status? country?
-- Или комбинация нескольких?

4. Проблемы с разработкой

• Сложнее поддерживать схему БД
• Миграции становятся медленнее
• Трудно понять, какие индексы действительно используются
• Новичкам сложнее разобраться

Когда индекс полезен

1. На колонках, по которым ищете

-- Есть частые запросы вроде этого?
SELECT * FROM users WHERE email = ?;

-- Тогда да, поставьте индекс на email
CREATE INDEX idx_users_email ON users(email);

2. На колонках, по которым фильтруете

SELECT * FROM orders WHERE status = 'pending' AND created_at > ?;

-- Индексы помогут
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

3. На колонках, по которым сортируете

SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;

-- Индекс ускорит сортировку
CREATE INDEX idx_posts_created_desc ON posts(created_at DESC);

4. На внешних ключах

// JPA автоматически не создает индексы на FK
// Но его стоит добавить вручную
@Entity
public class Comment {
    @ManyToOne
    @Index(name = "idx_comment_post") // явно указываем индекс
    private Post post;
}

5. На UNIQUE колонках

-- UNIQUE ограничение автоматически создает индекс
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255) UNIQUE -- индекс создается автоматически
);

Как правильно добавлять индексы

Шаг 1: Анализируйте медленные запросы

-- В PostgreSQL включите EXPLAIN
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM users WHERE email = 'user@example.com';

-- Результат: Seq Scan → медленно, нужен индекс
-- Результат: Index Scan → быстро

Шаг 2: Смотрите, какие колонки часто в WHERE

-- Логируйте медленные запросы
SET log_min_duration_statement = 1000; -- логируем запросы > 1 сек

-- Потом анализируйте паттерны
-- Если 80% запросов ищут по (status, created_at) →
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

Шаг 3: Измеряйте эффект

// В Spring Data JPA используйте @Query с нормальным SQL
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    // Перед добавлением индекса:
    @Query(value = "SELECT * FROM users WHERE email = ?1", nativeQuery = true)
    User findByEmail(String email); // 500ms (Seq Scan)
    
    // После добавления индекса:
    // 5ms (Index Scan) — 100x ускорение!
}

Пример правильной схемы для типичного приложения

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255) UNIQUE, -- автоматический индекс
    username VARCHAR(100) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    
    -- Индексы только на часто используемых колонках
    INDEX idx_users_email (email),
    INDEX idx_users_created_at (created_at),
    INDEX idx_users_username (username)
    -- ОК, 3 индекса на 6 колонок
);

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status VARCHAR(50) NOT NULL,
    amount DECIMAL(10, 2),
    created_at TIMESTAMP NOT NULL,
    
    FOREIGN KEY (user_id) REFERENCES users(id),
    
    -- Индексы на часто используемые комбинации
    INDEX idx_orders_user_id (user_id), -- для JOIN
    INDEX idx_orders_status (status), -- для WHERE status = ?
    INDEX idx_orders_created_at (created_at), -- для ORDER BY
    INDEX idx_orders_status_created (status, created_at) -- композитный индекс
);

Composite Indexes (композитные индексы)

Это важная концепция:

-- Плохо: 2 отдельных индекса
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Хорошо: 1 композитный индекс
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

-- Почему? Эффективнее для запроса:
SELECT * FROM orders WHERE status = ? AND created_at > ?;

-- Порядок колонок в индексе важен!
-- Лучше: (status, created_at) если часто ищете по обоим
-- Плохо: (created_at, status) — status будет использован, но не эффективно

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

1. Начните БЕЗ индексов (кроме PRIMARY KEY и UNIQUE)
2. Найдите медленные запросы (EXPLAIN, логи)
3. Добавьте индексы на самые медленные (измеряйте эффект)
4. Контролируйте количество (обычно 3-5 индексов на таблицу достаточно)
5. Регулярно проверяйте (может быть неиспользуемые индексы)

Как найти неиспользуемые индексы

-- PostgreSQL
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0; -- индекс никогда не использовался

-- Удалите неиспользуемые индексы
DROP INDEX CONCURRENTLY idx_unused_index;

Вывод

• Не ставьте индексы на все колонки просто так
• Индекс помогает чтению, но замедляет запись
• Добавляйте индексы основываясь на анализе реальных запросов
• Измеряйте эффект (EXPLAIN)
• Регулярно чистите неиспользуемые индексы
• 3-5 хороших индексов лучше, чем 20 случайных

Главное правило: Премьер оптимизация — враг (Donald Knuth). Сначала напишите код, потом оптимизируйте только те места, которые действительно медленные.