如何加速mysql count查询或者让它在后台运行

bwntbbo3 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(305)

我有一张大约有300万行的table。table是这样的：

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

我需要每个导演的电影数量。在mysql客户机的select语句中，为一个控制器执行此操作大约需要800毫秒。我正在遍历主director表，获取director.id并用它查询该表的计数。有三百万张唱片要花很长时间。
重要的
请注意，一个导演可以有超过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
}

问题是：
有什么办法能提高效率吗？我想最好是事先将电影计数写入director表，这样我就可以快速地选择它。有没有一种方法可以自动实现这一点，当空闲的mysql引擎缓慢地查询它并在movie\u count后台将其写入director表？

mysql count background-process

来源：https://stackoverflow.com/questions/52970743/how-to-accelerate-mysql-count-query-or-let-it-run-in-background

2条答案

按热度按时间

ujv3wf0j1#

向表中添加索引：

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
}

赞(0）回复(0）举报 2021-06-18

hm2xizp92#

您可以一次“收集”所有控制器id，然后使用单个查询，而不是在循环中查询单个控制器id。

public function movieCountForDirectors($director_ids)
{
    $sql = "SELECT `director_id`, 
                    COUNT(*) `movie_count` 
             FROM `movie_director`
             WHERE `director_id` IN (" .$director_ids . ") 
             GROUP BY `director_id`";

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

        $stmt->execute();

        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return $rows;
}

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;
    }
}

// Get all director id(s) in comma separated string
$director_ids = implode(',', array_column($directors, 'id'));

// get movie counts for all of them
$counts = movieCountForDirectors($director_ids);

赞(0）回复(0）举报 2021-06-18

我来回答

如何加速mysql count查询或者让它在后台运行

2条答案

相关问题

热门标签

最新问答