mysql - 如何加速MySQL计数查询或让它在后台运行

Question

我有一个包含大约 300 万行的表。表格是这样的:

director_id movie_id
----------- --------
1           5
1           9
1           11
3           7
3           15

我需要每个导演的 movie_count。在来自 MySQL 客户端的 SELECT 语句中，为单个 Controller 执行此操作大约需要 800 毫秒。我正在遍历主导演表，获取 director.id 并使用它查询该表的计数。 300 万条记录需要很长时间。

重要

请注意，一位导演可以拥有超过 2000 部电影。不只是 3 或 5。

代码是这样的:

public function movieCount($director_id)
{
    $sql = 'SELECT COUNT(`director_id`) `movie_count` FROM `movie_director`
                WHERE `director_id` = "' .$director_id .'"';

        $stmt = $this->pdo->prepare($sql);

        $stmt->execute();

        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        return $row;
}


public function directors(): iterable
{
    $sql = 'SELECT `id`, `name` FROM `directors`';

    $stmt = $this->pdo->prepare($sql);

    $stmt->execute();

    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
        yield $row;
    }
}


foreach($directors as $director) {
    $movieCount = movieCount($director["id"]);

    // Write movieCount to somewhere
}

问题是:

有什么办法可以提高效率吗？我认为最好事先将 movie_count 写入导演表，以便我可以快速选择它。有没有办法自动化这个，当空闲的 MySQL 引擎慢慢查询它并在后台将它写入 movie_count 中的 director 表？

Answer 1

为你的表添加索引:

ALTER TABLE movie_director ADD INDEX accelerate (director_id);

然后您可以为特定的 director_id 运行选择查询:

SELECT director_id, 
       count(*) as total_movies 
FROM movie_director
WHERE director_id = 123

如果你更关心性能，你需要摆脱这个循环:

foreach($directors as $director) {
    $movieCount = movieCount($director["id"]);
    ...
}

并使用 GROUP BY 查询一次获得所有计数:

SELECT director_id, 
       count(*) as total_movies 
FROM movie_director
GROUP BY director_id;

要将此更改应用于您的代码，您可以修改您的函数:

public function movieCounts()
{
    $sql = 'SELECT COUNT(*) `movie_count` 
            FROM `movie_director`
            GROUP BY `director_id`';
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $rows;
}
$counts = movieCounts();
foreach($directors as $director) {
    $movieCount = $counts[$director['id']] ?? 0;

    // Write movieCount to somewhere
}