mysql - 提高空间 MySQL 查询的性能

Question

我有一个查询返回所有记录，与我的 MySQL 5.7 数据库中的 POINT 字段相比，按距固定点的距离排序。

举个简单的例子，假设它看起来像这样:

SELECT shops.*, st_distance(location, POINT(:lat, :lng)) as distanceRaw 
FROM shops 
ORDER BY distanceRaw
LIMIT 50

我的实际查询还必须进行一些连接以获得结果的额外数据。

问题是，为了按距离对数据进行排序，它需要计算数据库中每条记录(目前大约有 100,000 条记录)的距离。

我无法缓存查询，因为它只会特定于那些原始坐标。

有没有办法限制必须计算的数据？例如，附近商店的可靠粗略计算，比如 lat + lng +/- 3 度？这样它只需要处理数据的一个子集？

如果有人在这种优化方面有任何经验，我希望得到一些建议，谢谢。

Answer 1

是的，您可以在 where 标准中使用一些简单的近似值来过滤掉那些明显超出半径的位置。 This great blog post标题为“SQL(MySQL、PostgreSQL、SQL Server)的快速最近位置查找器”描述了此类优化:

Remember, from our background information earlier in this article, that a degree of latitude is 111.045 km. So, if we have an index on our latitude column, we can use a SQL clause like this to eliminate the points that are too far north or too far south to possibly be within 50 km.

latitude BETWEEN latpoint - (50.0 / 111.045)
AND latpoint + (50.0 / 111.045)

This WHERE clause lets MySQL use an index to omit lots of latitude points before computing the haversine distance formula. It allows MySQL to perform a range scan on the latitude index.

Finally, we can use a similar but more complex SQL clause to eliminate points that are too far east or west. This clause is more complex because degrees of longitude are smaller distances the further away from the equator we move. This is the formula.

longitude BETWEEN longpoint - (50.0 / (111.045 *
COS(RADIANS(latpoint))))
AND longpoint + (50.0 / (111.045 * COS(RADIANS(latpoint))))

So, putting it all together, this query finds the neareast 15 points that are within a bounding box of 50km of the (latpoint,longpoint).

以上描述了边界矩形的理论背景。