php - 当您有大量数据时，脚本功能会变慢

Question

下面的代码是一个社交网络脚本，是提要的类别，当费用有很多帖子和许多关注者时，问题甚至会变得非常慢，有人可以告诉我查询和反馈是否有问题下面的脚本，谢谢

public function __construct($dbo = NULL)
{
    parent::__construct($dbo);
}

public function count()
{
    $count = 0;

    $stmt = $this->db->prepare("SELECT * FROM profile_followers WHERE follower = (:followerId) ORDER BY create_at DESC");
    $stmt->bindParam(':followerId', $this->requestFrom, PDO::PARAM_INT);

    if ($stmt->execute()) {

        while ($row = $stmt->fetch()) {

            $stmt2 = $this->db->prepare("SELECT count(*) FROM posts WHERE fromUserId = (:fromUserId) AND removeAt = 0 ORDER BY createAt DESC");
            $stmt2->bindParam(':fromUserId', $row['follow_to'], PDO::PARAM_INT);
            $stmt2->execute();

            $count = $count + $stmt2->fetchColumn();
        }
    }

    return $count;
}

public function getMaxId()
{
    $stmt = $this->db->prepare("SELECT MAX(id) FROM posts");
    $stmt->execute();

    return $number_of_rows = $stmt->fetchColumn();
}

public function get($itemId = 0)
{
    if ($itemId == 0) {

        $itemId = $this->getMaxId();
        $itemId++;
    }

    $feed = array("error" => false,
                  "error_code" => ERROR_SUCCESS,
                  "itemId" => $itemId,
                  "items" => array());

    $stmt = $this->db->prepare("SELECT * FROM profile_followers WHERE follower = (:followerId) ORDER BY create_at DESC");
    $stmt->bindParam(':followerId', $this->requestFrom, PDO::PARAM_INT);

    if ($stmt->execute()) {

        $items = array();

        while ($row = $stmt->fetch()) {

            $stmt2 = $this->db->prepare("SELECT id FROM posts WHERE fromUserId = (:fromUserId) AND id < (:itemId) AND removeAt = 0 ORDER BY id DESC");
            $stmt2->bindParam(':fromUserId', $row['follow_to'], PDO::PARAM_INT);
            $stmt2->bindParam(':itemId', $itemId, PDO::PARAM_INT);
            $stmt2->execute();

            while ($row2 = $stmt2->fetch())  {

                $items[] = array("id" => $row2['id'], "itemId" => $row2['id']);
            }
        }

        $stmt3 = $this->db->prepare("SELECT id FROM posts WHERE fromUserId = (:fromUserId) AND id < (:itemId) AND removeAt = 0 ORDER BY id DESC");
        $stmt3->bindParam(':fromUserId', $this->requestFrom, PDO::PARAM_INT);
        $stmt3->bindParam(':itemId', $itemId, PDO::PARAM_INT);
        $stmt3->execute();

        while ($row3 = $stmt3->fetch())  {

            $items[] = array("id" => $row3['id'], "itemId" => $row3['id']);
        }

        $currentItem = 0;
        $maxItem = 20;

        if (count($items) != 0) {

            arsort($items);

            foreach ($items as $key => $value) {

                if ($currentItem < $maxItem) {

                    $currentItem++;

                    $item = new post($this->db);
                    $item->setRequestFrom($this->requestFrom);

                    $itemInfo = $item->info($value['itemId']);

                    array_push($feed['items'], $itemInfo);

                    $feed['itemId'] = $itemInfo['id'];

                    unset($itemInfo);
                    unset($item);
                }
            }
        }
    }

    return $feed;
}

public function setRequestFrom($requestFrom)
{
    $this->requestFrom = $requestFrom;
}

public function getRequestFrom()
{
    return $this->requestFrom;
}`

Answer 1

一些一般建议:

• 使用“SELECT follow_to”代替“SELECT *”，因为这是唯一的您使用的列
• 在同一条语句上，排序需要时间并且不被使用
• 无需为每个 profile_follower 执行新查询
• 在内部查询中，不需要仅仅为了获取计数而进行排序

如果有 1,000 个关注者，您就会对帖子进行 1,000 次不必要的排序。

相反，请在 id 上使用一个查询并为帖子建立索引，并在关注者上使用 removeAt 和索引 profile_followers。

SELECT  count(*) 
FROM    posts p
JOIN    profile_followers pf ON p.fromUserId = pf.follow_to
WHERE   p.removeAt = 0
AND     pf.follower = :followerId;