我尝试更新数据库中的许多行（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行，如果将batchSize增加到10000，则需要大约10分钟的时间（使用上面的代码需要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);
}
1条答案
按热度按时间qmb5sa221#
这实际上更多的是一个SQL世界而不是PHP世界的问题，但这里是我的建议：
不要在while循环中一次只做一行。创建一个更复杂的update语句，可以在一次数据库命中中完成所有操作。数据库命令是php代码中最慢的部分，您需要限制对数据库的调用次数。
当你确信你可以在一个sql命令中完成操作，或者即使你不认为这是可能的，那么就把你的代码拉到数据库的存储过程中。把复杂的sql查询作为存储过程可以帮助你维护代码。
请确定您的数据表上有索引。您必须确定您的查询符合这些索引，以取得最佳效能。
下面是单个查询的一个选项：
显然，我们无法访问您的数据库，因此您需要在必要的地方进行更改，但希望它能让您走上正轨。
编辑：尝试用以下代码替换第二个代码集：
如果它工作，那么让我知道它需要运行的时间，如果它不工作，它是一个问题的SQL（再次我看不到表，所以我猜测）。