Как изменить миллионы записей одним скриптом, чтобы не перегрузить базу данных?

Для массового обновления больших объемов данных без простоя БД используется стратегия пакетной обработки (batch processing) с контролем нагрузки.

Основной алгоритм (пакетное обновление):

$batchSize = 1000; // Размер пакета. Подбирается экспериментально (1000-10000).
$lastId = 0;
$connection->beginTransaction();

do {
    // 1. Выбираем порцию записей по первичному ключу (эффективнее OFFSET)
    $stmt = $connection->prepare(
        'SELECT id, some_field FROM huge_table WHERE id > :lastId ORDER BY id LIMIT :limit'
    );
    $stmt->bindValue(':lastId', $lastId, PDO::PARAM_INT);
    $stmt->bindValue(':limit', $batchSize, PDO::PARAM_INT);
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (empty($rows)) {
        break; // Все записи обработаны
    }

    // 2. Подготавливаем и выполняем пакетное обновление
    $updateStmt = $connection->prepare(
        'UPDATE huge_table SET calculated_field = :value WHERE id = :id'
    );
    foreach ($rows as $row) {
        $newValue = complexCalculation($row['some_field']); // Логика обновления
        $updateStmt->execute([':value' => $newValue, ':id' => $row['id']]);
        $lastId = $row['id']; // Запоминаем последний обработанный ID
    }

    // 3. Фиксируем транзакцию для каждого пакета
    $connection->commit();
    $connection->beginTransaction(); // Начинаем следующую

    // 4. Контроль нагрузки: пауза и прогресс
    usleep(100000); // 0.1 сек. Снижает нагрузку на реплики и дисковую подсистему.
    echo 'Processed up to ID: ' . $lastId . PHP_EOL;

} while (true);

$connection->commit();

Оптимизации и альтернативы:

• Временная таблица: Создать новую таблицу с обновленными данными, затем атомарно переименовать (RENAME TABLE). Это самое быстрое решение, если логика обновления сложная.
• Чанки по времени: Если в таблице есть created_at, обрабатывайте данные по дням/неделям.
• Отключение индексов и ограничений: Перед массовым UPDATE можно временно отключить вторичные индексы и FOREIGN KEY CHECKS, затем пересоздать.

  ALTER TABLE huge_table DISABLE KEYS;
  -- ... выполнение обновления ...
  ALTER TABLE huge_table ENABLE KEYS;

• Использование LOAD DATA INFILE: Если данные можно подготовить в файле, это самый быстрый способ импорта.
• Планировщик (pt-online-schema-change): Для структурных изменений используйте специализированные инструменты, которые создают триггеры для онлайн-миграции.

Обязательные шаги:

1. Резервное копирование перед запуском.
2. Запуск в период минимальной нагрузки (ночь).
3. Мониторинг репликации и нагрузки на БД (slow query log, CPU, I/O).