Может ли индекс ухудшить производительность БД?

Индексы: когда помощник становится врагом

Прямой ответ: ДА, индекс может ухудшить производительность. Это контринтуитивно, но очень реально.

Основные затраты индексов

1. Стоимость INSERT/UPDATE/DELETE

-- Таблица без индекса
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255),
    name VARCHAR(255)
);

-- INSERT: 1 операция - добавляем в таблицу
INSERT INTO users VALUES (1, 'john@example.com', 'John');

-- Если добавим 5 индексов
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_created_at ON users(created_at);
CREATE INDEX idx_status_email ON users(status, email);
CREATE INDEX idx_last_login ON users(last_login_date);

-- INSERT: 6 операций (1 в таблицу + 5 в индексы)
-- UPDATE: 6 операций
-- DELETE: 6 операций

В цифрах:

• без индексов: INSERT = 100 мкс
• с 5 индексами: INSERT = 500+ мкс (в 5 раз медленнее!)

2. Стоимость памяти

-- Таблица users с 10 млн записей
SELECT pg_size_pretty(pg_total_relation_size('users'));
-- Результат: ~500 MB

-- Добавляем индексы
CREATE INDEX idx1 ON users(email);
CREATE INDEX idx2 ON users(name);
CREATE INDEX idx3 ON users(created_at);
CREATE INDEX idx4 ON users(status);

SELECT pg_size_pretty(pg_total_relation_size('users'));
-- Результат: ~2 GB (из-за индексов!)

-- Теперь все данные не влезают в RAM
-- Начинаются диск-обращения
-- Производительность падает

Проблема 1: Индекс полностью не используется

-- Создали индекс
CREATE INDEX idx_status_email ON users(status, email);

-- Но query его не использует
SELECT * FROM users WHERE email = 'john@example.com';
-- Или
SELECT * FROM users WHERE email LIKE '%john%';
-- Или
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

-- Результат:
-- - Индекс занимает память
-- - Замедляет INSERT/UPDATE/DELETE
-- - Не помогает query

// Как это выглядит в приложении
public class UserRepository {
    
    // ❌ Это не использует индекс (нет сортировки по status)
    public List<User> findByEmail(String email) {
        String sql = "SELECT * FROM users WHERE email = ?";
        // Индекс idx_status_email не используется
        return jdbcTemplate.query(sql, new UserRowMapper(), email);
    }
    
    // ❌ LIKE с % в начале не использует индекс
    public List<User> findByEmailContains(String pattern) {
        String sql = "SELECT * FROM users WHERE email LIKE ?";
        return jdbcTemplate.query(sql, new UserRowMapper(), "%" + pattern + "%");
    }
    
    // ✅ Это использует индекс idx_status_email
    public List<User> findByStatusAndEmail(String status, String email) {
        String sql = "SELECT * FROM users WHERE status = ? AND email = ?";
        return jdbcTemplate.query(sql, new UserRowMapper(), status, email);
    }
}

Проблема 2: Индекс ухудшает query из-за Type Casting

-- Индекс на email (VARCHAR)
CREATE INDEX idx_email ON users(email);

-- Но query приводит тип - индекс не используется
SELECT * FROM users WHERE email = 123;  -- Ищет 123 как строку

-- В Java:
public List<User> findByEmail(Long emailId) {  // Ошибка типа
    String sql = "SELECT * FROM users WHERE email = ?";
    return jdbcTemplate.query(sql, new UserRowMapper(), emailId);
}

-- Индекс не используется, query медленнее

Проблема 3: Index Bloat

-- Индекс на таблице с частыми UPDATE/DELETE
CREATE INDEX idx_status ON users(status);

-- Много операций
UPDATE users SET status = 'INACTIVE' WHERE last_login < '2020-01-01';
DELETE FROM users WHERE status = 'DELETED';

-- Индекс растет, становится фрагментированным
SELECT pg_size_pretty(pg_relation_size('idx_status'));
-- Результат: 500 MB (хотя было 50 MB)

-- Решение: REINDEX
REINDEX INDEX idx_status;  -- Медленно!

Проблема 4: Too Many Indexes

-- Query Planner должен выбрать лучший индекс
CREATE INDEX idx1 ON orders(customer_id);
CREATE INDEX idx2 ON orders(order_date);
CREATE INDEX idx3 ON orders(status);
CREATE INDEX idx4 ON orders(customer_id, order_date);
CREATE INDEX idx5 ON orders(status, order_date);
CREATE INDEX idx6 ON orders(customer_id, status);
CREATE INDEX idx7 ON orders(customer_id, status, order_date);

-- Query Planner тратит ЦПУ на выбор
-- Может выбрать неоптимальный индекс
-- Стоимость поддержки всех индексов растет

Практический пример: Медленное приложение из-за индексов

public class OrderService {
    private JdbcTemplate jdbc;
    
    // Вставляем 1000 заказов
    public void importOrders(List<Order> orders) {
        long start = System.currentTimeMillis();
        
        for (Order order : orders) {
            String sql = "INSERT INTO orders (customer_id, amount, status, " +
                        "created_at, updated_at, description) VALUES (?, ?, ?, ?, ?, ?)";
            jdbc.update(sql, 
                order.getCustomerId(),
                order.getAmount(),
                order.getStatus(),
                order.getCreatedAt(),
                order.getUpdatedAt(),
                order.getDescription()
            );
        }
        
        long elapsed = System.currentTimeMillis() - start;
        System.out.println("Imported in " + elapsed + "ms");  // ~10 сек без индексов
    }
}

