Что может произойти при добавлении индекса в таблицу, состоящую из миллиона записей

Добавление индекса в большую таблицу: последствия и нюансы

Это классический вопрос на собеседованиях, который проверяет понимание взаимодействия между Java приложением и базой данных. Добавление индекса на таблицу с миллионом записей имеет множество последствий, как положительных, так и отрицательных.

Позитивные эффекты

1. Ускорение поиска

Главное преимущество — значительное улучшение скорости SELECT запросов. Для таблицы с миллионом записей разница колоссальна:

// Без индекса: O(n) — полный scan таблицы
SELECT * FROM users WHERE email = user@example.com;
// ~500k проверок в среднем (half table scan)

// С индексом на email: O(log n) — B-tree search
SELECT * FROM users WHERE email = user@example.com;
// ~20 проверок (для сбалансированного B-tree)

2. Оптимизация JOIN операций

Индексы на внешние ключи критичны для производительности JOIN:

// Пример: JOIN с индексом быстрее в 100+ раз
SELECT u.name, o.total 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.status = completed;

// Индекс на orders.user_id делает этот запрос эффективным

Отрицательные эффекты

1. Замедление операций записи (INSERT, UPDATE, DELETE)

Это критичный момент! При каждой вставке/обновлении/удалении база должна обновить сам индекс:

// Вставка одной строки:
INSERT INTO users (id, name, email, created_at) 
VALUES (1000001, John, john@example.com, NOW());

// За кулисами база выполняет:
// 1. Вставляет строку в основную таблицу
// 2. Обновляет индекс на email (перестраивает B-tree узлы)
// 3. Обновляет индекс на created_at
// 4. Обновляет индекс на id (если primary key)
// = 3-4x медленнее, чем без индексов

В Java приложении это означает:

public class UserRepository {
    public void saveUser(User user) {
        // Без индексов: ~1ms
        // С индексами: ~3-5ms
        // С индексами на большой таблице: ~10-50ms
        userMapper.insert(user);
    }
    
    public void bulkInsert(List<User> users) {
        // Batch insert замедляется пропорционально количеству индексов
        for (User user : users) {
            userMapper.insert(user);
        }
    }
}

2. Увеличение размера памяти БД

Индекс занимает дополнительное место:

• Индекс на VARCHAR(255) колонке может занять 20-30% от размера таблицы
• Для миллиона записей это может быть сотни МБ

Таблица users: 500 MB (основные данные)
Индекс на email: 150 MB
Индекс на created_at: 80 MB
Индекс на phone: 120 MB
──────────────────────────
Общий размер БД: 850 MB вместо 500 MB (+70%)

3. Замедление операций обслуживания БД

• Vacuum/Analyze в PostgreSQL: дольше анализировать индексы
• Backup/Restore: больше данных для резервного копирования
• Репликация: медленнее передавать изменения на реплики

Проблемы с памятью и кэшем

1. Конкуренция за буфер-кэш

База хранит в памяти (buffer pool):

• Страницы основной таблицы
• Страницы индексов

Для таблицы с миллионом записей:

Buffer Pool размер: 4 GB (типично)

Без индексов:
- Таблица занимает: 2 GB (50% pool)
- Остаток для кэша запросов: 2 GB

С индексами:
- Таблица: 2 GB
- Индекс 1: 400 MB
- Индекс 2: 300 MB
- Индекс 3: 200 MB
- Остаток: 700 MB (слишком мало!)

Результат: частые page faults, обращение на диск → замедление всех операций.

2. TLB miss и CPU cache thrashing

При полном таблице сканированию с множеством индексов процессор тратит ресурсы на переключение между разными областями памяти.

Практические последствия для Java приложения

public class PerformanceMetrics {
    // Scenario: Таблица с 1M записей, 3 индекса
    
    public void demonstrateImpact() {
        // SELECT запросы: ускорение в 50-100x
        long selectStart = System.nanoTime();
        findUserByEmail("user@example.com");
        long selectTime = System.nanoTime() - selectStart;
        // Ожидаем: 0.1-0.5ms (с индексом vs 50-100ms без)
        
        // INSERT операции: замедление в 3-10x
        long insertStart = System.nanoTime();
        saveNewUser(new User());
        long insertTime = System.nanoTime() - insertStart;
        // Ожидаем: 10-50ms (с индексом vs 1-5ms без)
        
        // Batch операции: критичны!
        long batchStart = System.nanoTime();
        for (int i = 0; i < 10000; i++) {
            saveNewUser(new User());
        }
        long batchTime = System.nanoTime() - batchStart;
        // 100-500 seconds vs 10-50 seconds
    }
}

Блокировки и concurrency

При добавлении индекса база часто держит lock на таблицу (в зависимости от БД):

// PostgreSQL: CREATE INDEX CONCURRENTLY — не блокирует
// MySQL: ONLINE DDL — минимальные блокировки
// Oracle: В фоне, но может быть затратно

// Для Java приложения:
public void upgradeDatabase() throws SQLException {
    // Все SELECT/INSERT/UPDATE/DELETE зависают во время индексации
    // В продакшене это может привести к отказу в обслуживании!
    try (Connection conn = getConnection()) {
        conn.createStatement()
            .execute("CREATE INDEX CONCURRENTLY idx_email ON users(email)");
    }
    // 10 минут блокировки = 10 минут downtime!
}

Когда добавлять индекс?

Добавляй, если:

• Таблица часто читается (SELECT в 10+ раз чаще, чем INSERT/UPDATE)
• Есть фильтры по этой колонке в WHERE/JOIN
• Данные отсортированы по этой колонке в ORDER BY

Не добавляй, если:

• Таблица часто пишется (high-throughput INSERT/UPDATE)
• Колонка редко используется в WHERE
• Место на диске критично

Итоговый вывод

Добавление индекса на таблицу с миллионом записей — это компромисс между чтением и написанием. На собеседовании важно продемонстрировать:

1. Понимание B-tree структуры
2. Знание O(log n) vs O(n) сложности
3. Осознание проблем с concurrency и памятью
4. Умение анализировать query plans
5. Практический опыт настройки БД