logo标签列表

Haversine公式中的Join操作

phpmysqljoinoptimizationhaversine
4
我正在使用以下PHP代码实现Haversine公式：

我正在使用以下PHP代码实现Haversine公式

$result=mysqli_query($mysqli,"SELECT *,( 6371 * acos( cos( radians({$lat}) ) * cos( radians( `latitude` ) ) * cos( radians( `longitude` ) -radians({$lon}) ) +sin( radians({$lat}) ) * sin( radians( `latitude` ) ) ) ) AS distance FROM `places` HAVING distance <= {$radius} ORDER BY distance ASC") or die(mysqli_error($mysqli));

在Haversine获取循环内部，我有一个查询，遍历haversine结果来选择与haversine公式返回的ID匹配的记录。查询如下。

 while($row = mysqli_fetch_assoc($result)) 

    {
   $rest_time=$row['id'];

$result1=mysqli_query($mysqli,"SELECT * FROM my_friends  WHERE personal_id='".$personal_id."' AND id='".$rest_time."'") or die(mysqli_error($mysqli)); 

//Some operations here
    }

我该如何执行Join操作将这些查询混合成一个单一的查询？从优化的角度来看，如果第二个表格有50000个用户而第一个表格几乎只有1000条记录，那么这样做是否明智？

4
警告：在使用 mysqli 时，您应该使用参数化查询和bind_param将用户数据添加到查询中。不要使用字符串插值或串联来实现此操作，因为这会导致严重的SQL注入漏洞。永远不要直接将$_POST或$_GET数据放入查询中，如果有人试图利用您的错误，这可能会非常危险。 - tadman
@tadman，您认为查询中出现的变量未经过净化，但这不一定是事实。 - Walter Tross
@WalterTross 当你假设任何事情时，那就是你遇到问题的时候。这太危险了，不能轻视。必须明显地转义，否则就没有证明它们是安全的。 - tadman
1
@tadman，你写道：“...你已经创建了一个严重的SQL注入漏洞”。除了我会称之为漏洞而不是错误之外，你还应该写“除非你能保证你使用的变量是数字或转义字符串”。在我曾经工作的公司多年中，我们确实保证了这一点（并没有使用mysqli），因此在这种情况下直接指出错误是过头了。但当然，你是正确的，关于字符串拼接和插值现在应该被避免，因为mysqli是标准。 - Walter Tross
1
我已经使用了参数化查询和bind_param。我只是向您展示公式。这个讨论正在完全不同的方向上进行。 - Ashif Shereef
@AshifShereef 请尝试将代码包含在你的示例中，这可以避免干扰并有助于推广最佳实践。 - tadman
2个回答
5
任何在这里对所有行进行操作的操作都将因有那么多记录而变慢。
你需要做的是利用索引。要使用索引，它必须是一个简单查询，而不是 函数的结果（目前就是这样）。
通过进行半径搜索，您正在围绕一个点画圆。在制作圆之前，通过使用一些三角函数，我们可以得出以下结果。

Circle two squares

S1代表内部最大的正方形，S2代表外部最小的正方形。

现在我们可以计算出这两个正方形的尺寸，S2外面的任何东西都会被索引命中，而S1里面的任何东西都会被索引命中，只留下需要使用慢速方法查找的小区域。

如果您需要从该点开始的距离，请忽略S1部分（因为圆内的所有内容都需要haversine函数），请注意，虽然圆内的所有内容都需要它，但不是每个点都在距离范围内，因此仍然需要两个WHERE子句。

因此，让我们使用单位圆来计算这些点 Unit Circle

function getS1S2($latitude, $longitude, $kilometer)
{
    $radiusOfEarthKM  = 6371;
    $latitudeRadians  = deg2rad($latitude);
    $longitudeRadians = deg2rad($longitude);
    $distance         = $kilometer / $radiusOfEarthKM;

    $deltaLongitude = asin(sin($distance) / cos($latitudeRadians));

    $bounds = new \stdClass();

    // these are the outer bounds of the circle (S2)
    $bounds->minLat  = rad2deg($latitudeRadians  - $distance);
    $bounds->maxLat  = rad2deg($latitudeRadians  + $distance);
    $bounds->minLong = rad2deg($longitudeRadians - $deltaLongitude);
    $bounds->maxLong = rad2deg($longitudeRadians + $deltaLongitude);

    // and these are the inner bounds (S1)
    $bounds->innerMinLat  = rad2deg($latitudeRadians  + $distance       * cos(5 * M_PI_4));
    $bounds->innerMaxLat  = rad2deg($latitudeRadians  + $distance       * sin(M_PI_4));
    $bounds->innerMinLong = rad2deg($longitudeRadians + $deltaLongitude * sin(5 * M_PI_4));
    $bounds->innerMaxLong = rad2deg($longitudeRadians + $deltaLongitude * cos(M_PI_4));

    return $bounds;
}

