修复在MySQL中循环产生的性能问题

Question

我正在开发一个虚拟游戏棋盘风格的游戏，玩家可以在棋盘的某个区域获得积分。(现在别对我太客气，因为我这样做只是一种爱好，所以我可能会以最糟糕的方式来做这件事)

我有三张桌子。一个存储所有玩家信息(例如，id、屏幕名称)。第二个存储所有的区域信息(例如，id，x，y)和第三个存储每个玩家在每个区域中有多少点(例如，id、playerid、areaid、points)。为了创建一个“排行榜”，我循环了所有的玩家，然后在这个循环中我也循环了所有的区域，最后在第二个循环中，我得到了那个区域的前导，看看它是否与第一个循环中的当前玩家匹配，如果匹配，我会递增一个计数器，然后将它存储到一个数组中。(参见下面的代码和一些注释)

我研究了MySQL缓存，但我无法访问许多服务器选项，并且希望尽可能多地保留结果，因此缓存可能不是正确的方法。

我的问题是我这样做是否正确。目前只有10个左右的玩家，而且大约是。500个区域。我发现下面的脚本已经运行了大约5-8秒。潜在地可能有数百万个区域，因此处理过程中如此长的延迟可能是灾难性的(至少对于排行榜而言)。我这样做是对的吗，或者有没有更好的方法？

<?php
    $leaders = array();

    //Loop through all the players
    $sql = "SELECT * FROM players";
    $result = mysqli_query($con, $sql) or die(mysqli_error($con));
    while ($row = mysqli_fetch_array($result)) {
        //save player information into variables
        $playerId = $row['id'];
        $playerScreenName = $row['screenname'];

        //Reset the area counter
        $AreaCount = 0;
        $leader = array();

        //Loop through all areas
        $sql2 = "SELECT * FROM areas";
        $result2 = mysqli_query($con, $sql2) or die(mysqli_error($con));
        while ($row2 = mysqli_fetch_array($result2)) {
            $areaId = $row2['id'];

            //Get the player with the most points in that area
            $sql3 = "SELECT * FROM points WHERE areaid='$areaId' ORDER BY totalpoints DESC LIMIT 1";
            $result3 = mysqli_query($con, $sql3) or die(mysqli_error($con));
            while ($row3 = mysqli_fetch_array($result3)) {
                $leaderOfArea = $row3['playerid'];

                //See if the leader of the area is the same player we are looping through
                if ($playerId == $leaderOfArea) {
                    //if it is, then increment the counter
                    $AreaCount++;
                }
            }
        }
        //Store the leader information into an array to be output later
        $leader['screenname'] = $playerScreenName;
        $leader['areacount'] = $AreaCount;

        $leaders[] = $leader;
    }

    // sort leaders by score
    usort($leaders, 'compare_areacount');
?>

Answer 1

打开数据库连接是有开销的，当您在一个循环中这样做时，您会加剧问题(然后当您在其中添加一个循环时，您会使问题变得更糟)。相反，可以使用联接或子查询将其重新构造为一个查询。

This post has some specifics.

Answer 2

我认为这段代码会有所帮助。您可能需要使用所需的适当列名来更改sql查询。

<?php

$leaders = array();

//Loop through all the players
$sql = "SELECT * FROM players";
$result = mysqli_query($con, $sql) or die(mysqli_error($con));

$players = array();
while ($row = mysqli_fetch_array($result)) {
    //save player information into variables
    $players[$row['id']] = array($row['screenname'], 0);
    // number 0 will be the count of how many times this player is the leader
}

$sql = "SELECT Area.id, Area.name, (SELECT Pts.playerid FROM `points`"
    . " AS Pts WHERE Pts.areaid=Area.id ORDER BY totalpoints"
    . " DESC LIMIT 1) AS `leader_id` FROM `areas` AS Area";
$result = mysqli_query($con, $sql) or die(mysqli_error($con));

$areas = array();
while ($row = mysqli_fetch_row($result)) {
    $areas[$row[0]] = $row;

    // $row[2] will contain leader_id
    // index 1 corresponds to the second element in player values array
    $players[$row[2]][1]++;
}

// now if you want to print:
foreach ($areas as $area_id => $area) {
    echo "Area id: " . $area_id . ", name: " . $area[1] . ", leader_id: " . $area[2] . "<br /><br />";
}

foreach ($players as $player_id => $player) {
    echo "Player id: " . $player_id . ", name: " . $player[0] . ", No of areas this player is a leader of: " . $player[1] . "<br /><br />";
}