改善空间MySQL查询的性能 [英] Improving performance of spatial MySQL query

问题描述

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

I have a query that returns all records, ordered by distance from a fixed point, compared to a POINT field in my MySQL 5.7 database.

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

For a simple example, lets say it looks like this:

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

我的实际查询还必须进行几次连接才能获取结果的其他数据.

My actual query also has to do a few joins to get additional data for the results.

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

The issue is, that in order to sort the data by distance, it needs to calculate the distance over every single record in the database (currently around 100,000 records).

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

I can't cache the query, as it would only be specific to those original coordinates.

是否仍然限制必须计算的数据?例如，对附近商店进行可靠的粗略计算，例如lat + lng +/- 3度?这样它只需要处理一部分数据?

Is there anyway to limit the data that has to be calculated? E.g a reliable rough calculation for nearby shops, say +/- 3 degrees for the lat + lng? So that it only has to process a subset of the data?

如果有人对这种优化有任何经验，我很乐意提供一些建议，谢谢.

If anyone has any experience in this sort of optimisation, I'd love some advice, thanks.

推荐答案

是的，您可以使用一些简单的近似值，根据其中的条件来过滤掉半径范围之外的那些位置. 这篇出色的博文标题为"SQL的最近位置查找器( MySQL，PostgreSQL，SQL Server)"描述了这样的优化:

Yes, you can use some simple approximation in where criteria to filter out those locations that are obvioulsy out of the radius. This great blog post titled "Fast nearest-location finder for SQL (MySQL, PostgreSQL, SQL Server)" describes such optimizations:

请记住，根据本文前面的背景资料， 纬度为111.045公里.所以，如果我们有一个索引 我们的纬度列，我们可以使用这样的SQL子句消除 距离太远或太远而无法到达的点 50公里之内.

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)

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

此WHERE子句允许MySQL使用索引来省略很多纬度 计算Haversine距离公式之前的点数.它允许 MySQL对纬度索引执行范围扫描.

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.

最后，我们可以使用类似但更复杂的SQL子句来消除 东西方向太远的点.这个子句比较复杂 因为经度越小，距离越远 从赤道开始.这是公式.

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))))

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

因此，将所有内容放在一起，此查询将找到最接近的15点 在(纬度，经度)50公里的边界框内.

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

上面介绍了边界矩形的理论背景.

The above describes the theoretical background to bounding rectangles.

这篇关于改善空间MySQL查询的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！