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

# Оптимизация медленных INSERT запросов

Проблема

После добавления индексов твои INSERT запросы стали медленнее! Почему?

Использование индексов при INSERT:
Чтобы вставить строку, БД должна:
1. Вставить данные в основную таблицу → O(1)
2. Обновить КАЖДЫЙ индекс → O(log n) за индекс

Если индексов 5, то каждый INSERT требует обновить 5 B-Tree структур!

Так что индексы замедляют вставки, но ускоряют чтения. Это нормально и неизбежно.

Решение 1: Batch Insert (пакетная вставка)

Вместо вставки по одной строке, вставляй сразу 1000 строк:

@Service
public class UserService {
    @Autowired
    private UserRepository userRepository;
    
    // Плохо: N запросов
    public void insertUsersSlowly(List<User> users) {
        for (User user : users) {
            userRepository.save(user); // 1000 запросов
        }
    }
    
    // Хорошо: 1 запрос
    @Transactional
    public void insertUsersFast(List<User> users) {
        userRepository.saveAll(users); // Вставляет все за раз
    }
    
    // Лучше: в батчах по 100
    @Transactional
    public void insertUsersOptimal(List<User> users) {
        int batchSize = 100;
        for (int i = 0; i < users.size(); i += batchSize) {
            int end = Math.min(i + batchSize, users.size());
            userRepository.saveAll(users.subList(i, end));
            // Периодически очищаем session для экономии памяти
            entityManager.flush();
            entityManager.clear();
        }
    }
}

Результат:

• 1000 операций по одной: ~10 секунд
• 1000 операций в одном батче: ~0.5 секунд
• 1000 операций батчами по 100: ~1 секунд (но экономнее по памяти)

Решение 2: Использование JDBC вместо Hibernate

Для bulk-операций Hibernate неэффективен. Используй raw SQL:

@Service
public class UserBatchService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public void batchInsertUsers(List<User> users) {
        String sql = "INSERT INTO users (id, email, name, created_at) VALUES (?, ?, ?, ?)";
        
        // batchUpdate выполняет все вставки в одной транзакции
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                User user = users.get(i);
                ps.setString(1, user.getId());
                ps.setString(2, user.getEmail());
                ps.setString(3, user.getName());
                ps.setTimestamp(4, Timestamp.valueOf(LocalDateTime.now()));
            }
            
            @Override
            public int getBatchSize() {
                return users.size();
            }
        });
    }
}

Производительность:

• Hibernate saveAll(1000): ~2 секунды
• JdbcTemplate batchUpdate(1000): ~0.2 секунды

Решение 3: Отключи индексы во время bulk insert

Для очень больших объёмов (> 100K записей) отключи индексы:

@Service
public class LargeDataImportService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Transactional
    public void importManyUsers(List<User> users) {
        try {
            // Отключаем индексы (зависит от БД)
            jdbcTemplate.execute("ALTER TABLE users DISABLE TRIGGER ALL"); // PostgreSQL
            
            // Вставляем данные быстро
            batchInsertUsers(users);
            
        } finally {
            // Восстанавливаем индексы и перестраиваем их
            jdbcTemplate.execute("ALTER TABLE users ENABLE TRIGGER ALL");
            jdbcTemplate.execute("REINDEX TABLE users"); // Перестроить индексы
        }
    }
}

Результат:

• С индексами: 50 секунд
• Отключены индексы + REINDEX после: 5 секунд

Решение 4: Оптимизируй конфигурацию Hibernate

# application.properties

# Включи batch mode
spring.jpa.properties.hibernate.jdbc.batch_size=100
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true

# Убери статистику (она замедляет)
spring.jpa.properties.hibernate.generate_statistics=false

# Размер очередности записи
spring.jpa.properties.hibernate.jdbc.fetch_size=100

@Configuration
public class HibernateConfig {
    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        em.setPackagesToScan("com.example.domain");
        
        Properties props = new Properties();
        props.put("hibernate.jdbc.batch_size", "100");
        props.put("hibernate.order_inserts", "true");
        props.put("hibernate.order_updates", "true");
        props.put("hibernate.jdbc.use_scrollable_resultset", "true");
        em.setJpaProperties(props);
        
        return em;
    }
}

Решение 5: Выбери правильные индексы

Не все индексы равны полезны для INSERT:

-- Плохо: много индексов
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 ON users(status);
CREATE INDEX idx_email_status ON users(email, status); -- Еще один!

-- Хорошо: только нужные индексы
CREATE INDEX idx_email ON users(email);           -- Используется в WHERE
CREATE INDEX idx_email_status ON users(email, status); -- Используется в JOIN
-- idx_created_at не нужен, если не часто сортируешь по нему

Правило: каждый индекс замедляет INSERT на 5-10%. 5 индексов = до 50% замедления.

Решение 6: Используй UNLOGGED таблицы (PostgreSQL)

Для временных или быстро меняющихся данных:

-- UNLOGGED таблицы быстрее, но теряют данные при краше
CREATE UNLOGGED TABLE temp_users (
    id UUID PRIMARY KEY,
    email VARCHAR(255),
    data JSONB
);

После импорта скопируй в обычную таблицу:

@Service
public class ImportService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Transactional
    public void importData(List<User> users) {
        // 1. Вставляем в UNLOGGED таблицу (быстро)
        batchInsertToTemp(users);
        
        // 2. Копируем в обычную таблицу
        jdbcTemplate.update("INSERT INTO users SELECT * FROM temp_users");
        
        // 3. Очищаем
        jdbcTemplate.update("TRUNCATE temp_users");
    }
}

Полная стратегия оптимизации

@Service
public class OptimizedBulkInsertService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private EntityManager entityManager;
    
    @Transactional
    public void insertMillionUsers(List<User> users) {
        // Шаг 1: Отключи внешние ключи
        jdbcTemplate.execute("SET session_replication_role = replica");
        
        try {
            // Шаг 2: Вставляй батчами
            int batchSize = 500;
            for (int i = 0; i < users.size(); i += batchSize) {
                List<User> batch = users.subList(
                    i, 
                    Math.min(i + batchSize, users.size())
                );
                
                batchInsertUsersViaJdbc(batch);
                
                entityManager.flush();
                entityManager.clear();
            }
        } finally {
            // Шаг 3: Включи обратно и перестрой индексы
            jdbcTemplate.execute("SET session_replication_role = DEFAULT");
            jdbcTemplate.execute("REINDEX TABLE users");
        }
    }
    
    private void batchInsertUsersViaJdbc(List<User> users) {
        String sql = "INSERT INTO users (id, email, name) VALUES (?, ?, ?)";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, users.get(i).getId());
                ps.setString(2, users.get(i).getEmail());
                ps.setString(3, users.get(i).getName());
            }
            
            @Override
            public int getBatchSize() {
                return users.size();
            }
        });
    }
}

Чеклист оптимизации

1. ✅ Используй saveAll() или batchUpdate() вместо циклических save()
2. ✅ Батчи по 100-500 элементов (не все сразу)
3. ✅ Включи батч-режим в Hibernate конфиг
4. ✅ Перепроверь: все ли индексы нужны?
5. ✅ Используй JDBC для очень больших объёмов
6. ✅ Отключай индексы если вставляешь > 100K записей
7. ✅ REINDEX после отключения индексов
8. ✅ Мониторь с EXPLAIN ANALYZE

Сравнение производительности

Вывод: Индексы действительно замедляют INSERT, но это цена за быстрые SELECT. Оптимизируй через批处理 (batch processing) и JDBC, а не через удаление индексов.