为 MySQL 半径内的点查找大表的最快方法是什么(经度纬度)

目前我有几个表有 100k+ 行.我正在尝试查找如下数据.

SELECT*, SQRT(POW(69.1 * (latitude - '49.1044302'), 2) + POW(69.1 * ('-122.801094' - longitude) * COS(latitude/57.3), 2)) AS距离从站有距离<5ORDER BY 距离限制 100

但目前这种方法会因高负载而变慢.有些查询需要 20 多秒才能完成.

如果有人知道任何更好的优化方法就太好了.

解决方案

首先，如果你有大量的地理空间数据，你应该使用 mysql 的地理空间扩展而不是这样的计算.然后您可以创建空间索引，这将加快很多查询，您不必像上面那样编写冗长的查询.

使用与 ST_Distance 或创建具有感兴趣半径的几何体以及 ST_within 可能会给您带来良好的结果，并且可能比当前快得多.然而，实现此目标的最佳和最快方法是 ST_Dwithin 尚未在 mysql 中实现.>

Currently I have a few tables with 100k+ rows. I am trying to lookup the data like follows.

SELECT
*, SQRT(POW(69.1 * (latitude - '49.1044302'), 2) + POW(69.1 * ('-122.801094' - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM stops
HAVING distance < 5
ORDER BY distance limit 100

But currently this method slows with high load. Some queries are taking 20+ seconds to complete.

If anyone knows any better ways to optimize this would be great.

解决方案

Well first of all if you have a lot of geospatial data, you should be using mysql's geospatial extensions rather than calculations like this. You can then create spatial indexes that would speed up many queries and you don't have to write long drawn out queries like the one above.

Using a comparision with ST_Distance or creating a geometry with the radius of interest along with ST_within might give you good results and could be a lot faster than the current. However the best and fastest way to achieve this, ST_Dwithin isn't implemented yet in mysql.