mysql - 距经度 x 距离的最大纬度 - 距纬度 x 距离的最大经度 - SQL

Question

现在我有一个包含 1 亿条插入的表:

CREATE TABLE o (
    id          int UNIQUE,
    latitude    FLOAT(10, 8),
    longitude   FLOAT(11, 8)

);

在我的后端，我收到用户的纬度/经度，并尝试返回 x 距离内的所有内容。

我认为我可以计算 X 距离的最大纬度/经度，而不是对每个结果执行距离公式。

因此，我们通过查找最大纬度/最小纬度、最大长/最小长来创建一个正方形。

一旦我们有了这些最大值，我们就会对此范围的值进行查询，从而使我们的子集显着变小，然后执行实际距离公式(即查找 X 距离内的值)。

所以我要问你的问题是:是什么让我跑得更快？

选项 1)

• 1 亿个条目获得该集合的距离公式。

选项 2)

• 我们不是对 1 亿个条目的集合计算距离公式，而是计算最小/最大纬度/经度。
• 从包含 1 亿条条目的表格中选择该范围内的值
• 在我们新的较小集合上计算距离公式。

选项3)

• SQL 中已经存在一些东西

如果选项 2 更快，那么下一个问题实际上就是解决数学问题。

如果您想查看继续阅读:

经纬度公式

dlon = lon2 - lon1
dlat = lat2 - lat1
a = (sin(dlat/2))^2 + cos(lat1) * cos(lat2) * (sin(dlon/2))^2
c = 2 * atan2(sqrt(a), sqrt(1-a)) 
d = R * c

显然我们可以重新排列它，因为 D(假设 1 英里)和 R(地球半径)是一个设定值，因此我们得到 D/R = C。

问题是我们如何计算 C/2 = atan2(sqrt(a), sqrt(1-a))？

Answer 1

1 - 100M 行需要扫描和测试。偶尔做一次还可以，但是做太多就太慢了。

2 -- 使用伪方形边界框并执行

WHERE latitude  BETWEEN ...
  AND longitude BETWEEN ...

是一个很好的第一步。纬度范围是一个简单常数乘以X；经度范围也除以 cos(latitude)。

但是当您尝试在正方形中查找那些行时，问题就出现了。 latitude 和/或 longitude 索引的任何组合，无论是单独还是一起，都只会部分过滤。也就是说，它将忽略经度并为您提供纬度范围内的所有内容，反之亦然。这可能会让您减少到 100,000 行来检查距离。这比 100,000,000 好很多，但没有您希望的那么好。

3 -- http://mysql.rjweb.org/doc.php/latlng确实到达广场，或者非常接近。它是按规模设计的。我只测试了 3M 行，而不是 100M，但它应该可以正常工作。

主要技巧是按纬度进行分区，然后将经度作为主键中的第一列，以便 InnoDB 将分区中附近的行聚集在一起。如果您查找 X 英里(或公里)内的所有行，它可能会查找(并计算大圆距离)所需行数的大约两倍，而不是 100K。如果您想查找最近的 100 个项目，则可能需要大约 400 (4x)。

对于 SPATIAL 索引，您可能需要升级到 5.7.6 ，这是添加 ST_Distance_Sphere() 和 ST_MakeEnvelope() 的时间。 (MakeEnvelope 只比自己构建一个 Polygon 稍微方便一点——它有平坦地球综合症。)