Куда стоит применять индексы?

Куда стоит применять индексы в базе данных

Индексы — это структуры данных для ускорения поиска. Но не все колонки нужно индексировать. Это вопрос о производительности и оптимизации БД.

Основное правило

Индекс нужен там, где часто выполняется поиск, сортировка или фильтрация.

1. Столбцы в WHERE условиях

Это самый важный случай:

-- Часто выполняемый запрос
SELECT * FROM users WHERE email = 'john@example.com';
-- CREATE INDEX idx_users_email ON users(email);

-- Без индекса: O(n) — проверяет все 1M строк
-- С индексом: O(log n) — находит в миллисекундах

2. Столбцы в JOIN условиях

// Запрос: получить все заказы пользователя
List<Order> getOrdersByUser(Long userId) {
    // SELECT * FROM orders WHERE user_id = ?
    // CREATE INDEX idx_orders_user_id ON orders(user_id);
}

SELECT * FROM orders o
JOIN users u ON o.user_id = u.id  -- индекс на user_id в orders
WHERE u.id = 42;

Без индекса на user_id в таблице orders JOIN будет очень медленным для больших таблиц.

3. Столбцы в ORDER BY

public class ArticleRepository {
    // SELECT * FROM articles ORDER BY created_at DESC LIMIT 10
    // CREATE INDEX idx_articles_created_at ON articles(created_at DESC);
    public List<Article> getLatestArticles() { ... }
}

Без индекса БД должна отсортировать все строки. С индексом — готовый отсортированный список.

4. Столбцы в GROUP BY

-- Подсчет заказов по статусам
SELECT status, COUNT(*) FROM orders GROUP BY status;
-- CREATE INDEX idx_orders_status ON orders(status);

5. PRIMARY KEY и UNIQUE колонки

Это автоматические индексы:

@Entity
public class User {
    @Id  // PRIMARY KEY — автоматически индексирован
    private Long id;
    
    @Column(unique = true)  // UNIQUE — автоматически индексирован
    private String email;
}

6. FOREIGN KEY колонки

@Entity
public class Order {
    @ManyToOne
    @JoinColumn(name = "user_id")  // Хороший кандидат для индекса
    private User user;
}

CREATE INDEX idx_orders_user_id ON orders(user_id);

При JOIN или фильтрации по user_id индекс даст огромный прирост производительности.

Практический пример: интернет-магазин

-- Таблица заказов
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,              -- Автоиндекс
    user_id BIGINT NOT NULL,            -- ИНДЕКС (часто ищем заказы пользователя)
    status VARCHAR(50),                 -- ИНДЕКС (часто фильтруем по статусу)
    created_at TIMESTAMP,               -- ИНДЕКС (часто сортируем по дате)
    total_amount DECIMAL(10,2)          -- Не нужен индекс
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Составной индекс для типичного запроса
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Когда НЕ нужны индексы

1. Редко используемые столбцы

public class User {
    private Long id;          // Индекс: нужен
    private String email;     // Индекс: нужен (поиск по email)
    private String phone;     // Индекс: может быть не нужен (редко ищут)
    private String address;   // Индекс: НЕ нужен (не ищут отдельно)
}

2. Столбцы с низкой селективностью

-- Таблица 1M пользователей, статус: active/inactive
-- CREATE INDEX idx_users_active ON users(is_active);
-- ПЛОХО! Индекс вернет 950K строк, всё равно нужно читать диск

SELECT * FROM users WHERE is_active = true;
-- Без индекса: scan 1M строк
-- С индексом: scan 1M строк через индекс (примерно одинаково)

3. Столбцы, часто обновляемые

@Entity
public class Analytics {
    private Long id;
    private Long pageViews;  // Часто обновляется
    private Long uniqueVisitors;  // Часто обновляется
}

// CREATE INDEX idx_pageviews ON analytics(pageViews);
// ПЛОХО! Индекс обновляется при каждом UPDATE

Составные индексы (Composite Index)

Важно: Порядок имеет значение!

-- Хороший индекс для типичного запроса
CREATE INDEX idx_orders_user_date 
ON orders(user_id, created_at DESC);

-- Эффективен для:
SELECT * FROM orders 
WHERE user_id = 42 
ORDER BY created_at DESC;

-- НЕЭФФЕКТИВЕН для:
SELECT * FROM orders 
WHERE created_at > '2024-01-01'
ORDER BY user_id;  -- user_id не первый в индексе

Проблема: слишком много индексов

@Entity
public class Product {
    // Каждый @Column с @Index замедлит INSERT и UPDATE
    
    @Column(index = true)  // ✅ Нужен
    private Long categoryId;
    
    @Column(index = true)  // ✅ Нужен
    private String sku;
    
    @Column(index = true)  // ❌ Излишний
    private String description;
    
    @Column(index = true)  // ❌ Излишний
    private String warehouse_location;
}

Цена индекса:

• SELECT: ускорение (+ 10x)
• INSERT: замедление (+ 5-10%)
• UPDATE: замедление (+ 5-10%)
• DELETE: замедление (+ 5-10%)
• Дисковое пространство: +20-30% на каждый индекс

Как определить, нужен ли индекс

public class IndexDecisionMatrix {
    public boolean shouldCreateIndex(
            String columnName,
            int selectivityPercent,  // % уникальных значений
            int queryFrequency) {    // запросов в секунду
        
        // ✅ НУЖЕН индекс
        if (selectivityPercent > 50 && queryFrequency > 100) {
            return true;
        }
        
        // ✅ НУЖЕН индекс
        if (usedInWhereClause(columnName) || usedInOrderBy(columnName)) {
            return true;
        }
        
        // ❌ НЕ НУЖЕН
        if (selectivityPercent < 10) {
            return false;  // Слишком много дубликатов
        }
        
        // ❌ НЕ НУЖЕН
        if (columnNeverQueried(columnName)) {
            return false;
        }
        
        return false;  // Сомнения? Не создавай, измеряй
    }
}

Инструменты анализа

-- PostgreSQL: EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

-- Если видишь "Sequential Scan" — нужен индекс
-- Если видишь "Index Scan" — индекс уже работает

-- MySQL: EXPLAIN
EXPLAIN
SELECT * FROM orders WHERE user_id = 42;

Чек-лист для индексов

• PRIMARY KEY и UNIQUE — автоматические
• FOREIGN KEY — критические для JOIN
• WHERE условия — высокий приоритет
• ORDER BY / GROUP BY — средний приоритет
• Низкая селективность (< 10%) — НЕ индексировать
• Часто обновляемые столбцы — исключить
• Составные индексы — упорядочить по приоритету
• EXPLAIN ANALYZE — всегда проверить

Золотое правило: Индексируй то, что часто ищется, не индексируй то, что часто обновляется.