Блог

Я пытаюсь обновить много строк (более 100 000) в своей базе данных, но это занимает некоторое время (более 10 минут и все еще не завершено). Мне интересно, является ли это намеренным поведением или что-то не так в моем коде. Чтобы предотвратить зависание базы данных во время выполнения обновления, мне сказали обновлять по одной строке за раз, не уверен, что это так, как это должно быть реализовано.

Я обновляю изображения в моей таблице песен, чтобы они были нулевыми, если эти песни воспроизводились в моей таблице плейлистов

 private function updateBlogSongs ($blog_id) {

        $db = Yii::app()->db;

        $affectedRows = 0;


        $sql = "SELECT *

                FROM `firstdatabase`.song s

                INNER JOIN `seconddatabase`.playlist p ON s.name LIKE p.song_name";


        $dataReader = $db->createCommand($sql)->query(); // Rows from the song table that were played in the given blog

        $row = $dataReader->read();


        while ($row != false) {

            $sql = "UPDATE `firstdatabase`.song s

                    SET s.image = NULL

                    WHERE s.song_id = " . $row['song_id'];


            $affectedRows  = $db->createCommand($sql)->execute();

            

            $row = $dataReader->read();

        }


        return $affectedRows;

    }
 

Редактировать: после прочтения комментария собаки я внес некоторые изменения:
При 500 000 строках в таблице композиций это займет около 10 минут, если я увеличу размер пакета до 10000 (для приведенного выше кода потребовалось 8 часов). При размере пакета 250 это занимает около 50 минут. Я выбрал 250, потому что выполнение запроса занимает около 1 секунды, а для выполнения с размером пакета 10000 требуется более 10 секунд (ограничение составляет 1 секунду). Я хотел бы сделать это быстрее, но не уверен, что еще нужно изменить

 $batchSize = 250;
        $lastSongID = 0;
        $rowIndex = 0;
        $affectedRows = 0;

        $sql = "SELECT max(song_id) FROM `firstdatabase`.song";
        $lastSongID = intval($db->createCommand($sql)->query()->read()['max(song_id)']);
        
        echo($lastSongID . ' songs in table.' . PHP_EOL);
        echo('Updating songs...' . PHP_EOL);

        while($rowIndex <= $lastSongID) {
            $startTime = microtime(true);

            
            $sql = "UPDATE `firstdatabase`.song
                    SET image = NULL
                    WHERE song_id in (
                        SELECT song_id
                        FROM (
                            SELECT song_id, name 
                            FROM `firstdatabase`.song 
                            WHERE song_id > " . $rowIndex . "
                            LIMIT " . $batchSize . "
                        ) s
                        INNER JOIN (
                            SELECT DISTINCT song_name 
                            FROM `seconddatabase`.playlist 
                        ) p ON s.name LIKE p.song_name
                        ORDER BY s.song_id ASC
                    )";

            $affectedRows  = $db->createCommand($sql)->execute();


            $rowIndex  = $batchSize;
            
            $endTime = microtime(true);
            $elapsedTime = round($endTime - $startTime, 2);
          
            
        }
 

На самом деле это скорее вопрос к миру SQL, а не к миру PHP, но вот мои рекомендации:

Не делайте это по одной строке за раз в цикле while. Создайте более сложную инструкцию обновления, которая может сделать все это за одно обращение к базе данных. Команды базы данных — самая медленная часть вашего php-кода, вы хотите ограничить количество обращений к базе данных.

Когда вы уверены, что сможете выполнить операцию с помощью одной команды sql, или даже если вы не думаете, что это возможно, затем перенесите свой код в хранимую процедуру в базе данных. Использование сложных SQL-запросов в качестве хранимых процедур может очень помочь в поддержании вашего кода.

Убедитесь, что у вас есть индексы в ваших таблицах. Вы должны убедиться, что ваши запросы попадают в эти индексы для лучшей производительности.

Вот вариант для одного запроса:

 update `firstdatabase`.song
set image = null
where song_id in (
    select s.song_id 
    from `firstdatabase`.song s
    INNER JOIN `seconddatabase`.playlist p 
        ON s.name LIKE p.song_name"
);
 

Очевидно, что у нас нет доступа к вашей базе данных, поэтому вам нужно будет вносить изменения там, где это необходимо, но, надеюсь, это поможет вам встать на правильный путь.

РЕДАКТИРОВАТЬ: попробуйте заменить свой второй набор кода следующим:

     $lastSongID = 0;
    $rowIndex = 0;
    $affectedRows = 0;
    
    $sql = "SELECT max(song_id) FROM `firstdatabase`.song";
    $lastSongID = intval($db->createCommand($sql)->query()->read()['max(song_id)']);
    
    echo($lastSongID . ' songs in table.' . PHP_EOL);
    echo('Updating songs...' . PHP_EOL);
    
    $startTime = microtime(true);
    $sql = "
        update `firstdatabase`.song
        set image = null
        where song_id in (
            select s.song_id 
            from `firstdatabase`.song s
            INNER JOIN `seconddatabase`.playlist p 
                ON s.name LIKE p.song_name"
        )";
    
    $affectedRows  = $db->createCommand($sql)->execute();
        
    $endTime = microtime(true);
    $elapsedTime = round($endTime - $startTime, 2);
 

Если это сработает, то дайте мне знать время, необходимое для запуска, если это не сработает, это проблема с SQL (опять же, я не вижу таблиц, поэтому я предполагаю).