PrepBro
← Назад к вопросам

Как проиндексировать большое количество данных при добавлении их в таблицу без индексов

2.3 Middle🔥 61 комментариев
#Базы данных и SQL

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI22 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

# Как проиндексировать большое количество данных при добавлении их в таблицу без индексов

Этот вопрос о performance оптимизации при загрузке большого объема данных. Есть несколько стратегий в зависимости от сценария.

Сценарий 1: Таблица уже полна, нужно добавить индекс

Проблема

TABLE users: 100 миллионов записей
Попытка создать индекс:
CREATE INDEX idx_email ON users(email);

Результат: таблица блокируется на часы, никто не может писать в БД!

✅ Решение 1: Создание индекса CONCURRENTLY (PostgreSQL)

-- Не блокирует таблицу, но медленнее
CREATE INDEX CONCURRENTLY idx_email ON users(email);

-- Проверить прогресс
SELECT * FROM pg_stat_progress_create_index;

✅ Решение 2: Online DDL (MySQL 8.0+)

-- ALTER TABLE с ALGORITHM=INPLACE не требует перестроения таблицы
ALTER TABLE users ADD INDEX idx_email (email), 
    ALGORITHM=INPLACE, LOCK=NONE;

-- Проверить статус
SHOW PROCESSLIST WHERE command LIKE '%ALTER%';

❌ Решение 3: Offline (если время позволяет)

-- Стандартный способ, блокирует таблицу
CREATE INDEX idx_email ON users(email);

-- Требует downtime
-- Используй только ночью или на слабо нагруженной БД

Сценарий 2: Массивная загрузка данных в пустую таблицу

❌ НЕПРАВИЛЬНО: Сначала индексы, потом данные

-- Таблица создана с индексами
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    amount DECIMAL(10, 2),
    created_at TIMESTAMP
);

CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_created_at ON orders(created_at);
CREATE INDEX idx_user_created ON orders(user_id, created_at);

-- Теперь загружаем 100M записей
LOAD DATA INFILE 'orders.csv'...

-- ПРОБЛЕМА: каждый INSERT обновляет ВСЕ индексы!
-- Это займёт часы

✅ ПРАВИЛЬНО: Сначала данные, потом индексы

-- 1. Создаём таблицу БЕЗ индексов (только PRIMARY KEY)
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    amount DECIMAL(10, 2),
    created_at TIMESTAMP
);

-- 2. Быстро загружаем данные
LOAD DATA INFILE 'orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ','
IGNORE 1 ROWS;
-- За 5 минут вместо 5 часов!

-- 3. ПОТОМ создаём индексы
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_created_at ON orders(created_at);
CREATE INDEX idx_user_created ON orders(user_id, created_at);
-- Индексирование 100M записей займёт час, но это асинхронно

Сценарий 3: Батч загрузка через приложение (Java)

❌ МЕДЛЕННО: INSERT по одному

@Service
public class OrderImportService {
    
    @Autowired
    private OrderRepository orderRepository;
    
    public void importOrders(List<Order> orders) {
        // ❌ 100 миллионов INSERT'ов по отдельности
        for (Order order : orders) {
            orderRepository.save(order);  // 1 SQL запрос = 100M запросов!
        }
        // Это займёт дни
    }
}

✅ БЫСТРЕЕ: Batch INSERT

@Service
public class OrderImportService {
    
    @Autowired
    private OrderRepository orderRepository;
    
    @Transactional
    public void importOrders(List<Order> orders) {
        // ✅ Батч save
        orderRepository.saveAll(orders);
        // 100M записей в одной транзакции
        // INSERT с BATCH SIZE 1000
        // Это займёт минуты вместо дней
    }
}

Конфигурация BatchSize:

spring:
  jpa:
    hibernate:
      jdbc:
        batch_size: 1000  # Вставляем по 1000 записей
      order_inserts: true
      order_updates: true

# application.properties
spring.jpa.properties.hibernate.jdbc.batch_size=1000
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true

✅ ЕЩЕ БЫСТРЕЕ: Прямой SQL BATCH

@Service
public class FastOrderImportService {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public void importOrdersFast(List<Order> orders) {
        String sql = "INSERT INTO orders(id, user_id, amount, created_at) VALUES (?, ?, ?, ?)";
        
        List<Object[]> batchArgs = new ArrayList<>();
        for (Order order : orders) {
            batchArgs.add(new Object[]{
                order.getId(),
                order.getUserId(),
                order.getAmount(),
                order.getCreatedAt()
            });
        }
        
