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

Стратегии массового обновления миллионов записей в MySQL

Массовое обновление миллионов записей — классическая задача, требующая баланса между производительностью и доступностью БД. Никогда не выполняйте UPDATE table SET column = value WHERE condition на миллионах строк одним запросом — это заблокирует таблицу и вызовет отказ в обслуживании.

Ключевые принципы безопасного обновления

1. Разбиение на пачки (Batch processing) — обрабатывать данные порциями по 1000-10000 записей
2. Минимизация блокировок — использовать условия, не блокирующие всю таблицу
3. Контроль нагрузки — добавлять паузы между пачками
4. Мониторинг — отслеживать влияние на производительность в реальном времени
5. Резервное копирование — всегда иметь бекап перед массовыми изменениями

Практическая реализация на PHP

<?php
class BatchUpdater
{
    private $db;
    private $batchSize = 5000;
    private $delayMicroseconds = 100000; // 0.1 секунда
    
    public function __construct(PDO $db)
    {
        $this->db = $db;
        $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    
    public function updateUsersStatus(string $newStatus, array $conditions): void
    {
        $offset = 0;
        $totalUpdated = 0;
        
        while (true) {
            // Используем LIMIT с OFFSET для пагинации
            $query = "SELECT id FROM users WHERE " . 
                     implode(' AND ', $conditions) . 
                     " LIMIT :limit OFFSET :offset";
            
            $stmt = $this->db->prepare($query);
            $stmt->bindValue(':limit', $this->batchSize, PDO::PARAM_INT);
            $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
            $stmt->execute();
            
            $ids = $stmt->fetchAll(PDO::FETCH_COLUMN);
            
            if (empty($ids)) {
                break;
            }
            
            // Обновляем пачку записей
            $this->updateBatch($ids, $newStatus);
            
            $totalUpdated += count($ids);
            $offset += $this->batchSize;
            
            echo "Обработано: $totalUpdated записей\n";
            
            // Даем БД "передохнуть"
            usleep($this->delayMicroseconds);
            
            // Периодически коммитим для освобождения блокировок
            if ($totalUpdated % 50000 === 0) {
                $this->db->commit();
            }
        }
        
        echo "Обновлено всего: $totalUpdated записей\n";
    }
    
    private function updateBatch(array $ids, string $newStatus): void
    {
        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        
        $query = "UPDATE users SET status = ?, updated_at = NOW() 
                  WHERE id IN ($placeholders)";
        
        $stmt = $this->db->prepare($query);
        $stmt->execute(array_merge([$newStatus], $ids));
    }
}

// Использование
$db = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
$updater = new BatchUpdater($db);
$updater->updateUsersStatus('active', ['is_verified = 1']);
?>

Продвинутые техники оптимизации

Использование временных таблиц

-- Создаем временную таблицу с ID для обновления
CREATE TEMPORARY TABLE batch_ids (id INT PRIMARY KEY);
INSERT INTO batch_ids 
SELECT id FROM users WHERE condition LIMIT 10000;

-- Обновляем основную таблицу через JOIN
UPDATE users u
JOIN batch_ids b ON u.id = b.id
SET u.status = 'new_value';

DROP TEMPORARY TABLE batch_ids;

Планирование через событийный механизм MySQL

DELIMITER //
CREATE EVENT batch_update_event
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
    UPDATE users 
    SET status = 'processed' 
    WHERE status = 'pending' 
    LIMIT 5000;
END //
DELIMITER ;

Критические рекомендации

1. Индексы — убедитесь, что условия WHERE используют индексы
2. Репликация — выполняйте на реплике, затем делайте переключение
3. Время выполнения — запускайте в часы минимальной нагрузки (ночью)
4. Логирование — ведите подробный лог прогресса
5. Откат — предусмотрите механизм отката изменений

Мониторинг во время выполнения

// Проверка нагрузки на БД
$load = sys_getloadavg();
if ($load[0] > 3.0) { // Высокая нагрузка
    usleep(500000); // Увеличиваем паузу
    $this->batchSize = max(1000, $this->batchSize / 2);
}

Альтернативные подходы

1. Инструменты ETL — Apache Spark, Talend для обработки вне БД
2. Встроенные процедуры MySQL — хранимые процедуры с курсорами
3. Веб-воркеры — распределение задачи между несколькими процессами
4. Фоновые очереди — RabbitMQ, Redis для асинхронной обработки

Важнейший принцип: начинайте с тестовой среды на полной копии данных. Сначала протестируйте на 1% данных, измерьте время и нагрузку, затем рассчитайте общее время выполнения и спланируйте окно для продакшена.