// После добавления 8 индексов:
// Было: 10 сек
// Стало: 45 сек (в 4.5 раза медленнее!)

Проблема 5: Index Covering неправильно

-- Индекс
CREATE INDEX idx_user_status ON users(status);

-- Query требует больше полей
SELECT id, status, email, created_at, last_login 
FROM users 
WHERE status = 'ACTIVE';

-- Query Planner:
-- 1. Использует индекс -> получает user_id
-- 2. Идет в основную таблицу -> получает остальные поля
-- 3. 2 обращения вместо 1

-- Решение: Covering index
CREATE INDEX idx_user_status ON users(status, id, email, created_at, last_login);
-- Теперь все нужные поля в индексе

Когда индекс реально замедляет

-- Маленькая таблица (< 1000 строк)
CREATE TABLE config (id INT, key VARCHAR(100), value VARCHAR(255));
INSERT INTO config VALUES (1, 'key1', 'value1'); -- 500 строк всего

CREATE INDEX idx_key ON config(key);

-- Query
SELECT * FROM config WHERE key = 'key1';

-- PostgreSQL может выбрать Sequential Scan вместо индекса
-- Потому что индекс может быть медленнее для маленькой таблицы
-- Индекс занимает место, но не экономит время

Диагностика: EXPLAIN ANALYZE

-- Без индекса
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'john@example.com';
-- Seq Scan on users  (cost=0.00..450.00 rows=1) (actual time=0.523..450.123)

-- С индексом
CREATE INDEX idx_email ON users(email);
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'john@example.com';
-- Index Scan using idx_email on users  (cost=0.42..8.44 rows=1) (actual time=0.012..0.015)

-- С плохим индексом
EXPLAIN ANALYZE
SELECT * FROM users WHERE email LIKE '%john%';
-- Seq Scan on users  (cost=0.00..450.00 rows=500)  -- ИНДЕКС НЕ ИСПОЛЬЗУЕТСЯ

Как тестировать в Java

public class IndexPerformanceTest {
    
    @Test
    public void comparePerformanceWithoutIndex() {
        // Без индекса
        dropIndex();
        long start = System.nanoTime();
        for (int i = 0; i < 10000; i++) {
            userRepository.findByEmail("email" + i + "@example.com");
        }
        long timeWithoutIndex = System.nanoTime() - start;
        
        // С индексом
        createIndex();
        start = System.nanoTime();
        for (int i = 0; i < 10000; i++) {
            userRepository.findByEmail("email" + i + "@example.com");
        }
        long timeWithIndex = System.nanoTime() - start;
        
        System.out.println("Without index: " + timeWithoutIndex / 1_000_000 + "ms");
        System.out.println("With index: " + timeWithIndex / 1_000_000 + "ms");
        
        assertTrue(timeWithIndex < timeWithoutIndex);  // Index должен помочь
    }
}

Лучшие практики: как избежать проблем

-- ❌ Плохо: создавать индексы "для будущего"
CREATE INDEX idx1 ON users(id, email, name, status, created_at);
CREATE INDEX idx2 ON users(status, email);
CREATE INDEX idx3 ON users(created_at);
CREATE INDEX idx4 ON users(name);

-- ✅ Хорошо: индекс для конкретного query
-- 1. Найти медленные query (EXPLAIN ANALYZE, logs)
SLOW QUERY: SELECT * FROM users WHERE status = 'ACTIVE' AND created_at > '2024-01-01'

-- 2. Создать индекс для этого query
CREATE INDEX idx_status_created ON users(status, created_at);

-- 3. Проверить что помог
EXPLAIN ANALYZE SELECT * FROM users WHERE status = 'ACTIVE' AND created_at > '2024-01-01';

-- 4. Если не помог - удалить
DROP INDEX idx_status_created;

Правила создания индексов

✅ СОЗДАВАЙ индекс если:
- Query выполняется чаще чем INSERT/UPDATE на эту таблицу
- Таблица > 10000 строк
- Индекс покрывает реальный WHERE условие
- EXPLAIN показал Seq Scan вместо Index Scan

❌ НЕ СОЗДАВАЙ индекс если:
- Таблица маленькая (< 1000 строк)
- Столбец используется в LIKE '%pattern%'
- Уже есть индекс на первый столбец
- Много INSERT/UPDATE и мало SELECT
- Index bloat и не поддерживаешь его

Итоговый ответ

ДА, индекс может ухудшить производительность из-за:

1. Стоимости INSERT/UPDATE/DELETE - каждая операция обновляет индекс
2. Занимаемой памяти - индекс вытеснит данные из кэша
3. Неправильного использования - индекс может вообще не использоваться
4. Index bloat - со временем индекс фрагментируется
5. Выбора плана - Query planner может выбрать неоптимальный индекс

Правило: Создавай индекс ТОЛЬКО для проблемных query, потом проверь EXPLAIN ANALYZE, потом удали если не помог.