        // Батч размер 5000 строк
        int[] result = jdbcTemplate.batchUpdate(sql, batchArgs);
        
        // За 2 минуты вместо часа с Hibernate!
    }
}

✅ МАКСИМАЛЬНО БЫСТРО: BULK LOAD утилиты

@Service
public class BulkLoadService {
    
    public void bulkLoadOrders(List<Order> orders) throws IOException {
        // 1. Создаём CSV файл
        File csvFile = createCsvFile(orders);
        
        // 2. Используем MySQL LOAD DATA INFILE
        String sql = "LOAD DATA LOCAL INFILE '" + csvFile.getAbsolutePath() + "' " +
                     "INTO TABLE orders " +
                     "FIELDS TERMINATED BY ',' " +
                     "LINES TERMINATED BY '\\n' " +
                     "(id, user_id, amount, created_at)";
        
        jdbcTemplate.execute(sql);
        
        // За 30 секунд вместо 2 минут!
    }
    
    private File createCsvFile(List<Order> orders) throws IOException {
        File csvFile = new File("/tmp/orders.csv");
        try (PrintWriter writer = new PrintWriter(csvFile)) {
            for (Order order : orders) {
                writer.println(order.getId() + "," +
                              order.getUserId() + "," +
                              order.getAmount() + "," +
                              order.getCreatedAt());
            }
        }
        return csvFile;
    }
}

Сценарий 4: Инкрементальная загрузка с индексами

Проблема: Индексы замедляют INSERT

✅ Решение: Временно отключить индексы

PostgreSQL:

-- Отключить триггеры и индексы
ALTER TABLE orders DISABLE TRIGGER ALL;

-- Загружаем данные
INSERT INTO orders (...) SELECT ...

-- Включить триггеры
ALTER TABLE orders ENABLE TRIGGER ALL;

-- Пересчитать индексы
REINDEX TABLE orders;

MySQL:

-- Отключить foreign key проверки
SET FOREIGN_KEY_CHECKS = 0;

INSERT INTO orders (...) VALUES (...);

SET FOREIGN_KEY_CHECKS = 1;

Сценарий 5: Продакшн сценарий (БД в продакшене)

Правильный подход

@Service
public class SafeImportService {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public void importDataSafely(List<Order> orders) {
        // 1. Начинаем в downtime окно (ночь)
        LocalDateTime startTime = LocalDateTime.now();
        
        // 2. Временно отключаем write операции (read-only mode)
        jdbcTemplate.execute("SET GLOBAL read_only = ON");
        
        try {
            // 3. Отключаем индексы
            jdbcTemplate.execute("ALTER TABLE orders DISABLE KEYS");
            
            // 4. Быстро загружаем
            bulkInsert(orders);
            
            // 5. Включаем индексы
            jdbcTemplate.execute("ALTER TABLE orders ENABLE KEYS");
            
            // 6. Проверяем целостность
            validateData();
            
            // 7. Пересчитываем статистику
            jdbcTemplate.execute("ANALYZE TABLE orders");
            
        } finally {
            // 8. Включаем write операции
            jdbcTemplate.execute("SET GLOBAL read_only = OFF");
        }
        
        LocalDateTime endTime = LocalDateTime.now();
        log.info("Import completed in " + Duration.between(startTime, endTime).getSeconds() + " seconds");
    }
}

Сравнение скоростей (100M записей)

Метод                    | Время  | Примечание
─────────────────────────────────────────────────────
INSERT по одному         | 24ч   | Совсем медленно
Batch INSERT (1000 rows) | 2-3ч  | Нормально
JDBC Batch (5000 rows)   | 30мин | Хорошо
LOAD DATA INFILE         | 5мин  | Отлично
LOAD DATA + no indexes   | 1мин  | Лучшее

Best Practices

  1. Всегда загружай данные БЕЗ индексов (если это новая таблица)
  2. Используй BATCH INSERT для приложений (batch_size = 1000-5000)
  3. LOAD DATA INFILE для массивов файлов CSV
  4. Создавай индексы ПОСЛЕ загрузки данных
  5. Проверяй целостность данных после импорта
  6. Мониторь размер индексов с помощью EXPLAIN
  7. Планируй downtime для больших операций

Монитор процесса

-- PostgreSQL
SELECT * FROM pg_stat_progress_create_index;
SELECT query, rows_processed FROM pg_stat_progress_copy;

-- MySQL
SHOW PROCESSLIST;
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;