我有下面两张table
城市
内径，纬度，经度
山脉
id、纬度、经度
SELECT cities.id,
(SELECT id FROM mountains
WHERE SQRT(POW(69.1 * ( latitude - cities.lat ) , 2 ) +
POW( 69.1 * (cities.lon - longitude ) *
COS( latitude / 57.3 ) , 2 ) )<20 LIMIT 1) as mountain_id
FROM cities（查询耗时0.5060秒。）
出于复杂性的考虑，我删除了查询的一些部分（例如orderby、where）。但是，这并不会真正影响执行时间。
下面解释一下。
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY cities ALL NULL NULL NULL NULL 478379
2 DEPENDENT SUBQUERY mountains ALL NULL NULL NULL NULL 15645 Using where使用select本身不是我的问题，但是当我尝试使用给定的结果时。。。例如
id mountain_id
588437 NULL
588993 4269
589014 4201
589021 4213
589036 4952
589052 7625
589113 9235
589125 NULL
589176 1184
589210 4317…为了更新一个表，一切都变得非常缓慢。我几乎尝试了我所知道的一切。我知道依赖子查询不是最优的，但我不知道如何摆脱它。
有什么方法可以改进我的查询吗。也许把它改成一个连接？
这两个表本身并没有什么共同点，除了纬度和经度是不同的，只有在使用计算时才有联系。
mariadb的空间距离搜索（km，miles）似乎还不可用。
1条答案
按热度按时间kcwpcxri1#
使这种运算快速的诀窍是避免对每一对可能的lat/lon点进行所有的计算。为此，应合并边界框操作。
让我们从使用连接开始。在伪代码中，您需要这样的东西，但是如果您捕获了一些额外的对并不重要，只要它们比其他对相距更远。
因此，我们需要弄清楚如何快速实现on子句——让它使用索引，而不是在所有的城市和山区漫游（向伍迪·古思里道歉）。
让我们来试试这个on子句。它在+/-20英里的正方形边界框内搜索附近的成对对象。
在这个查询中，
20.0是比较极限半径，以及69.0是定义每度纬度法定英里数的常数。然后，将复合索引放在
(lat, lon, id)在两张table上，还有你的JOIN操作将能够使用索引范围扫描来提高查询效率。最后，您可以在伪代码中使用这些子句来扩充查询
这里你需要用到一个距离公式。你的问题中的笛卡尔距离公式是一个近似值，除非你在极点附近，否则效果相当好。你可以用一个大圆公式来代替。这些被称为球余弦定律，哈弗森公式，或文森特公式。