现在您的查询变成了

SELECT 
  *
FROM
  `places` 
HAVING p.nlatitude BETWEEN {$bounds->minLat} 
  AND {$bounds->maxLat} 
  AND p.nlongitude BETWEEN {$bounds->minLong} 
  AND {$bounds->maxLong} 
  AND (
    (
      p.nlatitude BETWEEN {$bounds->innerMinLat} 
      AND {$bounds->innerMaxLat} 
      AND p.nlongitude BETWEEN {$bounds->innerMinLong} 
      AND {$bounds->innerMaxLong}
    ) 
    OR (
      6371 * ACOS(
        COS(RADIANS({ $lat })) * COS(RADIANS(`latitude`)) * COS(
          RADIANS(`longitude`) - RADIANS({ $lon })
        ) + SIN(RADIANS({ $lat })) * SIN(RADIANS(`latitude`))
      )
    )
  )) <= {$radius} 
ORDER BY distance ASC

重要提示

上述内容是为了易读性而添加的文本，请确保这些值被正确地转义/最好是参数化

然后就可以利用索引，使连接更快地完成

添加连接后变为：

SELECT 
  *
FROM
  `places` p
  INNER JOIN my_friends f ON f.id = p.id
WHERE   p.latitude BETWEEN {$bounds->minLat} 
  AND {$bounds->maxLat} 
  AND p.longitude BETWEEN {$bounds->minLong} 
  AND {$bounds->maxLong} 
  AND (
    (
      p.latitude BETWEEN {$bounds->innerMinLat} 
      AND {$bounds->innerMaxLat} 
      AND p.longitude BETWEEN {$bounds->innerMinLong} 
      AND {$bounds->innerMaxLong}
    ) 
    OR (
      6371 * ACOS(
        COS(RADIANS({ $lat })) * COS(RADIANS(`latitude`)) * COS(
          RADIANS(`longitude`) - RADIANS({ $lon })
        ) + SIN(RADIANS({ $lat })) * SIN(RADIANS(`latitude`))
      )
    )
  )  <= {$radius} 
  AND f.personal_id = {$personal_id}
ORDER BY distance ASC

重要提示

以上内容为了易读性而提供，但请确保这些值被正确转义/最好是参数化。

假设您有正确的索引，此查询应保持快速并允许您进行联接。

查看上面的代码，我不确定personal_id来自哪里，因此将其保留为原样。

如果您需要从查询中获取距离，则可以删除S1平方项。

    (
      p.latitude BETWEEN {$bounds->innerMinLat} 
      AND {$bounds->innerMaxLat} 
      AND p.longitude BETWEEN {$bounds->innerMinLong} 
      AND {$bounds->innerMaxLong}
    )

并移动那个 OR 的第二部分

  6371 * ACOS(
    COS(RADIANS({ $lat })) * COS(RADIANS(`latitude`)) * COS(
      RADIANS(`longitude`) - RADIANS({ $lon })
    ) + SIN(RADIANS({ $lat })) * SIN(RADIANS(`latitude`))
  )

回到仍然使用S2的选择器。
我还会确保在查询中删除“魔术数字”，6371是地球半径（单位：千米）。
考虑到我不想加入这些表。我正在像您的答案中一样循环遍历查询结果。然而，我也需要“距离”属性，就像我可以通过row['distance']获取一样。我该如何从您的查询中获取它？ - Ashif Shereef
只需将原始查询中的相同计算添加到select语句中即可。但问题是关于如何进行连接？ - exussum
如果我删除s1 Square，然后将计算部分移动到初始的select *中，那么它仍然会利用边界吗？ - Ashif Shereef
@exussem 为了使用距离，将haversine移动到select而不是添加，这样不是更好吗？ - digout
Haversine将运行所有行，然后删除较小的正方形优化。使用勾股定理效果相当不错且快速。 - exussum
1
在这种情况下，将第一个查询作为派生子查询放入第二个查询中：
SELECT  p.*, f.*    -- Select only the columns you need, not all
    FROM  
    (
        SELECT  *,
                ( 6371 * acos( cos( radians({$lat}) ) * cos( radians( `latitude` ) )
                  * cos( radians( `longitude` ) -radians({$lon}) )
                  +sin( radians({$lat}) ) * sin( radians( `latitude` ) ) )
                ) AS distance
            FROM  `places`
            HAVING  distance <= {$radius}
            ORDER BY  distance ASC"
            LIMIT 10               -- Didn't you forget this??
    ) AS p
    JOIN  my_friends AS f  ON f.personal_id p.personal_id
      AND  id='".$rest_time."'"     -- Huh??
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接